Your later comment about execution time varying down to as little as 4
seconds is interesting - it suggests that a large volume of the data was
discarded through storage indexes, but I didn't think Exadata created
storage indexes on temporary tablespaces.
If I've understood your description, you populate a "very large" GTT, and
then run a pair of queries against that GTT for each distinct value of FID,
and want to find either
1) a way to make each execution significantly faster
or
2) re-engineer your code so that you don't end you doing two big tablescans
for each FID.
Inevitably option (1) is more desirable from the point of view of
complexity, cost and risk.
I don't think I've seen anything that tells us how many rows are in the
GTT, or how widely that number of rows might vary (which may have something
to do with getting times of 4 seconds). A common feature of the two
queries, though, is that from a few million rows you're selecting and
aggregating a few 10''s of thousands down to a result of just a couple of
rows; and that it the critical pointer to a fairly simple type of solution.
Given the number of columns summed in the first query an index on JUST FID
looks like a good idea, especially if you can populate the table in order
of FID (as suggested by Mark).
For the second query your thought of creating an index on (fid, eid, etyp)
isn't good enough as you would need to include DCD in the index if you want
to avoid visiting the table (which might be a good strategy). Notice,
however, that Andy pointed out the optimizer's use of "group by placement"
(vw_dtp_xxxxx) to pre-aggregate 27,000 rows down to 2 rows before joining.
With that clue I would investigate the correctness of creating a second GTT
which is a "select distinct" of fid, eid, etyp, and dcd - it looks as if
this might produce a table about 10,000 smaller than the main GTT, which
could then be used in the query.
A follow-on from the idea of the summary GTT for the second query is that
you could try exactly the same type of strategy for the first query. After
populating the main GTT populate a summary GTT which aggregates the main
GTT just once to produce the entire result set, then change the first query
to pick its results from the summary GTT.
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
