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 Go to next message](/forum/theme/orafaq/images/down.png) |
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] Report message to a moderator
|
|
|
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
orausern
Messages: 826 Registered: December 2005
|
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 #602572 is a reply to message #602571] |
Thu, 05 December 2013 14:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
orausern
Messages: 826 Registered: December 2005
|
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 #602576 is a reply to message #602574] |
Thu, 05 December 2013 14:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
orausern
Messages: 826 Registered: December 2005
|
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 #602581 is a reply to message #602579] |
Thu, 05 December 2013 15:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
orausern
Messages: 826 Registered: December 2005
|
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 #602613 is a reply to message #602556] |
Thu, 05 December 2013 23:15 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 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 #602632 is a reply to message #602622] |
Fri, 06 December 2013 02:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Roachcoach
Messages: 1576 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 #602664 is a reply to message #602660] |
Fri, 06 December 2013 08:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
orausern
Messages: 826 Registered: December 2005
|
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 #602668 is a reply to message #602665] |
Fri, 06 December 2013 08:57 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
orausern
Messages: 826 Registered: December 2005
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
orausern
Messages: 826 Registered: December 2005
|
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 #602673 is a reply to message #602672] |
Fri, 06 December 2013 09:29 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
orausern
Messages: 826 Registered: December 2005
|
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 1975 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 #602683 is a reply to message #602680] |
Fri, 06 December 2013 11:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
orausern
Messages: 826 Registered: December 2005
|
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.
|
|
|
Goto Forum:
Current Time: Sat Sep 07 19:58:55 CDT 2024
|