This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Feb 13, 2013 3:21 AM by Paul Horth RSS

Child Query taking more time

KODS Newbie
Currently Being Moderated
Dear Gurus/Masters/All,

I request your valuble assistance in tuning one of my SQL.

We are using oracle 10.2.04 version and OS is HP-UX 11.23(ia64) version

In my production environment one SQL is taking more time to complete the task. According to EXPLAIN PLAN, i observed that one of it's WHERE condition execution is causing the issue.

I took the explain plan of the WHERE condition which is causing the issue. It is going for full table scan to satisfy the criteria. But a normal index exists on this column.

Main Query WHERE condition and Explain Plan.
SELECT column list ....
FROM
    SIEBEL.S_ADDR_PER T1,
    SIEBEL.S_PTY_PAY_PRFL T2,
    SIEBEL.S_INVLOC T3,
    SIEBEL.S_ORDER T4,
    SIEBEL.S_ORG_EXT T5,
    SIEBEL.S_POSTN T6,
    SIEBEL.S_PARTY T7,
    SIEBEL.S_PROJ T8,
    SIEBEL.S_CON_ADDR T9,
    SIEBEL.S_ORG_EXT T10,
    SIEBEL.S_USER T11,
    SIEBEL.S_DOC_QUOTE T12,
    SIEBEL.S_ACCNT_POSTN T13,
    SIEBEL.S_INS_CLAIM T14,
    SIEBEL.S_USER T15,
    SIEBEL.S_ORG_EXT T16,
    SIEBEL.S_ASSET T17,
    SIEBEL.S_ORDER_TNTX T18,
    SIEBEL.S_ORG_EXT_TNTX T19,
    SIEBEL.S_PERIOD T20,
    SIEBEL.S_DEPOSIT_TNT T21,
    SIEBEL.S_ADDR_PER T22,
    SIEBEL.S_PAYMENT_TERM T23,
    SIEBEL.S_ORG_EXT_X T24,
    SIEBEL.S_ORG_EXT T25,
    SIEBEL.S_INSCLM_ELMNT T26,
    SIEBEL.S_INVOICE T27
WHERE
   T25.BU_ID = T10.PAR_ROW_ID (+) AND
   T26.INSCLM_ID = T14.ROW_ID (+) AND
   T27.ELEMENT_ID = T26.ROW_ID (+) AND
   T27.LAST_UPD_BY = T15.PAR_ROW_ID (+) AND
   T4.QUOTE_ID = T12.ROW_ID (+) AND
   T3.CG_ASSSET_ID = T17.ROW_ID (+) AND
   T27.BL_ADDR_ID = T22.ROW_ID (+) AND
   T8.BU_ID = T5.PAR_ROW_ID (+) AND
   T27.PER_PAY_PRFL_ID = T2.ROW_ID (+) AND
   T27.REMIT_ORG_EXT_ID = T16.PAR_ROW_ID (+) AND
   T27.PROJ_ID = T8.ROW_ID (+) AND
   T27.BL_PERIOD_ID = T20.ROW_ID (+) AND
   T27.PAYMENT_TERM_ID = T23.ROW_ID (+) AND
   T12.BU_ID = T19.PAR_ROW_ID (+) AND
   T27.ACCNT_ID = T25.PAR_ROW_ID (+) AND
   T27.ORDER_ID = T18.ROW_ID (+) AND
   T4.SRC_INVLOC_ID = T3.ROW_ID (+) AND
   T27.ORDER_ID = T4.ROW_ID (+) AND
   T27.ACCNT_ID = T24.PAR_ROW_ID (+) AND
   T18.PR_DEPOSIT_ID = T21.ROW_ID (+) AND
   T27.BL_ADDR_ID = T9.ADDR_PER_ID (+) AND T27.ACCNT_ID = T9.ACCNT_ID (+) AND
   T27.BL_ADDR_ID = T1.ROW_ID (+) AND
   T25.PR_POSTN_ID = T13.POSITION_ID (+) AND T25.ROW_ID = T13.OU_EXT_ID (+) AND
   T13.POSITION_ID = T7.ROW_ID (+) AND
   T13.POSITION_ID = T6.PAR_ROW_ID (+) AND
   T6.PR_EMP_ID = T11.PAR_ROW_ID (+) AND
   (T27.INVC_TYPE_CD = :1)
ORDER BY
   T27.INVC_DT;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2576210427

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                              | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                       |                   |    39M|    71G|       |   624M  (1)|278:42:59 |
|   1 |  SORT ORDER BY                                         |                   |    39M|    71G|   150G|   624M  (1)|278:42:59 |
|   2 |   NESTED LOOPS OUTER                                   |                   |    39M|    71G|       |   610M  (1)|272:11:24 |
|   3 |    NESTED LOOPS OUTER                                  |                   |    39M|    70G|       |   515M  (1)|229:48:41 |
|   4 |     NESTED LOOPS OUTER                                 |                   |    39M|    69G|       |   483M  (1)|215:41:04 |
|   5 |      NESTED LOOPS OUTER                                |                   |    39M|    68G|       |   483M  (1)|215:41:04 |
|   6 |       NESTED LOOPS OUTER                               |                   |    39M|    67G|       |   483M  (1)|215:41:04 |
|   7 |        NESTED LOOPS OUTER                              |                   |    39M|    66G|       |   406M  (1)|181:17:50 |
|   8 |         NESTED LOOPS OUTER                             |                   |    39M|    65G|       |   343M  (1)|153:12:57 |
|   9 |          NESTED LOOPS OUTER                            |                   |    39M|    64G|       |   311M  (1)|139:04:56 |
|  10 |           NESTED LOOPS OUTER                           |                   |    39M|    63G|       |   185M  (1)| 82:37:56 |
|  11 |            NESTED LOOPS OUTER                          |                   |    39M|    54G|       |   108M  (1)| 48:11:29 |
|  12 |             NESTED LOOPS OUTER                         |                   |    39M|    53G|       |   108M  (1)| 48:11:29 |
|  13 |              NESTED LOOPS OUTER                        |                   |    39M|    51G|       |    76M  (1)| 34:03:51 |
|  14 |               NESTED LOOPS OUTER                       |                   |    39M|    49G|       |    76M  (1)| 34:03:51 |
|  15 |                NESTED LOOPS OUTER                      |                   |    39M|    46G|       |    76M  (1)| 34:03:51 |
|  16 |                 NESTED LOOPS OUTER                     |                   |    39M|    44G|       |    76M  (1)| 34:03:51 |
|  17 |                  NESTED LOOPS OUTER                    |                   |    39M|    40G|       |    65M  (1)| 29:25:49 |
|  18 |                   NESTED LOOPS OUTER                   |                   |    39M|    39G|       |    65M  (1)| 29:25:49 |
|  19 |                    NESTED LOOPS OUTER                  |                   |    39M|    38G|       |    65M  (1)| 29:25:49 |
|  20 |                     NESTED LOOPS OUTER                 |                   |    39M|    34G|       |    65M  (1)| 29:17:44 |
|  21 |                      NESTED LOOPS OUTER                |                   |    39M|    32G|       |    65M  (1)| 29:17:08 |
|  22 |                       NESTED LOOPS OUTER               |                   |    39M|    31G|       |    65M  (1)| 29:09:04 |
|  23 |                        NESTED LOOPS OUTER              |                   |    39M|    30G|       |  2043K  (9)| 00:54:42 |
|  24 |                         NESTED LOOPS OUTER             |                   |    39M|    30G|       |  2043K  (9)| 00:54:42 |
|  25 |                          NESTED LOOPS OUTER            |                   |    39M|    25G|       |  2015K  (7)| 00:53:57 |
|  26 |                           NESTED LOOPS OUTER           |                   |    39M|    22G|       |  2015K  (7)| 00:53:57 |
|  27 |                            NESTED LOOPS OUTER          |                   |    39M|    16G|       |  2015K  (7)| 00:53:57 |
|* 28 |                             TABLE ACCESS FULL          | S_INVOICE         |    39M|     9G|       |  2015K  (7)| 00:53:57 |
|  29 |                             TABLE ACCESS BY INDEX ROWID| S_PROJ            |     1 |   188 |       |     1   (0)| 00:00:01 |
|* 30 |                              INDEX UNIQUE SCAN         | S_PROJ_P1         |     1 |       |       |     1   (0)| 00:00:01 |
|  31 |                            TABLE ACCESS BY INDEX ROWID | S_PAYMENT_TERM    |     1 |   156 |       |     1   (0)| 00:00:01 |
|* 32 |                             INDEX UNIQUE SCAN          | S_PAYMENT_TERM_P1 |     1 |       |       |     1   (0)| 00:00:01 |
|  33 |                           TABLE ACCESS BY INDEX ROWID  | S_INSCLM_ELMNT    |     1 |    77 |       |     1   (0)| 00:00:01 |
|* 34 |                            INDEX UNIQUE SCAN           | S_INSCLM_ELMNT_P1 |     1 |       |       |     1   (0)| 00:00:01 |
|  35 |                          TABLE ACCESS BY INDEX ROWID   | S_INS_CLAIM       |     1 |   134 |       |     1   (0)| 00:00:01 |
|* 36 |                           INDEX UNIQUE SCAN            | S_INS_CLAIM_P1    |     1 |       |       |     1   (0)| 00:00:01 |
|  37 |                         TABLE ACCESS BY INDEX ROWID    | S_PERIOD          |     1 |    19 |       |     1   (0)| 00:00:01 |
|* 38 |                          INDEX UNIQUE SCAN             | S_PERIOD_P1       |     1 |       |       |     1   (0)| 00:00:01 |
|  39 |                        TABLE ACCESS BY INDEX ROWID     | S_USER            |     1 |    25 |       |     2   (0)| 00:00:01 |
|* 40 |                         INDEX UNIQUE SCAN              | S_USER_U2         |     1 |       |       |     1   (0)| 00:00:01 |
|  41 |                       TABLE ACCESS BY INDEX ROWID      | S_ORDER_TNTX      |     1 |    26 |       |     2   (0)| 00:00:01 |
|* 42 |                        INDEX UNIQUE SCAN               | S_ORDER_TNTX_P1   |     1 |       |       |     1   (0)| 00:00:01 |
|  43 |                      TABLE ACCESS BY INDEX ROWID       | S_DEPOSIT_TNT     |     1 |    45 |       |     1   (0)| 00:00:01 |
|* 44 |                       INDEX UNIQUE SCAN                | S_DEPOSIT_TNT_P1  |     1 |       |       |     1   (0)| 00:00:01 |
|  45 |                     TABLE ACCESS BY INDEX ROWID        | S_ORDER           |     1 |   101 |       |     2   (0)| 00:00:01 |
|* 46 |                      INDEX UNIQUE SCAN                 | S_ORDER_P1        |     1 |       |       |     1   (0)| 00:00:01 |
|  47 |                    TABLE ACCESS BY INDEX ROWID         | S_INVLOC          |     1 |    47 |       |     1   (0)| 00:00:01 |
|* 48 |                     INDEX UNIQUE SCAN                  | S_INVLOC_P1       |     1 |       |       |     1   (0)| 00:00:01 |
|  49 |                   TABLE ACCESS BY INDEX ROWID          | S_DOC_QUOTE       |     1 |    21 |       |     1   (0)| 00:00:01 |
|* 50 |                    INDEX UNIQUE SCAN                   | S_DOC_QUOTE_P1    |     1 |       |       |     1   (0)| 00:00:01 |
|* 51 |                  TABLE ACCESS FULL                     | S_ORG_EXT_TNTX    |     1 |    94 |       |     0   (0)| 00:00:01 |
|  52 |                 TABLE ACCESS BY INDEX ROWID            | S_PTY_PAY_PRFL    |     1 |    74 |       |     1   (0)| 00:00:01 |
|* 53 |                  INDEX UNIQUE SCAN                     | S_PTY_PAY_PRFL_P1 |     1 |       |       |     1   (0)| 00:00:01 |
|  54 |                TABLE ACCESS BY INDEX ROWID             | S_ADDR_PER        |     1 |    84 |       |     2   (0)| 00:00:01 |
|* 55 |                 INDEX UNIQUE SCAN                      | S_ADDR_PER_P1     |     1 |       |       |     1   (0)| 00:00:01 |
|  56 |               TABLE ACCESS BY INDEX ROWID              | S_ADDR_PER        |     1 |    57 |       |     1   (0)| 00:00:01 |
|* 57 |                INDEX UNIQUE SCAN                       | S_ADDR_PER_P1     |     1 |       |       |     1   (0)| 00:00:01 |
|  58 |              TABLE ACCESS BY INDEX ROWID               | S_ORG_EXT         |     1 |    32 |       |     1   (0)| 00:00:01 |
|* 59 |               INDEX UNIQUE SCAN                        | S_ORG_EXT_U3      |     1 |       |       |     1   (0)| 00:00:01 |
|  60 |             TABLE ACCESS BY INDEX ROWID                | S_ORG_EXT         |     1 |    32 |       |     1   (0)| 00:00:01 |
|* 61 |              INDEX UNIQUE SCAN                         | S_ORG_EXT_U3      |     1 |       |       |     1   (0)| 00:00:01 |
|  62 |            TABLE ACCESS BY INDEX ROWID                 | S_ORG_EXT         |     1 |   256 |       |     2   (0)| 00:00:01 |
|* 63 |             INDEX UNIQUE SCAN                          | S_ORG_EXT_U3      |     1 |       |       |     1   (0)| 00:00:01 |
|  64 |           TABLE ACCESS BY INDEX ROWID                  | S_ACCNT_POSTN     |     1 |    32 |       |     3   (0)| 00:00:01 |
|* 65 |            INDEX RANGE SCAN                            | S_ACCNT_POSTN_U1  |     1 |       |       |     2   (0)| 00:00:01 |
|  66 |          TABLE ACCESS BY INDEX ROWID                   | S_POSTN           |     1 |    21 |       |     1   (0)| 00:00:01 |
|* 67 |           INDEX UNIQUE SCAN                            | S_POSTN_U2        |     1 |       |       |     1   (0)| 00:00:01 |
|  68 |         TABLE ACCESS BY INDEX ROWID                    | S_USER            |     1 |    25 |       |     2   (0)| 00:00:01 |
|* 69 |          INDEX UNIQUE SCAN                             | S_USER_U2         |     1 |       |       |     1   (0)| 00:00:01 |
|  70 |        TABLE ACCESS BY INDEX ROWID                     | S_ORG_EXT         |     1 |    32 |       |     2   (0)| 00:00:01 |
|* 71 |         INDEX UNIQUE SCAN                              | S_ORG_EXT_U3      |     1 |       |       |     1   (0)| 00:00:01 |
|  72 |       TABLE ACCESS BY INDEX ROWID                      | S_ASSET           |     1 |    24 |       |     2   (0)| 00:00:01 |
|* 73 |        INDEX UNIQUE SCAN                               | S_ASSET_P1        |     1 |       |       |     2   (0)| 00:00:01 |
|  74 |      TABLE ACCESS BY INDEX ROWID                       | S_CON_ADDR        |     1 |    36 |       |     3   (0)| 00:00:01 |
|* 75 |       INDEX RANGE SCAN                                 | S_CON_ADDR_U1     |     1 |       |       |     2   (0)| 00:00:01 |
|* 76 |     INDEX UNIQUE SCAN                                  | S_PARTY_P1        |     1 |    12 |       |     1   (0)| 00:00:01 |
|  77 |    TABLE ACCESS BY INDEX ROWID                         | S_ORG_EXT_X       |     1 |    37 |       |     2   (0)| 00:00:01 |
|* 78 |     INDEX RANGE SCAN                                   | S_ORG_EXT_X_U1    |     1 |       |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  28 - filter("T27"."INVC_TYPE_CD"=:1)
  30 - access("T27"."PROJ_ID"="T8"."ROW_ID"(+))
  32 - access("T27"."PAYMENT_TERM_ID"="T23"."ROW_ID"(+))
  34 - access("T27"."ELEMENT_ID"="T26"."ROW_ID"(+))
  36 - access("T26"."INSCLM_ID"="T14"."ROW_ID"(+))
  38 - access("T27"."BL_PERIOD_ID"="T20"."ROW_ID"(+))
  40 - access("T27"."LAST_UPD_BY"="T15"."PAR_ROW_ID"(+))
  42 - access("T27"."ORDER_ID"="T18"."ROW_ID"(+))
  44 - access("T18"."PR_DEPOSIT_ID"="T21"."ROW_ID"(+))
  46 - access("T27"."ORDER_ID"="T4"."ROW_ID"(+))
  48 - access("T4"."SRC_INVLOC_ID"="T3"."ROW_ID"(+))
  50 - access("T4"."QUOTE_ID"="T12"."ROW_ID"(+))
  51 - filter("T12"."BU_ID"="T19"."PAR_ROW_ID"(+))
  53 - access("T27"."PER_PAY_PRFL_ID"="T2"."ROW_ID"(+))
  55 - access("T27"."BL_ADDR_ID"="T1"."ROW_ID"(+))
  57 - access("T27"."BL_ADDR_ID"="T22"."ROW_ID"(+))
  59 - access("T8"."BU_ID"="T5"."PAR_ROW_ID"(+))
  61 - access("T27"."REMIT_ORG_EXT_ID"="T16"."PAR_ROW_ID"(+))
  63 - access("T27"."ACCNT_ID"="T25"."PAR_ROW_ID"(+))
  65 - access("T25"."ROW_ID"="T13"."OU_EXT_ID"(+) AND "T25"."PR_POSTN_ID"="T13"."POSITION_ID"(+))
  67 - access("T13"."POSITION_ID"="T6"."PAR_ROW_ID"(+))
  69 - access("T6"."PR_EMP_ID"="T11"."PAR_ROW_ID"(+))
  71 - access("T25"."BU_ID"="T10"."PAR_ROW_ID"(+))
  73 - access("T3"."CG_ASSSET_ID"="T17"."ROW_ID"(+))
  75 - access("T27"."BL_ADDR_ID"="T9"."ADDR_PER_ID"(+) AND "T27"."ACCNT_ID"="T9"."ACCNT_ID"(+))
       filter("T27"."ACCNT_ID"="T9"."ACCNT_ID"(+))
  76 - access("T13"."POSITION_ID"="T7"."ROW_ID"(+))
  78 - access("T27"."ACCNT_ID"="T24"."PAR_ROW_ID"(+))

117 rows selected.
SQL> EXPLAIN PLAN FOR
2 SELECT * FROM SIEBEL.S_INVOICE T27 WHERE T27.INVC_TYPE_CD=:1;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1810797629

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |    39M|     9G|  2016K  (8)| 00:53:59 |
|*  1 |  TABLE ACCESS FULL| S_INVOICE |    39M|     9G|  2016K  (8)| 00:53:59 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T27"."INVC_TYPE_CD"=:1)

13 rows selected.
Edited by: KODS on Feb 13, 2013 1:08 PM
  • 1. Re: Child Query taking more time
    IvanBlanarik Journeyer
    Currently Being Moderated
    Hi,
    your query is doing a full table scan of S_INVOICE table. You should check if you have an index on the INVC_TYPE_CD column.
  • 2. Re: Child Query taking more time
    asahide Expert
    Currently Being Moderated
    Hello,

    Could you use index hint?

    Regards,
  • 3. Re: Child Query taking more time
    KODS Newbie
    Currently Being Moderated
    Dear asahideO,

    Thanks for your suggestion.

    Yes I forget to use the index hit to force oracle to use existing index. As per your advise i used the index hint. But for my surprise it is taking even more. column INVC_TYPE_CD contains normal index with uniqueness is unique.

    Please find the explain plan of the child query below.
    SQL> EXPLAIN PLAN FOR
      2  SELECT /*+ index(T27 S_INVOICE_U1) */ * FROM SIEBEL.S_INVOICE T27 WHERE T27.INVC_TYPE_CD=:1;
    
    Explained.
    
    SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 1913027903
    
    --------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |              |    39M|     9G|    20M  (1)| 09:21:49 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| S_INVOICE    |    39M|     9G|    20M  (1)| 09:21:49 |
    |*  2 |   INDEX FULL SCAN           | S_INVOICE_U1 |    39M|       |   751K  (4)| 00:20:08 |
    --------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("T27"."INVC_TYPE_CD"=:1)
           filter("T27"."INVC_TYPE_CD"=:1)
    
    15 rows selected.
  • 4. Re: Child Query taking more time
    Paul Horth Expert
    Currently Being Moderated
    Given you have an index on that column, it could be any number of reasons why Oracle is not using it.

    For example, if there are only two type codes and there's about 50% of each type: then Oracle won't (and shouldn't) use the index.

    Can you give more information about the type code?
    Also post create index statement.

    Other reasons are, out-of-date statistics, implicit type conversion, incorrect init.ora parameters, etc.

    Edited by: Paul Horth on Feb 13, 2013 8:31 AM
  • 5. Re: Child Query taking more time
    BluShadow Guru Moderator
    Currently Being Moderated
    asahideO wrote:
    Hello,

    Could you use index hint?

    Regards,
    Hints should be avoided in production code.

    http://docs.oracle.com/cd/E11882_01/server.112/e17118/sql_elements006.htm

    >
    Hints were introduced in Oracle7, when users had little recourse if the optimizer generated suboptimal plans. Now Oracle provides a number of tools, including the SQL Tuning Advisor, SQL plan management, and SQL Performance Analyzer, to help you address performance problems that are not solved by the optimizer. Oracle strongly recommends that you use those tools rather than hints. The tools are far superior to hints, because when used on an ongoing basis, they provide fresh solutions as your data and database environment change.
    >

    So, aside from a handful of hints (such as APPEND etc.), things like index hints should be avoided. Better to find the cause of the performance issue and fix that.
  • 6. Re: Child Query taking more time
    IvanBlanarik Journeyer
    Currently Being Moderated
    Could you pls show us the output of these two queries?
    select * from dba_indexes
    where index_name = 'S_INVOICE_U1';
    
    select column_name from dba_ind_columns
    where index_name = 'S_INVOICE_U1'
    order by column_position;
  • 7. Re: Child Query taking more time
    Karthick_Arp Guru
    Currently Being Moderated
    what is the number of rows in t_invoice (Plan says 32M) and how many rows does the condition (T27.INVC_TYPE_CD = :1) returns?
  • 8. Re: Child Query taking more time
    KODS Newbie
    Currently Being Moderated
    Dear Paul,

    Please find the details below.
    CREATE UNIQUE INDEX "SIEBEL"."S_INVOICE_U1" 
        ON "SIEBEL"."S_INVOICE"  ("INVC_NUM", "INVC_TYPE_CD", 
        "CONFLICT_ID") 
        TABLESPACE "CRMSBL_AEM_INDEX" PCTFREE 10 INITRANS 2 MAXTRANS 
        255 
        STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 
        2147483645 PCTINCREASE 0) 
        NOLOGGING;
    
    
    select owner,num_distinct,num_nulls,last_analyzed from DBA_TAB_COL_STATISTICS where table_name='S_INVOICE' and column_name='INVC_TYPE_CD';
    owner   num_distinct     num_nulls     last_analyzed
    -------     ------------     ------------     ----------------
    SIEBEL     5          0          10-02-13
  • 9. Re: Child Query taking more time
    KODS Newbie
    Currently Being Moderated
    Dear Ivan,

    Please find the details below.
    select * from dba_indexes where index_name = 'S_INVOICE_U1';
    
    OWNER                          INDEX_NAME                     INDEX_TYPE                  TABLE_OWNER                    TABLE_NAME                     TABLE_TYPE  UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME                INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOGGING BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS   NUM_ROWS SAMPLE_SIZE LAST_ANALYZED DEGREE                                   INSTANCES                                PARTITIONED TEMPORARY GENERATED SECONDARY BUFFER_POOL USER_STATS DURATION        PCT_DIRECT_ACCESS ITYP_OWNER                     ITYP_NAME                      PARAMETERS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               GLOBAL_STATS DOMIDX_STATUS DOMIDX_OPSTATUS FUNCIDX_STATUS JOIN_INDEX IOT_REDUNDANT_PKEY_ELIM DROPPED
    ------------------------------ ------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- ---------- ----------- ------------- ------------------------------ --------- --------- -------------- ----------- ----------- ----------- ------------ ------------- -------------- --------- --------------- -------- ------- ------ ----------- ------------- ----------------------- ----------------------- ----------------- -------- -------- ----------- ------------- ---------------------------------------- ---------------------------------------- ----------- --------- --------- --------- ----------- ---------- --------------- ----------------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ ------------- --------------- -------------- ---------- ----------------------- -------
    SIEBEL                         S_INVOICE_U1                   NORMAL                      SIEBEL                         S_INVOICE                      TABLE       UNIQUE     DISABLED                  CRMSBL_AEM_INDEX                       2       255          65536                       1  2147483645                                                                           10 NO           3      902796     196739390                       1                       1         125598294 VALID    196739390   196739390 10-02-13      1                                        1                                        NO          N         N         N         DEFAULT     NO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  YES                                                       NO         NO                      NO      
    
    
    select * from dba_ind_columns where index_name = 'S_INVOICE_U1' order by column_position;
    
    INDEX_OWNER                    INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     COLUMN_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
    ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------- ------------- ----------- -------
    SIEBEL                         S_INVOICE_U1                   SIEBEL                         S_INVOICE                      INVC_NUM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       1           200          50 ASC     
    SIEBEL                         S_INVOICE_U1                   SIEBEL                         S_INVOICE                      INVC_TYPE_CD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   2           120          30 ASC     
    SIEBEL                         S_INVOICE_U1                   SIEBEL                         S_INVOICE                      CONFLICT_ID                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    3            60          15 ASC     
  • 10. Re: Child Query taking more time
    Paul Horth Expert
    Currently Being Moderated
    KODS wrote:
    Dear Paul,

    Please find the details below.
    CREATE UNIQUE INDEX "SIEBEL"."S_INVOICE_U1" 
    ON "SIEBEL"."S_INVOICE"  ("INVC_NUM", "INVC_TYPE_CD", 
    "CONFLICT_ID") 
    TABLESPACE "CRMSBL_AEM_INDEX" PCTFREE 10 INITRANS 2 MAXTRANS 
    255 
    STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 
    2147483645 PCTINCREASE 0) 
    NOLOGGING;
    
    
    select owner,num_distinct,num_nulls,last_analyzed from DBA_TAB_COL_STATISTICS where table_name='S_INVOICE' and column_name='INVC_TYPE_CD';
    owner   num_distinct     num_nulls     last_analyzed
    -------     ------------     ------------     ----------------
    SIEBEL     5          0          10-02-13
    So, your index isn't on just invc_type_cd. It's got invc_num as the leading column, which you're not specifying in your query.

    Oracle is simply deciding it's not worth using the index (not even a skip scan). Forcing it to use the index will proably make performance worse.

    Unless you can also provide some restriction on invc_num, it won't use the index.

    Putting an index on just invc_type_cd is not worth doing: there are only 5 distinct values.

    Given that it will FTS the table, it may be useful to look at parallelism.
  • 11. Re: Child Query taking more time
    KODS Newbie
    Currently Being Moderated
    Dear All,

    Thank all for assisting me. With your valuable input i tuned the query.

    I created a NO SEGMENT index on column INVC_TYPE_CD and the execution time reduced to 01 sec.
    SQL> EXPLAIN PLAN FOR
      2  SELECT * FROM SIEBEL.S_INVOICE T27 WHERE T27.INVC_TYPE_CD=:1;
    
    Explained.
    
    SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 2787099069
    
    ---------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |                     |    39M|     9G|   133   (1)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| S_INVOICE           |    39M|     9G|   133   (1)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | S_INVOICE_NOSEGMNT1 |    39M|       |     4   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("T27"."INVC_TYPE_CD"=:1)
    
    14 rows selected.
    Thanks,
    Kods
  • 12. Re: Child Query taking more time
    KODS Newbie
    Currently Being Moderated
    My child query is tuned but there is no effect on my parent query.

    Please find the parent query explain plan below.
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 2944193161
    
    --------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                              | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                                       |                     |    39M|    71G|       |   622M  (1)|277:49:03 |
    |   1 |  SORT ORDER BY                                         |                     |    39M|    71G|   150G|   622M  (1)|277:49:03 |
    |   2 |   NESTED LOOPS OUTER                                   |                     |    39M|    71G|       |   608M  (1)|271:17:28 |
    |   3 |    NESTED LOOPS OUTER                                  |                     |    39M|    70G|       |   513M  (1)|228:54:45 |
    |   4 |     NESTED LOOPS OUTER                                 |                     |    39M|    69G|       |   481M  (1)|214:47:08 |
    |   5 |      NESTED LOOPS OUTER                                |                     |    39M|    68G|       |   481M  (1)|214:47:07 |
    |   6 |       NESTED LOOPS OUTER                               |                     |    39M|    67G|       |   481M  (1)|214:47:07 |
    |   7 |        NESTED LOOPS OUTER                              |                     |    39M|    66G|       |   404M  (1)|180:23:54 |
    |   8 |         NESTED LOOPS OUTER                             |                     |    39M|    65G|       |   341M  (1)|152:19:01 |
    |   9 |          NESTED LOOPS OUTER                            |                     |    39M|    64G|       |   309M  (1)|138:11:00 |
    |  10 |           NESTED LOOPS OUTER                           |                     |    39M|    63G|       |   183M  (1)| 81:44:00 |
    |  11 |            NESTED LOOPS OUTER                          |                     |    39M|    54G|       |   106M  (1)| 47:17:33 |
    |  12 |             NESTED LOOPS OUTER                         |                     |    39M|    53G|       |   106M  (1)| 47:17:33 |
    |  13 |              NESTED LOOPS OUTER                        |                     |    39M|    51G|       |    74M  (1)| 33:09:55 |
    |  14 |               NESTED LOOPS OUTER                       |                     |    39M|    49G|       |    74M  (1)| 33:09:55 |
    |  15 |                NESTED LOOPS OUTER                      |                     |    39M|    46G|       |    74M  (1)| 33:09:55 |
    |  16 |                 NESTED LOOPS OUTER                     |                     |    39M|    44G|       |    74M  (1)| 33:09:55 |
    |  17 |                  NESTED LOOPS OUTER                    |                     |    39M|    40G|       |    63M  (1)| 28:31:52 |
    |  18 |                   NESTED LOOPS OUTER                   |                     |    39M|    39G|       |    63M  (1)| 28:31:52 |
    |  19 |                    NESTED LOOPS OUTER                  |                     |    39M|    38G|       |    63M  (1)| 28:31:52 |
    |  20 |                     NESTED LOOPS OUTER                 |                     |    39M|    34G|       |    63M  (1)| 28:23:47 |
    |  21 |                      NESTED LOOPS OUTER                |                     |    39M|    32G|       |    63M  (1)| 28:23:12 |
    |  22 |                       NESTED LOOPS OUTER               |                     |    39M|    31G|       |    63M  (1)| 28:15:07 |
    |  23 |                        NESTED LOOPS OUTER              |                     |    39M|    30G|       | 28199 (100)| 00:00:46 |
    |  24 |                         NESTED LOOPS OUTER             |                     |    39M|    30G|       | 28198 (100)| 00:00:46 |
    |  25 |                          NESTED LOOPS OUTER            |                     |    39M|    25G|       |   136   (1)| 00:00:01 |
    |  26 |                           NESTED LOOPS OUTER           |                     |    39M|    22G|       |   135   (1)| 00:00:01 |
    |  27 |                            NESTED LOOPS OUTER          |                     |    39M|    16G|       |   134   (1)| 00:00:01 |
    |  28 |                             TABLE ACCESS BY INDEX ROWID| S_INVOICE           |    39M|     9G|       |   133   (1)| 00:00:01 |
    |* 29 |                              INDEX RANGE SCAN          | S_INVOICE_NOSEGMNT1 |    39M|       |       |     4   (0)| 00:00:01 |
    |  30 |                             TABLE ACCESS BY INDEX ROWID| S_PROJ              |     1 |   188 |       |     1   (0)| 00:00:01 |
    |* 31 |                              INDEX UNIQUE SCAN         | S_PROJ_P1           |     1 |       |       |     1   (0)| 00:00:01 |
    |  32 |                            TABLE ACCESS BY INDEX ROWID | S_PAYMENT_TERM      |     1 |   156 |       |     1   (0)| 00:00:01 |
    |* 33 |                             INDEX UNIQUE SCAN          | S_PAYMENT_TERM_P1   |     1 |       |       |     1   (0)| 00:00:01 |
    |  34 |                           TABLE ACCESS BY INDEX ROWID  | S_INSCLM_ELMNT      |     1 |    77 |       |     1   (0)| 00:00:01 |
    |* 35 |                            INDEX UNIQUE SCAN           | S_INSCLM_ELMNT_P1   |     1 |       |       |     1   (0)| 00:00:01 |
    |  36 |                          TABLE ACCESS BY INDEX ROWID   | S_INS_CLAIM         |     1 |   134 |       |     1   (0)| 00:00:01 |
    |* 37 |                           INDEX UNIQUE SCAN            | S_INS_CLAIM_P1      |     1 |       |       |     1   (0)| 00:00:01 |
    |  38 |                         TABLE ACCESS BY INDEX ROWID    | S_PERIOD            |     1 |    19 |       |     1   (0)| 00:00:01 |
    |* 39 |                          INDEX UNIQUE SCAN             | S_PERIOD_P1         |     1 |       |       |     1   (0)| 00:00:01 |
    |  40 |                        TABLE ACCESS BY INDEX ROWID     | S_USER              |     1 |    25 |       |     2   (0)| 00:00:01 |
    |* 41 |                         INDEX UNIQUE SCAN              | S_USER_U2           |     1 |       |       |     1   (0)| 00:00:01 |
    |  42 |                       TABLE ACCESS BY INDEX ROWID      | S_ORDER_TNTX        |     1 |    26 |       |     2   (0)| 00:00:01 |
    |* 43 |                        INDEX UNIQUE SCAN               | S_ORDER_TNTX_P1     |     1 |       |       |     1   (0)| 00:00:01 |
    |  44 |                      TABLE ACCESS BY INDEX ROWID       | S_DEPOSIT_TNT       |     1 |    45 |       |     1   (0)| 00:00:01 |
    |* 45 |                       INDEX UNIQUE SCAN                | S_DEPOSIT_TNT_P1    |     1 |       |       |     1   (0)| 00:00:01 |
    |  46 |                     TABLE ACCESS BY INDEX ROWID        | S_ORDER             |     1 |   101 |       |     2   (0)| 00:00:01 |
    |* 47 |                      INDEX UNIQUE SCAN                 | S_ORDER_P1          |     1 |       |       |     1   (0)| 00:00:01 |
    |  48 |                    TABLE ACCESS BY INDEX ROWID         | S_INVLOC            |     1 |    47 |       |     1   (0)| 00:00:01 |
    |* 49 |                     INDEX UNIQUE SCAN                  | S_INVLOC_P1         |     1 |       |       |     1   (0)| 00:00:01 |
    |  50 |                   TABLE ACCESS BY INDEX ROWID          | S_DOC_QUOTE         |     1 |    21 |       |     1   (0)| 00:00:01 |
    |* 51 |                    INDEX UNIQUE SCAN                   | S_DOC_QUOTE_P1      |     1 |       |       |     1   (0)| 00:00:01 |
    |* 52 |                  TABLE ACCESS FULL                     | S_ORG_EXT_TNTX      |     1 |    94 |       |     0   (0)| 00:00:01 |
    |  53 |                 TABLE ACCESS BY INDEX ROWID            | S_PTY_PAY_PRFL      |     1 |    74 |       |     1   (0)| 00:00:01 |
    |* 54 |                  INDEX UNIQUE SCAN                     | S_PTY_PAY_PRFL_P1   |     1 |       |       |     1   (0)| 00:00:01 |
    |  55 |                TABLE ACCESS BY INDEX ROWID             | S_ADDR_PER          |     1 |    84 |       |     2   (0)| 00:00:01 |
    |* 56 |                 INDEX UNIQUE SCAN                      | S_ADDR_PER_P1       |     1 |       |       |     1   (0)| 00:00:01 |
    |  57 |               TABLE ACCESS BY INDEX ROWID              | S_ADDR_PER          |     1 |    57 |       |     1   (0)| 00:00:01 |
    |* 58 |                INDEX UNIQUE SCAN                       | S_ADDR_PER_P1       |     1 |       |       |     1   (0)| 00:00:01 |
    |  59 |              TABLE ACCESS BY INDEX ROWID               | S_ORG_EXT           |     1 |    32 |       |     1   (0)| 00:00:01 |
    |* 60 |               INDEX UNIQUE SCAN                        | S_ORG_EXT_U3        |     1 |       |       |     1   (0)| 00:00:01 |
    |  61 |             TABLE ACCESS BY INDEX ROWID                | S_ORG_EXT           |     1 |    32 |       |     1   (0)| 00:00:01 |
    |* 62 |              INDEX UNIQUE SCAN                         | S_ORG_EXT_U3        |     1 |       |       |     1   (0)| 00:00:01 |
    |  63 |            TABLE ACCESS BY INDEX ROWID                 | S_ORG_EXT           |     1 |   256 |       |     2   (0)| 00:00:01 |
    |* 64 |             INDEX UNIQUE SCAN                          | S_ORG_EXT_U3        |     1 |       |       |     1   (0)| 00:00:01 |
    |  65 |           TABLE ACCESS BY INDEX ROWID                  | S_ACCNT_POSTN       |     1 |    32 |       |     3   (0)| 00:00:01 |
    |* 66 |            INDEX RANGE SCAN                            | S_ACCNT_POSTN_U1    |     1 |       |       |     2   (0)| 00:00:01 |
    |  67 |          TABLE ACCESS BY INDEX ROWID                   | S_POSTN             |     1 |    21 |       |     1   (0)| 00:00:01 |
    |* 68 |           INDEX UNIQUE SCAN                            | S_POSTN_U2          |     1 |       |       |     1   (0)| 00:00:01 |
    |  69 |         TABLE ACCESS BY INDEX ROWID                    | S_USER              |     1 |    25 |       |     2   (0)| 00:00:01 |
    |* 70 |          INDEX UNIQUE SCAN                             | S_USER_U2           |     1 |       |       |     1   (0)| 00:00:01 |
    |  71 |        TABLE ACCESS BY INDEX ROWID                     | S_ORG_EXT           |     1 |    32 |       |     2   (0)| 00:00:01 |
    |* 72 |         INDEX UNIQUE SCAN                              | S_ORG_EXT_U3        |     1 |       |       |     1   (0)| 00:00:01 |
    |  73 |       TABLE ACCESS BY INDEX ROWID                      | S_ASSET             |     1 |    24 |       |     2   (0)| 00:00:01 |
    |* 74 |        INDEX UNIQUE SCAN                               | S_ASSET_P1          |     1 |       |       |     2   (0)| 00:00:01 |
    |  75 |      TABLE ACCESS BY INDEX ROWID                       | S_CON_ADDR          |     1 |    36 |       |     3   (0)| 00:00:01 |
    |* 76 |       INDEX RANGE SCAN                                 | S_CON_ADDR_U1       |     1 |       |       |     2   (0)| 00:00:01 |
    |* 77 |     INDEX UNIQUE SCAN                                  | S_PARTY_P1          |     1 |    12 |       |     1   (0)| 00:00:01 |
    |  78 |    TABLE ACCESS BY INDEX ROWID                         | S_ORG_EXT_X         |     1 |    37 |       |     2   (0)| 00:00:01 |
    |* 79 |     INDEX RANGE SCAN                                   | S_ORG_EXT_X_U1      |     1 |       |       |     2   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
      29 - access("T27"."INVC_TYPE_CD"=:1)
      31 - access("T27"."PROJ_ID"="T8"."ROW_ID"(+))
      33 - access("T27"."PAYMENT_TERM_ID"="T23"."ROW_ID"(+))
      35 - access("T27"."ELEMENT_ID"="T26"."ROW_ID"(+))
      37 - access("T26"."INSCLM_ID"="T14"."ROW_ID"(+))
      39 - access("T27"."BL_PERIOD_ID"="T20"."ROW_ID"(+))
      41 - access("T27"."LAST_UPD_BY"="T15"."PAR_ROW_ID"(+))
      43 - access("T27"."ORDER_ID"="T18"."ROW_ID"(+))
      45 - access("T18"."PR_DEPOSIT_ID"="T21"."ROW_ID"(+))
      47 - access("T27"."ORDER_ID"="T4"."ROW_ID"(+))
      49 - access("T4"."SRC_INVLOC_ID"="T3"."ROW_ID"(+))
      51 - access("T4"."QUOTE_ID"="T12"."ROW_ID"(+))
      52 - filter("T12"."BU_ID"="T19"."PAR_ROW_ID"(+))
      54 - access("T27"."PER_PAY_PRFL_ID"="T2"."ROW_ID"(+))
      56 - access("T27"."BL_ADDR_ID"="T1"."ROW_ID"(+))
      58 - access("T27"."BL_ADDR_ID"="T22"."ROW_ID"(+))
      60 - access("T8"."BU_ID"="T5"."PAR_ROW_ID"(+))
      62 - access("T27"."REMIT_ORG_EXT_ID"="T16"."PAR_ROW_ID"(+))
      64 - access("T27"."ACCNT_ID"="T25"."PAR_ROW_ID"(+))
      66 - access("T25"."ROW_ID"="T13"."OU_EXT_ID"(+) AND "T25"."PR_POSTN_ID"="T13"."POSITION_ID"(+))
      68 - access("T13"."POSITION_ID"="T6"."PAR_ROW_ID"(+))
      70 - access("T6"."PR_EMP_ID"="T11"."PAR_ROW_ID"(+))
      72 - access("T25"."BU_ID"="T10"."PAR_ROW_ID"(+))
      74 - access("T3"."CG_ASSSET_ID"="T17"."ROW_ID"(+))
      76 - access("T27"."BL_ADDR_ID"="T9"."ADDR_PER_ID"(+) AND "T27"."ACCNT_ID"="T9"."ACCNT_ID"(+))
           filter("T27"."ACCNT_ID"="T9"."ACCNT_ID"(+))
      77 - access("T13"."POSITION_ID"="T7"."ROW_ID"(+))
      79 - access("T27"."ACCNT_ID"="T24"."PAR_ROW_ID"(+))
    
    118 rows selected.
  • 13. Re: Child Query taking more time
    KODS Newbie
    Currently Being Moderated
    Dear All,

    My child query get befitted from No segment index but there is no change in execution time of parent query.

    Please find the explain plan of the parent query.
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 2944193161
    
    --------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                              | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                                       |                     |    39M|    71G|       |   622M  (1)|277:49:03 |
    |   1 |  SORT ORDER BY                                         |                     |    39M|    71G|   150G|   622M  (1)|277:49:03 |
    |   2 |   NESTED LOOPS OUTER                                   |                     |    39M|    71G|       |   608M  (1)|271:17:28 |
    |   3 |    NESTED LOOPS OUTER                                  |                     |    39M|    70G|       |   513M  (1)|228:54:45 |
    |   4 |     NESTED LOOPS OUTER                                 |                     |    39M|    69G|       |   481M  (1)|214:47:08 |
    |   5 |      NESTED LOOPS OUTER                                |                     |    39M|    68G|       |   481M  (1)|214:47:07 |
    |   6 |       NESTED LOOPS OUTER                               |                     |    39M|    67G|       |   481M  (1)|214:47:07 |
    |   7 |        NESTED LOOPS OUTER                              |                     |    39M|    66G|       |   404M  (1)|180:23:54 |
    |   8 |         NESTED LOOPS OUTER                             |                     |    39M|    65G|       |   341M  (1)|152:19:01 |
    |   9 |          NESTED LOOPS OUTER                            |                     |    39M|    64G|       |   309M  (1)|138:11:00 |
    |  10 |           NESTED LOOPS OUTER                           |                     |    39M|    63G|       |   183M  (1)| 81:44:00 |
    |  11 |            NESTED LOOPS OUTER                          |                     |    39M|    54G|       |   106M  (1)| 47:17:33 |
    |  12 |             NESTED LOOPS OUTER                         |                     |    39M|    53G|       |   106M  (1)| 47:17:33 |
    |  13 |              NESTED LOOPS OUTER                        |                     |    39M|    51G|       |    74M  (1)| 33:09:55 |
    |  14 |               NESTED LOOPS OUTER                       |                     |    39M|    49G|       |    74M  (1)| 33:09:55 |
    |  15 |                NESTED LOOPS OUTER                      |                     |    39M|    46G|       |    74M  (1)| 33:09:55 |
    |  16 |                 NESTED LOOPS OUTER                     |                     |    39M|    44G|       |    74M  (1)| 33:09:55 |
    |  17 |                  NESTED LOOPS OUTER                    |                     |    39M|    40G|       |    63M  (1)| 28:31:52 |
    |  18 |                   NESTED LOOPS OUTER                   |                     |    39M|    39G|       |    63M  (1)| 28:31:52 |
    |  19 |                    NESTED LOOPS OUTER                  |                     |    39M|    38G|       |    63M  (1)| 28:31:52 |
    |  20 |                     NESTED LOOPS OUTER                 |                     |    39M|    34G|       |    63M  (1)| 28:23:47 |
    |  21 |                      NESTED LOOPS OUTER                |                     |    39M|    32G|       |    63M  (1)| 28:23:12 |
    |  22 |                       NESTED LOOPS OUTER               |                     |    39M|    31G|       |    63M  (1)| 28:15:07 |
    |  23 |                        NESTED LOOPS OUTER              |                     |    39M|    30G|       | 28199 (100)| 00:00:46 |
    |  24 |                         NESTED LOOPS OUTER             |                     |    39M|    30G|       | 28198 (100)| 00:00:46 |
    |  25 |                          NESTED LOOPS OUTER            |                     |    39M|    25G|       |   136   (1)| 00:00:01 |
    |  26 |                           NESTED LOOPS OUTER           |                     |    39M|    22G|       |   135   (1)| 00:00:01 |
    |  27 |                            NESTED LOOPS OUTER          |                     |    39M|    16G|       |   134   (1)| 00:00:01 |
    |  28 |                             TABLE ACCESS BY INDEX ROWID| S_INVOICE           |    39M|     9G|       |   133   (1)| 00:00:01 |
    |* 29 |                              INDEX RANGE SCAN          | S_INVOICE_NOSEGMNT1 |    39M|       |       |     4   (0)| 00:00:01 |
    |  30 |                             TABLE ACCESS BY INDEX ROWID| S_PROJ              |     1 |   188 |       |     1   (0)| 00:00:01 |
    |* 31 |                              INDEX UNIQUE SCAN         | S_PROJ_P1           |     1 |       |       |     1   (0)| 00:00:01 |
    |  32 |                            TABLE ACCESS BY INDEX ROWID | S_PAYMENT_TERM      |     1 |   156 |       |     1   (0)| 00:00:01 |
    |* 33 |                             INDEX UNIQUE SCAN          | S_PAYMENT_TERM_P1   |     1 |       |       |     1   (0)| 00:00:01 |
    |  34 |                           TABLE ACCESS BY INDEX ROWID  | S_INSCLM_ELMNT      |     1 |    77 |       |     1   (0)| 00:00:01 |
    |* 35 |                            INDEX UNIQUE SCAN           | S_INSCLM_ELMNT_P1   |     1 |       |       |     1   (0)| 00:00:01 |
    |  36 |                          TABLE ACCESS BY INDEX ROWID   | S_INS_CLAIM         |     1 |   134 |       |     1   (0)| 00:00:01 |
    |* 37 |                           INDEX UNIQUE SCAN            | S_INS_CLAIM_P1      |     1 |       |       |     1   (0)| 00:00:01 |
    |  38 |                         TABLE ACCESS BY INDEX ROWID    | S_PERIOD            |     1 |    19 |       |     1   (0)| 00:00:01 |
    |* 39 |                          INDEX UNIQUE SCAN             | S_PERIOD_P1         |     1 |       |       |     1   (0)| 00:00:01 |
    |  40 |                        TABLE ACCESS BY INDEX ROWID     | S_USER              |     1 |    25 |       |     2   (0)| 00:00:01 |
    |* 41 |                         INDEX UNIQUE SCAN              | S_USER_U2           |     1 |       |       |     1   (0)| 00:00:01 |
    |  42 |                       TABLE ACCESS BY INDEX ROWID      | S_ORDER_TNTX        |     1 |    26 |       |     2   (0)| 00:00:01 |
    |* 43 |                        INDEX UNIQUE SCAN               | S_ORDER_TNTX_P1     |     1 |       |       |     1   (0)| 00:00:01 |
    |  44 |                      TABLE ACCESS BY INDEX ROWID       | S_DEPOSIT_TNT       |     1 |    45 |       |     1   (0)| 00:00:01 |
    |* 45 |                       INDEX UNIQUE SCAN                | S_DEPOSIT_TNT_P1    |     1 |       |       |     1   (0)| 00:00:01 |
    |  46 |                     TABLE ACCESS BY INDEX ROWID        | S_ORDER             |     1 |   101 |       |     2   (0)| 00:00:01 |
    |* 47 |                      INDEX UNIQUE SCAN                 | S_ORDER_P1          |     1 |       |       |     1   (0)| 00:00:01 |
    |  48 |                    TABLE ACCESS BY INDEX ROWID         | S_INVLOC            |     1 |    47 |       |     1   (0)| 00:00:01 |
    |* 49 |                     INDEX UNIQUE SCAN                  | S_INVLOC_P1         |     1 |       |       |     1   (0)| 00:00:01 |
    |  50 |                   TABLE ACCESS BY INDEX ROWID          | S_DOC_QUOTE         |     1 |    21 |       |     1   (0)| 00:00:01 |
    |* 51 |                    INDEX UNIQUE SCAN                   | S_DOC_QUOTE_P1      |     1 |       |       |     1   (0)| 00:00:01 |
    |* 52 |                  TABLE ACCESS FULL                     | S_ORG_EXT_TNTX      |     1 |    94 |       |     0   (0)| 00:00:01 |
    |  53 |                 TABLE ACCESS BY INDEX ROWID            | S_PTY_PAY_PRFL      |     1 |    74 |       |     1   (0)| 00:00:01 |
    |* 54 |                  INDEX UNIQUE SCAN                     | S_PTY_PAY_PRFL_P1   |     1 |       |       |     1   (0)| 00:00:01 |
    |  55 |                TABLE ACCESS BY INDEX ROWID             | S_ADDR_PER          |     1 |    84 |       |     2   (0)| 00:00:01 |
    |* 56 |                 INDEX UNIQUE SCAN                      | S_ADDR_PER_P1       |     1 |       |       |     1   (0)| 00:00:01 |
    |  57 |               TABLE ACCESS BY INDEX ROWID              | S_ADDR_PER          |     1 |    57 |       |     1   (0)| 00:00:01 |
    |* 58 |                INDEX UNIQUE SCAN                       | S_ADDR_PER_P1       |     1 |       |       |     1   (0)| 00:00:01 |
    |  59 |              TABLE ACCESS BY INDEX ROWID               | S_ORG_EXT           |     1 |    32 |       |     1   (0)| 00:00:01 |
    |* 60 |               INDEX UNIQUE SCAN                        | S_ORG_EXT_U3        |     1 |       |       |     1   (0)| 00:00:01 |
    |  61 |             TABLE ACCESS BY INDEX ROWID                | S_ORG_EXT           |     1 |    32 |       |     1   (0)| 00:00:01 |
    |* 62 |              INDEX UNIQUE SCAN                         | S_ORG_EXT_U3        |     1 |       |       |     1   (0)| 00:00:01 |
    |  63 |            TABLE ACCESS BY INDEX ROWID                 | S_ORG_EXT           |     1 |   256 |       |     2   (0)| 00:00:01 |
    |* 64 |             INDEX UNIQUE SCAN                          | S_ORG_EXT_U3        |     1 |       |       |     1   (0)| 00:00:01 |
    |  65 |           TABLE ACCESS BY INDEX ROWID                  | S_ACCNT_POSTN       |     1 |    32 |       |     3   (0)| 00:00:01 |
    |* 66 |            INDEX RANGE SCAN                            | S_ACCNT_POSTN_U1    |     1 |       |       |     2   (0)| 00:00:01 |
    |  67 |          TABLE ACCESS BY INDEX ROWID                   | S_POSTN             |     1 |    21 |       |     1   (0)| 00:00:01 |
    |* 68 |           INDEX UNIQUE SCAN                            | S_POSTN_U2          |     1 |       |       |     1   (0)| 00:00:01 |
    |  69 |         TABLE ACCESS BY INDEX ROWID                    | S_USER              |     1 |    25 |       |     2   (0)| 00:00:01 |
    |* 70 |          INDEX UNIQUE SCAN                             | S_USER_U2           |     1 |       |       |     1   (0)| 00:00:01 |
    |  71 |        TABLE ACCESS BY INDEX ROWID                     | S_ORG_EXT           |     1 |    32 |       |     2   (0)| 00:00:01 |
    |* 72 |         INDEX UNIQUE SCAN                              | S_ORG_EXT_U3        |     1 |       |       |     1   (0)| 00:00:01 |
    |  73 |       TABLE ACCESS BY INDEX ROWID                      | S_ASSET             |     1 |    24 |       |     2   (0)| 00:00:01 |
    |* 74 |        INDEX UNIQUE SCAN                               | S_ASSET_P1          |     1 |       |       |     2   (0)| 00:00:01 |
    |  75 |      TABLE ACCESS BY INDEX ROWID                       | S_CON_ADDR          |     1 |    36 |       |     3   (0)| 00:00:01 |
    |* 76 |       INDEX RANGE SCAN                                 | S_CON_ADDR_U1       |     1 |       |       |     2   (0)| 00:00:01 |
    |* 77 |     INDEX UNIQUE SCAN                                  | S_PARTY_P1          |     1 |    12 |       |     1   (0)| 00:00:01 |
    |  78 |    TABLE ACCESS BY INDEX ROWID                         | S_ORG_EXT_X         |     1 |    37 |       |     2   (0)| 00:00:01 |
    |* 79 |     INDEX RANGE SCAN                                   | S_ORG_EXT_X_U1      |     1 |       |       |     2   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
      29 - access("T27"."INVC_TYPE_CD"=:1)
      31 - access("T27"."PROJ_ID"="T8"."ROW_ID"(+))
      33 - access("T27"."PAYMENT_TERM_ID"="T23"."ROW_ID"(+))
      35 - access("T27"."ELEMENT_ID"="T26"."ROW_ID"(+))
      37 - access("T26"."INSCLM_ID"="T14"."ROW_ID"(+))
      39 - access("T27"."BL_PERIOD_ID"="T20"."ROW_ID"(+))
      41 - access("T27"."LAST_UPD_BY"="T15"."PAR_ROW_ID"(+))
      43 - access("T27"."ORDER_ID"="T18"."ROW_ID"(+))
      45 - access("T18"."PR_DEPOSIT_ID"="T21"."ROW_ID"(+))
      47 - access("T27"."ORDER_ID"="T4"."ROW_ID"(+))
      49 - access("T4"."SRC_INVLOC_ID"="T3"."ROW_ID"(+))
      51 - access("T4"."QUOTE_ID"="T12"."ROW_ID"(+))
      52 - filter("T12"."BU_ID"="T19"."PAR_ROW_ID"(+))
      54 - access("T27"."PER_PAY_PRFL_ID"="T2"."ROW_ID"(+))
      56 - access("T27"."BL_ADDR_ID"="T1"."ROW_ID"(+))
      58 - access("T27"."BL_ADDR_ID"="T22"."ROW_ID"(+))
      60 - access("T8"."BU_ID"="T5"."PAR_ROW_ID"(+))
      62 - access("T27"."REMIT_ORG_EXT_ID"="T16"."PAR_ROW_ID"(+))
      64 - access("T27"."ACCNT_ID"="T25"."PAR_ROW_ID"(+))
      66 - access("T25"."ROW_ID"="T13"."OU_EXT_ID"(+) AND "T25"."PR_POSTN_ID"="T13"."POSITION_ID"(+))
      68 - access("T13"."POSITION_ID"="T6"."PAR_ROW_ID"(+))
      70 - access("T6"."PR_EMP_ID"="T11"."PAR_ROW_ID"(+))
      72 - access("T25"."BU_ID"="T10"."PAR_ROW_ID"(+))
      74 - access("T3"."CG_ASSSET_ID"="T17"."ROW_ID"(+))
      76 - access("T27"."BL_ADDR_ID"="T9"."ADDR_PER_ID"(+) AND "T27"."ACCNT_ID"="T9"."ACCNT_ID"(+))
           filter("T27"."ACCNT_ID"="T9"."ACCNT_ID"(+))
      77 - access("T13"."POSITION_ID"="T7"."ROW_ID"(+))
      79 - access("T27"."ACCNT_ID"="T24"."PAR_ROW_ID"(+))
    
    118 rows selected.
  • 14. Re: Child Query taking more time
    Karthick_Arp Guru
    Currently Being Moderated
    An execution plan obtained with EXPLAIN PLAN FOR might not be accurate. The run time execution plan may variy based on the bind variable value passed.

    Here is an example

    Test case setup
    SQL> create table t
      2  as
      3  select 0 id, 'aaaaaaa' name
      4    from dual
      5  union all
      6  select 1 id, 'aaaaaaa' name
      7    from all_objects
      8  /
     
    Table created.
     
    SQL> select id, count(*) from t group by id;
     
            ID   COUNT(*)
    ---------- ----------
             1     213251
             0          1
     
    SQL> create index t_idx on t(id);
     
    Index created.
     
    SQL> exec dbms_stats.gather_table_stats(user, 'T', cascade=>true) 
     
    PL/SQL procedure successfully completed.
    
    SQL> var val number
    SQL> 
    SQL> exec :val := 1                                         
     
    PL/SQL procedure successfully completed.
     
    Execution plan with EXPLAIN PLAN FOR statement
    SQL> explain plan for select count(*) from t where id = :val;
     
    Explained.
     
    SQL> select * from table(dbms_xplan.display);
     
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------
    Plan hash value: 3482591947
     
    ---------------------------------------------------------------------------
    | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |       |     1 |     3 |   128   (3)| 00:00:02 |
    |   1 |  SORT AGGREGATE   |       |     1 |     3 |            |          |
    |*  2 |   INDEX RANGE SCAN| T_IDX |   210K|   617K|   128   (3)| 00:00:02 |
    ---------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - access("ID"=TO_NUMBER(:VAL))
     
    14 rows selected.
     
    It shows a INDEX RANGE SCAN

    Lets see in real what is happening
    SQL> select count(*) from t where id = :val;
     
      COUNT(*)
    ----------
        213251
     
    SQL> select * from table(dbms_xplan.display_cursor);
     
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------
    SQL_ID  5t576vnzcyyz6, child number 0
    -------------------------------------
    select count(*) from t where id = :val
     
    Plan hash value: 1799443504
     
    -------------------------------------------------------------------------------
    | Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |       |       |       |   124 (100)|          |
    |   1 |  SORT AGGREGATE       |       |     1 |     3 |            |          |
    |*  2 |   INDEX FAST FULL SCAN| T_IDX |   210K|   617K|   124   (8)| 00:00:02 |
    -------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - filter("ID"=:VAL)
     
     
    19 rows selected.
     
    SQL> 
    Its going for INDEX FAST FULL SCAN. The bind variable value is critical in building the execution plan.
    So to get the actual execution plan use DBMS_XPLAN.DISPLAY_CURSOR after running the actual query.
1 2 Previous Next

Legend

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