find match between 2 sql queries

Question

I have two queries from v$sqlarea. For example

query 1: select * from employee emp where emp.eid = 5

query 2: select * from employee v where v.eid = 15

Both are exactly the same in structure. but they will be compiled separately each time..

I need to match such queries that vary only by alias names or bind variables.

The inbuilt function utl_match.jaro_winkler_similarity() in oracle provides a pretty good string match algorithm. But sql perspective match is not provided. Is there any other solution?

Answer 1

Hi pradeep,

there is a script on Asktom that will find exactly this kind of statements (statements NOT using binds).

The output of that last query will show you statements that are identical in the shared pool after all numbers and character string constants have been removed. These statements -- and more importantly their counts -- are the potential bottlenecks. In addition to causing the contention, they will be HUGE cpu consumers.

Answer 2

Oracle actually does this internally, to support cursor_sharing = similar. I am not aware that they expose this functionality anywhere.

Answer 3

The alias names is a tricky one. You could look for SQLs with the same PLAN_HASH_VALUE as a starting point.