Any option in 19C for GTT to improve performance

From: Pap <oracle.developer35_at_gmail.com>
Date: Fri, 2 Jun 2023 02:59:35 +0530
Message-ID: <CAEjw_fib7CB5WTnRcEC8pcaTdN-eSwegb_Qiz+iAUqxX=g+74Q_at_mail.gmail.com>



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
Received on Thu Jun 01 2023 - 23:29:35 CEST

Original text of this message