Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Insert performance with dbms_random

Jairam SMay 13 2020 — edited May 13 2020

Dear All,

Hope you are doing good. I need a help on below,

Oracle DB version: 12.2.0.1 (But all versions will be tested)

OS: RHEL 7

For our testing environment, we need to generate test data on oracle tables. Table size will be decided by the tester.

Larger the size more the number of rows.

This table should be updated with unique data once in every hour.

We can use any kind of data, means, table might not need to have meaningfull data.

In order to acheive this, I'm using below,

created a table as below,

create table test (tno int,tname varchar2(4000),tdate timestamp,tvalue int);

For random data, I used dbms_random option to insert 500MB data which needs 485450 records,

insert into test select level as tno,dbms_random.string('X',1000) as tname,sysdate as tdate,dbms_random.value(10001,20000) as tvalue from dual connect by level <= 50000;

The problem is insert performance is very slow, it takes 7mins to insert 500MB data.

Can someone please help me to achieve this with a very good throughput? Thank you.,

Regards,

Jai.

This post has been answered by BrunoVroman on May 13 2020
Jump to Answer
Comments
Post Details
Added on May 13 2020
12 comments
1,123 views