Use Extended Statistics to Optimize Oracle E-Business Suite Queries

Version 3

    Use Extended Statistics to Optimize Oracle E-Business Suite Queries

          
    By Wissem EL KHLIFI, Oracle ACE

    The Oracle Tuning approach of queries in Oracle E-Business suite environment is different than any tuning effort in any other environment. As you may know, Oracle E-Business Suite standard queries are there and we cannot touch them as they are packaged and part of the EBS product.  So rewriting the queries in those standard programs is not supported. So as a DBA, we need to find other query optimization techniques like creating indexes, define histograms, gather statistics, use Dynamic Sampling, SQL profiles, SQL plan management (SPM), SQL patches, modify instance parameters (if the issue identified is global) , etc.  This article describes how to use extended statistics to fix query performance issue in an Oracle E-Business Suite environment.

     

     

     

    In our Oracle E-Business suite R12 environment, some users were complaining of the slowness of one of the Oracle EBS Standard Programs called APCX_INV_CRATE_DEF_TXNS which is part of the General Ledger’s of the Oracle financial product pack.

     

    After checking Oracle Enterprise Manager Cloud Control and our internal monitoring tool, I noticed the module had been running for almost 3 hours.

     

     

    UseExtdStats_Wissen_1.jpg

    Oracle Enterprise Manager Cloud Control showed the following SQL ID: 4jm5nz5f3dwbg with following cost in optimizer 3776.

     

          dba_hist_sqlstat shows the following for the SQL_ID 4jm5nz5f3dwbg;

     

     

     

    UseExtdStats_Wissen_2.jpg

     

     

     

    AWR SQL report shows the query was executed 431 times with overall elapsed time of 112 minutes.
    Remember you can run AWR SQL report calling awr_sql_report_html or AWR_SQL_REPORT_TEXT functions of the DBMS_WORKLOAD_REPOSITORY package given you are licensed to use the Oracle Database Diagnostic Pack.

     

    select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_sql_report_html(2274198806,1,42863,42920,'4jm5nz5f3dwbg '));

     

     

     

    UseExtdStats_Wissen_3.jpg

     


    The SQL Tuning approach

     

    Ok, now we identified the SQL. We need to check how to find a way to tune it.
    Let's start by checking the SQL Query Execution Plan;

     

    APPS:reptr12 SQL> EXPLAIN PLAN SET STATEMENT_ID = 'WISSEM' FOR
    SELECT CODE_COMBINATION_ID FROM GL_CODE_COMBINATIONS_KFV WHERE CONCATENATED_SEGMENTS = :B1 AND CHART_OF_ACCOUNTS_ID = 50425;  
    APPS:reptr12 SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic +predicate +cost'));      

    PLAN_TABLE_OUTPUT
            ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            Plan hash value: 2511713508

     

    ----------------------------------------------------------------------------
            | Id  | Operation                   | Name                    | Cost (%CPU)|
            ----------------------------------------------------------------------------
            |   0 | SELECT STATEMENT            |                         |  3975   (3)|
            |*  1 |  TABLE ACCESS BY INDEX ROWID| GL_CODE_COMBINATIONS    |  3975   (3)|
            |*  2 |   INDEX RANGE SCAN          | GL_CODE_COMBINATIONS_C2 |   371   (1)|
            ----------------------------------------------------------------------------

     

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

     

       1 - filter(DECODE("CHART_OF_ACCOUNTS_ID",101,"SEGMENT1"||'.'||"SEGMEN
                    T2"||'.'||"SEGMENT3"||'.'||"SEGMENT4"||'.'||"SEGMENT5"||'.'||"SEGMENT6"|
                    |'.'||"SEGMENT7"||'.'||"SEGMENT8",50173,"SEGMENT1"||'.'||"SEGMENT2"||'.'
                    ||"SEGMENT3"||'.'||"SEGMENT4"||'.'||"SEGMENT5"||'.'||"SEGMENT6"||'.'||"S
                    EGMENT7"||'.'||"SEGMENT8"||'.'||"SEGMENT9",50425,"SEGMENT1"||'.'||"SEGME
                    NT2"||'.'||"SEGMENT3"||'.'||"SEGMENT4"||'.'||"SEGMENT5"||'.'||"SEGMENT6"
                    ||'.'||"SEGMENT7"||'.'||"SEGMENT8"||'.'||"SEGMENT9",50446,"SEGMENT1"||'.
                    '||"SEGMENT2"||'.'||"SEGMENT3"||'.'||"SEGMENT4"||'.'||"SEGMENT5"||'.'||"
                    SEGMENT6"||'.'||"SEGMENT7"||'.'||"SEGMENT8"||'.'||"SEGMENT9",50526,"SEGM
                    ENT1"||'.'||"SEGMENT2"||'.'||"SEGMENT3"||'.'||"SEGMENT4"||'.'||"SEGMENT5

     

                  "||'.'||"SEGMENT6"||'.'||"SEGMENT7"||'.'||"SEGMENT8"||'.'||"SEGMENT9",50
                    626,"SEGMENT1"||'.'||"SEGMENT2"||'.'||"SEGMENT3"||'.'||"SEGMENT4"||'.'||
                    "SEGMENT5"||'.'||"SEGMENT6"||'.'||"SEGMENT7"||'.'||"SEGMENT8"||'.'||"SEG
                    MENT9",50627,"SEGMENT1"||'.'||"SEGMENT2"||'.'||"SEGMENT3"||'.'||"SEGMENT
                    4"||'.'||"SEGMENT5"||'.'||"SEGMENT6"||'.'||"SEGMENT7"||'.'||"SEGMENT8"||
                    '.'||"SEGMENT9",50628,"SEGMENT1"||'.'||"SEGMENT2"||'.'||"SEGMENT3"||'.'|
                    |"SEGMENT4"||'.'||"SEGMENT5"||'.'||"SEGMENT6"||'.'||"SEGMENT7"||'.'||"SE
                    GMENT8"||'.'||"SEGMENT9",50806,"SEGMENT1"||'.'||"SEGMENT2"||'.'||"SEGMEN
                    T3"||'.'||"SEGMENT4"||'.'||"SEGMENT5"||'.'||"SEGMENT6",NULL)=:B1)
         2 - access("CHART_OF_ACCOUNTS_ID"=50425)

     

    33 rows selected.

     

    Elapsed: 00:00:01.31
            APPS:reptr12 SQL>

     

    You see here the cost of execution is 3975 and the plan is using an index range scan GL_CODE_COMBINATIONS_C2.

     

    Let's get the values of the binds from V$SQL_BIND_CAPTURE dynamic view.
    SELECT * FROM V$SQL_BIND_CAPTURE WHERE SQL_ID = '4jm5nz5f3dwbg'
    If I run the query in a cloned database using the values from V$SQL_BIND_CAPTURE, we see one query execution takes about 5 minutes. 5 minutes for a single execution, that means 5 x 431 executions = 2155 minutes.
    APPS:reptr12 SQL> set timi on
    APPS:reptr12 SQL> SELECT CODE_COMBINATION_ID FROM GL_CODE_COMBINATIONS_KFV WHERE CONCATENATED_SEGMENTS = '5510.7514000.0000.0000.334.4053.0000.0000.00000' AND CHART_OF_ACCOUNTS_ID = 50425;

     

    CODE_COMBINATION_ID
          -------------------
                  3734443

     

    Elapsed: 00:05:06.42
          APPS:reptr12 SQL>
          The query is based on a view GL_CODE_COMBINATIONS_KF and the view has its function to calculate the CONCATENATED_SEGMENTS string value;
          SELECT CODE_COMBINATION_ID
        FROM GL_CODE_COMBINATIONS_KFV
       WHERE CONCATENATED_SEGMENTS = :B1
         AND CHART_OF_ACCOUNTS_ID = 50425

     

          Let's check the view definition;

     

    APPS:reptr12 SQL> set linesize 20000
    APPS:reptr12 SQL>set pagesize 20000
    APPS:reptr12 SQL>set long 200000
    APPS:reptr12 SQL>select TEXT from dba_views where VIEW_NAME='GL_CODE_COMBINATIONS_KFV';

     

    TEXT
          --------------------------------------------------------------------------------
          SELECT ROWID, CODE_COMBINATION_ID, CHART_OF_ACCOUNTS_ID,

     

    (DECODE(CHART_OF_ACCOUNTS_ID,
            101, SEGMENT1 || '.' || SEGMENT2 || '.' || SEGMENT3 || '.' || SEGMENT4 ||
          '.' || SEGMENT5 || '.' || SEGMENT6 || '.' || SEGMENT7 || '.' || SEGMENT8,
            50173, SEGMENT1 || '.' || SEGMENT2 || '.' || SEGMENT3 || '.' || SEGMENT4 |
          | '.' || SEGMENT5 || '.' || SEGMENT6 || '.' || SEGMENT7 || '.' || SEGMENT8 || '.
          ' || SEGMENT9,
            50425, SEGMENT1 || '.' || SEGMENT2 || '.' || SEGMENT3 || '.' || SEGMENT4 |
          | '.' || SEGMENT5 || '.' || SEGMENT6 || '.' || SEGMENT7 || '.' || SEGMENT8 || '.
          ' || SEGMENT9,
            50446, SEGMENT1 || '.' || SEGMENT2 || '.' || SEGMENT3 || '.' || SEGMENT4 |
          | '.' || SEGMENT5 || '.' || SEGMENT6 || '.' || SEGMENT7 || '.' || SEGMENT8 || '.
          ' || SEGMENT9,
            50526, SEGMENT1 || '.' || SEGMENT2 || '.' || SEGMENT3 || '.' || SEGMENT4 |
          | '.' || SEGMENT5 || '.' || SEGMENT6 || '.' || SEGMENT7 || '.' || SEGMENT8 || '.
          ' || SEGMENT9,
            50626, SEGMENT1 || '.' || SEGMENT2 || '.' || SEGMENT3 || '.' || SEGMENT4 |
          | '.' || SEGMENT5 || '.' || SEGMENT6 || '.' || SEGMENT7 || '.' || SEGMENT8 || '.
          ' || SEGMENT9,
            50627, SEGMENT1 || '.' || SEGMENT2 || '.' || SEGMENT3 || '.' || SEGMENT4 |
          | '.' || SEGMENT5 || '.' || SEGMENT6 || '.' || SEGMENT7 || '.' || SEGMENT8 || '.
          ' || SEGMENT9,
            50628, SEGMENT1 || '.' || SEGMENT2 || '.' || SEGMENT3 || '.' || SEGMENT4 |
          | '.' || SEGMENT5 || '.' || SEGMENT6 || '.' || SEGMENT7 || '.' || SEGMENT8 || '.
          ' || SEGMENT9,
            50806, SEGMENT1 || '.' || SEGMENT2 || '.' || SEGMENT3 || '.' || SEGMENT4 |
          | '.' || SEGMENT5 || '.' || SEGMENT6, NULL)),
       (DECODE(CHART_OF_ACCOUNTS_ID,
            101, RPAD(NVL(SEGMENT1, ' '), 2) || '.' || RPAD(NVL(SEGMENT2, ' '), 4) ||
          '.' || RPAD(NVL(SEGMENT3, ' '), 3) || '.' || RPAD(NVL(SEGMENT4, ' '), 2) || '.'
          || RPAD(NVL(SEGMENT5, ' '), 3) || '.' || RPAD(NVL(SEGMENT6, ' '), 3) || '.' || R
          PAD(NVL(SEGMENT7, ' '), 3) || '.' || RPAD(NVL(SEGMENT8, ' '), 4),
            50173, RPAD(NVL(SEGMENT1, ' '), 2) || '.' || RPAD(NVL(SEGMENT2, ' '), 10)
          || '.' || RPAD(NVL(SEGMENT3, ' '), 4) || '.' || RPAD(NVL(SEGMENT4, ' '), 3) || '
          .' || RPAD(NVL(SEGMENT5, ' '), 2) || '.' || RPAD(NVL(SEGMENT6, ' '), 3) || '.' |
          | RPAD(NVL(SEGMENT7, ' '), 3) || '.' || RPAD(NVL(SEGMENT8, ' '), 3) || '.' || RP
          AD(NVL(SEGMENT9, ' '), 4),
            50425, RPAD(NVL(SEGMENT1, ' '), 4) || '.' || RPAD(NVL(SEGMENT2, ' '), 7) |
          | '.' || RPAD(NVL(SEGMENT3, ' '), 4) || '.' || RPAD(NVL(SEGMENT4, ' '), 4) || '.
          ' || RPAD(NVL(SEGMENT5, ' '), 3) || '.' || RPAD(NVL(SEGMENT6, ' '), 4) || '.' ||
       RPAD(NVL(SEGMENT7, ' '), 4) || '.' || RPAD(NVL(SEGMENT8, ' '), 4) || '.' || RPA
          D(NVL(SEGMENT9, ' '), 5),
            50446, RPAD(NVL(SEGMENT1, ' '), 4) || '.' || RPAD(NVL(SEGMENT2, ' '), 7) |
          | '.' || RPAD(NVL(SEGMENT3, ' '), 4) || '.' || RPAD(NVL(SEGMENT4, ' '), 4) || '.
          ' || RPAD(NVL(SEGMENT5, ' '), 3) || '.' || RPAD(NVL(SEGMENT6, ' '), 4) || '.' ||
       RPAD(NVL(SEGMENT7, ' '), 4) || '.' || RPAD(NVL(SEGMENT8, ' '), 4) || '.' || RPA
          D(NVL(SEGMENT9, ' '), 5),
            50526, RPAD(NVL(SEGMENT1, ' '), 4) || '.' || RPAD(NVL(SEGMENT2, ' '), 7) |
          | '.' || RPAD(NVL(SEGMENT3, ' '), 4) || '.' || RPAD(NVL(SEGMENT4, ' '), 4) || '.
          ' || RPAD(NVL(SEGMENT5, ' '), 3) || '.' || RPAD(NVL(SEGMENT6, ' '), 4) || '.' ||
       RPAD(NVL(SEGMENT7, ' '), 4) || '.' || RPAD(NVL(SEGMENT8, ' '), 4) || '.' || RPA
          D(NVL(SEGMENT9, ' '), 5),
            50626, RPAD(NVL(SEGMENT1, ' '), 4) || '.' || RPAD(NVL(SEGMENT2, ' '), 8) |
          | '.' || RPAD(NVL(SEGMENT3, ' '), 4) || '.' || RPAD(NVL(SEGMENT4, ' '), 4) || '.
          ' || RPAD(NVL(SEGMENT5, ' '), 3) || '.' || RPAD(NVL(SEGMENT6, ' '), 4) || '.' ||
       RPAD(NVL(SEGMENT7, ' '), 4) || '.' || RPAD(NVL(SEGMENT8, ' '), 4) || '.' || RPA
          D(NVL(SEGMENT9, ' '), 5),
            50627, RPAD(NVL(SEGMENT1, ' '), 4) || '.' || RPAD(NVL(SEGMENT2, ' '), 6) |
          | '.' || RPAD(NVL(SEGMENT3, ' '), 4) || '.' || RPAD(NVL(SEGMENT4, ' '), 4) || '.
          ' || RPAD(NVL(SEGMENT5, ' '), 3) || '.' || RPAD(NVL(SEGMENT6, ' '), 4) || '.' ||
       RPAD(NVL(SEGMENT7, ' '), 4) || '.' || RPAD(NVL(SEGMENT8, ' '), 4) || '.' || RPA
          D(NVL(SEGMENT9, ' '), 5),
            50628, RPAD(NVL(SEGMENT1, ' '), 4) || '.' || RPAD(NVL(SEGMENT2, ' '), 6) |
          | '.' || RPAD(NVL(SEGMENT3, ' '), 4) || '.' || RPAD(NVL(SEGMENT4, ' '), 4) || '.
          ' || RPAD(NVL(SEGMENT5, ' '), 3) || '.' || RPAD(NVL(SEGMENT6, ' '), 4) || '.' ||
       RPAD(NVL(SEGMENT7, ' '), 4) || '.' || RPAD(NVL(SEGMENT8, ' '), 4) || '.' || RPA
          D(NVL(SEGMENT9, ' '), 5),
            50806, RPAD(NVL(SEGMENT1, ' '), 2) || '.' || RPAD(NVL(SEGMENT2, ' '), 3) |
          | '.' || RPAD(NVL(SEGMENT3, ' '), 3) || '.' || RPAD(NVL(SEGMENT4, ' '), 4) || '.
          ' || RPAD(NVL(SEGMENT5, ' '), 3) || '.' || RPAD(NVL(SEGMENT6, ' '), 3), NULL)),

     

    ACCOUNT_TYPE, REFERENCE3, JGZZ_RECON_FLAG, DETAIL_BUDGETING_ALLOWED_FLAG, DETAI
          L_POSTING_ALLOWED_FLAG, LEDGER_SEGMENT, ALTERNATE_CODE_COMBINATION_ID, LAST_UPDA
          TE_DATE, LAST_UPDATED_BY, ENABLED_FLAG, SUMMARY_FLAG, SEGMENT1, SEGMENT2, SEGMEN
          T3, SEGMENT4, SEGMENT5, SEGMENT6, SEGMENT7, SEGMENT8, SEGMENT9, SEGMENT10, SEGME
          NT11, SEGMENT12, SEGMENT13, SEGMENT14, SEGMENT15, SEGMENT16, SEGMENT17, SEGMENT1
          8, SEGMENT19, SEGMENT20, SEGMENT21, SEGMENT22, SEGMENT23, SEGMENT24, SEGMENT25,
          SEGMENT26, SEGMENT27, SEGMENT28, SEGMENT29, SEGMENT30, DESCRIPTION, TEMPLATE_ID,
       ALLOCATION_CREATE_FLAG, START_DATE_ACTIVE, END_DATE_ACTIVE, ATTRIBUTE1, ATTRIBU
          TE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATT
          RIBUTE9, ATTRIBUTE10, CONTEXT, SEGMENT_ATTRIBUTE1, SEGMENT_ATTRIBUTE2, SEGMENT_A
          TTRIBUTE3, SEGMENT_ATTRIBUTE4, SEGMENT_ATTRIBUTE5, SEGMENT_ATTRIBUTE6, SEGMENT_A
          TTRIBUTE7, SEGMENT_ATTRIBUTE8, SEGMENT_ATTRIBUTE9, SEGMENT_ATTRIBUTE10, SEGMENT_
          ATTRIBUTE11, SEGMENT_ATTRIBUTE12, SEGMENT_ATTRIBUTE13, SEGMENT_ATTRIBUTE14, SEGM
          ENT_ATTRIBUTE15, SEGMENT_ATTRIBUTE16, SEGMENT_ATTRIBUTE17, SEGMENT_ATTRIBUTE18,
          SEGMENT_ATTRIBUTE19, SEGMENT_ATTRIBUTE20, SEGMENT_ATTRIBUTE21, SEGMENT_ATTRIBUTE
          22, SEGMENT_ATTRIBUTE23, SEGMENT_ATTRIBUTE24, SEGMENT_ATTRIBUTE25, SEGMENT_ATTRI
          BUTE26, SEGMENT_ATTRIBUTE27, SEGMENT_ATTRIBUTE28, SEGMENT_ATTRIBUTE29, SEGMENT_A
          TTRIBUTE30, SEGMENT_ATTRIBUTE31, SEGMENT_ATTRIBUTE32, SEGMENT_ATTRIBUTE33, SEGME
          NT_ATTRIBUTE34, SEGMENT_ATTRIBUTE35, SEGMENT_ATTRIBUTE36, SEGMENT_ATTRIBUTE37, S
          EGMENT_ATTRIBUTE38, SEGMENT_ATTRIBUTE39, SEGMENT_ATTRIBUTE40, SEGMENT_ATTRIBUTE4
          1, SEGMENT_ATTRIBUTE42, REFERENCE1, REFERENCE2, REFERENCE4, REFERENCE5, JGZZ_REC
          ON_CONTEXT, PRESERVE_FLAG, REFRESH_FLAG, IGI_BALANCED_BUDGET_FLAG, COMPANY_COST_
          CENTER_ORG_ID, REVALUATION_ID
       FROM GL_CODE_COMBINATIONS

     

    Elapsed: 00:00:00.13
          APPS:reptr12 SQL>

     

          From the above, we see that CONCATENATED_SEGMENTS column is a built based on a Multi-Column concatenation.
          Let’s try to Create an FBI (Function Based Index) based on an extended statistics; we may try using extended statistics to optimize multi-column relationships and function-based statistics as ALL those SEGMENTS* columns are used together.

     

          Extended statistics (multi-column statistics) is a technique which allows you to collect statistics on a group of columns, here a concatenation of columns, thus giving the Oracle Optimizer more accurate statistics and information about the relationship between columns.

     

        In order to create extended statistics, we need to use the create_extended_stats function of the dbms_stats package.

     

    APPS:reptr12 SQL> SELECT dbms_stats.create_extended_stats('GL', 'GL_CODE_COMBINATIONS',
        2  '(DECODE(CHART_OF_ACCOUNTS_ID, 101, SEGMENT1 || ''.'' || SEGMENT2 || ''.'' || SEGMENT3 || ''.'' || SEGMENT4 || ''.'' || SEGMENT5 || ''.'' || SEGMENT6 || ''.'' || SEGMENT7 || ''.'' || SEGMENT8,  50173, SEGMENT1 || ''.'' || SEGMENT2 || ''.'' || SEGMENT3 || ''.'' || SEGMENT4 || ''.'' || SEGMENT5 || ''.'' || SEGMENT6 || ''.'' || SEGMENT7 || ''.'' || SEGMENT8 || ''.'' || SEGMENT9,  50425, SEGMENT1 || ''.'' || SEGMENT2 || ''.'' || SEGMENT3 || ''.'' || SEGMENT4 || ''.'' || SEGMENT5 || ''.'' || SEGMENT6 || ''.'' || SEGMENT7 || ''.'' || SEGMENT8 || ''.'' || SEGMENT9,   50446, SEGMENT1 || ''.'' || SEGMENT2 || ''.'' || SEGMENT3 || ''.'' || SEGMENT4 || ''.'' || SEGMENT5 || ''.'' || SEGMENT6 || ''.'' || SEGMENT7 || ''.'' || SEGMENT8 || ''.'' || SEGMENT9,  50526, SEGMENT1 || ''.'' || SEGMENT2 || ''.'' || SEGMENT3 || ''.'' || SEGMENT4 || ''.'' || SEGMENT5 || ''.'' || SEGMENT6 || ''.'' || SEGMENT7 || ''.'' || SEGMENT8 || ''.'' || SEGMENT9, 50626, SEGMENT1 || ''.'' || SEGMENT2 || ''.'' || SEGMENT3 || ''.'' || SEGMENT4 || ''.'' || SEGMENT5 || ''.'' || SEGMENT6 || ''.'' || SEGMENT7 || ''.'' || SEGMENT8 || ''.'' || SEGMENT9, 50627, SEGMENT1 || ''.'' || SEGMENT2 || ''.'' || SEGMENT3 || ''.'' || SEGMENT4 || ''.'' || SEGMENT5 || ''.'' || SEGMENT6 || ''.'' || SEGMENT7 || ''.'' || SEGMENT8 || ''.'' || SEGMENT9,  50628, SEGMENT1 || ''.'' || SEGMENT2 || ''.'' || SEGMENT3 || ''.'' || SEGMENT4 || ''.'' || SEGMENT5 || ''.'' || SEGMENT6 || ''.'' || SEGMENT7 || ''.'' || SEGMENT8 || ''.'' || SEGMENT9, 50806, SEGMENT1 || ''.'' || SEGMENT2 || ''.'' || SEGMENT3 || ''.'' || SEGMENT4 || ''.'' || SEGMENT5 || ''.'' || SEGMENT6, NULL))' )
        3  FROM dual;

     

    DBMS_STATS.CREATE_EXTENDED_STATS('GL','GL_CODE_COMBINATIONS','(DECODE(CHART_OF_ACCOUNTS_ID,101,SEGMENT1||''.''||SEGMENT2||''.''||SEGMENT3||''.''||SEGMENT4||''.''||SEGMENT5||''.''||SEGMENT6||''.''||SE
          -------
          SYS_STUTNBACMU3E7BR$B1DFLKJOTF

     

    Elapsed: 00:00:06.80
          APPS:reptr12 SQL>

     

    The "SYS_STUTNBACMU3E7BR$B1DFLKJOTF" is a system-generated name for the virtual column that gets created after the call of the dbms_stats.create_extended_stats function.

     

          Oracle will automatically maintain the statistics on that column group "SYS_STUTNBACMU3E7BR$B1DFLKJOTF" when statistics are gathered on the table GL_CODE_COMBINATIONS.

     

          So let's gather stats on the GL_CODE_COMBINATIONS table: This is an Oracle E-Business suite so we need to use the FND_STATS package to gather the statistics; the only supported method is FND_STATS.

     

    exec fnd_stats.gather_table_stats ('GL','GL_CODE_COMBINATIONS', percent=>10,degree=>15,cascade=>TRUE);

     

        Now let's create a concatenated index or composite index for the predicates used in the original query;APPS:reptr12 SQL> create index GL.GL_CODE_COMBINATIONS_C3 on GL.GL_CODE_COMBINATIONS ("SYS_STUTNBACMU3E7BR$B1DFLKJOTF", CHART_OF_ACCOUNTS_ID)  tablespace GLX PARALLEL 8;

     

    Index created.

     

    Elapsed: 00:01:28.98
           APPS:reptr12 SQL>
           Now, we gather statistics on the new index using FND_STATS:
           BEGIN
       fnd_stats.GATHER_INDEX_STATS('GL', 'GL_CODE_COMBINATIONS_C3',  percent=>100,degree=>16);
           END;
           Elapsed: 00:02:05.88

    Now let's check the new execution plan of the query:

     

    APPS:reptr12 SQL> EXPLAIN PLAN SET STATEMENT_ID = 'WISSEM' FOR
           SELECT CODE_COMBINATION_ID FROM GL_CODE_COMBINATIONS_KFV WHERE CONCATENATED_SEGMENTS = :B1 AND CHART_OF_ACCOUNTS_ID = 50425;

     

    Explained.

     

    Elapsed: 00:00:00.13
           APPS:reptr12 SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic +predicate +cost'));

     

    PLAN_TABLE_OUTPUT
           --------------------------------------------------------------------------------

     

     

     

    Plan hash value: 2516557537

     

    ----------------------------------------------------------------------------
           | Id  | Operation                   | Name                    | Cost (%CPU)|
           ----------------------------------------------------------------------------
           |   0 | SELECT STATEMENT            |                         |     1   (0)|
           |   1 |  TABLE ACCESS BY INDEX ROWID| GL_CODE_COMBINATIONS    |     1   (0)|
           |*  2 |   INDEX RANGE SCAN          | GL_CODE_COMBINATIONS_C3 |     1   (0)|
           ----------------------------------------------------------------------------

     

     

     

    Predicate Information (identified by operation id):

     

    PLAN_TABLE_OUTPUT
          --------------------------------------------------------------------------------
        ---------------------------------------------------

     

       2 - access(DECODE("CHART_OF_ACCOUNTS_ID",101,"SEGMENT1"||'.'||"SEGMEN
                    T2"||'.'||"SEGMENT3"||'.'||"SEGMENT4"||'.'||"SEGMENT5"||'.'||"SEGM
          ENT6"|

     

                  |'.'||"SEGMENT7"||'.'||"SEGMENT8",50173,"SEGMENT1"||'.'||"SEGMENT2
      "||'.'

     

                  ||"SEGMENT3"||'.'||"SEGMENT4"||'.'||"SEGMENT5"||'.'||"SEGMENT6"||'
          .'||"S

     

    PLAN_TABLE_OUTPUT
          --------------------------------------------------------------------------------

     

                  EGMENT7"||'.'||"SEGMENT8"||'.'||"SEGMENT9",50425,"SEGMENT1"||'.'||
      "SEGME

     

                  NT2"||'.'||"SEGMENT3"||'.'||"SEGMENT4"||'.'||"SEGMENT5"||'.'||"SEG
          MENT6"

     

                  ||'.'||"SEGMENT7"||'.'||"SEGMENT8"||'.'||"SEGMENT9",50446,"SEGMENT
          1"||'.

     

                  '||"SEGMENT2"||'.'||"SEGMENT3"||'.'||"SEGMENT4"||'.'||"SEGMENT5"||

     

    PLAN_TABLE_OUTPUT
          --------------------------------------------------------------------------------
          '.'||"

     

                  SEGMENT6"||'.'||"SEGMENT7"||'.'||"SEGMENT8"||'.'||"SEGMENT9",50526
          ,"SEGM

     

                  ENT1"||'.'||"SEGMENT2"||'.'||"SEGMENT3"||'.'||"SEGMENT4"||'.'||"SE
          GMENT5

     

                  "||'.'||"SEGMENT6"||'.'||"SEGMENT7"||'.'||"SEGMENT8"||'.'||"SEGMEN
          T9",50

     

     

     

    PLAN_TABLE_OUTPUT
          --------------------------------------------------------------------------------
                    626,"SEGMENT1"||'.'||"SEGMENT2"||'.'||"SEGMENT3"||'.'||"SEGMENT4"|
          |'.'||

     

                  "SEGMENT5"||'.'||"SEGMENT6"||'.'||"SEGMENT7"||'.'||"SEGMENT8"||'.'
    ||"SEG

     

                  MENT9",50627,"SEGMENT1"||'.'||"SEGMENT2"||'.'||"SEGMENT3"||'.'||"S
          EGMENT

     

                  4"||'.'||"SEGMENT5"||'.'||"SEGMENT6"||'.'||"SEGMENT7"||'.'||"SEGME
          NT8"||

     

    PLAN_TABLE_OUTPUT
          --------------------------------------------------------------------------------

     

                  '.'||"SEGMENT9",50628,"SEGMENT1"||'.'||"SEGMENT2"||'.'||"SEGMENT3"
          ||'.'|

     

                  |"SEGMENT4"||'.'||"SEGMENT5"||'.'||"SEGMENT6"||'.'||"SEGMENT7"||'.
          '||"SE

     

                  GMENT8"||'.'||"SEGMENT9",50806,"SEGMENT1"||'.'||"SEGMENT2"||'.'||"
          SEGMEN

     

                  T3"||'.'||"SEGMENT4"||'.'||"SEGMENT5"||'.'||"SEGMENT6",NULL)=:B1 A

     

    PLAN_TABLE_OUTPUT
          --------------------------------------------------------------------------------
          ND

     

                  "CHART_OF_ACCOUNTS_ID"=50425)

     

     

     

    33 rows selected.

     

    Elapsed: 00:00:00.87
          APPS:reptr12 SQL>

     


          Now the plan is using the new index GL_CODE_COMBINATIONS_C3 that we just created. The index is composed of the "SYS_STUTNBACMU3E7BR$B1DFLKJOTF" system-generated name for the virtual column and the CHART_OF_ACCOUNTS_ID column. You see the cost of the execution is reduced to 1 from 3975.
          Now we run the query again;

     


          APPS:reptr12 SQL> set timi on
          APPS:reptr12 SQL> SELECT CODE_COMBINATION_ID FROM GL_CODE_COMBINATIONS_KFV WHERE CONCATENATED_SEGMENTS = '5510.7514000.0000.0000.334.4053.0000.0000.00000' AND CHART_OF_ACCOUNTS_ID = 50425;

     

    CODE_COMBINATION_ID
    -------------------
                3734443

     

    Elapsed: 00:00:00.11
          APPS:reptr12 SQL>

     


          Bingo! Now the execution is reduced from 5 minutes to 11 milliseconds (from 306042 milliseconds to 11 milliseconds).
          for 431 executions, the overall execution time is reduced from 2198 minutes to 0.079 minute, huge gain right?

     


    How to delete extended statistics;

     

          In order to reproduce the issue in other cloned environment, I have to delete extended statistics to back to the original problem.

     

          So here are the steps I used to delete extended statistics that I created before.

     

    First, I query the dba_stat_extensions table and check the EXTENSION_NAME and DROPPABLE columns.

     

        Note the SYS_STUTNBACMU3E7BR$B1DFLKJOTF virtual column and the flag DROPPABLE to YES (Indicates whether the extension is droppable using DBMS_STATS.DROP_EXTENDED_STATS (YES) or not (NO)). So we can drop the virtual column.

     

    SELECT *     FROM   dba_stat_extensions WHERE  table_name = 'GL_CODE_COMBINATIONS' ;

     

    OWNER      TABLE_NAME           EXTENSION_NAME                 EXTENSION                      CREATO DROPPABLE
          ---------- -------------------- ------------------------------ ------------------------------ ------ ---
          GL         GL_CODE_COMBINATIONS SYS_STUTNBACMU3E7BR$B1DFLKJOTF (DECODE("CHART_OF_ACCOUNTS_ID" USER   YES
                                                                     ,101,"SEGMENT1"||'.'||"SEGMENT
                                                                     2"||'.'||"SEGMENT3"|

     

    GL         GL_CODE_COMBINATIONS SYS_NC00120$                   (NVL("SEGMENT1",'0'))          SYSTEM NO
          GL         GL_CODE_COMBINATIONS SYS_NC00121$                   (NVL("SEGMENT2",'0'))          SYSTEM NO
          GL         GL_CODE_COMBINATIONS SYS_NC00122$                   (NVL("SEGMENT3",'0'))          SYSTEM NO
          GL         GL_CODE_COMBINATIONS SYS_NC00123$                   (NVL("SEGMENT4",'0'))          SYSTEM NO
          GL         GL_CODE_COMBINATIONS SYS_NC00124$                   (NVL("SEGMENT7",'0'))          SYSTEM NO
          GL         GL_CODE_COMBINATIONS SYS_NC00110$                   (NVL("SEGMENT3",''))           SYSTEM NO
          GL         GL_CODE_COMBINATIONS SYS_NC00111$                   (NVL("SEGMENT1",''))           SYSTEM NO
          GL         GL_CODE_COMBINATIONS SYS_NC00112$                   (NVL("SEGMENT8",'0'))          SYSTEM NO
          GL         GL_CODE_COMBINATIONS SYS_NC00113$                   (NVL("SEGMENT6",'0'))          SYSTEM NO
          GL         GL_CODE_COMBINATIONS SYS_NC00114$                   (NVL("SEGMENT9",'0'))          SYSTEM NO
          GL         GL_CODE_COMBINATIONS SYS_NC00115$                   ('last_update_date')           SYSTEM NO

     

    12 rows selected.

     

    Elapsed: 00:00:00.34
          APPS:reptr12 SQL>

     

          To confirm that's the column statistic that we want to drop we query the DBA_stat_extensions table.

     

    APPS:reptr12 SQL> SELECT table_name, DBMS_LOB.substr(extension, 3000)  extension
                            FROM DBA_stat_extensions
                          WHERE table_name = 'GL_CODE_COMBINATIONS'
                             AND creator = 'USER';

     

    TABLE_NAME           EXTENSION
          -------------------- ------------------------------
          GL_CODE_COMBINATIONS (DECODE("CHART_OF_ACCOUNTS_ID"
                           ,101,"SEGMENT1"||'.'||"SEGMENT
                           2"||'.'||"SEGMENT3"||'.'||"SEG
                           MENT4"||'.'||"SEGMENT5"||'.'||
                           "SEGMENT6"||'.'||"SEGMENT7"||'
                           .'||"SEGMENT8",50173,"SEGMENT1
                           "||'.'||"SEGMENT2"||'.'||"SEGM
                           ENT3"||'.'||"SEGMENT4"||'.'||"
                           SEGMENT5"||'.'||"SEGMENT6"||'.
                           '||"SEGMENT7"||'.'||"SEGMENT8"
                           ||'.'||"SEGMENT9",50425,"SEGME
                           NT1"||'.'||"SEGMENT2"||'.'||"S
                           EGMENT3"||'.'||"SEGMENT4"||'.'
                           ||"SEGMENT5"||'.'||"SEGMENT6"|
                           |'.'||"SEGMENT7"||'.'||"SEGMEN
                           T8"||'.'||"SEGMENT9",50446,"SE
                           GMENT1"||'.'||"SEGMENT2"||'.'|
                           |"SEGMENT3"||'.'||"SEGMENT4"||
                           '.'||"SEGMENT5"||'.'||"SEGMENT
                           6"||'.'||"SEGMENT7"||'.'||"SEG
                            MENT8"||'.'||"SEGMENT9",50526,
                         "SEGMENT1"||'.'||"SEGMENT2"||'
                         .'||"SEGMENT3"||'.'||"SEGMENT4
                         "||'.'||"SEGMENT5"||'.'||"SEGM
                         ENT6"||'.'||"SEGMENT7"||'.'||"
                         SEGMENT8"||'.'||"SEGMENT9",506
                         26,"SEGMENT1"||'.'||"SEGMENT2"
                         ||'.'||"SEGMENT3"||'.'||"SEGME
                         NT4"||'.'||"SEGMENT5"||'.'||"S
                         EGMENT6"||'.'||"SEGMENT7"||'.'
                         ||"SEGMENT8"||'.'||"SEGMENT9",
                         50627,"SEGMENT1"||'.'||"SEGMEN
                         T2"||'.'||"SEGMENT3"||'.'||"SE
                         GMENT4"||'.'||"SEGMENT5"||'.'|
                         |"SEGMENT6"||'.'||"SEGMENT7"||
                         '.'||"SEGMENT8"||'.'||"SEGMENT
                         9",50628,"SEGMENT1"||'.'||"SEG
                         MENT2"||'.'||"SEGMENT3"||'.'||
                         "SEGMENT4"||'.'||"SEGMENT5"||'
                         .'||"SEGMENT6"||'.'||"SEGMENT7
                         "||'.'||"SEGMENT8"||'.'||"SEGM
                         ENT9",50806,"SEGMENT1"||'.'||"
                         SEGMENT2"||'.'||"SEGMENT3"||'.
                         '||"SEGMENT4"||'.'||"SEGMENT5"
                         ||'.'||"SEGMENT6",NULL))

     

    Elapsed: 00:00:00.12
    APPS:reptr12 SQL>

     

    We drop the column expression stats which are similar to extended stats column expression grouping then drop extended stats.

     

    BEGIN 
              DBMS_STATS.delete_column_stats( ownname => 'GL', tabname => 'GL_CODE_COMBINATIONS', colname => 'SYS_STUTNBACMU3E7BR$B1DFLKJOTF');
          END;

     

    We drop the composite index we created;
         
          drop index GL.GL_CODE_COMBINATIONS_C3 ;

     

    We drop extended stats; DBA_STAT_EXTENSIONS displays information about all optimizer statistics extensions in the database. We call DBMS_STATS.drop_extended_stats to drop extended stats;

     

    BEGIN 
          FOR rec IN ( SELECT table_name, DBMS_LOB.substr(extension, 3000)  extension 
                              FROM DBA_stat_extensions 
                            WHERE table_name = 'GL_CODE_COMBINATIONS' 
                               AND creator = 'USER' ) LOOP 
              DBMS_STATS.drop_extended_stats( 'GL', rec.table_name, rec.extension ); 
          END LOOP; 
          END; 
        /

     


    4- Conclusion;

     

    We have seen in this post how creating extended statistics can be very helpful to optimize multi-column relationships and function-based statistics in an Oracle E-Business suite environment.

     


    About The Author

    Wissem is a Senior DBA with over 12 years of experience specialized in Oracle HA solutions / Big Data. He works for “Schneider Electric / APC Global operations”. Wissem has also worked for several international enterprise leaders in many industries including Banking, Telecommunications, Internet and Energy. Wissem is the first Oracle ACE in Spain and he has many Oracle certifications including OCP DBA. Follow Wissem on his blog www.oracle-class.com or in twitter @orawiss