This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Feb 8, 2010 10:23 AM by sb92075 RSS

SQL taking very long time to execute

Yasu Newbie
Currently Being Moderated
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
    CharlesHooper Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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
    CharlesHooper Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    From which table is BROKER_CODE as CHILD_BROKER_CODE obtained?
  • 11. Re: SQL taking very long time to execute
    user503699 Expert
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Broker column exists only in CM_CACHE_BROKER_DETAIL table.

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

    -Yasser
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points