Oracle Performance: Find Sql that are "Invisible"
The "Mystery" Sql
It's usually easy to find long-running sql. For one thing, they show up in the first section of a typical AWR report. The cumulative runtime for all executions of problem sql are clearly shown in the "Elapsed Time" section. They will often show up in other sections as well, such as the Disk I/O or Buffer Gets sections. So normally, this is a trivial step.
I can never find those guys!
But what if the application doesn't use bind variables? In that case, there isn't any "cumultive runtime," since each sql has a different sql_id. In these cases, the sql will not show up anywhere in the AWR report. How can you spot these?
How to Spot Invisible Sql
It turns out that these evasive sql are easy to find. Here's the key: The sql statement uses the same amount of memory, even though the literal values change. Knowing this, you can do a simple query that identifies statements that have the same form.
Here's how I do it:
Select Persistent_Mem, Count(*) From V$Sql Group By Persistent_Mem Having Count(*) > 1000 Order by 2;
PERSISTENT_MEM COUNT(*)
-------------- ----------
8728 1251
1712 1621
7176 81406
Notice that in the above example, there is one form (Persistent_Mem = 7176) that is apparently run a huge amount of times. Now, run another query to identify the culprit (and cut-off at just a first few rows):
Select Sql_Text From V$Sql Where Persistent_mem = 7176 and RowNum < 5;
Invisible No More!
Once you have identified the culprit(s), you can easily "roll-up" the statistics for all sql having that Persistent_Mem. You will never be fooled again!
