Re: Difference in temp table insert performance

From: Jonathan Lewis <jlewisoracle_at_gmail.com>
Date: Sat, 8 Jan 2022 17:59:52 +0000
Message-ID: <CAGtsp8=xnGrqoTdnMaKk9Z15e-uO+H8SGLCdKBbCQPfLTsE0Zw_at_mail.gmail.com>



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_at_gmail.com> 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.
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Sat Jan 08 2022 - 18:59:52 CET

Original text of this message