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

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_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