Optimizer Hint that Ignores Histogram and Column Stats

There was an Internal Forum and the question raised was “is there any optimizer hint that causes the optimizer to ignore histogram information ?” The question seemed to be interesting and thought of posting my reply to the question.

CARDINALITY hint is the one that directs the Optimizer to take the value of the Number of Rows to be processed by a Rowset or a Table. This hint is particularly usefull in Versions prior to 10g due to Optimizer Calculation mismatch with dependant columns. A quick demonstration below :

  1. I created a table vivek_test with 10000 rows
  2. Gathered Stats on this table, with Histogram on TEMPORARY column
  3. Then I executed the first query without any Hint and the Optimizer accurately calculated 53 rows for TEMPORARY=’Y’
  4. Then the second query with CARDINALITY hint and the Optimizer ignored histogram and column stats
create table vivek_test as
select * from all_objects where rownum<=10000;

exec dbms_stats.gather_table_stats(user,'VIVEK_TEST',null,null,null,'FOR ALL COLUMNS SIZE 1 FOR COLUMNS TEMPORARY SIZE 10');
SQL> select temporary, count(*) from vivek_test group by temporary;

T   COUNT(*)
- ----------
Y         53
N       9947

select /*+ NO_HINT */ owner, object_name
from vivek_test
where temporary='Y';

column sql_text for a50 wrap

select sql_id, sql_text, buffer_gets, executions, version_count
from	v$sqlarea
where	sql_text like 'select /*+ NO_HINT%';

SQL_ID        SQL_TEXT                                           BUFFER_GETS EXECUTIONS VERSION_COUNT
------------- -------------------------------------------------- ----------- ---------- -------------
6xhaxy9xb82zn select /*+ NO_HINT */ owner, object_name from vive         132          1             1
              k_test where temporary='Y'


Elapsed: 00:00:00.23

select plan_table_output from table(dbms_xplan.display_cursor('6xhaxy9xb82zn'));

SQL_ID  6xhaxy9xb82zn, child number 0
-------------------------------------
select /*+ NO_HINT */ owner, object_name from vivek_test where
temporary='Y'

Plan hash value: 3275957777

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |    85 (100)|          |
|*  1 |  TABLE ACCESS FULL| VIVEK_TEST |    53 |  1431 |    85   (2)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TEMPORARY"='Y')


19 rows selected.


select /*+ CARDINALITY(vivek_test,300) */ owner, object_name
from vivek_test
where temporary='Y';



select sql_id, sql_text, buffer_gets, executions, version_count
from	v$sqlarea
where	sql_text like 'select /*+ CARDINALITY(vivek_test,300%';

SQL_ID        SQL_TEXT                                           BUFFER_GETS EXECUTIONS VERSION_COUNT
------------- -------------------------------------------------- ----------- ---------- -------------
5xw7ckfhkz07p select /*+ CARDINALITY(vivek_test,300) */ owner, o         132          1             1
              bject_name from vivek_test where temporary='Y'


Elapsed: 00:00:00.02

select plan_table_output from table(dbms_xplan.display_cursor('5xw7ckfhkz07p'));

SQL_ID  5xw7ckfhkz07p, child number 0
-------------------------------------
select /*+ CARDINALITY(vivek_test,300) */ owner, object_name from
vivek_test where temporary='Y'

Plan hash value: 3275957777

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |    85 (100)|          |
|*  1 |  TABLE ACCESS FULL| VIVEK_TEST |   300 |  8100 |    85   (2)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TEMPORARY"='Y')


19 rows selected.

Advertisements

About Vivek Sharma
I am an Oracle Professional from Mumbai (India). I blog on the issues that I feel is Interesting for my readers. Some of these are my real life examples, which I hope, you would find interesting. Comments are always a welcome. The Technical Observations & Views here are my own and not necessarily those of Oracle or its affiliates. These are purely based on my understandings, learnings and resolutions of various customer issues.

One Response to Optimizer Hint that Ignores Histogram and Column Stats

  1. Hi Vivek,

    This was the crisp and short example, enough to clear the doubt.

    Regards,
    -Praveen.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s