If you want to find out what your version of 19c does in these
circumstances you need to do the test yourself. (Otherwise you could pay me
to do the test for you, of course)
The differences in the effects of append (or not) for the GTT may not be
terribly important as you change; but I think there may be some possible
benefit in enhancements in the use of flash cache when you upgrade: you may
not be able to reduce the number of write and reads to temp but you may be
able to configure 19c to make them faster. (Left as exercise because I
can't do the necessary testing)
If the problem is the temp read/write for sorting (enable events 10032 and
10033 to check before running the procedure) then you may be able to
eliminate some of the I/O by forcing larger memory allocations - but at
700M rows to index you may still be unable to avoid temp I/O. (The 10032
will also tell you if you do 3 x 700M rows sorts) or a single 2100M row
sort; the 10033 will tell you about "sort runs" being written to disc, and
"merges" from disk which would be reads.)
You could always work on options for reducing the number of indexes - it's
possible that you don't need all three.
As far as the TSFE object id being reported for the operation which should
be the load as select, I wouldn't spend too much time worrying about it.
There are other cases where the object id, or various other columns from
v$session (which is often a source for v$active_session_history) are not
updated, or are not updated exactly when you might expect, so that attempts
to produce reports produce misleading resuilts.
Regards
Jonathan Lewis
On Sat, 8 Jan 2022 at 16:51, Pap <oracle.developer35@xxxxxxxxx> wrote:
Thank you so much Jonathan. As always, great detail.
You are correct. The INSERT /*+APPEND*/ is used here for the data load
into the global temporary table and GTT being created as 'on commit
preserve rows'. And this insert query which i had posted is the first time
population which means the table is populated from an empty stage.
As we are currently on Oracle version 11.2.0.4 with Exadata X8 and are
planning to move to 19C soon. So curious to know regarding the points you
stated i.e. how the append hint behavior will vary with versions and how
the presence or absence of data on GTT will have an effect on 'on commit
preserve type table load? Can you please explain this a bit more ?
And as you rightly pointed out, the storage intelligence of the exadata is
not getting applied here because if the table is a global temporary table
it would have benefited more from the real table. But I think we currently
have some design constraints like the same data load sql process is running
from multiple sessions for multiple customers at same time. So thinking of
the possible options at hand here , is the only option to make the
data load faster here is, parallel hint or dropping indexes from the global
temp table?
And also Jonathan, another point , i was still seeing in
dba_hist_active_sess_history , if i group the sample by sql_plan_line_id,
event , current_obj# the top most sample count appears on
sql_plan_line_id-1, and its pointing to the object TSFE which is not
actually the global temporary table that is getting loaded rather its the
one which is getting full scanned on plan_line_id-8.
