How far should bind variables go in Oracle?
Use a bind variable anywhere that you might actually change the value that is passed in.

It probably doesn't make sense, for example, to use a bind variable in the EXISTS clause in your third example because it seems extremely unlikely that anyone would ever want to pass in a different constant for the EXISTS clause.

In your second example, it probably does make sense to use a bind variable rather than a hard-coded status assuming that the application(s) are likely to want to run this query for different id and status values.  On the other hand, if the code would only ever pass in a status of 2 for this query, it probably doesn't make sense to use a bind variable.  Assuming that status is not evenly distributed, you'd be better off giving the optimizer the information that you're always going to use a status of 2 rather than trying to rely on bind variable peeking to give the optimizer that information.

If the value is never going to change-- i.e. if the status in your second query will always be 2-- you can execute the query as many times as you'd like and it will only appear once in v$sql or v$sqlarea.  In my case, I'll execute the query 10 times

SQL&gt; ed
Wrote file afiedt.buf

  1* select * from v$sql where sql_text like '%jc_bind_test%'
SQL&gt; select /* jc_literal_test */ count(*)
  2    from emp
  3   where ename = 'KING';

  COUNT(*)
----------
         1

SQL&gt; /

  COUNT(*)
----------
         1

SQL&gt; /

  COUNT(*)
----------
         1

SQL&gt; /

  COUNT(*)
----------
         1

SQL&gt; /

  COUNT(*)
----------
         1

SQL&gt; /

  COUNT(*)
----------
         1

SQL&gt; /

  COUNT(*)
----------
         1

SQL&gt; /

  COUNT(*)
----------
         1

SQL&gt; /

  COUNT(*)
----------
         1

SQL&gt; /

  COUNT(*)
----------
         1


And from v$sql or v$sqlarea, you'll see only one row for the query that has the hard-coded literal.  That row will show that the query was executed 10 times

SQL&gt; ed
Wrote file afiedt.buf

  1* select sql_text, executions, loaded_versions, parse_calls from v$sqlarea where sql_text like '%jc_literal_test%'
SQL&gt; /

SQL_TEXT                                 EXECUTIONS LOADED_VERSIONS PARSE_CALLS
---------------------------------------- ---------- --------------- -----------
select * from v$sql where sql_text like           1               1           1
'%jc_literal_test%'

select sql_text, executions, loaded_vers          5               1           5
ions, parse_calls from v$sql where sql_t
ext like '%jc_literal_test%'

select sql_text, executions, loaded_vers          1               1           1
ions, parse_calls from v$sqlarea where s
ql_text like '%jc_literal_test%'

select /* jc_literal_test */ count(*)            10               1          10
from emp  where ename = 'KING'