Internal PL/SQL Developer SELECT statement not using bind variables.

[Francois Sicard]

Version: 8.0.1.1498

We're seeing hundreds of SELECT statements in our Oracle shared pool similar to the following, with a variety of values for MY_SCHEMA and MY_TABLE:

SELECT col.*,
com.comments
FROM sys.dba_tab_columns col,
sys.dba_col_comments com
WHERE col.owner = 'MY_SCHEMA'
AND col.table_name = 'MY_TABLE'
AND com.owner(+) = 'MY_SCHEMA'
AND com.table_name(+) = 'MY_TABLE'
AND com.column_name(+) = col.column_name
ORDER BY col.column_id

This statement is presumably being constructed and executed by PL/SQL Developer when a user right-mouse clicks on "View" for a table. A nice enhancement that would reduce hard parses and shared pool usage on the instance would be to use bind variables in place of the literals.

 

[Marco Kalter]

This is one statement where bind variables were replaced by literals to help the optimizer take the right approach. Bind variables would lead to performance problems in many cases.