RE: db file sequential read waits on INSERT into non-indexed partitioned table
- From: "Wayne Adams" <work@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 28 Sep 2011 18:32:13 -0700
Thanks for the help guys! After reading the explanations here, I decided to
try a test. I created 2 identical tables (again with no indexes). One with
8 SUBPARTITONS, one with NO SUBPARTITIONS (range instead of range-hash) and
did my INSERTs. The difference was pretty impressive (and to me personally,
surprising).
INSERT into range-hash partitioned table (128 SUBPARTITIONS), no indexes
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0
0
Execute 6 8.96 14.00 3738 53964 180790
50877
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 7 8.96 14.00 3738 53964 180790
50877
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
---------------------------------------- Waited ----------
------------
db file sequential read 3726 0.05
4.77
gc current grant 2-way 1990 0.02
0.55
gc cr grant 2-way 70 0.00
0.01
gc current multi block request 288 0.01
0.19
enq: FB - contention 230 0.00
0.09
enq: TT - contention 56 0.00
0.02
row cache lock 81 0.00
0.01
****************************************************************************
****
INSERT into range-hash partitioned table (8 SUBPARTITIONS), no indexes
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0
0
Execute 6 7.73 8.82 1110 47728 162319
50877
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 7 7.73 8.83 1110 47728 162319
50877
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
---------------------------------------- Waited ----------
------------
gc current grant 2-way 471 0.00
0.12
db file sequential read 1106 0.02
0.93
gc cr grant 2-way 6 0.00
0.00
gc current multi block request 334 0.00
0.18
enq: FB - contention 290 0.00
0.08
gc cr block 2-way 1 0.00
0.00
enq: HW - contention 3 0.00
0.00
row cache lock 2 0.00
0.00
****************************************************************************
****
INSERT into range partitioned table (no SUBPARTITIONS), no indexes
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0
0
Execute 6 4.62 5.02 276 10485 48295
50877
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 7 4.62 5.02 276 10485 48295
50877
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
---------------------------------------- Waited ----------
------------
db file sequential read 275 0.00
0.15
enq: FB - contention 194 0.01
0.17
enq: HW - contention 3 0.00
0.00
row cache lock 2 0.00
0.00
gc current block 2-way 1 0.00
0.00
****************************************************************************
****
Intuitively, you would expect some overhead for having the subpartitions,
but I did not expect quite that much difference (1/2 the CPU, and 1/3 the
elapsed time with no SUBPARTITIONS vs the 128).
We may have to revisit whether we need the SUBPARTITIONS at all (we don't
reference the SUBPARTITION key in our queries much).
Wayne
-----Original Message-----
From: Greg Rahn [mailto:greg@xxxxxxxxxxxxxxxxxx]
Sent: Tuesday, September 27, 2011 8:18 PM
To: work@xxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: db file sequential read waits on INSERT into non-indexed
partitioned table
Understand that "db file sequential read" just means its not a multi-block
read - it's a single block read. Most any time that a single block needs to
be retrieved, this event shows up. There are numerous reasons for this:
file header block access, space management (bitmap blocks), etc, etc.
As I mentioned, it would appear that this event showed up because of extent
allocation. Simply put, when doing bulk inserts, the larger the extent, the
less frequent the need to get a new extent.
Especially important when doing bulk inserts into numerous segments (e.g.
128 hash subpartitions).
The rest of the stuff you mention doesn't really matter here that I see.
On Tue, Sep 27, 2011 at 3:57 PM, Wayne Adams <work@xxxxxxxxxxxxxx> wrote:
> Thanks for the feedback guys!
>
> To elaborate, this particular scenario and discussion is partly out of
> curiosity (myself trying to understand the "db file sequential read"
> on INSERT with no indexes), and partly as a pre-cursor to putting
> forth a more concrete INSERT performance problem example very soon.
>
> To provide some additional info.
>
> 1) All of these inserts were into the same RANGE partition (we
> partition by month), and the test/trace was done after having just
inserted 450k rows.
> The table has already been dropped, so I can't tell the extent size at
> the end, but it probably wasn't 64k.
> 2) The table never has any updates or deletes, only inserts.
> 3) The table does have the DEGREE set to 4, but we did not issue the
> "alter session force parallel dml" clause, so I didn't think it would
> do any concurrent inserts. Am I wrong in that?
>
> I am also puzzled by the high CPU time for the insert. In this
> example, it's 75% of the total elapsed time. I have never heard of a
> high number of bind variables in an INSERT causing high CPU. The data
> being passed to the insert has just been finished being massaged by a
> bunch of PL/SQL code, so I'm not sure how would using a GTT in this
> example help. You would have to use the binds to insert into the GTT too
wouldn't you?
--
Regards,
Greg Rahn
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
