RE: Any option in 19C for GTT to improve performance

If the predicate is on the join, then you only win with the composite index if you avoid the original table through the pruning join, grabbing the rowid of the first table and all the columns of the joined table you need, then join back to the original table by rowid.  

Whether or not that is a win depends on a lot. You’re talking about cell offloading, so that puts you on Exadata, right? Then you may also make use of zone maps.  

But without some careful engineering the single column index does sound better. I took you at your word there was predicate filtering on all three columns, which is a bit different than join matching.  

How is the joined to table indexed?  

mwf  

From: Andy Sayer [mailto:andysayer_at_gmail.com] Sent: Friday, June 02, 2023 5:13 PM
To: Pap
Cc: Mark W. Farnham; Oracle L
Subject: Re: Any option in 19C for GTT to improve performance  

The filters on those columns are coming from the join. You’re going to this table first so there’s no opportunity to use the filters.  

The join order looks fine, you get down to 2 rows after the cleverly pushed distinct and spend pretty much no time looping the other tables.  

I stand by my single column index suggestion.  

Thanks,

Andy  

On Fri, Jun 2, 2023 at 2:09 PM, Pap <oracle.developer35_at_gmail.com> wrote:

Thank you Andy.

In the second query all the three columns were used in the predicate section, so was thinking of composite one with fid as leading column , so as help first query index access path. Am I interpreting anything wrong here?  

On Sat, 3 Jun, 2023, 2:15 am Andy Sayer, <andysayer_at_gmail.com> wrote:

Hi Pap,  

Not sure where the composite index is coming from. The only index I would recommend based on the information you’ve shared is one purely on (fid). Or am I missing something?  

Cell offload and direct path reads are both pretty similar behaviour. They might kick in if it’s useful but also relying them for something criticism is not a good idea.  

Most of the time you do not need anything fancy. A single column index here will do the trick just fine. Try it out, if it improves these two statements but doesn’t make the whole process fast enough then you need to be looking at the process as a whole.  

Thanks,

Andy      

On Fri, Jun 2, 2023 at 1:34 PM, Pap <oracle.developer35_at_gmail.com> wrote:

Thank You so much Andy and Mark. So it looks like at a minimum, a new composite index with leading column fid i.e. on column(fid, eid,etyp) would give us a quick performance boost in this scenario.  

One interesting thing I saw, I got two different past executions of the same query from dba_hist_reports as below. In one case it took 4sec vs the other 48 seconds. While in the case of 4 seconds it scanned a lot higher volume of data.  

But in the slow run cases, I don't see any cell offloading percentage in slow run(~48 second run sql monitor). So does this mean we can't rely on the cell offloading feature always to see consistency performance? And is it true that, based on the volume of the data in the GTT, it's switching the decision , whether to go for cell smartscan or not, and it may vary release to release?  

https://gist.github.com/oracle9999/751bb4eb74501b87b99b1970bba2175f      

On Fri, Jun 2, 2023 at 7:18 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:

should be “minus the run time reduction of the queries”  

(Since you populate once and run many queries, probably this is a win.)  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham Sent: Friday, June 02, 2023 9:03 AM
To: oracle.developer35_at_gmail.com; 'Andy Sayer' Cc: 'Oracle L'
Subject: RE: Any option in 19C for GTT to improve performance  

To me that makes sense. I would also advocate testing any extra time to populate the GTT in the order fid,eid,etyp minus the run time of the queries.  

It can often be surprising the reduction in i/o required by index accesses when the cluster factor is “perfect” (which is WHY the CBO uses cluster factor as a tie breaker.)  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Pap Sent: Friday, June 02, 2023 1:44 AM
To: Andy Sayer
Cc: Oracle L
Subject: Re: Any option in 19C for GTT to improve performance  

Thank You Andy.
For the temp table F_GTT we already have an index on columns (eid,etyp). However the first query is just having a filter on FID, so correct me here, there is no other option but to have a new index created column FID. But in case of the second query there are all the columns used as joins/filters (eid,etyp,fid). So I was thinking of adding a new index on (fid,eid,etyp) to help both the queries. Hope this approach will help us.

And out of curiosity, in regards to the amount of physical reads for the GTT here in these queries i.e. 22GB for retrieving 27K rows. Is it correct to assume, these reads can't be further optimized by other techniques like caching the table or any other technique? Also the sql monitoring plan shows the majority of the time in the table scan of the F_GTT, and those to "CPU time" only. So does this mean it's not the IO(read/write) speed but CPU speed which is the bottleneck here, and thus it may not help by improving the read/write IO speed further by caching etc but we should concentrate in the direction of how to optimize the CPU time here. Is this understanding correct?

As per the current design it populates all the data in the GTT in one shot , and then traverses that one by one and does aggregation based on a certain set of input values and presents it.The input values of the FID are coming from a tool which does the aggregation and format it and present it. Yes I can imagine it's like a plsql loop in which the FIDs are passed one by one. But the third party tool is something we don't have much idea of and so currently investigating, how easily we can make those queries submit in batches or in oneshot rather in a loop fashion.  

Regards

Pap  

On Fri, Jun 2, 2023 at 6:07 AM Andy Sayer <andysayer_at_gmail.com> wrote:

Hi Pap,  

The monitor report tells us that you're having to do 22GB of physical reads against the table to retrieve 27K rows which get sorted in 11MB of memory. I think that's definitely a good use case of an index.  

Why is so much data going into the GTT if you're not reading all of it? Or is this a small chunk of some big PL/SQL loop?  

Thanks,

Andy  

On Thu, 1 Jun 2023 at 14:30, Pap <oracle.developer35_at_gmail.com> wrote:

Hello Experts,  

We are using version 19.15.0.0.0 of oracle. And we are having two queries on the global temporary table as below. The key problem is that as part of a process these queries are getting executed thousands of times and thus increasing the overall elapsed time of the job. We have a discussion going on to minimize the execution of these queries by passing the input bind values/literals into this query in batches or storing them in a global temporary table and thus hitting the query once. However, as that needs a bigger design change and will take time , we wanted to understand if any other quick changes can be done to make the query faster for individual execution?

From the sql monitor below it shows the majority of time is spent on scanning table F_GTT in full. And also we saw the filter on the column - FID is not a selective one, so even a new index on this column is not going to help the query. and Also the input value of this FID is coming as a literal and comes as a different value for each execution. As we are recently moved to 19C, are there any other features/option of caching or anything available for global temporary tables , which we can utilize in this scenario to have some quick improvements for individual execution , which will eventually make the overall execution of the job faster?  

Below is the sql query and the real time sql monitor associated with it. And it shows the majority of the time has been spent on scanning the table F_GTT in full in both the cases and also the time is ON CPU only.  

https://gist.github.com/oracle9999/b991022f72cd9fdd5776f797a187f7c1  

Regards

Pap

--
http://www.freelists.org/webpage/oracle-l