Re: How to avoid soft parses with ADO

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

and without the Oracle version number or the SQL statement you expect a useful response.

Like Dilbert ... I think the database should be mauve.

To be more precise ... what statistics gathered in what manner lead you to believe that the number of soft parses is inappropriate and in any way related to a performance issue?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org