I've written up a blog note adding a little to my earlier reply:
Case Study | Oracle Scratchpad (wordpress.com)
<https://jonathanlewis.wordpress.com/2023/06/13/case-study-9/>
Regards
Jonathan Lewis
On Thu, 1 Jun 2023 at 22:30, Pap <oracle.developer35@xxxxxxxxx> 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
