There is a common misconception that Index access is the best access method for query execution. This approach leads to people concentrate on removing Full Table Scans (FTS) from the Query execution plan. I recently worked on a performance tuning issue where I found such scenario. Basically customer was trying to generate report for 3 years and query was running for more then 8 hours without producing any results. We were asked to look into this problem.
Following diagnostic data was collected to diagnose the issue.
1) 10046 trace at level 12
2) Execution plan from V$SQL_PLAN (Database version is 9.2)
3) Explain plan for offending query
From 10046 trace and V$session_wait, I found out query was waiting on “db file sequential read” wait event for table SCOTT_BILL. This indicated that we were reading this table using index rowid’s. Please find below explain plan for the problematic query.
SQL> @?/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 161 | 66436 |
| 1 | SORT ORDER BY | | 1 | 161 | 66433 |
|* 2 | FILTER | | | | |
| 3 | NESTED LOOPS | | 1 | 161 | 66424 |
| 4 | NESTED LOOPS | | 1 | 141 | 66421 |
| 5 | NESTED LOOPS | | 1 | 126 | 66419 |
| 6 | NESTED LOOPS | | 391 | 27761 | 65246 |
|* 7 | TABLE ACCESS FULL | SCOTT_PO | 21716 | 254K| 98 |
| 8 | TABLE ACCESS BY INDEX ROWID| SCOTT_POIC | 1 | 59 | 3 |
|* 9 | INDEX RANGE SCAN | SCOTT_POIC_MK3 | 1 | | 2 |
|* 10 | TABLE ACCESS BY INDEX ROWID | SCOTT_BILL | 1 | 55 | 3 |
|* 11 | INDEX RANGE SCAN | SCOTT_BILL_FK7 | 1 | | 2 |
|* 12 | TABLE ACCESS BY INDEX ROWID | SCOTT_CUST | 1 | 15 | 2 |
|* 13 | INDEX RANGE SCAN | SCOTT_CUST_FK1 | 1 | | 1 |
| 14 | TABLE ACCESS BY INDEX ROWID | SCOTT_DSST | 1 | 20 | 3 |
|* 15 | INDEX UNIQUE SCAN | SCOTT_DSST_PK | 1 | | 2 |
| 16 | SORT AGGREGATE | | 1 | 17 | |
| 17 | FIRST ROW | | 1 | 17 | 4 |
|* 18 | INDEX RANGE SCAN (MIN/MAX)| SCOTT_DSST_PK | 166M| | 4 |
| 19 | SORT AGGREGATE | | 1 | 36 | |
|* 20 | INDEX RANGE SCAN | SCOTT_POIC_UK1 | 1 | 36 | 3 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SYS_ALIAS_5"."POIC_SEQ_NO"= (SELECT /*+ */ MAX("B"."POIC_SEQ_NO")
FROM "MAT"."SCOTT_POIC" "B" WHERE "B"."POIC_PO_SEQ_NO"=:B1 AND "B"."POIC_PO_NO"=:B2
AND "B"."POIC_ITEM_CODE"=:B3 AND "B"."POIC_PITM_SEQ_NO"=:B4 AND
NVL("B"."POIC_RQMT_NO",0)=NVL(:B5,0) AND "B"."POIC_CTRT_TYPE"='BAS'))
7 - filter(NVL("SCOTT_PO"."PO_COMP_CODE",'01')='01')
9 - access("SCOTT_PO"."PO_NO"="SYS_ALIAS_5"."POIC_PO_NO" AND
"SCOTT_PO"."PO_SEQ_NO"="SYS_ALIAS_5"."POIC_PO_SEQ_NO" AND
"SYS_ALIAS_5"."POIC_CTRT_TYPE"='BAS')
filter("SYS_ALIAS_5"."POIC_CTRT_TYPE"='BAS')
10 - filter("SYS_ALIAS_6"."BILL_ASRV_DATE">=TO_DATE('2008-04-01 00:00:00',
'yyyy-mm-dd hh24:mi:ss') AND "SYS_ALIAS_6"."BILL_ASRV_DATE"<=TO_DATE('2008-11-30
23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND NVL("SYS_ALIAS_6"."BILL_COMP_CODE",'01')='01
' AND "SYS_ALIAS_6"."BILL_SRV_TYPE"='S' AND
"SYS_ALIAS_6"."BILL_EXCISE_FORM_SERIES_FLAG" IS NULL AND
NVL("SYS_ALIAS_6"."BILL_EXCISE_FORM_SERIES_FLAG",'X')<>'N' AND
"SYS_ALIAS_6"."BILL_INER_CODE" IS NULL AND
"SYS_ALIAS_6"."BILL_ITEM_CODE"="SYS_ALIAS_5"."POIC_ITEM_CODE" AND
"SYS_ALIAS_6"."BILL_PITM_SEQ_NO"="SYS_ALIAS_5"."POIC_PITM_SEQ_NO")
11 - access("SYS_ALIAS_6"."BILL_PO_NO"="SYS_ALIAS_5"."POIC_PO_NO" AND
"SYS_ALIAS_6"."BILL_PO_SEQ_NO"="SYS_ALIAS_5"."POIC_PO_SEQ_NO")
filter(NVL("SYS_ALIAS_6"."BILL_RQMT_NO",0)=NVL("SYS_ALIAS_5"."POIC_RQMT_NO",0
))
12 - filter(NVL("SCOTT_CUST"."CUST_COMP_CODE",'01')='01' AND
"SCOTT_CUST"."CUST_INER_CODE" IS NULL)
13 - access("SCOTT_CUST"."CUST_SRV_NO"="SYS_ALIAS_6"."BILL_SRV_NO")
15 - access("SYS_ALIAS_6"."BILL_SRV_NO"="C"."DSST_SRV_NO" AND
"C"."DSST_PROC_GROUP"=1 AND "C"."DSST_PROCESS"='PSRV' AND "C"."DSST_SEQ_NO"=
(SELECT /*+ */ MAX("D"."DSST_SEQ_NO") FROM "MAT"."SCOTT_DSST" "D" WHERE
"D"."DSST_PROCESS"='PSRV' AND "D"."DSST_PROC_GROUP"=1 AND "D"."DSST_SRV_NO"=:B1))
18 - access("D"."DSST_SRV_NO"=:B1 AND "D"."DSST_PROC_GROUP"=1 AND
"D"."DSST_PROCESS"='PSRV')
20 - access("B"."POIC_PO_NO"=:B1 AND "B"."POIC_PO_SEQ_NO"=:B2 AND
"B"."POIC_ITEM_CODE"=:B3 AND "B"."POIC_PITM_SEQ_NO"=:B4 AND
"B"."POIC_CTRT_TYPE"='BAS')
filter("B"."POIC_ITEM_CODE"=:B1 AND "B"."POIC_PITM_SEQ_NO"=:B2 AND
NVL("B"."POIC_RQMT_NO",0)=NVL(:B3,0) AND "B"."POIC_CTRT_TYPE"='BAS')
Looking at explain plan (and plan from V$SQL_PLAN) confirmed that we were using index SCOTT_BILL_FK7. Plan shows that CBO estimates only 1 row for this operation. But since we have been on this wait even for close to 8 hours, it was clear that we are accessing more then 1 row.Normally this query was run for small range(date column) and used to complete in 10 mins. e.g Below is explain plan for the same query with 4 day’s range.
SQL> @?/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 161 | 28876 |
| 1 | SORT ORDER BY | | 1 | 161 | 28873 |
|* 2 | FILTER | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID | SCOTT_CUST | 1 | 15 | 2 |
| 4 | NESTED LOOPS | | 1 | 161 | 28864 |
| 5 | NESTED LOOPS | | 1 | 146 | 28862 |
| 6 | NESTED LOOPS | | 1 | 134 | 28861 |
| 7 | NESTED LOOPS | | 56 | 4200 | 28581 |
|* 8 | TABLE ACCESS BY INDEX ROWID | SCOTT_BILL | 2123 | 114K| 22212 |
|* 9 | INDEX RANGE SCAN | SCOTT_BILL_MK10 | 26081 | | 85 |
| 10 | TABLE ACCESS BY INDEX ROWID | SCOTT_DSST | 1 | 20 | 3 |
|* 11 | INDEX UNIQUE SCAN | SCOTT_DSST_PK | 1 | | 2 |
| 12 | SORT AGGREGATE | | 1 | 17 | |
| 13 | FIRST ROW | | 1 | 17 | 4 |
|* 14 | INDEX RANGE SCAN (MIN/MAX)| SCOTT_DSST_PK | 166M| | 4 |
|* 15 | TABLE ACCESS BY INDEX ROWID | SCOTT_POIC | 1 | 59 | 5 |
|* 16 | INDEX RANGE SCAN | SCOTT_POIC_MK3 | 1 | | 2 |
|* 17 | TABLE ACCESS BY INDEX ROWID | SCOTT_PO | 1 | 12 | 1 |
|* 18 | INDEX UNIQUE SCAN | SCOTT_PO_PK | 1 | | |
|* 19 | INDEX RANGE SCAN | SCOTT_CUST_FK1 | 1 | | 1 |
| 20 | SORT AGGREGATE | | 1 | 36 | |
|* 21 | INDEX RANGE SCAN | SCOTT_POIC_UK1 | 1 | 36 | 3 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SYS_ALIAS_5"."POIC_SEQ_NO"= (SELECT /*+ */ MAX("B"."POIC_SEQ_NO") FROM
"MAT"."SCOTT_POIC" "B" WHERE "B"."POIC_PO_SEQ_NO"=:B1 AND "B"."POIC_PO_NO"=:B2 AND
"B"."POIC_ITEM_CODE"=:B3 AND "B"."POIC_PITM_SEQ_NO"=:B4 AND
NVL("B"."POIC_RQMT_NO",0)=NVL(:B5,0) AND "B"."POIC_CTRT_TYPE"='BAS'))
3 - filter(NVL("SCOTT_CUST"."CUST_COMP_CODE",'01')='01' AND
"SCOTT_CUST"."CUST_INER_CODE" IS NULL)
8 - filter(NVL("SYS_ALIAS_6"."BILL_COMP_CODE",'01')='01' AND
"SYS_ALIAS_6"."BILL_SRV_TYPE"='S' AND "SYS_ALIAS_6"."BILL_EXCISE_FORM_SERIES_FLAG" IS
NULL AND NVL("SYS_ALIAS_6"."BILL_EXCISE_FORM_SERIES_FLAG",'X')<>'N' AND
"SYS_ALIAS_6"."BILL_INER_CODE" IS NULL)
9 - access("SYS_ALIAS_6"."BILL_ASRV_DATE">=TO_DATE('2008-04-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "SYS_ALIAS_6"."BILL_ASRV_DATE"<=TO_DATE('2008-04-05 23:59:59',
'yyyy-mm-dd hh24:mi:ss'))
11 - access("SYS_ALIAS_6"."BILL_SRV_NO"="C"."DSST_SRV_NO" AND "C"."DSST_PROC_GROUP"=1
AND "C"."DSST_PROCESS"='PSRV' AND "C"."DSST_SEQ_NO"= (SELECT /*+ */
MAX("D"."DSST_SEQ_NO") FROM "MAT"."SCOTT_DSST" "D" WHERE "D"."DSST_PROCESS"='PSRV' AND
"D"."DSST_PROC_GROUP"=1 AND "D"."DSST_SRV_NO"=:B1))
14 - access("D"."DSST_SRV_NO"=:B1 AND "D"."DSST_PROC_GROUP"=1 AND
"D"."DSST_PROCESS"='PSRV')
15 - filter("SYS_ALIAS_6"."BILL_PITM_SEQ_NO"="SYS_ALIAS_5"."POIC_PITM_SEQ_NO" AND
NVL("SYS_ALIAS_6"."BILL_RQMT_NO",0)=NVL("SYS_ALIAS_5"."POIC_RQMT_NO",0))
16 - access("SYS_ALIAS_6"."BILL_PO_NO"="SYS_ALIAS_5"."POIC_PO_NO" AND
"SYS_ALIAS_6"."BILL_PO_SEQ_NO"="SYS_ALIAS_5"."POIC_PO_SEQ_NO" AND
"SYS_ALIAS_6"."BILL_ITEM_CODE"="SYS_ALIAS_5"."POIC_ITEM_CODE" AND
"SYS_ALIAS_5"."POIC_CTRT_TYPE"='BAS')
17 - filter(NVL("SCOTT_PO"."PO_COMP_CODE",'01')='01')
18 - access("SCOTT_PO"."PO_NO"="SYS_ALIAS_5"."POIC_PO_NO" AND
"SCOTT_PO"."PO_SEQ_NO"="SYS_ALIAS_5"."POIC_PO_SEQ_NO")
19 - access("SCOTT_CUST"."CUST_SRV_NO"="SYS_ALIAS_6"."BILL_SRV_NO")
21 - access("B"."POIC_PO_NO"=:B1 AND "B"."POIC_PO_SEQ_NO"=:B2 AND
"B"."POIC_ITEM_CODE"=:B3 AND "B"."POIC_PITM_SEQ_NO"=:B4 AND "B"."POIC_CTRT_TYPE"='BAS')
filter("B"."POIC_ITEM_CODE"=:B1 AND "B"."POIC_PITM_SEQ_NO"=:B2 AND
NVL("B"."POIC_RQMT_NO",0)=NVL(:B3,0) AND "B"."POIC_CTRT_TYPE"='BAS')
Note: cpu costing is off
We can see that we used SCOTT_BILL_MK10 index here which was on the date column. This is also clear from predicate information available from explain plan.
9 - access("SYS_ALIAS_6"."BILL_ASRV_DATE">=TO_DATE('2008-04-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "SYS_ALIAS_6"."BILL_ASRV_DATE"<=TO_DATE('2008-04-05 23:59:59',
'yyyy-mm-dd hh24:mi:ss'))
Coming back to our case, we see that index SCOTT_BILL_FK7 is being used here.This index is created on BILL_PO_NO, BILL_PO_SEQ_NO and BILL_RQMT_NO columns.
From explain plan, we see that following condition is used due to which we access the index
11 - access("SYS_ALIAS_6"."BILL_PO_NO"="SYS_ALIAS_5"."POIC_PO_NO" AND
"SYS_ALIAS_6"."BILL_PO_SEQ_NO"="SYS_ALIAS_5"."POIC_PO_SEQ_NO")
filter(NVL("SYS_ALIAS_6"."BILL_RQMT_NO",0)=NVL("SYS_ALIAS_5"."POIC_RQMT_NO",0
))
Checking the column values, we see following
COLUMN_NAME NUM_DISTINCT NUM_NULLS ------------------------------ ------------ ---------- BILL_PO_NO 31841 636 BILL_PO_SEQ_NO 4 1296 BILL_RQMT_NO 437 27333280
I believe lot of null records present in the table along with NVL function usage is causing this issue. We can see from above data, that column BILL_RQMT_NO has 27 million null records. As B-Tree index does not index Null columns(B-tree indexes index null columns too if index is a concatenated index and the other column has non-null value. Refer to this article from Richard Foote). CBO assumed that only 1 row will be returned and it choose index SCOTT_BILL_FK7 as best access method. But because of NVL function, this returns more rows instead of just one row. This is my hypothesis and not sure , if its correct 🙂
As a quick fix , I suggested using FULL hint on SCOTT_BILL table so that we can perform a Full table Scan (FTS). This helped and query completed in 28 mins. If we look at long term solution, having a default value of 0 for this column could also be looked at. But all these things need to be decided in consideration with application design.
This is also a reminder to a fact that index access is not always the best access method. It is suitable if we are looking for very small data set and could become problem if data set increases.Whereas Full table Scan (FTS) perform better in such cases as we perform multiblock I/O.
Recent Comments