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.