Skip to content

Instantly share code, notes, and snippets.

@aryangoti

aryangoti/txt

Created June 14, 2022 08:37
Show Gist options
  • Save aryangoti/4546973ec9f7b3b1d1d60a39fe784165 to your computer and use it in GitHub Desktop.
Save aryangoti/4546973ec9f7b3b1d1d60a39fe784165 to your computer and use it in GitHub Desktop.
Download ZIP
2018_bad
Raw
txt
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fkszva9bt3ucx, child number 0
-------------------------------------
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.docu
Plan hash value: 1744951999
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distr
ib |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | | | 730 (100)| | | | | |
|
| 1 | PX COORDINATOR | | | | | | | | | |
|
| 2 | PX SEND QC (RANDOM) | :TQ10005 | 22 | 2860 | 730 (2)| 00:00:14 | | | Q1,05 | P->S | QC (RAND
) |
| 3 | HASH UNIQUE | | 22 | 2860 | 730 (2)| 00:00:14 | | | Q1,05 | PCWP |
|
| 4 | PX RECEIVE | | 22 | 2860 | 725 (2)| 00:00:14 | | | Q1,05 | PCWP |
|
| 5 | PX SEND HASH | :TQ10004 | 22 | 2860 | 725 (2)| 00:00:14 | | | Q1,04 | P->P | HASH
|
|* 6 | HASH JOIN | | 22 | 2860 | 725 (2)| 00:00:14 | | | Q1,04 | PCWP |
|
| 7 | PX RECEIVE | | 22 | 2596 | 285 (4)| 00:00:06 | | | Q1,04 | PCWP |
|
| 8 | PX SEND BROADCAST | :TQ10003 | 22 | 2596 | 285 (4)| 00:00:06 | | | Q1,03 | P->P | BROADCAS
T |
|* 9 | HASH JOIN | | 22 | 2596 | 285 (4)| 00:00:06 | | | Q1,03 | PCWP |
|
| 10 | PX RECEIVE | | 22 | 2310 | 276 (4)| 00:00:05 | | | Q1,03 | PCWP |
|
| 11 | PX SEND BROADCAST | :TQ10002 | 22 | 2310 | 276 (4)| 00:00:05 | | | Q1,02 | P->P | BROADCAS
T |
|* 12 | HASH JOIN BUFFERED | | 22 | 2310 | 276 (4)| 00:00:05 | | | Q1,02 | PCWP |
|
| 13 | BUFFER SORT | | | | | | | | Q1,02 | PCWC |
|
| 14 | PX RECEIVE | | 915 | 88755 | 274 (5)| 00:00:05 | | | Q1,02 | PCWP |
|
| 15 | PX SEND HASH | :TQ10000 | 915 | 88755 | 274 (5)| 00:00:05 | | | | S->P | HASH
|
| 16 | NESTED LOOPS | | 915 | 88755 | 274 (5)| 00:00:05 | | | | |
|
| 17 | NESTED LOOPS | | 915 | 88755 | 274 (5)| 00:00:05 | | | | |
|
| 18 | PARTITION RANGE SINGLE | | 587 | 29937 | 29 (38)| 00:00:01 | 29 | 29 | | |
|
|* 19 | TABLE ACCESS BY LOCAL INDEX ROWID| LIT_FACT_BD | 587 | 29937 | 29 (38)| 00:00:01 | 29 | 29 | | |
|
| 20 | BITMAP CONVERSION TO ROWIDS | | | | | | | | | |
|
| 21 | BITMAP AND | | | | | | | | | |
|
| 22 | BITMAP MERGE | | | | | | | | | |
|
| 23 | BITMAP KEY ITERATION | | | | | | | | | |
|
| 24 | TABLE ACCESS BY INDEX ROWID | FIRM | 1 | 12 | 2 (0)| 00:00:01 | | | | |
|
|* 25 | INDEX RANGE SCAN | FIRM_ULT_PARENT_FIRM_IDX1 | 1 | | 1 (0)| 00:00:01 | | | | |
|
|* 26 | BITMAP INDEX RANGE SCAN | LIT_FACT_BD_IDX09 | | | | | 29 | 29 | | |
|
| 27 | BITMAP MERGE | | | | | | | | | |
|
| 28 | BITMAP KEY ITERATION | | | | | | | | | |
|
| 29 | INDEX FULL SCAN | OPPOSING_COUNSEL_FK01 | 20 | 160 | 1 (0)| 00:00:01 | | | | |
|
|* 30 | BITMAP INDEX RANGE SCAN | LIT_FACT_BD_IDX11 | | | | | 29 | 29 | | |
|
| 31 | PARTITION RANGE SINGLE | | 1 | | 0 (0)| | 29 | 29 | | |
|
|* 32 | INDEX RANGE SCAN | LIT_FACT_BD_IDX20 | 1 | | 0 (0)| | 29 | 29 | | |
|
|* 33 | TABLE ACCESS BY LOCAL INDEX ROWID | LIT_FACT_BD | 2 | 92 | 0 (0)| | 29 | 29 | | |
|
| 34 | PX RECEIVE | | 20 | 160 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP |
|
| 35 | PX SEND HASH | :TQ10001 | 20 | 160 | 2 (0)| 00:00:01 | | | Q1,01 | P->P | HASH
|
| 36 | PX BLOCK ITERATOR | | 20 | 160 | 2 (0)| 00:00:01 | | | Q1,01 | PCWC |
|
|* 37 | TABLE ACCESS FULL | OPPOSING_COUNSEL | 20 | 160 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP |
|
| 38 | PX BLOCK ITERATOR | | 1854 | 24102 | 9 (0)| 00:00:01 | | | Q1,03 | PCWC |
|
|* 39 | TABLE ACCESS FULL | DATE_CODE | 1854 | 24102 | 9 (0)| 00:00:01 | | | Q1,03 | PCWP |
|
| 40 | PX BLOCK ITERATOR | | 606K| 7110K| 440 (1)| 00:00:08 | | | Q1,04 | PCWC |
|
|* 41 | TABLE ACCESS FULL | FIRM | 606K| 7110K| 440 (1)| 00:00:08 | | | Q1,04 | PCWP |
|
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("OPCLF"."FIRM_ID"="OPF"."FIRM_ID")
9 - access("RD"."PUBLICATION_DATE_ID"="DC"."DATE_ID")
12 - access("OP"."OPPOSING_COUNSEL_ROLE_ID"="OPCLF"."CLIENT_ROLE_ID" AND "RD"."CLIENT_ROLE_ID"="OP"."LEGAL_ROLE_ID")
19 - filter("RD"."YEAR_NUMBER"=2018)
25 - access("FIRM"."ULTIMATE_PARENT_FIRM_ID"=252094)
26 - access("RD"."FIRM_ID"="FIRM"."FIRM_ID")
30 - access("RD"."CLIENT_ROLE_ID"="OP"."LEGAL_ROLE_ID")
32 - access("OPCLF"."DOCUMENT_ID"="RD"."DOCUMENT_ID" AND "OPCLF"."FIRM_ID">=1000)
33 - filter("OPCLF"."YEAR_NUMBER"=2018)
37 - access(:Z>=:Z AND :Z<=:Z)
39 - access(:Z>=:Z AND :Z<=:Z)
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')))
41 - access(:Z>=:Z AND :Z<=:Z)
filter("OPF"."FIRM_ID">=1000)
Note
-----
- dynamic sampling used for this statement (level=6)
- Degree of Parallelism is 8 because of hint
- star transformation used for this statement
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment