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.

Agenda for Sangam 2010, Hyderabad

Got couple of mails from my customer and many of these were interested on a detailed explanation on the optmization done at their site. While some of these will be covered during the presentation, some of these may miss out due to the time slot alloted to me. I have prioritized these optimizations and the interesting ones have been on my ppt’s.

In case, any one wants me to speak on a specific optimization feature, do update the blog (comment) and I would try to relate it to my Real Life Optimizations and present it with good examples.

One Interesting topic to be covered will be “Relation between Column Ordering of a Table and CPU time required to process a column”. This will also feature in an Oracle Connect Magazine to be circulated by AIOUG.

Next, I will also answer a common question raised by a Customer to Performance Tuning Specialist “Till Yesterday, the performance of the system was perfectly fine, How come the performance degraded all of a sudden ?”

Upgrade from 10g to 11g

For past few days, I was busy with critical upgrades of some mission critical databases from 10g to 11g. Apart from some minor issues, no performance issues have been reported post-upgrade. This should be a major relief for most of the customers planning to upgrade their system from 10g to 11g. Optimizer has not changed much between these two versions and therefore, unlike 9i to 10g, the upgrade from 10g to 11g should be smooth.

While comparing the Execution Plan of the critical queries during UAT testing, only one change observed is the introduction of Nested Loop Batching and this may change the plan hash value between the two versions. No performance degradation is observed with the introduction of NL Batching.

Post-Upgrade, it is now time to implement 11g new features and I may write on these, as and when, I have something interesting to share on these.

%d bloggers like this: