Home » RDBMS Server » Performance Tuning » A case need to help clear (Oracle 11.2.0.4, any platform)
A case need to help clear [message #668907] Wed, 21 March 2018 22:28 Go to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Dear,
I had got a package, one procedure on it called as renew_fnc_list, it invoke to 3 tables, 2 is normal, 1 is partitioned table by day. I write details here

The tables informations
-- REG is a normal table, approximately 3722800 rows (many DMLs on every day, of course, no more 6mil rows exceed)
-- REG has got some indexed reverse key on columns, the indexes are B-Tree
-- REG has got closed statistic to day
-- Total size in REG is about 400MB

sysadmin@SDP> desc sdp.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

sysadmin@SDP> select owner, object_name, object_type from dba_objects where owner='SDP' and object_name='REG'

OWNER                          OBJECT_NAME  OBJECT_TYPE
------------------------------ ------------ -------------------
SDP                            REG          TABLE

sysadmin@SDP> select owner, table_name, index_name from dba_indexes
  2  where owner='SDP'
  3  and table_name='REG';

OWNER                          TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------ ------------------------------
SDP                            REG                            INDX_REG_SRVPCKGMODID
SDP                            REG                            REG_PK
SDP                            REG                            INDX_REG_MSISDN
SDP                            REG                            INDX_REG_AUTORENEW
SDP                            REG                            INDX_REG_EXPIREDATE
SDP                            REG                            INDX_REG_RETRYCOUNT

sysadmin@SDP> select owner, sum(bytes/1024/1024) mb
  2  from dba_segments
  3  where owner='SDP'
  4  and segment_name='REG'
  5  group by owner
  6  /

OWNER                                  MB
------------------------------ ----------
SDP                                 441.5

sysadmin@SDP> select count(*) from sdp.reg
  2  /

  COUNT(*)
----------
   3722800
   
sysadmin@SDP> select owner, table_name, to_char(last_analyzed,'dd-mm-yyyy hh24:mi:ss') as "LAST_ANA"
  2  from dba_tables
  3  where owner='SDP'
  4  and table_name='REG';

OWNER                          TABLE_NAME                     LAST_ANA
------------------------------ ------------------------------ --------------------------------------
SDP                            REG                            21-03-2018 22:01:57


-- SERVICE_LIST
-- SERVICE_LIST is a very small table, there are maximum 360 rows on, therefore I have no need to index on.

sysadmin@SDP> select owner, object_name, object_type
  2  from dba_objects
  3  where owner='SDP'
  4  and object_name='SERVICE_LIST';

OWNER                          OBJECT_NAME  OBJECT_TYPE
------------------------------ ------------ -------------------
SDP                            SERVICE_LIST TABLE

sysadmin@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(20)

sysadmin@SDP> select owner, table_name, to_char(last_analyzed,'dd-mm-yyyy hh24:mi:ss') as "LAST_ANA"
  2  from dba_tables
  3  where owner='SDP'
  4  and table_name='SERVICE_LIST';

OWNER                          TABLE_NAME                     LAST_ANA
------------------------------ ------------------------------ ---------------------------------------------------------------------------
SDP                            SERVICE_LIST                   11-03-2018 22:06:50

sysadmin@SDP> select owner, sum(bytes/1024/1024) mb
  2  from dba_segments
  3  where owner='SDP'
  4  and segment_name='SERVICE_LIST'
  5  group by owner
  6  /

OWNER                                  MB
------------------------------ ----------
SDP                                 .0625

sysadmin@SDP> select count(*) from sdp.service_list;

  COUNT(*)
----------
       281

-- PACKAGE_CHARGE_LOG
-- PACKAGE_CHARGE_LOG is a partitioned table by day, but there are about 3 milions rows, not more.

sysadmin@SDP> desc sdp.package_charge_log
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 REG_ID                                                                              NOT NULL NUMBER(10)
 CHARGE_DATE                                                                         NOT NULL DATE

sysadmin@SDP> select count(*) from sdp.package_charge_log;

  COUNT(*)
----------
   1061353

sysadmin@SDP> select table_owner, table_name, to_char(last_analyzed,'dd-mm-yyyy hh24:mi:ss') as "LAST_ANA"
  2  from dba_tab_partitions
  3  where table_owner='SDP'
  4  and table_name='PACKAGE_CHARGE_LOG';

TABLE_OWNER                    TABLE_NAME                     LAST_ANA
------------------------------ ------------------------------ ---------------------------------------------------------------------------
SDP                            PACKAGE_CHARGE_LOG             22-03-2018 08:36:50
SDP                            PACKAGE_CHARGE_LOG             22-03-2018 08:36:50
SDP                            PACKAGE_CHARGE_LOG             22-03-2018 08:36:51
...
SDP                            PACKAGE_CHARGE_LOG             22-03-2018 08:36:52

121 rows selected.


Now, I have a simply query which belonged to WebLogic againts to those tables, the SQL was called from renew_fnc_list function

SELECT X.* 
	FROM ( SELECT * 
				FROM ( SELECT R.REG_ID, R.PACKAGE_ID,
								R.SERVICE_ID, R.MSISDN, 
								TO_CHAR(R.EXPIRE_DATE, 'yyyy/MM/dd hh24:mi:ss') EXPIRE_DATE, 
								R.LAST_RETRY_DATE LAST_RENEW_DATE, 
								R.RETRY_COUNT, R.SUBPACKAGE_ID, 
								0 NEXT_CHARGE_LEVEL, 
								TO_CHAR(SYSDATE,'yyyymmddHH24') NEXT_RETRY_DATE 
							FROM SDP.REG R 
							WHERE 1 = 1 
							AND R.EXPIRE_DATE < SYSDATE 
							AND R.MOD100 >= 3 
							AND R.MOD100 <= 10 
							AND R.START_RENEW_FLG = 0 
							AND R.RETRY_COUNT = 0 
							AND R.AUTO_RENEW = 1 ) EI 
				WHERE 1 = 1 
				AND NOT EXISTS
						(SELECT 1 
							FROM SDP.PACKAGE_CHARGE_LOG CL 
							WHERE CL.CHARGE_DATE = TRUNC(SYSDATE) 
							AND CL.REG_ID = EI.REG_ID) ) X, SDP.SERVICE_LIST SL 
	WHERE X.SERVICE_ID = SL.SERVICE_ID 
	AND SL.SERVICE_RENEW_IP = '10.144.33.69' 
	AND ROWNUM<= 250;

Analyzed:

1. How many executions from the last fill full pool on each node?
EXECUTIONS ELAP_EXEC ELAP_PER_EXEC TOTAL_CPU_TIME_SEC CPU_TIME_SEC
---------- --------- ------------- ------------------ ------------
     18026     13959             1               5524            0

2. What is the explain plan for the SQL query?
sdpadm@SDP> explain plan for
  2  SELECT X.*
  3  FROM ( SELECT *
  4  FROM ( SELECT R.REG_ID, R.PACKAGE_ID,
  5  R.SERVICE_ID, R.MSISDN,
  6  TO_CHAR(R.EXPIRE_DATE, 'yyyy/MM/dd hh24:mi:ss') EXPIRE_DATE,
  7  R.LAST_RETRY_DATE LAST_RENEW_DATE,
  8  R.RETRY_COUNT, R.SUBPACKAGE_ID,
  9  0 NEXT_CHARGE_LEVEL,
 10  TO_CHAR(SYSDATE,'yyyymmddHH24') NEXT_RETRY_DATE
 11  FROM SDP.REG R
 12  WHERE 1 = 1
 13  AND R.EXPIRE_DATE < SYSDATE
 14  AND R.MOD100 >= 3
 15  AND R.MOD100 <= 10
 16  AND R.START_RENEW_FLG = 0
 17  AND R.RETRY_COUNT = 0
 18  AND R.AUTO_RENEW = 1 ) EI
 19  WHERE 1 = 1
 20  AND NOT EXISTS
 21  (SELECT 1
 22  FROM SDP.PACKAGE_CHARGE_LOG CL
 23  WHERE CL.CHARGE_DATE = TRUNC(SYSDATE)
 24  AND CL.REG_ID = EI.REG_ID) ) X, SDP.SERVICE_LIST SL
 25  WHERE X.SERVICE_ID = SL.SERVICE_ID
 26  AND SL.SERVICE_RENEW_IP = '10.144.33.69'
 27  AND ROWNUM<= 250;

Explained.

sdpadm@SDP> select plan_table_output from
  2  table(dbms_xplan.display('plan_table',null,'typical -cost -bytes'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3967005198

------------------------------------------------------------------------------------------
| Id  | Operation                | Name               | Rows  | Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                    |   250 | 00:03:29 |       |       |
|*  1 |  COUNT STOPKEY           |                    |       |          |       |       |
|*  2 |   HASH JOIN ANTI         |                    |   409 | 00:03:29 |       |       |
|*  3 |    HASH JOIN             |                    | 92943 | 00:03:04 |       |       |
|*  4 |     TABLE ACCESS FULL    | SERVICE_LIST       |   274 | 00:00:01 |       |       |
|*  5 |     TABLE ACCESS FULL    | REG                | 93113 | 00:03:04 |       |       |
|   6 |    PARTITION RANGE SINGLE|                    |   490K| 00:00:13 |   KEY |   KEY |
|*  7 |     TABLE ACCESS FULL    | PACKAGE_CHARGE_LOG |   490K| 00:00:13 |   KEY |   KEY |
------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=250)
   2 - access("CL"."REG_ID"="R"."REG_ID")
   3 - access("R"."SERVICE_ID"="SL"."SERVICE_ID")
   4 - filter("SL"."SERVICE_RENEW_IP"='10.144.33.69')
   5 - filter("R"."MOD100"<=10 AND "R"."RETRY_COUNT"=0 AND
              "R"."START_RENEW_FLG"=0 AND "R"."EXPIRE_DATE"<SYSDATE@! AND "R"."MOD100">=3 AND
              "R"."AUTO_RENEW"=1)
   7 - filter("CL"."CHARGE_DATE"=TRUNC(SYSDATE@!))

26 rows selected.


Well, this is dangerous time to query, all of them was FTS (Full table Scan), none of indexes was to be used, even the VALID indexes.

And then, I broke the sub-query to check-out

-- Breaking the sub-query for testing perforamance
/*
sdpadm@SDP> explain plan for
  2  SELECT R.REG_ID, R.PACKAGE_ID,
  3  R.SERVICE_ID, R.MSISDN,
  4  TO_CHAR(R.EXPIRE_DATE, 'yyyy/MM/dd hh24:mi:ss') EXPIRE_DATE,
  5  R.LAST_RETRY_DATE LAST_RENEW_DATE,
  6  R.RETRY_COUNT, R.SUBPACKAGE_ID,
  7  0 NEXT_CHARGE_LEVEL,
  8  TO_CHAR(SYSDATE,'yyyymmddHH24') NEXT_RETRY_DATE
  9  FROM SDP.REG R
 10  WHERE 1 = 1
 11  AND R.EXPIRE_DATE < SYSDATE
 12  AND R.MOD100 >= 3
 13  AND R.MOD100 <= 10
 14  AND R.START_RENEW_FLG = 0
 15  AND R.RETRY_COUNT = 0
 16  AND R.AUTO_RENEW = 1
 17  /

Explained.

sdpadm@SDP> select plan_table_output from table(dbms_xplan.display('plan_table',null,'typical -cost -bytes'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 546024852

-----------------------------------------------------
| Id  | Operation         | Name | Rows  | Time     |
-----------------------------------------------------
|   0 | SELECT STATEMENT  |      | 93113 | 00:03:04 |
|*  1 |  TABLE ACCESS FULL| REG  | 93113 | 00:03:04 |
-----------------------------------------------------

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

   1 - filter("R"."MOD100"<=10 AND "R"."RETRY_COUNT"=0 AND
              "R"."START_RENEW_FLG"=0 AND "R"."EXPIRE_DATE"<SYSDATE@! AND
              "R"."MOD100">=3 AND "R"."AUTO_RENEW"=1)

15 rows selected.

-- For adjust only EXPIRE_DATE column
sdpadm@SDP> set autotrace traceonly explain
sdpadm@SDP> SELECT R.REG_ID, R.PACKAGE_ID,
  2  R.SERVICE_ID, R.MSISDN,
  3  TO_CHAR(R.EXPIRE_DATE, 'yyyy/MM/dd hh24:mi:ss') EXPIRE_DATE,
  4  R.LAST_RETRY_DATE LAST_RENEW_DATE,
  5  R.RETRY_COUNT, R.SUBPACKAGE_ID,
  6  0 NEXT_CHARGE_LEVEL,
  7  TO_CHAR(SYSDATE,'yyyymmddHH24') NEXT_RETRY_DATE
  8  FROM SDP.REG R
  9  WHERE R.EXPIRE_DATE < SYSDATE
 10  /

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2694K|   141M| 15289   (1)| 00:03:04 |
|*  1 |  TABLE ACCESS FULL| REG  |  2694K|   141M| 15289   (1)| 00:03:04 |
--------------------------------------------------------------------------

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

   1 - filter("R"."EXPIRE_DATE"<SYSDATE@!)
*/

-- Force to use index hint
/*

sdpadm@SDP> SELECT /*+ INDX_REG_EXPIREDATE/ R.REG_ID, R.PACKAGE_ID,
		R.SERVICE_ID, R.MSISDN, 
		TO_CHAR(R.EXPIRE_DATE, 'yyyy/MM/dd hh24:mi:ss') EXPIRE_DATE, 
		R.LAST_RETRY_DATE LAST_RENEW_DATE, 
		R.RETRY_COUNT, R.SUBPACKAGE_ID, 
		0 NEXT_CHARGE_LEVEL, 
		TO_CHAR(SYSDATE,'yyyymmddHH24') NEXT_RETRY_DATE 
	FROM SDP.REG R 
	WHERE 1 = 1 
	AND R.EXPIRE_DATE < SYSDATE;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2694K|   141M| 15289   (1)| 00:03:04 |
|*  1 |  TABLE ACCESS FULL| REG  |  2694K|   141M| 15289   (1)| 00:03:04 |
--------------------------------------------------------------------------

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

   1 - filter("R"."EXPIRE_DATE"<SYSDATE@!)
*/

-- What's about plan table to PACKAGE_CHARGE_LOG?

-- PACKAGE_CHARGE_LOG
sdpadm@SDP> SELECT 1
  2  FROM SDP.PACKAGE_CHARGE_LOG CL
  3  WHERE CL.CHARGE_DATE = TRUNC(SYSDATE)
  4  /
Execution Plan
----------------------------------------------------------
Plan hash value: 1600327213

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                           |   491K|  3839K|   365   (3)| 00:00:05 |    |          |
|   1 |  PARTITION RANGE SINGLE|                           |   491K|  3839K|   365   (3)| 00:00:05 |   KEY |   KEY |
|*  2 |   INDEX FAST FULL SCAN | INDX_PCKGCHRGLOG_CHRGDATE |   491K|  3839K|   365   (3)| 00:00:05 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------------------------

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

   2 - filter("CL"."CHARGE_DATE"=TRUNC(SYSDATE@!))


How did the REG's index disappear?

OK, I made an obvious test case,
-- Create a table which has got structure same to REG, insert all rows to sample table from REG, create only one index on EXPIREDATE column

sdpadm@SDP> truncate table plan_table;

Table truncated.

sdpadm@SDP> create table regex as select * from sdp.reg;

Table created.

sdpadm@SDP> alter table regex add constraint regex_pk primary key(reg_id);

Table altered.

sdpadm@SDP> create index indx_regex_expird on regex(expire_date);

Index created.

sdpadm@SDP> begin
  2  dbms_stats.gather_table_stats(
  3  ownname=>'SDPADM',
  4  tabname=>'REGEX',
  5  method_opt=>'for all indexed columns size auto',
  6  cascade=>true);
  7  end;
  8  /

PL/SQL procedure successfully completed.

-- And test
SELECT X.* 
	FROM ( SELECT * 
				FROM ( SELECT R.REG_ID, R.PACKAGE_ID,
								R.SERVICE_ID, R.MSISDN, 
								TO_CHAR(R.EXPIRE_DATE, 'yyyy/MM/dd hh24:mi:ss') EXPIRE_DATE, 
								R.LAST_RETRY_DATE LAST_RENEW_DATE, 
								R.RETRY_COUNT, R.SUBPACKAGE_ID, 
								0 NEXT_CHARGE_LEVEL, 
								TO_CHAR(SYSDATE,'yyyymmddHH24') NEXT_RETRY_DATE 
							FROM SDPADM.REGEX R 
							WHERE 1 = 1 
							AND R.EXPIRE_DATE < SYSDATE 
							AND R.MOD100 >= 3 
							AND R.MOD100 <= 10 
							AND R.START_RENEW_FLG = 0 
							AND R.RETRY_COUNT = 0 
							AND R.AUTO_RENEW = 1 ) EI 
				WHERE 1 = 1 
				AND NOT EXISTS
						(SELECT 1 
							FROM SDP.PACKAGE_CHARGE_LOG CL 
							WHERE CL.CHARGE_DATE = TRUNC(SYSDATE) 
							AND CL.REG_ID = EI.REG_ID) ) X, SDP.SERVICE_LIST SL 
	WHERE X.SERVICE_ID = SL.SERVICE_ID 
	AND SL.SERVICE_RENEW_IP = '10.144.33.69' 
	AND ROWNUM<= 250;

/* The new explain plan
sdpadm@SDP> truncate table plan_table;

Table truncated.

sdpadm@SDP> explain plan for
  2  SELECT X.*
  3  FROM ( SELECT *
  4  FROM ( SELECT R.REG_ID, R.PACKAGE_ID,
  5  R.SERVICE_ID, R.MSISDN,
  6  TO_CHAR(R.EXPIRE_DATE, 'yyyy/MM/dd hh24:mi:ss') EXPIRE_DATE,
  7  R.LAST_RETRY_DATE LAST_RENEW_DATE,
  8  R.RETRY_COUNT, R.SUBPACKAGE_ID,
  9  0 NEXT_CHARGE_LEVEL,
 10  TO_CHAR(SYSDATE,'yyyymmddHH24') NEXT_RETRY_DATE
 11  FROM SDPADM.REGEX R
 12  WHERE 1 = 1
 13  AND R.EXPIRE_DATE < SYSDATE
 14  AND R.MOD100 >= 3
 15  AND R.MOD100 <= 10
 16  AND R.START_RENEW_FLG = 0
 17  AND R.RETRY_COUNT = 0
 18  AND R.AUTO_RENEW = 1 ) EI
 19  WHERE 1 = 1
 20  AND NOT EXISTS
 21  (SELECT 1
 22  FROM SDP.PACKAGE_CHARGE_LOG CL
 23  WHERE CL.CHARGE_DATE = TRUNC(SYSDATE)
 24  AND CL.REG_ID = EI.REG_ID) ) X, SDP.SERVICE_LIST SL
 25  WHERE X.SERVICE_ID = SL.SERVICE_ID
 26  AND SL.SERVICE_RENEW_IP = '10.144.33.69'
 27  AND ROWNUM<= 250;

Explained.

sdpadm@SDP> select plan_table_output from table(dbms_xplan.display('plan_table',null,'typical -cost -bytes'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2845502508

-------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name               | Rows  | Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                    |     1 | 00:02:37 |       |       |
|*  1 |  COUNT STOPKEY                  |                    |       |          |       |       |
|*  2 |   HASH JOIN ANTI                |                    |     1 | 00:02:37 |       |       |
|   3 |    NESTED LOOPS                 |                    |     1 | 00:02:25 |       |       |
|   4 |     NESTED LOOPS                |                    |     1 | 00:02:25 |       |       |
|*  5 |      TABLE ACCESS BY INDEX ROWID| REGEX              |     1 | 00:02:25 |       |       |
|*  6 |       INDEX RANGE SCAN          | INDX_REGEX_EXPIRD  | 31153 | 00:00:02 |       |       |
|*  7 |      INDEX UNIQUE SCAN          | SERVICE_LIST_PK    |     1 | 00:00:01 |       |       |
|*  8 |     TABLE ACCESS BY INDEX ROWID | SERVICE_LIST       |     1 | 00:00:01 |       |       |
|   9 |    PARTITION RANGE SINGLE       |                    |   491K| 00:00:13 |   KEY |   KEY |
|* 10 |     TABLE ACCESS FULL           | PACKAGE_CHARGE_LOG |   491K| 00:00:13 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=250)
   2 - access("CL"."REG_ID"="R"."REG_ID")
   5 - filter("R"."START_RENEW_FLG"=0 AND "R"."RETRY_COUNT"=0 AND "R"."AUTO_RENEW"=1 AND
              "R"."MOD100">=3 AND "R"."MOD100"<=10)
   6 - access("R"."EXPIRE_DATE"<SYSDATE@!)
   7 - access("R"."SERVICE_ID"="SL"."SERVICE_ID")
   8 - filter("SL"."SERVICE_RENEW_IP"='10.144.33.69')
  10 - filter("CL"."CHARGE_DATE"=TRUNC(SYSDATE@!))

29 rows selected.

With the same table, same structure, same rows, same index, Oracle used index, but why did Oracle do not use index on original table?

May you help me this case?

Thank you!
Re: A case need to help clear [message #668908 is a reply to message #668907] Wed, 21 March 2018 22:46 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Then, I upload 2 html files here to overview the bad SQL.
Sorry to my dear moderators, the upload.log is really upload.rar (include 2 html files inside), I change the extension then I can upload here.
  • Attachment: Upload.log
    (Size: 14.99KB, Downloaded 2364 times)
Re: A case need to help clear [message #668910 is a reply to message #668908] Thu, 22 March 2018 02:16 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
THis hint is incorrect,
SELECT /*+ INDX_REG_EXPIREDATE/ R.REG_ID, R.PACKAGE_ID,
it should be
SELECT /*+ index(r INDX_REG_EXPIREDATE) */ R.REG_ID, R.PACKAGE_ID,

You do need indexes on SERVICE_LIST and declared primary key and foreign key constraints. Even though the table is not large, the optimizer needs to know how the tables are related
Re: A case need to help clear [message #668911 is a reply to message #668910] Thu, 22 March 2018 04:06 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
As written that isn't even an incorrect hint. It's an un-ended comment that takes out everything after SELECT.
There's no way the OP actually ran that query.

@trantuananh24hg - you need to copy and paste what you actually ran without edits.
Re: A case need to help clear [message #668912 is a reply to message #668911] Thu, 22 March 2018 04:15 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I see zero benefit to the nesting you've got - all it's doing it making the optimizers life hard. Try getting rid of it.

Also - why are you doing a rownum restriction with no order by? Do you want a random selection of matching rows each time you run this?
Re: A case need to help clear [message #668913 is a reply to message #668910] Thu, 22 March 2018 04:17 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
One other point - you said that some indexes are reverse key. THat will make them unusable for non-equality predicates, such as these
                                                  AND R.EXPIRE_DATE < SYSDATE 
							AND R.MOD100 >= 3 
							AND R.MOD100 <= 10 

[Updated on: Thu, 22 March 2018 04:17]

Report message to a moderator

Re: A case need to help clear [message #668914 is a reply to message #668913] Thu, 22 March 2018 04:41 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Dear John,

I often write down in a text file, or word file before post something. In my text, the hint is actual /*+ index_here */, however, when I pasted, I did a mistake.

The SERVICE_LIST table has got a PK, SERVICE_LIST_PK.

With your last question, some indexes are reverse key, they are not reverse key on Expire_date, Mod100 columns but stands on MSISDN, AUTORENEW. Those columns are alway update/insert. The index on Expire_date and Mod100 are B-Tree Normal.

Dear cookiemonster.
Yes, I removed order by clause from original SQL with reduce temp-sort, my purpose is wondering disappear index. Absolutely, my system, my database, then I understand them, I am sorry when I did not post enough information than before. Respectly to your ask, your require more info, I will do it.

Thank you very much!

P/S:I attach a file details the execution here, please rename from .log to .html to open it.

[Updated on: Thu, 22 March 2018 04:46]

Report message to a moderator

Re: A case need to help clear [message #668915 is a reply to message #668914] Thu, 22 March 2018 05:17 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
trantuananh24hg wrote on Thu, 22 March 2018 09:41

Dear cookiemonster.
Yes, I removed order by clause from original SQL with reduce temp-sort,
Don't do that. Removing the order by on a top-n query changes the meaning of the query.
It's pointless seeing how fast a query that does something else is.

Post the actual query you are actually running.
Re: A case need to help clear [message #668916 is a reply to message #668915] Thu, 22 March 2018 05:24 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also give the full definitions for the indexes on reg.
Re: A case need to help clear [message #669382 is a reply to message #668916] Wed, 18 April 2018 21:30 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Sincere,

I come back here after a few days (exactly, near 1 month), this case above, I have finished tuned about 3 weeks ago. Because of my business hardly, I forgot to feedback.

In the case, we had 3 tables, 2 are Heap tables (REG, SERVICE_LIST), and one is partition table (PACKAGE_CHARGE_LOG). With a sub-query againts to REG, (I paid much time to break it, some time I re-wrote with another SQL, some time I created a test case to join, including inner join, outer join, or another actions). Let see it again

SELECT r.reg_id, r.package_id, r.service_id, r.msisdn,
                        TO_CHAR(r.expire_date, 'yyyy/MM/dd hh24:mi:ss') expire_date,
                        r.last_retry_date last_renew_date, 
						r.retry_count, r.subpackage_id,
                        0 next_charge_level,
                        TO_CHAR(sysdate,'yyyymmddHH24') next_retry_date
                    FROM SDP.REG r
                    WHERE 1 = 1
                    AND r.expire_date < SYSDATE   
                    AND r.mod100 >= 10
                    AND r.mod100 <= 100
                    AND NVL(r.start_renew_flg,0) = 0
                    AND r.retry_count = 0
                    AND r.auto_renew = 1;

The Explain plan
sdpadm@SDP> SELECT r.reg_id, r.package_id, r.service_id, r.msisdn,
  2                          TO_CHAR(r.expire_date, 'yyyy/MM/dd hh24:mi:ss') expire_date,
  3                          r.last_retry_date last_renew_date,
  4  r.retry_count, r.subpackage_id,
  5                          0 next_charge_level,
  6                          TO_CHAR(sysdate,'yyyymmddHH24') next_retry_date
  7                      FROM SDP.REG r
  8                      WHERE 1 = 1
  9                      AND r.expire_date < SYSDATE
 10                      AND r.mod100 >= 10
 11                      AND r.mod100 <= 100
 12                      AND NVL(r.start_renew_flg,0) = 0
 13                      AND r.retry_count = 0
 14                      AND r.auto_renew = 1
 15  /

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   858K|    52M| 17453   (1)| 00:03:30 |
|*  1 |  TABLE ACCESS FULL| REG  |   858K|    52M| 17453   (1)| 00:03:30 |
--------------------------------------------------------------------------

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

   1 - filter("R"."RETRY_COUNT"=0 AND "R"."MOD100">=10 AND
              "R"."EXPIRE_DATE"<SYSDATE@! AND NVL("R"."START_RENEW_FLG",0)=0 AND
              "R"."AUTO_RENEW"=1 AND "R"."MOD100"<=100)

We all see, there is not unused ever indexes on REG, why? 52M, 858K on those rows againts to total approximate 4 milions rows, 3min30sec to execute. Hmm, it is very slow.

I am busy, I will come back as soon as possible.
Re: A case need to help clear [message #669383 is a reply to message #669382] Wed, 18 April 2018 22:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
NULL value not usually indexed.
FTS is required to evaluate NVL("R"."START_RENEW_FLG",0)=0
Re: A case need to help clear [message #669386 is a reply to message #669383] Thu, 19 April 2018 04:41 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
table access is required. Doesn't necessarily mean a full table scan is.

@trantuananh24hg - you still haven't told us what indexes are on the table
Re: A case need to help clear [message #669387 is a reply to message #669382] Thu, 19 April 2018 05:03 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
The indexes on mod100 or expire_date should be usable for that query, but with an estimate of 858k rows out of 4m rows the optimizer is probably correct not to use one. You give it a /*+ index(r) */ hint, which is "use any index, in any way" to test.

It may be that the only way to speed up the query is to throw some parallelism at it.
Previous Topic: Remove Merge cartesian Join
Next Topic: Update Query Performance
Goto Forum:
  


Current Time: Fri Mar 29 02:16:37 CDT 2024