Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Sincere Advice on Sql Plan - Thanks -Please Help in Understanding

Re: Sincere Advice on Sql Plan - Thanks -Please Help in Understanding

From: Sanjay Mishra <>
Date: Wed, 13 Oct 2004 05:26:43 -0700 (PDT)
Message-ID: <>

Thanks Stephane for your advice. I even checked the Shared pool referenced by Juan and it look fine in the limit. I am having Cursor_sharing to Exact and so as per your advice and Manoj, I will try to use FORCE. THis is Oracle 9i Release1 DB and so will see if there is any bug in this release for the parameter. I had even reply to Manoj email where I have placed on of the trace output for the query having lots of Parsing even using Binds variable. I really need advice from lots of Tuning expert on thhis list so as to really learn them completely.  

Thanks for all your help and time.
Stephane Faroult <> wrote: Methinks that shared memory reloads are a direct consequence of having zillions of hard-coded queries executing only once. There is no need to add anything to the shared pool before the other question is fixed (by the way, you could add whatever you want without changing much to any ratio).
Therefore, the really first thing to do is : 1) Change the code and use bind variables (the original poster may be interested by a short paper on the topic, 2) If this option is not possible (source code unavailable, politics ...) plan B is to use cursor_sharing = force in the init.ora file, with a number of warnings about side effects (bugs in a number of versions, and possible problem with the use of histograms and star schemas).

Totally forget about the shared pool as long as statistics show excessive parsing. You just have one statement chasing the other.

Also, from the execution plan (snipped with a lot of stuff to avoid having quotes being more than my own input) I suspect that indexing l_translations on (l_type_gr_id, geo_id) might help. Generally speaking I like to keep indexing light but the name of the table leads me to think that it isn't heavily updated.

HTH S Faroult

Juan Carlos Reyes Pacheco wrote:

>Hi Sanjay if you see 2419 neither 291 are not binded,
>sendme your full trace file.
>The first question, can't you try to bind that variables or you have
>histograms in that columns.
> Please showme your library cache hit ratio to see if your shared pool is
>not too small
>The following query
>Shows you the amount of reloads needed from the library because there were
>not enough memory if the ratio is more that 1% you can think in add the
>shared pool size.
>Juan Carlos Reyes Pacheco
>-------Original Message-------
>From: Sanjay Mishra
>Date: 10/12/04 17:22:53
>To: Juan Carlos Reyes Pacheco;;
>Subject: Re: Sincere Advice on Sql Plan - Thanks -Please Help in
>There are several query like this and I am giving one of the recent trace.
>First is the TKPROF output and second is Trace Data
>SELECT DECODE(lt.t_value, NULL, gr.r_value,
>lt.t_value) description, TO_CHAR( id
>g_regions gr, l_translations lt WHERE sub_type = 2419 AND
>lt.geo_id (+) = AND lt.l_type_gr_id (+) = 291 ORDER BY
>call count cpu elapsed disk query current rows
>------- ------ -------- ---------- ---------- ---------- ----------
>Parse 10 0.01 0.00 0 0 0 0
>Execute 5 0.00 0.00 0 0 0 0
>Fetch 60 0.29 0.31 0 1240 0 1155
>------- ------ -------- ---------- ---------- ---------- ----------
>total 75 0.30 0.32 0 1240 0 1155


Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.

Received on Wed Oct 13 2004 - 07:22:15 CDT

Original text of this message