Databases
StackOverflow2013
Postsdbo
POInserts are 4x slower if table has lots of record (400K) vs. if it's empty
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POInserts are 4x slower if table has lots of record (400K) vs. if it's empty
    text
    copied!<p>(Database: Oracle 10G R2)</p> <p>It takes 1 minute to insert 100,000 records into a table. But if the table already contains some records (400K), then it takes 4 minutes and 12 seconds; also CPU-wait jumps up and “Free Buffer Waits” become really high (from dbconsole).</p> <p>Do you know what’s happing here? Is this because of frequent table extents? The extent size for these tables is 1,048,576 bytes. I have a feeling DB is trying to extend the table storage.</p> <p>I am really confused about this. So any help would be great!</p> <hr> <p>This is the insert statement:</p> <pre> begin for i in 1 .. 100000 loop insert into customer (id, business_name, address1, address2, city, zip, state, country, fax, phone, email ) values (customer_seq.nextval, dbms_random.string ('A', 20), dbms_random.string ('A', 20), dbms_random.string ('A', 20), dbms_random.string ('A', 20), trunc (dbms_random.value (10000, 99999)), 'CA', 'US', '798-779-7987', '798-779-7987', 'asdfasf@asfasf.com' ); end loop; end; </pre> <hr> <p>Here <a href="http://dag.wieers.com/home-made/dstat/" rel="nofollow noreferrer">dstat</a> output (CPU, IO, MEMORY, NET) for :</p> <ol> <li>Empty Table inserts: <a href="http://pastebin.com/f40f50dbb" rel="nofollow noreferrer">http://pastebin.com/f40f50dbb</a></li> <li>Table with 400K records: <a href="http://pastebin.com/f48d8ebc7" rel="nofollow noreferrer">http://pastebin.com/f48d8ebc7</a></li> </ol> <hr> <p>Output from <code>v$buffer_pool_statistics</code></p> <pre> ID: 3 NAME: DEFAULT BLOCK_SIZE: 8192 SET_MSIZE: 4446 CNUM_REPL: 4446 CNUM_WRITE: 0 CNUM_SET: 4446 BUF_GOT: 1407656 SUM_WRITE: 1244533 SUM_SCAN: 0 FREE_BUFFER_WAIT: 93314 WRITE_COMPLETE_WAIT: 832 BUFFER_BUSY_WAIT: 788 FREE_BUFFER_INSPECTED: 2141883 DIRTY_BUFFERS_INSPECTED: 1030570 DB_BLOCK_CHANGE: 44445969 DB_BLOCK_GETS: 44866836 CONSISTENT_GETS: 8195371 PHYSICAL_READS: 930646 PHYSICAL_WRITES: 1244533 </pre> <hr> <p><strong>UPDATE</strong></p> <p>I dropped indexes off this table and performance improved drastically even when inserting 100K into 600K records table (which took 47 seconds with no CPU wait - see dstat output <a href="http://pastebin.com/fbaccb10" rel="nofollow noreferrer">http://pastebin.com/fbaccb10</a> ) . </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload