Re: How to fix cache buffer chain issue
Date: Mon, 28 Jun 2021 00:07:44 +0530
Message-ID: <CAEjw_fjov4OeVCBLQd0eJCrhEQKg4dd=f_oBb3694fF1n=BqmQ_at_mail.gmail.com>
Thank you very much Jonathan and Andrew.
I had modified the query as per Jonathan's suggestion and tested by converting the first four UNION clauses. The results matching and also the modified query access path does show only one time access to the table/indexes of TAD/TAD_Ix2. Attached is the modified query with its sqlmonitor.
Andrew, for my understanding here, I think you got the HASH JOIN access of TAD clue from below plan, but i am still not able to understand how this path suggests the HASH JON access of TAD, as because line 9 and 10 shows two nested loops and HASH Join is in third position in the tree. So I am assuming line 9- i.e first nested loop JOIN is between TNI and TMTD and line 10 is for the join between TAD and the result set out of TNI JOIN TMTD. So doesn't it mean that table TAD is also accessed in a nested loop path here? Please guide me here. However, as you suggested, I simply tried the option i.e. forcing a HASH Join path through profile , for all SELECT queries. This way too the query is giving us results in less than minutes as opposed to ~15-20minutes with the all NESTED LOOP access path. And to answer your earlier question, here table TNI is a global temporary table and XX is a literal used in the query as filter, but yes as it's a global temporary table we are relying on dynamic sampling here and sometimes it goes off quite a bit more than actual cardinality and that is possibly causing different execution path.
| Id | Operation | Name
| Rows | Bytes | Cost (%CPU)| Time |
| 0 | INSERT STATEMENT |
| | | 12967 (100)| |
| 1 | LOAD TABLE CONVENTIONAL |
| | | | |
| 2 | SORT UNIQUE |
| 18 | 1548 | 12967 (1)| 00:02:36 |
| 3 | UNION-ALL |
| | | | |
| 4 | HASH GROUP BY |
| 1 | 86 | 738 (1)| 00:00:09 |
| 5 | NESTED LOOPS |
| 1 | 86 | 736 (1)| 00:00:09 |
| 6 | NESTED LOOPS |
| 1 | 86 | 736 (1)| 00:00:09 |
| 7 | NESTED LOOPS |
| 1 | 79 | 735 (1)| 00:00:09 | |* 8 | HASH JOIN | | 1 | 60 | 734 (1)| 00:00:09 |
| 9 | NESTED LOOPS |
| 12 | 384 | 26 (0)| 00:00:01 |
| 10 | NESTED LOOPS |
| 12 | 384 | 26 (0)| 00:00:01 | |* 11 | TABLE ACCESS STORAGE FULL | TNI | 12 | 192 | 2 (0)| 00:00:01 | |* 12 | INDEX UNIQUE SCAN | MTD_PK | 1 | | 1 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | TMTD
| 1 | 16 | 2 (0)| 00:00:01 | *|* 14 | TABLE ACCESS BY INDEX ROWID | TAD | 14 | 392 | 708 (1)| 00:00:09 ||* 15 | INDEX RANGE SCAN | TAD_IX2 | 28797 | | 116 (0)| 00:00:02 || 16 | SORT AGGREGATE | | 1 | 8 | | || 17 | INDEX FULL SCAN (MIN/MAX) | TAD_IX2 | 1 | 8
| 3 (0)| 00:00:01 |*
| 18 | TABLE ACCESS BY INDEX ROWID | TMMC
| 1 | 19 | 1 (0)| 00:00:01 | |* 19 | INDEX RANGE SCAN | TMMC_IX1 | 1 | | 0 (0)| | |* 20 | INDEX UNIQUE SCAN | MCT_PK | 1 | | 0 (0)| |
| 21 | TABLE ACCESS BY INDEX ROWID | TMCT
| 1 | 7 | 1 (0)| 00:00:01 |
| 22 | HASH GROUP BY |
| 1 | 86 | 717 (1)| 00:00:09 |
Predicate Information (identified by operation id):
8 - access("TMTD"."PMENT"="TAD"."PMENT") 11 - storage("TNI"."NE"='XX')
filter("TNI"."NE"='XX')
12 - access("TNI"."NID"="TMTD"."DID")
14 - filter(((NVL(:B1,'ZZZZ')='ZZZZ' OR ("TAD"."MCD"=:B1 AND
NVL(:B1,'ZZZZ')<>'ZZZZ')) AND
TRUNC(:B2)-"ADT"<=1))
15 - access("TAD"."DT_CR"=)
19 - access("TAD"."MCD"="TMMC"."PTMCD") 20 - access("TMTD"."CID"="TMCT"."SWCTID")
Regards
Pap
On Thu, Jun 24, 2021 at 3:34 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:
> It's up to you to check the validaty of my guess regarding the nature of > the query simply breaking a data set for a given date by different ranges > then the basis of the mechanism is to take the predicate (list) that breaks > the data set into pieces and combine the list in a CASE expression that > generates a value that can be used as an extra grouping column, along the > lines of: > > select > case > when adt - to_date(:b1,'yyyy-mm-dd') <= 1 > then 'g1' > when adt - to_date(:b1,'yyyy-mm-dd') > 1 and adt - > to_date(:b1,'yyyy-mm-dd') <= 2 then 'g2' > when adt - to_date(:b1,'yyyy-mm-dd') > 2 and adt - > to_date(:b1,'yyyy-mm-dd') <= 7 then 'g3' > when adt - to_date(:b1,'yyyy-mm-dd') > 7 > then 'g4' > end flag, > mcd, > sum(value_col), count(*) > from > v1 > group by > case > when adt - to_date(:b1,'yyyy-mm-dd') <= 1 > then 'g1' > when adt - to_date(:b1,'yyyy-mm-dd') > 1 and adt - > to_date(:b1,'yyyy-mm-dd') <= 2 then 'g2' > when adt - to_date(:b1,'yyyy-mm-dd') > 2 and adt - > to_date(:b1,'yyyy-mm-dd') <= 7 then 'g3' > when adt - to_date(:b1,'yyyy-mm-dd') > 7 > then 'g4' > end, > mcd > order by > flag, mcd > / > > > Regards > Jonathan Lewis > > On Tue, 22 Jun 2021 at 19:16, Pap <oracle.developer35_at_gmail.com> wrote: > >> Thanks much Jonathan. >> >> Trying to visualize/understand the code modification which you suggested. >> Agreed that the difference in the query is just the date criteria filter on >> column ADT of table TAD. But currently we are fetching sum(amt) and >> count(*) based on the grouping column in each of those queries. >> >> >>
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Jun 27 2021 - 20:37:44 CEST
- text/plain attachment: list_modified_query_sqlmonitor.txt