我的猪窝

附录1:Statspack Report模块解释(1)

1.1 附录1：STATSPACK REPORT模块解释 5
1.1.1 Head Information 5
1.1.1.1 原始算法 5
1.1.1.2 关键字说明 6
1.1.1.3 参数说明 7
1.1.1.1.1 SESSION_CACHED_CURSORS 7
1.1.1.1.2 OPEN_CURSORS 7
1.1.2 Cache Sizes (end) 7
1.1.2.1 原始算法 8
1.1.2.2 关键字说明 8
1.1.2.3 参数说明 8
1.1.1.1.3 DB_CACHE_SIZE 8
1.1.1.1.4 DB_KEEP_CACHE_SIZE 9
1.1.1.1.5 DB_RECYCLE_CACHE_SIZE 9
1.1.1.1.6 DB_nK_CACHE_SIZE 9
1.1.1.1.7 DB_BLOCK_SIZE 9
1.1.1.1.8 SHARED_POOL_SIZE 10
1.1.1.1.9 LOG_BUFFER 10

1.1 附录1：Statspack Report模块解释
1.1.1 Head Information
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
XXX 3965530941 Xdb 1 9.2.0.6.0 NO XXX

Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 1811 04-11月-05 17:00:04 141 373.2
End Snap: 1812 04-11月-05 18:00:02 149 354.2
Elapsed: 59.97(mins)

1.1.1.1 原始算法
我们可通过如下语句直接从数据库获取更多的DB和instance信息：
 SELECT dbid,name,CREATEd,log_mode,checkpoint_change#,archive_change#,controlfile_created,controlfile_change# FROM v$database;
 SELECT instance_number,instance_name,host_name,version,startup_time,status,PARALLEL,archiver,database_status FROM v$instance;

也可直接从perfstat表查询：
 SELECT DISTINCT
dbid "DB Id"
, instance_number "Inst Num"
, db_name "DB Name"
, instance_name "Instance"
, host_name "Host"
, version "Release"
, PARALLEL "Cluster"
FROM stats$database_instance;
整个statspack report报告一行记录。

 SELECT snap_id,TO_CHAR(snap_time,'yyyy-mm-dd hh24:mi:ss') "snap_time" FROM STATS$SNAPSHOT where snap_id = &Snap_Id;
开始采集点和结束采集点各report一行记录。
 SELECT value "Sessions" FROM STATS$SYSSTAT WHERE name = 'logons current' AND snap_id = &Snap_Id;
开始采集点和结束采集点各report一行记录。
 SELECT C.value/S.value "Curs/Sess" FROM STATS$SYSSTAT S,STATS$SYSSTAT C
WHERE S.name = 'logons current'
AND C.name = 'opened cursors current'
AND S.snap_id = &Snap_Id
AND S.snap_id = C.snap_id;
开始采集点和结束采集点各report一行记录。

STATS$SYSSTAT 表信息在采集点时刻由v$sysstat直接填充。
STATS$DATABASE_INSTANCE 表信息在采集点时刻由v$instance和v$database直接填充。
每个库STATS$DATABASE_INSTANCE 只有一行记录。

1.1.1.2 关键字说明
Begin Snap：开始采集点的信息。
End Snap ：结束采集点的信息。
Elapsed ：两次采集点之间的时间跨度（during this sample period）。
Sessions ：表示采集点的会话数。
Curs/Sess ：表示采集点平均每个会话打开的游标数。

该部分头信息，提供了我们很多关于statspack 本次report的有用的信息。
Elapsed最好在60分钟到10分钟之间。
可以根据Curs/Sess，合理设置初始化参数SESSION_CACHED_CURSORS，可以提高性能。

v$instance.PARALLEL ：(YES/NO) Indicates whether the instance is mounted in cluster database
mode
v$sysstat.name = 'logons current' ： Total number of current logons. Useful only in V$SYSSTAT.
v$sysstat.name = 'opened cursors current' ：Total number of current open cursors

Cursors （from A96524）
A cursor is a handle or name for a private SQL area--an area in memory in which a parsed statement and other information for processing the statement are kept.
Although most Oracle users rely on the automatic cursor handling of the Oracle utilities, the programmatic interfaces offer application designers more control over cursors. In application development, a cursor is a named resource available to a program and can be used specifically to parse SQL statements embedded within the application.
Each user session can open multiple cursors up to the limit set by the initialization parameter OPEN_CURSORS. However, applications should close unneeded cursors to conserve system memory. If a cursor cannot be opened due to a limit on the number of cursors, then the database administrator can alter the OPEN_CURSORS initialization parameter.
Some statements (primarily DDL statements) require Oracle to implicitly issue recursive SQL statements, which also require recursive cursors. For example, a CREATE TABLE statement causes many updates to various data dictionary tables to record the new table and columns. Recursive calls are made for those recursive cursors; one cursor can run several recursive calls. These recursive cursors also use shared SQL areas.

1.1.1.3 参数说明
1.1.1.1.1 SESSION_CACHED_CURSORS
Parameter type Integer
Default value 0
Parameter class Dynamic: ALTER SESSION
Range of values 0 to operating system-dependent
Real Application Clusters Multiple instances can have different values.

SESSION_CACHED_CURSORS lets you specify the number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor. Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed.

1.1.1.1.2 OPEN_CURSORS
Parameter type Integer
Default value 50
Parameter class Dynamic: ALTER SYSTEM
Range of values 1 to 4294967295 (4 GB -1)

OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors. This parameter also constrains the size of the PL/SQL cursor cache which PL/SQL uses to avoid having to reparse as statements are reexecuted by a user.

It is important to set the value of OPEN_CURSORS high enough to prevent your application from running out of open cursors. The number will vary from one application to another. Assuming that a session does not open the number of cursors specified by OPEN_CURSORS, there is no added overhead to setting this value higher than actually needed.

1.1.2 Cache Sizes (end)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 5,120M Std Block Size: 8K
Shared Pool Size: 800M Log Buffer: 3,072K

1.1.2.1 原始算法
（内存信息在Statspack Report里以end snap id查询）：
 SELECT snap_id,DECODE(name,'db_block_buffers','Buffer Cache (M)','db_block_size','Std Block Size (K)',
'shared_pool_size','Shared Pool Size (M)','log_buffer','Log Buffer (M)'),
DECODE(name,'db_block_size',value/1024,value/1024/1024) "value"
FROM STATS$PARAMETER
WHERE name IN ('db_block_buffers','db_block_size','shared_pool_size','log_buffer')
AND snap_id = &End_Snap_Id;

如果'Buffer Cache (M)' 为0，则使用如下语句计算'Buffer Cache (M)'：
 SELECT 'Buffer Cache (M)',SUM(value)/1024/1024 FROM STATS$PARAMETER
WHERE name IN ('db_cache_size','db_keep_cache_size','db_recycle_cache_size','db_2k_cache_size','db_4k_cache_size',
'db_8k_cache_size','db_16k_cache_size','db_32k_cache_size')
AND snap_id = &End_Snap_Id;

STATS$PARAMETER 的信息在采集点由v$system_parameter直接填充。

1.1.2.2 关键字说明
Buffer Cache ：包含了default、keep、recyle及其他块大小的数据缓冲的总大小，单位M。
Std Block Size ：缺省数据块大小，单位K。
Shared Pool Size ：共享缓冲池大小，单位M。
Log Buffer ：日志缓冲池大小，单位K。

该部分信息，粗略的提供了数据库的一些配置信息，我们可根据经验，能快速粗略的定位在内存分配上是否存在明显的性能问题。
理解report的一个重要部分，这部分内存信息，是结束采集点时刻的信息。

1.1.2.3 参数说明
1.1.1.1.3 DB_CACHE_SIZE
Parameter type Big integer
Syntax DB_CACHE_SIZE = integer [K | M | G]
Default value 48 MB, rounded up to the nearest granule size
Parameter class Dynamic: ALTER SYSTEM

DB_CACHE_SIZE specifies the size of the DEFAULT buffer pool for buffers with the primary block size (the block size defined by the DB_BLOCK_SIZE parameter).
The value must be at least the size of one granule (smaller values are automatically rounded up to the granule size). A value of zero is illegal because zero is the size of the DEFAULT pool for the standard block size, which is the block size for the SYSTEM tablespace.

1.1.1.1.4 DB_KEEP_CACHE_SIZE
Parameter type Big integer
Syntax DB_KEEP_CACHE_SIZE = integer [K | M | G]
Default value 0 (KEEP cache is not configured by default)
Parameter class Dynamic: ALTER SYSTEM
Range of values Minimum: the granule size
Maximum: operating system-dependent
DB_KEEP_CACHE_SIZE specifies the size of the KEEP buffer pool. The size of the buffers in the KEEP buffer pool is the primary block size (the block size defined by the DB_BLOCK_SIZE parameter).

1.1.1.1.5 DB_RECYCLE_CACHE_SIZE
Parameter type Big integer
Syntax DB_RECYCLE_CACHE_SIZE = integer [K | M | G]
Default value 0 (RECYCLE cache is not configured by default)
Parameter class Dynamic: ALTER SYSTEM
Range of values Minimum: the granule size
Maximum: operating system-dependent
DB_RECYCLE_CACHE_SIZE specifies the size of the RECYCLE buffer pool. The size of the buffers in the RECYCLE pool is the primary block size (the block size defined by the DB_BLOCK_SIZE parameter).

1.1.1.1.6 DB_nK_CACHE_SIZE
Parameter type Big integer
Syntax DB_[2 | 4 | 8 | 16 | 32]K_CACHE_SIZE = integer [K | M | G]
Default value 0 (additional block size caches are not configured by default)
Parameter class Dynamic: ALTER SYSTEM
Range of values Minimum: the granule size
Maximum: operating system-dependent
DB_nK_CACHE_SIZE (where n = 2, 4, 8, 16, 32) specifies the size of the cache for the nK buffers. You can set this parameter only when DB_BLOCK_SIZE has a value other than nK. For example, if DB_BLOCK_SIZE=4096, then it is illegal to specify the parameter DB_4K_CACHE_SIZE (because the size for the 4 KB block cache is already specified by DB_CACHE_SIZE).
Do not set this parameter to zero if there are any online tablespaces with an nK block size.
Platform-specific block size restrictions apply. For example, you cannot set DB_32K_CACHE_SIZE if the maximum block size on the platform is less than 32 KB. Also, you cannot set DB_2K_CACHE_SIZE if the minimum block size is greater than 2 KB.

1.1.1.1.7 DB_BLOCK_SIZE
Parameter type Integer
Default value 2048
Parameter class Static
Range of values 2048 to 32768, but your operating system may have a narrower range
Real Application Clusters
You must set this parameter for every instance, and multiple instances must have the same value.

--------------------------------------------------------------------------------
Caution:
Set this parameter at the time of database creation. Do not alter it afterward.
--------------------------------------------------------------------------------

DB_BLOCK_SIZE specifies the size (in bytes) of Oracle database blocks. Typical values are 2048 and 4096. The value for DB_BLOCK_SIZE in effect at the time you create the database determines the size of the blocks. The value must remain set to its initial value.

If you are using Oracle9i Real Application Clusters, then this parameter affects the maximum value of the FREELISTS storage parameter for tables and indexes. Oracle uses one database block for each freelist group. Decision support system (DSS) and data warehouse database environments tend to benefit from larger block size values.

1.1.1.1.8 SHARED_POOL_SIZE
Parameter type Big integer
Syntax SHARED_POOL_SIZE = integer [K | M | G]
Default value 32-bit platforms: 8 MB, rounded up to the nearest granule size
64-bit platforms: 64 MB, rounded up to the nearest granule size
Parameter class Dynamic: ALTER SYSTEM
Range of values Minimum: the granule size
Maximum: operating system-dependent

SHARED_POOL_SIZE specifies (in bytes) the size of the shared pool. The shared pool contains shared cursors, stored procedures, control structures, and other structures. If you set PARALLEL_AUTOMATIC_TUNING to false, then Oracle also allocates parallel execution message buffers from the shared pool. Larger values improve performance in multi-user systems. Smaller values use less memory.

You can monitor utilization of the shared pool by querying the view V$SGASTAT.

1.1.1.1.9 LOG_BUFFER
Parameter type Integer
Default value 512 KB or 128 KB * CPU_COUNT, whichever is greater
Parameter class Static
Range of values Operating system-dependent

LOG_BUFFER specifies the amount of memory (in bytes) that Oracle uses when buffering redo entries to a redo log file. Redo log entries contain a record of the changes that have been made to the database block buffers. The LGWR process writes redo log entries from the log buffer to a redo log file.

In general, larger values for LOG_BUFFER reduce redo log file I/O, particularly if transactions are long or numerous. In a busy system, a value 65536 or higher is reasonable.