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 next message
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;
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

Re: Range Scan Index [message #671409 is a reply to message #671404] Wed, 29 August 2018 01:12 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
One more thing: I often follow orafaq, but it is not comfortable to load in mobile.

Report message to a moderator

Re: Range Scan Index [message #671412 is a reply to message #671404] Wed, 29 August 2018 01:55 Go to previous messageGo to next message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
Can you show the index columns, not just the index name? It looks to me as though your use of functions (both explicit and implicit) is suppressing index usage. For example, this

AND (R.EXPIRE_DATE <= SYSDATE

would be indexable if R.EXPIRE_DATE is a date column and is indexd. But in fact R.REXPIRE_DATE is this,

TO_CHAR(R.EXPIRE_DATE,'yyyy/MM/dd hh24:mi:ss') EXPIRE_DATE

where you have converted the date to a string. There may be a index on EXPIRE_DATE, but is there an index on the TO_CHAR of EXPIRE date? Furthermore, you are comparing the string to SYSDATE, which is a DATE. That is impossible, so Oracle has to use an implicit TO_DATE to convert it back.

You have several cases like that.

I would rewrite the code to get rd of all the implicit type casting, and remove as many functions as possible. Then indexes may become usable.

[Updated on: Wed, 29 August 2018 01:56]

Report message to a moderator

Re: Range Scan Index [message #671414 is a reply to message #671412] Wed, 29 August 2018 02:42 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Dear John
These are columns and indexes association 
sdpadm@SDP> set linesize 150
sdpadm@SDP> col column_name format a25
sdpadm@SDP> col table_name format a9
sdpadm@SDP> col index_name format a30
sdpadm@SDP> col column_expression format a19
sdpadm@SDP> SELECT ic.column_name, ic.table_name, ic.index_name, ie.column_expression
  2  FROM all_ind_columns ic
  3  LEFT JOIN all_ind_expressions ie
  4    ON ie.index_owner  = ic.index_owner
  5    AND ie.index_name  = ic.index_name
  6    AND ie.column_position = ic.column_position
  7  WHERE ic.index_owner  = 'SDP'
  8    AND ic.table_name  = 'REG'
  9    ORDER BY ic.column_name asc;

COLUMN_NAME               TABLE_NAM INDEX_NAME                     COLUMN_EXPRESSION
------------------------- --------- ------------------------------ -------------------
AUTO_RENEW                REG       INDX_REGSDP_AUTORENEW
EXPIRE_DATE               REG       INDX_REG_EXPDATE
LAST_RENEW_ID             REG       INDX_REG_LASTRNID
MOD100                    REG       INDX_REG_MOD100
MSISDN                    REG       REGSDP_CST
MSISDN                    REG       INDX_REG_MSISDN
PACKAGE_ID                REG       INDX_REG_SRVPCKGID
PACKAGE_ID                REG       REGSDP_CST
REG_ID                    REG       REGSDP_PK
RETRY_COUNT               REG       INDX_REG_RETRYCOUNT
SERVICE_ID                REG       REGSDP_CST
SERVICE_ID                REG       INDX_REG_SRVPCKGID

12 rows selected.

For more informations, I verify clustering factors
sdpadm@SDP> select i.index_name,i.clustering_factor,t.blocks,t.num_rows,i.degree
  2      from dba_indexes i,
  3           dba_tables t
  4      where i.table_name = t.table_name
  5      and t.table_name='REG';

INDEX_NAME                     CLUSTERING_FACTOR     BLOCKS   NUM_ROWS DEGREE
------------------------------ ----------------- ---------- ---------- ----------------------------------------
INDX_REGSDP_AUTORENEW                     108118     101815    5866027 1
REGSDP_CST                               5610722     101815    5866027 1
INDX_REG_LASTRNID                        5511146     101815    5866027 1
INDX_REG_RETRYCOUNT                      2937498     101815    5866027 1
INDX_REG_MSISDN                          5924984     101815    5866027 1
INDX_REG_EXPDATE                         2589365     101815    5866027 1
REGSDP_PK                                1068964     101815    5866027 1
INDX_REG_MOD100                          5180650     101815    5866027 1
INDX_REG_SRVPCKGID                       1673182     101815    5866027 1

9 rows selected.

sdpadm@SDP>

So, I write a very simply SQL and see the poor explain
sdpadm@SDP> SELECT R.REG_ID, R.PACKAGE_ID, R.SERVICE_ID, R.CHARGE_IMMEDIATE, R.MSISDN,
  2  R.SUBPACKAGE_ID, 0 NEXT_CHARGE_LEVEL
  3  FROM SDP.REG R
  4  WHERE R.MOD100 >= 10
  5  AND R.MOD100 <250;

Execution Plan
----------------------------------------------------------
Plan hash value: 546024852

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  5279K|   221M| 27686   (1)| 00:05:33 |
|*  1 |  TABLE ACCESS FULL| REG  |  5279K|   221M| 27686   (1)| 00:05:33 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("R"."MOD100">=10 AND "R"."MOD100"<250)

* How many distinct values in mod100?
sdpadm@SDP> select count(distinct mod100), count(*) from sdp.reg;

COUNT(DISTINCTMOD100)   COUNT(*)
--------------------- ----------
                  100    5862799

[Updated on: Wed, 29 August 2018 03:02]

Report message to a moderator

Re: Range Scan Index [message #671419 is a reply to message #671412] Wed, 29 August 2018 03:35 Go to previous messageGo to next message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
Sorry, I misread your code - you are missing a right bracket, which I assume should come at the very end?

Report message to a moderator

Re: Range Scan Index [message #671420 is a reply to message #671414] Wed, 29 August 2018 03:35 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
How many rows does the simplified query return?
How long does it actually take?

5 mins to full table scan a 5 million row table is horribly slow.

Report message to a moderator

Re: Range Scan Index [message #671421 is a reply to message #671419] Wed, 29 August 2018 03:46 Go to previous messageGo to next message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
Your problem may be that you have only single column indexes. Oracle can use only one b-tree index to implement a predicate, and perhaps none of them is selective enough. How about trying a composite index on reg, on columns
auto_renew,start_renew_flg,expire_date,mod100

Report message to a moderator

Re: Range Scan Index [message #671422 is a reply to message #671419] Wed, 29 August 2018 03:47 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
John Watson wrote on Wed, 29 August 2018 08:35
Sorry, I misread your code - you are missing a right bracket, which I assume should come at the very end?
Dear John,
May you tell me what missing bracket?

The full SQL is here
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;

[Updated on: Wed, 29 August 2018 03:48]

Report message to a moderator

Re: Range Scan Index [message #671423 is a reply to message #671420] Wed, 29 August 2018 03:51 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
cookiemonster wrote on Wed, 29 August 2018 08:35
How many rows does the simplified query return?
How long does it actually take?

5 mins to full table scan a 5 million row table is horribly slow.
No, the time respond to return result about 12-15s, depend to busy table REG which is always being done DML on. 

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, SDP.SERVICE_LIST SL
  9  WHERE 1 = 1
 10  AND R.SERVICE_ID = SL.SERVICE_ID
 11  AND R.MOD100 >= 0
 12  AND R.MOD100 <= 25
 13  AND R.START_RENEW_FLG = 0
 14  AND (R.EXPIRE_DATE <= SYSDATE AND R.LAST_RETRY_DATE <= TRUNC(SYSDATE))
 15  AND R.AUTO_RENEW = 1
 16  AND SL.SERVICE_RENEW_IP = '10.144.17.69'
 17  ORDER BY RETRY_COUNT ASC
 18  /

9 row selected

Elapsed: 00:00:12.60

I am sorry, I make a hidden result for our business.

Report message to a moderator

Re: Range Scan Index [message #671424 is a reply to message #671422] Wed, 29 August 2018 03:53 Go to previous messageGo to next message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
In our first post, you had
SELECT * FROM (
as the first line, with no closing ). I misread it, I thought you were selecting from a subquery joined to a table. Forget it.

Report message to a moderator

Re: Range Scan Index [message #671425 is a reply to message #671421] Wed, 29 August 2018 03:55 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
John Watson wrote on Wed, 29 August 2018 08:46
Your problem may be that you have only single column indexes. Oracle can use only one b-tree index to implement a predicate, and perhaps none of them is selective enough. How about trying a composite index on reg, on columns
auto_renew,start_renew_flg,expire_date,mod100
Yes, one time I tried to create concatenated indexes, but, I really dropped when some other SQL invoking other purpose, so I create single index on single column.

OK, I will create concatenated indexes as your suggesstion and reply later.

Report message to a moderator

Re: Range Scan Index [message #671426 is a reply to message #671424] Wed, 29 August 2018 03:57 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
John Watson wrote on Wed, 29 August 2018 08:53
In our first post, you had
SELECT * FROM (
as the first line, with no closing ). I misread it, I thought you were selecting from a subquery joined to a table. Forget it.
I am sorry, it's my mistake, I usually write down notepad before posting here, the notepad file contains some SQLs, the SELECT * .. was belonged to another SQL.

[Updated on: Wed, 29 August 2018 05:57]

Report message to a moderator

Re: Range Scan Index [message #671447 is a reply to message #671426] Wed, 29 August 2018 21:26 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Dear John, I have just recreated the concatenated indexes.

*Recreate concatenated indexes
	
sdpadm@SDP> drop index sdp.INDX_REG_MOD100;
sdpadm@SDP> drop index sdp.INDX_REG_LASTRNID;
sdpadm@SDP> drop index sdp.INDX_REGSDP_AUTORENEW;


sdpadm@SDP> create index sdp.indx_reg_modstrnautorn on sdp.reg(mod100,START_RENEW_FLG,AUTO_RENEW) tablespace sdpindx;
sdpadm@SDP> execute dbms_stats.gather_table_stats('SDP','REG');
sdpadm@SDP> begin
dbms_stats.gather_table_stats(
ownname=>'SDP',
tabname=>'REG',
method_opt=>'for all indexed columns size auto',
cascade=>true);
end;
/

* Verify statistics on table and indexes
sdpadm@SDP> select owner,table_name,last_analyzed, global_stats
  2  from dba_tables
  3  where owner='SDP'
  4  and table_name='REG';

OWNER                          TABLE_NAME                     LAST_ANAL GLO
------------------------------ ------------------------------ --------- ---
SDP                            REG                            30-AUG-18 YES

sdpadm@SDP> select owner, index_name, last_analyzed, global_stats
  2  from dba_indexes
  3  where owner='SDP'
  4  and table_name='REG';

OWNER                          INDEX_NAME                     LAST_ANAL GLO
------------------------------ ------------------------------ --------- ---
SDP                            INDX_REG_SRVPCKGID             30-AUG-18 YES
SDP                            INDX_REG_MODSTRNAUTORN         30-AUG-18 YES
SDP                            REGSDP_PK                      30-AUG-18 YES
SDP                            INDX_REG_EXPDATE               30-AUG-18 YES
SDP                            INDX_REG_MSISDN                30-AUG-18 YES
SDP                            INDX_REG_RETRYCOUNT            30-AUG-18 YES
SDP                            REGSDP_CST                     30-AUG-18 YES

7 rows selected.

*The indexes detail
sdpadm@SDP> @indxverify
-- Verify the index name and column name association
Enter value for indx_owner: SDP
old   7: WHERE ic.index_owner  = '&&indx_owner'
new   7: WHERE ic.index_owner  = 'SDP'
Enter value for tabname: REG
old   8:   AND ic.table_name  = '&&tabname'
new   8:   AND ic.table_name  = 'REG'

COLUMN_NAME               TABLE_NAM INDEX_NAME                     COLUMN_EXPRESSION   COLUMN_POSITION
------------------------- --------- ------------------------------ ------------------- ---------------
AUTO_RENEW                REG       INDX_REG_MODSTRNAUTORN                                           3		<<-- Here is number 3 position of concatenated indexes
EXPIRE_DATE               REG       INDX_REG_EXPDATE                                                 1
MOD100                    REG       INDX_REG_MODSTRNAUTORN                                           1		<<-- Here is the number 1 position of concatenated indexes
MSISDN                    REG       INDX_REG_MSISDN                                                  1
MSISDN                    REG       REGSDP_CST                                                       3
PACKAGE_ID                REG       REGSDP_CST                                                       2
PACKAGE_ID                REG       INDX_REG_SRVPCKGID                                               2
REG_ID                    REG       REGSDP_PK                                                        1
RETRY_COUNT               REG       INDX_REG_RETRYCOUNT                                              1
SERVICE_ID                REG       REGSDP_CST                                                       1
SERVICE_ID                REG       INDX_REG_SRVPCKGID                                               1
START_RENEW_FLG           REG       INDX_REG_MODSTRNAUTORN                                           2		<<-- Here is the number 2 position of concatenated indexes

12 rows selected.

-- Press any key to continue

-- Verify the clustering factor of which index
Enter value for tabname: REG
old   5:     and t.table_name='&&tabname'
new   5:     and t.table_name='REG'
Enter value for usname: SDP
old   6:        and t.owner='&&usname'
new   6:        and t.owner='SDP'

INDEX_NAME                     CLUSTERING_FACTOR     BLOCKS   NUM_ROWS DEGREE
------------------------------ ----------------- ---------- ---------- ----------------------------------------
INDX_REG_SRVPCKGID                       1731529     101815    5855132 1
INDX_REG_MODSTRNAUTORN                   5554391     101815    5855132 1
REGSDP_PK                                1026753     101815    5855132 1
INDX_REG_EXPDATE                         2556761     101815    5855132 1
INDX_REG_MSISDN                          5769187     101815    5855132 1
INDX_REG_RETRYCOUNT                      3055976     101815    5855132 1
REGSDP_CST                               5284823     101815    5855132 1

7 rows selected.

sdpadm@SDP>

But, it's still FTS
* The explain plan table and timed to execute
sdpadm@SDP> set autotrace traceonly explain
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, SDP.SERVICE_LIST SL
  9  WHERE 1 = 1
 10  AND R.SERVICE_ID = SL.SERVICE_ID
 11  AND R.MOD100 >= 0
 12  AND R.MOD100 <= 25
 13  AND R.START_RENEW_FLG = 0
 14  AND (R.EXPIRE_DATE <= SYSDATE AND R.LAST_RETRY_DATE <= TRUNC(SYSDATE))
 15  AND R.AUTO_RENEW = 1
 16  AND SL.SERVICE_RENEW_IP = '10.144.17.69'
 17  ORDER BY RETRY_COUNT ASC
 18  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3354283033

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |   231K|    20M|       | 32712   (1)| 00:06:33 |
|   1 |  SORT ORDER BY      |              |   231K|    20M|    27M| 32712   (1)| 00:06:33 |
|*  2 |   HASH JOIN         |              |   231K|    20M|       | 27715   (1)| 00:05:33 |
|*  3 |    TABLE ACCESS FULL| SERVICE_LIST |   334 |  6346 |       |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| REG          |   231K|    16M|       | 27711   (1)| 00:05:33 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("R"."SERVICE_ID"="SL"."SERVICE_ID")
   3 - filter("SL"."SERVICE_RENEW_IP"='10.144.17.69')
   4 - filter("R"."MOD100"<=25 AND "R"."START_RENEW_FLG"=0 AND
              "R"."LAST_RETRY_DATE"<=TRUNC(SYSDATE@!) AND "R"."EXPIRE_DATE"<=SYSDATE@! AND
              "R"."AUTO_RENEW"=1 AND "R"."MOD100">=0)

sdpadm@SDP> set autotrace off
sdpadm@SDP> set timing on
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, SDP.SERVICE_LIST SL
  9  WHERE 1 = 1
 10  AND R.SERVICE_ID = SL.SERVICE_ID
 11  AND R.MOD100 >= 0
 12  AND R.MOD100 <= 25
 13  AND R.START_RENEW_FLG = 0
 14  AND (R.EXPIRE_DATE <= SYSDATE AND R.LAST_RETRY_DATE <= TRUNC(SYSDATE))
 15  AND R.AUTO_RENEW = 1
 16  AND SL.SERVICE_RENEW_IP = '10.144.17.69'
 17  ORDER BY RETRY_COUNT ASC
 18  /

9 rows selected

Elapsed: 00:00:06.72
sdpadm@SDP>

Nothing changed, the SQL wasted at least 6s to execute.

Report message to a moderator

Re: Range Scan Index [message #671448 is a reply to message #671447] Thu, 30 August 2018 01:33 Go to previous messageGo to next message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
Quote:
How about trying a composite index on reg, on columns
auto_renew,start_renew_flg,expire_date,mod100
Note the column order.

Report message to a moderator

Re: Range Scan Index [message #671449 is a reply to message #671448] Thu, 30 August 2018 01:37 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
John Watson wrote on Thu, 30 August 2018 06:33
Quote:
How about trying a composite index on reg, on columns
auto_renew,start_renew_flg,expire_date,mod100
Note the column order.
You mean composite index on even the RETRY_COUNT which column has been being order clause?

Report message to a moderator

Re: Range Scan Index [message #671450 is a reply to message #671449] Thu, 30 August 2018 01:40 Go to previous messageGo to next message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
create index whatever on reg(auto_renew,start_renew_flg,expire_date,mod100);

Report message to a moderator

Re: Range Scan Index [message #671451 is a reply to message #671450] Thu, 30 August 2018 01:44 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Dear Mr John,

I have created the index in my posted reply above.

sdpadm@SDP> create index sdp.indx_reg_modstrnautorn on sdp.reg(mod100,START_RENEW_FLG,AUTO_RENEW) tablespace sdpindx;

Report message to a moderator

Re: Range Scan Index [message #671455 is a reply to message #671451] Thu, 30 August 2018 03:26 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then try creating the one John suggested instead.

Report message to a moderator

Re: Range Scan Index [message #671468 is a reply to message #671455] Thu, 30 August 2018 09:34 Go to previous message
JPBoileau
Messages: 88
Registered: September 2017
Member
I'd got a step further from what John said, and try:

CREATE INDEX REG_NEWINDEX ON REG (
START_RENEW_FLG,
AUTO_RENEW,
EXPIRE_DATE,
MOD100,
NVL(LAST_RETRY_DATE, TRUNC(SYSDATE-1)),
SERVICE_ID);

I'm including the NVL(LAST_RETRY_DATE...) to prevent a table lookup for a filtering condition.
I'm also including the SERVICE_ID to prevent another table lookup for a filtering condition.

JP

[Updated on: Thu, 30 August 2018 09:34]

Report message to a moderator

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

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Feb 02 17:54:11 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.