Re: Difference in temp table insert performance
Date: Fri, 7 Jan 2022 21:17:25 +0000
Message-ID: <CAGtsp8kSOzOC=MY=m5kW5w2-1ryTyXcNw-mKEMD17+LpSUF9Qg_at_mail.gmail.com>
The large number of combined reads and writes to temp on in a LOAD operation suggest two possibilities (though I've not seen the pattern before).
- maybe the statistics are actually being reported in the wrong place in the plan for some reason
- possibly the code is doing something with LOB columns - perhaps if it was calling a function in the select list that concatenates a number of values to construct a LOB value this is one of the side effects.
Look at the SQL, the column definitions, and the projection information - that might give you some clues.
BTW - what is the FILTER operation doing ?
Regards
Jonathan Lewis
On Fri, 7 Jan 2022 at 17:15, Pap <oracle.developer35_at_gmail.com> wrote:
> Hello Listers, Its version 11.2.0.4 of Oracle. And is planned to move to
> 19C soon.
>
> For one of the third party applications , we see direct path insert into
> the global temporary temp table is taking significant time. Below is sql
> monitor from two of the queries , both of them are loading data into global
> temporary tables and in the first case it's inserting ~500million and in
> second case it's inserting ~700million rows. But what we see is even the
> first case global temporary table holds no indexes in it , it's still
> showing ~84% of the activity in the data load step(plan_line_id - 1). And
> the activity section in the sql monitor showing significant time samples
> for 'direct path read temp' and 'direct path write temp'.
>
> In the second case it's inserting ~747million rows but is not spending
> that much time in the data load part i.e. plan_line_id-1 and also i am not
> seeing those 'direct path read temp' and 'direct path write temp' samples
> there. Even this global temporary has 3- indexes in it.
>
> So we wanted to understand what must be causing this ? and if we could
> make the data load into the global temporary table faster in the first
> case? In the second case I understand it's the HASH join part where we are
> spending a lot of time as it spills to temp and we may not have much option
> at hand but we were expecting at least the data load should not take this
> amount of time.
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 07 2022 - 22:17:25 CET