Re: db file sequential read waits on INSERT into non-indexed partitioned table
Date: Tue, 27 Sep 2011 20:17:32 -0700
Message-ID: <CAGXkmitwYwSYpXhzgt6ZetzUSJ9DmoeNNM9+L+A=EhOdcEo0EA_at_mail.gmail.com>
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_at_wayneadams.com> 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-lReceived on Tue Sep 27 2011 - 22:17:32 CDT