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

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

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.

StackExchange Database Administrators Q#25309, answer score: 2