I have given the query details below. The oracle version used in Oracle 9.2. The details about this query are that HISTORY_VIEW_NOW is a view and it is very large. The columns begin_inst and end_inst have indexes on them.
Using bind variables in the query and very slow execution:
-
-
SELECT *
-
FROM HISTORY_VIEW_NOW
-
WHERE begin_inst <= :endTime
-
AND end_inst >= :beginTime
-
AND family = 'ABC';
-
-
Explain plan:
-
-
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
-
-
SELECT STATEMENT Optimizer Mode=CHOOSE 3 3396
-
HASH JOIN 3 441 3396
-
HASH JOIN 6 444 103
-
TABLE ACCESS BY INDEX ROWID USER1.TABLE1 4 180 4
-
INDEX RANGE SCAN USER1.ENT_IDX1 4 2
-
VIEW USER1.MNT_TABLE1 1 K 52 K 99
-
UNION-ALL
-
HASH JOIN OUTER 630 25 K 6
-
TABLE ACCESS FULL USER1.TABLE1 630 7 K 4
-
TABLE ACCESS FULL USER1.TABLE2 507 14 K 1
-
HASH JOIN 107 2 K 44
-
VIEW 107 1 K 39
-
SORT UNIQUE 107 1 K 39
-
INDEX FAST FULL SCAN USER1.TABLE2_U01 507 5 K 1
-
TABLE ACCESS FULL USER1.TABLE1 630 7 K 4
-
HASH JOIN 503 16 K 45
-
TABLE ACCESS FULL USER1.TABLE1 630 7 K 4
-
VIEW 503 10 K 40
-
SORT UNIQUE 503 14 K 40
-
TABLE ACCESS FULL USER1.TABLE2 503 14 K 1
-
TABLE ACCESS FULL USER1.TABLE1 629 11 K 4
-
VIEW 4 K 304 K 3292
-
UNION-ALL
-
TABLE ACCESS BY INDEX ROWID USER1.HISTORY_TABLE 2 K 101 K 1643
-
INDEX RANGE SCAN USER1.OEE_H_BEGIN_IDX 7 K 25
-
TABLE ACCESS BY INDEX ROWID USER1.HISTORY_TABLE 2 K 74 K 1643
-
INDEX RANGE SCAN USER1.OEE_H_BEGIN_IDX 7 K 25
-
FILTER
-
TABLE ACCESS FULL USER1.TABLE3 51 1 K 3
-
FILTER
-
TABLE ACCESS FULL USER1.TABLE3 38 1 K 3
-
Without using bind variables in the query and very fast execution: (I am using the same values in this query as used for the bind variables above)
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
-
-
SELECT STATEMENT Optimizer Mode=CHOOSE 1 120
-
HASH JOIN 1 147 120
-
HASH JOIN 6 444 103
-
TABLE ACCESS BY INDEX ROWID user1.table1 4 180 4
-
INDEX RANGE SCAN user1.ENT_IDX1 4 2
-
VIEW user1.MNT_table1 1 K 52 K 99
-
UNION-ALL
-
HASH JOIN OUTER 630 25 K 6
-
TABLE ACCESS FULL user1.table1 630 7 K 4
-
TABLE ACCESS FULL user1.table2 507 14 K 1
-
HASH JOIN 107 2 K 44
-
VIEW 107 1 K 39
-
SORT UNIQUE 107 1 K 39
-
INDEX FAST FULL SCAN user1.table2_U01 507 5 K 1
-
TABLE ACCESS FULL user1.table1 630 7 K 4
-
HASH JOIN 503 16 K 45
-
TABLE ACCESS FULL user1.table1 630 7 K 4
-
VIEW 503 10 K 40
-
SORT UNIQUE 503 14 K 40
-
TABLE ACCESS FULL user1.table2 503 14 K 1
-
TABLE ACCESS FULL user1.table1 629 11 K 4
-
VIEW 1 K 128 K 16
-
UNION-ALL
-
TABLE ACCESS BY INDEX ROWID user1.history_table 4 192 5
-
INDEX RANGE SCAN user1.OEE_H_END_IDX 4 3
-
TABLE ACCESS BY INDEX ROWID user1.history_table 4 152 5
-
INDEX RANGE SCAN user1.OEE_H_END_IDX 4 3
-
FILTER
-
TABLE ACCESS FULL user1.table3 1 K 39 K 3
-
FILTER
-
TABLE ACCESS FULL user1.table3 769 21 K 3
-
Note that the cost for the query that uses the bind variables is much higher than the cost of the query without bind variables. I also tried the same query with the underlying table alone and still the execution time was long.
The modified query with the base table of the HISTORY_VIEW_NOW view is below.
-
-
SELECT *
-
FROM HISTORY_TABLE
-
WHERE begin_inst <= :endTime
-
AND end_inst >= :beginTime
-
-
--Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
-
-
SELECT STATEMENT Optimizer Mode=CHOOSE 2 K 1643
-
TABLE ACCESS BY INDEX ROWID USER1.HISTORY_TABLE 2 K 110 K 1643
-
INDEX RANGE SCAN USER1.OEE_H_BEGIN_IDX 7 K 25
-
-
--The modified query without bind variables (and using the same values as used ---for the bind variables) produced the explain plan below.
-
-
--Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
-
-
SELECT STATEMENT Optimizer Mode=CHOOSE 4 5
-
TABLE ACCESS BY INDEX ROWID USER1.HISTORY_TABLE 4 208 5
-
INDEX RANGE SCAN USER1.OEE_H_END_IDX 4 3
-
Any pointers to this problem would be very helpful.
Thanks.