From oracle-l-bounce@freelists.org Wed Oct 13 01:54:43 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i9D6shV13775 for ; Wed, 13 Oct 2004 01:54:43 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i9D6sgI13765 for ; Wed, 13 Oct 2004 01:54:42 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B06D672C747; Wed, 13 Oct 2004 02:00:48 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 19432-35; Wed, 13 Oct 2004 02:00:48 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 047B772C729; Wed, 13 Oct 2004 02:00:48 -0500 (EST) Message-ID: <416E2417.5020908@roughsea.com> Date: Wed, 13 Oct 2004 02:00:48 -0500 (EST) From: Stephane Faroult Organization: RoughSea Limited User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.2) Gecko/20040805 Netscape/7.2 X-Accept-Language: en, fr-fr, en-us MIME-Version: 1.0 To: jreyes@dazasoftware.com Cc: smishra_97@yahoo.com, "oracle-l@freelists.org" Subject: Re: Sincere Advice on Sql Plan - Thanks -Please Help in Understanding References: <20041012212023.19701.qmail@web51307.mail.yahoo.com> <416C592F.000001.02664@DAZA-MGEJCA5J7T> In-Reply-To: <416C592F.000001.02664@DAZA-MGEJCA5J7T> Content-type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-archive-position: 11003 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: sfaroult@roughsea.com Precedence: normal Reply-To: sfaroult@roughsea.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org X-Orig-Date: Thu, 14 Oct 2004 09:00:39 +0200 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, http://www.roughsea.com/papers/binding_en.html) 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 >SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES", >ROUND(SUM(RELOADS)*100/SUM(PINS),2) "% RATIO" >FROM V$LIBRARYCACHE >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 >OCP >-------Original Message------- > >From: Sanjay Mishra >Date: 10/12/04 17:22:53 >To: Juan Carlos Reyes Pacheco; mrothouse@gmail.com; oracle-l@freelists.org >Subject: Re: Sincere Advice on Sql Plan - Thanks -Please Help in >Understanding > >Juan > >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(gr.id) id >FROM >g_regions gr, l_translations lt WHERE sub_type = 2419 AND >lt.geo_id (+) = gr.id AND lt.l_type_gr_id (+) = 291 ORDER BY >description > > >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 > > > -- http://www.freelists.org/webpage/oracle-l