How To Detect Queries That Do Not Use Bind Variable

dbtut

5 years ago

For the database, the use of bind variables in queries is quite important. The hard parsing process in the database will increase when bind variable is not in use. Soft parsing will be reduced. This will have a negative impact on performance.

With the following query, you can see the queries that do not use the bind variable and the number of copies of these queries.

SELECT SUBSTR(sql_text, 1, 100) sql,
count(*) copies
FROM v$sqlarea
GROUP BY substr(sql_text, 1, 100)
HAVING count(*) > 3
ORDER BY count(*);

SELECT SUBSTR(sql_text, 1, 100) sql,

count(*) copies

FROM v$sqlarea

GROUP BY substr(sql_text, 1, 100)

HAVING count(*) > 3

ORDER BY count(*);