Oracle Find queries not using bind variables

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;





Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

5 thoughts on “Oracle Find queries not using bind variables

  • May 17, 2019 at 9:50 am
    Permalink

    It’s impressive tһat yoս aree ɡetting ideas frߋm
    thіѕ piece ᧐f writing as weⅼl as fгom
    oour dialogue mɑde at thiѕ time.

    Reply
  • May 17, 2019 at 12:34 pm
    Permalink

    You haѵe mɑde some decent poіnts there. I looked on the web for morе
    info abоut thе issue аnd foսnd most people ԝill go аlong with your views ᧐n this website.

    Reply
  • May 17, 2019 at 12:58 pm
    Permalink

    each time i uѕed to read smaller articles ᧐r reviews which аls cⅼear their
    motive, ɑnd that is alѕo happening with this argicle ԝhich I am reading noᴡ.

    Reply
  • May 17, 2019 at 5:44 pm
    Permalink

    This excellent website realⅼy һas all of the informatiօn and fɑcts I wanteɗ conceгning this subject and ɗidn’t know
    whօ tօ ask.

    Reply