Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

New data warehouse report has long running query

Charles MOct 25 2018 — edited Oct 30 2018

Hi All,

Development team has created a report which takes approximately 1.5 hours to complete. The database team was asked to look into it from the db-side.

I have gathered the following as an initial assessment of the SQL that is hitting the data warehouse:

DB Parameters:

NAME

TYPE

VALUE

optimizer_adaptive_features	boolean	TRUE
optimizer_adaptive_reporting_only	boolean	FALSE
optimizer_capture_sql_plan_baselines boolean	FALSE
optimizer_dynamic_sampling	integer	2
optimizer_features_enable	string	12.1.0.2
optimizer_index_caching	integer	0
optimizer_index_cost_adj	integer	100
optimizer_inmemory_aware	boolean	TRUE
optimizer_mode	string	ALL_ROWS
optimizer_secure_view_merging	boolean	TRUE
optimizer_use_invisible_indexes	boolean	FALSE
optimizer_use_pending_statistics	boolean	FALSE
optimizer_use_sql_plan_baselines	boolean	TRUE

NAME

TYPE

VALUE

db_file_multiblock_read_count

integer

128

NAME

TYPE

VALUE

db_block_size

integer

8192

NAME

TYPE

VALUE

cursor_sharing

string

EXACT

SNAME

PNAME

PVAL1 PVAL2

SYSSTATS_INFO	STATUS	COMPLETED
SYSSTATS_INFO	DSTART	03-11-2017 00:00
SYSSTATS_INFO	DSTOP	03-11-2017 00:00
SYSSTATS_INFO	FLAGS	1
SYSSTATS_MAIN	CPUSPEEDNW	1332
SYSSTATS_MAIN	IOSEEKTIM	10
SYSSTATS_MAIN	IOTFRSPEED	4096
SYSSTATS_MAIN	SREADTIM
SYSSTATS_MAIN	MREADTIM
SYSSTATS_MAIN	CPUSPEED
SYSSTATS_MAIN	MBRC
SYSSTATS_MAIN	MAXTHR
SYSSTATS_MAIN	SLAVETHR

Explain Plan:

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 3953956312

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 6 | 1692 | | 68819 (1)| 00:00:03 | | | |

| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | |

|* 3 | TABLE ACCESS FULL | W_MCAL_DAY_D | 156 | 4368 | | 2121 (1)| 00:00:01 | | | |

| 4 | PX COORDINATOR | | | | | | | | | |

| 5 | PX SEND QC (RANDOM) | :TQ10009 | 1 | 426 | | 66588 (1)| 00:00:03 | Q1,09 | P->S | QC (RAND) |

|* 7 | HASH JOIN | | 1 | 426 | | 66588 (1)| 00:00:03 | Q1,09 | PCWP | |

| 8 | PX RECEIVE | | 1 | 399 | | 64360 (1)| 00:00:03 | Q1,09 | PCWP | |

| 9 | PX SEND HYBRID HASH | :TQ10008 | 1 | 399 | | 64360 (1)| 00:00:03 | Q1,08 | P->P | HYBRID HASH|

| 10 | STATISTICS COLLECTOR | | | | | | | Q1,08 | PCWC | |

|* 11 | HASH JOIN | | 1 | 399 | | 64360 (1)| 00:00:03 | Q1,08 | PCWP | |

| 12 | PX RECEIVE | | 1 | 355 | | 64355 (1)| 00:00:03 | Q1,08 | PCWP | |

| 13 | PX SEND HYBRID HASH | :TQ10007 | 1 | 355 | | 64355 (1)| 00:00:03 | Q1,07 | P->P | HYBRID HASH|

| 14 | STATISTICS COLLECTOR | | | | | | | Q1,07 | PCWC | |

|* 15 | HASH JOIN | | 1 | 355 | | 64355 (1)| 00:00:03 | Q1,07 | PCWP | |

| 16 | PX RECEIVE | | 1 | 330 | | 64351 (1)| 00:00:03 | Q1,07 | PCWP | |

| 17 | PX SEND HYBRID HASH | :TQ10006 | 1 | 330 | | 64351 (1)| 00:00:03 | Q1,06 | P->P | HYBRID HASH|

| 18 | STATISTICS COLLECTOR | | | | | | | Q1,06 | PCWC | |

|* 19 | HASH JOIN BUFFERED | | 1 | 330 | | 64351 (1)| 00:00:03 | Q1,06 | PCWP | |

| 20 | BUFFER SORT | | | | | | | Q1,06 | PCWC | |

| 21 | PX RECEIVE | | 27659 | 1728K| | 580 (1)| 00:00:01 | Q1,06 | PCWP | |

| 22 | PX SEND HYBRID HASH | :TQ10000 | 27659 | 1728K| | 580 (1)| 00:00:01 | | S->P | HYBRID HASH|

| 23 | STATISTICS COLLECTOR | | | | | | | | | |

|* 24 | TABLE ACCESS BY INDEX ROWID BATCHED | W_AP_HOLDS_F | 27659 | 1728K| | 580 (1)| 00:00:01 | | | |

| 25 | BITMAP CONVERSION TO ROWIDS | | | | | | | | | |

| 26 | BITMAP AND | | | | | | | | | |

| 27 | BITMAP MERGE | | | | | | | | | |

| 28 | BITMAP KEY ITERATION | | | | | | | | | |

|* 29 | ED TABLE ACCESS BY INDEX ROWID BATCH | W_INT_ORG_D | 9 | 81 | | 4 (0)| 00:00:01 | | | |

|* 30 | INDEX RANGE SCAN | W_INT_ORG_D_M10 | 20 | | | 1 (0)| 00:00:01 | | | |

| 32 | BITMAP MERGE | | | | | | | | | |

| 33 | BITMAP KEY ITERATION | | | | | | | | | |

|* 34 | TABLE ACCESS FULL | W_AP_HOLD_RELEASE_REASON_D | 125 | 875 | | 5 (0)| 00:00:01 | | | |

| 36 | PX RECEIVE | | 1336K| 338M| | 67695 (7)| 00:00:03 | Q1,06 | PCWP | |

| 37 | PX SEND HYBRID HASH | :TQ10005 | 1336K| 338M| | 67695 (7)| 00:00:03 | Q1,05 | P->P | HYBRID HASH|

| 38 | VIEW | | 1336K| 338M| | 67695 (7)| 00:00:03 | Q1,05 | PCWP | |

| 39 | UNION-ALL | | | | | | | Q1,05 | PCWP | |

| 40 | HASH UNIQUE | | 663K| 12M| 20M| 5224 (1)| 00:00:01 | Q1,05 | PCWP | |

| 41 | PX RECEIVE | | 663K| 12M| | 5224 (1)| 00:00:01 | Q1,05 | PCWP | |

| 42 | PX SEND HASH | :TQ10004 | 663K| 12M| | 5224 (1)| 00:00:01 | Q1,04 | P->P | HASH |

| 43 | HASH UNIQUE | | 663K| 12M| 20M| 5224 (1)| 00:00:01 | Q1,04 | PCWP | |

| 44 | PX BLOCK ITERATOR | | | | | | | Q1,04 | PCWC | |

| 46 | PX SELECTOR | | | | | | | Q1,05 | PCWP | |

| 47 | HASH UNIQUE | | 672K| 15M| 25M| 62471 (1)| 00:00:03 | Q1,05 | PCWP | |

|* 48 | TABLE ACCESS FULL | W_AP_XACT_F | 672K| 15M| | 57725 (1)| 00:00:03 | Q1,05 | PCWP | |

| 49 | BUFFER SORT | | | | | | | Q1,07 | PCWC | |

| 50 | PX RECEIVE | | 9 | 225 | | 4 (0)| 00:00:01 | Q1,07 | PCWP | |

| 51 | PX SEND HYBRID HASH | :TQ10001 | 9 | 225 | | 4 (0)| 00:00:01 | | S->P | HYBRID HASH|

|* 52 | TABLE ACCESS BY INDEX ROWID BATCHED | W_INT_ORG_D | 9 | 225 | | 4 (0)| 00:00:01 | | | |

|* 53 | INDEX RANGE SCAN | W_INT_ORG_D_M10 | 20 | | | 1 (0)| 00:00:01 | | | |

| 54 | BUFFER SORT | | | | | | | Q1,08 | PCWC | |

| 55 | PX RECEIVE | | 125 | 5500 | | 5 (0)| 00:00:01 | Q1,08 | PCWP | |

| 56 | PX SEND HYBRID HASH | :TQ10002 | 125 | 5500 | | 5 (0)| 00:00:01 | | S->P | HYBRID HASH|

|* 57 | TABLE ACCESS FULL | W_AP_HOLD_RELEASE_REASON_D | 125 | 5500 | | 5 (0)| 00:00:01 | | | |

| 58 | BUFFER SORT | | | | | | | Q1,09 | PCWC | |

| 59 | PX RECEIVE | | 8027 | 211K| | 2228 (1)| 00:00:01 | Q1,09 | PCWP | |

| 60 | PX SEND HYBRID HASH | :TQ10003 | 8027 | 211K| | 2228 (1)| 00:00:01 | | S->P | HYBRID HASH|

|* 61 | TABLE ACCESS FULL | W_PARTY_D | 8027 | 211K| | 2228 (1)| 00:00:01 | | | |

|* 62 | FILTER | | | | | | | | | |

| 63 | SORT GROUP BY | | 6 | 1692 | | 102 (2)| 00:00:01 | | | |

| 64 | NESTED LOOPS | | 105 | 29610 | | 97 (2)| 00:00:01 | | | |

| 65 | NESTED LOOPS | | 105 | 29610 | | 97 (2)| 00:00:01 | | | |

| 66 | NESTED LOOPS | | 3 | 783 | | 94 (2)| 00:00:01 | | | |

| 67 | NESTED LOOPS | | 1 | 243 | | 93 (2)| 00:00:01 | | | |

| 68 | NESTED LOOPS | | 1 | 232 | | 92 (2)| 00:00:01 | | | |

| 69 | NESTED LOOPS | | 1 | 204 | | 9 (0)| 00:00:01 | | | |

|* 70 | HASH JOIN | | 1 | 188 | | 8 (0)| 00:00:01 | | | |

|* 71 | HASH JOIN | | 1 | 162 | | 6 (0)| 00:00:01 | | | |

| 72 | TABLE ACCESS FULL | SYS_TEMP_0FD9D69A0_B77B4DD0 | 1 | 129 | | 2 (0)| 00:00:01 | | | |

| 73 | TABLE ACCESS BY INDEX ROWID BATCHED | W_INT_ORG_D_TL | 25 | 825 | | 4 (0)| 00:00:01 | | | |

|* 74 | INDEX RANGE SCAN | W_INT_ORG_D_TL_U1 | 25 | | | 1 (0)| 00:00:01 | | | |

| 75 | TABLE ACCESS FULL | SYS_TEMP_0FD9D699F_B77B4DD0 | 156 | 4056 | | 2 (0)| 00:00:01 | | | |

| 76 | TABLE ACCESS BY INDEX ROWID | W_USER_D | 1 | 16 | | 1 (0)| 00:00:01 | | | |

|* 77 | INDEX UNIQUE SCAN | W_USER_D_P1 | 1 | | | 0 (0)| 00:00:01 | | | |

|* 78 | TABLE ACCESS BY INDEX ROWID BATCHED | W_MCAL_DAY_D | 1 | 28 | | 92 (2)| 00:00:01 | | | |

| 79 | BITMAP CONVERSION TO ROWIDS | | | | | | | | | |

| 80 | BITMAP AND | | | | | | | | | |

| 81 | BITMAP MERGE | | | | | | | | | |

| 82 | BITMAP KEY ITERATION | | | | | | | | | |

| 83 | TABLE ACCESS FULL | SYS_TEMP_0FD9D69A0_B77B4DD0 | 1 | 3 | | 2 (0)| 00:00:01 | | | |

| 85 | BITMAP MERGE | | | | | | | | | |

| 86 | BITMAP KEY ITERATION | | | | | | | | | |

| 87 | TABLE ACCESS FULL | SYS_TEMP_0FD9D699F_B77B4DD0 | 156 | 468 | | 2 (0)| 00:00:01 | | | |

| 89 | TABLE ACCESS BY INDEX ROWID | W_PARTY_ORG_D | 1 | 11 | | 1 (0)| 00:00:01 | | | |

|* 90 | INDEX UNIQUE SCAN | W_PARTY_ORG_D_P1 | 1 | | | 0 (0)| 00:00:01 | | | |

| 91 | TABLE ACCESS BY INDEX ROWID | W_SUPPLIER_ACCOUNT_D | 7 | 126 | | 1 (0)| 00:00:01 | | | |

|* 92 | INDEX UNIQUE SCAN | W_SPLR_ACCNT_D_P1 | 1 | | | 0 (0)| 00:00:01 | | | |

|* 93 | INDEX UNIQUE SCAN | W_EMPLOYEE_D_P1 | 1 | | | 0 (0)| 00:00:01 | | | |

| 94 | TABLE ACCESS BY INDEX ROWID | W_EMPLOYEE_D | 36 | 756 | | 1 (0)| 00:00:01 | | | |

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter(CASE "T2519709"."ROW_WID" WHEN 0 THEN NULL ELSE "T2519709"."MCAL_DAY_DT" END <=TO_DATE(' 2018-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND CASE

"T2519709"."ROW_WID" WHEN 0 THEN NULL ELSE "T2519709"."MCAL_DAY_DT" END >=TO_DATE(' 2018-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

"T2519709"."ADJUSTMENT_PERIOD_FLG"='N')

7 - access("T2520043"."SUPPLIER_WID"="T2534878"."ROW_WID")

11 - access("T2519877"."ROW_WID"="T2520043"."HOLD_REASON_WID")

15 - access("T2520043"."PAYABLES_ORG_WID"="T2591329"."SCD1_WID")

19 - access("T2520043"."INVOICE_NUM"="T2972724"."PURCH_INVOICE_NUM" AND "T2520043"."SPLR_ACCT_WID"="T2972724"."SPLR_ACCT_WID")

24 - filter("T2520043"."DELETE_FLG"='N')

29 - filter("T2591329"."PAYABLES_ORG_FLG"='U' OR "T2591329"."PAYABLES_ORG_FLG"='Y')

30 - access("T2591329"."CURRENT_FLG"='Y')

31 - access("T2520043"."PAYABLES_ORG_WID"="T2591329"."SCD1_WID")

34 - filter("T2519877"."TYPE_FLG"='H' OR "T2519877"."TYPE_FLG"='U' OR "T2519877"."TYPE_FLG" IS NULL)

35 - access("T2520043"."HOLD_REASON_WID"="T2519877"."ROW_WID")

45 - filter("PURCH_ORDER_NUM" IS NOT NULL)

48 - filter("PURCH_ORDER_NUM" IS NULL AND "PURCH_ORDER_DT_WID"=0)

52 - filter("T2591329"."PAYABLES_ORG_FLG"='U' OR "T2591329"."PAYABLES_ORG_FLG"='Y')

53 - access("T2591329"."CURRENT_FLG"='Y')

57 - filter("T2519877"."TYPE_FLG"='H' OR "T2519877"."TYPE_FLG"='U' OR "T2519877"."TYPE_FLG" IS NULL)

61 - filter("T2534878"."SUPPLIER_FLG"='Y')

62 - filter(SUM(CASE WHEN "C13"='Y' THEN 1 ELSE 0 END )>0)

70 - access("C0"="C0" AND "C1"="C7")

71 - access("DATASOURCE_NUM_ID"="C5" AND "INTEGRATION_ID"="C4")

74 - access("LANGUAGE_CODE"='US')

77 - access("T2519902"."ROW_WID"="C9")

78 - filter("T2618846"."ADJUSTMENT_PERIOD_FLG"='N')

84 - access("T2618846"."MCAL_CAL_WID"="C0" AND "C3"="T2618846"."MCAL_DAY_DT_WID")

filter("T2618846"."MCAL_CAL_WID"="C0" AND "C0"="T2618846"."MCAL_CAL_WID")

88 - access("T2618846"."MCAL_CAL_WID"="C0")

90 - access("C2"="T2616936"."ROW_WID")

92 - access("C8"="T2639505"."ROW_WID")

filter("C1"="T2639505"."ROW_WID")

93 - access("C6"="T2622263"."ROW_WID")

Note

-----

- dynamic statistics used: dynamic sampling (level=2)

- star transformation used for this statement

- 1 Sql Plan Directive used for this statement

Execution Plan:

435 rows selected.

Elapsed: 01:21:59.32

Execution Plan

----------------------------------------------------------

Plan hash value: 3953956312

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 6 | 1692 | | 68819 (1)| 00:00:03 | | | |

| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | |

|* 3 | TABLE ACCESS FULL | W_MCAL_DAY_D | 156 | 4368 | | 2121 (1)| 00:00:01 | | | |

| 4 | PX COORDINATOR | | | | | | | | | |

| 5 | PX SEND QC (RANDOM) | :TQ10009 | 1 | 426 | | 66588 (1)| 00:00:03 | Q1,09 | P->S | QC (RAND) |

|* 7 | HASH JOIN | | 1 | 426 | | 66588 (1)| 00:00:03 | Q1,09 | PCWP | |

| 8 | PX RECEIVE | | 1 | 399 | | 64360 (1)| 00:00:03 | Q1,09 | PCWP | |

| 9 | PX SEND HYBRID HASH | :TQ10008 | 1 | 399 | | 64360 (1)| 00:00:03 | Q1,08 | P->P | HYBRID HASH|

| 10 | STATISTICS COLLECTOR | | | | | | | Q1,08 | PCWC | |

|* 11 | HASH JOIN | | 1 | 399 | | 64360 (1)| 00:00:03 | Q1,08 | PCWP | |

| 12 | PX RECEIVE | | 1 | 355 | | 64355 (1)| 00:00:03 | Q1,08 | PCWP | |

| 13 | PX SEND HYBRID HASH | :TQ10007 | 1 | 355 | | 64355 (1)| 00:00:03 | Q1,07 | P->P | HYBRID HASH|

| 14 | STATISTICS COLLECTOR | | | | | | | Q1,07 | PCWC | |

|* 15 | HASH JOIN | | 1 | 355 | | 64355 (1)| 00:00:03 | Q1,07 | PCWP | |

| 16 | PX RECEIVE | | 1 | 330 | | 64351 (1)| 00:00:03 | Q1,07 | PCWP | |

| 17 | PX SEND HYBRID HASH | :TQ10006 | 1 | 330 | | 64351 (1)| 00:00:03 | Q1,06 | P->P | HYBRID HASH|

| 18 | STATISTICS COLLECTOR | | | | | | | Q1,06 | PCWC | |

|* 19 | HASH JOIN BUFFERED | | 1 | 330 | | 64351 (1)| 00:00:03 | Q1,06 | PCWP | |

| 20 | BUFFER SORT | | | | | | | Q1,06 | PCWC | |

| 21 | PX RECEIVE | | 24893 | 1555K| | 580 (1)| 00:00:01 | Q1,06 | PCWP | |

| 22 | PX SEND HYBRID HASH | :TQ10000 | 24893 | 1555K| | 580 (1)| 00:00:01 | | S->P | HYBRID HASH|

| 23 | STATISTICS COLLECTOR | | | | | | | | | |

|* 24 | TABLE ACCESS BY INDEX ROWID BATCHED | W_AP_HOLDS_F | 24893 | 1555K| | 580 (1)| 00:00:01 | | | |

| 25 | BITMAP CONVERSION TO ROWIDS | | | | | | | | | |

| 26 | BITMAP AND | | | | | | | | | |

| 27 | BITMAP MERGE | | | | | | | | | |

| 28 | BITMAP KEY ITERATION | | | | | | | | | |

|* 29 | ED TABLE ACCESS BY INDEX ROWID BATCH | W_INT_ORG_D | 9 | 81 | | 4 (0)| 00:00:01 | | | |

|* 30 | INDEX RANGE SCAN | W_INT_ORG_D_M10 | 20 | | | 1 (0)| 00:00:01 | | | |

| 32 | BITMAP MERGE | | | | | | | | | |

| 33 | BITMAP KEY ITERATION | | | | | | | | | |

|* 34 | TABLE ACCESS FULL | W_AP_HOLD_RELEASE_REASON_D | 125 | 875 | | 5 (0)| 00:00:01 | | | |

| 36 | PX RECEIVE | | 757K| 192M| | 65495 (4)| 00:00:03 | Q1,06 | PCWP | |

| 37 | PX SEND HYBRID HASH | :TQ10005 | 757K| 192M| | 65495 (4)| 00:00:03 | Q1,05 | P->P | HYBRID HASH|

| 38 | VIEW | | 757K| 192M| | 65495 (4)| 00:00:03 | Q1,05 | PCWP | |

| 39 | UNION-ALL | | | | | | | Q1,05 | PCWP | |

| 40 | HASH UNIQUE | | 84139 | 1561K| 20M| 3024 (2)| 00:00:01 | Q1,05 | PCWP | |

| 41 | PX RECEIVE | | 84139 | 1561K| | 3024 (2)| 00:00:01 | Q1,05 | PCWP | |

| 42 | PX SEND HASH | :TQ10004 | 84139 | 1561K| | 3024 (2)| 00:00:01 | Q1,04 | P->P | HASH |

| 43 | HASH UNIQUE | | 84139 | 1561K| 20M| 3024 (2)| 00:00:01 | Q1,04 | PCWP | |

| 44 | PX BLOCK ITERATOR | | | | | | | Q1,04 | PCWC | |

| 46 | PX SELECTOR | | | | | | | Q1,05 | PCWP | |

| 47 | HASH UNIQUE | | 672K| 15M| 25M| 62471 (1)| 00:00:03 | Q1,05 | PCWP | |

|* 48 | TABLE ACCESS FULL | W_AP_XACT_F | 672K| 15M| | 57725 (1)| 00:00:03 | Q1,05 | PCWP | |

| 49 | BUFFER SORT | | | | | | | Q1,07 | PCWC | |

| 50 | PX RECEIVE | | 9 | 225 | | 4 (0)| 00:00:01 | Q1,07 | PCWP | |

| 51 | PX SEND HYBRID HASH | :TQ10001 | 9

Added on Oct 25 2018

#general-database-discussions

19 comments

762 views