1 2 Previous Next 19 Replies Latest reply: May 10, 2013 2:41 AM by user12090988 Go to original post RSS
      • 15. Re: This query is taking a long time:
        user12090988
        Thanks for all your response! You all have been of very great help! Hats off to you all.

        @Seankim: The new rewritten query recomended by you seems to be running faster. I have given below the stats for it. Please clarify my doubt below:
        The estimated time as below shows about 12 seconds. But the time in the explain plan shows about 16 mins. What should I be looking at?
        SQL> explain plan for
          2   SELECT   /*+ leading(A B C) use_hash(B) use_nl(C) */
          3             A.TRANS_NO,
          4                 A.TRANS_ID,
          5                 A.REQUEST_IND,
          6                 A.TRANS_STATUS_IND,
          7                 A.TRANS_STATUS_DATE,
          8                 A.DELIVERY_DATE,
          9                 C.EMAIL_ADDR
         10             FROM   IIS_TRANS_MASTER A, M_TRANS B, IIS_REQUEST_TRANS C
         11            WHERE
         12                  C.TRANS_NO = A.TRANS_NO
         13                 AND A.TRANS_STATUS_IND = 'P'
         14                 AND A.TRANS_ID = B.TRANS_ID
         15                 AND A.TRANS_ID <> 'I009'
         16                 AND A.TRANS_ID <> 'NPKG'
         17                 AND B.TRANS_CO_TYPE = 'I'
         18                 AND A.TRANS_ID NOT IN
         19                       ('P012', 'P13B', 'P13C', 'P14B', 'P14C', 'P015')
         20                 AND B.DEPT = 'IRD'
         21       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: 127425677
        
        ------------------------------------------------------------------------------------------------------------------
        | Id  | Operation                       | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
        ------------------------------------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT                |                        | 38091 |  3124K|       | 81590   (1)| 00:16:20 |
        |   1 |  SORT ORDER BY                  |                        | 38091 |  3124K|  3472K| 81590   (1)| 00:16:20 |
        |   2 |   NESTED LOOPS                  |                        |       |       |       |            |          |
        |   3 |    NESTED LOOPS                 |                        | 38091 |  3124K|       | 80842   (1)| 00:16:11 |
        |*  4 |     HASH JOIN                   |                        | 38091 |  1673K|  8488K|  4738   (1)| 00:00:57 |
        |*  5 |      TABLE ACCESS BY INDEX ROWID| IIS_TRANS_MASTER       |   177K|  6409K|       |  4315   (1)| 00:00:52 |
        |*  6 |       INDEX RANGE SCAN          | IIS_TRANS_MASTER_IDX01 |   212K|       |       |   394   (2)| 00:00:05 |
        |*  7 |      TABLE ACCESS FULL          | M_TRANS                |    10 |    80 |       |     7   (0)| 00:00:01 |
        |*  8 |     INDEX UNIQUE SCAN           | SYS_C0060873           |     1 |       |       |     1   (0)| 00:00:01 |
        |   9 |    TABLE ACCESS BY INDEX ROWID  | IIS_REQUEST_TRANS      |     1 |    39 |       |     2   (0)| 00:00:01 |
        ------------------------------------------------------------------------------------------------------------------
        
        Predicate Information (identified by operation id):
        ---------------------------------------------------
        
           4 - access("A"."TRANS_ID"="B"."TRANS_ID")
           5 - 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')
           6 - access("A"."TRANS_STATUS_IND"='P')
           7 - 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')
           8 - access("C"."TRANS_NO"="A"."TRANS_NO")
        
        29 rows selected.
        
        Elapsed: 00:00:00.01
        SQL> 
        SQL> rollback;
        
        Rollback complete.
        
        Elapsed: 00:00:00.01
        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.01
        SQL>  SELECT   /*+ leading(A B C) use_hash(B) use_nl(C) */
          2             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;
        
        23 rows selected.
        
        Elapsed: 00:00:00.12
        
        Execution Plan
        ----------------------------------------------------------
        Plan hash value: 127425677
        
        ------------------------------------------------------------------------------------------------------------------
        | Id  | Operation                       | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
        ------------------------------------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT                |                        | 38091 |  3124K|       | 81590   (1)| 00:16:20 |
        |   1 |  SORT ORDER BY                  |                        | 38091 |  3124K|  3472K| 81590   (1)| 00:16:20 |
        |   2 |   NESTED LOOPS                  |                        |       |       |       |            |          |
        |   3 |    NESTED LOOPS                 |                        | 38091 |  3124K|       | 80842   (1)| 00:16:11 |
        |*  4 |     HASH JOIN                   |                        | 38091 |  1673K|  8488K|  4738   (1)| 00:00:57 |
        |*  5 |      TABLE ACCESS BY INDEX ROWID| IIS_TRANS_MASTER       |   177K|  6409K|       |  4315   (1)| 00:00:52 |
        |*  6 |       INDEX RANGE SCAN          | IIS_TRANS_MASTER_IDX01 |   212K|       |       |   394   (2)| 00:00:05 |
        |*  7 |      TABLE ACCESS FULL          | M_TRANS                |    10 |    80 |       |     7   (0)| 00:00:01 |
        |*  8 |     INDEX UNIQUE SCAN           | SYS_C0060873           |     1 |       |       |     1   (0)| 00:00:01 |
        |   9 |    TABLE ACCESS BY INDEX ROWID  | IIS_REQUEST_TRANS      |     1 |    39 |       |     2   (0)| 00:00:01 |
        ------------------------------------------------------------------------------------------------------------------
        
        Predicate Information (identified by operation id):
        ---------------------------------------------------
        
           4 - access("A"."TRANS_ID"="B"."TRANS_ID")
           5 - 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')
           6 - access("A"."TRANS_STATUS_IND"='P')
           7 - 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')
           8 - access("C"."TRANS_NO"="A"."TRANS_NO")
        
        
        Statistics
        ----------------------------------------------------------
                 23  recursive calls
                  0  db block gets
               2155  consistent gets
                 49  physical reads
                  0  redo size
               2023  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)
                 23  rows processed
        Edited by: user12090988 on May 5, 2013 8:17 PM
        • 16. Re: This query is taking a long time:
          user12090988
          Hi Seankim,
          Thanks for your response. I had one doubt. Why do we have to force it using Hits to decide the leading table? Why does the database not take it by itself?
          • 17. Re: This query is taking a long time:
            APC
            user12090988 wrote:
            Why do we have to force it using Hits to decide the leading table? Why does the database not take it by itself?
            Because the CBO can't always figure things out for itself. The optimizer is just a Rules Engine, albeit a very sophisticated one. There are some times when the CBO's default starting point for a given type of query lead it down a path from which it can't possibly reach the optimal execution plan. In those cases we can help it out by using hints.

            This is what Jonathon Lewis calls [url http://jonathanlewis.wordpress.com/2009/04/28/strategic-hints/]strategic hinting.

            Of course you're right to be cautious about hinting production code: most of us find it easier to be dumber than the CBO than to be smarter ;)

            Cheers, APC
            • 18. Re: This query is taking a long time:
              user12090988
              Thanks!
              • 19. Re: This query is taking a long time:
                user12090988
                All you guys are experts..
                1 2 Previous Next