Skip to content

Instantly share code, notes, and snippets.

@aryangoti

aryangoti/txt

Created June 14, 2022 06:36
Show Gist options
  • Save aryangoti/888adb4095d4bcea5b68a9c2f82f9c22 to your computer and use it in GitHub Desktop.
Save aryangoti/888adb4095d4bcea5b68a9c2f82f9c22 to your computer and use it in GitHub Desktop.
Download ZIP
2019_pq_hint_2
Raw
txt
SQL Text
------------------------------
SELECT /*+ PARALLEL(8) PQ_DISTRIBUTE(@"SEL$B62753A3" "OP"@"SEL$1" HASH HASH) PQ_DISTRIBUTE(@"SEL$B62753A3" "OPF"@"SEL$5" HASH HASH) FULL(@"SEL$B62753A3" "OPCLF"@"SEL$4") USE_HASH(@"SEL$B62753A3" "OPCLF"@"SEL$4") */ 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 : DONE (ERROR)
Instance ID : 1
Session : SYS (1833:2603)
SQL ID : cyuzrr0babdtp
SQL Execution ID : 16777216
Execution Started : 06/13/2022 23:54:37
First Refresh Time : 06/13/2022 23:54:37
Last Refresh Time : 06/13/2022 23:54:40
Duration : 3s
Module/Action : sqlplus@c111gyy (TNS V1-V3)/-
Service : SYS$USERS
Program : sqlplus@c111gyy (TNS V1-V3)
Global Stats
=================================================================================================
| Elapsed | Cpu | IO | Concurrency | Other | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes |
=================================================================================================
| 5551 | 4202 | 1349 | 0.20 | 0.01 | 1G | 2M | 51GB | 259K | 51GB |
=================================================================================================
Parallel Execution Details (DOP=8 , Servers Allocated=16)
==================================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Concurrency | Other | Buffer | Read | Read | Write | Write | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | (sample #) |
==================================================================================================================================================
| PX Coordinator | QC | | 0.07 | 0.07 | | | | 9181 | | . | | . | |
| 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 | 5550 | 4200 | 1349 | 0.20 | | 1G | 2M | 51GB | 259K | 51GB | |
| p009 | Set 2 | 2 | | | | | | | | . | | . | |
| p010 | Set 2 | 3 | | | | | | | | . | | . | |
| p011 | Set 2 | 4 | 1.41 | 1.40 | | | 0.01 | 452K | | . | | . | |
| p012 | Set 2 | 5 | | | | | | | | . | | . | |
| p013 | Set 2 | 6 | | | | | | | | . | | . | |
| p014 | Set 2 | 7 | | | | | | | | . | | . | |
| p015 | Set 2 | 8 | | | | | | | | . | | . | |
==================================================================================================================================================
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 | | | | | | 3 | | | | | | | | | |
| 1 | PX COORDINATOR | | | | | | 3 | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | 21 | 306 | | | | | | | | | | | | |
| 3 | HASH UNIQUE | | 21 | 306 | | | | | | | | | | | | |
| 4 | PX RECEIVE | | 21 | 305 | | | | | | | | | | | | |
| 5 | PX SEND HASH | :TQ10002 | 21 | 305 | 1470 | +3972 | 2 | 847M | | | | | | | | |
| 6 | BUFFER SORT | | 21 | 306 | 5440 | +2 | 2 | 847M | 2M | 51GB | 259K | 51GB | 134M | 54G | | |
| 7 | NESTED LOOPS | | 21 | 305 | 3971 | +2 | 2 | 847M | | | | | | | | |
| 8 | NESTED LOOPS | | 21 | 305 | 3971 | +2 | 2 | 847M | | | | | | | | |
| 9 | NESTED LOOPS | | 21 | 302 | 3971 | +2 | 2 | 847M | | | | | | | | |
| 10 | NESTED LOOPS | | 21 | 299 | 3971 | +2 | 2 | 847M | | | | | | | | |
| 11 | HASH JOIN | | 388 | 138 | 3971 | +2 | 2 | 34304 | | | | | 6M | | | |
| 12 | BUFFER SORT | | | | 1 | +2 | 2 | 36855 | | | | | 3M | | | |
| 13 | PX RECEIVE | | 835 | 136 | 1 | +2 | 2 | 36855 | | | | | | | | |
| 14 | PX SEND HASH | :TQ10000 | 835 | 136 | 1 | +3 | 1 | 38694 | | | | | | | | |
| 15 | NESTED LOOPS | | 835 | 136 | 3 | +1 | 1 | 38694 | | | | | | | 16.67 | Cpu (1) |
| 16 | NESTED LOOPS | | 835 | 136 | 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 | 598 | 136 | 1 | +3 | 38694 | 38694 | | | | | | | | |
| 23 | PX RECEIVE | | 20 | 2 | 3971 | +2 | 2 | 4 | | | | | | | | |
| 24 | PX SEND HASH | :TQ10001 | 20 | 2 | | | | | | | | | | | | |
| 25 | PX BLOCK ITERATOR | | 20 | 2 | | | | | | | | | | | | |
| 26 | TABLE ACCESS FULL | OPPOSING_COUNSEL | 20 | 2 | | | | | | | | | | | | |
| 27 | PARTITION RANGE SINGLE | | 1 | | 3971 | +2 | 34305 | 847M | | | | | | | | |
| 28 | TABLE ACCESS BY LOCAL INDEX ROWID | LIT_FACT_BD | 1 | | 3971 | +2 | 34305 | 847M | | | | | | | 16.67 | Cpu (1) |
| 29 | INDEX RANGE SCAN | LIT_FACT_BD_IDX20 | 1 | | 3971 | +2 | 34305 | 847M | | | | | | | | |
| 30 | TABLE ACCESS BY INDEX ROWID | DATE_CODE | 1 | | 3971 | +2 | 847M | 847M | | | | | | | | |
| 31 | INDEX UNIQUE SCAN | PK_DATE_CODE | 1 | | 3971 | +2 | 847M | 847M | | | | | | | 33.33 | Cpu (2) |
| 32 | INDEX UNIQUE SCAN | PK_FIRM | 1 | | 5439 | +2 | 847M | 847M | | | | | | | 16.67 | Cpu (1) |
| 33 | TABLE ACCESS BY INDEX ROWID | FIRM | 1 | | 3971 | +2 | 847M | 847M | | | | | | | 16.67 | Cpu (1) |
============================================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
11 - access("RD"."CLIENT_ROLE_ID"="OP"."LEGAL_ROLE_ID")
18 - access("FIRM"."ULTIMATE_PARENT_FIRM_ID"=252094)
21 - access("RD"."FIRM_ID"="FIRM"."FIRM_ID")
22 - filter("RD"."YEAR_NUMBER"=2019)
26 - access(:Z>=:Z AND :Z<=:Z)
28 - filter("OPCLF"."YEAR_NUMBER"=2019)
29 - access("OPCLF"."DOCUMENT_ID"="RD"."DOCUMENT_ID" AND "OPCLF"."FIRM_ID">=1000 AND
"OP"."OPPOSING_COUNSEL_ROLE_ID"="OPCLF"."CLIENT_ROLE_ID")
filter("OP"."OPPOSING_COUNSEL_ROLE_ID"="OPCLF"."CLIENT_ROLE_ID")
30 - filter(("DC"."DATE_VALUE">=TO_DATE(' 2017-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"DC"."DATE_VALUE"<=TO_DATE(' 2022-04-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
31 - access("RD"."PUBLICATION_DATE_ID"="DC"."DATE_ID")
32 - access("OPCLF"."FIRM_ID"="OPF"."FIRM_ID")
filter("OPF"."FIRM_ID">=1000)
Note
-----
- dynamic sampling used for this statement (level=6)
- Degree of Parallelism is 8 because of hint
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
86 rows selected.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment