RE: db file sequential read waits on INSERT into non-indexed partitioned table

Just for completeness, I created another test table (range-hash, 128 SUBP)
specifying a large INITIAL/NEXT so that I would get 8MB extents.  I then ran
the tests again.  The results were practically identical to my other tests.

INSERT into range-hash (128 SUBP)

call     count       cpu    elapsed       disk      query    current
rows
------- ------  -------- ---------- ---------- ---------- ----------
----------
Parse        1      0.00       0.00          0          0          0
0
Execute      6      8.89      13.97       2572      53355     180107
50877
Fetch        0      0.00       0.00          0          0          0
0
------- ------  -------- ---------- ---------- ---------- ----------
----------
total        7      8.89      13.97       2572      53355     180107
50877

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total
Waited
  ----------------------------------------   Waited  ----------
------------
  db file sequential read                      2570        0.04
4.61
  gc current grant 2-way                       1495        0.03
0.45
  gc cr grant 2-way                              17        0.00
0.00
  enq: FB - contention                          216        0.00
0.12
  gc current multi block request                439        0.00
0.16
  enq: TT - contention                            5        0.00
0.00
  gc current grant busy                           2        0.00
0.00
****************************************************************************
****

INSERT into range (no SUBP)

call     count       cpu    elapsed       disk      query    current
rows
------- ------  -------- ---------- ---------- ---------- ----------
----------
Parse        1      0.00       0.00          0          0          0
0
Execute      6      3.03       3.17         99      10218      48616
50877
Fetch        0      0.00       0.00          0          0          0
0
------- ------  -------- ---------- ---------- ---------- ----------
----------
total        7      3.03       3.17         99      10218      48616
50877

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total
Waited
  ----------------------------------------   Waited  ----------
------------
  db file sequential read                        99        0.00
0.07
  enq: FB - contention                          194        0.00
0.08
  buffer busy waits                               1        0.00
0.00
  KJC: Wait for msg sends to complete             1        0.00
0.00
****************************************************************************
****


Wayne

-----Original Message-----
From: Wayne Adams [mailto:work@xxxxxxxxxxxxxx] 
Sent: Thursday, September 29, 2011 12:08 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: db file sequential read waits on INSERT into non-indexed
partitioned table

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


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


Other related posts: