1 2 Previous Next 19 Replies Latest reply: May 10, 2013 2:41 AM by user12090988 RSS

    This query is taking a long time:

    user12090988
      Hi All,
      I need help in tuning this query. The stats are as below:
      SQL> show parameter user_dump_dest
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      user_dump_dest                       string      /opt/app/oracle/diag/rdbms/ebi
                                                       zfile/EBIZFILE/trace
      SQL> show parameter optimizer
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      optimizer_capture_sql_plan_baselines boolean     FALSE
      optimizer_dynamic_sampling           integer     2
      optimizer_features_enable            string      11.2.0.2
      optimizer_index_caching              integer     0
      optimizer_index_cost_adj             integer     100
      optimizer_mode                       string      ALL_ROWS
      optimizer_secure_view_merging        boolean     TRUE
      optimizer_use_invisible_indexes      boolean     FALSE
      optimizer_use_pending_statistics     boolean     FALSE
      optimizer_use_sql_plan_baselines     boolean     TRUE
      SQL> show parameter db_file_multi
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      db_file_multiblock_read_count        integer     128
      SQL> show parameter db_block_size
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      db_block_size                        integer     8192
      SQL> show parameter cursor_sharing
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      cursor_sharing                       string      EXACT
      SQL> 
      SQL> column sname format a20
      SQL> column pname format a20
      SQL> column pval2 format a20
      SQL> 
      SQL> select
        2  sname, pname, pval1, pval2
        3  from
        4  sys.aux_stats$;
      
      SNAME                PNAME                     PVAL1 PVAL2
      -------------------- -------------------- ---------- --------------------
      SYSSTATS_INFO        STATUS                          COMPLETED
      SYSSTATS_INFO        DSTART                          11-03-2009 02:33
      SYSSTATS_INFO        DSTOP                           11-03-2009 02:33
      SYSSTATS_INFO        FLAGS                         1
      SYSSTATS_MAIN        CPUSPEEDNW            714.19791
      SYSSTATS_MAIN        IOSEEKTIM                    10
      SYSSTATS_MAIN        IOTFRSPEED                 4096
      SYSSTATS_MAIN        SREADTIM
      SYSSTATS_MAIN        MREADTIM
      SYSSTATS_MAIN        CPUSPEED
      SYSSTATS_MAIN        MBRC
      SYSSTATS_MAIN        MAXTHR
      SYSSTATS_MAIN        SLAVETHR
      
      13 rows selected.
      
      Elapsed: 00:00:00.03
      SQL> 
      SQL> explain plan for
        2   SELECT   A.TRANS_NO,
        3            A.TRANS_ID,
        4            A.REQUEST_IND,
        5            A.TRANS_STATUS_IND,
        6            A.TRANS_STATUS_DATE,
        7            A.DELIVERY_DATE,
        8            C.EMAIL_ADDR
        9        FROM     IIS_TRANS_MASTER A, M_TRANS B, IIS_REQUEST_TRANS C
       10       WHERE
       11             C.TRANS_NO = A.TRANS_NO
       12            AND A.TRANS_STATUS_IND = 'P'
       13            AND A.TRANS_ID = B.TRANS_ID
       14            AND A.TRANS_ID <> 'I009'
       15            AND A.TRANS_ID <> 'NPKG'
       16            AND B.TRANS_CO_TYPE = 'I'
       17            AND A.TRANS_ID NOT IN
       18                  ('P012', 'P13B', 'P13C', 'P14B', 'P14C', 'P015')
       19            AND B.DEPT = 'IRD'
       20  ORDER BY     A.CREATED_DATE;
      
      Explained.
      
      Elapsed: 00:00:00.01
      SQL> 
      SQL> set pagesize 1000;
      SQL> set linesize 170;
      SQL> @/opt/app/oracle/product/11.2.0/rdbms/admin/utlxplp.sql
      SQL> Rem
      SQL> Rem $Header: utlxplp.sql 23-jan-2002.08:55:23 bdagevil Exp $
      SQL> Rem
      SQL> Rem utlxplp.sql
      SQL> Rem
      SQL> Rem Copyright (c) 1998, 2002, Oracle Corporation.     All rights reserved.
      SQL> Rem
      SQL> Rem    NAME
      SQL> Rem      utlxplp.sql - UTiLity eXPLain Parallel plans
      SQL> Rem
      SQL> Rem    DESCRIPTION
      SQL> Rem      script utility to display the explain plan of the last explain plan
      SQL> Rem      command. Display also Parallel Query information if the plan happens to
      SQL> Rem      run parallel
      SQL> Rem
      SQL> Rem    NOTES
      SQL> Rem      Assume that the table PLAN_TABLE has been created. The script
      SQL> Rem      utlxplan.sql should be used to create that table
      SQL> Rem
      SQL> Rem      With SQL*plus, it is recomended to set linesize and pagesize before
      SQL> Rem      running this script. For example:
      SQL> Rem      set linesize 130
      SQL> Rem      set pagesize 0
      SQL> Rem
      SQL> Rem    MODIFIED   (MM/DD/YY)
      SQL> Rem    bdagevil     01/23/02 - rewrite with new dbms_xplan package
      SQL> Rem    bdagevil     04/05/01 - include CPU cost
      SQL> Rem    bdagevil     02/27/01 - increase Name column
      SQL> Rem    jihuang     06/14/00 - change order by to order siblings by.
      SQL> Rem    jihuang     05/10/00 - include plan info for recursive SQL in LE row source
      SQL> Rem    bdagevil     01/05/00 - make deterministic with order-by
      SQL> Rem    bdagevil     05/07/98 - Explain plan script for parallel plans
      SQL> Rem    bdagevil     05/07/98 - Created
      SQL> Rem
      SQL> 
      SQL> set markup html preformat on
      SQL> 
      SQL> Rem
      SQL> Rem Use the display table function from the dbms_xplan package to display the last
      SQL> Rem explain plan. Use default mode which will display only relevant information
      SQL> Rem
      SQL> select * from table(dbms_xplan.display());
      
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      Plan hash value: 3822676895
      
      ------------------------------------------------------------------------------------------------------------
      | Id  | Operation                      | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
      ------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT               |                   | 38075 |  3123K|       | 22550   (2)| 00:04:31 |
      |   1 |  SORT ORDER BY                 |                   | 38075 |  3123K|  3472K| 22550   (2)| 00:04:31 |
      |*  2 |   HASH JOIN                    |                   | 38075 |  3123K|  2120K| 21801   (2)| 00:04:22 |
      |   3 |    NESTED LOOPS                |                   |       |       |       |            |          |
      |   4 |     NESTED LOOPS               |                   | 38075 |  1673K|       |  4177   (1)| 00:00:51 |
      |*  5 |      TABLE ACCESS FULL         | M_TRANS           |    10 |    80 |       |     7   (0)| 00:00:01 |
      |*  6 |      INDEX RANGE SCAN          | IDX_IIS_TRANS_ID  | 39401 |       |       |   109   (3)| 00:00:02 |
      |*  7 |     TABLE ACCESS BY INDEX ROWID| IIS_TRANS_MASTER  |  3940 |   142K|       |  3186   (1)| 00:00:39 |
      |   8 |    TABLE ACCESS FULL           | IIS_REQUEST_TRANS |  2114K|    78M|       | 12368   (2)| 00:02:29 |
      ------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - access("C"."TRANS_NO"="A"."TRANS_NO")
         5 - filter("B"."DEPT"='IRD' AND "B"."TRANS_CO_TYPE"='I' AND "B"."TRANS_ID"<>'I009' AND
                    "B"."TRANS_ID"<>'NPKG' AND "B"."TRANS_ID"<>'P012' AND "B"."TRANS_ID"<>'P13B' AND
                    "B"."TRANS_ID"<>'P13C' AND "B"."TRANS_ID"<>'P14B' AND "B"."TRANS_ID"<>'P14C' AND
                    "B"."TRANS_ID"<>'P015')
         6 - access("A"."TRANS_ID"="B"."TRANS_ID")
             filter("A"."TRANS_ID"<>'I009' AND "A"."TRANS_ID"<>'NPKG' AND "A"."TRANS_ID"<>'P012' AND
                    "A"."TRANS_ID"<>'P13B' AND "A"."TRANS_ID"<>'P13C' AND "A"."TRANS_ID"<>'P14B' AND
                    "A"."TRANS_ID"<>'P14C' AND "A"."TRANS_ID"<>'P015')
         7 - filter("A"."TRANS_STATUS_IND"='P')
      
      29 rows selected.
      
      Elapsed: 00:00:00.02
      SQL> 
      SQL> rollback;
      
      Rollback complete.
      
      Elapsed: 00:00:00.00
      SQL> 
      SQL> rem Set the ARRAYSIZE according to your application
      SQL> set autotrace traceonly arraysize 100
      SQL> 
      SQL> alter session set tracefile_identifier = 'mytrace1';
      
      Session altered.
      
      Elapsed: 00:00:00.00
      SQL> 
      SQL> rem if you're using bind variables
      SQL> rem define them here
      SQL> 
      SQL> rem variable b_var1 number
      SQL> rem variable b_var2 varchar2(20)
      SQL> 
      SQL> rem and initialize them
      SQL> 
      SQL> rem exec :b_var1 := 1
      SQL> rem exec :b_var2 := 'DIAG'
      SQL> set pagesize 1000;
      SQL> set linesize 170;
      SQL> alter session set events '10046 trace name context forever, level 8';
      
      Session altered.
      
      Elapsed: 00:00:00.00
      SQL>  SELECT   A.TRANS_NO,
        2            A.TRANS_ID,
        3            A.REQUEST_IND,
        4            A.TRANS_STATUS_IND,
        5            A.TRANS_STATUS_DATE,
        6            A.DELIVERY_DATE,
        7            C.EMAIL_ADDR
        8        FROM     IIS_TRANS_MASTER A, M_TRANS B, IIS_REQUEST_TRANS C
        9       WHERE
       10             C.TRANS_NO = A.TRANS_NO
       11            AND A.TRANS_STATUS_IND = 'P'
       12            AND A.TRANS_ID = B.TRANS_ID
       13            AND A.TRANS_ID <> 'I009'
       14            AND A.TRANS_ID <> 'NPKG'
       15            AND B.TRANS_CO_TYPE = 'I'
       16            AND A.TRANS_ID NOT IN
       17                  ('P012', 'P13B', 'P13C', 'P14B', 'P14C', 'P015')
       18            AND B.DEPT = 'IRD'
       19  ORDER BY     A.CREATED_DATE;
      
      19 rows selected.
      
      Elapsed: 00:00:15.44
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 3822676895
      
      ------------------------------------------------------------------------------------------------------------
      | Id  | Operation                      | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
      ------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT               |                   | 38075 |  3123K|       | 22550   (2)| 00:04:31 |
      |   1 |  SORT ORDER BY                 |                   | 38075 |  3123K|  3472K| 22550   (2)| 00:04:31 |
      |*  2 |   HASH JOIN                    |                   | 38075 |  3123K|  2120K| 21801   (2)| 00:04:22 |
      |   3 |    NESTED LOOPS                |                   |       |       |       |            |          |
      |   4 |     NESTED LOOPS               |                   | 38075 |  1673K|       |  4177   (1)| 00:00:51 |
      |*  5 |      TABLE ACCESS FULL         | M_TRANS           |    10 |    80 |       |     7   (0)| 00:00:01 |
      |*  6 |      INDEX RANGE SCAN          | IDX_IIS_TRANS_ID  | 39401 |       |       |   109   (3)| 00:00:02 |
      |*  7 |     TABLE ACCESS BY INDEX ROWID| IIS_TRANS_MASTER  |  3940 |   142K|       |  3186   (1)| 00:00:39 |
      |   8 |    TABLE ACCESS FULL           | IIS_REQUEST_TRANS |  2114K|    78M|       | 12368   (2)| 00:02:29 |
      ------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - access("C"."TRANS_NO"="A"."TRANS_NO")
         5 - filter("B"."DEPT"='IRD' AND "B"."TRANS_CO_TYPE"='I' AND "B"."TRANS_ID"<>'I009' AND
                    "B"."TRANS_ID"<>'NPKG' AND "B"."TRANS_ID"<>'P012' AND "B"."TRANS_ID"<>'P13B' AND
                    "B"."TRANS_ID"<>'P13C' AND "B"."TRANS_ID"<>'P14B' AND "B"."TRANS_ID"<>'P14C' AND
                    "B"."TRANS_ID"<>'P015')
         6 - access("A"."TRANS_ID"="B"."TRANS_ID")
             filter("A"."TRANS_ID"<>'I009' AND "A"."TRANS_ID"<>'NPKG' AND "A"."TRANS_ID"<>'P012' AND
                    "A"."TRANS_ID"<>'P13B' AND "A"."TRANS_ID"<>'P13C' AND "A"."TRANS_ID"<>'P14B' AND
                    "A"."TRANS_ID"<>'P14C' AND "A"."TRANS_ID"<>'P015')
         7 - filter("A"."TRANS_STATUS_IND"='P')
      
      
      Statistics
      ----------------------------------------------------------
               23  recursive calls
                0  db block gets
           164826  consistent gets
            74235  physical reads
                0  redo size
             1839  bytes sent via SQL*Net to client
              524  bytes received via SQL*Net from client
                2  SQL*Net roundtrips to/from client
                1  sorts (memory)
                0  sorts (disk)
               19  rows processed
      
      SQL> 
      SQL> disconnect
      Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
        • 1. Re: This query is taking a long time:
          Manik
          Basic question, why is order by required there..? Are you sending this to some front end with ordered resultset or what?

          Just wanted to check the performance after creating index on IIS_REQUEST_TRANS.TRANS_NO

          Cheers,
          Manik.
          • 2. Re: This query is taking a long time:
            user12090988
            Hi Manik,
            I had checked that, and the index exists. Below is the list of index on that table:
            TABLE_NAME                     INDEX_NAME                     COLUMN_NAME
            ------------------------------ ------------------------------ ----------------------------------------
            IIS_REQUEST_TRANS              IDX_IIS_REQUEST_TRANS_REQ_ID   REQ_ID
            IIS_REQUEST_TRANS              SYS_C0060873                   TRANS_NO
            • 3. Re: This query is taking a long time:
              Manik
              Are the statistics gathered for the tables involved in the queries?

              you can also check this http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sqltun.htm

              Cheers,
              Manik.
              • 4. Re: This query is taking a long time:
                user12090988
                I had gathered stats for IIS_REQUEST_TRANS. Let me also do for the other two tables and check the performance. I shall update you.
                • 5. Re: This query is taking a long time:
                  user12090988
                  I gathered statistics for the other two tables too, but everything still remains the same.
                  • 6. Re: This query is taking a long time:
                    Manik
                    ok..

                    1) Is order by really required there?
                    2) Are the plans changed after gathering stats?
                    3) What does dbms_sqltune suggest?


                    Cheers,
                    Manik.
                    • 7. Re: This query is taking a long time:
                      user12090988
                      Hi Manik,
                      1) Not really. Order by may not be required.
                      2) After gathering the stats, below is the result:
                      NAME_COL_PLUS_SHOW_PARAM                                                         TYPE
                      -------------------------------------------------------------------------------- -----------
                      VALUE_COL_PLUS_SHOW_PARAM
                      ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                      user_dump_dest                                                                   string
                      /opt/app/oracle/diag/rdbms/ebizfile/EBIZFILE/trace
                      SQL> show parameter optimizer
                      
                      NAME_COL_PLUS_SHOW_PARAM                                                         TYPE
                      -------------------------------------------------------------------------------- -----------
                      VALUE_COL_PLUS_SHOW_PARAM
                      ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                      Elapsed: 00:00:00.05
                      SQL>  SELECT   A.TRANS_NO,
                        2            A.TRANS_ID,
                        3            A.REQUEST_IND,
                        4            A.TRANS_STATUS_IND,
                        5            A.TRANS_STATUS_DATE,
                        6            A.DELIVERY_DATE,
                        7            C.EMAIL_ADDR
                        8        FROM     IIS_TRANS_MASTER A, M_TRANS B, IIS_REQUEST_TRANS C
                        9       WHERE
                       10             C.TRANS_NO = A.TRANS_NO
                       11            AND A.TRANS_STATUS_IND = 'P'
                       12            AND A.TRANS_ID = B.TRANS_ID
                       13            AND A.TRANS_ID <> 'I009'
                       14            AND A.TRANS_ID <> 'NPKG'
                       15            AND B.TRANS_CO_TYPE = 'I'
                       16            AND A.TRANS_ID NOT IN
                       17                  ('P012', 'P13B', 'P13C', 'P14B', 'P14C', 'P015')
                       18            AND B.DEPT = 'IRD'
                       19  ORDER BY     A.CREATED_DATE;
                      
                      20 rows selected.
                      
                      Elapsed: 00:00:06.58
                      
                      Execution Plan
                      ----------------------------------------------------------
                      Plan hash value: 3822676895
                      
                      ------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                      | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                      ------------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT               |                   | 38091 |  3124K|       | 22553   (2)| 00:04:31 |
                      |   1 |  SORT ORDER BY                 |                   | 38091 |  3124K|  3472K| 22553   (2)| 00:04:31 |
                      |*  2 |   HASH JOIN                    |                   | 38091 |  3124K|  2128K| 21804   (2)| 00:04:22 |
                      |   3 |    NESTED LOOPS                |                   |       |       |       |            |          |
                      |   4 |     NESTED LOOPS               |                   | 38091 |  1673K|       |  4180   (1)| 00:00:51 |
                      |*  5 |      TABLE ACCESS FULL         | M_TRANS           |    10 |    80 |       |     7   (0)| 00:00:01 |
                      |*  6 |      INDEX RANGE SCAN          | IDX_IIS_TRANS_ID  | 39418 |       |       |   109   (3)| 00:00:02 |
                      |*  7 |     TABLE ACCESS BY INDEX ROWID| IIS_TRANS_MASTER  |  3942 |   142K|       |  3189   (1)| 00:00:39 |
                      |   8 |    TABLE ACCESS FULL           | IIS_REQUEST_TRANS |  2114K|    78M|       | 12368   (2)| 00:02:29 |
                      ------------------------------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                      
                         2 - access("C"."TRANS_NO"="A"."TRANS_NO")
                         5 - filter("B"."DEPT"='IRD' AND "B"."TRANS_CO_TYPE"='I' AND "B"."TRANS_ID"<>'I009' AND
                                    "B"."TRANS_ID"<>'NPKG' AND "B"."TRANS_ID"<>'P012' AND "B"."TRANS_ID"<>'P13B' AND
                                    "B"."TRANS_ID"<>'P13C' AND "B"."TRANS_ID"<>'P14B' AND "B"."TRANS_ID"<>'P14C' AND
                                    "B"."TRANS_ID"<>'P015')
                         6 - access("A"."TRANS_ID"="B"."TRANS_ID")
                             filter("A"."TRANS_ID"<>'I009' AND "A"."TRANS_ID"<>'NPKG' AND "A"."TRANS_ID"<>'P012' AND
                                    "A"."TRANS_ID"<>'P13B' AND "A"."TRANS_ID"<>'P13C' AND "A"."TRANS_ID"<>'P14B' AND
                                    "A"."TRANS_ID"<>'P14C' AND "A"."TRANS_ID"<>'P015')
                         7 - filter("A"."TRANS_STATUS_IND"='P')
                      
                      
                      Statistics
                      ----------------------------------------------------------
                               17  recursive calls
                                0  db block gets
                           158643  consistent gets
                            49083  physical reads
                                0  redo size
                             1917  bytes sent via SQL*Net to client
                              524  bytes received via SQL*Net from client
                                2  SQL*Net roundtrips to/from client
                                1  sorts (memory)
                                0  sorts (disk)
                               20  rows processed
                      I am yet to try the DBMS_SQLTUNE.
                      Are you able to give me in simplified form the steps in executing this query for the DBMS_SQLTUNE? Thanks!
                      • 8. Re: This query is taking a long time:
                        Manik
                        1) Not really. Order by may not be required.
                        Skip the order by, check the plans and proceed.
                        Are you able to give me in simplified form the steps in executing this query for the DBMS_SQLTUNE? Thanks!
                        example of using dbms_sqltune is as below:

                        http://www.oracle-base.com/articles/10g/automatic-sql-tuning-10g.php

                        Cheers,
                        Manik.
                        • 9. Re: This query is taking a long time:
                          DK2010
                          Hi,

                          As per my understanding this is the victim
                            8 |    TABLE ACCESS FULL           | IIS_REQUEST_TRANS |  2114K|    78M|       | 12368   (2)| 00:02:29 |
                          could be one reason is higher DB_FILE_MULTIBLOCK_READ_COUNT
                          http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams047.htm
                          The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high.
                          have you tried with hint
                          • 10. Re: This query is taking a long time:
                            367852
                            1. Didn't quite get the purpose of 2 lines in your sql
                             13            AND A.TRANS_ID  'I009'
                             14            AND A.TRANS_ID  'NPKG'
                            2. Can you print the out of below for your tables:
                            select last_analyzed, num_rows, sample_size
                            from user_tables
                            where table_name in ('IIS_TRANS_MASTER', 'M_TRANS', 'IIS_REQUEST_TRANS');

                            3. Please print the existing indexes for all 3 tables, I suppose you have already done it for IIS_REQUEST_TRANS.
                            • 11. Re: This query is taking a long time:
                              Martin Preiss
                              Hi,

                              your query returns 20 rows but the CBO estimates 38091 - so there is an error somewhere in the estimations.

                              1. step 5 does a Full Table Scan on M_TRANS and thinks that the given filter conditions limit the resultset to contain only 10 rows - so the first question would be if the 10 are plausible.

                              2. based on the small number of rows from step 5 the following step step 4 does a NL join with an access on IIS_TRANS_MASTER using the index IDX_IIS_TRANS_ID. Here the CBO estimates a resultset of 38091 rows - and again the question is: is this number in the right ballpark.

                              3. the last join is a hash join in step 2 combining the (estimated) 38091 rows from the preceeding NL join with the > 2M rows in IIS_REQUEST_TRANS. Since your resultset only contains 20 rows it's quite obvious that the hash join is not a good idea here and a NL join would perhaps be a better idea.

                              You could try to add a couple of hints (/*+ leading (B A C), use_nl(B A C) */ ) to check if the performance gets better with a NL join (instead of the hash join) in step 2. Another option would be to check the size of the resultsets for the filtered FTS on M_TRANS and the join of M_TRANS und IIS_TRANS_MASTER. This could be done by using simple count-queries or by the use of trace options (sql trace, rowsource statistics, or sql monitoring - if you have the appropriate licence)

                              Regards

                              Martin
                              • 12. Re: This query is taking a long time:
                                user13328581
                                Interesting, I have never used dbms_sqltune mechanism before.
                                • 13. Re: This query is taking a long time:
                                  APC
                                  user13328581 wrote:
                                  Interesting, I have never used dbms_sqltune mechanism before.
                                  That's probably because it requires licenses for both the Diagnostic and Tuning packs, which are chargeable extras to the Enterprise Edition, and hence found in only the most well-funded of places.

                                  Cheers, APC
                                  • 14. Re: This query is taking a long time:
                                    1006154
                                    Hi~!
                                    I think that the main reason of bad performance is big difference between "Actual Rows" and "Estimate Rows".
                                    so, if you use "gather_plan_statistics"hint dbms_xplan package, you can find the difference.
                                    for example.
                                    select /*+ gather_plan_statistics */ count(*) from emp;
                                    
                                    select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
                                    and, why don't you print the result of your 10046 trace? you just print result of auto trace.
                                    The "Rows' in the result of auto trace is just "Estimate Rows".

                                    Anyway, this query maybe faster, if you use hint like below.
                                    1) i agree with martin, so try that.
                                    2) if A.TRANS_STATUS_IND = 'P' is very good filter condition and IIS_TRANS_MASTER is not big. so leading A is good, like below.
                                    {code}
                                    SELECT /*+ leading(A B C) use_hash(B) use_nl(C) */
                                    A.TRANS_NO,
                                              A.TRANS_ID,
                                              A.REQUEST_IND,
                                              A.TRANS_STATUS_IND,
                                              A.TRANS_STATUS_DATE,
                                              A.DELIVERY_DATE,
                                              C.EMAIL_ADDR
                                         FROM     IIS_TRANS_MASTER A, M_TRANS B, IIS_REQUEST_TRANS C
                                         WHERE
                                              C.TRANS_NO = A.TRANS_NO
                                              AND A.TRANS_STATUS_IND = 'P'
                                              AND A.TRANS_ID = B.TRANS_ID
                                              AND A.TRANS_ID 'I009'
                                              AND A.TRANS_ID 'NPKG'
                                              AND B.TRANS_CO_TYPE = 'I'
                                              AND A.TRANS_ID NOT IN
                                                   ('P012', 'P13B', 'P13C', 'P14B', 'P14C', 'P015')
                                              AND B.DEPT = 'IRD'
                                    ORDER BY     A.CREATED_DATE;
                                    {code}

                                    Edited by: seankim on 2013. 5. 3 오후 9:33
                                    1 2 Previous Next