SQL Profile ignores hints

Sometimes , hints can be very powerful to let optimizer choose proper execution plans. This lets application execute with good performance. But sometimes given hints to sqls can be harmful choosing wrong execution plans.At this situations , development can take care of this sqls and by removing hints problems can be solved. But if development process can not be done immediately . DBA can solve this issues by telling optimizer to ignore hints.

alter session set "_optimizer_ignore_hints"=true 

command ignores all hints during session. This can solve your problem but also can be trouble.At sql level ignoring hints can be done by import_sql_profile procedure in dbms_sqltune package.

For sample below sql is used . Firstly given hint for sql tell optimizer not to use index. But for some reason hint losed its validity and index should be used for query.

SQL> explain plan for select /*+no_index(s) */ count(1) from xdba_dcyear s where cust_name like 'ER%';

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3491557762
--------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |     1 |   252 |     2   (0)| 0
|   1 |  SORT AGGREGATE    |                    |     1 |   252 |            |
|*  2 |   TABLE ACCESS FULL| XDBA_DCYEAR |    20 |  5040 |     2   (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("CUST_NAME" LIKE 'ER%')
Note
-----
   - dynamic sampling used for this statement (level=2)
18 rows selected
Executed in 0.076 seconds

Now dbms_sqltune.import_sql_profile procudure is used with attribute "IGNORE_OPTIM_EMBEDDED_HINTS".

begin
 dbms_sqltune.import_sql_profile(
 name => 'SQLPROF1',
 category => 'DEFAULT',
 sql_text => 'select /*+no_index(s) */ count(1) from xdba_dim_cust_year s where cust_name like ''ER%''',

 profile => sqlprof_attr('IGNORE_OPTIM_EMBEDDED_HINTS'));
end;
/


SQL> explain plan for select /*+no_index(s) */ count(1) from xdba_dcyear s where cust_name like 'ER%';

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2253536563
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   252 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |   252 |            |          |
|*  2 |   INDEX RANGE SCAN| X1   |    20 |  5040 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CUST_NAME" LIKE 'ER%')
       filter("CUST_NAME" LIKE 'ER%')
Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL profile "SQLPROF1" used for this statement
20 rows selected
Executed in 0.126 seconds

As a result no_index hint is ignored and index is being used according to explain plan. Also usage of "SQLPROF1" profile can be observe from explain plan notes

In order to remove sql profile following procedure is used.

begin
 dbms_sqltune.import.drop_sql_profile('SQLPROF1');
end;
/

Note : dbms_sqltune package is under Oracle Tuning Pack license.This must be considered.

Estimate size of index using explain plan

Today i have learned cool way to define after index rebuilt what will the index size be ? This method , estimating index size is based on explain plan . Not only rebuild process , additionally creation of index is  involved .

Basically ,

1. create table tbl1 as select * from dba_objects;
2. insert into tbl1 select * from tbl1 -- 2 times
3. commit;
3. call dbms_stats.gather_table_stats('USER','TBL1');
4. explain plan for create index user.tbl_idx1 on tbl1(object_name);
5. commit;
6. select * from table (dbms_xplan.display);

-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |          |   414K|  9707K|   441   (3)| 00:00:03 |
|   1 |  INDEX BUILD NON UNIQUE| TBL_IDX1 |       |       |            |          |
|   2 |   SORT CREATE INDEX    |          |   414K|  9707K|            |          |
|   3 |    TABLE ACCESS FULL   | TBL1     |   414K|  9707K|   334   (3)| 00:00:02 |
-----------------------------------------------------------------------------------

Note
-----
   - estimated index size: 16M bytes


6. create index user.tbl_idx1 on tbl1(object_name);
7. select bytes/1024 from dba_segments where segment_name='TBL_IDX1'

Size : 16384K

As a result , actual size and estimate size are so close . 

CArlos Sierra has a great article and script about this method which can be applied to whole system , schema ,table or single index . Object create DDL is used with explain plan statement to detect approximate size like this,

declare
  v_ddl clob;
begin
  select replace(dbms_metadata.get_ddl(object_type => 'INDEX',name => 'TBL_IDX1'),chr(10),' ') into v_ddl   FROM DUAL;

  execute immediate 'explain plan for '||v_ddl;
  commit;
end ;

After script execution,below sql gives segment size and estimated size

select object_name,object_owner,info estimatedSize,ds.BYTES segmentSize
  from (select p.object_name,p.object_owner,extractvalue(value(d), '/info/@type') type,
               extractvalue(value(d), '/info') info
          from plan_table p,
               table(xmlsequence(extract(xmltype(p.other_xml), '/*/info'))) d
         where p.other_xml is not null)X,dba_segments ds
 where type = 'index_size'
   and ds.owner=X.object_owner
   and ds.segment_name=X.object_name

OBJECT_NAME    OBJECT_OWNER    ESTIMATEDSIZE    SEGMENTSIZE
TBL_IDX1           USER                     16777216    16777216

This post was based on below blog posts

http://carlos-sierra.net/2014/07/18/free-script-to-very-quickly-and-cheaply-estimate-the-size-of-an-index-if-it-were-to-be-rebuilt/

http://richardfoote.wordpress.com/2014/04/24/estimate-index-size-with-explain-plan-i-cant-explain/