Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Are the stats up to date? Is there a reason why you can't add small_table to the where (along with the other bits in the IN clause) and just do a straight join with the main table? –  Phil Oct 2 '12 at 21:40
    
@Phil The stats are fine, but thanks. And, there isn't any reason to not join.. I actually did try that, thats what I meant with "I have tried to isolate the "small query" in a with clause, tried to use an join instead a sub query", sorry if it was ambiguous. –  filippo Oct 2 '12 at 21:45
1  
Can you post explain plans comparing the good plan vs. the bad one please? –  Chris Saxon Oct 3 '12 at 8:00
    
Your statistics (if they are up-to-date) must confuse the CBO, have you tried the DYNAMIC_SAMPLING hint? –  Vincent Malgrat Oct 3 '12 at 9:09
    
@ChrisSaxon Sure, There you have it. I renamed the tables to make sense with the topic, hope I haven't messed it up. –  filippo Oct 3 '12 at 13:50

3 Answers 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.

share|improve this answer
    
I found another post that suggested it's better to use "where exists" instead of "where <field> in". I can't say I've tried that before, though I've seen it used once or twice. –  Brian Efting Oct 3 '12 at 4:31
2  
The RULE hint has been deprecated since Oracle 10gR1, so shouldn't be considered as a first solution - particularly in 11gR2. Re-writing the query is a better place to start. –  Chris Saxon Oct 3 '12 at 8:00
    
@ChrisSaxon, while I can appreciate that the best option is to fix the query, sometimes time doesn't allow us the luxury while our users suffer through poor performing DML. We must remain open to the options available to us. CBO is prone to problems in situations such as this...ignoring RBO when it's still available to us is a mistake. If it gets the query performing better in the short term then we have the luxury to take our time testing fixes. –  Brian Efting Oct 5 '12 at 3:09
    
@ChrisSaxon, If I could I'd +1 your answer I would. That's a great explanation of what's going on. I didn't really think of it that way. Thanks. –  Brian Efting Oct 7 '12 at 3:13
    
@Brain - thanks. It's possible that a RULE hint will be required to "fix" this query, but if the tuning pack is licensed then an SQL profile is a far superior solution. If it's not I'd still try other hints first, but as I say we really need much more info about the data to know which will work. –  Chris Saxon Oct 7 '12 at 11:24

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.