Why does the query cost change so much and how to prevent it

Question

The query causing me the issue is too big, but the piece that seems to be the core is quite simple, I will try to go with that:

The query has an structure like:

SELECT
    ... a lot of stuff ...
WHERE
    ... lots of complex clauses ...
    AND my_id in ( select my_id from small_table where .. something simple ..)

And that has a cardinality of a few billions, bytes read around 12gb and explodes my temp.

However, if I execute the select my_id from small_table, which yields (always) 7 records, take these records and change the query to:

SELECT
    ... a lot of stuff ...
WHERE
    ... lots of complex clauses ...
    AND my_id in ( 1, 2, 3, 4, 5, 6, 7 )

(I mean, values hardcoded)

Cost, cardinality, and bytes read drop dramatically and the query executes in a few minutes.

Now, I have tried to isolate the "small query" in a with clause, tried to use an join instead a sub query and nothing... the result is always the same.

Why is it this way and how could I possibly prevent it from happening?

Maybe worth mentioning that in both cases (fast and slow) the costly part of the query is a FTS on one of the big tables used in the join.

Also, I am using Oracle 11gR2

[EDIT] These are the explain plans of the two example executions

The bad one. Notice I didn't use in ( ), but rather a simple join adding small_table to the from clause.

Plan
SELECT STATEMENT  ALL_ROWSCost: 5,736,441                                   
    22 HASH JOIN RIGHT SEMI  Cost: 5,736,441  Bytes: 52,324,480  Cardinality: 158,080                               
        11 VIEW VIEW VW_NSO_1 Cost: 20  Bytes: 13  Cardinality: 1                           
            10 NESTED LOOPS                         
                8 NESTED LOOPS  Cost: 18  Bytes: 91  Cardinality: 1                     
                    6 NESTED LOOPS  Cost: 4  Bytes: 70  Cardinality: 1                  
                        4 TABLE ACCESS BY INDEX ROWID TABLE MYUSER.SMALL_TABLE Cost: 1  Bytes: 35  Cardinality: 1           
                            3 INDEX UNIQUE SCAN INDEX (UNIQUE) MYUSER.PK_SMALL_TABLE Cost: 0  Cardinality: 1        
                                2 TABLE ACCESS BY INDEX ROWID TABLE MYUSER.SMALL_TABLE Cost: 2  Bytes: 27  Cardinality: 1   
                                    1 INDEX RANGE SCAN INDEX MYUSER.IDX_SMALL_TABLE_1ATUAL Cost: 1  Cardinality: 6  
                        5 TABLE ACCESS FULL TABLE MYUSER.SMALL_TABLE Cost: 3  Bytes: 35  Cardinality: 1             
                    7 INDEX RANGE SCAN INDEX (UNIQUE) MYUSER.PK_MEDIUM_TABLE Cost: 1  Cardinality: 53               
                9 TABLE ACCESS BY INDEX ROWID TABLE MYUSER.MEDIUM_TABLE Cost: 14  Bytes: 420  Cardinality: 20                   
        21 HASH JOIN  Cost: 5,736,193  Bytes: 15,281,925,342  Cardinality: 48,056,369                           
            19 NESTED LOOPS                         
                17 NESTED LOOPS  Cost: 951,151  Bytes: 500,185,440  Cardinality: 1,736,755                      
                    15 NESTED LOOPS  Cost: 3  Bytes: 792  Cardinality: 22               
                        13 TABLE ACCESS BY INDEX ROWID TABLE MYUSER.SMALL_TABLE Cost: 2  Bytes: 27  Cardinality: 1              
                            12 INDEX RANGE SCAN INDEX MYUSER.IDX_SMALL_TABLE_1ATUAL Cost: 1  Cardinality: 6         
                        14 INDEX RANGE SCAN INDEX (UNIQUE) MYUSER.PK_MEDIUM_TABLE Cost: 1  Bytes: 477  Cardinality: 53              
                    16 INDEX RANGE SCAN INDEX MYUSER.IDX_HUGE_TABLE_1 Cost: 18,849  Cardinality: 1,322,763                  
                18 TABLE ACCESS BY INDEX ROWID TABLE MYUSER.HUGE_TABLE Cost: 413,818  Bytes: 19,620,720  Cardinality: 77,860                    
            20 TABLE ACCESS FULL TABLE MYUSER.HUGE_TABLE Cost: 3,958,129  Bytes: 12,063,594,150  Cardinality: 402,119,805  

The good one. All I did here was taking the result of select my_id from small_table where .. something simple .. (seven records), and added an my_id in ( 1, 2, 3, 4, 5, 6, 7) to the end of the big query. Same as described:

Plan
SELECT STATEMENT  ALL_ROWSCost: 4,558,125                                               
    18 HASH JOIN  Cost: 4,558,125  Bytes: 36,100,625  Cardinality: 122,375                                              
        16 NESTED LOOPS                                         
            14 NESTED LOOPS  Cost: 413,809  Bytes: 5,271,671  Cardinality: 122,597                                      
                12 VIEW VIEW VW_NSO_1 Cost: 20  Bytes: 13  Cardinality: 1                               
                    11 HASH UNIQUE  Bytes: 91  Cardinality: 1                           
                        10 NESTED LOOPS                         
                            8 NESTED LOOPS  Cost: 18  Bytes: 91  Cardinality: 1                     
                                6 NESTED LOOPS  Cost: 4  Bytes: 70  Cardinality: 1                  
                                    4 TABLE ACCESS BY INDEX ROWID TABLE MYUSER.SMALL_TABLE Cost: 1  Bytes: 35  Cardinality: 1           
                                        3 INDEX UNIQUE SCAN INDEX (UNIQUE) MYUSER.PK_SMALL_TABLE Cost: 0  Cardinality: 1        
                                            2 TABLE ACCESS BY INDEX ROWID TABLE MYUSER.SMALL_TABLE Cost: 2  Bytes: 27  Cardinality: 1   
                                                1 INDEX RANGE SCAN INDEX MYUSER.IDX_SMALL_TABLE_1ATUAL Cost: 1  Cardinality: 5  
                                    5 TABLE ACCESS FULL TABLE MYUSER.SMALL_TABLE Cost: 3  Bytes: 35  Cardinality: 1             
                                7 INDEX RANGE SCAN INDEX (UNIQUE) MYUSER.PK_MEDIUM_TABLE Cost: 1  Cardinality: 53               
                            9 TABLE ACCESS BY INDEX ROWID TABLE MYUSER.MEDIUM_TABLE Cost: 14  Bytes: 420  Cardinality: 20                   
                13 INDEX RANGE SCAN INDEX MYUSER.IDX_HUGE_TABLE_1 Cost: 18,849  Cardinality: 1,322,763                                  
            15 TABLE ACCESS BY INDEX ROWID TABLE MYUSER.HUGE_TABLE Cost: 413,788  Bytes: 3,677,910  Cardinality: 122,597                                    
        17 TABLE ACCESS FULL TABLE MYUSER.HUGE_TABLE Cost: 3,962,804  Bytes: 3,655,562,652  Cardinality: 14,506,201  

hope it helps.

Answer 1

Most likely your tables have up-to-date statistics but sometimes the optimizer is baffled because it oversimplifies the cardinality estimation.

This seems to be a good candidate for dynamic sampling. In its default value (2 in 10g and 11g), dynamic sampling will only be used if one of the table has no statistics. In your case you would need to change its value to be able to let the optimizer collect statistics to build a better plan.

I suggest you use the DYNAMIC_SAMPLING hint that will let you modify the optimizer behaviour for a single query. I tested with a subquery and you need to use one of the following syntax:

• the full hint directly on the top of the query, this will sample all tables, which will definitely work but may take too much time.

  SELECT /*+ DYNAMIC_SAMPLING (10) */ FROM ...
  

• the full hint on the subquery:

  SELECT
    ... a lot of stuff ...
  WHERE
    ... lots of complex clauses ...
    AND my_id in ( select /*+ DYNAMIC_SAMPLING (10) */ my_id 
                     from small_table 
                    where .. something simple ..)
  

• the hint with a query block name:

  SELECT /*+ DYNAMIC_SAMPLING (@my_block 10) */
    ... a lot of stuff ...
  WHERE
    ... lots of complex clauses ...
    AND my_id in ( select /*+ QB_NAME(my_block) */ my_id 
                     from small_table 
                    where .. something simple ..)
  

The second and third option should produce the same result: sampling only on one table.

Answer 2

The major difference between the good and bad plans is that the bad plan is expected to return most/all the data from the large table, whereas the good plans is expecting to return around 5% of it.

The reason the hardcoded values can make this difference is because the optimizer knows explicitly which values you're looking for. When you join to another table it knows how many rows to expect, but not necessarily the values for those rows.

In the following example, there's a small table with RW values 1-9 where VAL = 'N' and a RW = 10 and VAL = 'Y'. The large table links to the small table, but the vast majority of the rows have the RW value 10. If we want to find the uncommon RW values (1-9) in the large table, then we can join to the small table where these rows have VAL = 'N', or just explicity list the numbers 1-9. In either case we're expecting just 9 rows to be returned from the large table.

In the joined case, while the optimizer recognises that the query against SMALL_TAB returns 9 rows, it's unable to determine exactly how these link to the values in LARGE_TAB. Consequently it does a full table scan against this expecting 10,000 rows, rather than the 9 row index range scan:

create table small_tab as
select rownum rw, decode(rownum, 10, 'Y', 'N') val
from   dual
connect by level <= 10;

create table large_tab as
select case when rownum >= 10 then 10 else rownum end rw, --9990 rows have same value
        dbms_random.string('x', 20) filler
from   dual
connect by level <= 10000;

create index sm_i on small_tab(rw);
create index lg_i on large_tab(rw);

--calc stats, SIZE 10 to ensure we get histograms on the large table
exec dbms_stats.gather_table_stats(user, 'small_tab', cascade => true);
exec dbms_stats.gather_table_stats(user, 'large_tab', method_opt => 'FOR ALL COLUMNS SIZE 10', cascade => true);

explain plan for 
SELECT * FROM large_tab l
where  l.rw in (select s.rw from small_tab s where val = 'N');

SELECT * FROM table(dbms_xplan.display(NULL, NULL, 'BASIC +ROWS'));
-- the optimizer doesn't know how the SMALL_TAB.RW values returned by the
-- subquery match back to LARGE_TAB.RW, so assumes we need to full scan the whole table
--------------------------------------------------
| Id  | Operation            | Name      | Rows  |
--------------------------------------------------
|   0 | SELECT STATEMENT     |           |  5000 |
|   1 |  HASH JOIN RIGHT SEMI|           |  5000 |
|   2 |   TABLE ACCESS FULL  | SMALL_TAB |     5 |
|   3 |   TABLE ACCESS FULL  | LARGE_TAB | 10000 |
--------------------------------------------------

explain plan for 
SELECT * FROM large_tab
where  rw IN (1, 2, 3, 4, 5, 6, 7, 8, 9);

SELECT * FROM table(dbms_xplan.display(NULL, NULL, 'BASIC +ROWS'));
--because we've explicity asked for values, these can be directly compared 
--against the stats information and the optimizer knows we should expect 9 rows.
---------------------------------------------------------- 
| Id  | Operation                    | Name      | Rows  | 
---------------------------------------------------------- 
|   0 | SELECT STATEMENT             |           |     9 | 
|   1 |  INLIST ITERATOR             |           |       | 
|   2 |   TABLE ACCESS BY INDEX ROWID| LARGE_TAB |     9 | 
|   3 |    INDEX RANGE SCAN          | LG_I      |     9 | 
---------------------------------------------------------- 

To help you fix this, if the query is static (doesn't change) and you've licensed the tuning pack, I'd advise looking at the SQL tuning advisor. With this you can create an SQL profile locking the query to a good plan. Hopefully the advisor will find the good plan for you automatically, but if not you may have to create it manually. Some links:

Using the tuning advisor package: http://www.oracle-base.com/articles/10g/automatic-sql-tuning-10g.php Manually creating SQL profiles: http://kerryosborne.oracle-guy.com/2010/07/sqlt-coe_xfr_sql_profilesql/

If you're not licensed for the tuning pack, then I think you're down to restructing your query, adding indexes and fiddling with hints. Finding out which changes will benefit will be a bit of trial-and-error; it's difficult to say exactly what you should do without access to the actual datasets.

Answer 3

Where I work we are having all sorts of problems with CBO in our biggest database. We're on 11gR1 but I imagine the optimizer is very similar in R2, though our other databases on that version don't seem to have noticable issues.

Even though I am loathe to do/suggest this (we've done it a few times out of necessity) have you tried adding the 'RULE' hint to your query to see how it performs?

You would do something like: select /*+ rule */ ... lots and lots of stuff;

There is probably still another way to write that query so that CBO won't have a problem with it, though, and that would be preferable to using the hint. At least if you try the hint you could see if it is the optimizer at fault and look have a point from which to ask another question.

There is a patch available for R1 that fixes some optimizer issues and a similar patch could be available for R2, though I haven't checked. My metalink login info is on my machine at work.