Hello,
Here below a test case.
I have a query which take more than 3 second. It base on a view statement which use a distinct.
SQL> set autot traceonly explain stat
SQL> SELECT FILL.USERID,FILL.WORKLISTNAME,FILL.WLRECNAME,FILL.DESCR
FROM PS_RB_WF_TRANS_VW FILL
WHERE USERID IN (SELECT B.RB_WF_GRP_NAME FROM PS_RB_WF_GRP_PLIST B WHERE B.USERID = '326923');
Elapsed: 00:00:03.10
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14043 Card=1813 Bytes=346283)
1 0 NESTED LOOPS (SEMI) (Cost=14043 Card=1813 Bytes=346283)
2 1 VIEW OF 'PS_RB_WF_TRANS_VW' (Cost=14043 Card=828664 Bytes=136729560)
3 2 SORT (UNIQUE) (Cost=14043 Card=828664 Bytes=88667048)
4 3 HASH JOIN (Cost=715 Card=828664 Bytes=88667048)
5 4 TABLE ACCESS (FULL) OF 'PS_RB_WL_GRID_DFN' (Cost=2 Card=39 Bytes=1950)
6 4 INDEX (FAST FULL SCAN) OF 'IDX011' (UNIQUE) (Cost=709 Card=828664 Bytes=47233848)
7 1 INDEX (UNIQUE SCAN) OF 'PS_RB_WF_GRP_PLIST' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
20188 consistent gets
0 physical reads
0 redo size
866 bytes sent via SQL*Net to client
650 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> set autot off
SQL> select dbms_metadata.get_ddl('VIEW','PS_RB_WF_TRANS_VW',user) from dual;
DBMS_METADATA.GET_DDL('VIEW','PS_RB_WF_TRANS_VW',USER)
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYSADM"."PS_RB_WF_TRANS_VW" ("USERID", "WORKLISTNAME", "WLRECNAME", "DESCR") AS
SELECT DISTINCT A.OPRID , A.WORKLISTNAME , B.WLRECNAME , B.DESCR
FROM PSWORKLIST A , PS_RB_WL_GRID_DFN B
WHERE B.WORKLISTNAME = A.WORKLISTNAME
AND A.INSTSTATUS <=2
AND A.BUSPROCNAME <> 'Administer Workflow'
Because of the time is not low enough, I try to tune it. Just to try, I replace the DISTINCT by a GROUP BY on all the selected columns. The time is now less than 0.1 second.
SQL> CREATE OR REPLACE FORCE VIEW "SYSADM"."PS_RB_WF_TRANS_VW" ("USERID", "WORKLISTNAME", "WLRECNAME", "DESCR") AS
2 SELECT A.OPRID , A.WORKLISTNAME , B.WLRECNAME , B.DESCR
3 FROM PSWORKLIST A , PS_RB_WL_GRID_DFN B
4 WHERE B.WORKLISTNAME = A.WORKLISTNAME
5 AND A.INSTSTATUS <=2
6 AND A.BUSPROCNAME <> 'Administer Workflow'
7 GROUP BY A.OPRID , A.WORKLISTNAME , B.WLRECNAME , B.DESCR;
View created.
Elapsed: 00:00:00.01
SQL> set autot traceonly explain stat
SQL> SELECT FILL.USERID,FILL.WORKLISTNAME,FILL.WLRECNAME,FILL.DESCR
FROM PS_RB_WF_TRANS_VW FILL
WHERE USERID IN (SELECT B.RB_WF_GRP_NAME FROM PS_RB_WF_GRP_PLIST B WHERE B.USERID = '326923');
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=1229 Bytes=163457)
1 0 SORT (GROUP BY) (Cost=64 Card=1229 Bytes=163457)
2 1 HASH JOIN (Cost=39 Card=1229 Bytes=163457)
3 2 TABLE ACCESS (FULL) OF 'PS_RB_WL_GRID_DFN' (Cost=2 Card=39 Bytes=1950)
4 2 NESTED LOOPS (Cost=36 Card=1229 Bytes=102007)
5 4 SORT (UNIQUE)
6 5 INDEX (FAST FULL SCAN) OF 'PS_RB_WF_GRP_PLIST' (UNIQUE) (Cost=2 Card=2 Bytes=52)
7 4 INDEX (RANGE SCAN) OF 'IDX011' (UNIQUE) (Cost=16 Card=604 Bytes=34428)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
125 consistent gets
0 physical reads
0 redo size
866 bytes sent via SQL*Net to client
650 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
The explain plan is a quite different and seems better in the second case. How explain that ?
Thanks,
By the way, Oracle is 9.2.0.8 and stats are up-to-date (tables and indexes).
Message was edited by:
user583229