Re: db file sequential read waits on INSERT into non-indexed partitioned table
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: work@xxxxxxxxxxxxxx
- Date: Tue, 27 Sep 2011 20:17:32 -0700
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:
