Guys,
I need help in finding out what is happening with this query.
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
Query 1 with the hint.
SQL> explain plan for
2 select /*+ leading(t1253794,t92473,t399322,t414335,t398195,t146170,t414406) */
3 sum(T1253794.BUDGET_LOC_AMT) as c1,
4 sum(case when T146170.BUDGET_NAME = 'HPCA FC' then T1253794.BUDGET_LOC_AMT else 0 end ) as c2,
5 T414335.HIER1_CODE as c3,
6 T414335.HIER1_NAME as c4,
7 T414335.HIER2_CODE as c5
8 from
9 WC_MCAL_DAY_MD T399322 /* Dim_WC_MCAL_DAY_MD_Fiscal_Day */ ,
10 WC_HIERARCHY_MD T414335 /* Dim_WC_HIERARCHY_MD_Segment1 */ ,
11 W_PROFIT_CENTER_D T92473 /* Dim_W_PROFIT_CENTER_D */ ,
12 WC_INT_ORG_MD T398195 /* Dim_WC_INT_ORG_MD_Company */ ,
13 W_BUDGET_D T146170 /* Dim_W_BUDGET_D */ ,
14 WC_GL_ACTUAL_BUDGET_F T1253794 /* Fact_WC_GL_ACTUAL_BUDGET_F_HPCA */
15 where ( T399322.ROW_WID = T1253794.BALANCE_DT_WID and T92473.ROW_WID = T1253794.PROFIT_CENTER_WID and T398195.X_ORG_ID = T1253794.ORG_ID and T146170.ROW_WID = T1253794.BUDGET_WID and T399322.MCAL_CAL_WID = 1000 and T398195.ORG_NAME = 'Health Professional Councils Authority' and T92473.X_PROFIT_CENTER_NUM_NAME = '9453 - Dental Council' and T398195.BUSINESS_GROUP_FLG = 'N' and T398195.COMPANY_FLG = 'Y' and T399322.MCAL_PER_NAME_YEAR = '2020' and T414335.HIER1_CODE = 'YALLPL' and T414335.HIER2_CODE = 'YREV' and T414335.HIER_CODE = '1011324' and T414335.GL_SEGMENT_WID = T1253794.GL_SEGMENT1_WID and T1253794.LEDGER_WID = 14001 )
16 group by T414335.HIER1_CODE, T414335.HIER1_NAME, T414335.HIER2_CODE
17 order by c4, c3, c5
18 ;
Explained.
Elapsed: 00:00:00.71
SQL> @xp
Plan hash value: 1639162002
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 214 | 1723K (1)| 00:04:30 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10003 | 1 | 214 | 1723K (1)| 00:04:30 | | | Q1,03 | P->S | QC (ORDER) |
| 3 | SORT GROUP BY | | 1 | 214 | 1723K (1)| 00:04:30 | | | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | 1 | 214 | 1723K (1)| 00:04:30 | | | Q1,03 | PCWP | |
| 5 | PX SEND RANGE | :TQ10002 | 1 | 214 | 1723K (1)| 00:04:30 | | | Q1,02 | P->P | RANGE |
| 6 | HASH GROUP BY | | 1 | 214 | 1723K (1)| 00:04:30 | | | Q1,02 | PCWP | |
| 7 | NESTED LOOPS | | 1 | 214 | 1723K (1)| 00:04:30 | | | Q1,02 | PCWP | |
| 8 | NESTED LOOPS | | 1 | 214 | 1723K (1)| 00:04:30 | | | Q1,02 | PCWP | |
| 9 | NESTED LOOPS | | 1 | 195 | 1723K (1)| 00:04:30 | | | Q1,02 | PCWP | |
|* 10 | HASH JOIN | | 1 | 142 | 1723K (1)| 00:04:30 | | | Q1,02 | PCWP | |
| 11 | PX RECEIVE | | 1 | 49 | 32 (13)| 00:00:01 | | | Q1,02 | PCWP | |
| 12 | PX SEND BROADCAST | :TQ10000 | 1 | 49 | 32 (13)| 00:00:01 | | | Q1,00 | P->P | BROADCAST |
| 13 | PX BLOCK ITERATOR | | 1 | 49 | 32 (13)| 00:00:01 | | | Q1,00 | PCWC | |
|* 14 | TABLE ACCESS INMEMORY FULL | WC_HIERARCHY_MD | 1 | 49 | 32 (13)| 00:00:01 | | | Q1,00 | PCWP | |
|* 15 | HASH JOIN | | 3309 | 300K| 1723K (1)| 00:04:30 | | | Q1,02 | PCWP | |
| 16 | PART JOIN FILTER CREATE | :BF0000 | 253 | 4554 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 17 | PX RECEIVE | | 253 | 4554 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 18 | PX SEND BROADCAST | :TQ10001 | 253 | 4554 | 2 (0)| 00:00:01 | | | Q1,01 | P->P | BROADCAST |
| 19 | PX BLOCK ITERATOR | | 253 | 4554 | 2 (0)| 00:00:01 | | | Q1,01 | PCWC | |
|* 20 | TABLE ACCESS INMEMORY FULL | WC_MCAL_DAY_MD | 253 | 4554 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
|* 21 | HASH JOIN | | 3315 | 242K| 1723K (1)| 00:04:30 | | | Q1,02 | PCWP | |
| 22 | PX BLOCK ITERATOR | | 1776K| 59M| 1723K (1)| 00:04:30 |KEY(SQ)|KEY(SQ)| Q1,02 | PCWC | |
|* 23 | TABLE ACCESS STORAGE FULL | WC_GL_ACTUAL_BUDGET_F | 1776K| 59M| 1723K (1)| 00:04:30 | 1 |1048575| Q1,02 | PCWP | |
|* 24 | TABLE ACCESS STORAGE FULL | W_PROFIT_CENTER_D | 1 | 40 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
|* 25 | TABLE ACCESS BY INDEX ROWID BATCHED| WC_INT_ORG_MD | 1 | 53 | 1 (0)| 00:00:01 | | | Q1,02 | PCWP | |
|* 26 | INDEX RANGE SCAN | WC_INT_ORG_MD_M1 | 95 | | 0 (0)| 00:00:01 | | | Q1,02 | PCWP | |
|* 27 | INDEX UNIQUE SCAN | W_BUDGET_D_P1 | 1 | | 0 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 28 | TABLE ACCESS BY INDEX ROWID | W_BUDGET_D | 1 | 19 | 0 (0)| 00:00:01 | | | Q1,02 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("T414335"."GL_SEGMENT_WID"="T1253794"."GL_SEGMENT1_WID")
14 - inmemory("T414335"."HIER2_CODE"='YREV' AND "T414335"."HIER1_CODE"='YALLPL' AND "T414335"."HIER_CODE"='1011324')
filter("T414335"."HIER2_CODE"='YREV' AND "T414335"."HIER1_CODE"='YALLPL' AND "T414335"."HIER_CODE"='1011324')
15 - access("T399322"."ROW_WID"="T1253794"."BALANCE_DT_WID")
20 - inmemory("T399322"."MCAL_PER_NAME_YEAR"='2020' AND "T399322"."MCAL_CAL_WID"=1000)
filter("T399322"."MCAL_PER_NAME_YEAR"='2020' AND "T399322"."MCAL_CAL_WID"=1000)
21 - access("T92473"."ROW_WID"="T1253794"."PROFIT_CENTER_WID")
23 - storage("T1253794"."LEDGER_WID"=14001)
filter("T1253794"."LEDGER_WID"=14001)
24 - storage("T92473"."X_PROFIT_CENTER_NUM_NAME"='9453 - Dental Council')
filter("T92473"."X_PROFIT_CENTER_NUM_NAME"='9453 - Dental Council')
25 - filter("T398195"."ORG_NAME"='Health Professional Councils Authority' AND "T398195"."X_ORG_ID"="T1253794"."ORG_ID" AND
"T398195"."BUSINESS_GROUP_FLG"='N')
26 - access("T398195"."COMPANY_FLG"='Y')
27 - access("T146170"."ROW_WID"="T1253794"."BUDGET_WID")
Note
-----
- Degree of Parallelism is 4 because of table property
- parallel scans affinitized for inmemory
Query 2 without the hint.
SQL> set timing on
SQL> explain plan for
2 select /* leading(t1253794,t92473,t399322,t414335,t398195,t146170,t414406) */
3 sum(T1253794.BUDGET_LOC_AMT) as c1,
4 sum(case when T146170.BUDGET_NAME = 'HPCA FC' then T1253794.BUDGET_LOC_AMT else 0 end ) as c2,
5 T414335.HIER1_CODE as c3,
6 T414335.HIER1_NAME as c4,
7 T414335.HIER2_CODE as c5
8 from
9 WC_MCAL_DAY_MD T399322 /* Dim_WC_MCAL_DAY_MD_Fiscal_Day */ ,
10 WC_HIERARCHY_MD T414335 /* Dim_WC_HIERARCHY_MD_Segment1 */ ,
11 W_PROFIT_CENTER_D T92473 /* Dim_W_PROFIT_CENTER_D */ ,
12 WC_INT_ORG_MD T398195 /* Dim_WC_INT_ORG_MD_Company */ ,
13 W_BUDGET_D T146170 /* Dim_W_BUDGET_D */ ,
14 WC_GL_ACTUAL_BUDGET_F T1253794 /* Fact_WC_GL_ACTUAL_BUDGET_F_HPCA */
15 where ( T399322.ROW_WID = T1253794.BALANCE_DT_WID and T92473.ROW_WID = T1253794.PROFIT_CENTER_WID and T398195.X_ORG_ID = T1253794.ORG_ID and T146170.ROW_WID = T1253794.BUDGET_WID and T399322.MCAL_CAL_WID = 1000 and T398195.ORG_NAME = 'Health Professional Councils Authority' and T92473.X_PROFIT_CENTER_NUM_NAME = '9453 - Dental Council' and T398195.BUSINESS_GROUP_FLG = 'N' and T398195.COMPANY_FLG = 'Y' and T399322.MCAL_PER_NAME_YEAR = '2020' and T414335.HIER1_CODE = 'YALLPL' and T414335.HIER2_CODE = 'YREV' and T414335.HIER_CODE = '1011324' and T414335.GL_SEGMENT_WID = T1253794.GL_SEGMENT1_WID and T1253794.LEDGER_WID = 14001 )
16 group by T414335.HIER1_CODE, T414335.HIER1_NAME, T414335.HIER2_CODE
17 order by c4, c3, c5
18 ;
Explained.
Elapsed: 00:00:17.77
SQL> @xp
Plan hash value: 727351767
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 214 | 1723K (1)| 00:04:30 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 1 | 214 | 1723K (1)| 00:04:30 | | | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT GROUP BY | | 1 | 214 | 1723K (1)| 00:04:30 | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 1 | 214 | 1723K (1)| 00:04:30 | | | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 1 | 214 | 1723K (1)| 00:04:30 | | | Q1,00 | P->P | RANGE |
| 6 | HASH GROUP BY | | 1 | 214 | 1723K (1)| 00:04:30 | | | Q1,00 | PCWP | |
| 7 | NESTED LOOPS | | 1 | 214 | 1723K (1)| 00:04:30 | | | Q1,00 | PCWP | |
| 8 | NESTED LOOPS | | 1 | 214 | 1723K (1)| 00:04:30 | | | Q1,00 | PCWP | |
| 9 | NESTED LOOPS | | 1 | 195 | 1723K (1)| 00:04:30 | | | Q1,00 | PCWP | |
| 10 | NESTED LOOPS | | 1 | 177 | 1723K (1)| 00:04:30 | | | Q1,00 | PCWP | |
| 11 | NESTED LOOPS | | 1 | 137 | 1723K (1)| 00:04:30 | | | Q1,00 | PCWP | |
|* 12 | HASH JOIN | | 1 | 84 | 1723K (1)| 00:04:30 | | | Q1,00 | PCWP | |
| 13 | JOIN FILTER CREATE | :BF0000 | 1 | 49 | 32 (13)| 00:00:01 | | | Q1,00 | PCWP | |
|* 14 | TABLE ACCESS INMEMORY FULL | WC_HIERARCHY_MD | 1 | 49 | 32 (13)| 00:00:01 | | | Q1,00 | PCWP | |
| 15 | JOIN FILTER USE | :BF0000 | 1776K| 59M| 1723K (1)| 00:04:30 | | | Q1,00 | PCWP | |
| 16 | PX BLOCK ITERATOR | | 1776K| 59M| 1723K (1)| 00:04:30 |KEY(SQ)|KEY(SQ)| Q1,00 | PCWC | |
|* 17 | TABLE ACCESS STORAGE FULL | WC_GL_ACTUAL_BUDGET_F | 1776K| 59M| 1723K (1)| 00:04:30 | KEY | KEY | Q1,00 | PCWP | |
|* 18 | TABLE ACCESS BY INDEX ROWID BATCHED| WC_INT_ORG_MD | 1 | 53 | 1 (0)| 00:00:01 | | | Q1,00 | PCWP | |
|* 19 | INDEX RANGE SCAN | WC_INT_ORG_MD_M1 | 95 | | 0 (0)| 00:00:01 | | | Q1,00 | PCWP | |
|* 20 | TABLE ACCESS BY INDEX ROWID | W_PROFIT_CENTER_D | 1 | 40 | 0 (0)| 00:00:01 | | | Q1,00 | PCWP | |
|* 21 | INDEX UNIQUE SCAN | W_PROFT_CNTR_D_P1 | 1 | | 0 (0)| 00:00:01 | | | Q1,00 | PCWP | |
|* 22 | TABLE ACCESS BY INDEX ROWID | WC_MCAL_DAY_MD | 1 | 18 | 0 (0)| 00:00:01 | | | Q1,00 | PCWP | |
|* 23 | INDEX UNIQUE SCAN | WC_MCAL_DAY_MD_P1 | 1 | | 0 (0)| 00:00:01 | | | Q1,00 | PCWP | |
|* 24 | INDEX UNIQUE SCAN | W_BUDGET_D_P1 | 1 | | 0 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 25 | TABLE ACCESS BY INDEX ROWID | W_BUDGET_D | 1 | 19 | 0 (0)| 00:00:01 | | | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
12 - access("T414335"."GL_SEGMENT_WID"="T1253794"."GL_SEGMENT1_WID")
14 - inmemory("T414335"."HIER2_CODE"='YREV' AND "T414335"."HIER1_CODE"='YALLPL' AND "T414335"."HIER_CODE"='1011324')
filter("T414335"."HIER2_CODE"='YREV' AND "T414335"."HIER1_CODE"='YALLPL' AND "T414335"."HIER_CODE"='1011324')
17 - storage("T1253794"."LEDGER_WID"=14001 AND SYS_OP_BLOOM_FILTER(:BF0000,"T1253794"."GL_SEGMENT1_WID"))
filter("T1253794"."LEDGER_WID"=14001 AND SYS_OP_BLOOM_FILTER(:BF0000,"T1253794"."GL_SEGMENT1_WID"))
18 - filter("T398195"."ORG_NAME"='Health Professional Councils Authority' AND "T398195"."X_ORG_ID"="T1253794"."ORG_ID" AND
"T398195"."BUSINESS_GROUP_FLG"='N')
19 - access("T398195"."COMPANY_FLG"='Y')
20 - filter("T92473"."X_PROFIT_CENTER_NUM_NAME"='9453 - Dental Council')
21 - access("T92473"."ROW_WID"="T1253794"."PROFIT_CENTER_WID")
22 - filter("T399322"."MCAL_PER_NAME_YEAR"='2020' AND "T399322"."MCAL_CAL_WID"=1000)
23 - access("T399322"."ROW_WID"="T1253794"."BALANCE_DT_WID")
24 - access("T146170"."ROW_WID"="T1253794"."BUDGET_WID")
Note
-----
- Degree of Parallelism is 4 because of table property
- parallel scans affinitized for inmemory
54 rows selected.
The issue with the above 2 queries is that when the hint is provided the explain plan is generated very quickly as apposed to query without the hint.( 0.71 sec vs 17.77 sec)
Please advise what is should look for in order to find what is going on.
There are similar queries that were taking around 90 seconds to generate the explain plan. But when i run the query it was taking approximately 98 seconds to finish.
Checked the sql monitoring in the enterprise manager the duration it was showing 12sec.
I don't have access on the server but can ask the dba's to provide the info.
Any help is appreciated.
Thanks
Alvinder