Re: Re: sql run time
From: <l.flatz_at_bluewin.ch>
Date: Fri, 06 Nov 2015 07:48:06 +0000
Message-ID: <31198745.5763.1446796086799.JavaMail.webmail_at_bluewin.ch>
Execution plans can change over time and switch from efficient to inefficient. Often an misestimation of cardinality is the cause. Quite often such misestmatuion was always there and geting worse with data growth. At some point it breaks. I rarly deal with past plan though. I want to know what is going wrong NOW and how I can stop it. On SE, runtime stats would be the measure of choice. Easy and fast to come by. alter session set statistics_level=all;
run your statement
select * from table (dbms_xplan.display_cursor(null,null,'RUNSTATS_LAST')); If strange waits are involved a trace could be helpfull too. ----Ursprüngliche Nachricht----
Von : dombrooks_at_hotmail.com
Datum : 06/11/2015 - 08:14 (GMT)
An : iggy_fernandez_at_hotmail.com, veeeraman_at_gmail.com, oracle-l_at_freelists.org Betreff : Re: sql run time
_at_font-face{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} It depends (doesn't it always?).
Potentially if your physical IO time was slower, your SQL could be running for longer therefore you might need to do more work to get a read consistent picture (more buffer gets) which then makes it even slower, etc - circular. On 6 November 2015, at 01:43, Iggy Fernandez <iggy_fernandez_at_hotmail.com> wrote: 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 plan
Changes in data
Changes 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_at_gmail.com
To: oracle-l_at_freelists.org
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.
Date: Fri, 06 Nov 2015 07:48:06 +0000
Message-ID: <31198745.5763.1446796086799.JavaMail.webmail_at_bluewin.ch>
Execution plans can change over time and switch from efficient to inefficient. Often an misestimation of cardinality is the cause. Quite often such misestmatuion was always there and geting worse with data growth. At some point it breaks. I rarly deal with past plan though. I want to know what is going wrong NOW and how I can stop it. On SE, runtime stats would be the measure of choice. Easy and fast to come by. alter session set statistics_level=all;
run your statement
select * from table (dbms_xplan.display_cursor(null,null,'RUNSTATS_LAST')); If strange waits are involved a trace could be helpfull too. ----Ursprüngliche Nachricht----
Von : dombrooks_at_hotmail.com
Datum : 06/11/2015 - 08:14 (GMT)
An : iggy_fernandez_at_hotmail.com, veeeraman_at_gmail.com, oracle-l_at_freelists.org Betreff : Re: sql run time
_at_font-face{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} It depends (doesn't it always?).
Potentially if your physical IO time was slower, your SQL could be running for longer therefore you might need to do more work to get a read consistent picture (more buffer gets) which then makes it even slower, etc - circular. On 6 November 2015, at 01:43, Iggy Fernandez <iggy_fernandez_at_hotmail.com> wrote: 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 plan
Changes in data
Changes 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_at_gmail.com
To: oracle-l_at_freelists.org
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
-- -- http://www.freelists.org/webpage/oracle-lReceived on Fri Nov 06 2015 - 08:48:06 CET