RE: db file sequential read waits on INSERT into non-indexed partitioned table
- From: "Wayne Adams" <work@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Sep 2011 00:08:11 -0700
Thanks for the response Gaja.
In regards to the extents, I'm preloading the tables with 500k before
running my tests. By the time the preload is done, the tables have already
jumped up to 8MB extents. So, for purposes of my testing, it should be the
same as if I had created with the 8MB extents to start with.
I agree with your comments about LIO. When I'm doing query perf tuning,
that always plays a big factor. What surprised me was the number of LIO's
required to handle the hash SUBPARTITION INSERTS. When doing research on
performance implications of partitioning, I have yet to find any reference
that talks about this kind of INSERT performance degradation (could be I'm
just not doing the right keyword search...).
As for the GTT, I could see where that might be useful for queries. For
INSERTS, I did actually do a test INSERT into a GTT first and then tried
INSERT INTO XXXXXXX SELECT FROM GTT. The INSERT into the GTT was fast (2 -
3 sec for 50k rows), but the subsequent INSERT into the partitioned table
had the same performance as if I had not used the GTT. So, no perf benefit
for using GTT in my case.
Wayne
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=--
Hi Wayne,
Just wondering if you perform your tests by creating the table with a large
extent size such as 8MB as Greg had originally suggested? If not, it may be
worthwhile to do the tests again. Here is the trend in the numbers I
observe:
Sub-Partitions CPU(in secs)QUERYCURRENTTOTAL LIO
1288.9653964180790234,754
87.7347728162319210,047
04.6210485 4829558,780
The "current" numbers in the trace output are rather high when there are
sub-partitions (possibly for segment management) and extent size could play
a
part here. So, it may be worthwhile to take Greg's advice on this and
re-test. Also, one can observe in this case, there is a direct correlation
between CPU time and LIO (query+current).
Moral of this story - Performance Management is about LIO Management. When
you
optimize the LIO, the CPU numbers automatically reflect the optimization.
With
regards to your question with GTTs, the concept is still the same - LIO
Management. In a prior project, the offending SQL with a 1000 bind variables
was a SELECT and it was easy to populate a GTT with the bind variable values
and then hang a sub-query off the original SELECT to read the populated GTT.
It
dropped LIO down to nothing and with it the CPU numbers.
Given that the GTT lives in the PGA until it overflows into the TEMP
tablespace, LIOs (or the resemblance of it) that occur in the PGA are
usually
super-fast and low on CPU, as there is no buffer cache management in the
picture. If this table's function in the application is that of a "staging
table" and is used for some sort of pre-loading operation, I'd give the GTT
(with ON COMMIT PRESERVE ROWS) a shot, just for the heck of it. To ensure
that
there is no spillage to the TEMP tablespace, do your tests by setting
WORKAREA_SIZE_POLICY to MANUAL in the session, and set a reasonable number
for
SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE (keep them the SAME value, say
128MB
or so) and do your tests. Keep us posted.
Cheers,
Gaja
-----Original Message-----
From: Wayne Adams [mailto:work@xxxxxxxxxxxxxx]
Sent: Wednesday, September 28, 2011 6:32 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Cc: 'Greg Rahn'
Subject: RE: db file sequential read waits on INSERT into non-indexed
partitioned table
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:
