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

"peter" <p_msantos_at_yahoo.com> a écrit dans le message de news: 1132176421.079150.306860_at_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 Received on Wed Nov 16 2005 - 15:45:43 CST