1 2 3 Previous Next 34 Replies Latest reply on Dec 16, 2013 8:09 AM by Jonathan Lewis

    A performance related question - optimizer getting wrong cardinality

    orausern

      Hi Experts,

       

      I am on Oracle 10.2.0.4 on Linux. (planned upgrade to 11gR2 in next 4 months). I have a query that is not doing well and I am trying  to find the issue with it. Following is the output of dbms_xplan that shows that there is a mismatch in E-Rows and A-rows (but I don't know what the column under 'Starts' means. Can someone please suggest some pointer , if the below plan shows up any issues:

      -------------------------------------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                              | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
      -------------------------------------------------------------------------------------------------------------------------------------------------------------------
      |   1 |  SORT AGGREGATE                        |                            |      1 |      1 |      1 |00:00:02.16 |     132K|   2148 |       |       |          |
      |   2 |   VIEW                                 |                            |      1 |      1 |   9781 |00:00:02.14 |     132K|   2148 |       |       |          |
      |   3 |    HASH UNIQUE                         |                            |      1 |      1 |   9781 |00:00:02.13 |     132K|   2148 |  1365K|  1041K|     1/0/0|
      |*  4 |     FILTER                             |                            |      1 |        |   9788 |00:00:02.09 |     132K|   2148 |       |       |          |
      |   5 |      NESTED LOOPS OUTER                |                            |      1 |      1 |   9788 |00:00:02.07 |     132K|   2148 |       |       |          |
      |   6 |       NESTED LOOPS                     |                            |      1 |      1 |   9788 |00:00:01.97 |     112K|   2145 |       |       |          |
      |   7 |        NESTED LOOPS                    |                            |      1 |      1 |   9788 |00:00:01.80 |   83233 |   2031 |       |       |          |
      PLAN_TABLE_OUTPUT
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      |   8 |         NESTED LOOPS                   |                            |      1 |      1 |   9788 |00:00:01.71 |   73443 |   2031 |       |       |          |
      |   9 |          VIEW                          |                            |      1 |      1 |   9788 |00:00:01.17 |   44033 |   1493 |       |       |          |
      |  10 |           NESTED LOOPS                 |                            |      1 |      1 |   9788 |00:00:01.16 |   44033 |   1493 |       |       |          |
      |  11 |            NESTED LOOPS                |                            |      1 |      1 |   9790 |00:00:00.62 |    4873 |    825 |       |       |          |
      |* 12 |             INDEX RANGE SCAN           | PK_CE_CLNT_EVT_RL          |      1 |      1 |      2 |00:00:00.01 |       2 |      2 |       |       |          |
      |* 13 |             TABLE ACCESS BY INDEX ROWID| ACC_USR_RL_CROSS           |      2 |      3 |   9790 |00:00:00.58 |    4871 |    823 |       |       |          |
      |* 14 |              INDEX RANGE SCAN          | PK_ACC_USR_RL_CROSS        |      2 |      2 |  18025 |00:00:00.14 |     134 |    130 |       |       |          |
      |* 15 |            TABLE ACCESS BY INDEX ROWID | ACC_USR                    |   9790 |      1 |   9788 |00:00:00.51 |   39160 |    668 |       |       |          |
      |* 16 |             INDEX UNIQUE SCAN          | ACC_USR_IDX1               |   9790 |      1 |   9788 |00:00:00.16 |   29372 |    115 |       |       |          |
      |* 17 |          TABLE ACCESS BY INDEX ROWID   | ACC_USR                    |   9788 |      1 |   9788 |00:00:00.50 |   29410 |    538 |       |       |          |
      |* 18 |           INDEX RANGE SCAN             | ACC_USR_IDX2               |   9788 |      1 |   9788 |00:00:00.43 |   19621 |    537 |       |       |          |
      |* 19 |         INDEX UNIQUE SCAN              | PK_CE_CLNT_EVT_RL          |   9788 |      1 |   9788 |00:00:00.06 |    9790 |      0 |       |       |          |
      |* 20 |        INDEX UNIQUE SCAN               | PK_ACC_AIS                 |   9788 |      1 |   9788 |00:00:00.14 |   29366 |    114 |       |       |          |
      |  21 |       TABLE ACCESS BY INDEX ROWID      | CE_USR_EVT                 |   9788 |      1 |      0 |00:00:00.08 |   19578 |      3 |       |       |          |
      |* 22 |        INDEX UNIQUE SCAN               | PK_CE_USR_EVT              |   9788 |      1 |      0 |00:00:00.04 |   19578 |      3 |       |       |          |
      
        • 1. Re: A performance related question - optimizer getting wrong cardinality
          sb92075

          are statistics current for all tables & indexes involved?

          • 2. Re: A performance related question - optimizer getting wrong cardinality
            orausern


            Hi sb92075,

             

            Thanks for the response. I re-confirmed by checking the statistics again, Yes, the statistics are accurate.

             

            Thanks,

            Nirav

            • 3. Re: A performance related question - optimizer getting wrong cardinality
              JohnWatson

              Your problem is here,

               

              |  11 |    NESTED LOOPS                |                      |      1 |      1 |   9790 |

              |* 12 |     INDEX RANGE SCAN           | PK_CE_CLNT_EVT_RL    |      1 |      1 |      2 |

              |* 13 |     TABLE ACCESS BY INDEX ROWID| ACC_USR_RL_CROSS     |      2 |      3 |   9790 |

              |* 14 |      INDEX RANGE SCAN          | PK_ACC_USR_RL_CROSS  |      2 |      2 |  18025 |

               

              At operation ID 14 the CBO expects two rows, but gets 18025. ID 13 applies a filter and cuts this down to 9790, but that is still far more than the 3 rows expected. Then this figure of 9790 cascades to the other operations, meaning that IDs 15 through 22 have to be iterated (the STARTS) that many times.

              You have chosen not to show the predicate being applied at ID 14, the DDL of the tables and indexes, or even the query: without this information it is not possible to diagnose the problem further.

              1 person found this helpful
              • 4. Re: A performance related question - optimizer getting wrong cardinality
                Nikolay Savvinov

                Hi,

                 

                1) are you sure you even have  a problem? The plan you posted shows that the query completes within 2 seconds -- why are you expecting it to run faster? What is your target performance goal and what is it based on?

                2) when posting query plans, be sure to include the predicate section -- without it, we're missing some very important information. And of course, it wouldn't hurt if you posted the query text as well.

                 

                Best regards,

                  Nikolay

                • 5. Re: A performance related question - optimizer getting wrong cardinality
                  orausern

                  Thank you JohnWatson! (and thanks to all the other experts too who reviewed) I think you found out the root cause!!! I am mentioning below, the several details like the query, the predicate section, all tables and their ddl involved in the query. The root cause is perhaps isolated to what JohnWatson said that there is one table (ACC_USR_RL_CROSS) whose cardinatly is drastically wrong. The question is how to fix that!

                  --here are the DDLs , queries and row counts of tables:

                   

                  plan:
                  Plan hash value: 2376187279
                  -------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  | Id  | Operation                              | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
                  -------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  |   1 |  SORT AGGREGATE                        |                            |      1 |      1 |      1 |00:00:02.21 |     132K|   2146 |       |       |          |
                  |   2 |   VIEW                                 |                            |      1 |      1 |   9781 |00:00:02.20 |     132K|   2146 |       |       |          |
                  |   3 |    HASH UNIQUE                         |                            |      1 |      1 |   9781 |00:00:02.19 |     132K|   2146 |  1365K|  1041K|     1/0/0|
                  |*  4 |     FILTER                             |                            |      1 |        |   9788 |00:00:02.14 |     132K|   2146 |       |       |          |
                  |   5 |      NESTED LOOPS OUTER                |                            |      1 |      1 |   9788 |00:00:02.13 |     132K|   2146 |       |       |          |
                  |   6 |       NESTED LOOPS                     |                            |      1 |      1 |   9788 |00:00:02.02 |     112K|   2143 |       |       |          |
                  |   7 |        NESTED LOOPS                    |                            |      1 |      1 |   9788 |00:00:01.85 |   83231 |   2029 |       |       |          |
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  |   8 |         NESTED LOOPS                   |                            |      1 |      1 |   9788 |00:00:01.76 |   73441 |   2029 |       |       |          |
                  |   9 |          VIEW                          |                            |      1 |      1 |   9788 |00:00:01.17 |   44032 |   1492 |       |       |          |
                  |  10 |           NESTED LOOPS                 |                            |      1 |      1 |   9788 |00:00:01.16 |   44032 |   1492 |       |       |          |
                  |  11 |            NESTED LOOPS                |                            |      1 |      1 |   9790 |00:00:00.61 |    4872 |    824 |       |       |          |
                  |* 12 |             INDEX RANGE SCAN           | PK_WW_CE_CLNT_EVT_RL       |      1 |      1 |      2 |00:00:00.01 |       2 |      2 |       |       |          |
                  |* 13 |             TABLE ACCESS BY INDEX ROWID| ACC_USR_RL_CROSS           |      2 |      3 |   9790 |00:00:00.59 |    4870 |    822 |       |       |          |
                  |* 14 |              INDEX RANGE SCAN          | PK_ACC_USR_RL_CROSS        |      2 |      2 |  18025 |00:00:00.13 |     134 |    130 |       |       |          |
                  |* 15 |            TABLE ACCESS BY INDEX ROWID | ACC_USR                    |   9790 |      1 |   9788 |00:00:00.52 |   39160 |    668 |       |       |          |
                  |* 16 |             INDEX UNIQUE SCAN          | ACC_USR_IDX10              |   9790 |      1 |   9788 |00:00:00.15 |   29372 |    115 |       |       |          |
                  |* 17 |          TABLE ACCESS BY INDEX ROWID   | ACC_USR                    |   9788 |      1 |   9788 |00:00:00.55 |   29409 |    537 |       |       |          |
                  |* 18 |           INDEX RANGE SCAN             | ACC_USR_IDX16              |   9788 |      1 |   9788 |00:00:00.48 |   19621 |    537 |       |       |          |
                  |* 19 |         INDEX UNIQUE SCAN              | PK_WW_CE_CLNT_EVT_RL       |   9788 |      1 |   9788 |00:00:00.06 |    9790 |      0 |       |       |          |
                  |* 20 |        INDEX UNIQUE SCAN               | PK_ACC_SIA                 |   9788 |      1 |   9788 |00:00:00.14 |   29366 |    114 |       |       |          |
                  |  21 |       TABLE ACCESS BY INDEX ROWID      | WW_CE_USR_EVT              |   9788 |      1 |      0 |00:00:00.08 |   19578 |      3 |       |       |          |
                  |* 22 |        INDEX UNIQUE SCAN               | PK_WW_CE_USR_EVT           |   9788 |      1 |      0 |00:00:00.04 |   19578 |      3 |       |       |          |
                  -------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  Peeked Binds (identified by position):
                  --------------------------------------
                     1 - :SYS_B_0 (VARCHAR2(30), CSID=873): 'E61CABC12CA12C7D'
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                     2 - :SYS_B_1 (NUMBER): 61011924
                     3 - :SYS_B_0 (VARCHAR2(30), CSID=873, Primary=1)
                     4 - :SYS_B_1 (NUMBER, Primary=2)
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                     4 - filter("UEVT"."STATUS" IS NULL)
                    12 - access("REVT"."CLNT_OID"=:V_CLNT_OID AND "REVT"."EVT_ID"=:V_EVT_ID)
                    13 - filter("URX"."INCLUDED"='Y')
                    14 - access("URX"."CLNT_OID"=:V_CLNT_OID AND "REVT"."RL_OID"="URX"."RL_OID")
                    15 - filter("USR_OID" IS NOT NULL)
                    16 - access("CLNT_OID"=:V_CLNT_OID AND "URX"."USR_ID"="USR_ID")
                    17 - filter("CLNT_OID"=:V_CLNT_OID)
                    18 - access("from$_subquery$_013"."USR_OID"="USR_OID")
                    19 - access("CLNT_OID"=:V_CLNT_OID AND "EVT_ID"=:V_EVT_ID AND "from$_subquery$_013"."QCSJ_C000000001300001"="RL_OID")
                    20 - access("CLNT_OID"=:V_CLNT_OID AND "USR_OID"="USR_OID")
                    22 - access("UEVT"."CLNT_OID"=:V_CLNT_OID AND "UEVT"."USR_OID"="USR"."USR_OID" AND "UEVT"."EVT_ID"=:V_EVT_ID)
                         filter("UEVT"."CLNT_OID"="REVT"."CLNT_OID")
                  query:
                  SELECT COUNT (*) THE_COUNT
                    FROM ((  SELECT DISTINCT (E.USR_OID) USR_OID,
                                             E.LST_NAME,
                                             E.FRST_NAME,
                                             E.IS_USR_ID,
                                             S.EMPL_ID,
                                             S.FILE_NMR,
                                             S.PARENT_CD,
                                             S.CHILD_CD
                               FROM WW_CE_USR_EVT_RPT E, ACC_USR S
                              WHERE     E.CLNT_OID = :v_CLNT_oid
                                    AND E.CLNT_OID = S.CLNT_OID
                                    AND E.USR_OID = S.USR_OID
                                    AND EVT_ID = :v_EVT_ID
                                    AND RL_OID IN (SELECT RL_OID
                                                       FROM WW_CE_CLNT_EVT_RL
                                                      WHERE CLNT_OID = :v_CLNT_oid AND EVT_ID = :v_EVT_ID)
                                    AND STATUS IS NULL
                           ORDER BY LAST_NAME)) A
                  /
                  Here WW_CE_USR_EVT_RPT is a synonym that refers to a view WW_CE_USR_EVT_VIEW. This view has the following definition:
                  CREATE OR REPLACE FORCE VIEW WW_CE_USR_EVT_VIEW
                  (
                     clnt_oid,
                     USR_OID,
                     ISI_USR_ID,
                     FRST_NAME,
                     LST_NAME,
                     EVT_ID,
                     RL_OID,
                     STATUS,
                     EMPLOYEE_ID,
                     FILE_NUMBER,
                     PARENT_cd,
                     CHILD_cd
                  )
                  AS
                     SELECT 
                           usr.clnt_oid,
                            usr.USR_oid,
                            usr.isi_USR_id,
                            usr.frst_name,
                            usr.lst_name,
                            revt.EVT_id,
                            revt.RL_oid,
                            uevt.status,
                            usr.EMPLOYEE_ID,
                            usr.FILE_NUMBER,
                            usr.PARENT_cd,
                            usr.CHILD_cd
                       FROM acc_usr usr
                            INNER JOIN acc_usr_RL_cross urx
                               ON urx.usr_id = usr.usr_id AND usr.clnt_oid = urx.clnt_oid
                            INNER JOIN ww_ce_clnt_EVT_RL revt
                               ON     revt.RL_oid = urx.RL_oid
                                  AND revt.clnt_oid = urx.clnt_oid
                            LEFT OUTER JOIN ww_ce_usr_EVT uevt
                               ON     uevt.clnt_oid = usr.clnt_oid
                                  AND uevt.usr_oid = usr.usr_oid
                                  AND uevt.EVT_id = revt.EVT_id
                                  AND uevt.clnt_oid = revt.clnt_oid
                      WHERE usr.usr_oid IS NOT NULL AND urx.included = 'Y';
                  This view consists of a join to 4 tables and these tables are as follows - note that I am putting first below that table ddl which is problatic:
                  acc_usr_RL_cross -since it is this table's cardinality that is going wrong:
                  --first table referrred in the view:
                  --Amount of data - number of rows: 32875574
                  CREATE TABLE ACC_USR_RL_CROSS
                  (
                    CLNT_OID      VARCHAR2(16 BYTE)             NOT NULL,
                    RL_OID        NUMBER(12)                    NOT NULL,
                    USR_ID         VARCHAR2(80 BYTE)             NOT NULL,
                    INCLUDED        CHAR(1 BYTE)                  NOT NULL,
                    ADDED_MANUALLY  CHAR(1 BYTE)                  NOT NULL,
                    CREATED_DATE    DATE,
                    CREATED_BY      VARCHAR2(80 BYTE),
                    MODIFIED_DATE   DATE,
                    MODIFIED_BY     VARCHAR2(80 BYTE)
                  );
                  
                  CREATE UNIQUE INDEX PK_ACC_USR_RL_CROSS ON ACC_USR_RL_CROSS
                  (CLNT_OID, RL_OID, USR_ID);
                  
                  CREATE INDEX ACC_USR_RL_CROSS_IDX04 ON ACC_USR_RL_CROSS
                  (CLNT_OID, USR_ID);
                  
                  CREATE INDEX ACC_USR_RL_CROSS_IDX05 ON ACC_USR_RL_CROSS
                  (USR_ID);
                  
                  CREATE INDEX ACC_USR_RL_CROSS_IDX06 ON ACC_USR_RL_cross
                  (RL_OID);
                  
                  CREATE INDEX TEST_ACC_USR_RL_CROSS_IDX01 ON ACC_USR_RL_CROSS
                  (CLNT_OID, USR_ID, RL_OID, INCLUDED);
                  ALTER TABLE ACC_USR_RL_CROSS ADD (
                    CONSTRAINT PK_ACC_USR_RL_CROSS
                    PRIMARY KEY
                    (CLNT_OID, RL_OID, USR_ID)
                    USING INDEX PK_ACC_USR_RL_CROSS
                    ENABLE VALIDATE);
                  --second table referrred in the view:
                  --Amount of data - number of rows: 25612290
                  CREATE TABLE ACC_USR
                  (
                    CLNT_OID         VARCHAR2(16 BYTE)          NOT NULL,
                    USR_OID           VARCHAR2(16 BYTE),
                    ISI_USR_ID        VARCHAR2(80 BYTE),
                    USR_ID            VARCHAR2(80 BYTE)          NOT NULL,
                    FRST_NAME         VARCHAR2(64 BYTE)          NOT NULL,
                    LST_NAME          VARCHAR2(64 BYTE)          NOT NULL,
                    EMPLOYEE_ID        VARCHAR2(27 BYTE),
                    OPERATOR_ID        VARCHAR2(30 BYTE),
                    MAIL_ADDR              VARCHAR2(256 BYTE),
                    LOCT           VARCHAR2(60 BYTE),
                    DEPT         VARCHAR2(60 BYTE),
                    JOB                VARCHAR2(45 BYTE),
                    IS_ACTIVE          CHAR(1 BYTE)               NOT NULL,
                    REGION_CD        VARCHAR2(20 BYTE),
                    PARENT_CD        VARCHAR2(20 BYTE),
                    FILE_NUMBER        VARCHAR2(20 BYTE),
                    MSGID              NUMBER(10),
                    SERVICECENTERCD  VARCHAR2(20 BYTE),
                    SEC_NUM                VARCHAR2(11 BYTE),
                    LANGCD           VARCHAR2(10 BYTE),
                    CHILD_CD         VARCHAR2(20 BYTE),
                    CREATED_DATE       DATE,
                    MODIFIED_DATE      DATE
                  );
                  
                  CREATE UNIQUE INX ACC_USR_INX10 ON ACC_USR
                  (CLNT_OID, USR_ID);
                  
                  CREATE INX ACC_USR_INX11 ON ACC_USR
                  (ISI_USR_ID);
                  
                  CREATE INDEX ACC_USR_INX12 ON ACC_USR
                  (CLNT_OID, EMPLOYEE_ID);
                  
                  CREATE INDEX ACC_USR_INX13 ON ACC_USR
                  (CLNT_OID, ISI_USR_ID);
                  
                  CREATE INDEX ACC_USR_INX14 ON ACC_USR
                  (CLNT_OID, USR_OID);
                  
                  CREATE INDEX ACC_USR_INX15 ON ACC_USR
                  (USR_ID);
                  
                  CREATE INDEX ACC_USR_INX16 ON ACC_USR
                  (USR_OID);
                  
                  CREATE INDEX ACC_USR_INX17 ON ACC_USR
                  (UPPER("ISI_USR_ID"));
                  
                  CREATE INDEX ACC_USR_INX18 ON ACC_USR
                  (CLNT_OID, UPPER("LAST_NAME"));
                  
                  CREATE INDEX ACC_USR_INX19 ON ACC_USR
                  (CLNT_OID, UPPER("LAST_NAME"), UPPER("FIRST_NAME"));
                  --third table referrred in the view:
                  --Amount of data - number of rows: 3067
                  CREATE TABLE WW_CE_CLNT_EVT_RL
                  (
                    CLNT_OID  VARCHAR2(16 BYTE)                 NOT NULL,
                    EVT_ID   NUMBER(12)                        NOT NULL,
                    RL_OID    NUMBER(12)
                  );
                  
                  CREATE UNIQUE INDEX PK_WW_CE_CLNT_EVT_RL ON WW_CE_CLNT_EVT_RL
                  (CLNT_OID, EVENT_ID, ROLE_OID);
                  
                  ALTER TABLE WW_CE_CLNT_EVT_RL ADD (
                    CONSTRAINT PK_WW_CE_CLNT_EVT_RL
                    PRIMARY KEY
                    (CLNT_OID, EVENT_ID, ROLE_OID)
                    USING INDEX PK_WW_CE_CLNT_EVT_RL
                    ENABLE VALIDATE);
                  --forth table referrred in the view:
                  --Amount of data - number of rows: 111070
                  CREATE TABLE WW_CE_USR_EVT
                  (
                    CLNT_OID  VARCHAR2(16 BYTE)                 NOT NULL,
                    USR_OID    VARCHAR2(16 BYTE)                 NOT NULL,
                    EVT_ID    NUMBER(12)                        NOT NULL,
                    STATUS      NUMBER(3)
                  );
                  
                  CREATE UNIQUE INDEX PK_WW_CE_USR_EVT ON WW_CE_USR_EVT
                  (CLNT_OID, USR_OID, EVT_ID);
                  
                  ALTER TABLE WW_CE_USR_EVT ADD (
                    CONSTRAINT PK_WW_CE_USR_EVT
                    PRIMARY KEY
                    (CLNT_OID, USR_OID, EVT_ID)
                    USING INDEX PK_WW_CE_USR_EVT
                    ENABLE VALIDATE);
                  
                  
                  • 6. Re: A performance related question - optimizer getting wrong cardinality
                    orausern

                    Also...I am getting a thought that may be the root cause is also something else - I heard from a presentation that focus on where the time is getting spent and try to fix that ....now if we go by that, following are the more time consuming steps in the plan:

                    |* 15 |            TABLE ACCESS BY INDEX ROWID | ACC_USR                    |   9790 |      1 |   9788 |00:00:00.52 |   39160 |    668 |       |       |          |
                    |* 16 |             INDEX UNIQUE SCAN          | ACC_USR_IDX10              |   9790 |      1 |   9788 |00:00:00.15 |   29372 |    115 |       |       |          |
                    |* 17 |          TABLE ACCESS BY INDEX ROWID   | ACC_USR                    |   9788 |      1 |   9788 |00:00:00.55 |   29409 |    537 |       |       |          |
                    |* 18 |           INDEX RANGE SCAN             | ACC_USR_IDX16              |   9788 |      1 |   9788 |00:00:00.48 |   19621 |    537 |       |       |          |
                    

                     

                     

                    So this means that somehow wrong indexes are getting used to get the data from ACC_USER table and we may be able to fix that somehow ---the predicates are these:

                      15 - filter("USR_OID" IS NOT NULL)

                      16 - access("CLNT_OID"=:V_CLNT_OID AND "URX"."USR_ID"="USR_ID")

                      17 - filter("CLNT_OID"=:V_CLNT_OID)

                      18 - access("from$_subquery$_013"."USR_OID"="USR_OID")

                     

                    But what do these mean and how to go about fixing these? for example I don't know what this means: access("from$_subquery$_013"."USR_OID..."  what is the from$ etc. here and how to review that?

                    Thanks,

                    • 7. Re: A performance related question - optimizer getting wrong cardinality
                      Iordan Iotzov

                      Hi,

                       

                      You can try to find the reason for the discrepancy in CBO’s cardinality estimates :


                      The first thing you can check is if CLNT_OID or RL_OID columns in ACC_USR_RL_CROSS are skewed?


                      If they are not, you can check if those two columns are dependent columns.

                       

                      HTH,
                      Iordan Iotzov

                      • 8. Re: A performance related question - optimizer getting wrong cardinality
                        jgarry

                        Yes, focusing on where the time is spent is the correct way.  Note where John mentioned that this is getting iterated - that means the cardinality problem is telling the optimizer it will take a small amount of time, so it does a lot of small amounts - but of course, if it had the right cardinality it might not loop a lot of times.

                         

                        Here's one way to work through it: Cardinality feedback | Oracle Scratchpad

                         

                        What options were used to gather the statistics?

                        • 9. Re: A performance related question - optimizer getting wrong cardinality
                          JohnWatson

                          You seem to have drifted away from the point: that the cardinality estimate at step 14 may be causing selection of a bad plan. You need to test this. I see that you are using a bind variable: run the the query with different literals a few times, not a bind, and see if the estimate is then correct and if the plan changes.

                          • 10. Re: A performance related question - optimizer getting wrong cardinality
                            David Berger

                            The best way that you create histograms on every skewed columns.

                             

                            If you execute these statements your problem will be solved probably:

                             

                            EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'...', tabname=> 'ACC_USR_RL_CROSS', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY', cascade=> TRUE, degree=>32);  

                            EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'...', tabname=> 'ACC_USR', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY', cascade=> TRUE, degree=>32);

                            EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'...', tabname=> 'WW_CE_CLNT_EVT_RL', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY', cascade=> TRUE, degree=>32);

                            EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'...', tabname=> 'WW_CE_USR_EVT', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY', cascade=> TRUE, degree=>32);

                            • 11. Re: A performance related question - optimizer getting wrong cardinality
                              orausern


                              Hi JohnWatson,

                               

                              Thanks for the suggestion. Can you also suggest how to make sure to use the literal instead ofthe bind variable - we have Java applicaiton and it uses the prepared statement. Lets say we have a sql like:

                              select * from emp where empno=:1;

                              Now how to change it so it doesn't use bind variable but uses literal- should I use CURSOR_SHARING_EXACT hint, or is there some other way - sorry for the naive question!

                              Thanks

                              • 12. Re: A performance related question - optimizer getting wrong cardinality
                                JohnWatson

                                Just do the test. There is no point in trying to work out a solution until you know what the problem is.

                                • 13. Re: A performance related question - optimizer getting wrong cardinality
                                  David Berger

                                  Your tasks:

                                  1.) Execute the above statements.

                                  2.) Check the explain plan

                                  3.) You do not need to set the cursor_sharing parameter. The best way if you have it as EXACT. (SIMILAR is deprecated in 11g)

                                  4.) execute 2-3 times the statement with different bind variables (one with high selective variable and another one with no high selective) -> Oracle needs to create bind dependent execution plans (Child-Cursors). -> Adaptive Cursor Sharing.

                                  5.) check again the explain plan

                                  • 14. Re: A performance related question - optimizer getting wrong cardinality
                                    orausern

                                    Hi JohnWatson, DavidBerger,

                                     

                                    Thank you! I tried to run the sql with different values and with and without bind variables...my observerations are as follows:

                                    1) It makes no change in the execution plan whether we use bind varialbe or not - all steps in the plan and in the predicte/filter are exactly same whether we use bind variable or literals...I verified it twice.

                                    2) The same issue of completely wrong cardinality continues in both cases - whether we use bind variables or we use literals , the cardinality has Huge difference (expected versus actual).  Here is that part of the plan for the two different runs with different values of CLNT_OID and EVT_ID:

                                    --ONE DATA SET of CLNT_OID and EVT_ID:
                                    --------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                    | Id  | Operation                               | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
                                    --------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                    |* 14 |              TABLE ACCESS BY INDEX ROWID| ACC_USR_RL_CROSS           |      2 |      9 |   2316 |00:00:01.90 |    1307 |    301 |       |       |          |
                                    |* 15 |               INDEX RANGE SCAN          | PK_ACC_USR_RL_CROSS        |      2 |     11 |   2317 |00:00:00.08 |      25
                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------
                                      14 - access("URX"."CLNT_OID"=:V_CLNT_OID AND "REVT"."RL_OID"="URX"."RL_OID")
                                      15 - access("URX"."CLIENT_OID"=:V_CLIENT_OID AND "REVT"."ROLE_OID"="URX"."ROLE_OID")
                                    --Different DATA SET of CLNT_OID and EVT_ID:
                                    --------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                    | Id  | Operation                               | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
                                    --------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                    |* 14 |              TABLE ACCESS BY INDEX ROWID| ACC_USR_RL_CROSS           |      1 |      9 |  32504 |00:00:05.44 |   21273 |   2062 |      0 |       |       |          |
                                    |* 15 |               INDEX RANGE SCAN          | PK_ACC_USR_RL_CROSS        |      1 |     11 |  45314 |00:00:00.87 |     321 |    321 |      0 |       |       |          |
                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------
                                      14 - access("URX"."CLNT_OID"=:V_CLNT_OID AND "REVT"."RL_OID"="URX"."RL_OID")
                                      15 - access("URX"."CLIENT_OID"=:V_CLIENT_OID AND "REVT"."ROLE_OID"="URX"."ROLE_OID")
                                    
                                    
                                    

                                     

                                    What can be done to get this right - will DYNAMIC_SAMPLIC hint solve this issue of wrong cardinaliy and first of all , is this the root cause of issue -again i also see that most of time is getting spent is accessing the ACC_USR table (so may be that is also the root cause?).

                                    1 2 3 Previous Next