Open Cursors. This is a list of currently open cursors obtained from the V$OPEN_CURSORS view. It can be instrumental in figuring out the various activities completed by this session, when such cursor information is available.
The number of private SQL areas, and therefore the amount of memory in the PGA they consume, is determined by the intialization parameter OPEN_CURSORS. You should set this parameter based on the SQL your application submits, the number of active users, and the available memory on the server.
Oracle Technology Network offers a detailed analysis of how and when PL/SQL closes cursors in an article titled "Cursor reuse in PL/SQL static SQL." Nested anonymous blocks provide an example of one case in which PL/SQL does not implicitly close cursors. For an interesting discussion of this issue see Jonathan Gennick's article, "Does PL/SQL Implicitly Close Cursors?" at http://gennick.com/open_cursors.html.
• If you declare a cursor in a package at the package level and then open it in a particular block or program, that cursor will stay open until you explicitly close it or until your session closes. Therefore, it is extremely important that you include your CLOSE statement for any packaged cursors as soon as you are done with them (and in the exception section as well), as in the following:
• OPEN my_package.my_cursor;
• ... Do stuff with the cursor
• CLOSE my_package.my_cursor;
• WHEN OTHERS
• CLOSE my_package.my_cursor;
• If you have opened a SELECT FOR UPDATE query, it is especially important to close the cursor as soon as you are done because this query causes row-level locks to be applied.
• You should close a cursor only if it is currently open. You can check a cursor's status with the %ISOPEN cursor attribute before you try to close the cursor:
• IF company_cur%ISOPEN
• CLOSE company_cur;
• END IF;
• If you leave too many cursors open, you may exceed the value set by the database initialization parameter, OPEN_CURSORS. If this happens, you will encounter the dreaded error message:
• ORA-01000: maximum open cursors exceeded
If you get this message, check your usage of package-based cursors to make sure they are closed when no longer needed.
This brings us to another important fact about cursors: there are two ways a cursor can be closed. A soft-closed cursor is one that you can no longer use in your application without reopening it. This is what you get when you close a cursor using a statement such as this one:
or even when an implicit cursor closes automatically. However, PL/SQL does not immediately free the session memory associated with this cursor. Instead, it caches cursors to avoid a soft parse should the cursor be opened again, as often happens. You will see, if you look in the V$OPEN_CURSOR view , that the CLOSE alone does not reduce the count of this session's open cursors.
It turns out that PL/SQL maintains its own "session cursor cache"; that is, it decides when to close a cursor for good. This cache can hold a maximum number of cursors, as specified by the OPEN_CURSORS database initialization parameter. A least-recently-used (LRU) algorithm determines which of the soft-closed cursors need to be hard-closed and hence deallocated.
However, PL/SQL's internal algorithm works optimally only if your programs close their cursors immediately after they are through fetching with them. So remember:
If you explicitly open a cursor, you should explicitly close it ... as soon as you are through using it (but not sooner).
There are a few ways that Oracle allows PL/SQL programmers to intervene in the default behavior. One way you can close all of your session cursors, of course, is to terminate the session! Less drastic ways include:
If you'd like to discover for yourself how much UGA and PGA your current session uses, you can run a query like the following:
SELECT n.name, ROUND(m.value/1024) kbytes
FROM V$STATNAME n, V$MYSTAT m
WHERE n.statistic# = m.statistic#
AND n.name LIKE 'session%memory%';
Here is an example that looks at the memory required by objects in the shared pool immediately after database startup:[*]
[*] If you're wondering why the columns of data do not line up properly with their headings, it's probably because of the severe limitations of DBMS_OUTPUT. If you don't like it, write your own (grab the query from V$SQLAREA after running the package).
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> EXEC DBMS_SHARED_POOL.sizes(minsize => 125)
SIZE(K) KEPT NAME
------- ------ ---------------------------------------------------------------
433 SYS.STANDARD (PACKAGE)
364 SYS.DBMS_RCVMAN (PACKAGE BODY)
249 SYSMAN.MGMT_JOB_ENGINE (PACKAGE BODY)
224 SYS.DBMS_RCVMAN (PACKAGE)
221 SYS.DBMS_STATS_INTERNAL (PACKAGE)
220 SYS.DBMS_BACKUP_RESTORE (PACKAGE)
125 MERGE INTO cache_stats_1$ D USING (select * from table(dbms_sta
ts_internal.format_cache_rows(CURSOR((select dataobj# o, st
atistic# stat, nvl(value, 0) val from gv$segstat where stat
istic# in (0, 3, 5) and obj# > 0 and inst_id = 1) union all
(select obj# o, 7 stat,nvl(sum(num_buf), 0) val from x$kcb
oqh x where inst_id = 1 group by obj#) order by o))) wh
The "minsize => 125" means "show only objects that are 125K or larger." This output shows that the package STANDARD occupies the most shared memory, 433K.[ ]
[ ] There is a bug in older versions of DBMS_SHARED_POOL.sizes that results in the amount's being over-reported by about 2.3%. Oracle's package erroneously computed kilobytes by dividing bytes by 1000 instead of by 1024.
Bytes set aside for the shared pool.
Bytes of memory reserved to hold rows of data from the database (may need to be reduced in order to increase the size of the shared pool).
Bytes of memory reserved for an optional region of memory that holds UGA for shared server connections. This prevents the variable portion of the UGA from competing for use of the shared pool.
Bytes used by the Java memory manager.
Bytes used by the Oracle Streams feature.
If that's too much to fuss with, and you are using Oracle Database 10g or later, the DBA can turn on the Automatic Shared Memory Management features:
Set to a nonzero number of bytes, which indicates the size of the SGA from which Oracle will automatically allocate the cache and pools indicated above.
Total amount of memory used by all of the server processes in the instance. Generally, it should be equal to the amount of server memory available to Oracle minus the SGA size.
An example of an initSID.ora file follows:
[View full width]
#initialization parameter file: initmydb1.ora
db_name = "mydb1"
instance_name = mydb1
control_files = ("/mydatabases/mydb1/control01.ctl", "/mydatabases2/mydb1/control02.ctl",
open_cursors = 100
shared_pool_size = 360407040
large_pool_size = 614400
java_pool_size = 52428800
processes = 220
background_dump_dest = /mydatabases/mydb1/logs/bdump
core_dump_dest = /mydatabases/mydb1/logs/cdump
user_dump_dest = /mydatabases/mydb1/logs/udump
db_block_size = 8192
compatible = "9.1.0"
sort_area_size = 65536
sort_area_retained_size = 65536
OPEN_CURSORS : The maximum number of cursors that you want to have opened in your instance at any given time.
Modifying the SPFILE
The SPFILE can be modified; however, it has to be modified indirectly, either through the OEM interface or from the SQLPLUS command line.
Modifying the SPFILE Using OEM
To modify the SPFILE using the GUI OEM interface, you launch the console either in standalone mode or by connecting to an existing repository. Expand the database that you are working on from the Database folder, expand the Instance folder, and click on Configuration.
The interface can be launched both by issuing the command oemapp console at the command line and by launching through the Windows Start menu.
Within the General tab, click the All Initialization Parameters button to see the parameters that you can work with. They are viewed in alphabetical order. If you have logged in as SYSDBA, you can modify both the working instance parameters as well as the SPFILE values, both the dynamic parameters as well as the static ones. If you don't log in as someone with SYSDBA authority, you can alter only dynamic parameters.
Modify the parameter's value column and click OK.
To alter the contents of the SPFILE from the SQL prompt command line, you could enter the following command (this alters the running session and makes the change in the SPFILE):
Alter system set open_cursors=4000 scope=both;
Table 4.2 shows the parameters that the ALTER SYSTEM command can take when you are using an SPFILE.
The change is made in the server parameter file (SPFILE) only, but the changes will not take place until the next startup.