loading tables with test data

Hi all,

Is there any way to load a table with n number of rows to test performance scenarios? Long time back I read some where about a package but not able to recall.

My requirement is to load several millions of data into few table and test performance scenarios. I can do this by inserting into same table several times but want to do this using package.

Appreciate your suggestion(s).

Thanks,

I am able to recall, it's dams_random package.

Thanks,

You can use a pipelined function to generate data, and use the dbms_random to randomize the output.

Vijay, as I believe in sharing try this out.

CREATE TABLE XXX.TAB1
(
ID NUMBER NOT NULL,
S1 VARCHAR2(30 BYTE),
N1 NUMBER(10,0),
INSERT_DATE DATE DEFAULT sysdate,
UPDATE_DATE DATE
)....tablespace bla bla bla


CREATE INDEX XXX.TAB1_ID ON XXX.TAB1 (ID) tablespace bla bla bla


CREATE OR REPLACE PACKAGE XXX.tab1dml_pkg as
procedure addtran_tab1(num_loops NUMBER, commit_size NUMBER) ;
procedure updtran_tab1(commit_size NUMBER) ;
end tab1dml_pkg;
/


procedure addtran_tab1 (num_loops IN NUMBER, commit_size IN NUMBER) is
id BINARY_INTEGER := 0;
i number := 0;
c_cnt number :=0;

l_start number default dbms_utility.get_time;

begin
WHILE id < num_loops
LOOP
id := id + 1;

INSERT INTO c0harpa.tab1
(id, N1, S1)
VALUES
(id,
DBMS_RANDOM.RANDOM,
DBMS_RANDOM.VALUE(10000000,99999999),
dbms_random.string('X', 20));

i := i+1;
if mod(i, commit_size) = 0
THEN
c_cnt := c_cnt+1;
COMMIT;
end if;

END LOOP;
commit;
dbms_output.put_line (round((dbms_utility.get_time-l_start)/100, 2) ||' Seconds...' || c_cnt || ' commits' );

END;


procedure updtran_tab1 (commit_size IN NUMBER) is
i number := 0;
c_cnt number :=0;

l_start number default dbms_utility.get_time;

begin
for r in (select * from xxx.tab1)
LOOP


UPDATE XXX.tab1 set
N1= DBMS_RANDOM.RANDOM,
S1=dbms_random.string('U', 25),
UPDATE_DATE=sysdate where id = r.id;

i := i+1;
if mod(i, commit_size) = 0
THEN
c_cnt := c_cnt+1;
COMMIT;
end if;
END LOOP;
commit;
dbms_output.put_line (round((dbms_utility.get_time-l_start)/100, 2) ||' Seconds.
..' || c_cnt || ' commits' );

END;

END;

In this example, you will insert 1 million rows into the table and commit on
every 1000th row. The update package will read though every row in the
the table, in this case (1 million) and will commit on every 1000th row.

Code to invoke from shell script like this:

LOG=/tmp/test.out

sqlplus / <> $LOG 2>&1
set serveroutput on
set timing on
exec XXX.tab1dml_pkg.addtran_tab1(1000000, 1000);
exec XXX.tab1dml_pkg.updtran_tab1(1000);
exit
EOT


If the numbers are too large you may get a 1555 so you may want to
add code to handle that.

There are different values that can be used for the RANDOM function, change
to suit your needs.... In my test case I have cols N1-N20 (all NUMBERS) and
S1-S20 (ALL VARCHAR2) and I use different parameters to RANDOM in order
to get a good mix (distribution) of data.

Good luck

Thank you Stecal & BeefStu ...

Thanks,