SCRIPT - to Set the 'SESSION_CACHED_CURSORS' and 'OPEN_CURSORS' Parameters(文档 ID 208857.1)

最新推荐文章于 2023-03-25 15:20:54 发布

mybluetiankong

最新推荐文章于 2023-03-25 15:20:54 发布

阅读量999

点赞数

分类专栏：网络资源_ORACLE_调优

网络资源_ORACLE_调优专栏收录该内容

109 篇文章

订阅专栏

本文提供了一种检查并调整Oracle数据库中'SESSION_CACHED_CURSORS'和'OPEN_CURSORS'参数的方法，以解决因游标缓存或打开游标限制导致的性能瓶颈。适用于Oracle Server Enterprise Edition 8i, 9i, 10g 和 11g 版本。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

 
  
  
      
 
 
      Abstract 
 SCRIPT -  to Tune the 'SESSION_CACHED_CURSORS' and  'OPEN_CURSORS' Parameters
 
 Product Name, Product Version Oracle Server Enterprise Edition  Versions 8i, 9i, 10g and 11g. 
  
 Platform  GENERIC
 Date Created  24-Nov-2002
 
 Instructions 
 Execution Environment:
     SQL*Plus

Access Privileges:
     Requires user access to v$statname, v$sesstat, v$parameter, 

Usage:
     sqlplus <user>/<pw> @[SCRIPTFILE] 

Instructions:


PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text 
editors, e-mail packages, and operating systems handle text formatting (spaces, 
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.The script will produce an output file named [outputfile].
This file can be viewed in a browser or uploaded for support analysis. 
 
 Description 
 To allow the DBA/Analysts to check whether the sessions cursor cache or
open cursors are really a constraint and then increase the parameter 
session_cached_cursors or open_cursors accordingly.

The Parameter SESSION_CACHED_CURSORS lets you specify the number of  cursors to cache within an individual session.
Repeated parse calls of the same SQL statement cause the session cursor for 
that statement to be moved into the session cursor cache. 

This script reports the setting of these parameters (at the system level) in the VALUE column and then 
the current maximum usage in any session as a percentage of these limits.

If either of the Usage column figures approaches 100%, then the corresponding 
parameter should normally be increased. 


          
           NOTE: Remember that the Parameter SESSION_CACHED_CURSORS can be changed dynamically 
at the session level. This means that if a session has increased its session cursor cache above the standard parameter setting, you could see
percentages greater than 100%.

If this happens you need to consider whether you want to use a higher value for all sessions or whether this 'special' session that is setting the higher
value is an exception.

          
 
 
 References 
 Note:1012049.6 Tuning Library Cache Latch Contention

 
 
 Script 
 select
  'session_cached_cursors'  parameter,
  lpad(value, 5)  value,
  decode(value, 0, '  n/a', to_char(100 * used / value, '990') || '%')  usage
from
  ( select
      max(s.value)  used
    from
      v$statname  n,
      v$sesstat  s
    where
      n.name = 'session cursor cache count' and
      s.statistic# = n.statistic#
  ),
  ( select
      value
    from
      v$parameter
    where
      name = 'session_cached_cursors'
  )
union all
select
  'open_cursors',
  lpad(value, 5),
  to_char(100 * used / value,  '990') || '%'
from
  ( select
      max(sum(s.value))  used
    from
      v$statname  n,
      v$sesstat  s
    where
      n.name in ('opened cursors current') and
      s.statistic# = n.statistic#
    group by
      s.sid
  ),
  ( select
      value
    from
      v$parameter
    where
      name = 'open_cursors'
  )
/ 
 Sample Output 
 PARAMETER               VALUE     USAGE 
 ----------------------  --------  --------- 
 session_cached_cursors         0        n/a 
 open_cursors                 300         1% 
  
 Disclaimer 
 EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,
PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS
ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.

ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
TIME WITHOUT NOTICE. 
 
 Limitation of Liability 
 IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,
DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.

SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.
ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU. 
 
 
 
       
 
 
  REFERENCES 
 NOTE:1012049.6 - Tuning Library Cache Latch Contention (8i and below)
 
 
 NOTE:214179.1 - SYSTEMS: Standalone Script Template
 
 
 NOTE:430473.1 - ORA-4031 Common Analysis/Diagnostic Scripts [Video]
 
 

   
            相关内容 
          
              产品 
            
       Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Personal Edition > RDBMS > Database Level Performance Issues (not SQL Tuning)
Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Enterprise Edition > RDBMS > Database Level Performance Issues (not SQL Tuning)
Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Standard Edition > Generic RDBMS > Database Level Performance Issues (not SQL Tuning)
 
              关键字 
            
     DATA_COLLECT;
     
     QTUNE