Skip to content

Instantly share code, notes, and snippets.

@aryangoti

aryangoti/txt

Created June 9, 2022 11:04
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save aryangoti/a7704a8075f118f7d942e49acee1900d to your computer and use it in GitHub Desktop.
Save aryangoti/a7704a8075f118f7d942e49acee1900d to your computer and use it in GitHub Desktop.
Download ZIP
2018_query
Raw
txt
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('2018')
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('2018')
INNER JOIN report_engine.firm opf ON opclf.firm_id = opf.firm_id
AND opf.firm_id >= 1000
WHERE 1 = 1;
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('2018') 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('2018') 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 (ALL ROWS)
Instance ID : 1
Session : SYS (647:28741)
SQL ID : fkszva9bt3ucx
SQL Execution ID : 16777216
Execution Started : 06/09/2022 05:05:35
First Refresh Time : 06/09/2022 05:05:35
Last Refresh Time : 06/09/2022 05:05:59
Duration : 24s
Module/Action : sqlplus@c111dhw (TNS V1-V3)/-
Service : SYS$USERS
Program : sqlplus@c111dhw (TNS V1-V3)
Fetch Calls : 176
Global Stats
=========================================================================================================
| Elapsed | Cpu | IO | Concurrency | Other | Fetch | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes |
=========================================================================================================
| 28 | 20 | 7.30 | 0.70 | 0.03 | 176 | 4M | 4584 | 143MB | 703 | 143MB |
=========================================================================================================
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.87 | 0.16 | | 0.70 | 0.01 | 5952 | | . | | . | |
| p000 | Set 1 | 1 | 0.23 | 0.23 | | | | 2 | | . | | . | |
| p001 | Set 1 | 2 | 0.14 | 0.14 | | 0.00 | | 2 | | . | | . | |
| p002 | Set 1 | 3 | 0.20 | 0.20 | | 0.00 | | 2 | | . | | . | |
| p003 | Set 1 | 4 | 0.18 | 0.18 | | | | | | . | | . | |
| p004 | Set 1 | 5 | 0.16 | 0.16 | | | | | | . | | . | |
| p005 | Set 1 | 6 | 0.15 | 0.15 | | 0.00 | | | | . | | . | |
| p006 | Set 1 | 7 | 0.23 | 0.23 | | 0.00 | | | | . | | . | |
| p007 | Set 1 | 8 | 0.09 | 0.09 | | 0.00 | | | | . | | . | |
| p008 | Set 2 | 1 | 22 | 14 | 7.30 | 0.00 | | 3M | 4584 | 143MB | 703 | 143MB | direct path read temp (6) |
| | | | | | | | | | | | | | direct path write temp (2) |
| p009 | Set 2 | 2 | 1.31 | 1.31 | | 0.00 | 0.00 | 253K | | . | | . | |
| p010 | Set 2 | 3 | 0.37 | 0.36 | | | 0.01 | 58989 | | . | | . | |
| p011 | Set 2 | 4 | 1.99 | 1.98 | | | 0.01 | 469K | | . | | . | |
| p012 | Set 2 | 5 | 0.01 | 0.01 | | 0.00 | | 506 | | . | | . | |
| p013 | Set 2 | 6 | 0.00 | 0.00 | | | | | | . | | . | |
| p014 | Set 2 | 7 | 0.14 | 0.14 | | 0.00 | 0.00 | 18682 | | . | | . | |
| p015 | Set 2 | 8 | 0.00 | | | | 0.00 | | | . | | . | |
=================================================================================================================================================================
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) |
=======================================================================================================================================================================================================================================
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"=2018)
26 - access(:Z>=:Z AND :Z<=:Z)
28 - filter("OPCLF"."YEAR_NUMBER"=2018)
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment