Oracle

August 21, 2010

Index hint in SQL Profile being ignored

Filed under: Uncategorized — srivenu @ 10:10 am

Recently i faced an issue with one sql profile. An index hint in it was being ignored by the CBO. This is just the opposite of the one of the situations mentioned here by Kerry Osborne.

I had a sql like this

UPDATE 	XYZ
SET 	STATUS_FLAG='2'
WHERE 	created_date>=sysdate-1
and 	STATUS_FLAG=1
AND 	ATTRIBUTE9='ABCDE'
and 	CLASS!='CLASS1'
and 	rownum <=500
/

The CBO was using this plan

----------------------------------------------------------------------
| Id  | Operation                            | Name                  |
----------------------------------------------------------------------
|   1 |  UPDATE                              | XYZ                   |
|*  2 |   COUNT STOPKEY                      |                       |
|   3 |    PARTITION RANGE ITERATOR          |                       |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| XYZ                   |
|*  5 |      INDEX RANGE SCAN                | CREATED_DATE          |
----------------------------------------------------------------------

I wanted it to use the composite index (IDX_XYZ_SF_CD_A9) on STATUS_FLAG, CREATED_DATE & ATTRIBUTE9 colums. For legacy reasons they were using CURSOR_SHARING=SIMILAR, so i wanted to fix it using a SQL Profile. (Just a note of caution – Its not the recommended way, thanks for these notes by Jonathan Lewis and Tom Kyte).
I created a SQL Profile like this (thanks to Kerry Osborne for his scripts)

IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘10.2.0.4’)
FIRST_ROWS
OUTLINE_LEAF(@”UPD$1″)
INDEX_RS_ASC(@”UPD$1″ “XYZ”@”UPD$1” “IDX_XYZ_SF_CD_A9″)

But the plan generated by the CBO does not change and  it continues to use the single column index on CREATED_DATE. I scratched my head and verified that i properly flushed the shared pool and that v$sql is showing my profile usage .

Finally i tried modifying the index hint – removed the index name and specified all the column names like this

IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘10.2.0.4’)
FIRST_ROWS
OUTLINE_LEAF(@”UPD$1″)
INDEX_RS_ASC(@”UPD$1” “XYZ”@”UPD$1” (“XYZ”.”STATUS_FLAG” “XYZ”.”CREATED_DATE” “XYZ”.”ATTRIBUTE9″))

Voila! it started working!

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: