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).
a) maybe the statistics are actually being reported in the wrong place in
the plan for some reason
b) 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@xxxxxxxxx> 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.
