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.

 

Oracle Find 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;   

 

Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.

https://ittutorial.org/oracle-database-performance-tuning-tutorial-12-what-is-the-automatic-sql-tuning-and-how-to-automated-sql-tuning/