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  |
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 #668910 is a reply to message #668908] |
Thu, 22 March 2018 02:16  |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
THis hint is incorrect,
SELECT /*+ INDX_REG_EXPIREDATE/ R.REG_ID, R.PACKAGE_ID,
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 |
cookiemonster
Messages: 13963
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 |
cookiemonster
Messages: 13963
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 #668914 is a reply to message #668913] |
Thu, 22 March 2018 04:41 |
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 |
cookiemonster
Messages: 13963
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 #669382 is a reply to message #668916] |
Wed, 18 April 2018 21:30 |
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 #669387 is a reply to message #669382] |
Thu, 19 April 2018 05:03 |
John Watson
Messages: 8964
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.
|
|
|
|
Goto Forum:
Current Time: Sun Feb 02 18:23:16 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
