Oracle Find queries not using bind variables Object Detail

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;



  

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.

Leave a Reply

Your email address will not be published. Required fields are marked *