hi Team,
I am struggling in one of query tunning , below are details assoicated with , please analyse and recommend on it .
SELECT *
FROM (SELECT
/*+ INDEX(A INDX14_TABLE1) INDEX(B IDX51_TABLE2) */
*
FROM TABLE1 A,
Table2 B
WHERE EXE.process_id = EXT.pin
AND activity = :"SYS_B_0"
AND ( status = :"SYS_B_1"
OR status IS NULL )
AND status = :"SYS_B_2"
AND process = :"SYS_B_3"
AND ((( zone = :"SYS_B_4" )))
ORDER BY process DESC,
fork DESC)
WHERE ROWNUM <= :"SYS_B_5"
above query does 31K of LIO and due to rownum it brings 10- 12 rows depending upon value in ROWNUM passed , without rownum , total rows are 500~600 returned byt LIO is too high around 30K
For testing pupose i executed query as below :(by removing hint it genrates similar plan )
SELECT
/*+ INDEX(A INDX14_TABLE1) INDEX(B IDX51_TABLE2) */
*
FROM TABLE1 A,
Table2 B
WHERE A.process = b.pin
AND activity = :"SYS_B_0"
AND ( status = :"SYS_B_1"
OR status IS NULL )
AND status = :"SYS_B_2"
AND process = :"SYS_B_3"
AND ((( zone = :"SYS_B_4" )))
ORDER BY process DESC,
fork DESC
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.52 | 31088 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.52 | 31088 | | | |
|* 2 | HASH JOIN | | 1 | 30154 | 160 |00:00:00.52 | 31088 | 1106K| 1106K| 2467K (0)|
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TABLE1 | 1 | 30154 | 594 |00:00:00.01 | 372 | | | |
|* 4 | INDEX RANGE SCAN | INDX14_TABLE1 | 1 | 8898 | 594 |00:00:00.01 | 33 | | | |
|* 5 | INDEX FAST FULL SCAN | IDX51_TABLE2 | 1 | 713K| 1075K|00:00:00.40 | 30716 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------
INDX14_TABLE1 index con column : process,activity,status, ( these rows filter nad bring 120 rows from table1
Note : their two colun process and process_id , index used here is having column (process,activity,status)
IDX51_TABLE2: pin ,zone
for column zone data is skew , but histogram will not benefit
my question :
It should execute in nested loop , i am not sure why it is going in HASH join
any futher way to tune it
TABLE2 brings more row , but from table1 rows are around 120 , then it should using nested loop , is something wrong with stats here ??
What really matters here is how "good" the index on pin,zone is on table2.
Lets say I get 120 rows back from table1 as you say. That means 120 probes into table2. If the IDX51_TABLE2 is nice and selective, than 120 probes might not be expensive. But if the index is not great, then we are multiplying "not great" by 120.
You can test out the performance yourself by using a hint like:
LEADING(a b)
USE_NL_WITH_INDEX(b IDX51_TABLE2)
to force the nested loop from table1 (a) into table2 (b) and do some performance comparisons.
If it *is* indeed better, than look at using an outline or sql plan management to lock in the plan.
If it is *not* better, than you might need to look at alternative/modified index definitions if possible.