This is demo about Query which performing Parallel but takes lot of time.
Configuring Table parallel degree / instances degree incorrect can result adverse effect.
Platform is Exadata-X2.
Elapsed time has dropped from 2 minutes to 20 seconds.
Lets see What explain plan says .
As you can see cost are too much high for full table scan.
Lets see actual execution Time .
Almost 2 minutes.
What are these tables and why it takes so long even if its running PARALLEL.
Culprit is Table DUMMY_TPLGY, which is going for FULL TABLE SCAN and is very big.
----------Now Tables are in PARALLEL but degree is one Only.
So lets change it default and let oracle decide how to execute it.
---New execution plan has low cost as compare to previous plan even Execution time also dropped drastically .
---Now Lets run and Measure execution time.
Here we go only 20 seconds from 2 minutes.
Configuring Table parallel degree / instances degree incorrect can result adverse effect.
Platform is Exadata-X2.
Elapsed time has dropped from 2 minutes to 20 seconds.
SELECT count(1) FROM DUMMY_TPLGY WHERE (DUMMY_TPLGY.DVIC_TYPE = 'optical_node' OR DUMMY_TPLGY.DVIC_TYPE = 'amplifier') AND DUMMY_TPLGY.SNPSHT_DTM in (SELECT SNPSHT_DTM FROM DUMMY_TPLGY_CTRL WHERE AVL_IND = 'Y' AND SNPSHT_DTM > (SELECT MAX (DAY_KEY) FROM RCM_CTRL WHERE TBL_NAME = 'DUMMY_DIM' AND AVL_IND = 'Y'));
Lets see What explain plan says .
set timing on set time on set long 999999 set lines 180 set pages 555 12:24:11 SQL> explain plan for 12:24:11 2 SELECT count(1) 12:24:11 3 FROM DUMMY_TPLGY 12:24:11 4 WHERE (DUMMY_TPLGY.DVIC_TYPE = 'optical_node' 12:24:11 5 OR DUMMY_TPLGY.DVIC_TYPE = 'amplifier') 12:24:11 6 AND DUMMY_TPLGY.SNPSHT_DTM in 12:24:11 7 (SELECT SNPSHT_DTM 12:24:11 8 FROM DUMMY_TPLGY_CTRL 12:24:11 9 WHERE AVL_IND = 'Y' 12:24:11 10 AND SNPSHT_DTM > 12:24:11 11 (SELECT MAX (DAY_KEY) 12:24:11 12 FROM RCM_CTRL 12:24:11 13 WHERE TBL_NAME = 'DUMMY_DIM' AND AVL_IND = 'Y')); Explained. Elapsed: 00:00:02.05
As you can see cost are too much high for full table scan.
12:24:15 SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 231818000
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 50332 (1)| 00:10:04 | | | | | |
| 1 | SORT AGGREGATE | | 1 | 26 | | | | | | | |
| 2 | NESTED LOOPS | | 3434K| 85M| 49646 (1)| 00:09:56 | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| DUMMY_TPLGY_CTRL | 3 | 30 | 1 (0)| 00:00:01 | | | | | |
|* 4 | INDEX RANGE SCAN | XPK_DUMMY_TPLGY_CTRL | 1 | | 1 (0)| 00:00:01 | | | | | |
| 5 | SORT AGGREGATE | | 1 | 26 | | | | | | | |
| 6 | PX COORDINATOR | | | | | | | | | | |
| 7 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 26 | | | | | Q1,00 | P->S | QC (RAND)|
| 8 | SORT AGGREGATE | | 1 | 26 | | | | | Q1,00 | PCWP | |
| 9 | PX BLOCK ITERATOR | | 382 | 9932 | 24 (0)| 00:00:01 | | | Q1,00 | PCWC | |
|* 10 | TABLE ACCESS FULL | RCM_CTRL | 382 | 9932 | 24 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 11 | PARTITION LIST ITERATOR | | 1174K| 17M| 16548 (1)| 00:03:19 | KEY | KEY | | | |
|* 12 | TABLE ACCESS FULL | DUMMY_TPLGY | 1174K| 17M| 16548 (1)| 00:03:19 | KEY | KEY | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("AVL_IND"='Y')
4 - access("SNPSHT_DTM"> (SELECT MAX(SYS_OP_CSR(SYS_OP_MSR(MAX("DAY_KEY")),0)) FROM "RCM"."RCM_CTRL" "RCM_CTRL" WHERE
"TBL_NAME"='DUMMY_DIM' AND "AVL_IND"='Y'))
10 - filter("TBL_NAME"='DUMMY_DIM' AND "AVL_IND"='Y')
12 - filter(("DUMMY_TPLGY"."DVIC_TYPE"='amplifier' OR "DUMMY_TPLGY"."DVIC_TYPE"='optical_node') AND
"DUMMY_TPLGY"."SNPSHT_DTM"="SNPSHT_DTM")
Note
-----
- dynamic sampling used for this statement (level=6)
33 rows selected.
Lets see actual execution Time .
Elapsed: 00:00:01.01 12:24:28 SQL> 12:24:28 SQL> 12:28:14 SQL> SELECT count(1) 12:28:15 2 FROM DUMMY_TPLGY 12:28:15 3 WHERE (DUMMY_TPLGY.DVIC_TYPE = 'optical_node' 12:28:15 4 OR DUMMY_TPLGY.DVIC_TYPE = 'amplifier') 12:28:15 5 AND DUMMY_TPLGY.SNPSHT_DTM in 12:28:15 6 (SELECT SNPSHT_DTM 12:28:15 7 FROM DUMMY_TPLGY_CTRL 12:28:15 8 WHERE AVL_IND = 'Y' 12:28:15 9 AND SNPSHT_DTM > 12:28:15 10 (SELECT MAX (DAY_KEY) 12:28:15 11 FROM RCM_CTRL 12:28:15 12 WHERE TBL_NAME = 'DUMMY_DIM' AND AVL_IND = 'Y')); COUNT(1) ---------- 3574152 Elapsed: 00:02:07.34 12:30:23 SQL>
Almost 2 minutes.
What are these tables and why it takes so long even if its running PARALLEL.
Culprit is Table DUMMY_TPLGY, which is going for FULL TABLE SCAN and is very big.
SQL> SELECT OWNER,TABLE_NAME,DEGREE,INSTANCES,PARTITIONED,NUM_ROWS FROM DBA_TABLES WHERE TABLE_NAME IN ('DUMMY_TPLGY','RCM_CTRL','DUMMY_TPLGY_CTRL');
OWNER TABLE_NAME DEGREE INSTANCES PARTITIONED NUM_ROWS
---------- -------------------- ---------- ---------- --------------- ----------
RCM RCM_CTRL DEFAULT DEFAULT NO 4597
TNC DUMMY_TPLGY 1 1 YES 277101036
TNC DUMMY_TPLGY_CTRL 1 1 NO 59
----------Now Tables are in PARALLEL but degree is one Only.
So lets change it default and let oracle decide how to execute it.
SQL> ALTER TABLE TNC.DUMMY_TPLGY PARALLEL (DEGREE DEFAULT INSTANCES DEFAULT);
Table altered.
SQL> ALTER TABLE TNC.DUMMY_TPLGY_CTRL PARALLEL (DEGREE DEFAULT INSTANCES DEFAULT);
Table altered.
SQL> SELECT OWNER,TABLE_NAME,DEGREE,INSTANCES,PARTITIONED,NUM_ROWS FROM DBA_TABLES WHERE TABLE_NAME IN ('DUMMY_TPLGY','RCM_CTRL','DUMMY_TPLGY_CTRL');
OWNER TABLE_NAME DEGREE INSTANCES PARTITIONED NUM_ROWS
---------- -------------------- ---------- ---------- --------------- ----------
RCM RCM_CTRL DEFAULT DEFAULT NO 4597
TNC DUMMY_TPLGY DEFAULT DEFAULT YES 277101036
TNC DUMMY_TPLGY_CTRL DEFAULT DEFAULT NO 59
---New execution plan has low cost as compare to previous plan even Execution time also dropped drastically .
12:34:38 SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3873636908
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib|
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 1746 (1)| 00:00:21 | | | | | |
| 1 | SORT AGGREGATE | | 1 | 26 | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ20001 | 1 | 26 | | | | | Q2,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 26 | | | | | Q2,01 | PCWP | |
| 5 | NESTED LOOPS | | 3434K| 85M| 1723 (1)| 00:00:21 | | | Q2,01 | PCWP | |
| 6 | BUFFER SORT | | | | | | | | Q2,01 | PCWC | |
| 7 | PX RECEIVE | | | | | | | | Q2,01 | PCWP | |
| 8 | PX SEND BROADCAST | :TQ20000 | | | | | | | | S->P | BROADCAST |
|* 9 | TABLE ACCESS BY INDEX ROWID| DUMMY_TPLGY_CTRL | 3 | 30 | 1 (0)| 00:00:01 | | | | | |
|* 10 | INDEX RANGE SCAN | XPK_DUMMY_TPLGY_CTRL | 1 | | 1 (0)| 00:00:01 | | | | | |
| 11 | SORT AGGREGATE | | 1 | 26 | | | | | | | |
| 12 | PX COORDINATOR | | | | | | | | | | |
| 13 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 26 | | | | |Q1,00 | P->S | QC (RAND) |
| 14 | SORT AGGREGATE | | 1 | 26 | | | | |Q1,00 | PCWP | |
| 15 | PX BLOCK ITERATOR | | 382 | 9932 | 24 (0)| 00:00:01 | | |Q1,00 | PCWC | |
|* 16 | TABLE ACCESS FULL | RCM_CTRL | 382 | 9932 | 24 (0)| 00:00:01 | | |Q1,00 | PCWP | |
| 17 | PX BLOCK ITERATOR | | 1174K| 17M| 574 (1)| 00:00:07 | KEY | KEY |Q2,01 | PCWC | |
|* 18 | TABLE ACCESS FULL | DUMMY_TPLGY | 1174K| 17M| 574 (1)| 00:00:07 | KEY | KEY |Q2,01 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter("AVL_IND"='Y')
10 - access("SNPSHT_DTM"> (SELECT MAX(SYS_OP_CSR(SYS_OP_MSR(MAX("DAY_KEY")),0)) FROM "RCM"."RCM_CTRL" "RCM_CTRL" WHERE "TBL_NAME"='DUMMY_DIM'
AND "AVL_IND"='Y'))
16 - filter("TBL_NAME"='DUMMY_DIM' AND "AVL_IND"='Y')
18 - filter(("DUMMY_TPLGY"."DVIC_TYPE"='amplifier' OR "DUMMY_TPLGY"."DVIC_TYPE"='optical_node') AND "DUMMY_TPLGY"."SNPSHT_DTM"="SNPSHT_DTM")
---Now Lets run and Measure execution time.
SQL> ALTER SESSION ENABLE PARALLEL QUERY; Session altered. SQL> SET TIME ON 12:51:00 SQL> SET TIMING ON; 12:51:03 SQL> 12:51:15 SQL> SELECT count(1) 12:51:16 2 FROM DUMMY_TPLGY 12:51:16 3 WHERE (DUMMY_TPLGY.DVIC_TYPE = 'optical_node' 12:51:16 4 OR DUMMY_TPLGY.DVIC_TYPE = 'amplifier') 12:51:16 5 AND DUMMY_TPLGY.SNPSHT_DTM in 12:51:16 6 (SELECT SNPSHT_DTM 12:51:16 7 FROM DUMMY_TPLGY_CTRL 12:51:16 8 WHERE AVL_IND = 'Y' 12:51:16 9 AND SNPSHT_DTM > 12:51:16 10 (SELECT MAX (DAY_KEY) 12:51:16 11 FROM RCM_CTRL 12:51:16 12 WHERE TBL_NAME = 'DUMMY_DIM' AND AVL_IND = 'Y')); COUNT(1) ---------- 3574152 Elapsed: 00:00:20.89 12:51:37 SQL>
Here we go only 20 seconds from 2 minutes.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.