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@xxxxxxxx> 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@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Mark W. Farnham
*Sent:* Friday, June 02, 2023 9:03 AM
*To:* oracle.developer35@xxxxxxxxx; '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@xxxxxxxxxxxxx [
mailto:oracle-l-bounce@xxxxxxxxxxxxx ;<oracle-l-bounce@xxxxxxxxxxxxx>] *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@xxxxxxxxx> 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@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
