Home » RDBMS Server » Performance Tuning » Range Scan Index (Oracle 11g, any platforms)
Range Scan Index [message #671404] Tue, 28 August 2018 22:17 Go to previous message
trantuananh24hg
Messages: 741
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;
Now, I take a brief information
* 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

 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: Exporting AWR data in a csv file
Next Topic: Index on order by column
Goto Forum:
  


Current Time: Tue Jan 28 10:43:49 CST 2020