Home » RDBMS Server » Performance Tuning » SQL Tuning Advise ( Oracle 11.2.0.4.0 - 64bit Production)
| SQL Tuning Advise [message #638675] |
Thu, 18 June 2015 00:00  |
 |
avineshr
Messages: 14
Registered: September 2012
Location: malaysia
|
Junior Member |
|
|
Hi All,
I have some difficulty in tuning below query , please advise your suggestion.
SQL Query:
SELECT TOKEN_NO, REG_PLACE, VEHICLE_NO, PORT, TERMINAL,
TOKEN_TYPE,
MOBILE_PREFIX, MOBILE_NO, EMAIL_ADDRESS,
NOTIFICATION_TYPE,
TKN_GEN_TYPE, PAYMENT_KEY, RECEIPT_NO, TOKEN_ACTIVE,
START_TIME,
END_TIME, SCH_DATE, SLOT_ID, STATUS, CNT, TKN_GEN_CODE
FROM (SELECT A.*, COUNT (*) OVER () CNT, ROWNUM AS RN
FROM (SELECT ET.TOKEN_NO, ET.REG_PLACE,
ET.VEHICLE_NO, ET.PORT,
ET.TERMINAL, ET.TOKEN_TYPE,
ET.MOBILE_PREFIX,
ET.MOBILE_NO, ET.EMAIL_ADDRESS,
ET.NOTIFICATION_TYPE,
ET.TKN_GEN_TYPE, T.PAYMENT_KEY,
T.RECEIPT_NO,
CASE
WHEN ET.IS_VALID = 1
AND GS.SCH_DATE >= TRUNC (SYSDATE,
'DDD')
THEN 'Active'
WHEN ET.IS_VALID = 1
AND GS.SCH_DATE < TRUNC (SYSDATE,
'DDD')
THEN 'Expired'
WHEN ET.IS_VALID = 0
THEN 'In Active'
END TOKEN_ACTIVE,
GS.START_TIME, GS.END_TIME,
GS.SCH_DATE, GS.SLOT_ID,
DECODE (T.TRX_STATUS,
'P', 'Pending',
'0', 'Error',
'1', 'SUCCESS'
) STATUS,
ET.TKN_GEN_CODE
FROM opctjad_dba.ET_TOKENS ET,
opctjad_dba.GATE_SCHEDULE GS, opctjad_dba.TOKEN_PAYMENT T
WHERE T.TOKEN_NO = ET.TOKEN_NO
AND GS.SLOT_ID = ET.SLOT_ID
AND ( (:B6 IS NOT NULL AND ET.TKN_ID =
:B7)
OR (:B6 IS NULL)
)
AND (ET.TKN_GEN_CODE = NVL (:B5,
ET.TKN_GEN_CODE))
AND ET.REG_PLACE = NVL (:B4, ET.REG_PLACE)
AND ET.VEHICLE_NO = NVL (:B3,
ET.VEHICLE_NO)
AND ET.PORT = :B2
AND ( (:B1 = 'E')
OR ( (:B1 IN (:A, :A))
AND NOT EXISTS (
SELECT 1
FROM
opctjad_dba.ET_TOKEN_CONTRS ETC
WHERE (EIR_DATE IS NOT
NULL
OR TQ_MSG = 2
)
AND ETC.TKN_ID =
ET.TKN_ID
AND ETC.IS_VALID = 1)
AND ET.IS_VALID = 1
AND T.TRX_STATUS IN (:A, :b)
AND ET.TXN_SRC <> :c
)
)
AND ( (:B1 = 'E')
OR ( (:B1 IN (:d, :e))
AND EXISTS (
SELECT 1
FROM
opctjad_dba.ET_TOKEN_CONTRS TC
WHERE TC.TKN_ID =
ET.TKN_ID
AND TC.IS_VALID = 1)
)
)
AND ( (:B1 = 'E')
OR ( :B1 IN (:f, :g)
AND GS.SCH_DATE >= TRUNC (SYSDATE,
:h)
)
)
ORDER BY T.TOKEN_DATE DESC) A)
WHERE RN BETWEEN NVL (:B9, 1) AND NVL (:B8, CNT)
Please find below execution plan as per SQL Advisor:
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 2082645971
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 830 | 38 (3)| 00:00:01 |
|* 1 | VIEW | | 2 | 830 | 38 (3)| 00:00:01 |
| 2 | WINDOW BUFFER | | 2 | 778 | 38 (3)| 00:00:01 |
| 3 | COUNT | | | | | |
| 4 | VIEW | | 2 | 778 | 38 (3)| 00:00:01 |
| 5 | SORT ORDER BY | | 2 | 246 | 38 (3)| 00:00:01 |
| 6 | CONCATENATION | | | | | |
|* 7 | FILTER | | | | | |
|* 8 | FILTER | | | | | |
| 9 | NESTED LOOPS | | 1 | 123 | 26 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 123 | 26 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 93 | 23 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | ET_TOKENS | 1 | 64 | 21 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID| GATE_SCHEDULE | 1 | 29 | 2 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | GATE_SCHEDULE_NU2 | 1 | | 1 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | N_TOKEN_PAYMENT_2 | 1 | | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | TOKEN_PAYMENT | 1 | 30 | 3 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | ET_TOKEN_CONTRS | 1 | 17 | 2 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | ET_TOKEN_CONTRS_INDX1 | 1 | | 1 (0)| 00:00:01 |
|* 19 | TABLE ACCESS BY INDEX ROWID | ET_TOKEN_CONTRS | 1 | 11 | 2 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | ET_TOKEN_CONTRS_INDX1 | 1 | | 1 (0)| 00:00:01 |
|* 21 | FILTER | | | | | |
|* 22 | FILTER | | | | | |
| 23 | NESTED LOOPS | | 1 | 123 | 11 (0)| 00:00:01 |
| 24 | NESTED LOOPS | | 1 | 123 | 11 (0)| 00:00:01 |
| 25 | NESTED LOOPS | | 1 | 93 | 8 (0)| 00:00:01 |
|* 26 | TABLE ACCESS BY INDEX ROWID| ET_TOKENS | 1 | 64 | 6 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | ET_TOKENS_IND_GTE2 | 19 | | 1 (0)| 00:00:01 |
|* 28 | TABLE ACCESS BY INDEX ROWID| GATE_SCHEDULE | 1 | 29 | 2 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | GATE_SCHEDULE_NU2 | 1 | | 1 (0)| 00:00:01 |
|* 30 | INDEX RANGE SCAN | N_TOKEN_PAYMENT_2 | 1 | | 2 (0)| 00:00:01 |
| 31 | TABLE ACCESS BY INDEX ROWID | TOKEN_PAYMENT | 1 | 30 | 3 (0)| 00:00:01 |
|* 32 | TABLE ACCESS BY INDEX ROWID | ET_TOKEN_CONTRS | 1 | 17 | 2 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | ET_TOKEN_CONTRS_INDX1 | 1 | | 1 (0)| 00:00:01 |
|* 34 | TABLE ACCESS BY INDEX ROWID | ET_TOKEN_CONTRS | 1 | 11 | 2 (0)| 00:00:01 |
|* 35 | INDEX RANGE SCAN | ET_TOKEN_CONTRS_INDX1 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
|
|
|
|
|
|
|
|
| Re: SQL Tuning Advise [message #638687 is a reply to message #638683] |
Thu, 18 June 2015 05:06  |
|
avineshr
Messages: 14
Registered: September 2012
Location: malaysia
|
Junior Member |
|
|
Query takes around 1 minute 10 seconds.Please find the full explain plan
ENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql_tuning_task
Tuning Task Owner : OPCTJAD_DBA
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 100
Completion Status : COMPLETED
Started at : 06/18/2015 08:53:12
Completed at : 06/18/2015 08:53:14
-------------------------------------------------------------------------------
Schema Name: OPCTJAD_DBA
SQL ID : c7km9z04cs2u3
SQL Text : SELECT TOKEN_NO, REG_PLACE, VEHICLE_NO, PORT, TERMINAL,
TOKEN_TYPE,
MOBILE_PREFIX, MOBILE_NO, EMAIL_ADDRESS,
NOTIFICATION_TYPE,
TKN_GEN_TYPE, PAYMENT_KEY, RECEIPT_NO, TOKEN_ACTIVE,
START_TIME,
END_TIME, SCH_DATE, SLOT_ID, STATUS, CNT, TKN_GEN_CODE
FROM (SELECT A.*, COUNT (*) OVER () CNT, ROWNUM AS RN
FROM (SELECT ET.TOKEN_NO, ET.REG_PLACE,
ET.VEHICLE_NO, ET.PORT,
ET.TERMINAL, ET.TOKEN_TYPE,
ET.MOBILE_PREFIX,
ET.MOBILE_NO, ET.EMAIL_ADDRESS,
ET.NOTIFICATION_TYPE,
ET.TKN_GEN_TYPE, T.PAYMENT_KEY,
T.RECEIPT_NO,
CASE
WHEN ET.IS_VALID = 1
AND GS.SCH_DATE >= TRUNC (SYSDATE,
'DDD')
THEN 'Active'
WHEN ET.IS_VALID = 1
AND GS.SCH_DATE < TRUNC (SYSDATE,
'DDD')
THEN 'Expired'
WHEN ET.IS_VALID = 0
THEN 'In Active'
END TOKEN_ACTIVE,
GS.START_TIME, GS.END_TIME,
GS.SCH_DATE, GS.SLOT_ID,
DECODE (T.TRX_STATUS,
'P', 'Pending',
'0', 'Error',
'1', 'SUCCESS'
) STATUS,
ET.TKN_GEN_CODE
FROM opctjad_dba.ET_TOKENS ET,
opctjad_dba.GATE_SCHEDULE GS, opctjad_dba.TOKEN_PAYMENT T
WHERE T.TOKEN_NO = ET.TOKEN_NO
AND GS.SLOT_ID = ET.SLOT_ID
AND ( (:B6 IS NOT NULL AND ET.TKN_ID =
:B7)
OR (:B6 IS NULL)
)
AND (ET.TKN_GEN_CODE = NVL (:B5,
ET.TKN_GEN_CODE))
AND ET.REG_PLACE = NVL (:B4, ET.REG_PLACE)
AND ET.VEHICLE_NO = NVL (:B3,
ET.VEHICLE_NO)
AND ET.PORT = :B2
AND ( (:B1 = 'E')
OR ( (:B1 IN (:A, :A))
AND NOT EXISTS (
SELECT 1
FROM
opctjad_dba.ET_TOKEN_CONTRS ETC
WHERE (EIR_DATE IS NOT
NULL
OR TQ_MSG = 2
)
AND ETC.TKN_ID =
ET.TKN_ID
AND ETC.IS_VALID = 1)
AND ET.IS_VALID = 1
AND T.TRX_STATUS IN (:A, :b)
AND ET.TXN_SRC <> :c
)
)
AND ( (:B1 = 'E')
OR ( (:B1 IN (:d, :e))
AND EXISTS (
SELECT 1
FROM
opctjad_dba.ET_TOKEN_CONTRS TC
WHERE TC.TKN_ID =
ET.TKN_ID
AND TC.IS_VALID = 1)
)
)
AND ( (:B1 = 'E')
OR ( :B1 IN (:f, :g)
AND GS.SCH_DATE >= TRUNC (SYSDATE,
:h)
)
)
ORDER BY T.TOKEN_DATE DESC) A)
WHERE RN BETWEEN NVL (:B9, 1) AND NVL (:B8, CNT)
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 4 of the execution plan.
The optimizer cannot merge a view that contains an "ORDER BY" clause unless
the statement is a "DELETE" or an "UPDATE" and the parent query is the top
most query in the statement.
- The optimizer could not merge the view at line ID 1 of the execution plan.
The optimizer cannot merge a view that contains windowing functions.
- At least one important bind value was missing for this sql statement. The
accuracy of the advisor's analysis may depend on all important bind values
being supplied.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 2082645971
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 830 | 38 (3)| 00:00:01 |
|* 1 | VIEW | | 2 | 830 | 38 (3)| 00:00:01 |
| 2 | WINDOW BUFFER | | 2 | 778 | 38 (3)| 00:00:01 |
| 3 | COUNT | | | | | |
| 4 | VIEW | | 2 | 778 | 38 (3)| 00:00:01 |
| 5 | SORT ORDER BY | | 2 | 246 | 38 (3)| 00:00:01 |
| 6 | CONCATENATION | | | | | |
|* 7 | FILTER | | | | | |
|* 8 | FILTER | | | | | |
| 9 | NESTED LOOPS | | 1 | 123 | 26 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 123 | 26 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 93 | 23 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | ET_TOKENS | 1 | 64 | 21 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID| GATE_SCHEDULE | 1 | 29 | 2 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | GATE_SCHEDULE_NU2 | 1 | | 1 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | N_TOKEN_PAYMENT_2 | 1 | | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | TOKEN_PAYMENT | 1 | 30 | 3 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | ET_TOKEN_CONTRS | 1 | 17 | 2 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | ET_TOKEN_CONTRS_INDX1 | 1 | | 1 (0)| 00:00:01 |
|* 19 | TABLE ACCESS BY INDEX ROWID | ET_TOKEN_CONTRS | 1 | 11 | 2 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | ET_TOKEN_CONTRS_INDX1 | 1 | | 1 (0)| 00:00:01 |
|* 21 | FILTER | | | | | |
|* 22 | FILTER | | | | | |
| 23 | NESTED LOOPS | | 1 | 123 | 11 (0)| 00:00:01 |
| 24 | NESTED LOOPS | | 1 | 123 | 11 (0)| 00:00:01 |
| 25 | NESTED LOOPS | | 1 | 93 | 8 (0)| 00:00:01 |
|* 26 | TABLE ACCESS BY INDEX ROWID| ET_TOKENS | 1 | 64 | 6 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | ET_TOKENS_IND_GTE2 | 19 | | 1 (0)| 00:00:01 |
|* 28 | TABLE ACCESS BY INDEX ROWID| GATE_SCHEDULE | 1 | 29 | 2 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | GATE_SCHEDULE_NU2 | 1 | | 1 (0)| 00:00:01 |
|* 30 | INDEX RANGE SCAN | N_TOKEN_PAYMENT_2 | 1 | | 2 (0)| 00:00:01 |
| 31 | TABLE ACCESS BY INDEX ROWID | TOKEN_PAYMENT | 1 | 30 | 3 (0)| 00:00:01 |
|* 32 | TABLE ACCESS BY INDEX ROWID | ET_TOKEN_CONTRS | 1 | 17 | 2 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | ET_TOKEN_CONTRS_INDX1 | 1 | | 1 (0)| 00:00:01 |
|* 34 | TABLE ACCESS BY INDEX ROWID | ET_TOKEN_CONTRS | 1 | 11 | 2 (0)| 00:00:01 |
|* 35 | INDEX RANGE SCAN | ET_TOKEN_CONTRS_INDX1 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=NVL(:B9,1) AND "RN"<=NVL(:B8,"CNT") AND NVL(:B8,"CNT")>=NVL(:B9,1))
7 - filter((:B1='E' OR (:B1=:A OR :B1=:A) AND NOT EXISTS (SELECT 0 FROM
"OPCTJAD_DBA"."ET_TOKEN_CONTRS" "ETC" WHERE "ETC"."TKN_ID"=:B1 AND "ETC"."IS_VALID"=1 AND ("EIR_DATE"
IS NOT NULL OR "TQ_MSG"=2)) AND "ET"."IS_VALID"=1 AND ("T"."TRX_STATUS"=:A OR "T"."TRX_STATUS"=:B) AND
"ET"."TXN_SRC"<>:C) AND (:B1='E' OR (:B1=:D OR :B1=:E) AND EXISTS (SELECT 0 FROM
"OPCTJAD_DBA"."ET_TOKEN_CONTRS" "TC" WHERE "TC"."TKN_ID"=:B2 AND "TC"."IS_VALID"=1)))
8 - filter(:B3 IS NULL)
12 - filter((:B6 IS NULL OR :B6 IS NOT NULL AND "ET"."TKN_ID"=TO_NUMBER(:B7)) AND
"ET"."TKN_GEN_CODE"=NVL(:B5,"ET"."TKN_GEN_CODE") AND "ET"."REG_PLACE"=NVL(:B4,"ET"."REG_PLACE") AND
"ET"."VEHICLE_NO" IS NOT NULL AND "ET"."PORT"=:B2)
13 - filter(:B1='E' OR (:B1=:F OR :B1=:G) AND "GS"."SCH_DATE">=TRUNC(SYSDATE@!,:H))
14 - access("GS"."SLOT_ID"="ET"."SLOT_ID")
15 - access("T"."TOKEN_NO"="ET"."TOKEN_NO")
17 - filter("ETC"."IS_VALID"=1 AND ("EIR_DATE" IS NOT NULL OR "TQ_MSG"=2))
18 - access("ETC"."TKN_ID"=:B1)
19 - filter("TC"."IS_VALID"=1)
20 - access("TC"."TKN_ID"=:B1)
21 - filter((:B1='E' OR (:B1=:A OR :B1=:A) AND NOT EXISTS (SELECT 0 FROM
"OPCTJAD_DBA"."ET_TOKEN_CONTRS" "ETC" WHERE "ETC"."TKN_ID"=:B1 AND "ETC"."IS_VALID"=1 AND ("EIR_DATE"
IS NOT NULL OR "TQ_MSG"=2)) AND "ET"."IS_VALID"=1 AND ("T"."TRX_STATUS"=:A OR "T"."TRX_STATUS"=:B) AND
"ET"."TXN_SRC"<>:C) AND (:B1='E' OR (:B1=:D OR :B1=:E) AND EXISTS (SELECT 0 FROM
"OPCTJAD_DBA"."ET_TOKEN_CONTRS" "TC" WHERE "TC"."TKN_ID"=:B2 AND "TC"."IS_VALID"=1)))
22 - filter(:B3 IS NOT NULL)
26 - filter((:B6 IS NULL OR :B6 IS NOT NULL AND "ET"."TKN_ID"=TO_NUMBER(:B7)) AND
"ET"."TKN_GEN_CODE"=NVL(:B5,"ET"."TKN_GEN_CODE") AND "ET"."REG_PLACE"=NVL(:B4,"ET"."REG_PLACE") AND
"ET"."PORT"=:B2)
27 - access("ET"."VEHICLE_NO"=:B3)
28 - filter(:B1='E' OR (:B1=:F OR :B1=:G) AND "GS"."SCH_DATE">=TRUNC(SYSDATE@!,:H))
29 - access("GS"."SLOT_ID"="ET"."SLOT_ID")
30 - access("T"."TOKEN_NO"="ET"."TOKEN_NO")
32 - filter("ETC"."IS_VALID"=1 AND ("EIR_DATE" IS NOT NULL OR "TQ_MSG"=2))
33 - access("ETC"."TKN_ID"=:B1)
34 - filter("TC"."IS_VALID"=1)
35 - access("TC"."TKN_ID"=:B1)
-------------------------------------------------------------------------------
|
|
|
|
| Re: SQL Tuning Advise [message #638689 is a reply to message #638687] |
Thu, 18 June 2015 05:30 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
|
Seems you are using the SQL Developer SQL Tuning Advisor. The explain plan shows the optimizer estimates 2 rows to fetch in much less time than what you state the query actually takes to return the rows. How many rows does it actually return? Are the statistics gathered up to date?
|
|
|
|
| Re: SQL Tuning Advise [message #638691 is a reply to message #638687] |
Thu, 18 June 2015 06:18 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
How many rows are returned if you remove the filter WHERE RN BETWEEN NVL (:B9, 1) AND NVL (:B8, CNT) |
|
|
|
|
|
| Re: SQL Tuning Advise [message #638753 is a reply to message #638692] |
Fri, 19 June 2015 11:47 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Start by collecting basic stats on your tables. There is no way each line of the query plan can actually be returning just a few rows (one row in most steps in fact), yet the query takes a minute to run. So you must be fetching a lot more data than the plan shows which means your cardinality estimates are wrong which means your basic query plan may be wrong. You can't really do good tuning with out the basics first being accounted for, and representative stats are part of the basics of tuning SQL in an Oracle database.
exec dbms_stats.gather_table_stats(...)
for each table the query uses. Then try again and post your results.
If you are interested in learning SQL Tuning, here are the first chapter of my book on SQL Tuning, the Scripts from the book, a link the book if you decide it is worth your time to buy it, and a document that describes how to provide the minimum amount of information needed to tune a query so people on this forum can help you. You can share these attachments with your friends since they are free.
Kevin
[Updated on: Fri, 19 June 2015 11:52] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Thu Feb 06 13:28:47 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
