Home » RDBMS Server » Performance Tuning » How to correct completely wrong cardinality shown in query plan (10.2.0.4 on Linux)
How to correct completely wrong cardinality shown in query plan [message #602556] Thu, 05 December 2013 12:31 Go to next message
orausern
Messages: 735
Registered: December 2005
Location: Hyderabad
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]

Report message to a moderator

Re: How to correct completely wrong cardinality shown in query plan [message #602557 is a reply to message #602556] Thu, 05 December 2013 12:38 Go to previous messageGo to next message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
google bind variable peeking
Re: How to correct completely wrong cardinality shown in query plan [message #602562 is a reply to message #602557] Thu, 05 December 2013 13:13 Go to previous messageGo to next message
orausern
Messages: 735
Registered: December 2005
Location: Hyderabad
Senior Member
Thnaks BlackSwan and other experts. I referred the Performance tuning guide and also a Tom Kyte article on it. I understood now that Bind Variable Peeking means oracle generates one plan checking values of bind variable and reuses that plan , which is not suitable for a different value of bind variable! To fix that Tom Kyte suggested not to use bind variable and use literal instead of that. I tried that but still I see the same issue. Cardinality is completely wrong.
estimated is 1 row and actual is 10826!!

Please suggest what can be done , to fix this:

--alter system flush shared_pool;
--alter system flush buffer_cache;

SQL> alter session set cursor_sharing=exact;

Session altered.
Elapsed: 00:00:00.01
SQL> SELECT  count(*)
  FROM acc_usr_rl_cross a, ww_ce_clnt_evt_rl b
 WHERE
a.clnt_oid = 'E61CBE479123F1B5'
       AND
       a.clnt_oid = b.clnt_oid
       AND a.rl_oid = b.rl_oid
       and b.evt_id=61005609;  2    3    4    5    6    7    8

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

1 row selected.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dcc5zybj0chr2, child number 0
-------------------------------------
SELECT  count(*)   FROM acc_usr_rl_cross a, ww_ce_clnt_evt_rl b  WHERE a.clnt_oid
= 'E61CBE479123F1B5'        AND        a.clnt_oid = b.clnt_oid        AND a.rl_oid = b.rl_oid
and b.evt_id=61005609

Plan hash value: 3505715767

--------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |                      |      1 |      1 |      1 |00:00:00.10 |      92 |     82 |
|   2 |   NESTED LOOPS     |                      |      1 |      1 |  10826 |00:00:00.08 |      92 |     82 |
|*  3 |    INDEX RANGE SCAN| PK_WW_CE_CLNT_EVT_RL |      1 |      1 |      4 |00:00:00.01 |       2 |      2 |
|*  4 |    INDEX RANGE SCAN| PK_ACC_USR_RL_CROSS  |      4 |      1 |  10826 |00:00:00.07 |      90 |     80 |
--------------------------------------------------------------------------------------------------------------

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

   3 - access("B"."CLNT_OID"='E61CBE479123F1B5' AND "B"."EVT_ID"=61005609)

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   4 - access("A"."CLNT_OID"='E61CBE479123F1B5' AND "A"."RL_OID"="B"."RL_OID")


23 rows selected.

Elapsed: 00:00:00.55





Re: How to correct completely wrong cardinality shown in query plan [message #602563 is a reply to message #602562] Thu, 05 December 2013 13:17 Go to previous messageGo to next message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
are statistics current for both tables & indexes?

http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_stats.htm#ARPLS059
Re: How to correct completely wrong cardinality shown in query plan [message #602567 is a reply to message #602563] Thu, 05 December 2013 13:52 Go to previous messageGo to next message
orausern
Messages: 735
Registered: December 2005
Location: Hyderabad
Senior Member
Hi BlackSwan,
yes, the statistics are current. To rule out any issues with it, I deleted the statistics and recalculated this way:
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'MYSCHEMA',TABNAME=>'ACC_USR_RL_CROSS',CASCADE=>TRUE)

After this I re-ran the sql and yet see the exactly same issue!

Thanks
Re: How to correct completely wrong cardinality shown in query plan [message #602571 is a reply to message #602562] Thu, 05 December 2013 14:06 Go to previous messageGo to next message
John Watson
Messages: 4858
Registered: January 2010
Location: Global Village
Senior Member
Can you give it this hint /*+ index(ACC_USR_RL_CROSS,ACC_USR_RL_CROSS_IDX06) */ and
see how accurate the cardinality estimate is?
--
update: run it with the literals, please

[Updated on: Thu, 05 December 2013 14:07]

Report message to a moderator

Re: How to correct completely wrong cardinality shown in query plan [message #602572 is a reply to message #602571] Thu, 05 December 2013 14:12 Go to previous messageGo to next message
orausern
Messages: 735
Registered: December 2005
Location: Hyderabad
Senior Member
Hi John Watson,

The optimizer completely ignored the hint (not sure why!) and continued the same primary key index. same behaviour...here is the output:
SQL> SELECT   /*+ index(ACC_USR_RL_CROSS,ACC_USR_RL_CROSS_IDX06) */ count(*)
  2    FROM acc_usr_rl_cross a, PORTALMAS02_PORTAL.ww_ce_clnt_evt_rl b
  3   WHERE
  4  a.clnt_oid = 'E61CBE479123F1B5'
       AND
  5    6         a.clnt_oid = b.clnt_oid
  7         AND a.rl_oid = b.rl_oid
  8         and b.evt_id=61005609;

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

1 row selected.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6rt7qx97j3a31, child number 0
-------------------------------------
SELECT   /*+ index(ACC_USR_RL_CROSS,ACC_USR_RL_CROSS_IDX06) */ count(*)   FROM acc_usr_rl_cross a,
PORTALMAS02_PORTAL.ww_ce_clnt_evt_rl b  WHERE a.clnt_oid = 'E61CBE479123F1B5'        AND
a.clnt_oid = b.clnt_oid        AND a.rl_oid = b.rl_oid        and b.evt_id=61005609

Plan hash value: 3505715767

--------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |                      |      1 |      1 |      1 |00:00:00.12 |      92 |     82 |
|   2 |   NESTED LOOPS     |                      |      1 |      1 |  10826 |00:00:00.11 |      92 |     82 |
|*  3 |    INDEX RANGE SCAN| PK_WW_CE_CLNT_EVT_RL |      1 |      1 |      4 |00:00:00.01 |       2 |      2 |
|*  4 |    INDEX RANGE SCAN| PK_ACC_USR_RL_CROSS  |      4 |      1 |  10826 |00:00:00.08 |      90 |     80 |
--------------------------------------------------------------------------------------------------------------

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

   3 - access("B"."CLNT_OID"='E61CBE479123F1B5' AND "B"."EVT_ID"=61005609)

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   4 - access("A"."CLNT_OID"='E61CBE479123F1B5' AND "A"."RL_OID"="B"."RL_OID")


23 rows selected.

Re: How to correct completely wrong cardinality shown in query plan [message #602573 is a reply to message #602572] Thu, 05 December 2013 14:20 Go to previous messageGo to next message
John Watson
Messages: 4858
Registered: January 2010
Location: Global Village
Senior Member
OK, let's give it /*+ ordered */
I'm trying to see if there is any way of getting the cardinality estimate correct, I'm not trying to tune it.
Re: How to correct completely wrong cardinality shown in query plan [message #602574 is a reply to message #602572] Thu, 05 December 2013 14:23 Go to previous messageGo to next message
John Watson
Messages: 4858
Registered: January 2010
Location: Global Village
Senior Member
Stupid of me! My index hint should have used the alias, not the table name. Can you try with
/*+ index (a, ACC_USR_RL_CROSS_IDX06) */
Re: How to correct completely wrong cardinality shown in query plan [message #602576 is a reply to message #602574] Thu, 05 December 2013 14:32 Go to previous messageGo to next message
orausern
Messages: 735
Registered: December 2005
Location: Hyderabad
Senior Member
Hi JohnWatson

Some hope!! Getting for the first time a somewhat closer cardinality with ordered hint!!! getting cardinality of 2468 with this hint...

SQL> SELECT   /*+ ordered */  count(*)
  FROM acc_usr_rl_cross a, ww_ce_clnt_evt_rl b
  2    3   WHERE
  4  a.clnt_oid = 'E61CBE479123F1B5'
  5         AND
  6         a.clnt_oid = b.clnt_oid
  7         AND a.rl_oid = b.rl_oid
  8         and b.evt_id=61005609;

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

1 row selected.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  94w9zbvuvhz2g, child number 0
-------------------------------------
SELECT   /*+ ordered */  count(*)   FROM acc_usr_rl_cross a, ww_ce_clnt_evt_rl b  WHERE a.clnt_oid =
'E61CBE479123F1B5'        AND        a.clnt_oid = b.clnt_oid        AND a.rl_oid = b.rl_oid        and b.evt_id=61005609

Plan hash value: 3388451725

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
---------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |                       |      2 |      1 |      2 |00:00:00.13 |     184 |       |       |          |
|*  2 |   HASH JOIN        |                       |      2 |      1 |  21652 |00:00:00.11 |     184 |  1319K|  1319K|     2/0/0|
|*  3 |    INDEX RANGE SCAN| PK_ACC_USR_RL_CROSS   |      2 |   2468 |  24696 |00:00:00.02 |     180 |       |       |          |
|*  4 |    INDEX RANGE SCAN| PK_WW_CE_CLNT_EVT_RL  |      2 |      1 |      8 |00:00:00.01 |       4 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("A"."CLNT_OID"="B"."CLNT_OID" AND "A"."RL_OID"="B"."RL_OID")
   3 - access("A"."CLNT_OID"='E61CBE479123F1B5')

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   4 - access("B"."CLNT_OID"='E61CBE479123F1B5' AND "B"."EVT_ID"=61005609)


23 rows selected.

Elapsed: 00:00:00.02
SQL> ALTER SESSION SET CURSOR_SHARING=EXACT;

Session altered.

Elapsed: 00:00:00.00
SQL>
SELECT   /*+ index (a, ACC_USR_RL_CROSS_IDX06) */   count(*)
SQL>   2    FROM acc_usr_rl_cross a, ww_ce_clnt_evt_rl b
  3   WHERE
  4  a.clnt_oid = 'E61CBE479123F1B5'
  5         AND
  6         a.clnt_oid = b.clnt_oid
  7         AND a.rl_oid = b.rl_oid
  8         and b.evt_id=61005609;

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

1 row selected.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  66m8ddupg5711, child number 0
-------------------------------------
SELECT   /*+ index (a, ACC_USR_RL_CROSS_IDX06) */   count(*)   FROM acc_usr_rl_cross a,
ww_ce_clnt_evt_rl b  WHERE a.clnt_oid = 'E61CBE479123F1B5'        AND        a.clnt_oid =
b.clnt_oid        AND a.rl_oid = b.rl_oid        and b.evt_id=61005609

Plan hash value: 1126613625

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE              |                        |      1 |      1 |      1 |00:00:00.21 |     958 |    833 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| ACC_USR_RL_CROSS       |      1 |      1 |  10826 |00:00:00.18 |     958 |    833 |
|   3 |    NESTED LOOPS              |                        |      1 |      1 |  10831 |00:00:00.03 |      38 |     28 |
|*  4 |     INDEX RANGE SCAN         | PK_WW_CE_CLNT_EVT_RL   |      1 |      1 |      4 |00:00:00.01 |       2 |      0 |
|*  5 |     INDEX RANGE SCAN         | ACC_USR_RL_CROSS_IDX06 |      4 |    240 |  10826 |00:00:00.02 |      36 |     28 |
--------------------------------------------------------------------------------------------------------------------------

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


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   2 - filter("A"."CLNT_OID"='E61CBE479123F1B5')
   4 - access("B"."CLNT_OID"='E61CBE479123F1B5' AND "B"."EVT_ID"=61005609)
   5 - access("A"."RL_OID"="B"."RL_OID")


25 rows selected.



--update: changed a primary key name in the plan ...I am not showing the actual object names in the db.

[Updated on: Thu, 05 December 2013 14:35]

Report message to a moderator

Re: How to correct completely wrong cardinality shown in query plan [message #602577 is a reply to message #602576] Thu, 05 December 2013 14:41 Go to previous messageGo to next message
John Watson
Messages: 4858
Registered: January 2010
Location: Global Village
Senior Member
It looks as though all your index statistics are way off. Please execute

dbms_stats.gather_index_stats(ownname=>... , indname=> ... , estimate_percent=>100)

for all of them and then try again, with and without my two hints.

Re: How to correct completely wrong cardinality shown in query plan [message #602578 is a reply to message #602576] Thu, 05 December 2013 14:42 Go to previous messageGo to next message
orausern
Messages: 735
Registered: December 2005
Location: Hyderabad
Senior Member
Its past midnight (2 am) for me so I will leave and respond further tomorrow. and Thanks a MILLION for the awesome help and support!!!!
Re: How to correct completely wrong cardinality shown in query plan [message #602579 is a reply to message #602578] Thu, 05 December 2013 14:43 Go to previous messageGo to next message
orausern
Messages: 735
Registered: December 2005
Location: Hyderabad
Senior Member
Sure let me do that before I go home...
Re: How to correct completely wrong cardinality shown in query plan [message #602581 is a reply to message #602579] Thu, 05 December 2013 15:06 Go to previous messageGo to next message
orausern
Messages: 735
Registered: December 2005
Location: Hyderabad
Senior Member
No luck yet - unluckily. We are back to that same cardinality we were seeing!!
/*
exec dbms_stats.gather_index_stats(ownname=>'MYSCHEMA' , indname=> 'PK_SAC_USER_ROLE_XREF', estimate_percent=>100)
exec dbms_stats.gather_index_stats(ownname=>'MYSCHEMA' , indname=> 'ACC_USR_RL_CROSS_IDX04' , estimate_percent=>100)
exec dbms_stats.gather_index_stats(ownname=>'MYSCHEMA' , indname=> 'ACC_USR_RL_CROSS_IDX05' , estimate_percent=>100)
exec dbms_stats.gather_index_stats(ownname=>'MYSCHEMA' , indname=> 'ACC_USR_RL_CROSS_IDX06' , estimate_percent=>100)
There were done ....took several minutes each...

PL/SQL procedure successfully completed.

Elapsed: 00:03:04.86
SQL>

PL/SQL procedure successfully completed.

Elapsed: 00:01:34.93
SQL>
PL/SQL procedure successfully completed.

Elapsed: 00:01:14.47


SQL>
PL/SQL procedure successfully completed.

Elapsed: 00:01:23.24


*/


SQL> SELECT     count(*)
  FROM acc_usr_rl_cross a, ww_ce_clnt_evt_rl b
  2    3   WHERE
  4  a.clnt_oid = 'E61CBE479123F1B5'
  5         AND
  6         a.clnt_oid = b.clnt_oid
  7         AND a.rl_oid = b.rl_oid
  8         and b.evt_id=61005609;

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

1 row selected.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  92b5ypk1m1azt, child number 0
-------------------------------------
SELECT     count(*)   FROM acc_usr_rl_cross a, ww_ce_clnt_evt_rl b  WHERE
a.clnt_oid = 'E61CBE479123F1B5'        AND        a.clnt_oid = b.clnt_oid        AND a.rl_oid =
b.rl_oid        and b.evt_id=61005609

Plan hash value: 2469558586

---------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |                       |      1 |      1 |      1 |00:00:00.09 |      92 |     80 |
|   2 |   NESTED LOOPS     |                       |      1 |      1 |  10826 |00:00:00.07 |      92 |     80 |
|*  3 |    INDEX RANGE SCAN| PK_WW_CE_CLNT_EVT_RL  |      1 |      1 |      4 |00:00:00.01 |       2 |      0 |
|*  4 |    INDEX RANGE SCAN| PK_ACC_USR_RL_CROSS    |      4 |      1 |  10826 |00:00:00.05 |      90 |     80 |
---------------------------------------------------------------------------------------------------------------

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

   3 - access("B"."CLNT_OID"='E61CBE479123F1B5' AND "B"."EVT_ID"=61005609)

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   4 - access("A"."CLNT_OID"='E61CBE479123F1B5' AND "A"."RL_OID"="B"."RL_OID")

[Updated on: Thu, 05 December 2013 15:08]

Report message to a moderator

Re: How to correct completely wrong cardinality shown in query plan [message #602585 is a reply to message #602581] Thu, 05 December 2013 15:23 Go to previous messageGo to next message
orausern
Messages: 735
Registered: December 2005
Location: Hyderabad
Senior Member
Will be back tomorrow...thanks again experts for the review of this.
Re: How to correct completely wrong cardinality shown in query plan [message #602613 is a reply to message #602556] Thu, 05 December 2013 23:15 Go to previous messageGo to next message
Kevin Meade
Messages: 1967
Registered: December 1999
Location: Connecticut USA
Senior Member
There are two kinds of cardinality estimates: 1. FILTER cardinality estimates and 2. JOIN cardinality estimates. I do not know the details of join cardinality estimation. This is a bit of a problem because some of your examples show join cardinality estimation. This can be seen in your predicates. For example

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

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

For step #3, cardinalities are estimated using bind variables. This is a simple filter cardinality estimate and it in fact is reasonably close (though could be better).
For step #4, cardinalities are estimated in part using a join. Since step #3 goes first, we have tuple substitution occurring for B.RO_OID. This is where the join is happening.

Maybe someone like Jonathan Lewis et.al. who know better than I can give us a hint on why the join cardinality might be off here. Maybe I'll go check his books to see if he told us there.

As for the FILTER cardinality estimates this is easier to grasp. Oracle uses two basic assumptions where it estimates filter cardinalities.

1) all columns are independent of each other
2) data is evenly distributed

But thes assumptions usually have varying degress of truth to them for different sets of rows. When these assumptions are wrong, cardinality estimation have error. The more wrong the assumptions the bigger the error. There are some common ways to see this.

1) SKEW (data is not evenly distributed)
2) DEPENDENCE (two or more columns have an "overlapp" in filtering ability because they predicate each others values)
3) OUT-OF-BOUNDS (limitations of stats collection have lead to a condition where the target value is on either side of the range of a columns values seen during stats collection)

For example:

1) SKEW (1,2,3,4,4,4,[97 times]).  There are 100 values in this list.  NDV means number of distinct values.  This list has NDV=4.  Assuming an even distribution of values, WHERE C1=1 should yield 25 rows.  WHERE C1=4 should yield 25 rows.  Indeed WHERE C1=:BINDVARIABLE should yield 25 rows.  Yet 25 is not representative of the cardinality of any of the real values in this list (a very bad case here).

2) DEPENDENCE  There are eight rows here.  NVD(C2) = 2.   NDV(C3) = 2.  Thus WHERE C2='A' should yield (1/2)% of the rows or 50% of 8=4.  WHERE C3=2 should yield 1/2% or 50% of 8 rows or 4 rows.  Thus WHERE C2='A' and C3=2 should yield (1/2)*(1/2)% or (1/4)% or 25% of 8 rows or 2 rows.  Unfortunately the columns C2 and C3 are not independent columns for this set of rows.  Whenver C2='A' we know that C3=1.  The predicate WHERE C2='A' and C3=2 gives 0 rows.

c1  c2  c3
1   A   1
2   A   1
3   A   1
4   A   1
5   B   2
6   B   2
7   B   2
8   B   2

3) OUT-OF-BOUNDS (1,2,3,4,4,4[97 times]).  There are 100 values in this list.  BUT if you collected statistics on it using say 3% of the rows, you might only see values 1,2,3 in which case statistics would record a LOW(C1)=1 and HIGH (C1)=3.  Thus the predicate WHERE C=4 should yield 0 rows (rounded up to 1) since based on statistics the value 4 in not in the list.  But in fact there are 97 occurrances of this value in the list.  The target value in the predicate is outside the low/high range recorded when you collected statistics.  It is OUT-OF-BOUNDS.  In fact for WHERE C=4, oracle will not compute 0 but rather will attempt to pro-rate a value based on its "distance" from the closest edge.  

But this calculation can depend upon a lot of things (DATA TYPE, % OF ROWS SAMPLED DURING STATISTICS COLLECTION) to name a couple.  VARCHAR2 is a string.  That implies a DISCRETE set of values ordered in a specific way and thus a specific type of scaling.  NUMBER is a continuous variable and might require a numerically scaled calculation.  DATE is continuous too but it would require scaling based on date math not number math.  Also, if the table has 1 million rows right now, and when you collected stats the sample size was 1 million then the likely hood that you did not see 4 while collecting stats would be small since it appears that your stats colleection event looked at all rows, and this should figure into your scaling calculation.  But if the sample size was 1 thousand there there is a much higher chance you did not see all the values including 4 and so the scaling calculation might be modified to account for this probability.


So

1. when data has 0 SKEW, the simple calculation of 1/NDV gives the exact number of rows for an equi-predicate for any value.  But as SKEW goes up this simple math gets more wrong.

2. when columns are independent then 1/NDV is extendable indefinitely with simple multiplication of 1/NDV(C1)*1/NDV(C2)..*1/NDV(C(N)) for any arbitraty number of equi-predicates.  But the more correlation there is between two columns the more error this simple math introduces.

3. when a target value is within the range of observed values, basic math applies but when target values are OUT-OF-BOUNDS then scaling (AND THUS ERROR) is introduced.

IF you are on 11gR2 then there are ways to handle these problems that usually work.

1. SKEW = collect histograms in order to give a better description of the distribution of data.
2. DEPENDENCE = create extended statistics essentially making both columns appear together as a single item.
3. OUT-OF-BOUNDS = collect using 100% sample size or AUTO sample size on 11gR2.

It is even allowed and recommended to do all three so you could create extended stats on column pairs in your predicates, then collect 100% sample, buidling a histogram on this extended stat.

dbms_stats.create_extended_stats

method_opt=>'for all columns size 1 for column (c1,c2) size 254'

If we assume your latest query plan

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
---------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |                       |      2 |      1 |      2 |00:00:00.13 |     184 |       |       |          |
|*  2 |   HASH JOIN        |                       |      2 |      1 |  21652 |00:00:00.11 |     184 |  1319K|  1319K|     2/0/0|
|*  3 |    INDEX RANGE SCAN| PK_ACC_USR_RL_CROSS   |      2 |   2468 |  24696 |00:00:00.02 |     180 |       |       |          |
|*  4 |    INDEX RANGE SCAN| PK_WW_CE_CLNT_EVT_RL  |      2 |      1 |      8 |00:00:00.01 |       4 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("A"."CLNT_OID"="B"."CLNT_OID" AND "A"."RL_OID"="B"."RL_OID")
   3 - access("A"."CLNT_OID"='E61CBE479123F1B5')
   4 - access("B"."CLNT_OID"='E61CBE479123F1B5' AND "B"."EVT_ID"=61005609)

then

exec dbms_stats.create_extended_stats('<table_owner_goes_here>','ww_ce_clnt_evt_rl','(CLNT_OID,EVT_ID)')
exec dbms_stats.gather_table_stats('<table_owner_goes_here>','ww_ce_clnt_evt_rl',method_opt=>'for all columns size 1 for column (CLNT_OID,EVT_ID) size 254')
exec dbms_stats.gather_table_stats('<table_owner_goes_here>','acc_usr_rl_cross',method_opt=>'for all columns size 1 for column CLNT_OID size 254')

On 11gR2, default estimate_percent (or AUTO) is 100%
On 11gR2, default cascade is TRUE

Now try the query that gives the above plan with hash join. Note that HASH JOIN results in two filter cardinality estimates instead of one filter and one join cardinality estimate we see in the nested loops based plans. If after this you are not happy, then consider introducing DYNAMIC SAMPLING. If you are on a high enough version of the database, dynamic sampling may be smart enough to do better join cardinality calculations.

Hope this helps. You may find that there are limitations to the optimizer and you may eventually be forced into a BRUTE FORCE solution (hints,baselines etc.).

Kevin

[Updated on: Thu, 05 December 2013 23:21]

Report message to a moderator

Re: How to correct completely wrong cardinality shown in query plan [message #602622 is a reply to message #602613] Fri, 06 December 2013 01:16 Go to previous messageGo to next message
John Watson
Messages: 4858
Registered: January 2010
Location: Global Village
Senior Member
The problem with extended statistics, Kevin, is that OP is on 10g. I did think, though,
that Oracle would gather what are in effect extended statistics if there is composite index
across the columns, which there is. That was why I suggested analyzing the index with
100%.
I am now at a loss for any suggestions, other than hints.

This question has been kicking around here for some time,
https://forums.oracle.com/thread/2608629?tstart=0
Re: How to correct completely wrong cardinality shown in query plan [message #602632 is a reply to message #602622] Fri, 06 December 2013 02:13 Go to previous messageGo to next message
Roachcoach
Messages: 1233
Registered: May 2010
Location: UK
Senior Member
I'm a big fan of eliminating the simple bits first before getting too complex and with that in mind it looks like suspiciously like skew issues with what we have so far.

@OP: For the binds/literals you are passing, are those values representative of all results of this query i.e. are all executions getting approximately the same issue or just this set of values? Also, what did the 10053 tell you?
Re: How to correct completely wrong cardinality shown in query plan [message #602660 is a reply to message #602632] Fri, 06 December 2013 08:18 Go to previous messageGo to next message
Kevin Meade
Messages: 1967
Registered: December 1999
Location: Connecticut USA
Senior Member
John. I believe the creation of a hidden column and then subsequent collection of statistics on that column is related to function based indexes only.

In that case maybe try something like this:

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

   2 - access("A"."CLNT_OID"="B"."CLNT_OID" AND "A"."RL_OID"="B"."RL_OID")
   3 - access("A"."CLNT_OID"='E61CBE479123F1B5')
   4 - access("B"."CLNT_OID"='E61CBE479123F1B5' AND "B"."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;

Can become this:

create index fbi_i1 on ww_ce_clnt_evt_rl (clnt_oid||','||evt_id);

exec dmbs_stats.gather_table_stats('<owner>','ww_ce_clnt_evt_rl',method_opt=>'for all columns size 1 for all hidden_column size 254')

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
and (b.clnt_oid||','||b.evt_id) = :v_clnt_oid||','||:v_evt_id;


It is hack but gives the basic idea. Maybe OP can try this and tell us if it works as expected. Notice how transitive property was used in other queries to add the predicate B.CLNT_OID=:V_CLNT_OID to the query plan. We make that explicit here. Kevin
Re: How to correct completely wrong cardinality shown in query plan [message #602664 is a reply to message #602660] Fri, 06 December 2013 08:37 Go to previous messageGo to next message
orausern
Messages: 735
Registered: December 2005
Location: Hyderabad
Senior Member
Hi Kevin, John and Roachcoach,

My heartfelt thanks for the awesome help!
Hi Kevin,
Awesome analysis!!! Please find below my inputs:
1) Created that one index and calculating stats did not help and the optimizer stubbornly holds on to using the primary key index...I had tried the no_index hint and then it did
switch to using other index (04 AND 05) but the cardinality problem continued.


SQL> SELECT     count(*)
  FROM acc_usr_rl_cross a,ww_ce_clnt_evt_rl b
 WHERE
a.clnt_oid = 'E61CBE479123F1B5'
       AND
       a.clnt_oid = b.clnt_oid
       AND a.rl_oid = b.rl_oid
       and b.evt_id=61005609;  

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

1 row selected.

Elapsed: 00:00:00.49
SQL> @p
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats  +cost'))
  2  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  92b5ypk1m1azt, child number 0
-------------------------------------
SELECT     count(*)   FROM acc_usr_rl_cross a,ww_ce_clnt_evt_rl b  WHERE a.clnt_oid =
'E61CBE479123F1B5'        AND        a.clnt_oid = b.clnt_oid        AND a.rl_oid = b.rl_oid        and
b.evt_id=61005609

Plan hash value: 3505715767

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                 | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |                      |      1 |      1 |            |      1 |00:00:00.20 |      92 |     82 |
|   2 |   NESTED LOOPS     |                      |      1 |      1 |     5   (0)|  10826 |00:00:00.18 |      92 |     82 |
|*  3 |    INDEX RANGE SCAN| PK_WW_CE_CLNT_EVT_RL |      1 |      1 |     2   (0)|      4 |00:00:00.01 |       2 |      2 |
|*  4 |    INDEX RANGE SCAN| PK_ACC_USR_RL_CROSS  |      4 |      1 |     3   (0)|  10826 |00:00:00.17 |      90 |     80 |
---------------------------------------------------------------------------------------------------------------------------

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

   3 - access("B"."CLNT_OID"='E61CBE479123F1B5' AND "B"."EVT_ID"=61005609)

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   4 - access("A"."CLNT_OID"='E61CBE479123F1B5' AND "A"."RL_OID"="B"."RL_OID")


23 rows selected.


2) Now I understand that the whole issue is just with one table and not with two tables....we can reproduce that with a simpler single table query as follows:
The four rl_oid in the IN clause below is the out come of the two table join in the above query...so basically the issue is that this table: acc_usr_rl_cross gives completely
wrong cardinality and that is what has to be fixed.
SQL> select  count(*) from
acc_usr_rl_cross a
where a.clnt_oid = 'E61CBE479CA0F1B5'
and rl_oid in
( 62013104,
  62013108,
  62013109,
  62013110
)

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

1 row selected.

Elapsed: 00:00:00.25
SQL> @p
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats  +cost'))
  2  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  215fvpfmx4rcw, child number 0
-------------------------------------
select  count(*) from acc_usr_rl_cross a where a.clnt_oid = 'E61CBE479CA0F1B5' and rl_oid in ( 62013104,
62013108,   62013109,   62013110 )

Plan hash value: 1229111938

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |                     |      1 |      1 |            |      1 |00:00:00.09 |      92 |     80 |
|   2 |   INLIST ITERATOR  |                     |      1 |        |            |  10826 |00:00:00.08 |      92 |     80 |
|*  3 |    INDEX RANGE SCAN| PK_ACC_USR_RL_CROSS |      4 |      1 |     7   (0)|  10826 |00:00:00.07 |      92 |     80 |
--------------------------------------------------------------------------------------------------------------------------

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

   3 - access("A"."CLNT_OID"='E61CBE479CA0F1B5' AND (("RL_OID"=62013104 OR "RL_OID"=62013108 OR "RL_OID"=62013109
              OR "RL_OID"=62013110)))


21 rows selected.

Elapsed: 00:00:00.75


Thank you!!
Re: How to correct completely wrong cardinality shown in query plan [message #602665 is a reply to message #602664] Fri, 06 December 2013 08:42 Go to previous messageGo to next message
Kevin Meade
Messages: 1967
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes and since you are on 10g (is that correct?) extended stats is not available to you. So did you try the hack John and I suggested using the function based index? I don't see that anywhere?

Try this query instead once you create the index. It is semantically the same but the database don't know it.

SELECT count(*)
  FROM acc_usr_rl_cross a, ww_ce_clnt_evt_rl b
 WHERE a.clnt_oid = :v_clnt_oid
       AND a.rl_oid = b.rl_oid
and (b.clnt_oid||','||b.evt_id) = :v_clnt_oid||','||:v_evt_id;

Re: How to correct completely wrong cardinality shown in query plan [message #602668 is a reply to message #602665] Fri, 06 December 2013 08:57 Go to previous messageGo to next message
orausern
Messages: 735
Registered: December 2005
Location: Hyderabad
Senior Member
Hi Kevin,

Yes, I am on 10g (10.2.0.4) , I tried the other trick but it is not helping us, below is the copy of my sqlplus screen:
Note that evt_id is a number so I added a to_char in the index.
--Kevin reply2:
SQL>create index test_idx_ww1 on ww_ce_clnt_evt_rl (clnt_o
id||','||to_char(evt_id)) TABLESPACE DATA1;

Index created.

SQL>exec dbMs_stats.gather_table_stats('MYSCHEMA','WW_CE_CLNT_EVT_RL',method_opt=>'for all columns size 1 for all hidden columns
 size 254');

PL/SQL procedure successfully completed.

SELECT     count(*)
  FROM acc_usr_rl_cross a, ww_ce_clnt_evt_rl b
 WHERE     
a.clnt_oid = 'E61CBE479123F1B5'
       AND 
       a.clnt_oid = b.clnt_oid
       AND a.rl_oid = b.rl_oid
       and b.evt_id=61005609
       AND (b.clnt_oid||','||b.evt_id) = 'E61CBE479123F1B5'||','||to_char(61005609);

SQL> SELECT     count(*)
  2    FROM acc_usr_rl_cross a, ww_ce_clnt_evt_rl b
 WHERE
  3    4  a.clnt_oid = 'E61CBE479123F1B5'
  5         AND
  6         a.clnt_oid = b.clnt_oid
  7         AND a.rl_oid = b.rl_oid
  8         and b.evt_id=61005609
  9         AND (b.clnt_oid||','||b.evt_id) = 'E61CBE479123F1B5'||','||to_char(61005609);

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

1 row selected.

Elapsed: 00:00:00.33
SQL> @p
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats  +cost'))
  2  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cmq91qvuzh6bg, child number 0
-------------------------------------
SELECT     count(*)   FROM acc_usr_rl_cross a, ww_ce_clnt_evt_rl b  WHERE a.clnt_oid =
'E61CBE479123F1B5'        AND        a.clnt_oid = b.clnt_oid        AND a.rl_oid = b.rl_oid        and
b.evt_id=61005609        AND (b.clnt_oid||','||b.evt_id) = 'E61CBE479123F1B5'||','||to_char(61005609)

Plan hash value: 3505715767

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                 | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |                      |      1 |      1 |            |      1 |00:00:00.11 |      92 |     82 |
|   2 |   NESTED LOOPS     |                      |      1 |      1 |     5   (0)|  10826 |00:00:00.10 |      92 |     82 |
|*  3 |    INDEX RANGE SCAN| PK_WW_CE_CLNT_EVT_RL |      1 |      1 |     2   (0)|      4 |00:00:00.01 |       2 |      2 |
|*  4 |    INDEX RANGE SCAN| PK_ACC_USR_RL_CROSS  |      4 |      1 |     3   (0)|  10826 |00:00:00.07 |      90 |     80 |
---------------------------------------------------------------------------------------------------------------------------

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

   3 - access("B"."CLNT_OID"='E61CBE479123F1B5' AND "B"."EVT_ID"=61005609)

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       filter("B"."CLNT_OID"||','||TO_CHAR("B"."EVT_ID")='E61CBE479123F1B5,61005609')
   4 - access("A"."CLNT_OID"='E61CBE479123F1B5' AND "A"."RL_OID"="B"."RL_OID")


24 rows selected.


Re: How to correct completely wrong cardinality shown in query plan [message #602669 is a reply to message #602668] Fri, 06 December 2013 09:03 Go to previous messageGo to next message
orausern
Messages: 735
Registered: December 2005
Location: Hyderabad
Senior Member
Also had tried this other query with small variation but again , no help:

I really think that the whole issue is with Just One Table - this one: acc_usr_rl_cross. The single table query and output I sent earlier is totally wrong cardinality.
So that is simpler example (no joins, no other table to bother about) and the issue is reproduced. May be that can be used to test further solutions/alternatives.
SELECT     count(*)
  FROM acc_usr_rl_cross a, ww_ce_clnt_evt_rl b
 WHERE     
a.clnt_oid = 'E61CBE479123F1B5'
       AND 
       a.clnt_oid = b.clnt_oid
       AND a.rl_oid = b.rl_oid
       and b.evt_id=61005609
       AND (b.clnt_oid||','||to_char(b.evt_id)) = 'E61CBE479123F1B5'||','||to_char(61005609);

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

1 row selected.

Elapsed: 00:00:00.12
SQL> @p
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats  +cost'))
  2  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  a3ssruzy7c2jb, child number 0
-------------------------------------
SELECT     count(*)   FROM acc_usr_rl_cross a, ww_ce_clnt_evt_rl b  WHERE a.clnt_oid =
'E61CBE479123F1B5'        AND        a.clnt_oid = b.clnt_oid        AND a.rl_oid = b.rl_oid        and
b.evt_id=61005609        AND (b.clnt_oid||','||to_char(b.evt_id)) = 'E61CBE479123F1B5'||','||to_char(61005609)

Plan hash value: 3505715767

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                 | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |                      |      1 |      1 |            |      1 |00:00:00.11 |      92 |     81 |
|   2 |   NESTED LOOPS     |                      |      1 |      1 |     5   (0)|  10826 |00:00:00.10 |      92 |     81 |
|*  3 |    INDEX RANGE SCAN| PK_WW_CE_CLNT_EVT_RL |      1 |      1 |     2   (0)|      4 |00:00:00.01 |       2 |      1 |
|*  4 |    INDEX RANGE SCAN| PK_ACC_USR_RL_CROSS  |      4 |      1 |     3   (0)|  10826 |00:00:00.09 |      90 |     80 |
---------------------------------------------------------------------------------------------------------------------------

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

   3 - access("B"."CLNT_OID"='E61CBE479123F1B5' AND "B"."EVT_ID"=61005609)

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       filter("B"."CLNT_OID"||','||TO_CHAR("B"."EVT_ID")='E61CBE479123F1B5,61005609')
   4 - access("A"."CLNT_OID"='E61CBE479123F1B5' AND "A"."RL_OID"="B"."RL_OID")

[Updated on: Fri, 06 December 2013 09:05]

Report message to a moderator

Re: How to correct completely wrong cardinality shown in query plan [message #602671 is a reply to message #602669] Fri, 06 December 2013 09:18 Go to previous messageGo to next message
Kevin Meade
Messages: 1967
Registered: December 1999
Location: Connecticut USA
Senior Member
Getting much better. Good job. Please try this variation of the query. I am disappointed that the optimizer is not getting the hang of things.
SELECT count(*)
FROM acc_usr_rl_cross a, ww_ce_clnt_evt_rl b
WHERE a.clnt_oid = 'E61CBE479123F1B5' AND a.rl_oid = b.rl_oid
 AND (b.clnt_oid||','||to_char(b.evt_id)) = 'E61CBE479123F1B5'||','||to_char(61005609)
;

This eliminates all hooks between the two tables but still keeps the logic intact exploiting the new index. Again this is a real hack but we are getting desparate now so hacks apply.

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

   3 - access("B"."CLNT_OID"='E61CBE479123F1B5' AND "B"."EVT_ID"=61005609)
       filter("B"."CLNT_OID"||','||TO_CHAR("B"."EVT_ID")='E61CBE479123F1B5,61005609')
   4 - access("A"."CLNT_OID"='E61CBE479123F1B5' AND "A"."RL_OID"="B"."RL_OID")


Also please dump col stats again so we can verify that the extended stats were created for the function based index and a histogram was created.
Re: How to correct completely wrong cardinality shown in query plan [message #602672 is a reply to message #602671] Fri, 06 December 2013 09:26 Go to previous messageGo to next message
orausern
Messages: 735
Registered: December 2005
Location: Hyderabad
Senior Member
Quote:

Also dump col stats again so we can verify that the extended stats were c

Hi Kevin,
I did not understand, how to dump the col stats? Can you please send me the command for it?

Thanks,
Re: How to correct completely wrong cardinality shown in query plan [message #602673 is a reply to message #602672] Fri, 06 December 2013 09:29 Go to previous messageGo to next message
Kevin Meade
Messages: 1967
Registered: December 1999
Location: Connecticut USA
Senior Member
try this.

/*
CREATE OR replace FUNCTION kev_raw_to_string (rawval RAW, TYPE VARCHAR2) RETURN VARCHAR2 
IS 
  cn  NUMBER; 
  cv  VARCHAR2(32); 
  cd  DATE; 
  cnv NVARCHAR2(32); 
  cr  ROWID; 
  cc  CHAR(32); 
BEGIN 
    IF ( TYPE = 'NUMBER' ) THEN 
      dbms_stats.Convert_raw_value(rawval, cn); 

      RETURN '"'||cn||'"'; 
    ELSIF ( TYPE = 'VARCHAR2' ) THEN 
      dbms_stats.Convert_raw_value(rawval, cv); 

      RETURN '"'||cv||'"'; 
    ELSIF ( TYPE = 'DATE' ) THEN 
      dbms_stats.Convert_raw_value(rawval, cd); 

      RETURN '"'||to_char(cd,'dd-mon-rrrr.hh24:mi:ss')||'"'; 
    ELSIF ( TYPE = 'NVARCHAR2' ) THEN 
      dbms_stats.Convert_raw_value(rawval, cnv); 

      RETURN '"'||cnv||'"'; 
    ELSIF ( TYPE = 'ROWID' ) THEN 
      dbms_stats.Convert_raw_value(rawval, cr); 

      RETURN '"'||cnv||'"'; 
    ELSIF ( TYPE = 'CHAR' ) THEN 
      dbms_stats.Convert_raw_value(rawval, cc); 

      RETURN '"'||cc||'"'; 
    ELSE 
      RETURN '"UNSUPPORTED DATA_TYPE"'; 
    END IF; 
END;
/ 
*/
col low_value format a30
col high_value format a30
col last_analyzed format a22
--select table_name,column_name, num_distinct, num_nulls, num_buckets, sample_size,last_analyzed
select
  OWNER
, TABLE_NAME
, COLUMN_NAME
, NUM_DISTINCT
, NUM_NULLS
, NUM_BUCKETS
, SAMPLE_SIZE
, AVG_COL_LEN
, DENSITY
, TO_CHAR(LAST_ANALYZED,'dd-mon-rrrr.hh24:mi:ss') last_analyzed
, GLOBAL_STATS
, USER_STATS
, kev_raw_to_string (LOW_VALUE,(select data_type from dba_tab_columns b where b.owner = a.owner and b.table_name = a.table_name and b.column_name = a.column_name)) LOW_VALUE
, kev_raw_to_string (HIGH_VALUE,(select data_type from dba_tab_columns b where b.owner = a.owner and b.table_name = a.table_name and b.column_name = a.column_name)) HIGH_VALUE
from dba_tab_col_statistics a
where (owner,table_name) in
(
 (upper('&&1'),upper('&&2'))
)
--and (column_name = 'ROW_TERM_DATE$' or num_buckets > 1)
order by TABLE_NAME,COLUMN_NAME
/


You will need to create the package and then save the code as a script to execute. @showcolstats <owner> <table>
Re: How to correct completely wrong cardinality shown in query plan [message #602675 is a reply to message #602673] Fri, 06 December 2013 09:49 Go to previous messageGo to next message
orausern
Messages: 735
Registered: December 2005
Location: Hyderabad
Senior Member
Hi Kevin,

Some success!! Your and John's index is getting used now!! But note that we still get that cardinaly way off as usual...
SQL> SELECT count(*)
FROM acc_usr_rl_cross a, ww_ce_clnt_evt_rl b
WHERE a.clnt_oid = 'E61CBE479123F1B5'AND a.rl_oid = b.rl_oid
 AND (b.clnt_oid||','||to_char(b.evt_id)) = 'E61CBE479123F1B5'||','||to_char(61005609);
  2    3    4
  COUNT(*)
----------
     10826

1 row selected.

Elapsed: 00:00:00.52
SQL>
SQL> @p
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats  +cost'))
  2  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1tj6x42jsmhh3, child number 0
-------------------------------------
SELECT count(*) FROM acc_usr_rl_cross a, ww_ce_clnt_evt_rl b WHERE a.clnt_oid = 'E61CBE479123F1B5'AND
a.rl_oid = b.rl_oid  AND (b.clnt_oid||','||to_char(b.evt_id)) = 'E61CBE479123F1B5'||','||to_char(61005609)

Plan hash value: 848196772

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE               |                     |      1 |      1 |            |      1 |00:00:00.15 |      93 |     83 |
|   2 |   NESTED LOOPS                |                     |      1 |      6 |    20   (0)|  10826 |00:00:00.13 |      93 |     83 |
|   3 |    TABLE ACCESS BY INDEX ROWID| WW_CE_CLNT_EVT_RL   |      1 |      6 |     2   (0)|      4 |00:00:00.04 |       3 |      3 |
|*  4 |     INDEX RANGE SCAN          | TEST_IDX_WW1        |      1 |      6 |     1   (0)|      4 |00:00:00.03 |       2 |      2 |
|*  5 |    INDEX RANGE SCAN           | PK_ACC_USR_RL_CROSS |      4 |      1 |     3   (0)|  10826 |00:00:00.09 |      90 |     80 |
-------------------------------------------------------------------------------------------------------------------------------------

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

   4 - access("B"."SYS_NC00004$"='E61CBE479123F1B5,61005609')

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   5 - access("A"."CLNT_OID"='E61CBE479123F1B5' AND "A"."RL_OID"="B"."RL_OID")


23 rows selected.

Elapsed: 00:00:01.23


--Now output of your script: (also attached the file).
SQL> set echo on
SQL> @script_k.sql MYSCHEMA ACC_USR_RL_CROSS
SQL> col low_value format a30
SQL> col high_value format a30
SQL> col last_analyzed format a22
SQL> --select table_name,column_name, num_distinct, num_nulls, num_buckets, sample_size,last_analyzed
SQL> select
  2    OWNER
  3  , TABLE_NAME
  4  , COLUMN_NAME
  5  , NUM_DISTINCT
  6  , NUM_NULLS
  7  , NUM_BUCKETS
  8  , SAMPLE_SIZE
  9  , AVG_COL_LEN
 10  , DENSITY
 11  , TO_CHAR(LAST_ANALYZED,'dd-mon-rrrr.hh24:mi:ss') last_analyzed
 12  , GLOBAL_STATS
 13  , USER_STATS
 14  , kev_raw_to_string (LOW_VALUE,(select data_type from dba_tab_columns b where b.owner = a.owner and b.table_name = a.table_name and b.column_name = a.column_name)) LOW_VALUE
 15  , kev_raw_to_string (HIGH_VALUE,(select data_type from dba_tab_columns b where b.owner = a.owner and b.table_name = a.table_name and b.column_name = a.column_name)) HIGH_VALUE
 16  from dba_tab_col_statistics a
 17  where (owner,table_name) in
 18  (
 19   (upper('&&1'),upper('&&2'))
 20  )
 21  --and (column_name = 'ROW_TERM_DATE$' or num_buckets > 1)
 22  order by TABLE_NAME,COLUMN_NAME
 23  /
old  19:  (upper('&&1'),upper('&&2'))
new  19:  (upper('MYSCHEMA'),upper('ACC_USR_RL_CROSS'))

OWNER                          TABLE_NAME                                       
------------------------------ ------------------------------                   
COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS NUM_BUCKETS SAMPLE_SIZE  
------------------------------ ------------ ---------- ----------- -----------  
AVG_COL_LEN    DENSITY LAST_ANALYZED          GLO USE                           
----------- ---------- ---------------------- --- ---                           
LOW_VALUE                      HIGH_VALUE                                       
------------------------------ ------------------------------                   
MYSCHEMA                ACC_USR_RL_CROSS                                 
ADDDEDMANUALLY                            2          0           1        5800  
          2         .5 05-dec-2013.14:32:19   YES NO                            
"N                             "Y                                               
   "                              "                                             

OWNER                          TABLE_NAME                                       
------------------------------ ------------------------------                   
COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS NUM_BUCKETS SAMPLE_SIZE  
------------------------------ ------------ ---------- ----------- -----------  
AVG_COL_LEN    DENSITY LAST_ANALYZED          GLO USE                           
----------- ---------- ---------------------- --- ---                           
LOW_VALUE                      HIGH_VALUE                                       
------------------------------ ------------------------------                   
                                                                                
MYSCHEMA                ACC_USR_RL_CROSS                                 
CLNT_OID                               5425          0           1        5800  
         17 .000184332 05-dec-2013.14:32:19   YES NO                            
"001TWPBZEPF000XX"             "G4ZZRG5CEBJ00041"                               

OWNER                          TABLE_NAME                                       
------------------------------ ------------------------------                   
COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS NUM_BUCKETS SAMPLE_SIZE  
------------------------------ ------------ ---------- ----------- -----------  
AVG_COL_LEN    DENSITY LAST_ANALYZED          GLO USE                           
----------- ---------- ---------------------- --- ---                           
LOW_VALUE                      HIGH_VALUE                                       
------------------------------ ------------------------------                   
                                                                                
MYSCHEMA                ACC_USR_RL_CROSS                                 
CREATED_BY                               30    6602699           1        3028  
          5 .033333333 05-dec-2013.14:32:19   YES NO                            
"OAGMSG"                       "aaadd00356543"                                  

OWNER                          TABLE_NAME                                       
------------------------------ ------------------------------                   
COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS NUM_BUCKETS SAMPLE_SIZE  
------------------------------ ------------ ---------- ----------- -----------  
AVG_COL_LEN    DENSITY LAST_ANALYZED          GLO USE                           
----------- ---------- ---------------------- --- ---                           
LOW_VALUE                      HIGH_VALUE                                       
------------------------------ ------------------------------                   
                                                                                
MYSCHEMA                ACC_USR_RL_CROSS                                 
CREATED_DATE                         980484         24           1      561707  
          8 1.0199E-06 05-dec-2013.14:32:19   YES NO                            
"22-nov-2006.22:30:02"         "14-feb-2013.19:13:20"                           

OWNER                          TABLE_NAME                                       
------------------------------ ------------------------------                   
COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS NUM_BUCKETS SAMPLE_SIZE  
------------------------------ ------------ ---------- ----------- -----------  
AVG_COL_LEN    DENSITY LAST_ANALYZED          GLO USE                           
----------- ---------- ---------------------- --- ---                           
LOW_VALUE                      HIGH_VALUE                                       
------------------------------ ------------------------------                   
                                                                                
MYSCHEMA                ACC_USR_RL_CROSS                                 
INCL                                      2          0           1        5800  
          2         .5 05-dec-2013.14:32:19   YES NO                            
"N                             "Y                                               

OWNER                          TABLE_NAME                                       
------------------------------ ------------------------------                   
COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS NUM_BUCKETS SAMPLE_SIZE  
------------------------------ ------------ ---------- ----------- -----------  
AVG_COL_LEN    DENSITY LAST_ANALYZED          GLO USE                           
----------- ---------- ---------------------- --- ---                           
LOW_VALUE                      HIGH_VALUE                                       
------------------------------ ------------------------------                   
   "                              "                                             
                                                                                
MYSCHEMA                ACC_USR_RL_CROSS                                 
MODIFIED_BY                             519   11565204           1        7207  
          3 .001926782 05-dec-2013.14:32:19   YES NO                            

OWNER                          TABLE_NAME                                       
------------------------------ ------------------------------                   
COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS NUM_BUCKETS SAMPLE_SIZE  
------------------------------ ------------ ---------- ----------- -----------  
AVG_COL_LEN    DENSITY LAST_ANALYZED          GLO USE                           
----------- ---------- ---------------------- --- ---                           
LOW_VALUE                      HIGH_VALUE                                       
------------------------------ ------------------------------                   
"AALBRIGHT@TESTOY2"            "migrsdfd040311"                                
                                                                                
MYSCHEMA                ACC_USR_RL_CROSS                                 
MODIFIED_DATE                        492162   11685320           1      425760  
          2 2.0319E-06 05-dec-2013.14:32:19   YES NO                            

OWNER                          TABLE_NAME                                       
------------------------------ ------------------------------                   
COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS NUM_BUCKETS SAMPLE_SIZE  
------------------------------ ------------ ---------- ----------- -----------  
AVG_COL_LEN    DENSITY LAST_ANALYZED          GLO USE                           
----------- ---------- ---------------------- --- ---                           
LOW_VALUE                      HIGH_VALUE                                       
------------------------------ ------------------------------                   
"26-may-2010.11:10:23"         "14-feb-2013.18:12:11"                           
                                                                                
MYSCHEMA                ACC_USR_RL_CROSS                                 
RL_OID                                55741          0           1       55761  
          7  .00001794 05-dec-2013.14:32:19   YES NO                            

OWNER                          TABLE_NAME                                       
------------------------------ ------------------------------                   
COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS NUM_BUCKETS SAMPLE_SIZE  
------------------------------ ------------ ---------- ----------- -----------  
AVG_COL_LEN    DENSITY LAST_ANALYZED          GLO USE                           
----------- ---------- ---------------------- --- ---                           
LOW_VALUE                      HIGH_VALUE                                       
------------------------------ ------------------------------                   
"45"                           "800629054"                                      
                                                                                
MYSCHEMA                ACC_USR_RL_CROSS                                 
USR_ID                              6034675          0           1     3347090  
         18 1.6571E-07 05-dec-2013.14:32:19   YES NO                            

OWNER                          TABLE_NAME                                       
------------------------------ ------------------------------                   
COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS NUM_BUCKETS SAMPLE_SIZE  
------------------------------ ------------ ---------- ----------- -----------  
AVG_COL_LEN    DENSITY LAST_ANALYZED          GLO USE                           
----------- ---------- ---------------------- --- ---                           
LOW_VALUE                      HIGH_VALUE                                       
------------------------------ ------------------------------                   
"001TWPBZEPF000DA"             "STOPARLITEMP@UNTDBNK1"                          
                                                                                

9 rows selected.

SQL> 
SQL> spo off


update: made slight change in the output file to remove any real data....
  • Attachment: s_o.log
    (Size: 11.39KB, Downloaded 24 times)

[Updated on: Fri, 06 December 2013 09:54]

Report message to a moderator

Re: How to correct completely wrong cardinality shown in query plan [message #602676 is a reply to message #602675] Fri, 06 December 2013 09:55 Go to previous messageGo to next message
orausern
Messages: 735
Registered: December 2005
Location: Hyderabad
Senior Member
Just fyi...going for dinner and back in about an hour...
Re: How to correct completely wrong cardinality shown in query plan [message #602680 is a reply to message #602676] Fri, 06 December 2013 10:30 Go to previous messageGo to next message
Kevin Meade
Messages: 1967
Registered: December 1999
Location: Connecticut USA
Senior Member
this may be a reflection of JOIN cardinality calculations. Oracle needs somehow to figure out how many rows will match based on the join criteria. Also as you have noted, just getting an index to be used does not always solve problems like this. The issue still appears to be one of join cardinalities. It is just that the role of the tables has changed.

Do the following please.

select count(*)
from acc_usr_rl_cross a
where "A"."CLNT_OID"='E61CBE479123F1B5';

explain plan for select count(*)
from acc_usr_rl_cross a
where "A"."CLNT_OID"='E61CBE479123F1B5';

and show the plan

Also, add this hint to the query /*+ cardinality (a 10826) */ so that we can see if giving the right cardinality affects the plan. I should produce a hash join variation. then run the query and tell us how long it takes and if that is an improvement.
Re: How to correct completely wrong cardinality shown in query plan [message #602683 is a reply to message #602680] Fri, 06 December 2013 11:11 Go to previous message
orausern
Messages: 735
Registered: December 2005
Location: Hyderabad
Senior Member
Hi Kevin,
Here are those details...to summarize:
1) For the single table count query, optimizer DOES accept the hint and comes with right cardinality.
2) But...when I apply that same hint back to that two table join, it goes back to that wrong cardinality (1 E rows!).
3) This time suddenly the optimizer stopped using that primary key used used another index (in the unhinted query -query1) and came with a much better cardinality of 2468!
This is all too high tech for me!

SELECT count(*)
FROM acc_usr_rl_cross a
WHERE a.clnt_oid = 'E61CBE479123F1B5';



SELECT /*+ cardinality (a 10826) */ count(*)
FROM acc_usr_rl_cross a
WHERE a.clnt_oid = 'E61CBE479123F1B5';

--And finally the join query.
SELECT   /*+ cardinality (a 10826) */  count(*)
  FROM acc_usr_rl_cross a, .ww_ce_clnt_evt_rl b
 WHERE     
a.clnt_oid = 'E61CBE479123F1B5'
       AND 
       a.clnt_oid = b.clnt_oid
       AND a.rl_oid = b.rl_oid
       and b.evt_id=61005609;

output:
SQL> SELECT count(*)
FROM acc_usr_rl_cross a
WHERE a.clnt_oid = 'E61CBE479123F1B5';  2    3

  COUNT(*)
----------
     12348

1 row selected.

Elapsed: 00:00:00.15
SQL> @p
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats  +cost'))
  2  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  axk786n20frab, child number 0
-------------------------------------
SELECT count(*) FROM acc_usr_rl_cross a WHERE a.clnt_oid = 'E61CBE479123F1B5'

Plan hash value: 2693906332

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name                   | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE   |                        |      1 |      1 |            |      1 |00:00:00.08 |      81 |     81 |
|*  2 |   INDEX RANGE SCAN| ACC_USR_RL_CROSS_IDX04 |      1 |   2468 |    19   (0)|  12348 |00:00:00.06 |      81 |     81 |
----------------------------------------------------------------------------------------------------------------------------

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

   2 - access("A"."CLNT_OID"='E61CBE479123F1B5')


18 rows selected.

Elapsed: 00:00:00.48


--query 2
SQL> SELECT /*+ cardinality (a 10826) */ count(*)
FROM acc_usr_rl_cross a
WHERE a.clnt_oid = 'E61CBE479123F1B5';  2    3

  COUNT(*)
----------
     12348

1 row selected.

Elapsed: 00:00:00.13
SQL> @p
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats  +cost'))
  2  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  csxp27v611c43, child number 0
-------------------------------------
SELECT /*+ cardinality (a 10826) */ count(*) FROM acc_usr_rl_cross a WHERE a.clnt_oid = 'E61CBE479123F1B5'

Plan hash value: 2693906332

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name                   | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE   |                        |      1 |      1 |            |      1 |00:00:00.08 |      81 |     81 |
|*  2 |   INDEX RANGE SCAN| ACC_USR_RL_CROSS_IDX04 |      1 |  10826 |    19   (0)|  12348 |00:00:00.06 |      81 |     81 |
----------------------------------------------------------------------------------------------------------------------------

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

   2 - access("A"."CLNT_OID"='E61CBE479123F1B5')


18 rows selected.


--query 3
SQL> SELECT   /*+ cardinality (a 10826) */  count(*)
  2    FROM acc_usr_rl_cross a, .ww_ce_clnt_evt_rl b
  3   WHERE
  4  a.clnt_oid = 'E61CBE479123F1B5'
       AND
  5    6         a.clnt_oid = b.clnt_oid
  7         AND a.rl_oid = b.rl_oid
  8         and b.evt_id=61005609;

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

1 row selected.

Elapsed: 00:00:00.18
SQL> @p
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats  +cost'))
  2  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  43aux8smww0dp, child number 0
-------------------------------------
SELECT   /*+ cardinality (a 10826) */  count(*)   FROM acc_usr_rl_cross a, .ww_ce_clnt_evt_rl b
WHERE a.clnt_oid = 'E61CBE479123F1B5'        AND        a.clnt_oid = b.clnt_oid        AND a.rl_oid = b.rl_oid
   and b.evt_id=61005609

Plan hash value: 3505715767

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                 | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |                      |      1 |      1 |            |      1 |00:00:00.07 |      92 |     82 |
|   2 |   NESTED LOOPS     |                      |      1 |      1 |     5   (0)|  10826 |00:00:00.06 |      92 |     82 |
|*  3 |    INDEX RANGE SCAN| PK_WW_CE_CLNT_EVT_RL |      1 |      1 |     2   (0)|      4 |00:00:00.01 |       2 |      2 |
|*  4 |    INDEX RANGE SCAN| PK_ACC_USR_RL_CROSS  |      4 |      1 |     3   (0)|  10826 |00:00:00.05 |      90 |     80 |
---------------------------------------------------------------------------------------------------------------------------

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

   3 - access("B"."CLNT_OID"='E61CBE479123F1B5' AND "B"."EVT_ID"=61005609)

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   4 - access("A"."CLNT_OID"='E61CBE479123F1B5' AND "A"."RL_OID"="B"."RL_OID")


23 rows selected.

Previous Topic: FTS on table
Next Topic: SQL with BIND Variable Slow - Bind Peeking
Goto Forum:
  


Current Time: Fri Dec 19 01:45:26 CST 2014

Total time taken to generate the page: 0.08883 seconds