Hi,
Using bind variable in the queries is very crucial for performance tuning.
If you don’t use bind variables in the SQL queries then Oracle will know similar sqls differently like following.
select * from customer where id=63; select * from customer where id=34;
Above queries are almost same, just id variable is different. But Oracle optimizer will evaluate these SQLs like different SQL.
If you use bind variable instead of literal like following then Oracle will evaluate as same SQL and will use same execution plan and won’t be hard parse in Oracle.
variable SYS_B_0 number; exec :SYS_B_0:= 63 select * from customer where id= :SYS_B_0;
You can find out all queries which are not using bind variables with following script. You can see more queries with changing row nums of script.
Select *
from (
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 distinct ki.inst_id,
t.sql_id,
ki.sql_text,
t.plsql_procedure,
ki.user_name,
sum(ki.copies) copies,
sum(ki.executions) executions,
sum(ki.sharable_mem_mb) sharable_mem_mb
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#) sira,
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 t.sira = 1
group by ki.inst_id,
t.sql_id,
ki.sql_text,
t.plsql_procedure,
ki.user_name
order by sum(ki.executions) desc
)
where rownum < 51;
