Check database parameter
To check database parameter in general:
SQL> show parameter cursors ## list all parameter with name .*cursors.*
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 300
session_cached_cursors integer 50
For more details use v$parameter view:
SQL> select NAME, VALUE, DEFAULT_VALUE, ISDEFAULT, ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISMODIFIED, ISADJUSTED, DESCRIPTION from v$parameter where name like '%cursor%';
"NAME","VALUE","DEFAULT_VALUE","ISDEFAULT","ISSES_MODIFIABLE","ISSYS_MODIFIABLE","ISMODIFIED","ISADJUSTED","DESCRIPTION"
"cursor_space_for_time","FALSE","FALSE","TRUE","FALSE","FALSE","FALSE","FALSE","use more memory in order to get faster execution"
"session_cached_cursors","50","50","TRUE","TRUE","DEFERRED","FALSE","FALSE","Number of cursors to cache in a session."
"cursor_sharing","EXACT","EXACT","TRUE","TRUE","IMMEDIATE","FALSE","FALSE","cursor sharing mode"
"open_cursors","300","50","FALSE","FALSE","IMMEDIATE","FALSE","FALSE","max # cursors per session"
Update database parameter
SQL> ALTER SYSTEM SET parameter_name = ## scope = ##
scope:
- spfile: parameter updated in spfile (server parameter file, holding persistent initialization parameters to start instance) => restart database required
- memory: applied in memory only (dynamic parameter only)
- both: applied in parameter file + memory
To check if parameter is static or dynamic: ISSYS_MODIFIABLE in v$parameter
- IMMEDIATE: change takes effect immediately, no need to bounce the instance
- FALSE: restart instance required
- DEFERRED:
Example:
SQL> ALTER SYSTEM SET open_cursors=100 scope=both;
Increase parameters for
1) sessions, processes, transactions
Issue: connection timeouts
Errors in alert log:
ORA-00020: maximum number of processes (<limit>) exceeded ...
ORA-00020: No more process state objects available ...
Check Current utilization:
SQL> col RESOURCE_NAME for A50
SQL> set lines 200
SQL> SELECT RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION, LIMIT_VALUE FROM V$RESOURCE_LIMIT WHERE RESOURCE_NAME IN ('sessions', 'processes');
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
-------------------------------------------------- ------------------- --------------- ----------------------------------------
processes 494 494 500
sessions 503 555 784
Sessions and transactions are derived from processes parameter, Oracle calculates derived parameter accordingly. Recommended settings:
processes = x
sessions = x * 1.1 + 5
transactions = sessions * 1.1
2) SGA
Issue: low hit ratio buffer cache, “Undersized SGA” findings in AWR reports, physical RAM was increased on db host due to swapping/high consumption, …
Check current settings/advisor:
SQL> show parameter sga
SQL> show sga
SQL> select sum(value)/1024/1024/1024 as "TOTAL SGA(GB)" from v$sga;
Oracle estimation for increasing SGA:
SQL> SELECT sga_size, sga_size_factor, estd_db_time_factor, ESTD_PHYSICAL_READS, ESTD_BUFFER_CACHE_SIZE from v$sga_target_advice ORDER BY sga_size ASC;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS ESTD_BUFFER_CACHE_SIZE
---------- --------------- ------------------- ------------------- ----------------------
1152 .5 2.6505 693144366 288
1728 .75 1.368 399508604 480
2304 1 1 235587100 1056
2880 1.25 .6835 94611779 1632
3456 1.5 .6597 84033919 1920
4032 1.75 .6597 84033919 2112
4608 2 .6597 84033919 2688
SIZE_FACTOR=1 (2304 MB) ==> current size. Check for ESTD_DB_TIME_FACTOR, should be optimally low.
Oracle estimates that increasing to 3456 MB (size factor 1.5) would eliminate ~35% of physical reads.
Oracle estimation for increasing buffer cache:
SQL> SELECT size_factor, size_for_estimate, estd_physical_reads, estd_physical_read_time, estd_pct_of_db_time_for_reads FROM v$db_cache_advice;
SIZE_FACTOR SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READS ESTD_PHYSICAL_READ_TIME ESTD_PCT_OF_DB_TIME_FOR_READS
----------- ----------------- ------------------- ----------------------- -----------------------------
....
.9231 960 257639556 135834 58.5
1 1040 235291366 124036 53.4
1.0154 1056 229796095 121134 52.2
1.1077 1152 198179929 104443 45
1.2 1248 170957722 90071 38.8
1.2923 1344 164101340 86451 37.2
1.3846 1440 125013187 65815 28.3
1.4769 1536 104398860 54932 23.7
1.5692 1632 92278765 48533 20.9
1.6615 1728 85162586 44776 19.3
1.7538 1824 82372592 43303 18.6
1.8462 1920 81954658 43083 18.6
SIZE_FACTOR=1 (1040 MB) ==> current size. Oracle estimates that increasing to 1632 MB (size factor 1.5) would eliminate ~40% of physical reads.
3) open_cursors
OPEN_CURSORS: Maximum open cursor each session can have open (per session).
SESSION_CACHED_CURSORS: if 0 (default), no cursors will be cached for session. Setting value (no relation to open_cursors parameter) can reduce SQL parse times and increase execution time.
Set open cursors value high enough to prevent ZONOS services running out of cursors. When close to the limit increase.
If application has cursor leak (opening cursors and not closing it when done): Consider to set low enough to prevent sessions from opening excessive number of cursors (and filling up library (SQL) cache, or clogging CPU with thousands of parse requests). (and raise product bug ….)
Issue: ORA-01000: maximum open cursors exceeded
Check current settings/usage:
SQL> show parameter cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 300
session_cached_cursors integer 50
MAX value used by any session (if value is reached to limit consider to increase):
SQL> col PARAMETER_VALUE_DEFINE for a30
SQL> select max(a.value) as Session_Max_open_cursor, p.value as Parameter_Value_define
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and p.name = 'open_cursors'
group by p.value;
SESSION_MAX_OPEN_CURSOR PARAMETER_VALUE_DEFINE
----------------------- ------------------------------
66 300
Usage in percent (if usage >95% consider to increase):
SQL> select 'open_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 sys.v_$statname n, sys.v_$sesstat s
where n.name = 'opened cursors current'
and s.statistic# = n.statistic#),
(select value from sys.v_$parameter where name = 'open_cursors');
PARAMETER VALUE USAGE
------------ -------------------- -----
open_cursors 300 22%
