The question “How do you trouble-shoot a performance problem” came up in an online session I did for the AIOUG (All-India OUG) today. It’s a very broad question and the only possible answers are either extremely generic, or very specific – so here’s a specific example that I talked about to give some idea of the processes I go through. It’s an example from the Oracle-l list server asking the (paraphrased) question:
I’ve got a parallel query that take 24 seconds to return 2,500 rows for 2018. .The same query for 2019 should return a similar amount of data but consumes a lot of TEMP space before failing; it takes 45 minutes to complete if I remove the parallel hint. The only difference between the two queries is a change to a single predicate: “opclf.year_number = to_number(‘YYYY’)” and the statistics are up to date – what’s going on?
The ease of diagnosing a problem is dependent on the ease of access to all the right information, and you don’t always know initially what the right information might be. In this case the OP had started by posting to github the SQL Monitor reports for the two queries, which were automatically generated since the queries were using parallel execution.
Click here to show/hide the first few sections for the 2019 output
SQL Text
------------------------------
SELECT /*+ PARALLEL(8) */ DISTINCT rd.document_id AS doc_id ,'firm_id' AS criteria_key ,opf.ultimate_parent_firm_id AS series_id ,period_number FROM ( SELECT /*+ PARALLEL(8) */ DISTINCT rd.document_id ,rd.client_role_id ,( CASE WHEN MONTHS_BETWEEN(TO_DATE('04/28/2022', 'MM/DD/YYYY'), TO_DATE('04/01/2017', 'MM/DD/YYYY')) > 12 THEN TRUNC(dc.date_value, 'YEAR') ELSE TRUNC(dc.date_value, 'MONTH') END ) period_number FROM REPORT_ENGINE.date_code dc ,REPORT_ENGINE.lit_fact_bd rd INNER JOIN
report_engine.firm FIRM ON rd.firm_id = FIRM.firm_id WHERE dc.date_value BETWEEN TO_DATE('04/01/2017', 'MM/DD/YYYY') AND TO_DATE('04/28/2022', 'MM/DD/YYYY') AND rd.publication_date_id = dc.date_id AND rd.year_number = to_number('2019') AND (FIRM.ultimate_parent_firm_id IN (to_number('252094'))) ) rd INNER JOIN report_engine.opposing_counsel op ON rd.client_role_id = op.legal_role_id INNER JOIN report_engine.lit_fact_bd opclf ON opclf.document_id = rd.document_id AND op.opposing_counsel_role_id
= opclf.client_role_id AND opclf.year_number = to_number('2019') INNER JOIN report_engine.firm opf ON opclf.firm_id = opf.firm_id AND opf.firm_id >= 1000 WHERE 1 = 1
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Session : SYS (647:28741)
SQL ID : 3cjg20q2vw41f
SQL Execution ID : 16777216
Execution Started : 06/09/2022 05:08:24
First Refresh Time : 06/09/2022 05:08:25
Last Refresh Time : 06/09/2022 05:08:27
Duration : 221s
Module/Action : sqlplus@c111dhw (TNS V1-V3)/-
Service : SYS$USERS
Program : sqlplus@c111dhw (TNS V1-V3)
Global Stats
====================================================================
| Elapsed | Cpu | IO | Other | Buffer | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
====================================================================
| 222 | 179 | 28 | 15 | 49M | 11624 | 2GB |
====================================================================
Parallel Execution Details (DOP=8 , Servers Allocated=16)
=====================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Other | Buffer | Write | Write | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | (sample #) |
=====================================================================================================================================
| PX Coordinator | QC | | 0.26 | 0.26 | | 0.00 | 12144 | | . | |
| p000 | Set 1 | 1 | | | | | | | . | |
| p001 | Set 1 | 2 | | | | | | | . | |
| p002 | Set 1 | 3 | | | | | | | . | |
| p003 | Set 1 | 4 | | | | | | | . | |
| p004 | Set 1 | 5 | | | | | | | . | |
| p005 | Set 1 | 6 | | | | | | | . | |
| p006 | Set 1 | 7 | | | | | | | . | |
| p007 | Set 1 | 8 | | | | | | | . | |
| p008 | Set 2 | 1 | 220 | 177 | 28 | 15 | 48M | 11624 | 2GB | direct path write temp (28) |
| p009 | Set 2 | 2 | | | | | | | . | |
| p010 | Set 2 | 3 | | | | | | | . | |
| p011 | Set 2 | 4 | 1.71 | 1.70 | | 0.01 | 595K | | . | |
| p012 | Set 2 | 5 | | | | | | | . | |
| p013 | Set 2 | 6 | | | | | | | . | |
| p014 | Set 2 | 7 | | | | | | | . | |
| p015 | Set 2 | 8 | | | | | | | . | |
=====================================================================================================================================
You’ll note that I haven’t got as far as the body of the execution plan yet, and I’ve highlighted line 44 – a line in the middle of the summary of activity for the parallel execution processes. There are 8 servers in each of two sets (we’re running parallel 8) and the line I’ve highlighted is the first server of the second set. The reason I’ve highlighted it is that it’s the one server that’s doing almost all the work – effectively the query (at some point in the plan) is running serially.
So, a first thought, maybe we’ve just been unlucky and running parallel 7 (say) would result in a better distribution of data across parallel servers and allow each of 7 processes to do whatever they had to do to a much smaller amount of data. Maybe a change to the distribution method (pq_distribute() hint) would spread the workload more evenly. In either case “N” smaller workload might still use a lot of TEMP, but possibly no individual process would fail, and the job would complete nearly N times as quickly.
Note: the 2018 Monitor report showed an equivalent skew in the data distribution, but the amount of work needed was much smaller with a read/write load of only 143MB compared to 2GB for the 2019 report. The OP did try running parallel 7, but with no change to the overall effect.
Let’s take a quick glance at the plan body (click to view/hide)
SQL Plan Monitoring Details (Plan Hash Value=1862006233) ========================================================================================================================================================================================================================= | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Write | Write | Mem | Temp | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | | (%) | (# samples) | ========================================================================================================================================================================================================================= | 0 | SELECT STATEMENT | | | | | | 3 | | | | | | | | | 1 | PX COORDINATOR | | | | | | 3 | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10003 | 781 | 153 | | | | | | | | | | | | 3 | HASH UNIQUE | | 781 | 153 | | | | | | | | | | | | 4 | PX RECEIVE | | 781 | 151 | | | | | | | | | | | | 5 | PX SEND HASH | :TQ10002 | 781 | 151 | | | 2 | | | | | | | | | -> 6 | BUFFER SORT | | 781 | 153 | 219 | +3 | 2 | 0 | 11624 | 2GB | 61M | 2G | 26.21 | Cpu (26) | | | | | | | | | | | | | | | | direct path write temp (28) | | -> 7 | NESTED LOOPS | | 781 | 151 | 219 | +3 | 2 | 39M | | | | | | | | -> 8 | NESTED LOOPS | | 781 | 151 | 219 | +3 | 2 | 39M | | | | | 0.49 | Cpu (1) | | -> 9 | NESTED LOOPS | | 777 | 44 | 219 | +3 | 2 | 39M | | | | | | | | -> 10 | NESTED LOOPS | | 41 | 26 | 219 | +3 | 2 | 6463 | | | | | | | | -> 11 | HASH JOIN | | 41 | 21 | 219 | +3 | 2 | 6463 | | | 6M | | | | | 12 | BUFFER SORT | | | | 1 | +3 | 2 | 36855 | | | | | | | | 13 | PX RECEIVE | | 87 | 19 | 1 | +3 | 2 | 36855 | | | | | | | | 14 | PX SEND HASH | :TQ10000 | 87 | 19 | 1 | +3 | 1 | 38694 | | | | | | | | 15 | NESTED LOOPS | | 87 | 19 | 1 | +3 | 1 | 38694 | | | | | | | | 16 | NESTED LOOPS | | 87 | 19 | 1 | +3 | 1 | 38694 | | | | | | | | 17 | TABLE ACCESS BY INDEX ROWID | FIRM | 1 | 2 | 1 | +3 | 1 | 43 | | | | | | | | 18 | INDEX RANGE SCAN | FIRM_ULT_PARENT_FIRM_IDX1 | 1 | 1 | 1 | +3 | 1 | 43 | | | | | | | | 19 | PARTITION RANGE SINGLE | | | | 1 | +3 | 43 | 38694 | | | | | | | | 20 | BITMAP CONVERSION TO ROWIDS | | | | 1 | +3 | 43 | 38694 | | | | | | | | 21 | BITMAP INDEX SINGLE VALUE | LIT_FACT_BD_IDX09 | | | 1 | +3 | 43 | 49 | | | | | | | | 22 | TABLE ACCESS BY LOCAL INDEX ROWID | LIT_FACT_BD | 63 | 19 | 3 | +1 | 38694 | 38694 | | | | | 0.49 | Cpu (1) | | 23 | PX RECEIVE | | 20 | 2 | 1 | +3 | 2 | 2 | | | | | | | | 24 | PX SEND HASH | :TQ10001 | 20 | 2 | | | | | | | | | | | | 25 | PX BLOCK ITERATOR | | 20 | 2 | | | | | | | | | | | | 26 | TABLE ACCESS FULL | OPPOSING_COUNSEL | 20 | 2 | | | | | | | | | | | | -> 27 | TABLE ACCESS BY INDEX ROWID | DATE_CODE | 1 | | 219 | +3 | 6465 | 6463 | | | | | | | | -> 28 | INDEX UNIQUE SCAN | PK_DATE_CODE | 1 | | 219 | +3 | 6465 | 6465 | | | | | | | | -> 29 | PARTITION RANGE SINGLE | | 19 | | 219 | +3 | 6465 | 39M | | | | | | | | -> 30 | TABLE ACCESS BY LOCAL INDEX ROWID | LIT_FACT_BD | 19 | | 220 | +2 | 6465 | 39M | | | | | 35.92 | Cpu (74) | | -> 31 | INDEX RANGE SCAN | LIT_FACT_BD_IDX20 | 1 | | 219 | +3 | 6465 | 39M | | | | | 9.22 | Cpu (19) | | -> 32 | INDEX UNIQUE SCAN | PK_FIRM | 1 | | 219 | +3 | 39M | 39M | | | | | 10.68 | Cpu (22) | | -> 33 | TABLE ACCESS BY INDEX ROWID | FIRM | 1 | | 219 | +3 | 39M | 39M | | | | | 16.99 | Cpu (35) | ===================================================================================================================================================================
You can see from the “->” symbols at the left hand side of the plan that this report was generated while the plan was still running. The thing that leaps out as you glance down the page is the value in the “Rows (Actual)” column at operations 7-9 (which show the rowsources generated by some nested loop joins) and operations 29, 32 and 33 of the plan that tell us something about how those rowsources were generated.
Operation 29 has executed (Execs) 6,465 so far, producing a total of 39M rows, and operations 32 and 33 have both executed 39M times each producing a total of 39M rows by index unique scan.
The plan for the 2018 data was similar though the join order for DATE_CODE, LIT_FACT_BD and FIRM was different (and it was the join to LIT_FACT_BD that increased the row count dramatically – so hinting it to be the last table in the join might help a bit), but the largest rowcount for the 2018 query was only 3M rows, not the 39M that had appeared after only 6,465 rows of a possible driving 39,855 in the 2019 query.
So it’s not surprising that the query could take much longer for 2019. It’s not the volume of output that matters, it’s the volume of input (or, more accurately, throughput or intermediate) data that matters.
Let’s think about that volume, though: the 2018 plan generated 3M rows and then crunched them down to 2,500 rows and the 2019 plan was supposed to produce a similar sized output (from 39M+ rows). Could we rewrite the query in some way that made it do some intermediate aggregation so that the volume of data to be aggregated was never enormous?
Let’s take a look at the plan from the 2018 Monitor report (click to show/hide)
SQL Plan Monitoring Details (Plan Hash Value=472871521) ======================================================================================================================================================================================================================================= | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) | ======================================================================================================================================================================================================================================= | 0 | SELECT STATEMENT | | | | 1 | +24 | 17 | 2613 | | | | | | | | | | 1 | PX COORDINATOR | | | | 1 | +24 | 17 | 2613 | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10003 | 1 | 39 | 1 | +24 | 8 | 2613 | | | | | | | | | | 3 | HASH UNIQUE | | 1 | 39 | 9 | +16 | 8 | 2613 | | | | | 9M | | 6.90 | Cpu (2) | | 4 | PX RECEIVE | | 1 | 38 | 9 | +16 | 8 | 3M | | | | | | | | | | 5 | PX SEND HASH | :TQ10002 | 1 | 38 | 12 | +14 | 8 | 3M | | | | | | | 3.45 | Cpu (1) | | 6 | BUFFER SORT | | 1 | 39 | 23 | +2 | 8 | 3M | 4584 | 143MB | 703 | 143MB | 151M | 151M | 34.48 | Cpu (2) | | | | | | | | | | | | | | | | | | direct path read temp (6) | | | | | | | | | | | | | | | | | | direct path write temp (2) | | 7 | NESTED LOOPS | | 1 | 38 | 15 | +2 | 8 | 3M | | | | | | | | | | 8 | NESTED LOOPS | | 1 | 38 | 15 | +2 | 8 | 3M | | | | | | | | | | 9 | NESTED LOOPS | | 1 | 38 | 15 | +2 | 8 | 3M | | | | | | | | | | 10 | NESTED LOOPS | | 1 | 38 | 15 | +2 | 8 | 3M | | | | | | | | | | 11 | HASH JOIN | | 41 | 21 | 15 | +2 | 8 | 19334 | | | | | 7M | | | | | 12 | BUFFER SORT | | | | 13 | +2 | 8 | 19233 | | | | | 1M | | | | | 13 | PX RECEIVE | | 89 | 19 | 13 | +2 | 8 | 19233 | | | | | | | | | | 14 | PX SEND HASH | :TQ10000 | 89 | 19 | 1 | +1 | 1 | 19233 | | | | | | | | | | 15 | NESTED LOOPS | | 89 | 19 | 1 | +1 | 1 | 19233 | | | | | | | | | | 16 | NESTED LOOPS | | 89 | 19 | 1 | +1 | 1 | 19233 | | | | | | | | | | 17 | TABLE ACCESS BY INDEX ROWID | FIRM | 1 | 2 | 1 | +1 | 1 | 43 | | | | | | | | | | 18 | INDEX RANGE SCAN | FIRM_ULT_PARENT_FIRM_IDX1 | 1 | 1 | 1 | +1 | 1 | 43 | | | | | | | | | | 19 | PARTITION RANGE SINGLE | | | | 1 | +1 | 43 | 19233 | | | | | | | | | | 20 | BITMAP CONVERSION TO ROWIDS | | | | 1 | +1 | 43 | 19233 | | | | | | | | | | 21 | BITMAP INDEX SINGLE VALUE | LIT_FACT_BD_IDX09 | | | 1 | +1 | 43 | 51 | | | | | | | | | | 22 | TABLE ACCESS BY LOCAL INDEX ROWID | LIT_FACT_BD | 64 | 19 | 1 | +1 | 19233 | 19233 | | | | | | | | | | 23 | PX RECEIVE | | 20 | 2 | 15 | +2 | 8 | 20 | | | | | | | | | | 24 | PX SEND HASH | :TQ10001 | 20 | 2 | 1 | +14 | 8 | 20 | | | | | | | | | | 25 | PX BLOCK ITERATOR | | 20 | 2 | 1 | +14 | 8 | 20 | | | | | | | | | | 26 | TABLE ACCESS FULL | OPPOSING_COUNSEL | 20 | 2 | 1 | +14 | 3 | 20 | | | | | | | | | | 27 | PARTITION RANGE SINGLE | | 1 | | 15 | +2 | 19334 | 3M | | | | | | | | | | 28 | TABLE ACCESS BY LOCAL INDEX ROWID | LIT_FACT_BD | 1 | | 16 | +1 | 19334 | 3M | | | | | | | 17.24 | Cpu (5) | | 29 | INDEX RANGE SCAN | LIT_FACT_BD_IDX20 | 1 | | 15 | +2 | 19334 | 3M | | | | | | | | | | 30 | TABLE ACCESS BY INDEX ROWID | DATE_CODE | 1 | | 15 | +2 | 3M | 3M | | | | | | | 10.34 | Cpu (3) | | 31 | INDEX UNIQUE SCAN | PK_DATE_CODE | 1 | | 16 | +1 | 3M | 3M | | | | | | | 6.90 | Cpu (2) | | 32 | INDEX UNIQUE SCAN | PK_FIRM | 1 | | 23 | +2 | 3M | 3M | | | | | | | 6.90 | Cpu (2) | | 33 | TABLE ACCESS BY INDEX ROWID | FIRM | 1 | | 16 | +1 | 3M | 3M | | | | | | | 13.79 | Cpu (4) | =======================================================================================================================================================================================================================================
We see from operations 3 – 7 that the 3M rows generated from the nested loop joins pass up through a buffer sort operation before being crunched down to 2,613 rows. It’s probably the buffer sort (which is buffering but not sorting) that has mostly passed through a single server and spilled to disc in the 2019 report. We just don’t want that 39M+ rows ever to exist.
So how easy will it be to change the SQL (click to view/hide)
SELECT
/*+ PARALLEL(8) */
DISTINCT rd.document_id AS doc_id
,'firm_id' AS criteria_key
,opf.ultimate_parent_firm_id AS series_id
,period_number
FROM (
SELECT
/*+ PARALLEL(8) */
DISTINCT rd.document_id
,rd.client_role_id
,(
CASE
WHEN MONTHS_BETWEEN(TO_DATE('04/28/2022', 'MM/DD/YYYY'), TO_DATE('04/01/2017', 'MM/DD/YYYY')) > 12
THEN TRUNC(dc.date_value, 'YEAR')
ELSE TRUNC(dc.date_value, 'MONTH')
END
) period_number
FROM REPORT_ENGINE.date_code dc
,REPORT_ENGINE.lit_fact_bd rd
INNER JOIN report_engine.firm FIRM ON rd.firm_id = FIRM.firm_id
WHERE dc.date_value BETWEEN TO_DATE('04/01/2017', 'MM/DD/YYYY')
AND TO_DATE('04/28/2022', 'MM/DD/YYYY')
AND rd.publication_date_id = dc.date_id
AND rd.year_number = to_number('2019')
AND (FIRM.ultimate_parent_firm_id IN (to_number('252094')))
) rd
INNER JOIN report_engine.opposing_counsel op ON rd.client_role_id = op.legal_role_id
INNER JOIN report_engine.lit_fact_bd opclf ON opclf.document_id = rd.document_id
AND op.opposing_counsel_role_id = opclf.client_role_id
AND opclf.year_number = to_number('2019')
INNER JOIN report_engine.firm opf ON opclf.firm_id = opf.firm_id
AND opf.firm_id >= 1000
WHERE 1 = 1;
Lines 7-10 and 27 tell us we alredy have an inline view where we’re doing a “select distinct” and, unwinding the mix of “Oracle” and “ANSI” syntax, we can see that it joins DATE_CODE, LIT_FACT_BD and FIRM, and we know that one of those tables explodes the intermediate data size to something enormous. So it looks like the original author of this code had already worked out that the query needed to aggregate early.
Checking back to the original plans we note that there’s only one “hash unique” operation, and there’s no sign of a “view” operation, so maybe the performance problem is a result of the optimizer suddenly deciding it can do complex view merging with this inline view, and perhaps all we need to do is add the hint /*+ no_merge */ to the inline view and see what happens.
Here’s the plan after adding the hint (click to hide/vew)
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----- | 0 | SELECT STATEMENT | | | | 314 (100)| | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10005 | 23 | 2599 | 314 (1)| 00:00:06 | | | Q1,05 | P->S | QC (RAN D) | | 3 | HASH UNIQUE | | 23 | 2599 | 314 (1)| 00:00:06 | | | Q1,05 | PCWP | | | 4 | PX RECEIVE | | 23 | 2599 | 314 (1)| 00:00:06 | | | Q1,05 | PCWP | | | 5 | PX SEND HASH | :TQ10004 | 23 | 2599 | 314 (1)| 00:00:06 | | | Q1,04 | P->P | HASH | | 6 | HASH UNIQUE | | 23 | 2599 | 314 (1)| 00:00:06 | | | Q1,04 | PCWP | | | 7 | NESTED LOOPS | | 23 | 2599 | 313 (1)| 00:00:06 | | | Q1,04 | PCWP | | | 8 | NESTED LOOPS | | 23 | 2599 | 313 (1)| 00:00:06 | | | Q1,04 | PCWP | | | 9 | NESTED LOOPS | | 23 | 2323 | 310 (1)| 00:00:06 | | | Q1,04 | PCWP | | |* 10 | HASH JOIN | | 388 | 21340 | 148 (1)| 00:00:03 | | | Q1,04 | PCWP | | | 11 | PX RECEIVE | | 20 | 160 | 2 (0)| 00:00:01 | | | Q1,04 | PCWP | | | 12 | PX SEND BROADCAST | :TQ10002 | 20 | 160 | 2 (0)| 00:00:01 | | | Q1,02 | P->P | BROADCA ST | | 13 | PX BLOCK ITERATOR | | 20 | 160 | 2 (0)| 00:00:01 | | | Q1,02 | PCWC | | |* 14 | TABLE ACCESS FULL | OPPOSING_COUNSEL | 20 | 160 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | | | 15 | VIEW | | 835 | 39245 | 146 (1)| 00:00:03 | | | Q1,04 | PCWP | | | 16 | HASH UNIQUE | | 835 | 63460 | 146 (1)| 00:00:03 | | | Q1,04 | PCWP | | | 17 | PX RECEIVE | | 835 | 63460 | 145 (0)| 00:00:03 | | | Q1,04 | PCWP | | | 18 | PX SEND HASH | :TQ10003 | 835 | 63460 | 145 (0)| 00:00:03 | | | Q1,03 | P->P | HASH | |* 19 | HASH JOIN BUFFERED | | 835 | 63460 | 145 (0)| 00:00:03 | | | Q1,03 | PCWP | | | 20 | BUFFER SORT | | | | | | | | Q1,03 | PCWC | | | 21 | PX RECEIVE | | 835 | 52605 | 136 (0)| 00:00:03 | | | Q1,03 | PCWP | | | 22 | PX SEND HASH | :TQ10000 | 835 | 52605 | 136 (0)| 00:00:03 | | | | S->P | HASH | | 23 | NESTED LOOPS | | 835 | 52605 | 136 (0)| 00:00:03 | | | | | | | 24 | NESTED LOOPS | | 835 | 52605 | 136 (0)| 00:00:03 | | | | | | | 25 | TABLE ACCESS BY INDEX ROWID | FIRM | 1 | 12 | 2 (0)| 00:00:01 | | | | | | |* 26 | INDEX RANGE SCAN | FIRM_ULT_PARENT_FIRM_IDX1 | 1 | | 1 (0)| 00:00:01 | | | | | | | 27 | PARTITION RANGE SINGLE | | | | | | 30 | 30 | | | | | 28 | BITMAP CONVERSION TO ROWIDS | | | | | | | | | | | |* 29 | BITMAP INDEX SINGLE VALUE | LIT_FACT_BD_IDX09 | | | | | 30 | 30 | | | | |* 30 | TABLE ACCESS BY LOCAL INDEX ROWID| LIT_FACT_BD | 598 | 30498 | 136 (0)| 00:00:03 | 30 | 30 | | | | | 31 | PX RECEIVE | | 1854 | 24102 | 9 (0)| 00:00:01 | | | Q1,03 | PCWP | | | 32 | PX SEND HASH | :TQ10001 | 1854 | 24102 | 9 (0)| 00:00:01 | | | Q1,01 | P->P | HASH | | 33 | PX BLOCK ITERATOR | | 1854 | 24102 | 9 (0)| 00:00:01 | | | Q1,01 | PCWC | | |* 34 | TABLE ACCESS FULL | DATE_CODE | 1854 | 24102 | 9 (0)| 00:00:01 | | | Q1,01 | PCWP | | | 35 | PARTITION RANGE SINGLE | | 1 | 46 | 0 (0)| | 30 | 30 | Q1,04 | PCWP | | |* 36 | TABLE ACCESS BY LOCAL INDEX ROWID | LIT_FACT_BD | 1 | 46 | 0 (0)| | 30 | 30 | Q1,04 | PCWP | | |* 37 | INDEX RANGE SCAN | LIT_FACT_BD_IDX20 | 1 | | 0 (0)| | 30 | 30 | Q1,04 | PCWP | | |* 38 | INDEX UNIQUE SCAN | PK_FIRM | 1 | | 0 (0)| | | | Q1,04 | PCWP | | | 39 | TABLE ACCESS BY INDEX ROWID | FIRM | 1 | 12 | 0 (0)| | | | Q1,04 | PCWP | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----
Note particularly that operations 15 and 16 tell us that we’ve forced the optimizer into treating the inline view as a separate query block and we now have two aggregation steps, one inside the view, and another after joining FIRM (again) and LIT_FACT_BD (again) to the inline view.
Unfortunately the plan shown here is pulled from memory using dbms_xplan.display_cursor() after execution, so it include the various parallel executoin colums (TQ, IN-OUT, PQ Distrib), but doesn’t have the rowsource execution stats enabled so we can’t see the actual workload and data volume, but in the words of the OP: “adding no_merge hint did the trick and now the SQL is just executing fine”.
Summary
The steps for solving the performance problems of a specific SQL statement are very fluid. For a long-running or parallel statement the SQL Monitor report will automatically be created (though there are limits on the size of the plan that may disable the feature) and this is the easiest source of useful information, though you might also need to pull the execution plan from v$sql_plan to get details about parallel execution and partitioning at the same time.
If you’re not licensed for the diagnostic and performance packs, though, enabling SQL Trace to get the plan and waits gets you a lot of infomation, and querying (g)v$pq_tqstat immediately after running the query can fill in the parallel traffic details.
In this example the SQL Monitor report showed a highly skewed distribution, which might have been fixable by changing the PQ distribution, or even by simply changing the degree of parallelism.
Further examination of the report showed that the query generated an enormous rowsource which it then crunched down to a small result set. Comparing the 2018 and 2019 plans (which were not quite identical but were sufficiently similar to justify comparison) the same skew and explosion of rowsource were visible in both, though the data size involved in the 2018 plan made it seem that the plan was a “good” one which really it wasn’t.
The obvious target for tuning was to see if the explosion in volume could be reduced or eliminated by writing the query with some intermediate non-mergeable view(s), and it turned out that the query had been written with that intent in its original form but without a hint to block complex view merging. After adding the hint the performance was acceptable.
Oracle Scratchpad 
[…] Case Study (June 2022): an example of the SQL Monitor report showing that a no_merge hint was the appropriate solution […]
Pingback by Hinting Catalogue | Oracle Scratchpad — June 17, 2022 @ 2:35 pm BST Jun 17,2022 |
[…] Case Study (June 2022): an example of the SQL Monitor report showing that a no_merge hint was the appropriate solution […]
Pingback by Execution Plans Catalogue | Oracle Scratchpad — June 17, 2022 @ 2:37 pm BST Jun 17,2022 |
[…] Case Study (June 2022): an example of solving a performance problem by reading an SQL Monitor report […]
Pingback by Troubleshooting catalogue | Oracle Scratchpad — June 20, 2022 @ 3:34 pm BST Jun 20,2022 |