sql run time

We have a 11.2 SE db that is experiencing big spikes in CPU. Upon further investigation I see one SQL that has become resource intensive in terms of buffer gets and CPU time. The SQL was running fine till few days ago even though it was being executed several tens of 1000s of times in the half hr reporting period. Per the old reports, this sql has been executed that many times always. But something changed few days ago that this SQL shot up in terms of # of buffer gets per the report. I tried checking the v$sql for different versions today, but I see only one version of the SQL (remembered that from Jonathan's advise last time). Is there a way with SE to see the equivalent of awrddrpt?

also, I see the dbfile seq read double in value suddenly in the newer reports to about 26ms. Even though that is not a great number, the previous average was about 12/13ms. I am thinking the increase in the DBF SEQ RD time could not cause the increased buffer gets. Am I right in my assumption.

Thanks,
Ram

--

--

http://www.freelists.org/webpage/oracle-l Received on Fri Nov 06 2015 - 02:30:57 CET