Journals of Oracle Experience
Oracle Database Upgrade Path Reference List [ID 730365.1]
Master Note For Oracle Database Upgrades and Migrations [ID 1152016.1]
Patch Set Updates – One-off Patch Conflict Resolution [ID 1061295.1]
Patch Set Updates for Oracle Products [ID 854428.1]
Oracle Recommended Patches — Oracle Database [ID 756671.1]
Sometimes you may need to map ASM Disks to its physical devices.
If they are based on ASMLib you will see their ASM name, ie: ORCL:VOL1 when querying v$asm_disk
When running oracleasm querydisk VOL1 you will get in addition the major – minor numbers, that can be used to match the physical device, ie:
[root@orcldb2 ~]# ls -l /dev | grep 8, | grep 97
brw-rw—- 1 root disk 8, 81 Nov 4 13:02 sdg1
This script can do the job for a group of ASM Disks:
The output looks like this:
If you are using multi-path, you will need an additional step to map the physical device to the multi-path device, for instance if using EMC Powerpath if you want to map sdf1
[root@orclp ~]# powermt display dev=all
…
…
Pseudo name=emcpowerg
Symmetrix ID=000290101698
Logical device ID=0214
state=alive; policy=SymmOpt; priority=0; queued-IOs=0
==============================================================================
—————- Host ————— – Stor – — I/O Path – — Stats —
### HW Path I/O Paths Interf. Mode State Q-IOs Errors
==============================================================================
1 qla2xxx sdf FA 7bB active alive 0 0
2 qla2xxx sdq FA 10bB active alive 0 0
…
…
The last step is to check the partition assigned to the emcpower device, ie:
[root@orclp ~]# ls -l /dev/emcpowerg*
brw——- 1 root root 120, 96 Oct 29 20:41 /dev/emcpowerg
brw——- 1 root root 120, 97 Nov 15 13:08 /dev/emcpowerg1
The trace files in $BDUMP continue to show warning messages like “WARNING:Could not lower the asynch I/O limit to 165 for SQL direct I/O. It is set to -1”.
After searching possible causes of the problem in metalink, it turns out that’s related to a bug (confirmed to affect 10.2.0.5 on general platforms)
9772888: WARNING:COULD NOT LOWER THE ASYNCH I/O LIMIT TO 160 FOR SQL DIRECT I/O.
Fix:
Applying patch 9772888
or
Applying patchset 10.2.0.5.2
Reference:
Oracle Server – Enterprise Edition – Version: 10.2.0.5 and later [Release: 10.2 and later ]
Information in this document applies to any platform.
Warning below appears after upgrading to 10.2.0.5
*** SESSION ID:(538.48) 2010-07-15 08:41:30.649
WARNING:Could not lower the asynch I/O limit to 224 for SQL direct I/O. It is set to -1
WARNING:Could not lower the asynch I/O limit to 192 for SQL direct I/O. It is set to -1
WARNING:Could not lower the asynch I/O limit to 224 for SQL direct I/O. It is set to -1
WARNING:Could not lower the asynch I/O limit to 192 for SQL direct I/O. It is set to -1
WARNING:Could not lower the asynch I/O limit to 160 for SQL direct I/O. It is set to -1
Recently upgraded to 10.2.0.5.
This seems due to bug 9772888 10.2.0.5 RDBMS 10.2.0.5 BUFFER CACHE PRODID-5 PORTID-23 Abstract: WARNING:COULD NOT LOWER THE ASYNCH I/O LIMIT TO 160 FOR SQL DIRECT I/O. IT IS SE
Same behavior appears and on the same platform 10.2.0.5
1. Upgrade to Oracle 11.2 and install on top patchset 11.2.0.2.0 (or higher version patchset) where issue is fixed
OR
2. Install one-off patch on top of 10.2.0.5.0 if available to your platform.
| FAQ: ORA-4030 [Video] [ID 399497.1] | |||||
|
|
|||||
| Modified 24-FEB-2011 Type FAQ Status PUBLISHED | |||||
In this Document
Purpose
Common Bugs
Questions and Answers
What is an ORA-4030?
What is difference between 4030 and 4031?
What are the contents of Program Global Area memory?
Why do I see processes growing larger than the PGA_AGGREGATE_TARGET?
Can you control the size of a process?
Can you limit the size of a process?
What information needs to be gathered to diagnose?
Why does my code give ORA-4030 when run through listener connection, but not local connection?
What to look at in RDA?
What kernel or shell limits need to be checked?
How to monitor pga usage from within the database?
How to monitor memory usage from the OS on unix/linux?
How to monitor memory usage from the OS on MSwindows?
Why do we still get ORA-4030 or ORA-12500 on MSwindows 32 bit database after adding more ram?
How to create a heapdump?
What heapdump level to gather?
See high amount of ‘perm’ allocations. When to set 10235 event?
Configurations leading to excessive memory usage problems
References
Oracle Server – Enterprise Edition – Version: 8.1.5.0 to 10.2.0.4 – Release: 8.1.5 to 10.2
Information in this document applies to any platform.
This article is intended to
Notes:
Backport possibilities are only to indicate technical backport or patchset exception may be possible.
Actual availability of backports/PSEs are subject to backport policies as per
Note 209768.1 Pub Database, FMW, and OCS Software Error Correction Support Policy
| Bug | Reported | Fixed | Notes | Details |
|---|---|---|---|---|
| Bug 3130972 | Versions < 10.1.0.2 | 9.2.0.6/10.1.0.2.0 | Backports possible | The realfree allocator on Unix systems imposes a cap at 1Gb of memory per process. This fix relaxes that limit as in some cases it is desirable to allow a process to use over 1Gb of private memory. If the limit is reached an ORA-4030 occurs even though the system may have plenty of free memory available and can map that memory into the process address space. (The realfree allocator is used on Unix systems where PGA_AGGREGATE_TARGET is in use) Workaround: Configure the system to allow less than 1Gb of memory per process to avoid ORA-4030 errors. |
| Bug 3565920 | Versions < 10.1 | 9.2.0.8 and higher | Backports no longer | If the shared pool is resized then subsequeunt queries against views based on X$KSMSP, such as V$SHARED_POOL_RESERVED, result in a memory leak in the cursor work heap which then fails with ORA-4030. |
| Bug 4475206 | Versions < 10.2.0.4 | 10.2.0.4/11.1.0.6 | Backports possible | The PGA memory consumed becomes significantly higher than the value of the parameter pga_aggregate_target if a query has a very long chain of hash-join operations. This chain must be right-deep, ie. the build is performed on a base table and the probe is produced by an hash-join sub-tree. This is not really a memory leak but excess memory use compared to what should be used. |
| Bug 4625938 | Versions < 10.2 | 10.2 | 10.1.x | A memory leak involving ‘peihstdep’ and ‘PEIDEF’ can occur when TABLE functions are used and JDBC connection pooling is enabled. Workaround: Disable connection pooling. |
| Bug 5118748 | Versions < 10.2.0.3 | 10.2.0.3/11.1.0.6 | Backports possible | ORA 4030 or a memory leak can occur when using a lot of collections in PLSQL. Heapdumps of the “koh-kghu call ” heap include multiple chunks of type “pmucp2upkl korfp |
| Bug 5220562 | Versions < 10.2.0.4 | 10.2.0.4/11.1.0.6 | Backports possible | An insert select DML session’s process size may increases / ORA-4030 (with sort sub heap chunks) when there is concurrent DDL on the partitioned tables / objects involved in the DML. Workaround: Avoid concurrent execution of the DML and DDL, if possible. |
| Bug 5389854 | Versions < 10.2.0.4 | 10.2.0.4/11.1.0.6 | Backports possible | Execution of a procedure using bulk insert with save exceptions will consume large amounts of PGA memory during execution. If run with extremely large number of rows or for a long period of time this can lead to ORA-4030. The memory shows up on the “callheap,DARWIN” subheap as “koh-kghu call” memory |
| Bug 5391505 | Versions < 10.2.0.4 | 10.2.0.4/11.1.0.6 | Backports possible | PGA memory may keep on increasing during query parsing and can reach a large amount (sometimes even over 1G) when OR expansion occurs. Ultimately ORA-4030 may be encountered as memory runs out. The memory shows as “perm” space in the “kxs-heap-c” subheap. Workaround: alter session set “_no_or_expansion” = true |
| Bug 5464834 | Versions < 10.2.0.4 | 10.2.0.4/11.1.0.6 | Backports possible | ORA-4030 (kxs-heap-c,temporary memory) can occur when using EXPDP |
| Bug 5866410 | Versions < 11 | 11.1.0.6 | Backports possible | Bulk insert in PLSQL can consume a large amount of PGA memory which can lead to ORA-4030 errors. A heapdump will show lot of free memory in the free lists which is not used but instead fresh allocations are made. Workaround: Chunk the FORALL loop. Do a hybrid of FOR & FORALL so that the bulk_rowcount arrays doesnt grow abnormally large |
| Bug 5947623 | Versions >= 10.2.0.1 but < 11.1.0.7 | 10.2.0.4/11.1.0.7 | Backport possible | it is possible for a query to allocate too much memory executing a hash join over large volumes of data with few distinct join key values. The impact on 64-bit systems is greater. This is not really a memory leak as the fix only makes the query to spill to disk earlier. Workaround: set “_pga_max_size” |
| Bug 6011182 | Versions >= 10.2.0.1 but < 10.2.0.4 | 10.2.0.4 | Backport possible | High elapsed time and high memory consumption during parse can occur for queries with large numbers of query blocks. If you see high elapsed times and/or memory consumption during parse for a query, and the query has a large number of query blocks (eg many views, subqueries or UNION ALL branches) you may be hitting this bug. For error ORA-04030 The path of the leak is: top call heap -> call heap -> typecheck largest memory allocations w/comments: “logdef: qcopCre”, “kkoFroAnn: qksf”, “frodef: qksfroC” |
| Bug 6052169 | Versions < 11 | 11.1.0.6 | Backports possible | Slave memory grows unbounded and finally fails with ORA-4030. A heapdump of the memory shows ‘Statement Alloc’ string. |
| Bug 6061892 | Versions >= 10.2.0.1 but < 10.2.0.4 | 10.2.0.4/11.1.0.6 | Backports possible | It is possible to get error ORA-4030 and ORA-21780 when an application that drops and/or recreates many plsql packages or top-level Noteocedures or functions which are used in calls from SQL to PL/SQL. The leak is found in the heaps: pga heap -> session heap -> PLS non-lib hp. Most of the hunks on heap ‘PLS non-lib hp’ are PEIDEF or peihstdep Note: This fix introduces the problem described in bug 6139254 |
| Bug 6408917 | Versions < 11 | 11.1.0.6 | Backports possible | Excessive PGA memory consumption can be seen when using ref cursors returned from Java stored procedures. Ultimately this can lead to out of memory errors such as: ORA-29532: java call terminated by uncaught java exception: java.lang.outofmemory |
| Bug 6414844 | Versions < 11.2 | 11.2 | Backports possible to 10.1 | memory may be wasted in a subheap using kghu memory allocation.
A heapdump will show free chunks in each extent with a “free” chunk which is |
Basically, an ORA-4030 indicates a limit has been reached with respect to the Oracle process private memory allocation.
Typical causes:
* OS Memory limit reached such as physical memory and/or swap/virtual paging
* OS limits reached such as kernel or user shell limits that limit overall, user level or process level memory usage
* OS limit on PGA memory size due to SGA attach address Note 262540.1 Relocate SGABEG on 64-bit Oracle
* Oracle internal limit example Bug 3130972
* Application design causing limits to be reached
* Bug – space leaks, heap leaks
An ORA-4030 error is an error in the PGA; Memory limitation related to an Oracle rdbms server process is reached.
This includes the database background processes and oracle shadow processes that are spawned directly by the database or the listener.
An ORA-4031 error is an error in the SGA; Memory limitation in SGA component such as shared pool,large pool, java pool, streams pool is reached.
A Program Global Area (PGA) is a memory region that contains data and control information for a server process. It is a non-shared memory area created by Oracle when a server process is started. Access to it is exclusive to that server process and is read and written only by Oracle code acting on behalf of it. The total PGA memory allocated by each server process attached to an Oracle instance is also referred to as the aggregated PGA memory allocated by the instance.
Components of the PGA vary depending on how the database is configured. The PGA is comprised of four memory areas
* User Session Memory
User session memory is the memory allocated to hold a session’s variables (logon information) and other information related to the session. For a shared server, the session memory is shared and not private
* Private SQL Areas – contains data such as bind information and runtime buffers
A private SQL area contains data such as bind information and runtime memory structures. Each session that issues a SQL statement has a private SQL area. Each user that submits the same SQL statement has his or her own private SQL area that uses a single shared SQL area. Thus, many private SQL areas can be associated with the same shared SQL area.
The private SQL area of a cursor is itself divided into two areas whose lifetimes are different:
* The persistent area, which contains, for example, bind information. It is freed only when the cursor is closed.
* The run-time area, which is freed when the execution is terminated.
The location of a private SQL area depends on the type of connection established for a session. If a session is connected through a Dedicated Server, private SQL areas are located in the server process’s PGA. However, if a session is connected through a Shared Server, part of the private SQL area (specifically, the peristent area) is kept in the SGA.
The runtime area contains information used while the SQL statement is being executed. The size of the runtime area depends on the type and complexity of the SQL statement being executed and on the sizes of the rows that are processed by the statement. In general, the runtime area is somewhat smaller for INSERT, UPDATE, and DELETE statements than it is for SELECT statements, particularly when the SELECT statement requires a sort.
Oracle creates the runtime area as the first step of an execute request. For INSERT, UPDATE, and DELETE statements, Oracle frees the runtime area after the statement has been executed. For queries, Oracle frees the runtime area only after all rows are fetched or the query is canceled.
The location of a private SQL area depends on the type of connection established for a session. If a session is connected via a dedicated server, private SQL areas are located in the user’s PGA. However, if a session is connected via the multi-threaded server, the persistent areas and, for SELECT statements, the runtime areas, are kept in the SGA.
* SQL Work Areas
The application developer of an Oracle precompiler program or OCI program can explicitly open cursors, or handles to specific private SQL areas, and use them as a named resource throughout the execution of the program. Recursive cursors that Oracle issues implicitly for some SQL statements also use shared SQL areas.
The management of private SQL areas is the responsibility of the user process. The allocation and deallocation of private SQL areas depends largely on which application tool you are using, although the number of private SQL areas that a user process can allocate is always limited by the initialization parameter OPEN_CURSORS. The default value of this parameter is 50.
A private SQL area continues to exist until the corresponding cursor is closed or the statement handle is freed. Although Oracle frees the runtime area after the statement completes, the persistent area remains waiting. Application developers close all open cursors that will not be used again to free the persistent area and to minimize the amount of memory required for users of the application.
* Cursor and SQL Areas
For complex queries (for example, decision-support queries), a big portion of the runtime area is dedicated to work areas allocated by memory-intensive operators such as the following:
* Sort-based operators (order by, group-by, rollup, window function)
* Hash-join
* Bitmap merge
* Bitmap create
For example, a sort operator uses a work area (sometimes called the sort area) to perform the in-memory sort of a set of rows. Similarly, a hash-join operator uses a work area (also called the hash area) to build a hash table from its left input. If the amount of data to be processed by these two operators does not fit into a work area, then the input data is divided into smaller pieces. This allows some data pieces to be processed in memory while the rest are spilled to temporary disk storage to be processed later. Although bitmap operators do not spill to disk when their associated work area is too small, their complexity is inversely proportional to the size of their work area. Thus, these operators run faster with larger work area.
In shared server mode, portions of the UGA are stored in the following:
* Large pool if configured.
* Shared pool if large pool is not configured.
Note that as of 10g, the work areas controlled by PGA_AGGREGATE_TARGET are located in the pga for shared servers. On 9i shared server work areas are defined by the *_area_size parameters and located in the sga.
Parameter PGA_AGGREGATE_TARGET does not set a hard limit on pga size. It is only a target value used to dynamically size the process work areas.
It also does not affect other areas of the pga that are allowed to grow beyond this limit. See below for explanation.
As a database administrator you can control the size of a process within limitations.
For example, you can configure certain database initialization parameters that affect the size of a process work area.
If you choose, you can manually control the maximum size of SQL work areas by setting the following parameters: SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE. Setting these parameters is difficult, because the maximum work area size is ideally selected from the data input size and the total number of work areas active in the system. These two factors vary a lot from one work area to another and from one time to another. Thus, the various *_AREA_SIZE parameters are hard to tune under the best of circumstances.
With 9i and higher, you can automatically and globally manage the size of SQL work areas by specifying the total size dedicated to PGA memory for the Oracle instance by setting the initialization parameter PGA_AGGREGATE_TARGET. The specified number (for example, 2G) is a global target for the Oracle instance, and Oracle tries to ensure that the total amount of PGA memory allocated across all database server processes never exceeds this target. The key word in this statement is “tries”. It is possible that PGA memory will grow beyond the “target”.
The initialization parameter WORKAREA_SIZE_POLICY is a session- and system-level parameter that can be set to MANUAL or AUTO. The default is AUTO. You can switch back and forth between automatic and manual management of the work areas by setting PGA_AGGREGATE_TARGET and changing the WORKAREA_SIZE_POLICY parameter.
With PGA_AGGREGATE_TARGET, sizing of work areas for all dedicated sessions is automatic and all *_AREA_SIZE parameters are ignored for these sessions. Beginning with version 10 the PGA_AGGREGATE_TARGET is also applicable to shared server sessions.
At any given time, the total amount of PGA memory available to active work areas on the instance is automatically derived from the parameter PGA_AGGREGATE_TARGET. This amount is set to the value of PGA_AGGREGATE_TARGET minus the PGA memory allocated by other components of the system (for example, PGA memory allocated by sessions). The resulting PGA memory is then allotted to individual active work areas based on their specific memory requirement.
Keep in mind that the PGA_AGGREGATE_TARGET does not limit the amount of pga memory usage. It is only a target and is used to dynamically size the process work areas. It does not affect other areas of the pga that are allowed to grow beyond this limit.
There are certain areas of pga that cannot be controlled by initialization parameters. Such areas include pl/sql memory collections such as pl/sql tables and varrays.
Depending on the programming code and amount of data being handled these areas can grow very large (up to 20G internal limit on 10) and can consume large amounts of memory. This memory growth can be controlled by good programming practices. As an example, use LIMIT clause with BULK COLLECT.
See http://www.oracle.com/technology/oramag/oracle/08-mar/o28plsql.html
Additionally, programming mistakes can also lead to excessive memory usage.
For example a recursive or infinite loop can use up memory. An errorstack will show this code as follows:
—– PL/SQL Call Stack —–
object line object
handle number name
70000003d2d6198 17 package body SCOTT.REGISTRY_PKG
70000003d2d6198 133 package body SCOTT.REGISTRY_PKG
70000003d2d6198 155 package body SCOTT.REGISTRY_PKG
70000003d2d6198 31 package body SCOTT.REGISTRY_PKG
70000003d2d6198 133 package body SCOTT.REGISTRY_PKG
70000003d2d6198 155 package body SCOTT.REGISTRY_PKG
PL/SQL call stack truncated after 1024 bytes.
Refer to following notes for more details on Automatic PGA Memory Management
Note 223730.1 Automatic PGA Memory Managment in 9i and 10g
Note 443746.1 Automatic Memory Management(AMM) on 11g
You can take steps to control the size of a process as discussed above.
However, from within the database framework you cannot place a hard limit on the size of a process by setting any initialization parameters or database configuration.
You can limit the size of a process from the OS side by setting kernel limits or user shell limits.
However, this leads to the ORA-4030 and will cause transaction rollback.
Resolving memory issues is typically an iterative process requiring gathering of information over a period of time to identify possible trends that typically leads to further data gathering and diagnosis.
As part of initial step some basic questions should be answered, because a memory issue is usually triggered by some change.
When did problem start?
What changes have been made to system, including OS, applications, database installation?
Can the problem be isolated to any particular query, job or application?
If problem can be isolated to particular code, does problem reproduce on other systems?
Does problem code work when run through local connection?
Then, need to gather data from the database and OS.
RDA – This will provide basic OS information that can be reviewed for memory/swap configuration, as well as database configuration.
Alert log – may list errors, trace files and non-default parameters for startup.
Related trace files – initial trace files are typically of not much use for memory issues; need to setup event for proper heapdump as discussed below.
Run database monitoring queries – the queries provided later in this note can be used to identify a particular process growing large or total pga and sga usage per instance.
Run OS monitoring queries – OSwatcher is a good start to see OS information related to memory,swap and relative process sizes
Get heapdumps – methods discussed below on how to gather
On most unix/linux OS, for local connections, the shadow processes spawned on the database server inherit the ulimits of the OS user that starts the database.
For processes spawned through a listener connection, it is the ulimits of the OS user that starts the listener that are inherited.
Need to verify that memory related resource limits as listed by ulimit -a are set large enough to prevent the ORA-4030.
See below discussion on ulimits.
When PAM authentication is being used on linux, the limits as set by pam may be an issue if starting the db or listener.
See Note 261220.1 Ulimit Command Unusable on RHEL 3.0
Need to review the ulimits for the OS oracle user that is starting the database and listener.
Typically an RDA may not be run as oracle user, but as root. Need to distinguish if that is the case and then ask for appropriate oracle user ulimits. On most OS, for local connections, the shadow processes spawned on the database server inherit the ulimits of the OS user that starts the database.
For processes spawned through a listener connection, it is the ulimits of the OS user that starts the listener that are inherited.
Look at physical ram and swap/paging configuration.
Depending on the OS, some information may show the free memory and paging used. Look in both the Overview->System Information link
and the Performance -> Overview link for information related to memory. This may include information from various OS commands/utilities such as swap,free,top,vmstat
For some OS, may need to request specific OS related information or try using the OSwatcher utility.
While Oracle support cannot address all cases due to the numerous OS releases and versions, can make the following general recommendations for typical platforms.
Check the ulimits of OS user starting the database and listener.
In particular we are interested in the data (d) and memory limits (m or v).
The stack (s) is typically not an issue unless set too high on a 32 bit system which can prevent database startup.
typically, all soft settings can be shown with
>ulimit -a
for soft limits of individual resource limit use S
example for data segment size limit
>ulimit -dS
for hard limits of individual resource limit use H
>ulimit -dH
Note that specifying unlimited may actually correspond to a numeric value, although unlimited is listed in the ulimit display.
May need to consult OS documentation or vendor to confirm what actual values may be. If in doubt can always set to a numeric value rather than unlimited.
Additionally, if on RAC, how are database/listener started?
Is lsrnctl used directly to start listener or using srvctl or CRS?
The difference relates back to the ulimits of user starting the listener, srvctl
or the root user limits starting the CRS.
Need to review the limits for data and memory of root user.
Note 758131.1 SOME Client Connection FAIL WITH ORA-4030 ERROR when
connecting using RAC
Note 753516.1 The difference between using srvctl vs using sqlplus for
start/stop one or more database nodes
For further information refer to Note 188149.1 How to Display and Change UNIX Process Resource Limits
or specific OS documentation.
For common kernel limits on various OS see Note 169706.1 Oracle® Database on AIX®,HP-UX®,Linux®,Mac OS® X,
Solaris���,Tru64 Unix® Operating Systems Installation and Configuration Requirements Quick Reference (8.0.5 to 11.1)
On Solaris,AIX and linux, it is the ulimit settings that determine process memory related resource limits. No kernel settings are used to control the process memory limits.
HP
still uses kernel parameters such as following that set hard limits
maxtsiz(_64bit) / maxdsiz(_64bit) / maxssiz(_64bit)
TRU64
make use of kernel limits
/sbin/sysconfig -q proc
per-proc-data-size/max-per-proc-data-size
per-proc-address-space/max-per-proc-address-space
per-proc-stack-size/max-per-proc-stack-size
vm_rss_maxpercent
In some rare cases have seen some misleading process spawning or memory issues occurred due to other kernel settings that limited number of processes such as a low setting of
NPROC. If such limits are set low and there are a large number of processes on the server consider increasing this.
Queries can be run against the instance to show pga memory usage.
Depending on the time frame of the problem occurrence these queries need to be run at different time intervals to review for trends.
The queries are best run through a cron job, Oracle scheduler job or on MSwindows using the task scheduler.
An example unix shell script is contained in attachment memleak.sh that can be used to kick off the sql script at specified intervals. The script can be used to run pga_2010script1.sql containing pga memory queries.
For example start meamleak.sh collecting at 20 second increments
>./memleak.sh 20 > memleak.out
Monitor the pga usage for all processes related to an instance from v$sesstat.
Look at trends of individual processes growing in size.
REM v$sesstat pga/uga memory size
select p.spid, s.sid, substr(n.name,1,25) memory, s.value as Bytes from v$sesstat s, v$statname n, v$process p, v$session vs
where s.statistic# = n.statistic#
/* this query currently looks at both uga and pga, if only one of these is desired modify the like clause to pga or uga */
and n.name like '%ga memory%'
and s.sid=vs.sid
and vs.paddr=p.addr
/* --remove comment delimiters to view only certain sizes, i.e. over 10Mbytes
and s.value > 10000000 */
order by s.value asc;
List Largest process.
/* Do Not eliminate all background process because certain background processes do need to be monitored at times */
select pid,spid,substr(username,1,20) "USER" ,program,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM
from v$process
where pga_alloc_mem=(select max(pga_alloc_mem) from v$process
where program not like '%LGWR%');
Summation of pga based on v$process
REM allocated includes free PGA memory not yet released to the operating system by the server process
select sum(pga_alloc_mem)/1024/1024 as "Mbytes allocated", sum(PGA_USED_MEM)/1024/1024 as "Mbytes used" from v$process;
Summation of pga memory based on v$sesstat
select sum(value)/1024/1024 as Mbytes from v$sesstat s, v$statname n
where
n.STATISTIC# = s.STATISTIC# and
n.name = 'session pga memory';
PGA stats from v$pgastat
select substr(name,1,30), value, unit from v$pgastat;List all processes including pga size from v$process
Outer join will show if any defunct processes exist without associated session.
set linesize 120
set pagesize 120
column spid heading 'OSpid' format a8
column pid heading 'Orapid' format 999999
column sid heading 'Sess id' format 99999
column serial# heading 'Serial#' format 999999
column status heading 'Status' format a8
column pga_alloc_mem heading 'PGA alloc' format 99,999,999,999
column pga_used_mem heading 'PGA used' format 99,999,999,999
column username heading 'oracleuser' format a12
column osuser heading 'OS user' format a12
column program heading 'Program' format a20
SELECT
p.spid,
p.pid,
s.sid,
s.serial#,
s.status,
p.pga_alloc_mem,
p.PGA_USED_MEM,
s.username,
s.osuser,
s.program
FROM
v$process p,
v$session s
WHERE s.paddr ( + ) = p.addr
and p.BACKGROUND is null /* Remove if need to monitor background processes */
Order by p.pga_alloc_mem desc;
Summation of pga and sga gives a value of total memory usage by oracle instance
–look at total memory used by instance SGA and PGA
select sum(bytes)/1024/1024 as Mbytes from
(select value as bytes from v$sga
union all
select value as bytes from
v$sesstat s,
v$statname n
where
n.STATISTIC# = s.STATISTIC# and
n.name = 'session pga memory'
);
Memory usage on the OS side needs to be monitored.
A good method on unix/linux boxes is to setup OSwatcher per note:
Note.301137.1 Ext/Pub OS Watcher User Guide
OS Watcher (OSW) is a collection of UNIX shell scripts intended to collect and archive operating system and network metrics to aid support in diagnosing performance issues. OSW operates as a set of background processes on the server and gathers OS data on a regular basis, invoking such Unix utilities as top, vmstat, netstat and iostat.
Depending on the issue, set the rate data is gathered to reasonable value
If problem quickly reproduces may want low values such as 5 minutes or even lower.
If problem is unknown or long running may want to set to 30 minutes or higher.
Other methods may include customer scripts or 3rd party utilities that monitor OS memory usage.
Run perfmon utility to monitor memory usage.
This tool shows the memory used by the Oracle process more accurately than the information in Task Manager.
Method to do this varies on the windows version, but typical method is as follows:
If you go to the Control Panel (under Start, Settings) and click on Adminstrative Tools you can startup the Performance monitor.
You can also start up a Windows command window and type ‘Perfmon’.
This tool shows the memory used by the Oracle process more accurately than the information in Task Manager.
Under the console tree, select Performance Logs and Alerts. Right click on ‘Counter Logs’ and select ‘New Log Settings’ to start a new log.
Click on the Add Counters button to add new counters to the log.
Click on ‘Process’ under ‘Performance Object’.
Select counters, ‘Private Bytes’, ‘Thread Count’, ‘Handles’, and ‘Virtual Bytes’ in the ‘Select counters from list’ window.
You need to select these counters for the process, ‘Oracle’ and ‘Total’ located in the ‘Select instance from list’ window. Click on the ‘Add’ button.
Also, select Performance object ‘Memory’ and select counter ‘Available MBytes’. This will track
overall free memory available on the system.
Set the appropriate sample data interval. If problem occurs over a very long period of time such as several hours, then may set the interval to a few minutes.
If problem occurs relatively quickly on order of a few minutes, then set interval to a few seconds.
These logs can be uploaded to oracle support for review and can be imported into MSExcel for graphing to review for trends.
If the log is directly saved as a comma delimited file it can be imported directly. If saved as a binary file, it must first be opened with the permon tool and then saved as a comma delimited file for import into MSExcel.
Monitor memory usage on the ‘Virtual Bytes’ counter. ‘Virtual Bytes’ shows the actual “committed memory” that the process is allocated.
Video – Using MSWindows perfmon utility to monitor oracle.exe (02:31)
In a windows 32 bit system the addressable memory is limited to 4G for a process. This translates to about 1.8G of actual memory usage that an oracle process can address including the SGA and PGA usage. This can be increased to about 3G with use of the /3GB switch configured in the boot.ini file.
So even if you have over 4G of ram, a single instance cannot address any more memory above this 4G. This includes the total memory usage of an SGA and the sum of all associated PGAs (threads associated with the oracle process).
An exception to this is Address Windowing Extensions (AWE).
AWE allows a process to address memory above the 4G limit with use of /PAE switch. Oracle uses the AWE to allow usage of additional buffer cache above the 4G limit
with setting of database parameter USE_INDIRECT_DATA_BUFFERS=TRUE.
The AWE window is allocated in the lower 4G addressable memory space and by default is 1G. AWE size can be explicitly set in registry to reduce this size below 1G subject to the calculation as determined by Note 225349.1.
The threads attached to the oracle process(the PGAs) and other SGA components cannot make use of this AWE and thus cannot make use of any memory above the 4G. So need to consider that even if you setup the /3GB switch and use AWE it may reduce addressable memory available to the PGA.
See Note 225349.1 Implementing Address Windowing Extensions (AWE) or VLM on Windows Platforms that discusses configuration of /3GB and /PAE.
Also, note that with the 3GB switch set it can impact the amount of nonpaged pool space available to the OS. This space is used for async i/o and in an i/o intensive system could lead to i/o errors. The /USERVA switch may need to be set to increase available nonpaged pool space. The default value is 3070.
May need to set this lower to 3030 or lower such as 2090.
Three methods of gathering a heapdump are shown below depending on problem occurrence.
Method A. Set event in the pfile if general cause of error is unknown
Method B. Set event at session level if problem can be easily reproduced from session
Method C. Attach using oradebug to specific process
Method A
Set event at instance level to fire for any occurrence of the ORA-4030.
The following will set in memory.
ALTER SYSTEM SET EVENTS ‘4030 trace name heapdump level 536870917;name errorstack level 3’;
To turn off these events:
ALTER SYSTEM SET EVENTS ‘4030 trace name heapdump off;name errorstack off’;
If instance will be restarted, need to set in spfile to persist across startup.
ALTER SYSTEM SET EVENT=’4030 trace name heapdump level 536870917;name errorstack level 3′ scope=spfile;
or
place in pfile immediately after any other events
event= ‘4030 trace name heapdump level 536870917;name errorstack level 3’
Note that if on a RAC system need to also specify the instance or all instances with sid= in the alter statement.
Specify SID = ‘*’ if you want Oracle Database to change the value of the parameter for all instances.
Specify SID = ‘sid’ if you want Oracle Database to change the value of the parameter only for the instance sid. This setting takes precedence over previous and subsequent ALTER SYSTEM SET statements that specify SID = ‘*’.
To remove just these events from spfile:
ALTER SYSTEM SET EVENT=’4030 trace name heapdump off;name errorstack off’ scope=spfile;
Method B
Set event only at session level.
This will set event at session level and create trace file when error occurs in that session only.
ALTER SESSION SET EVENTS ‘4030 trace name heapdump level 536870917;name errorstack level 3’;
Method C
Can attach to a particular process using oradebug to get a heapdump.
This is useful and required if the process grows large, but does not cause an error.
Reproduce the problem, and monitor process memory growth.
Start a new sqlplus session to attach with oradebug
SQL>sqlplus /nolog
SQL>conn / as sysdba
SQL> alter system set max_dump_file_size=unlimited;
SQL> connect sys as sysdba
SQL> oradebug setospid <OSpid from query above for unix systems>
If on a MSwindows platform you will need to attach using the setorapid instead of setospid
SQL> oradebug setorapid <Orapid from query above>
SQL> oradebug unlimit
SQL> oradebug dump errorstack 10
SQL> oradebug dump heapdump 536870917
SQL> oradebug tracefile_name (shows the path and filename information)
SQL> oradebug close_trace (closes the trace file)
SQL>exit
@Internal see note for heapdump level definitions
With an ORA-4030 or process memory growth we are interested in the following heaps:
Level
top PGA 1 –on release 10 and higher includes top call heap
top UGA 4
Current call 8
User call 16
As of release 9.2.0.5 and higher, also interested in generating the five largest subheaps for each subheap with additional level 536870912
Typical level to gather would be 536870917
On releases lower than 9.2.0.5 will not be able to use this; use level 5
In some cases the heapdump may show large allocations of type ‘perm’.
On releases 9.2.0.5 and higher can set event 10235 level 65536 in order to further diagnose what these perm allocations are.
However, this event should only be set under direction from Oracle Support.
This level causes comments to be included with each perm allocation where possible.
The comments show as “cprm” chunks under the “perm” chunks in the PERMANENT section of the heapdump.
IMPORTANT: Do NOT set this event at session level. It can cause ORA-600 dumps.
Set at instance level only.
ALTER SYSTEM SET EVENTS ‘10235 trace name context forever, level 65536’;
To turn off:
ALTER SYSTEM SET EVENTS ‘10235 trace name context off’;
If instance will be restarted, need to set in spfile to persist across startup.
ALTER SYSTEM SET EVENT=’10235 trace name context forever, level 65536′ scope=spfile;
or
place in pfile immediately after any other events
event = ‘10235 trace name context forever, level 65536′
Note that if on a RAC system need to also specify the instance or all instances with sid= in the alter statement.
To turn off:
ALTER SYSTEM SET EVENT=’10235 trace name context off’ scope=spfile;
High swap usage on Solaris
——————————-
DISM may be in use.
Solaris Dynamic Intimate Shared Memory (DISM) provides shared memory that is dynamically resizable. DISM means that applications can respond to changes in memory availability by dynamically increasing or
decreasing the size of optimized shared memory segments. Oracle uses DISM for its dynamic System Global Area (SGA) capability.
DISM is available on Solaris 9 and higher.
Oracle database release 9i and higher uses DISM depending on certain database parameter settings.
Oracle 9i and higher, if SGA_MAX_SIZE > SGA_TARGET (or sum of sga compenents) then DISM is used.
On 11G, DISM is also used if MEMORY_TARGET or MEMORY_MAX_TARGET is set.
Otherwise, ISM is used if DISM is not enabled.
There are two issues to be aware of when DISM is being used:
First, DISM (unlike ISM), requires a swap reservation for all pages, whether or not they’re allocated.
This basically means need to have at least as much swap as the SGA size for instances that
are using DISM. An ORA-4030 could result on systems with inadequate swap space.
Second, additional issue with DISM per Sun bug 6559612.
If the ora_dism daemon fails to start, or dies for any reason, that can cause excessive memory/swap space usage to occur relative to the amount of async IO activity. ORA-4030 errors may occur and you could also see other errors in the database alert log such as following:
ORA-17500: ODM err:ODM ERROR V-41-4-1-253-12 Not enough space
To determine if DISM is being used for an Oracle instance, you can look on the OS side with ps tool.
>ps -aef | grep dism
If dism is being used it will show as a process in format ora_dism_$ORACLE_SID
To disable use of DISM can simply unset SGA_MAX_SIZE, MEMORY_TARGET, MEMORY_MAX_TARGET
or set sga_max_size=sga_target
or allow sga_max_size to default to sum of the sga components.
ZFS file system
—————
Oracle processes such as exp utility writing to a ZFS file system appear to use all of system memory.
The ZFS adaptive replacement cache (ARC) tries to use most of a system’s available memory to cache file system data.
The default is to use all of physical memory except 1 Gbyte. As memory pressure increases, the ARC relinquishes memory.”
See the ZFS Best Practices Guide
http://www.solarisinternals.com/wiki/index.php/ZFS_Best_Practices_Guide#ZFS_Administration_Considerations
or latest Solaris 10 documentation for configuring zfs_arc_max to limit cache memory.
Mounting Oracle_home location in concurrent I/O mode
—————————————————-
Having an ORACLE_HOME on a filesystem mounted with “cio” option is not supported.
Such a configuration will cause, installation, relinking and other unexpected problems.
One such problem is high memory use for oracle background processes
CIO creates private memory allocations for the executable files in ORACLE_HOME that cannot be shared or reused.
This leads to “orphans” memory segments that are not shown from the database side (v$process),
but the OS shows more and more memory growth–particularly for background processes.
Unused free memory on linux system with hugepages configured
————————————————————
If SGA size > HugePages size setting, then hugepages cannot be used.
This results in free memory never being used.
The sizing of hugepages should be just greater than the sga size in order for hugepages to be
implemented and to minimize wasted memory.
Verify if hugepages are being used.
>grep Huge /proc/meminfo
HugePages_Total: 1350
HugePages_Free: 5
Hugepagesize: 2048 kB
HugePages_Free should be much lower than HugePages_Total. If equivalent, then hugepages may not be
used because the SGA > hugepages.
Example:
sum of sga components or SGA_MAX_SIZE = 2648M
Need to configure at least that much space for hugepages.
Size for 2700M with a 2K hugepage size == 2764800Kb / 2048kb = 1350 pages required.
If you were to set the SGA larger than this the hugepages could not be used. You would need to
adjust the number of hugepages higher.
See Note 361323.1 HugePages on Linux for further information regarding hugepages.
Excessive memory/swap on AIX when large pages not configured correctly
—————————————————————————-
If database parameter LOCK_SGA=true then large pages will be used.
If not configured properly, that can lead to excessive memory/swap usage.
Configure large pages for the SGA per Note 372157.1 How to enable Large Page Feature on AIX-Based Systems
The utility svmon -G will show large page size=16 MB in use if properly configured.
Note, also that on 10.2.0.4 large pages are not used properly due to unpublished Bug 7226548.
See Note:740603.110.2.0.4 not using large pages on AIX.
NOTE:199746.1 – How to Resolve ORA-4030 Errors on UNIX
NOTE:233869.1 – Diagnosing and Resolving ORA-4030 errors
NOTE:863562.1 – Getting Started with ORA-4030 Diagnostics
 Attachments
|
||||
| How To Check if Asynchronous I/O is Working On Linux [ID 237299.1] | |||||
|
|
|||||
| Modified 10-NOV-2010 Type HOWTO Status PUBLISHED | |||||
In this Document
Goal
Solution
Caveat for ASMLib
Linux OS – Version: 3.0 to 5.0 – Release: RHEL3 to OEL5
Oracle Server – Enterprise Edition – Version: 9.0.1.0 to 11.1.0.6 [Release: 9.0.1 to 11.1]
Linux x86
Obsolete Linux Intel (64-bit)
Linux x86-64
**Checked for relevance on 10-Nov-2010***
Linux Kernel – Version: 3.0 to 5.0
In this document we are going to explain how to check that asynchronous I/O (AIO) is working. AIO can be enabled in Oracle 9i 9.2 and higher.
Many times there is a requirement to check if Asynchronous I/O is working on Linux Platform, so we can try to use it for our datafiles access inside database.
The slabinfo maintains statistics about objects in memory. Some of the structs used by Asynchronous I/O are threated as objects in the virtual memory, so we can look for those structs on slabinfo. The ones related to AIO are named kio*.
$ cat /proc/slabinfo | grep kioIf Async I/O is enabled:
$ cat /proc/slabinfo | grep kio
kioctx 270 270 128 9 9 1 : 252 126
kiocb 66080 66080 96 1652 1652 1 : 252 126
kiobuf 236 236 64 4 4 1 : 252 126
and if Async I/O is disabled:
$ cat /proc/slabinfo | grep kio
kioctx 0 0 128 0 0 1 : 252 126
kiocb 0 0 96 0 0 1 : 252 126
kiobuf 0 0 64 0 0 1 : 252 126In the SLAB allocator there are three different caches involved. The kioctx and kiocb are Async I/O data structures that are defined in aio.h header file. If it shows a non zero value that means async io is enabled.
If you have the source code loaded, you can review it at file aio.h. This file is located under:
/usr/src/linux-<version>/include/linux/aio.h
These data structures are using to track the I/O requests, and are allocated as part of the __init_aio_setup() call in aio.c.
Example strace of dbw0 process with AIO enabled (init.ora parameter filesystemio_options = asynch) shows:
...
io_submit(3071864832, 1, {{0xb7302e34, 0, 1, 0, 21}}) = 1
gettimeofday({1176916625, 58882}, NULL) = 0
io_getevents(-1223102464, 1, 1024, {{0xb7302e34, 0xb7302e34, 8192, 0}}, {600, 0}) = 1
...
Example strace of dbw0 process with AIO disabled (filesystemio_options = none):
... pwrite64(21, "\6\242004\21\300\220B\243\1\6\207\357\1"..., 8192, 36077568) = 8192 times(NULL) = 1775653082 times(NULL) = 1775653082 pwrite64(21, "\6\242<\21\300\220B\243\1\6\254\2*"..., 8192, 36143104) = 8192 ...
If Oracle ASMLib (see http://oss.oracle.com/projects/oracleasm ) is deployed, the kiocb structs are not used. ASMLib does not use the POSIX aio_*() functions. You will never see any kioctx or kiocb structures from ASMLib. It is far lower level than that.
In fact, ASMLib does AIO or SyncIO depending on how the I/O is passed to it, It makes no decisions at all. This is entirely up to kfk and the layers above it, kfk is entirely controlled by the disk_asynch_io parameter. So, we can check whether ASMLib is doing AIO by PL/SQL command “show param disk_asynch_io”. (You can disable AIO by setting disk_asynch_io=false)
With ASMLib, AIO is done via ioctl() calls (2.4 kernel), or read() calls (2.6 kernel) on the ASM device. Whether ASMLib uses aio depends on whether oracle is configured to do aio, In oracle 10g, if ASMLib is in use, the i/o is asynchronous, because oracle 10g enables aio by default.
The strace when using ASMlib will show read calls that look like this:
... read(16, "MSA\2\10P\222\377\377\377@\313\373\5"..., 80) = 80 ...
The first 3 characters, byte-swapped, are ASM, indicating an ASMLib I/O command structure.
Alert.log shows a very high frequency of redo log switches, only a few seconds between them; such aggressive log switches cause checkpoint hardly to keep pace with log switches and generate a lot of “CHECKPOINT NOT COMPLETE” in alert.log file. it not only happens during heavy activity period but also normal working hours. Waiting on “CHECKPOINT NOT COMPLETE” decreases system performance, as recommended, it can be solved in 2 manners:
1) use async_io or increases number of db writers to speed up checkpoint process
2) increase number of redo log groups to extend the cycle of reusing an old redo log
Customer reports they met problem of “ORA-01000: maximum open cursors exceeded” when running a application.
Connect to database as sysdba user, get current setting of open cursors limit
show parameter open_cursors NAME TYPE VALUE ------------------------------------ ----------- ----------- open_cursors integer 1000
list the top 10 sessions which are currently opening most cursors
select * from ( select ss.value, sn.name, ss.sid from v$sesstat ss, v$statname sn where ss.statistic# = sn.statistic# and sn.name like '%opened cursors current%' order by value desc) where rownum < 11 VALUE NAME SID ------ ------------------------------ ---------- 1000 opened cursors current 393 1000 opened cursors current 404 1000 opened cursors current 414 1000 opened cursors current 456 1000 opened cursors current 452 1000 opened cursors current 448 1000 opened cursors current 485 1000 opened cursors current 474 1000 opened cursors current 468 1000 opened cursors current 457
Check what makes session= 393 open so many cursors?
SQL> select sid, status, event, seconds_in_wait, state, blocking_session, prev_sql_id from v$session where sid=393; SID STATUS EVENT SECONDS_IN_WAIT STATE BLOCKING_SESSION PREV_SQL_ID ----- -------- ------------------------------ --------------- ------------------- ---------------- ------------- 393 INACTIVE SQL*Net more data from client 16607 WAITING 63yzf58cphjsa SQL> select sql_text from v$sqlarea where sql_id ='63yzf58cphjsa' SQL_TEXT -------------------------------------------------------------------------------- begin TST_TRO.ctrlBloccoOrdine(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11); end; SQL> select * from (select SQL_TEXT, sql_id, user_name, count(*) from v$open_cursor where sid=393 group by SQL_TEXT, sql_id, user_name order by count(*) desc) where rownum < 11; SQL_TEXT SQL_ID USER_NAME COUNT(*) -------------------------------------------------------------------------------- ------------- ---------- -------- SELECT 2 FROM DUAL 5v2mtp0wz00h8 CENPROD 886 SELECT 0 FROM DUAL 5vw6s1gtrfzcx CENPROD 18 SELECT 1 FROM DUAL bunvx480ynf57 CENPROD 14 SELECT clisocju,TRUNC(NVL(MAX(efadkro),SYSDATE)) FROM cfdenf 99mzst5bbd3vw CENPROD 3 SELECT SDRCTVA, SDRCINR, PKSTRUCOBJ.ge 09kcrqn5hhfrm CENPROD 2 SELECT SDRCTVA, SDRCINR, PKSTRUCOBJ.ge 3301qv5xj2jps CENPROD 2 SELECT SDRCTVA, SDRCINR, PKSTRUCOBJ.ge 474pqmx6dc0mj CENPROD 2 SELECT SDRCTVA, SDRCINR, PKSTRUCOBJ.ge 4ja9rd0agjv7r CENPROD 2 SELECT SDRCTVA, SDRCINR, PKSTRUCOBJ.ge 4dgknnbk4wkgu CENPROD 2 SELECT SDRCTVA, SDRCINR, PKSTRUCOBJ.ge 40xz5sn921s72 CENPROD 2</pre>
Search for statement “SELECT 2 FROM DUAL” in recently executed package “ctrlBloccoOrdine”, we find the piece of code that caused remaining open cursors:
w_sql := 'SELECT 2 FROM DUAL'; open p_cur for w_sql; commit; -- without close p_cur;
The cursor has been opened with closing in whole instance
select * from (select SQL_TEXT, sql_id, user_name, count(*) from v$open_cursor group by SQL_TEXT, sql_id, user_name order by count(*) desc) where rownum < 11; SQL_TEXT SQL_ID USER_NAME COUNT(*) -------------------------------------------------------------------------------- ------------- ---------- -------- SELECT 2 FROM DUAL 5v2mtp0wz00h8 CENPROD 11063 SELECT 0 FROM DUAL 5vw6s1gtrfzcx CENPROD 527 SELECT 1 FROM DUAL bunvx480ynf57 CENPROD 481 BEGIN :1 := pk_ums.Pl_UpdCaracPhy(:2); END; gh8a4w5rq6gbn STKPROD 245 SELECT clisocju,TRUNC(NVL(MAX(efadkro),SYSDATE)) FROM cfdenf 99mzst5bbd3vw CENPROD 110 SELECT AUPDATA FROM ADM_USERPREF WHERE AUPUSER=:1 AND AUPAP 73a9xvkqpg6vn STKPROD 46 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, 96g93hntrzjtr SYS 46 select TO_CHAR(CURRENT_DATE,'DD/MM/YY') from dual 7drfkfpg3gwra STKPROD 31 select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oi 04xtrk7uyhknh SYS 30 SELECT PARVAN1 FROM PARPOSTES T WHERE T.PARCMAG = :B3 AND T. bpq3apk5q2c6z CENPROD 29
Report this problem to development team and kill these sessions INACTIVE exceeding limit of opening cursors.
FERENCE:
Submitted by Natalka Roshak on Thu, 2005-12-01 23:55
Just about every DBA has had to deal with ora-1000 errors, “Maximum open cursors exceeded.” This article will discuss initialization parameters that affect open cursors, the difference between open and cached cursors, closing cursors, and monitoring open and cached cursors.
Open cursors take up space in the shared pool, in the library cache. To keep a renegade session from filling up the library cache, or clogging the CPU with millions of parse requests, we set the parameter OPEN_CURSORS.
OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. If a single session has OPEN_CURSORS # of cursors open, it will get an ora-1000 error when it tries to open one more cursor.
The default is value for OPEN_CURSORS is 50, but Oracle recommends that you set this to at least 500 for most applications. Some applications may need more, eg. web applications that have dozens to hundreds of users sharing a pool of sessions. Tom Kyte recommends setting it around 1000.
There are two main initialization parameters that affect cursors, and many folks get them confused. One is OPEN_CURSORS, and the other is SESSION_CACHED_CURSORS.
SESSION_CACHED_CURSORS sets the number of cached closed cursors each session can have. You can set SESSION_CACHED_CURSORS to higher than OPEN_CURSORS, lower than OPEN_CURSORS, or anywhere in between. This parameter has no effect on ora-1000’s or on the number of cursors a session will have open. Conversely, OPEN_CURSORS has no effect on the number of cursors cached. There’s no relationship between the two parameters.
If SESSION_CACHED_CURSORS is not set, it defaults to 0 and no cursors will be cached for your session. (Your cursors will still be cached in the shared pool, but your session will have to find them there.) If it is set, then when a parse request is issued, Oracle checks the library cache to see whether more than 3 parse requests have been issued for that statement. If so, Oracle moves the session cursor associated with that statement into the session cursor cache. Subsequent parse requests for that statement by the same session are then filled from the session cursor cache, thus avoiding even a soft parse. (Technically, a parse can’t be completely avoided; a “softer” soft parse is done that’s faster and requires less CPU.)
In the session cursor cache, Oracle manages the cached cursors using a LRU list. Once more than SESSION_CACHED_CURSORS closed cursors are cached, Oracle starts dropping cached cursors off the LRU end of the list whenever it needs to make room to cache a new cursor.
The obvious advantage to caching cursors by session is reduced parse times, which leads to faster overall execution times. This is especially so for applications like Oracle Forms applications, where switching from one form to another will close all the session cursors opened for the first form. Switching back then opens identical cursors. So caching cursors by session really cuts down on reparsing.
There’s another advantage, though. Since a session doesn’t have to go looking in the library cache for previously parsed SQL, caching cursors by session results in less use of the library cache and shared pool latches. These are often points of contention for busy OLTP systems. Cutting down on latch use cuts down on latch waits, providing not only an increase in speed but an increase in scalability.
I believe a lot of the confusion about open cursors vs. cached cursors comes from the names of the Oracle dynamic performance views used to monitor them. v$open_cursor shows cached cursors, not currently open cursors, by session. If you’re wondering how many cursors a session has open, don’t look in v$open_cursor. It shows the cursors in the session cursor cache for each session, not cursors that are actually open.
To monitor open cursors, query v$sesstat where name=’opened cursors current’. This will give the number of currently opened cursors, by session:
--total cursors open, by session select a.value, s.username, s.sid, s.serial# from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current';
If you’re running several N-tiered applications with multiple webservers, you may find it useful to monitor open cursors by username and machine:
--total cursors open, by username & machine select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, s.username, s.machine from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current' group by s.username, s.machine order by 1 desc;
The best advice for tuning OPEN_CURSORS is not to tune it. Set it high enough that you won’t have to worry about it. If your sessions are running close to the limit you’ve set for OPEN_CURSORS, raise it. Your goal in tuning this parameter is to set it high enough that you never get an ora-1000 during normal operations.
If you set OPEN_CURSORS to a high value, this doesn’t mean that every session will have that number of cursors open. Cursors are opened on an as-needed basis. And if one of your applications has a cursor leak, it will eventually show up even with OPEN_CURSORS set high.
To see if you’ve set OPEN_CURSORS high enough, monitor v$sesstat for the maximum opened cursors current. If your sessions are running close to the limit, up the value of OPEN_CURSORS.
SQL> select max(a.value) as highest_open_cur, p.value as max_open_cur
2> from v$sesstat a, v$statname b, v$parameter p
3> where a.statistic# = b.statistic#
4> and b.name = 'opened cursors current'
5> and p.name= 'open_cursors'
6> group by p.value;
HIGHEST_OPEN_CUR MAX_OPEN_CUR
---------------- ------------
1953 2500
After you’ve increased the value of OPEN_CURSORS, keep an eye on v$sesstat to see if opened cursors current keeps increasing for any of your sessions. If you have an application session whose opened cursors current always increases to catch up with OPEN_CURSORS, then you’ve likely got a cursor leak in your application code: your application is opening cursors and not closing them when it’s done.
There is nothing you, as a DBA, can do to fix a cursor leak. The application developers need to go through the code, find the cursors that are being left open, and close them. As a stopgap, the most you can do is raise OPEN_CURSORS very high and schedule times when all the application sessions will be closed and reopened (eg. by kicking the webserver).
Frustratingly for developers, the session statistic ‘currently open cursors’ can include some cursors that the application has closed. When application code calls for a cursor to be closed, Oracle actually marks the cursor as “closeable”. The cursor may not actually be closed until Oracle needs the space for another cursor.
So it’s not possible to test to see if a complex application is closing all its cursors by starting a session, running a test, and then checking to see if currently open cursors has gone down to 1. Even if the application is closing all its cursors properly, currently open cursors may report that some “closeable” cursors are still open.
One way for application developers to tell if an application is closing all its cursors is to do a single test run, on a dedicated development box, while monitoring “opened cursors cumulative” in v$sesstat for the session that’s running the test. Then set OPEN_CURSORS to a value a little bit higher than the peak cursors open during your test run, start a new session, and run through multiple iterations of the same test run. If your application still has a cursor leak, you will see the value of OPEN_CURSORS going up, and you may hit an ORA-1000 after a reasonable number of iterations. (Don’t set OPEN_CURSORS too low or it may be used up by recursive SQL; if your single test run opens very few cursors, consider making your test run longer rather than setting OPEN_CURSORS unreasonably low.)
v$sesstat also provides a statistic to monitor the number of cursors each session has in its session cursor cache.
--session cached cursors, by session select a.value, s.username, s.sid, s.serial# from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'session cursor cache count' ;
You can also see directly what is in the session cursor cache by querying v$open_cursor. v$open_cursor lists session cached cursors by SID, and includes the first few characters of the statement and the sql_id, so you can actually tell what the cursors are for.
select c.user_name, c.sid, sql.sql_text from v$open_cursor c, v$sql sql where c.sql_id=sql.sql_id -- for 9i and earlier use: c.address=sql.address and c.sid=&sid ;
If you choose to use SESSION_CACHED_CURSORS to help out an application that is continually closing and reopening cursors, you can monitor its effectiveness via two more statistics in v$sesstat. The statistic “session cursor cache hits” reflects the number of times that a statement the session sent for parsing was found in the session cursor cache, meaning it didn’t have to be reparsed and your session didn’t have to search through the library cache for it. You can compare this to the statistic “parse count (total)”; subtract “session cursor cache hits” from “parse count (total)” to see the number of parses that actually occurred.
SQL> select cach.value cache_hits, prs.value all_parses,
2> prs.value-cach.value sess_cur_cache_not_used
3> from v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
4> where cach.statistic# = nm1.statistic#
5> and nm1.name = 'session cursor cache hits'
6> and prs.statistic#=nm2.statistic#
7> and nm2.name= 'parse count (total)'
8> and cach.sid= &sid and prs.sid= cach.sid ;
Enter value for sid: 947
old 8: and cach.sid= &sid and prs.sid= cach.sid
new 8: and cach.sid= 947 and prs.sid= cach.sid
CACHE_HITS ALL_PARSES SESS_CUR_CACHE_NOT_USED
---------- ---------- -----------------------
106 210 104
Monitor this in concurrence with the session cursor cache count.
--session cached cursors, for a given SID, compared to max select a.value curr_cached, p.value max_cached, s.username, s.sid, s.serial# from v$sesstat a, v$statname b, v$session s, v$parameter2 p where a.statistic# = b.statistic# and s.sid=a.sid and a.sid=&sid and p.name='session_cached_cursors' and b.name = 'session cursor cache count' ;
If the session cursor cache count is maxed out, session_cursor_cache_hits is low compared to all parses, and you suspect that the application is re-submitting the same queries for parsing repeatedly, then increasing SESSION_CURSOR_CACHE_COUNT may help with latch contention and give a slight boost to performance. Note that if your application is not resubmitting the same queries for parsing repeatedly, then session_cursor_cache_hits will be low and the session cursor cache count may be maxed out, but caching cursors by session won’t help at all. For example, if your application is using a lot of unsharable SQL, raising this parameter won’t get you anything.
We’ve covered the difference between open cursors and session cached cursors, their initialization parameters, and how to monitor and tune them.
Chen Guang's Blog 