Re: V$SQL Executions

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Tue, 20 Sep 2011 13:09:42 +0100
Message-ID: <CABe10sbTHC=-KNd6dKGte1QTZn0Ltp=A4PNCZctiDgKBoTDwsA_at_mail.gmail.com>

grrr, trackpads causing me to hit send... The result was that I got a load of statements of the form

select <col list> from table where <conditions>

all of which had zero executions, but then there were also a lot of

select rowid,<col list> from table where <conditions>

which all had sensible execution counts (well they would have if they'd used bind variables ~ sigh )
The pattern shows up very well in a 10046 trace.

On Tue, Sep 20, 2011 at 1:06 PM, Niall Litchfield < niall.litchfield_at_gmail.com> wrote:

> Jay
>
> In addition to the other comments you have, I have in the past met an
> application that used a scrollable cursor for all resultsets. This turned
> out to result in a query with the rowid column prepended, the initial parse
> however was of the desired SQL
>
>
> On Tue, Sep 20, 2011 at 7:31 AM, Dunbar, Norman (Capgemini) <
> norman.dunbar.capgemini_at_environment-agency.gov.uk> wrote:
>
>> Morning Jay,
>>
>> >> ....So I have a bunch
>> >> of parsed, but
>> >> unexecuted queries. Seems like a waste of CPU time.
>>
>> Not to mention that fact that each and every parse, whether eventually
>> causing an execution or not, takes out a latch (like Highlanders, there
>> can be only one!) and causes a bottleneck.
>>
>> I suspect the developers and/or vendor be treated to the "big stick" and
>> educated in the errors of their ways.
>>
>> Good luck on that score though.
>>
>>
>> Cheers,
>> Norm.
>>
>> Norman Dunbar
>> Contract Senior Oracle DBA
>> Capgemini Database Build Team
>> Internal : 7 28 2051
>> External : 0113 231 2051
>>
>>
>> Information in this message may be confidential and may be legally
>> privileged. If you have received this message by mistake, please notify the
>> sender immediately, delete it and do not copy it to anyone else.
>>
>> We have checked this email and its attachments for viruses. But you should
>> still check any attachment before opening it.
>> We may have to make this message and any reply to it public if asked to
>> under the Freedom of Information Act, Data Protection Act or for litigation.
>> Email messages and attachments sent to or from any Environment Agency
>> address may also be accessed by someone other than the sender or recipient,
>> for business purposes.
>>
>> If we have sent you information and you wish to use it please read our
>> terms and conditions which you can get by calling us on 08708 506 506. Find
>> out more about the Environment Agency at www.environment-agency.gov.uk
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info


--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 20 2011 - 07:09:42 CDT