1 2 Previous Next 26 Replies Latest reply: Feb 8, 2010 12:23 PM by sb92075 RSS

    SQL taking very long time to execute

    Yasu
      Hi All,

      We have an sql query which is taking very long time to execute and below are the details of the sql.
      SQL> select * from v$version;
      
      BANNER
      ----------------------------------------------------------------
      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
      PL/SQL Release 10.2.0.4.0 - Production
      CORE    10.2.0.4.0      Production
      TNS for Solaris: Version 10.2.0.4.0 - Production
      NLSRTL Version 10.2.0.4.0 - Production
      
      SQL> explain plan for
        2  SELECT  A.BROKER_NAME,A.PARENT_BROKER_CODE, MAX(C.LAST_UPDATE_DATE) as LAST_SUBMISSION_DATE,
        3   BROKER_CODE as CHILD_BROKER_CODE,d.LOCATION
        FROM   CM_CACHE_BROKER a, REM_TAG_VALUE b, CM_ENUM_TAG c,CM_CACHE_BROKER_detail d
        4    5   WHERE   A.PARENT_BROKER_CODE = B.TAG_VALUE
        6           AND EXISTS
        7                 (SELECT   1
        8                    FROM   REM_TAG_LIST d
        9                   WHERE   D.TAG_LIST_ID = B.TAG_LIST_ID
       10                           AND D.TAG_LIST_CODE = 'FCBrokerCode')
       11           AND B.TAG_VALUE_ID = C.TAG_VALUE_ID
       12           AND a.BROKER_ID=d.BROKER_ID
       13  GROUP BY BROKER_NAME,PARENT_BROKER_CODE,BROKER_CODE,location order by BROKER_NAME;
      
      Explained.
      
      SQL> select * from table(dbms_xplan.display);
      
      PLAN_TABLE_OUTPUT
      ------------------------------------------------------------------------------------------------------------------------------------------------------
      Plan hash value: 1299486493
      
      --------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                           | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
      --------------------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                    |                        |  7947 |  1544K|       | 10839   (1)| 00:02:11 |       |       |
      |   1 |  SORT GROUP BY                      |                        |  7947 |  1544K|  3368K| 10839   (1)| 00:02:11 |       |       |
      |*  2 |   HASH JOIN                         |                        |  7947 |  1544K|       | 10493   (1)| 00:02:06 |       |       |
      |   3 |    TABLE ACCESS FULL                | CM_CACHE_BROKER_DETAIL |  6266 |   189K|       |    29   (0)| 00:00:01 |       |       |
      |   4 |    TABLE ACCESS BY LOCAL INDEX ROWID| CM_ENUM_TAG            |    41 |  1025 |       |  4970   (1)| 00:01:00 |       |       |
      |   5 |     NESTED LOOPS                    |                        |  4796 |   786K|       | 10463   (1)| 00:02:06 |       |       |
      |*  6 |      HASH JOIN                      |                        |   117 | 16731 |       |   978   (1)| 00:00:12 |       |       |
      |   7 |       TABLE ACCESS FULL             | CM_CACHE_BROKER        |  3781 |   158K|       |    15   (0)| 00:00:01 |       |       |
      |   8 |       NESTED LOOPS                  |                        | 74085 |  7234K|       |   962   (1)| 00:00:12 |       |       |
      |   9 |        TABLE ACCESS BY INDEX ROWID  | REM_TAG_LIST           |     1 |    43 |       |     1   (0)| 00:00:01 |       |       |
      |* 10 |         INDEX UNIQUE SCAN           | AK1_REM_TAG_LIST_CODE  |     1 |       |       |     0   (0)| 00:00:01 |       |       |
      |  11 |        TABLE ACCESS BY INDEX ROWID  | REM_TAG_VALUE          | 74085 |  4123K|       |   961   (1)| 00:00:12 |       |       |
      |* 12 |         INDEX RANGE SCAN            | REM_TAG_VALUE_IDX1     | 74085 |       |       |   115   (1)| 00:00:02 |       |       |
      |  13 |      PARTITION HASH ALL             |                        |  4120 |       |       |    32   (0)| 00:00:01 |     1 |    16 |
      |* 14 |       INDEX RANGE SCAN              | IDX5_ENUM_TAG          |  4120 |       |       |    32   (0)| 00:00:01 |     1 |    16 |
      --------------------------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - access("A"."BROKER_ID"="D"."BROKER_ID")
         6 - access("B"."TAG_VALUE"=SYS_OP_C2C("A"."PARENT_BROKER_CODE"))
        10 - access("D"."TAG_LIST_CODE"=U'FCBrokerCode')
        12 - access("D"."TAG_LIST_ID"="B"."TAG_LIST_ID")
        14 - access("B"."TAG_VALUE_ID"="C"."TAG_VALUE_ID")
      
      30 rows selected.
      Currently i have included /*+ gather_plan_statistics */ hint to collect actual and estimate of cardinality, but sql is still running for about 1 hour and is waiting on db file sequential read wait event.

      How can i find actual and estimated cardinality for this sql without executing it...as it is taking very long time to comlete.

      -Yasser
        • 1. Re: SQL taking very long time to execute
          Charles Hooper
          YasserRACDBA wrote:
          Hi All,

          We have an sql query which is taking very long time to execute and below are the details of the sql.
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          
          2 - access("A"."BROKER_ID"="D"."BROKER_ID")
          6 - access("B"."TAG_VALUE"=SYS_OP_C2C("A"."PARENT_BROKER_CODE"))
          10 - access("D"."TAG_LIST_CODE"=U'FCBrokerCode')
          12 - access("D"."TAG_LIST_ID"="B"."TAG_LIST_ID")
          14 - access("B"."TAG_VALUE_ID"="C"."TAG_VALUE_ID")
          
          30 rows selected.
          Currently i have included /*+ gather_plan_statistics */ hint to collect actual and estimate of cardinality, but sql is still running for about 1 hour and is waiting on db file sequential read wait event.

          How can i find actual and estimated cardinality for this sql without executing it...as it is taking very long time to comlete.

          -Yasser
          Hopefully, the query has completed by now. You have at least one implicit (character set to character set) data type conversion happening, as indicated by the SYS_OP_C2C function in the predicates. I have not yet determined the meaning of the U in the following predicate:
          10 - access("D"."TAG_LIST_CODE"=U'FCBrokerCode')
          If you knew the SQL_ID for the SQL statement, you might be able to monitor the view V$SQL_PLAN_STATISTICS_ALL to determine the actual cardinality to this point (not verified).

          Implicit Character Set to Character Set References:
          http://jonathanlewis.wordpress.com/2007/07/29/nls/
          http://joze-senegacnik.blogspot.com/2009/12/what-is-purpose-of-sysopc2c-internal.html

          A, B, C, D aliases for tables... it really makes it hard to understand the SQL statement and the execution plan, please see:
          http://jonathanlewis.wordpress.com/2010/01/26/aliases/

          Charles Hooper
          Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
          http://hoopercharles.wordpress.com/
          IT Manager/Oracle DBA
          K&M Machine-Fabricating, Inc.
          • 2. Re: SQL taking very long time to execute
            Yasu
            Was eagerly waiting for someone's response...thanks for involving in this thread.

            You are correct, implicit conversion is happening as indicated by SYS_OP_C2C function, datatype nvarchar is getting converted according to table CM_CACHE_BROKER definition. But i am totally confused with "U" meaning in predicate section, do you have any idea on this??

            Un-fortunately query was interrupted, and i am executing it again. Will monitor V$SQL_PLAN_STATISTICS_ALL during next execution.

            Will post the details once query completes. But what if in case query takes abnormal time like one or two days....how to find actual and estimated cardinality??How to perform Cardinality Feedback Tuning??

            -Yasser
            • 3. Re: SQL taking very long time to execute
              user503699
              YasserRACDBA wrote:
              Was eagerly waiting for someone's response...thanks for involving in this thread.

              You are correct, implicit conversion is happening as indicated by SYS_OP_C2C function, datatype nvarchar is getting converted according to table CM_CACHE_BROKER definition. But i am totally confused with "U" meaning in predicate section, do you have any idea on this??
              Yasser,

              The 'U' means the column is of type NVACHAR2 or NCHAR) and the passed string is a VARCHAR2
              6 - access("B"."TAG_VALUE"=SYS_OP_C2C("A"."PARENT_BROKER_CODE"))
                10 - access("D"."TAG_LIST_CODE"=U'FCBrokerCode')
              The above means D.TAG_LIST_CODE is of NVACHAR2 type whereas B.TAG_VALUE and A.PARENT_BROKER_CODE are NVARCHAR2 and VARCHAR2 (orcorresponding NCHAR and CHAR) datatypes respectively.
              • 4. Re: SQL taking very long time to execute
                Dom Brooks
                I've not seen this U notation before.

                I would guess that where the N notation indicates that the following string literal is an NCHAR string literal, the U would be unicode?
                However, unlike N (and Q notation for quotes, I've not seen this U documented anywhere.

                Here it states that:
                The TO_NCHAR function converts the data at run time, while the N function converts the data at compilation time.
                Maybe there is a similar distinction with U and UNISTR?

                Confirmation is not helped by the inconsistency of terms used to describe N which is referred in official documentation as notation, function and "nchar capability".
                • 5. Re: SQL taking very long time to execute
                  user503699
                  YasserRACDBA wrote:
                  Will post the details once query completes. But what if in case query takes abnormal time like one or two days....how to find actual and estimated cardinality??How to perform Cardinality Feedback Tuning??
                  I am sure Charles will be able to guide you on Cardinality Feedback Tuning but you may want to try executing the query without ORDER BY clause to see if it completes in reasonable time. If it does, you have 2 leads to follow:
                  1) You can use GATHER_PLAN_STATISTICS hint on query without ORDER BY and (hopefully) it will also complete in reasonable time &
                  2) You will have identified that SORT operation is one of the culprits for poor performance.

                  Your EXPLAIN PLAN suugests that your SORT will be spilling to disk (although I am not sure if soring about 7K rows should not be possible with a complete in-memory sort).

                  p.s. Had a second look at my post (should have done that before posting) and I guess the ORDER BY argument is not valid since the plan does not have explicit ORDER BY step but a SORT GROUP BY step. But I would be still interested in finding
                  a) why did oracle use SORT GROUP BY instead of HASH GROUP BY ?
                  b) Your SORT GROUP BY step and HASH JOIN step just below it expects to generate same number of rows. This looks unusual and the only reasons for the same that I can think of are
                  i) Some tables do not have up-to-date statistics OR
                  ii) GROUP BY in your query does not matter

                  You may want to look into this.

                  Edited by: user503699 on Feb 2, 2010 7:57 PM

                  p.s. One more update: Turns out ORDER BY does change things. It seems the answer to my first question above is oracle decides to use SORT GROUP BY in order to avoide additional SORT opertaion needed by ORDER BY clause. I remember reading somewhere that HASH GROUP BY operation is more efficient than SORT GROUP BY operation. So you may indeed want to test the query without ORDER BY clause to see if things change (for better). I know you need ORDER BY clause but if you can run the query without ORDER BY clause in reasonable time, the you can at least perform Cardinality Feedback Tuning by using GATHER_PLAN_STATISTICS output.
                  (And now I will shut up and not edit this post :) )

                  Edited by: user503699 on Feb 2, 2010 8:17 PM
                  • 6. Re: SQL taking very long time to execute
                    user503699
                    Dombrooks,

                    I accidently came across 'U' sign when I was testing something else (serendipity, I guess :) )
                    Here is a simplest way to see 'U'
                    SQL> create table a (a1 varchar2(10), a2 nvarchar2(10));
                    
                    Table created.
                    
                    SQL> insert into a values ('a','a') ;
                    
                    1 row created.
                    
                    SQL> commit;
                    
                    Commit complete.
                    
                    SQL> explain plan for select * from a where a1 = a2 and a2 = 'a' ;
                    
                    Explained.
                    
                    SQL> select * from table(dbms_xplan.display) ;
                    
                    PLAN_TABLE_OUTPUT
                    --------------------------------------------------------------------------------
                    Plan hash value: 1928539813
                    
                    --------------------------------------------------------------------------
                    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                    --------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT  |      |     1 |    19 |     3   (0)| 00:00:01 |
                    |*  1 |  TABLE ACCESS FULL| A    |     1 |    19 |     3   (0)| 00:00:01 |
                    --------------------------------------------------------------------------
                    
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                    
                    PLAN_TABLE_OUTPUT
                    --------------------------------------------------------------------------------
                    
                       1 - filter("A2"=U'a' AND SYS_OP_C2C("A1")=U'a')
                    
                    Note
                    -----
                       - dynamic sampling used for this statement
                    
                    17 rows selected.
                    • 7. Re: SQL taking very long time to execute
                      Charles Hooper
                      Charles Hooper wrote:
                      A, B, C, D aliases for tables... it really makes it hard to understand the SQL statement and the execution plan, please see:
                      http://jonathanlewis.wordpress.com/2010/01/26/aliases/
                      Notice that there are two tables assigned the alias of "D" - that makes it a bit more challenging to see what is happening. If we try to fix the aliases, the SQL statement might look like this:
                      SELECT
                        CCB.BROKER_NAME,
                        CCB.PARENT_BROKER_CODE,
                        MAX(CET.LAST_UPDATE_DATE) as LAST_SUBMISSION_DATE,
                        BROKER_CODE as CHILD_BROKER_CODE,
                        CBC.LOCATION
                      FROM
                        CM_CACHE_BROKER CCB,
                        REM_TAG_VALUE RTV,
                        CM_ENUM_TAG CET,
                        CM_CACHE_BROKER_detail CBD
                      WHERE
                        CCB.PARENT_BROKER_CODE = RTV.TAG_VALUE
                        AND EXISTS
                          (SELECT
                            1
                          FROM
                            REM_TAG_LIST RTL
                          WHERE
                            RTL.TAG_LIST_ID = RTV.TAG_LIST_ID
                            AND RTL.TAG_LIST_CODE = 'FCBrokerCode')
                        AND RTV.TAG_VALUE_ID = CET.TAG_VALUE_ID
                        AND CCB.BROKER_ID=CBC.BROKER_ID
                      GROUP BY
                        BROKER_NAME,
                        PARENT_BROKER_CODE,
                        BROKER_CODE,
                        location
                      order by
                        BROKER_NAME;
                      The explain plan would then look like this:
                      --------------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                           | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
                      --------------------------------------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT                    |                        |  7947 |  1544K|       | 10839   (1)| 00:02:11 |       |       |
                      |   1 |  SORT GROUP BY                      |                        |  7947 |  1544K|  3368K| 10839   (1)| 00:02:11 |       |       |
                      |*  2 |   HASH JOIN                         |                        |  7947 |  1544K|       | 10493   (1)| 00:02:06 |       |       |
                      |   3 |    TABLE ACCESS FULL                | CM_CACHE_BROKER_DETAIL |  6266 |   189K|       |    29   (0)| 00:00:01 |       |       |
                      |   4 |    TABLE ACCESS BY LOCAL INDEX ROWID| CM_ENUM_TAG            |    41 |  1025 |       |  4970   (1)| 00:01:00 |       |       |
                      |   5 |     NESTED LOOPS                    |                        |  4796 |   786K|       | 10463   (1)| 00:02:06 |       |       |
                      |*  6 |      HASH JOIN                      |                        |   117 | 16731 |       |   978   (1)| 00:00:12 |       |       |
                      |   7 |       TABLE ACCESS FULL             | CM_CACHE_BROKER        |  3781 |   158K|       |    15   (0)| 00:00:01 |       |       |
                      |   8 |       NESTED LOOPS                  |                        | 74085 |  7234K|       |   962   (1)| 00:00:12 |       |       |
                      |   9 |        TABLE ACCESS BY INDEX ROWID  | REM_TAG_LIST           |     1 |    43 |       |     1   (0)| 00:00:01 |       |       |
                      |* 10 |         INDEX UNIQUE SCAN           | AK1_REM_TAG_LIST_CODE  |     1 |       |       |     0   (0)| 00:00:01 |       |       |
                      |  11 |        TABLE ACCESS BY INDEX ROWID  | REM_TAG_VALUE          | 74085 |  4123K|       |   961   (1)| 00:00:12 |       |       |
                      |* 12 |         INDEX RANGE SCAN            | REM_TAG_VALUE_IDX1     | 74085 |       |       |   115   (1)| 00:00:02 |       |       |
                      |  13 |      PARTITION HASH ALL             |                        |  4120 |       |       |    32   (0)| 00:00:01 |     1 |    16 |
                      |* 14 |       INDEX RANGE SCAN              | IDX5_ENUM_TAG          |  4120 |       |       |    32   (0)| 00:00:01 |     1 |    16 |
                      --------------------------------------------------------------------------------------------------------------------------------------
                       
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                         2 - access("CCB"."BROKER_ID"="CBD"."BROKER_ID")
                         6 - access("RTV"."TAG_VALUE"=SYS_OP_C2C("CCB"."PARENT_BROKER_CODE"))
                        10 - access("RTL"."TAG_LIST_CODE"=U'FCBrokerCode')
                        12 - access("RTL"."TAG_LIST_ID"="RTV"."TAG_LIST_ID")
                        14 - access("RTV"."TAG_VALUE_ID"="CET"."TAG_VALUE_ID")
                      Please provide the output of the following two SQL statements:
                      SELECT
                        COUNT(*)
                      FROM
                        CM_CACHE_BROKER_DETAIL;
                       
                      SELECT
                        COUNT(*)
                      FROM
                        REM_TAG_LIST
                      WHERE
                        TAG_LIST_CODE='FCBrokerCode';
                      Charles Hooper
                      Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
                      http://hoopercharles.wordpress.com/
                      IT Manager/Oracle DBA
                      K&M Machine-Fabricating, Inc.
                      • 8. Re: SQL taking very long time to execute
                        Yasu
                        After fixing alias
                        SQL> explain plan for
                          2  SELECT
                          3    CCB.BROKER_NAME,
                          4    CCB.PARENT_BROKER_CODE,
                          5    MAX(CET.LAST_UPDATE_DATE) as LAST_SUBMISSION_DATE,
                          6    BROKER_CODE as CHILD_BROKER_CODE,
                          7    CBD.LOCATION
                          8  FROM
                          9    CM_CACHE_BROKER CCB,
                          REM_TAG_VALUE RTV,
                          CM_ENUM_TAG CET,
                          CM_CACHE_BROKER_detail CBD
                        WHERE
                          CCB.PARENT_BROKER_CODE = RTV.TAG_VALUE
                          AND EXISTS
                            (SELECT
                              1
                            FROM
                              REM_TAG_LIST RTL
                            WHERE
                              RTL.TAG_LIST_ID = RTV.TAG_LIST_ID
                              AND RTL.TAG_LIST_CODE = 'FCBrokerCode')
                         10   11   12   13   14   15   16   17   18   19   20   21   22   23    AND RTV.TAG_VALUE_ID = CET.TAG_VALUE_ID
                         24    AND CCB.BROKER_ID=CBD.BROKER_ID
                         25  GROUP BY
                         26    BROKER_NAME,
                         27    PARENT_BROKER_CODE,
                         28    BROKER_CODE,
                         29    location
                         30  order by
                         31    BROKER_NAME;
                        
                        Explained.
                        
                        Elapsed: 00:00:00.11
                        SQL> select * from table(dbms_xplan.display);
                        
                        PLAN_TABLE_OUTPUT
                        ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                        Plan hash value: 1299486493
                        
                        --------------------------------------------------------------------------------------------------------------------------------------
                        | Id  | Operation                           | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
                        --------------------------------------------------------------------------------------------------------------------------------------
                        |   0 | SELECT STATEMENT                    |                        |  7947 |  1544K|       | 10839   (1)| 00:02:11 |       |       |
                        |   1 |  SORT GROUP BY                      |                        |  7947 |  1544K|  3368K| 10839   (1)| 00:02:11 |       |       |
                        |*  2 |   HASH JOIN                         |                        |  7947 |  1544K|       | 10493   (1)| 00:02:06 |       |       |
                        |   3 |    TABLE ACCESS FULL                | CM_CACHE_BROKER_DETAIL |  6266 |   189K|       |    29   (0)| 00:00:01 |       |       |
                        |   4 |    TABLE ACCESS BY LOCAL INDEX ROWID| CM_ENUM_TAG            |    41 |  1025 |       |  4970   (1)| 00:01:00 |       |       |
                        |   5 |     NESTED LOOPS                    |                        |  4796 |   786K|       | 10463   (1)| 00:02:06 |       |       |
                        |*  6 |      HASH JOIN                      |                        |   117 | 16731 |       |   978   (1)| 00:00:12 |       |       |
                        |   7 |       TABLE ACCESS FULL             | CM_CACHE_BROKER        |  3781 |   158K|       |    15   (0)| 00:00:01 |       |       |
                        |   8 |       NESTED LOOPS                  |                        | 74085 |  7234K|       |   962   (1)| 00:00:12 |       |       |
                        |   9 |        TABLE ACCESS BY INDEX ROWID  | REM_TAG_LIST           |     1 |    43 |       |     1   (0)| 00:00:01 |       |       |
                        |* 10 |         INDEX UNIQUE SCAN           | AK1_REM_TAG_LIST_CODE  |     1 |       |       |     0   (0)| 00:00:01 |       |       |
                        |  11 |        TABLE ACCESS BY INDEX ROWID  | REM_TAG_VALUE          | 74085 |  4123K|       |   961   (1)| 00:00:12 |       |       |
                        |* 12 |         INDEX RANGE SCAN            | REM_TAG_VALUE_IDX1     | 74085 |       |       |   115   (1)| 00:00:02 |       |       |
                        |  13 |      PARTITION HASH ALL             |                        |  4120 |       |       |    32   (0)| 00:00:01 |     1 |    16 |
                        |* 14 |       INDEX RANGE SCAN              | IDX5_ENUM_TAG          |  4120 |       |       |    32   (0)| 00:00:01 |     1 |    16 |
                        --------------------------------------------------------------------------------------------------------------------------------------
                        
                        Predicate Information (identified by operation id):
                        ---------------------------------------------------
                        
                           2 - access("CCB"."BROKER_ID"="CBD"."BROKER_ID")
                           6 - access("RTV"."TAG_VALUE"=SYS_OP_C2C("CCB"."PARENT_BROKER_CODE"))
                          10 - access("RTL"."TAG_LIST_CODE"=U'FCBrokerCode')
                          12 - access("RTL"."TAG_LIST_ID"="RTV"."TAG_LIST_ID")
                          14 - access("RTV"."TAG_VALUE_ID"="CET"."TAG_VALUE_ID")
                        
                        30 rows selected.
                        
                        
                        SQL> SELECT
                          COUNT(*)
                        FROM
                          REM_TAG_LIST
                        WHERE
                          TAG_LIST_CODE='FCBrokerCode';
                          2    3    4    5    6
                          COUNT(*)
                        ----------
                                 1
                        
                        SQL> SELECT
                          COUNT(*)
                        FROM
                          CM_CACHE_BROKER_DETAIL;
                          2    3    4
                          COUNT(*)
                        ----------
                              6489
                        -Yasser
                        • 9. Re: SQL taking very long time to execute
                          Yasu
                          Plan shows using "hash group by" after eliminating order by clause.
                          SQL> explain plan for
                            2  SELECT
                            3    CCB.BROKER_NAME,
                            4    CCB.PARENT_BROKER_CODE,
                            MAX(CET.LAST_UPDATE_DATE) as LAST_SUBMISSION_DATE,
                            5    6    BROKER_CODE as CHILD_BROKER_CODE,
                            7    CBD.LOCATION
                          FROM
                            8    9    CM_CACHE_BROKER CCB,
                           10    REM_TAG_VALUE RTV,
                           11    CM_ENUM_TAG CET,
                           12    CM_CACHE_BROKER_detail CBD
                           13  WHERE
                           14    CCB.PARENT_BROKER_CODE = RTV.TAG_VALUE
                           15    AND EXISTS
                           16      (SELECT
                           17        1
                           18      FROM
                           19        REM_TAG_LIST RTL
                              WHERE
                           20   21        RTL.TAG_LIST_ID = RTV.TAG_LIST_ID
                           22        AND RTL.TAG_LIST_CODE = 'FCBrokerCode')
                           23    AND RTV.TAG_VALUE_ID = CET.TAG_VALUE_ID
                           24    AND CCB.BROKER_ID=CBD.BROKER_ID
                          GROUP BY
                            BROKER_NAME,
                            PARENT_BROKER_CODE,
                            BROKER_CODE,
                            location 25   26   27   28   29
                           30  /
                          
                          Explained.
                          
                          SQL> select * from table(dbms_xplan.display);
                          
                          PLAN_TABLE_OUTPUT
                          ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                          Plan hash value: 1716752470
                          
                          --------------------------------------------------------------------------------------------------------------------------------------
                          | Id  | Operation                           | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
                          --------------------------------------------------------------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT                    |                        |  7947 |  1544K|       | 10839   (1)| 00:02:11 |       |       |
                          |   1 |  HASH GROUP BY                      |                        |  7947 |  1544K|  3368K| 10839   (1)| 00:02:11 |       |       |
                          |*  2 |   HASH JOIN                         |                        |  7947 |  1544K|       | 10493   (1)| 00:02:06 |       |       |
                          |   3 |    TABLE ACCESS FULL                | CM_CACHE_BROKER_DETAIL |  6266 |   189K|       |    29   (0)| 00:00:01 |       |       |
                          |   4 |    TABLE ACCESS BY LOCAL INDEX ROWID| CM_ENUM_TAG            |    41 |  1025 |       |  4970   (1)| 00:01:00 |       |       |
                          |   5 |     NESTED LOOPS                    |                        |  4796 |   786K|       | 10463   (1)| 00:02:06 |       |       |
                          |*  6 |      HASH JOIN                      |                        |   117 | 16731 |       |   978   (1)| 00:00:12 |       |       |
                          |   7 |       TABLE ACCESS FULL             | CM_CACHE_BROKER        |  3781 |   158K|       |    15   (0)| 00:00:01 |       |       |
                          |   8 |       NESTED LOOPS                  |                        | 74085 |  7234K|       |   962   (1)| 00:00:12 |       |       |
                          |   9 |        TABLE ACCESS BY INDEX ROWID  | REM_TAG_LIST           |     1 |    43 |       |     1   (0)| 00:00:01 |       |       |
                          |* 10 |         INDEX UNIQUE SCAN           | AK1_REM_TAG_LIST_CODE  |     1 |       |       |     0   (0)| 00:00:01 |       |       |
                          |  11 |        TABLE ACCESS BY INDEX ROWID  | REM_TAG_VALUE          | 74085 |  4123K|       |   961   (1)| 00:00:12 |       |       |
                          |* 12 |         INDEX RANGE SCAN            | REM_TAG_VALUE_IDX1     | 74085 |       |       |   115   (1)| 00:00:02 |       |       |
                          |  13 |      PARTITION HASH ALL             |                        |  4120 |       |       |    32   (0)| 00:00:01 |     1 |    16 |
                          |* 14 |       INDEX RANGE SCAN              | IDX5_ENUM_TAG          |  4120 |       |       |    32   (0)| 00:00:01 |     1 |    16 |
                          --------------------------------------------------------------------------------------------------------------------------------------
                          
                          Predicate Information (identified by operation id):
                          ---------------------------------------------------
                          
                             2 - access("CCB"."BROKER_ID"="CBD"."BROKER_ID")
                             6 - access("RTV"."TAG_VALUE"=SYS_OP_C2C("CCB"."PARENT_BROKER_CODE"))
                            10 - access("RTL"."TAG_LIST_CODE"=U'FCBrokerCode')
                            12 - access("RTL"."TAG_LIST_ID"="RTV"."TAG_LIST_ID")
                            14 - access("RTV"."TAG_VALUE_ID"="CET"."TAG_VALUE_ID")
                          
                          30 rows selected.
                          But i am very sorry to say that STATISTICS are not up to date.
                          SQL> select table_name,partitioned,last_analyzed from dba_tables where table_name in ('CM_CACHE_BROKER','REM_TAG_VALUE','CM_ENUM_TAG','CM_CACHE_BROKER_DETAIL');
                          
                          TABLE_NAME                     PAR LAST_ANALYZED
                          ------------------------------ --- ------------------
                          REM_TAG_VALUE                  NO  06-JUN-09
                          CM_CACHE_BROKER_DETAIL         NO  23-APR-09
                          CM_CACHE_BROKER                NO  04-JUN-09
                          CM_ENUM_TAG                    YES 02-JUN-09
                          Can i enable dynamic sampling and check the execution plan without executing it?? As this query takes more than 30 mins by using dynamic sampling hints, and also i need to confirm before proposing stats collection requirement to my mentors...:-)

                          By the way please do not shut up....i gain lot information from your post, Thanks a lot.

                          -Yasser

                          Edited by: YasserRACDBA on Feb 2, 2010 9:14 PM
                          • 10. Re: SQL taking very long time to execute
                            sb92075
                            From which table is BROKER_CODE as CHILD_BROKER_CODE obtained?
                            • 11. Re: SQL taking very long time to execute
                              user503699
                              YasserRACDBA wrote:
                              Plan shows using "hash group by" after eliminating order by clause.
                              Well, as I said earlier, eliminating ORDER BY will only help in being able to use GATHER_PLAN_STATISTICS hint to perform Cardinality Feedback Tuning. So did you see any significant change in execution time?
                              But i am very sorry to say that STATISTICS are not up to date.
                              Can i enable dynamic sampling and check the execution plan without executing it?? As this query takes more than 30 mins by using dynamic sampling hints, and also i need to confirm before proposing stats collection requirement to my mentors...:-)
                              You need to convince your "mentors" the usefulness of stats collection ? I will be scared of your "mentors" :)
                              Using dynamic sampling is not always the right solution. Having the correct (not necessarily exactly accurate) statistics is the right solution.
                              By the way please do not shut up....i gain lot information from your post, Thanks a lot.
                              :) The "shut up" was applicable only for that post as I had edited it a couple of times and it may cause confusion.
                              • 12. Re: SQL taking very long time to execute
                                Mohamed Houri
                                Charles,

                                How could you always said

                                do not use explain plan for and select * from table(dbms_xplan.display)

                                Because of the bind variable that are all considered as varchar and impicit conversion could be made by the select * from table(dbms_xplan.display) while it might not be done in the real explain plan,

                                And here you didn't said anyword about this fact?

                                Regards

                                Mohamed Houri
                                • 13. Re: SQL taking very long time to execute
                                  Yasu
                                  Broker column exists only in CM_CACHE_BROKER_DETAIL table.

                                  -Yasser
                                  • 14. Re: SQL taking very long time to execute
                                    Yasu
                                    But why statistics has not been collected automatically even after using statistics_level to typical??

                                    -Yasser
                                    1 2 Previous Next