Re: v$sql - executions vs loads vs invalidations

From: Sriram Kumar <k.sriramkumar_at_gmail.com>
Date: Mon, 7 May 2012 18:55:32 +0530
Message-ID: <CAEq4C0cN18x6xZSpFW-5shbihHQ=hCJ7X5Ch=FAkH0vyVP47wg_at_mail.gmail.com>

Hi,
What is the client access library(ODBC?). Have seen a similar issue where 1=0 was fired by the driver to get the structure of the table (equivalent of describe) and it was firing before every application's table based resultset queries. This was not happening for all types of drivers but i think it was the Microsoft Oracle Driver. The solution at that time was to evaulate the use of Oracle driver and the problem vanished.

Have not seen the cursor invalidation but the tables were accessed either by quering the table directly with 1=0 as in this case or querying ALL_TAB_COLUMNS view with the table name

Best Regards

Sriram Kumar

On Wed, May 2, 2012 at 1:03 PM, Gerry Miller <gerry_at_millerandbowman.com>wrote:

> Hi Norman,
> The 11g explanation would make sense here.
>
> The parse count is 1 for each query.
>
> You are right about the application not using bind variables, although the
> WHERE clause is always "WHERE 1=0".
>
> Thanks
>
> Gerry
>
> Norman Dunbar wrote: Hi Gerry, On 01/05/12 10:41, Gerry Miller wrote:
> LOADS:
> 2794 INVALIDATIONS: 2793 EXECUTIONS: 0 How I read this is that this
> statementwas loaded 2794 times, invalidated 2793 but never executed, but I
> think this opinion may stem from my ignorance. This is one of hundreds of
> such statements. The 11g reference manual, is a little helpful on the
> EXECUTIONS, it states that this is the number of times that the object was
> executed since it was *brought into the cache*. So, it looks like your
> statement hasn't been executed any time in the last 2794 loads.
> Invalidation
> occurs, for example, when an underlying table is changed. The application
> (sounds nasty!) doesn't do anything like creating and dropping tables in a
> SQL Server "temporary table" manner does it? I suspect that PARSE_CALLS
> will
> be about 2794 as well. Sounds to me like one of those applications that
> doesn't use binds and always parses. Welcome to my world! :-( Cheers, Norm.
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Mon May 07 2012 - 08:25:32 CDT