Re: TKPROF output -- high current gets for INSERT
- From: sat0789@xxxxxxxxxxx
- To: "Wolfgang Breitling" <breitliw@xxxxxxxxxxxxx>
- Date: Thu, 30 Dec 2004 14:02:01 -0800
The tablespace is non-assm...oracle version is 9.2.0.5.0 64 bit ..the
transaction is pure inserts into fact table with lookups from dimension
tables to get their keys....no delete operation happens in this
transaction..
In our case fk bitmap indexes are made unusable before the load and we
rebuild them after its done.
The insert ETL stmt ran for 20 hours..total rows inserted was around 5
mill..avg row length is 250 bytes. block size is 16k. I think 20 hours
is tooo much. nothing else is running on the system other than the 4
concurrent inserts into the same partition.
Thanks,
Sat
On Thu, 30 Dec 2004 12:56:42 -0700, "Wolfgang Breitling"
<breitliw@xxxxxxxxxxxxx> said:
> From the "Elapsed times include waiting on following events" I conclude
> that this is (at least) Oracle 9i, but what Release/patch level exactly?
> When you are saying "I have freelists and initrans set appropriately",
> can
> we assume that the table is in a non-ASSM tablespace?
> A client of mine has a similar situation where an insert takes an
> inordinate amount of time, associated with large amounts of current gets.
> It got extremely bad when they hit the ASSM bug in 9.2.0.5 so the
> affected
> tables were hastily moved to a non-ASSM tablespace.
> Even with the tables in the non-ASSM tablespace the current gets / row
> ratio is ~30 (yours is "only" ~20). The inserts there are simple "insert
> ..
> select" and I've come to blame the high current get counts on the fact
> that
> a similar number of rows gets deleted from the table as are eventually
> re-inserted without a commit in between (which incidentally is the recipe
> for the 9.2.0.5 ASSM bug).
> Do you have a similar situation where rows are also deleted in the same
> transaction, or are they pure additions?
>
> At 01:09 AM 12/30/2004, sat0789@xxxxxxxxxxx wrote:
> >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
> >--
> >//www.freelists.org/webpage/oracle-l
>
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> http://www.centrexcc.com
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
