Quick search of queries without binds

Reading the Oracle Performance tuning guide put in my head an idea about how to find queries which are used literals instead of binds. We can use fact that such queries usually share their plans.

SQL> set pages 9999
SQL> set lines 140
SQL> column sql_text format a80
SQL> select plan_hash_value, sql_text, sql_id, executions
  2    from v$sql x
  3   where plan_hash_value in (select plan_hash_value
  4                               from v$sql
  5                              where plan_hash_value != 0
  6                                and upper(trim(sql_text)) not like '%DUAL'
  7                                and parsing_schema_name not like 'SYS%'
  8                              group by plan_hash_value
  9                             having count(distinct sql_id) > 1)
 10   order by x.plan_hash_value
 11  /

PLAN_HASH_VALUE SQL_TEXT                                                                         SQL_ID        EXECUTIONS
--------------- -------------------------------------------------------------------------------- ------------- ----------
        5300452 SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_P 1uzhrfn574t12          1
                RIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND (UPPER(USER) LIKE USERID)


        5300452 SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE   (UPPER('SQL*Plus') LIKE UPPE c4pc3jhzjcmc7          1
                R(PRODUCT)) AND   ((UPPER(USER) LIKE USERID) OR (USERID = 'PUBLIC')) AND   (UPPE
                R(ATTRIBUTE) = 'ROLES')

     1023777010 select count(1) from scott.test x where x.object_id=5651                         2ck17fz7g1wz0          1
     1023777010 select count(1) from scott.test x where x.object_id=132                          fpkm8ganm036t          1
     1950795681 select count(1) from scott.test x where x.object_type='TABLE'                    25gm9vc4cgh6r          1
     1950795681 select count(1) from scott.test x where x.object_type='VIEW'                     dmpf0r95suww4          1

6 rows selected.

Of course, you can receive either false negative or false positive results with this method. On the one hand, for example, there are a lot of cases where queries can share plans, i. e. they have common "from" and "where" clauses but different "select" clauses. On the other hand, data distribution patterns can influence CBO`s decisions and lead it to create different plans.