Hi, I'm working on a statement. My database is:
SQL*Plus: Release 11.2.0.1.0 Production on Mi Jan 16 18:22:34 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Verbunden mit:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management and OLAP options
on a virtualized OEL5.
I have this statement
WITH paco_paci AS
(SELECT paco_id
, cogr.name AS cogr_name
, cogr_id
, pave_id
, coin_id
, COIN.NAME AS COIN_NAME
FROM pac_zuo_paco_paci paci
JOIN pac_pave_cogr_values ppcv USING (paco_id)
JOIN pac_coding_group cogr USING (cogr_id)
JOIN pac_zuo_pave_paco_iot paco USING (paco_id)
JOIN pac_coding_instance_tab coin USING (paco_id))
SELECT cogr_id
FROM paco_paci src
-- left join paco_paci trg using(cogr_id, coin_name)
WHERE src.pave_id = :src_pave_id
-- and trg.pave_id = :trg_pave_id
this returns quick.
This is the execution plan
SELECT STATEMENT ALL_ROWSCost: 19 Bytes: 1.992 Cardinality: 24
13 NESTED LOOPS
11 NESTED LOOPS Cost: 19 Bytes: 1.992 Cardinality: 24
9 NESTED LOOPS Cost: 16 Bytes: 159 Cardinality: 3
6 NESTED LOOPS Cost: 15 Bytes: 102 Cardinality: 3
4 NESTED LOOPS Cost: 8 Bytes: 312 Cardinality: 12
1 INDEX RANGE SCAN INDEX (UNIQUE) PACS.PAC_PAPC_PK Cost: 1 Bytes: 168 Cardinality: 12
3 TABLE ACCESS BY INDEX ROWID TABLE PACS.PAC_PAVE_COGR_VALUES Cost: 1 Bytes: 12 Cardinality: 1
2 INDEX UNIQUE SCAN INDEX (UNIQUE) PACS.PACO_PK Cost: 1 Cardinality: 1
5 INDEX RANGE SCAN INDEX PACS.PAPV_PACO_ID Cost: 1 Bytes: 8 Cardinality: 1
8 TABLE ACCESS BY INDEX ROWID TABLE PACS.PAC_CODING_GROUP Cost: 1 Bytes: 19 Cardinality: 1
7 INDEX UNIQUE SCAN INDEX (UNIQUE) PACS.COGR_PK Cost: 1 Cardinality: 1
10 INDEX RANGE SCAN INDEX PACS.COIN_PACO_ID_I Cost: 1 Cardinality: 8
12 TABLE ACCESS BY INDEX ROWID TABLE PACS.PAC_CODING_INSTANCE_TAB Cost: 1 Bytes: 240 Cardinality: 8
Activating the commented parts in the statement slowes it down and the execution plan turns into this:
SELECT STATEMENT ALL_ROWSCost: 113.901 Bytes: 498.872.068 Cardinality: 2.900.419
23 TEMP TABLE TRANSFORMATION
17 LOAD AS SELECT SYS_TEMP_0FD9D69C5_8BA4CCE8
16 VIEW SYS. Cost: 68.595 Bytes: 165.323.883 Cardinality: 2.900.419
15 HASH JOIN Cost: 68.595 Bytes: 188.527.235 Cardinality: 2.900.419
13 VIEW SYS. Cost: 9.000 Bytes: 13.260.310 Cardinality: 378.866
12 HASH JOIN Cost: 9.000 Bytes: 15.533.506 Cardinality: 378.866
10 VIEW SYS. Cost: 5.533 Bytes: 10.229.247 Cardinality: 378.861
9 HASH JOIN Cost: 5.533 Bytes: 11.744.691 Cardinality: 378.861
4 VIEW VIEW PACS.index$_join$_004 Cost: 3 Bytes: 4.617 Cardinality: 243
3 HASH JOIN
1 INDEX FAST FULL SCAN INDEX (UNIQUE) PACS.COGR_NAME_UK Cost: 1 Bytes: 4.617 Cardinality: 243
2 INDEX FAST FULL SCAN INDEX (UNIQUE) PACS.COGR_PK Cost: 1 Bytes: 4.617 Cardinality: 243
8 VIEW SYS. Cost: 5.529 Bytes: 4.546.332 Cardinality: 378.861
7 HASH JOIN Cost: 5.529 Bytes: 7.577.220 Cardinality: 378.861
5 INDEX FAST FULL SCAN INDEX PACS.PAPV_PACO_ID Cost: 267 Bytes: 3.030.888 Cardinality: 378.861
6 TABLE ACCESS FULL TABLE PACS.PAC_PAVE_COGR_VALUES Cost: 3.272 Bytes: 17.118.000 Cardinality: 1.426.500
11 INDEX FAST FULL SCAN INDEX (UNIQUE) PACS.PAC_PAPC_PK Cost: 1.002 Bytes: 19.969.474 Cardinality: 1.426.391
14 TABLE ACCESS FULL TABLE PACS.PAC_CODING_INSTANCE_TAB Cost: 37.409 Bytes: 321.333.510 Cardinality: 10.711.117
22 HASH JOIN Cost: 45.306 Bytes: 498.872.068 Cardinality: 2.900.419
19 VIEW PACS. Cost: 9.198 Bytes: 249.436.034 Cardinality: 2.900.419
18 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_0FD9D69C5_8BA4CCE8 Cost: 9.198 Bytes: 165.323.883 Cardinality: 2.900.419
21 VIEW PACS. Cost: 9.198 Bytes: 249.436.034 Cardinality: 2.900.419
20 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_0FD9D69C5_8BA4CCE8 Cost: 9.198 Bytes: 165.323.883 Cardinality: 2.900.419
The question is:
how do I tune the statement so that is uses the first plan twice instead of this ... HashJoin approach?
My Options are hints and Stored Outlines. I cannot change DB parameters
Hints I already tried in the subselect are use_nl, ordered, leading, and rule.
any suggestions?
bye
TPD