OraFAQ Forum: Performance Tuning » How to correct completely wrong cardinality shown in query plan

Home » RDBMS Server » Performance Tuning » How to correct completely wrong cardinality shown in query plan (10.2.0.4 on Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to correct completely wrong cardinality shown in query plan [message #602556]

Thu, 05 December 2013 12:31

orausern
Messages: 826
Registered: December 2005

Senior Member

Hi Experts,
Below is the simplified and smaller test case of a bigger problem I have with a sql. But the bigger problem can be tracked down to completely wrong cardinality estimated by the CBO. This example sql from my performance environment shows that. Can you plesae suggest how I can correct this problem:

--table structures and indexes:
--number of rows: 13,383,431 

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,
  INCL            CHAR(1 BYTE)                  NOT NULL,
  ADDDEDMANUALLY  CHAR(1 BYTE)                  NOT NULL,
  CREATED_DATE    DATE,
  CREATED_BY      VARCHAR2(80 BYTE),
  MODIFIED_DATE   DATE,
  MODIFIED_BY     VARCHAR2(80 BYTE)
);


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 UNIQUE INDEX PK_ACC_USR_RL_CROSS ON ACC_USR_RL_CROSS
(CLNT_OID, RL_OID, USR_ID);

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);

--another table:
--number of rows: 42106
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, EVT_ID, RL_OID);


ALTER TABLE WW_CE_CLNT_EVT_RL ADD (
  CONSTRAINT PK_WW_CE_CLIENT_EVENT_ROLE
  PRIMARY KEY
  (CLNT_OID, EVT_ID, RL_OID)
  USING INDEX PK_WW_CE_CLNT_EVT_RL
  ENABLE VALIDATE);

Now the query:
variable v_clnt_oid varchar2(16);
variable v_EVT_ID NUMBER;

exec :v_clnt_oid:= 'E61CBE479123F1B5'
exec :v_EVT_ID:= 61005609
SELECT count(*)
  FROM acc_usr_rl_cross a, ww_ce_clnt_evt_rl b
 WHERE     
a.clnt_oid = :v_clnt_oid
       AND 
       a.clnt_oid = b.clnt_oid
       AND a.rl_oid = b.rl_oid
       and b.evt_id=:v_EVT_ID;

  COUNT(*)
----------
     10826

1 row selected.

Elapsed: 00:00:00.04
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats  +peeked_binds'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0579jam83wtz3, child number 0
-------------------------------------
SELECT count(*)   FROM acc_usr_rl_cross a, ww_ce_clnt_evt_rl b  WHERE
a.clnt_oid = :v_clnt_oid        AND        a.clnt_oid = b.clnt_oid        AND a.rl_oid =
b.rl_oid        and b.evt_id=:v_EVT_ID

Plan hash value: 3505715767

-----------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |                      |      3 |      1 |      3 |00:00:00.04 |      96 |
|   2 |   NESTED LOOPS     |                      |      3 |      1 |  10826 |00:00:00.02 |      96 |
|*  3 |    INDEX RANGE SCAN| PK_WW_CE_CLNT_EVT_RL |      3 |      1 |      4 |00:00:00.01 |       6 |
|*  4 |    INDEX RANGE SCAN| PK_ACC_USR_RL_CROSS  |      4 |      1 |  10826 |00:00:00.01 |      90 |
-----------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :SYS_B_0 (VARCHAR2(30), CSID=873): 'E61CBE479123F1B5'

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   2 - :SYS_B_1 (NUMBER): 61004616

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

   3 - access("B"."CLNT_OID"=:V_CLNT_OID AND "B"."EVT_ID"=:V_EVT_ID)
   4 - access("A"."CLNT_OID"=:V_CLNT_OID AND "A"."RL_OID"="B"."RL_OID")

The issue is with cardinality of PK_ACC_USR_RL_CROSS -the step 4 ...the estimate was 1 row and actual was 10826 rows!! How can I correct this? This is the same problem in a bigger query but it can be tracked down to issue with this cardinality and I am not able to fix the issue with it.

Thanks,

[Updated on: Thu, 05 December 2013 12:38]