RE: TKPROF output -- high current gets for INSERT

Doesn't each RE constraint use an index?


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Upcoming events:
- Performance Diagnosis 101: 1/4 Calgary
- SQL Optimization 101: 2/7 Dallas
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of sat0789@xxxxxxxxxxx
Sent: Thursday, December 30, 2004 2:09 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: TKPROF output -- high current gets for INSERT

Hello,
 We are running a ETL process through informatica. This process is
 running 4 concurrent sessions into a single partition of a fact table.
 I have freelists and initrans set appropriately. There is a db trigger
 at a statement level for this table which does "alter session
 skip_unusable_indexes = true". Bitmap indexes on FK'S for that
 partition is made unusable before we run the insert stmt.
There are 13 refrential integrity constriants for this fact table that
are enabled during this insert operation..

With this as input..can someone please shed some light on why i am
getting so many current gets...for eg  tkprof output has

Execute   1175     23.09      23.22         10(disk)  3616(query)   
1092396(current)       56400 (rows)

curious as to why there are 1 mill current mode buffer gets for 56K
rows. 
Since there are no indexes to be updated, is the referential integrity
and db trigger the main cause for this current gets ? If so can someone
explain please..

Waits are as follows..

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total
  Waited
  ----------------------------------------   Waited  ---------- 
  ------------
  SQL*Net more data from client                5269        0.00         
  0.17
  SQL*Net message to client                     586        0.00         
  0.00
  SQL*Net message from client                   586        2.80      
  1192.99
  db file sequential read                        10        0.00         
  0.02
  latch free                                      1        0.00         
  0.00

Any help in improving this insert would be appriciated..

thanks.

Sat
--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Other related posts: