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 Go to next message
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 #638681 is a reply to message #638675] Thu, 18 June 2015 02:41 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
How long is the query actually taking, because according to that explain plan it should be fast?
Re: SQL Tuning Advise [message #638683 is a reply to message #638675] Thu, 18 June 2015 03:14 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3123
Registered: May 2013
Location: World Wide on the Web
Senior Member
Also, the explain plan is incomplete. Please read http://www.orafaq.com/forum/t/84315/ and post the required details.
Re: SQL Tuning Advise [message #638687 is a reply to message #638683] Thu, 18 June 2015 05:06 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3123
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 Go to previous messageGo to next message
John Watson
Messages: 7266
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)
and how many many rows are in the table ET_TOKENS ?
Re: SQL Tuning Advise [message #638692 is a reply to message #638687] Thu, 18 June 2015 09:08 Go to previous messageGo to next message
bpeasland
Messages: 51
Registered: February 2015
Location: United States
Member

* 12 | TABLE ACCESS FULL | ET_TOKENS | 1 | 64 | 21 (0)| 00:00:01 |

That line is a major contributor to the overall execution plan cost. How mnay rows are in the ET_TOKENS table? Is there an index on the TOKEN_NO and or SLOT_ID columns that this table has in the WHERE clause? If not, you might want to play around with adding indexes. Also make sure any stats on those indexes are up-to-date.

HTH,
Brian
Re: SQL Tuning Advise [message #638753 is a reply to message #638692] Fri, 19 June 2015 11:47 Go to previous messageGo to next message
Kevin Meade
Messages: 2102
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

Re: SQL Tuning Advise [message #638755 is a reply to message #638753] Fri, 19 June 2015 11:57 Go to previous message
BlackSwan
Messages: 25859
Registered: January 2009
Location: SoCal
Senior Member
https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=oracle+bind+variable+peeking
Previous Topic: ANALYZE and GATHER STATS
Next Topic: Oracle Optimizer cost (merged)
Goto Forum:
  


Current Time: Sun Feb 25 14:34:21 CST 2018

Total time taken to generate the page: 0.02391 seconds