Hello All,
Good Afternoon!! Thought I will post some interesting findings of mine while I was given the task of reviewing and finding tuning scope for some complex pl/sql codes. In one of them, I found a weird way of doing a Left Outer Join against a table and they haven't used any hints typically used for joining operations (such as Ordered), but instead they used a Parallel with a table alias to the driven table, which I think is being rendered useless when it runs. This is what it looks like -
SELECT /*+ PARALLEL(V) */ DISTINCT
CASE WHEN y.u_dc_run_op IS NOT NULL THEN 1
ELSE 0 END
FROM (select 1 sw from dual) x
LEFT OUTER JOIN
(SELECT V.U_DC_RUN_OPT, v.u_dc_run_pend
FROM dview v, u_dc_process_ctrl u
WHERE v.u_dc_run_opt = u.opt
AND v.u_dc_run_pend = 1
AND u.process_wk = (TO_CHAR(SYSDATE,'WW') + 0)
AND TRIM(v.u_dc_run_opt) IS NOT NULL) y
On x.sw = y.u_dc_run_pend;
Oracle is running on - Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production.
The inner table itself is a result of another join against a 100M heavy table with another 100K light table (see below for their table definitions). Cost of the above query and Explain Plan results are as below -
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0rpsxqtvvcmsx, child number 0
-------------------------------------
SELECT /*+ PARALLEL(V) */ DISTINCT CASE WHEN
- y.u_dc_run_op IS NOT NULL THEN 1 ELSE 0 END FROM
(select 1 sw from dual) x LEFT OUTER JOIN
(SELECT V.U_DC_RUN_OPT_SET, v.u_dc_run_pend FROM
dview v, u_dc_process_ctrl u WHERE v.u_dc_run_opt =
- u.opt AND v.u_dc_run_pend = 1 AND
- u.process_wk = (TO_CHAR(SYSDATE,'WW') + 0) AND
TRIM(v.u_dc_run_opt) IS NOT NULL) y On x.sw =
- y.u_dc_run_pend
Plan hash value: 49153038
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1444K(100)| | | |
| 1 | HASH UNIQUE | | 1 | 19 | 1444K (3)| 00:01:55 | | |
| 2 | NESTED LOOPS OUTER | | 1 | 19 | 1444K (3)| 00:01:55 | | |
| 3 | TABLE ACCESS FULL | DUAL | 1 | 2 | 3 (0)| 00:00:01 | | |
|* 4 | VIEW | | 1 | 17 | 1444K (3)| 00:01:55 | | |
|* 5 | HASH JOIN | | 1 | 122 | 1444K (3)| 00:01:55 | | |
| 6 | TABLE ACCESS BY INDEX ROWID| U_DC_PROCESS_CTRL | 1 | 115 | 0 (0)| | | |
|* 7 | INDEX RANGE SCAN | U_DC_PROCESS_CTRL_PK | 1 | | 0 (0)| | | |
| 8 | PARTITION HASH ALL | | 283K| 1937K| 1444K (3)| 00:01:55 | 1 | 64 |
|* 9 | TABLE ACCESS FULL | DVIEW | 283K| 1937K| 1444K (3)| 00:01:55 | 1 | 64 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("Y"."U_DC_RUN_PEND"=1)
5 - access("V"."U_DC_RUN_OPT"="U"."OPT")
7 - access("U"."PROCESS_WK"=TO_NUMBER(TO_CHAR(SYSDATE@!,'WW'))+0)
9 - filter(("V"."U_DC_RUN_PEND"=1 AND TRIM("V"."U_DC_RUN_OPT") IS NOT NULL))
SQL_ID 0rpsxqtvvcmsx, child number 1
-------------------------------------
SELECT /*+ PARALLEL(V) */ DISTINCT CASE WHEN
- y.u_dc_run_opt IS NOT NULL THEN 1 ELSE 0 END FROM
(select 1 sw from dual) x LEFT OUTER JOIN
(SELECT V.U_DC_RUN_OPT, v.u_dc_run_pend FROM
dview v, u_dc_process_ctrl u WHERE v.u_dc_run_opt =
- u.opt AND v.u_dc_run_pend = 1 AND
- u.process_wk = (TO_CHAR(SYSDATE,'WW') + 0) AND
TRIM(v.u_dc_run_opt) IS NOT NULL) y On x.sw =
- y.u_dc_run_pend
Plan hash value: 49153038
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1444K(100)| | | |
| 1 | HASH UNIQUE | | 1 | 19 | 1444K (3)| 00:01:55 | | |
| 2 | NESTED LOOPS OUTER | | 1 | 19 | 1444K (3)| 00:01:55 | | |
| 3 | TABLE ACCESS FULL | DUAL | 1 | 2 | 3 (0)| 00:00:01 | | |
|* 4 | VIEW | | 1 | 17 | 1444K (3)| 00:01:55 | | |
|* 5 | HASH JOIN | | 1 | 122 | 1444K (3)| 00:01:55 | | |
| 6 | TABLE ACCESS BY INDEX ROWID| U_DC_PROCESS_CTRL | 4069 | 456K| 0 (0)| | | |
|* 7 | INDEX RANGE SCAN | U_DC_PROCESS_CTRL_PK | 1 | | 0 (0)| | | |
| 8 | PARTITION HASH ALL | | 5459K| 36M| 1444K (3)| 00:01:55 | 1 | 64 |
|* 9 | TABLE ACCESS FULL | DVIEW | 5459K| 36M| 1444K (3)| 00:01:55 | 1 | 64 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("Y"."U_DC_RUN_PEND"=1)
5 - access("V"."U_DC_RUN_OPT"="U"."OPT")
7 - access("U"."PROCESS_WK"=TO_NUMBER(TO_CHAR(SYSDATE@!,'WW'))+0)
9 - filter(("V"."U_DC_RUN_PEND"=1 AND TRIM("V"."U_DC_RUN_OPT") IS NOT NULL))
Note
-----
- cardinality feedback used for this statement
Here's the syntax how DView and u_dc_process_ctrl tables are created -
CREATE TABLE "A"."DVIEW"
( "DUNIT" VARCHAR2(50 CHAR),
"DGROUP" VARCHAR2(50 CHAR),
"LOC" VARCHAR2(50 CHAR),
"U_DC_RUN_OPT" VARCHAR2(50 CHAR) DEFAULT ' ',
"U_DC_RUN_PEND" NUMBER(1,0) DEFAULT 0
);
Please note that we have DView with 64 partitions on DUNIT as a key. Approximate volume of DVIEW is about 100M.
CREATE TABLE "A"."U_DC_PROCESS_CTRL"
( "PROCESS_WK" NUMBER(2,0),
"PROCESS_LVL" VARCHAR2(10 CHAR),
"ASSRT" VARCHAR2(50 CHAR),
"ITEM" VARCHAR2(50 CHAR),
"CNTRY" VARCHAR2(50 CHAR),
"OPT" VARCHAR2(50 CHAR),
"PROCESS_DT" DATE,
"EXCEPTION" VARCHAR2(255 CHAR) DEFAULT ' '
) ;
U_DC_PROCESS_CTRL is a non-partitioned table and has less than 10k records in it.
The problem is, this apparently simple query is running for 26 minutes and I think we can avoid doing the outer join or use an ORDERED hint instead of a PARALLEL hint to the query.
Appreciate any inputs to the above. Thanks much!!
Thanks,
Sanders.