Why does optimizer determine wrong cardinality when using MOD function? test included

[peter]

Hey guys,
I thought I'd try to get some feedback on something I'm looking at.
I have this 2 table query that I'm trying to figure out the bad
execution
plan that it's doing. On thing I noticed is that the cardinality
estimates from the
driving table are wrong..which then leads to the wrong join.

I've setup a little test to help illustrate the problem.
I've noticed that regularly when the mod function is used in the
predicate, it
throws off the optimizers's ability to estimate the number of rows
from the table...which then
causes execution plans to change..

Here is a little test I did, that I hope will help illustrate the
problem and hopefully you
can try it yourselves. I have not yet looked at the 10053 trace.

My environment:
============
- Oracle 10.1.0.4 on Solaris 64bit.
- pga_aggregate_target = 1.5 GB
- workarea_size_policy = auto
- db_cache_size = 2GB.
- shared_pool_size = 1GB.
- undo = AUTO.
- optimizer_features_enable=10.1.0
- optimizer_mode = 'ALL_ROWS'
- optimizer_index_caching=80
- optimizer_index_cost_adj=30
- db_file_multiblock_read_count =128

/* Here I setup a table of 10,000 rows where the product_id is going
to be
8000000 for 8000+ records and 4000000 for the rest. This is to
mimick my real production problem. */

SQL> CREATE TABLE TEST_T1 AS
SELECT ROWNUM+1000000 USER_ID,

DECODE(MOD(ROWNUM,8),0,'4000000000','8000000000) PRODUCT_ID,
0 CONFIRMED, sysdate CREATED
FROM ALL_TABLES where rownum <= 10000

/* here is what I have */
PRODUCT_ID| COUNT(*)
----------------------|----------
4000000000 | 1250
8000000000 | 8750


/* Then I create an index .. just to mimick my environment.
SQL> CREATE UNIQUE INDEX TEST_T1_PUC_U
ON TEST_T1(PRODUCT_ID,USER_ID,CONFIRMED);

/* Then I run dbms_stats. */
SQL> exec
dbms_stats.gather_table_stats('MYSCHEMA','TEST_T1',cascade=>TRUE);

TEST QUERIES
=============
SQL> select count(*) from TEST_T1 where product_id =8000000000
- oracle does a FTS and estimates the cardinality at about 5000 ...
I can live with that.

SQL> select count(*) from TEST_T1 where product_id = 8000000000 and
mod(user_id,2) = 0;
- oracle estimates the cardinality at 50-80 records depending on
the sample size from dbms_stats.
This estimate is very wrong because the query really returns 3750
records.

Can someone help me out here...maybe test in your similar environment.
You can probably see that if the optimizer estimates incorrectly, then
join orders can and probably be altered..

Why does the optimizer incorrectly guess the cardinality when using the
mod function?

--peter

[unknown]

"peter" <***@yahoo.com> a écrit dans le message de news: ***@g47g2000cwa.googlegroups.com...
| Hey guys,
| I thought I'd try to get some feedback on something I'm looking at.
| I have this 2 table query that I'm trying to figure out the bad
| execution
| plan that it's doing. On thing I noticed is that the cardinality
| estimates from the
| driving table are wrong..which then leads to the wrong join.
|
| I've setup a little test to help illustrate the problem.
| I've noticed that regularly when the mod function is used in the
| predicate, it
| throws off the optimizers's ability to estimate the number of rows
| from the table...which then
| causes execution plans to change..
|
| Here is a little test I did, that I hope will help illustrate the
| problem and hopefully you
| can try it yourselves. I have not yet looked at the 10053 trace.
|
| My environment:
| ============
| - Oracle 10.1.0.4 on Solaris 64bit.
| - pga_aggregate_target = 1.5 GB
| - workarea_size_policy = auto
| - db_cache_size = 2GB.
| - shared_pool_size = 1GB.
| - undo = AUTO.
| - optimizer_features_enable=10.1.0
| - optimizer_mode = 'ALL_ROWS'
| - optimizer_index_caching=80
| - optimizer_index_cost_adj=30
| - db_file_multiblock_read_count =128
|
| /* Here I setup a table of 10,000 rows where the product_id is going
| to be
| 8000000 for 8000+ records and 4000000 for the rest. This is to
| mimick my real production problem. */
|
| SQL> CREATE TABLE TEST_T1 AS
| SELECT ROWNUM+1000000 USER_ID,
|
| DECODE(MOD(ROWNUM,8),0,'4000000000','8000000000) PRODUCT_ID,
| 0 CONFIRMED, sysdate CREATED
| FROM ALL_TABLES where rownum <= 10000
|
| /* here is what I have */
| PRODUCT_ID| COUNT(*)
| ----------------------|----------
| 4000000000 | 1250
| 8000000000 | 8750
|
|
| /* Then I create an index .. just to mimick my environment.
| SQL> CREATE UNIQUE INDEX TEST_T1_PUC_U
| ON TEST_T1(PRODUCT_ID,USER_ID,CONFIRMED);
|
| /* Then I run dbms_stats. */
| SQL> exec
| dbms_stats.gather_table_stats('MYSCHEMA','TEST_T1',cascade=>TRUE);
|
| TEST QUERIES
| =============
| SQL> select count(*) from TEST_T1 where product_id =8000000000
| - oracle does a FTS and estimates the cardinality at about 5000 ...
| I can live with that.
|
| SQL> select count(*) from TEST_T1 where product_id = 8000000000 and
| mod(user_id,2) = 0;
| - oracle estimates the cardinality at 50-80 records depending on
| the sample size from dbms_stats.
| This estimate is very wrong because the query really returns 3750
| records.
|
| Can someone help me out here...maybe test in your similar environment.
| You can probably see that if the optimizer estimates incorrectly, then
| join orders can and probably be altered..
|
| Why does the optimizer incorrectly guess the cardinality when using the
| mod function?
|
| --peter
|

I don't in 10g but in 9i the default value for method_opt parameter
of dbms_stats.gather_table_stats is 'FOR ALL COLUMNS SIZE 1'
that is build an "histogram" with one bucket on all columns.
Try method_opt=>'FOR COLUMNS product_id SIZE 2, user_id SIZE 100' or the like.
You should have a better estimation.

Regards
Michel Cadot

[peter]

I believe in 10g the default is 'FOR ALL COLUMNS SIZE AUTO'.
In 10g, I believe oracle checks the sys.col_usage$ to see how a column
has been used to determine the proper # of buckets when "AUTO" option
is used for method_opt.

I tried my little example with 'FOR ALL COLUMNS SIZE 200' and it still
incorrectly estimates the
number of rows returned.

thanks for the feedback though!
--peter

[p***@gmail.com]

You have 10000 rows in the table.
You have two distinct values for product id (so density = 0.5 for
product_id)

For query
select * from test_t1 where product_id=800000

selectivity= 1/num_distinct (product_ids) = 1/2 = 0.5
computed cardinality = 10000*0.5=5000

For query
select * from test_t1 where product_id=80000 and mod(user_id,2)=0

for product_id=80000 selectivity=0.5 (as above)
for mod(user_id,2) Since you are applying a function to a database
column oracle assumes that this predicate will only return 1% of the
rows
therefore the selectivity is 0.01

So combined selectiviy = 0.5 * 0.01= 0.005
Cardinality = 10000*0.005=50

This is a general problem which happens when you apply a function to a
database column.

To solve this problem you should create a function based index on
mod(user_id,2) So oracle will use this index to calcualate the
effective selectivity of the column.

amit

[p***@gmail.com]

Post by p***@gmail.com
You have 10000 rows in the table.
You have two distinct values for product id (so density = 0.5 for
product_id)
For query
select * from test_t1 where product_id=800000
selectivity= 1/num_distinct (product_ids) = 1/2 = 0.5
computed cardinality = 10000*0.5=5000
For query
select * from test_t1 where product_id=80000 and mod(user_id,2)=0
for product_id=80000 selectivity=0.5 (as above)
for mod(user_id,2) Since you are applying a function to a database
column oracle assumes that this predicate will only return 1% of the
rows
therefore the selectivity is 0.01
So combined selectiviy = 0.5 * 0.01= 0.005
Cardinality = 10000*0.005=50
This is a general problem which happens when you apply a function to a
database column.
To solve this problem you should create a function based index on
mod(user_id,2) So oracle will use this index to calcualate the
effective selectivity of the column.
amit

SQL> drop index test_ind;

Index dropped.

SQL> create index test_ind on test_t1(mod(user_id,2));

Index created.

SQL> exec
dbms_stats.gather_table_stats('APPS','TEST_T1',cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain;
SQL> select * from test_t1 where product_id=80000 and
mod(user_id,2)=0;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=21 Card=5000 Bytes=1
35000)

1 0 TABLE ACCESS (FULL) OF 'TEST_T1' (Cost=21 Card=5000 Bytes=
135000)

1* select distinct_keys from user_indexes where
index_name='TEST_IND'
SQL> /

DISTINCT_KEYS
-------------
2
SINGLE TABLE ACCESS PATH
Column: PRODUCT_ID Col#: 2 Table: TEST_T1 Alias: TEST_T1
NDV: 2 NULLS: 0 DENS: 5.0000e-01
NO HISTOGRAM: #BKT: 1 #VAL: 2
NDV: 2 NULLS: 0 DENS: 5.0000e-01
NO HISTOGRAM: #BKT: 1 #VAL: 2
Column: SYS_NC0000 Col#: 5 Table: TEST_T1 Alias: TEST_T1
NDV: 2 NULLS: 0 DENS: 5.0000e-01 LO: 0 HI: 1
NO HISTOGRAM: #BKT: 1 #VAL: 2
TABLE: TEST_T1 ORIG CDN: 20000 ROUNDED CDN: 5000 CMPTD CDN:
5000
Access path: tsc Resc: 20 Resp: 17
Access path: index (equal)
Index: TEST_IND
TABLE: TEST_T1
RSC_CPU: 6376237 RSC_IO: 108
IX_SEL: 0.0000e+00 TB_SEL: 5.0000e-01
BEST_CST: 21.00 PATH: 2 Degree: 1



Notice Column SYS_NC0000 this is the virtual column i.e function based
index
DENSITY is 5.0000e-0.1 (i.e. 1/user_indexes.distinct_keys = 1/2=0.5)

amit

[peter]

Thanks for the explanation Amit.
Unfortunately, our customers don't always use mod(id_column,2) = 0 ,
sometimes they use different values and so a specific function based
index would probably not work.

thanks
-peter

[peter]

Just thought I'd add that to get around this 1% selectivity for queries
that use mod in the predicate, i've set dynamic_sampling=4. I don't
know of any other
way to get around this specially since the mod() function can be called
with whatever values
our customer wants. Since these types of queries usually take anywhere
between 2 -60 minutes having oracle due the dynamic sampling of the
blocks is not big hit.

I do have a similar question with how the cardinality is calculated on
index fast full scans, but
i'll post that in a new thread with the 10053 information.
--thanks again.

[p***@gmail.com]

optimizer_dynamic_sampling can results in wrong estimation sometimes
since its a small sample which may or may not reflect the whole data.
So you need to be carefull.