Home » RDBMS Server » Performance Tuning » Range Scan Index (Oracle 11g, any platforms)
| Range Scan Index [message #671404] |
Tue, 28 August 2018 22:17  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Sometimes ago, I asked for solution of my poor SQL, and I have got mistake to reply, because I did not find solution. And then, comeback with this still exists.
I have got a problem with SQL tuning, the SQL is following:
SELECT *
FROM ( SELECT R.REG_ID, R.PACKAGE_ID, R.SERVICE_ID,
R.CHARGE_IMMEDIATE, R.MSISDN,
TO_CHAR(R.EXPIRE_DATE,'yyyy/MM/dd hh24:mi:ss') EXPIRE_DATE,
NVL(R.LAST_RETRY_DATE,SYSDATE) LAST_RENEW_DATE,
NVL(R.RETRY_COUNT,0) RETRY_COUNT,
R.SUBPACKAGE_ID, 0 NEXT_CHARGE_LEVEL,
TO_CHAR(NVL(R.NEXT_RETRY_DATE,SYSDATE),'yyyymmddHH24') NEXT_RETRY_DATE
FROM SDP.REG R, SDP.SERVICE_LIST SL
WHERE 1 = 1
AND R.SERVICE_ID = SL.SERVICE_ID
AND R.MOD100 >= 10
AND R.MOD100 <= 250
AND R.START_RENEW_FLG = 0
AND (R.EXPIRE_DATE <= SYSDATE
AND NVL(R.LAST_RETRY_DATE,SYSDATE-1) <= TRUNC(SYSDATE))
AND R.AUTO_RENEW =1
AND SL.SERVICE_RENEW_IP = '10.144.17.69'
ORDER BY RETRY_COUNT ASC;
* Description of REG
Name Null? Type
----------------------------------------------------------------------------------- -------- ---------------------------------------
REG_ID NOT NULL NUMBER
SERVICE_ID NOT NULL NUMBER
PACKAGE_ID NOT NULL NUMBER
LAST_RENEW_ID NUMBER
MSISDN NOT NULL VARCHAR2(30)
EXPIRE_DATE DATE
MOD100 NUMBER
CHARGE_IMMEDIATE NUMBER
IN_RETRYING NUMBER
AUTO_RENEW NOT NULL NUMBER
START_DATE DATE
UPDATE_DATE DATE
RETRY_COUNT NUMBER
LAST_RETRY_DATE DATE
SUBPACKAGE_ID NUMBER
ID_OLD VARCHAR2(50)
START_RENEW_FLG NUMBER(38)
RETRY_SEND_COUNT NUMBER
NEXT_SEND_DATE DATE
NEXT_CHARGE_LEVEL NUMBER
NEXT_RETRY_DATE DATE
FIRST_MT_FLG NUMBER
LAST_RENEW_DATE DATE
*Description of SERVICE_LIST
sdpadm@SDP> desc sdp.service_list
Name Null? Type
----------------------------------------------------------------------------------- -------- ---------------------------------------
SERVICE_ID NOT NULL NUMBER
SERVICE_NAME NOT NULL VARCHAR2(100)
SERVICE_CODE NOT NULL VARCHAR2(100)
SERVICE_DESC VARCHAR2(300)
SERVICE_URL VARCHAR2(200)
SERVICE_TRADEMARK VARCHAR2(1)
SERVICE_RENEW_ORD NUMBER(1)
SERVICE_RENEW_IP VARCHAR2(15)
SERVICE_RENEW_PROTOCOL VARCHAR2(15)
SERVICE_NOTI_SUB VARCHAR2(1)
SERVICE_CSKH_NO VARCHAR2(20)
SERVICE_CSKH_PRICE VARCHAR2(150)
CHARGE_IMMEDIATE VARCHAR2(1)
SERVICE_STATUS VARCHAR2(10)
* The total rows of thoes tables
sdpadm@SDP> select count(1) from sdp.reg;
COUNT(1)
----------
5865821
sdpadm@SDP> select count(1) from sdp.service_list;
COUNT(1)
----------
346
sdpadm@SDP>
* The indexes exists on
sdpadm@SDP> select owner, table_name, index_name, index_type
2 from dba_indexes
3 where owner='SDP'
4 and table_name in ('REG','SERVICE_LIST');
OWNER TABLE_NAME INDEX_NAME INDEX_TYPE
------------------------------ ------------------------------ ------------------------------ ---------------------------
SDP REG INDX_REG_SRVPCKGID NORMAL
SDP REG INDX_REG_MOD100 NORMAL
SDP REG REGSDP_PK NORMAL
SDP REG INDX_REG_EXPDATE NORMAL
SDP REG INDX_REG_MSISDN NORMAL/REV
SDP REG INDX_REG_RETRYCOUNT NORMAL
SDP REG INDX_REG_LASTRNID NORMAL
SDP REG REGSDP_CST NORMAL
SDP REG INDX_REGSDP_AUTORENEW NORMAL/REV
SDP SERVICE_LIST SERVICE_LIST_PK NORMAL
SDP SERVICE_LIST INDX_SRVLIST_SRVRENEWIP NORMAL
SDP SERVICE_LIST SERVICE_LIST__UN NORMAL
12 rows selected.
sdpadm@SDP>
* High water mark of table, both of REG and SERVICE_LIST did not show the over HWM.
sdpadm@SDP> @tblhwm
Enter value for table_name: REG
old 14: where dts.table_name='&&table_name'
new 14: where dts.table_name='REG'
Enter value for owner: SDP
old 15: and dse.owner='&&owner'
new 15: and dse.owner='SDP'
BLKS_USED AVG_SPACE NUM_ROWS AVG_ROW_LEN EMPTY_BLKS ALLOC_BLKS PCT_HWM DATA_IN_BYTES DATA_IN_BLKS MOD_DATA_IN_BLKS PCT_SPC_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ------------- ------------ ---------------- ------------
101815 0 5866027 112 0 102376 .9945202 656995024 80199.5879 100249.485 .979228382
sdpadm@SDP> @tblhwm
Enter value for table_name: SERVICE_LIST
old 14: where dts.table_name='&&table_name'
new 14: where dts.table_name='SERVICE_LIST'
Enter value for owner: SDP
old 15: and dse.owner='&&owner'
new 15: and dse.owner='SDP'
BLKS_USED AVG_SPACE NUM_ROWS AVG_ROW_LEN EMPTY_BLKS ALLOC_BLKS PCT_HWM DATA_IN_BYTES DATA_IN_BLKS MOD_DATA_IN_BLKS PCT_SPC_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ------------- ------------ ---------------- ------------
5 0 339 98 0 8 .625 33222 4.05541992 5.0692749 .633659363
sdpadm@SDP>
-------------------------------------------------------------------------------------------------------------------------------------
1- Oracle will ignore an index for a number of reasons, but they boil down to two possibilities:
+ The structure of the SQL is such that Oracle cannot use an index
+ Oracle's cost based optimizer is electing not to use the index
SELECT R.REG_ID, R.PACKAGE_ID, R.SERVICE_ID,
R.CHARGE_IMMEDIATE, R.MSISDN,
TO_CHAR(R.EXPIRE_DATE,'yyyy/MM/dd hh24:mi:ss') EXPIRE_DATE,
NVL(R.LAST_RETRY_DATE,SYSDATE) LAST_RENEW_DATE,
NVL(R.RETRY_COUNT,0) RETRY_COUNT,
R.SUBPACKAGE_ID, 0 NEXT_CHARGE_LEVEL,
TO_CHAR(NVL(R.NEXT_RETRY_DATE,SYSDATE),'yyyymmddHH24') NEXT_RETRY_DATE
FROM SDP.REG R, SDP.SERVICE_LIST SL
WHERE 1 = 1
AND R.SERVICE_ID = SL.SERVICE_ID
AND R.MOD100 >= 10
AND R.MOD100 <= 250
AND R.START_RENEW_FLG = 0
AND (R.EXPIRE_DATE <= SYSDATE
AND NVL(R.LAST_RETRY_DATE,SYSDATE-1) <= TRUNC(SYSDATE))
AND R.AUTO_RENEW =1
AND SL.SERVICE_RENEW_IP = '10.144.17.69'
ORDER BY RETRY_COUNT ASC;
Execution Plan
----------------------------------------------------------
Plan hash value: 3354283033
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1165K| 104M| | 52953 (1)| 00:10:36 |
| 1 | SORT ORDER BY | | 1165K| 104M| 140M| 52953 (1)| 00:10:36 |
|* 2 | HASH JOIN | | 1165K| 104M| | 27827 (1)| 00:05:34 |
|* 3 | TABLE ACCESS FULL| SERVICE_LIST | 334 | 6346 | | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| REG | 1165K| 83M| | 27821 (1)| 00:05:34 |
--------------------------------------------------------------------------------------------
Let I make more simple SQL
sdpadm@SDP> SELECT R.REG_ID, R.PACKAGE_ID, R.SERVICE_ID,
2 R.CHARGE_IMMEDIATE, R.MSISDN,
3 TO_CHAR(R.EXPIRE_DATE,'yyyy/MM/dd hh24:mi:ss') EXPIRE_DATE,
4 NVL(R.LAST_RETRY_DATE,SYSDATE) LAST_RENEW_DATE,
5 NVL(R.RETRY_COUNT,0) RETRY_COUNT,
6 R.SUBPACKAGE_ID, 0 NEXT_CHARGE_LEVEL,
7 TO_CHAR(NVL(R.NEXT_RETRY_DATE,SYSDATE),'yyyymmddHH24') NEXT_RETRY_DATE
8 FROM SDP.REG R
9 WHERE R.MOD100 between 10 and 250;
Execution Plan
----------------------------------------------------------
Plan hash value: 546024852
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5269K| 346M| 27704 (1)| 00:05:33 |
|* 1 | TABLE ACCESS FULL| REG | 5269K| 346M| 27704 (1)| 00:05:33 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("R"."MOD100">=10 AND "R"."MOD100"<=250)
2- To find out which of these is true for my SQL, add an INDEX hint to SQL.
sdpadm@SDP> SELECT /*+ INDEX(r, INDX_REG_MOD100) */ R.REG_ID, R.PACKAGE_ID, R.SERVICE_ID,
2 R.CHARGE_IMMEDIATE, R.MSISDN,
3 TO_CHAR(R.EXPIRE_DATE,'yyyy/MM/dd hh24:mi:ss') EXPIRE_DATE,
4 NVL(R.LAST_RETRY_DATE,SYSDATE) LAST_RENEW_DATE,
5 NVL(R.RETRY_COUNT,0) RETRY_COUNT,
6 R.SUBPACKAGE_ID, 0 NEXT_CHARGE_LEVEL,
7 TO_CHAR(NVL(R.NEXT_RETRY_DATE,SYSDATE),'yyyymmddHH24') NEXT_RETRY_DATE
8 FROM SDP.REG R
9 WHERE R.MOD100 between 10 and 250;
Execution Plan
----------------------------------------------------------
Plan hash value: 2910863622
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5269K| 346M| 4752K (1)| 15:50:27 |
| 1 | TABLE ACCESS BY INDEX ROWID| REG | 5269K| 346M| 4752K (1)| 15:50:27 |
|* 2 | INDEX RANGE SCAN | INDX_REG_MOD100 | 5269K| | 10744 (1)| 00:02:09 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("R"."MOD100">=10 AND "R"."MOD100"<=250)
Yes, the index being used now, but the index is now being used with a UNIQUE or RANGE scan, it means that the Cost Based Optimiser doesn't think the index is very useful. Why?
-- Have I checked the statistics? Let try
sdpadm@SDP> set autotrace off
sdpadm@SDP> begin
2 dbms_stats.gather_table_stats(
3 ownname=>'SDP',
4 tabname=>'REG',
5 method_opt=>'for all indexed columns size auto',
6 cascade=>true);
7 end;
8 /
PL/SQL procedure successfully completed.
OK, so, is the performance better?
sdpadm@SDP> SELECT R.REG_ID, R.PACKAGE_ID, R.SERVICE_ID,
2 R.CHARGE_IMMEDIATE, R.MSISDN,
3 TO_CHAR(R.EXPIRE_DATE,'yyyy/MM/dd hh24:mi:ss') EXPIRE_DATE,
4 NVL(R.LAST_RETRY_DATE,SYSDATE) LAST_RENEW_DATE,
5 NVL(R.RETRY_COUNT,0) RETRY_COUNT,
6 R.SUBPACKAGE_ID, 0 NEXT_CHARGE_LEVEL,
7 TO_CHAR(NVL(R.NEXT_RETRY_DATE,SYSDATE),'yyyymmddHH24') NEXT_RETRY_DATE
8 FROM SDP.REG R
9 WHERE R.MOD100 >= 10 AND R.MOD100 <= 250
10 /
Execution Plan
----------------------------------------------------------
Plan hash value: 546024852
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5279K| 347M| 27704 (1)| 00:05:33 |
|* 1 | TABLE ACCESS FULL| REG | 5279K| 347M| 27704 (1)| 00:05:33 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("R"."MOD100">=10 AND "R"."MOD100"<=250)
sdpadm@SDP> SELECT /*+ INDEX(r, INDX_REG_MOD100) */ R.REG_ID, R.PACKAGE_ID, R.SERVICE_ID,
2 R.CHARGE_IMMEDIATE, R.MSISDN,
3 TO_CHAR(R.EXPIRE_DATE,'yyyy/MM/dd hh24:mi:ss') EXPIRE_DATE,
4 NVL(R.LAST_RETRY_DATE,SYSDATE) LAST_RENEW_DATE,
5 NVL(R.RETRY_COUNT,0) RETRY_COUNT,
6 R.SUBPACKAGE_ID, 0 NEXT_CHARGE_LEVEL,
7 TO_CHAR(NVL(R.NEXT_RETRY_DATE,SYSDATE),'yyyymmddHH24') NEXT_RETRY_DATE
8 FROM SDP.REG R
9 WHERE R.MOD100 >= 10 AND R.MOD100 <= 250
10 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2910863622
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5279K| 347M| 4934K (1)| 16:27:00 |
| 1 | TABLE ACCESS BY INDEX ROWID| REG | 5279K| 347M| 4934K (1)| 16:27:00 |
|* 2 | INDEX RANGE SCAN | INDX_REG_MOD100 | 5476K| | 11215 (1)| 00:02:15 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("R"."MOD100">=10 AND "R"."MOD100"<=250)
sdpadm@SDP> set autotrace off
It's just poor.
If the performance is still poor, then Oracle was probably right to ignore the index - I almost certainly have a Range Scan problem.
Inefficient range scans can have a number of causes:
+ Low cardinality index key
+ Use of range predicates such as <, >, LIKE, and BETWEEN. <<-- I think I got this problem
+ Partial use of a concatenated index
May you help me to solve this?
Thank you very much.
[Updated on: Tue, 28 August 2018 22:27] Report message to a moderator |
|
|
|
Current Time: Fri Apr 19 17:45:59 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
