RE: sql run time

  • From: Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx>
  • To: "veeeraman@xxxxxxxxx" <veeeraman@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 5 Nov 2015 17:42:13 -0800

I don't see how changes in db file sequential read time can impact buffer gets
Here are some ways buffer gets can change
Changes in execution planChanges in dataChanges in bind variables (in other
words, there is skew in resource consumption depending on the bind
variables)Effects of the Oracle consistency schema, block cleanout, etc.
If you don't have AWR, hopefully you are using Statspack. The sprepsql script
will give you insight into the execution history.
Iggy

Date: Thu, 5 Nov 2015 19:30:57 -0600
Subject: sql run time
From: veeeraman@xxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx

Hi, 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
--


Other related posts: