Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.icl.net!newsfeed.fjserv.net!newsfeed.freenet.de!news.germany.com!postnews.google.com!g10g2000cwb.googlegroups.com!not-for-mail
From: "sjaffarhussain@gmail.com" <sjaffarhussain@gmail.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: How to avoid soft parses with ADO
Date: 6 Jun 2006 00:54:33 -0700
Organization: http://groups.google.com
Lines: 29
Message-ID: <1149580473.082810.120580@g10g2000cwb.googlegroups.com>
References: <1149526294.947034.47200@h76g2000cwa.googlegroups.com>
NNTP-Posting-Host: 86.51.0.133
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1149580478 3830 127.0.0.1 (6 Jun 2006 07:54:38 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 6 Jun 2006 07:54:38 +0000 (UTC)
In-Reply-To: <1149526294.947034.47200@h76g2000cwa.googlegroups.com>
User-Agent: G2/0.2
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.5) Gecko/20041107 Firefox/1.0,gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.1 C6200A (NetCache NetApp/5.5R5), 1.1 cache2
Complaints-To: groups-abuse@google.com
Injection-Info: g10g2000cwb.googlegroups.com; posting-host=86.51.0.133;
   posting-account=bu9i3g0AAABbYGf3ZUyDvbu5Ih8XwBC3
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:268593

Did you set session_ casched_cursor?
It seems to be library cache contention.
Do you also use synonyms?
If possible, read, Metalink Note : 1012049.6Tuning Library Cache Latch
Contention


Tzanko wrote:
> Hi,
> We have an application using Oracle DB through ADO - Oracle OLEDB
> Provider for Oracle. I am monitoring the performance of the applciation
> with heavy load, and I can see that our hard parse ratio is good (2% of
> queries are being hard-passed) (and we are using bind variables in
> queries that are execured most often), but too many SQL queries are
> soft-parsed - say 80, 90%. We are using ADO and we create the ADO
> connection object and recordset object as required and then release as
> soon as we've finished with them, following ADO recomendations. We
> believe the connection is pooled and therefore it is better to release
> as soon as possible rather than hold it for a duration of a transaction
> or between transactions.
> In this scenario, can anyone suggest what can be done to reduce the
> number of soft parses. Oracle recomend that 30% of soft-parses as a
> treshold. The rest 70% of the queries should not require parsing at
> all.
> 
> Thanks
> 
> Tzanko