Hi,
Using bind variable in the queries is very crucial for performance tuning.
You can look at the following article to find out queries not using bind variables.
You can find out all queries which are not using bind variables object detail with following script.
You will find object detail of queries which is not using bind variables.
With subs as
(SELECT /*+ materialize */
m.sql_id, k.*, m.SQL_TEXT, m.SQL_FULLTEXT
FROM (SELECT inst_id,
parsing_schema_name AS user_name,
module,
plan_hash_value,
COUNT(0) copies,
SUM(executions) executions,
SUM(round(sharable_mem / (1024 * 1024), 2)) sharable_mem_mb
FROM gv$sqlarea
WHERE executions < 5
AND kept_versions = 0
GROUP BY inst_id, parsing_schema_name, module, plan_hash_value
HAVING COUNT(0) > 10
ORDER BY COUNT(0) DESC) k
LEFT JOIN gv$sqlarea m
ON k.plan_hash_value = m.plan_hash_value
WHERE k.plan_hash_value > 0)
select *
from (select sql_id,
program_id,
program_line#,
action,
module,
service,
parsing_schema_name,
round(buffer_gets / decode(executions, 0, 1, executions)) buffer_per_Exec,
row_number() over(partition by sql_id order by program_id desc, program_line#) lines,
decode(program_id,
0,
null,
owner || '.' || object_name || '(' || program_line# || ')') plsql_procedure
from gv$sql a, dba_objects b
where a.program_id = b.object_id(+)) t,
subs ki
where ki.sql_id = t.sql_id
and lines = 1;