Reviewing statements with hints, using PL/Scope

In my previous post, I showed how you can add a fake hint to tag the origin of a duplicated statement and as a side-effect make it unique. What if you regret and can’t remember where it was. Or you want to review old hinted statements after an upgrade to a newer version of the database/optimizer.

Personally, I prefer to have as few statementswith hints as possible and just provide the adequate statistics so the optimizer can do it’s job.

Anyway – with PL/Scope, finding hinted statements is really easy, provided you have all your statements in the database (where they should be for maximum maintainability).

One of the information bits it collects is if there’s a hint in the statement. This is tagged in the binary has_hint column in all_statements/user_statements:

SELECT s.type,
       s.object_name,
       s.object_type,
       s.line,
       s.full_text
FROM   user_statements s
WHERE  has_hint = 'YES';

TYPE    OBJECT_NAME  OBJECT_TYPE   LINE  FULL_TEXT
------  -----------  ------------  ----  ----------------------------------------------------------
SELECT  P_EMPLOYEES  PACKAGE BODY   156  SELECT /*+Heavy*/ * FROM EMPLOYEES WHERE EMPLOYEE_ID = :B1

Note – it doesn’t care if it’s a valid hint – only if the statement contains one or more hint-tags at all.

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s