-
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,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
I am able to recall, it's dams_random package.
Thanks,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
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,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|