From oracle-l-bounce@freelists.org Tue Sep 28 02:37:45 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i8S7bjW14926 for ; Tue, 28 Sep 2004 02:37:45 -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 i8S7bAI14476 for ; Tue, 28 Sep 2004 02:37:11 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F226272C3F7; Tue, 28 Sep 2004 02:43:12 -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 06349-24; Tue, 28 Sep 2004 02:43:12 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5266C72C1C0; Tue, 28 Sep 2004 02:43:12 -0500 (EST) In-Reply-To: <200409280733.i8S7X4Ee013815@server.oraperf.com> Subject: RE: session_cached_cursors pros & cons To: anjo.kolk@oraperf.com Cc: oracle-l@freelists.org, oracle-l-bounce@freelists.org Message-ID: From: SJHussain@alfransi.com.sa Date: Tue, 28 Sep 2004 10:42:25 +0300 X-MIMETrack: Serialize by Router on iNotes/CEN/BSF(Release 6.5.1|January 21, 2004) at 09/28/2004 10:34:26 AM MIME-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit X-archive-position: 10245 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: SJHussain@alfransi.com.sa Precedence: normal Reply-To: SJHussain@alfransi.com.sa X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Anjo, You gave me the exact answer what I was looking for. When I put the proposal of setting 100 as value, my management raised a question that do we need to increase the shared_pool_size, does lead to any shared_pool fragmentation and this kind of questions and I was not uptot the mark to conveince them and now I can confidently tell them. I have analyzed my statsreport with oraperf analyzer and it also recommend to put 100. Do you think setting 100 will cause the problems? Best Regards, Syed Jaffar Hussain "Anjo Kolk" To Sent by: , oracle-l-bounce@f , reelists.org cc 09/28/2004 10:33 Subject AM RE: session_cached_cursors pros & cons Please respond to anjo.kolk@oraperf .com I haven't read all the replies but one thing to keep in mind with session_cached_cursors is that cursors that are normally closed are kept open and that memory is kept pinned. That means that a larger shared pool is needed to keep all the 'open' cursors in the shared pool. It is also responsible for a larger fragmentation of the shared pool. In fact caching SQL statements increases performance, but could cause a fragmentation and other shared pool problems. So make sure that your shared pool is big enough. Anjo. -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Juan Carlos Reyes Pacheco Sent: Monday, September 27, 2004 7:01 PM To: SJHussain@alfransi.com.sa; oracle-l@freelists.org Subject: Re: session_cached_cursors pros & cons >From my paper, I pasted the whole topic, hope be useful. 1.A Soft Parses: session_cached_cursor parameter Two kinds of parse calls exist, hard and soft. "hard parse" occurs when the SQL or PL/SQL statement is not found in the shared SQL area (shared pool), so a complete parsing is required (data dictionary object descriptions user's privileges, generate the execution plan, etc). The most expensive kind of parsing, and should be minimized for repeated execution. The "soft parse", is performed when the statement is already in the shared pool (user must be authenticated again, all name translations must be done once more, syntaxis and security chekings), but the session lost the "link" to the shared portion, because the cursor was closed, so that the private portion must be rebuilt and linked to its shared portion again. To eliminate soft parsing in COBOL, C, or other 3GL applications, the precompiler option HOLD_CURSOR=YES should be used. Other options, such as RELEASE_CURSOR and MAXOPENCURSORS, can be used in conjunction with this to achieve optimal results. For non-3GL programs (when you do not have the same degree of control over cursors) such as Oracle Forms and other third-party tools, the cursors will automatically be closed when a new form is called and switching from one form to another closes all session cursors associated with the first form. So if you subsequently return to the caller, at least a soft parse will be performed for each cursor. In this case, you should enable this parameter that will keep a copy of the user's cursors even though they are closed. 1.A.i.a SESSION_CACHED_CURSOR parameter Lets you specify the number of session cursors to cache. After the first "soft parse", subsequent "soft parse" calls will find the cursor in the cache and do not need to reopen the cursor. To get placed in the session cache the same statement has to be parsed 3 times within the same cursor. Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed. Session cached cursors is a great help in reducing latching that takes place due to excessive soft parsing (where a program parses, executes, closes a statement over and over) Steven Adams says, http://www.ixora.com.au/scripts/library.htm The session cursor cache is an important facility for reducing load on the library cache. In our opinion, the session_cached_cursors parameter should always be set to at least 2. However, a larger value is normally beneficial. Tom comment, (if steve adams said it, it is more then likely "true". as they said -- a larger is normally beneficial. I am partial (opinion, no true science here) to 100.) 1.A.i.b Important . Be aware that this is done at the expense of increased memory allocation for every session in the this will increase UGA memory which is in the PGA in dedicated server mode and in the SGA in shared server mode. . An application to run optimally, is necessary to analyze how parsing works 1.A.ii Syntax You can set this parameter with ALTER SESSION SET SESSION_CACHED_CURSOR = value ALTER SYSTEM SET SESSION_CACHED_CURSOR = value [DEFERRED] In parameter file set SESSION_CACHED_CURSOR = (number), default value 0 1.A.iii Evaluating the accuracy of the value Set to 50 the parameter SESSION_CACHED_CURSOR and evaluate if this is enough 1.A.iii.a Stat: session cursor cache count Total number of cursors cached. This statistic is incremented only if SESSION_CACHED_CURSORS > 0. This statistic is the most useful in V$SESSTAT. If the value for this statistic in V$SESSTAT is close to the setting of the SESSION_CACHED_CURSORS parameter, the value of the parameter should be increased. 1.A.iii.a.1 Query to evaluate To evaluate this parameter you can save the information of every user every time he logs off in a table, after that you can analyze it in different ways here is one example: CREATE TABLE Stat_Session_Historic ( UUSER VARCHAR2(100), DDATE DATE, SstatisticName VARCHAR2 (200), VVALUE NUMBER (6) ) / CREATE OR REPLACE TRIGGER TGR_LOGOFF_STATS BEFORE LOGOFF ON DATABASE INSERT INTO Stat_Session_Historic SELECT USER, SYSDATE, 'session cursor cache count', VALUE FROM V$SESSTAT C WHERE C.statistic# = (select STATISTIC# from v$statname where name = session cursor cache count') AND C.SID = (SELECT SID FROM V$SESSION WHERE USER#= UID ) / -- And the select will be the first 10 of an average of their statics in a period of time for user SELECT UUSER, AVGVAL FROM ( SELECT UUSER, AVG( VVALUE ) AVGVAL FROM Stat_Session_Historic where TRUNC(DDATE) = TRUNC(SYSDATE) - only for today GROUP BY UUSER ORDER BY 2 DESC ) WHERE ROWNUM < 10 -- First 10 cases UUSER AVGVAL -------------- ADM 5 SAF 1 Then you compare this value with the value you had set to the parameter, and then decide to increase or decrease the parameter. 1.A.iii.b Parse vs. Execute in statistics call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.80 1.72 0 0 0 0 Execute 3 0.00 0.00 0 0 0 0 Fetch 3 0.02 0.05 2 665 0 14 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.82 1.77 2 665 0 14 Misses in library cache during parse: 0 In this statistics we can see there is too soft parsing, parse = execute. In a well tuned application parse = 1, execute = some number greater than or equal to 1 The best way to speed something up is to NOT do it. Hence, don't parse 3 times, just parse 1 time. if you find yourself parsing the same statement more then 3 times and you really cannot fix the code, session cached cursors can be of some assistance. if you do not, it will not help nor hurt. 1.A.iii.c Note.- . The V$SESSION_CURSOR_CACHE view is not a measure of the effectiveness of the SESSION_CACHED_CURSORS initialization parameter. . Soft parse % ratio, tells you if you have too many , hard parses, but they cannot be fixed with session cached cursors. 1.A.iv Examples and techniques 1.A.iv.a Demonstrating the effect of changes in the parameter session_cached_cursors >From Tom Kyte: ops$tkyte@ORA817.US.ORACLE.COM> alter session set session_cached_cursors =0; Session altered. no cached cursors...... ops$tkyte@ORA817.US.ORACLE.COM> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) like '%cursor ca%' 5 / NAME VALUE ------------------------------ ---------- session cursor cache hits 5 thats from logging in session cursor cache count 0 ops$tkyte@ORA817.US.ORACLE.COM> declare 2 l_cnt number; 3 begin 4 for i in 1 .. 100 5 loop 6 execute immediate 'select count(*) from dual d1' into l_cnt; 7 end loop; 8 end; 9 / PL/SQL procedure successfully completed. ops$tkyte@ORA817.US.ORACLE.COM> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) like '%cursor ca%' 5 / NAME VALUE ------------------------------ ---------- session cursor cache hits 5 no change session cursor cache count 0 now, lets cache upto 10 cursors ops$tkyte@ORA817.US.ORACLE.COM> alter session set session_cached_cursors=10; Session altered. ops$tkyte@ORA817.US.ORACLE.COM> declare 2 l_cnt number; 3 begin 4 for i in 1 .. 100 5 loop 6 execute immediate 'select count(*) from dual d2' into l_cnt; 7 end loop; 8 end; 9 / PL/SQL procedure successfully completed. ops$tkyte@ORA817.US.ORACLE.COM> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) like '%cursor ca%' 5 / NAME VALUE ------------------------------ ---------- session cursor cache hits 104 99 more hits! session cursor cache count 4 ops$tkyte@ORA817.US.ORACLE.COM> Our first query in that loop didn't get a hit (we hadn't cached it yet), the subsequent 99 did. It has to go through the mechanics of a pretending to do a softparse (making sure things haven't been invalidated and such) but the code path is much smaller. 1.A.iv.b Another script to evaluate this parameter >From Steven Adams http://www.ixora.com.au/scripts/sql/session_cursor_cache.sql Tom comment: the script looked reasonable to me. -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l Visit us on www.alfransi.com.sa ====== Banque Saudi Fransi - Privacy Notice ====== This message is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any use of this information by persons or entities other than the intended recipient is prohibited. If you have received this in error, please contact the sender and delete the material from your computer. Any opinions and other information contained in this message that do not relate to the official business of Banque Saudi Fransi shall be understood as neither given or endorsed by it. Although precautions have been taken to ensure no viruses are present in this email, BSF cannot accept responsibility for any loss or damage arising from the use of this email or attachments. ====== Banque Saudi Fransi - Privacy Notice ====== -- http://www.freelists.org/webpage/oracle-l