2012-10-26 11gR2 "ADMINISTRATOR'S GUIDE" page 252 - 304

 

Run the following query to obtain a value for SGA_TARGET: -- 获取SGA_TARGET应该设置的值
SELECT (
(SELECT SUM(value) FROM V$SGA) -
(SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)
) "SGA_TARGET"
FROM DUAL;

Note that after starting the database with the result cache disabled, if you use an
ALTER SYSTEM statement to set RESULT_CACHE_MAX_SIZE to a nonzero value but
do not restart the database, querying the value of the RESULT_CACHE_MAX_SIZE
parameter returns a nonzero value even though the result cache is still disabled. The
value of RESULT_CACHE_MAX_SIZE is therefore not the most reliable way to
determine if the result cache is enabled. You can use the following query instead:
SELECT dbms_result_cache.status() FROM dual;
DBMS_RESULT_CACHE.STATUS()
---------------------------------------------
ENABLED
查看数据库中是否有使用RESULT CACHE的语句。

The result cache takes its memory from the shared pool, so if you increase the
maximum result cache size, consider also increasing the shared pool size.
注意，RESULT CACHE从SHARED POOL中“拿”内存，所以，提高RESULT CACHE的同时，提高SHRED POOL内存的大小。

 

There are dynamic performance views that provide PGA memory use statistics. Most
of these statistics are enabled when PGA_AGGREGATE_TARGET is set. -- 与PGA区相关的一些动态性能视图
 -- Statistics on allocation and use of work area memory can be viewed in the
following dynamic performance views:
V$SYSSTAT
V$SESSTAT
V$PGASTAT
V$SQL_WORKAREA
V$SQL_WORKAREA_ACTIVE
 -- The following three columns in the V$PROCESS view report the PGA memory
allocated and used by an Oracle Database process:
PGA_USED_MEM
PGA_ALLOCATED_MEM
PGA_MAX_MEM

Memory Management Data Dictionary Views -- 内存管理的数据字典视图

 

You can also check specific threshold settings with the DBA_THRESHOLDS view. For
example: -- 通过这个语句可以查询THRESHOLDS的设置
SELECT metrics_name, warning_value, critical_value, consecutive_occurrences
FROM DBA_THRESHOLDS
WHERE metrics_name LIKE '%CPU Time%';

Server-Generated Alerts Data Dictionary Views

 

Monitoring Performance -- ADMINISTRATOR'S GUIDE中有一些是PERFORMANCE TUNING GUIDE中没有的
Monitoring Locks -- 锁
The utllockt.sql script displays, in a tree fashion, the sessions in the system that
are waiting for locks and the locks that they are waiting for. The location of this script
file is operating system dependent.
A second script, catblock.sql, creates the lock views that utllockt.sql needs, so
you must run it before running utllockt.sql.
Oracle提供了两个查锁的脚本。catblock.sql和utllock.sql

 

Performance Monitoring Data Dictionary Views -- 关于性能的数据字典视图

 

Viewing Critical Errors with the V$DIAG_CRITICAL_ERROR View
The following example shows the output for the V$DIAG_CRITICAL_ERROR view in
Oracle Database 11g Release 2 (11.2.0.2):  通过这个视图v$diag_critical_error可以查看critical errors
SELECT * FROM V$DIAG_CRITICAL_ERROR;
FACILITY ERROR
---------- ----------------------------------------------------------------
ORA 7445
ORA 4030
ORA 4031
ORA 29740
ORA 255
ORA 355
ORA 356
ORA 239
ORA 240
ORA 494
ORA 3137
ORA 227
ORA 353
ORA 1578
ORA 32701
ORA 32703
ORA 29770
ORA 29771
ORA 445
ORA 25319
OCI 3106
OCI 3113
OCI 3135

 

Running Health Checks Manually -- 手动运行health check
Running Health Checks Using the DBMS_HM PL/SQL Package
The DBMS_HM procedure for running a health check is called RUN_CHECK. To call RUN_
CHECK, supply the name of the check and a name for the run, as follows:
BEGIN
DBMS_HM.RUN_CHECK('Dictionary Integrity Check', 'my_run');
END;
/
To obtain a list of health check names, run the following query:
SELECT name FROM v$hm_check WHERE internal_check='N';
NAME
----------------------------------------------------------------
DB Structure Integrity Check
Data Block Integrity Check
Redo Integrity Check
Transaction Integrity Check
Undo Segment Integrity Check
Dictionary Integrity Check

Viewing Reports Using DBMS_HM -- 通过DBMS_HM查看报告
SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512
SELECT DBMS_HM.GET_RUN_REPORT('HM_RUN_1061') FROM DUAL;
DBMS_HM.GET_RUN_REPORT('HM_RUN_1061')
Run Name : HM_RUN_1061
Run Id : 1061
Check Name : Data Block Integrity Check
Mode : REACTIVE
Status : COMPLETED
Start Time : 2007-05-12 22:11:02.032292 -07:00
End Time : 2007-05-12 22:11:20.835135 -07:00
Error Encountered : 0
Source Incident Id : 7418
Number of Incidents Created : 0
Input Paramters for the Run
BLC_DF_NUM=1
BLC_BL_NUM=64349
Run Findings And Recommendations
Finding
Finding Name : Media Block Corruption
Finding ID : 1065
Type : FAILURE
Status : OPEN
Priority : HIGH
Message : Block 64349 in datafile 1:
is media corrupt
Message : Object BMRTEST1 owned by SYS might be unavailable
Finding
Finding Name : Media Block Corruption
Finding ID : 1071
Type : FAILURE
Status : OPEN
Priority : HIGH
Message : Block 64351 in datafile 1:
'/u01/app/oracle/dbs/t_db1.f' is media corrupt
Message : Object BMRTEST2 owned by SYS might be unavailable

Viewing Reports Using the ADRCI Utility -- 通过ADRCI查看报告
To create and view a checker report using ADRCI:
1. Ensure that operating system environment variables (such as ORACLE_HOME) are
set properly, and then enter the following command at the operating system
command prompt:
ADRCI
The utility starts and displays the following prompt:
adrci>>
Optionally, you can change the current ADR home. Use the SHOW HOMES
command to list all ADR homes, and the SET HOMEPATH command to change the
current ADR home. See Oracle Database Utilities for more information.
2. Enter the following command:
show hm_run
This command lists all the checker runs (stored in V$HM_RUN) registered in the
ADR repository.
3. Locate the checker run for which you want to create a report and note the checker
run name. The REPORT_FILE field contains a filename if a report already exists
for this checker run. Otherwise, generate the report with the following command:
create report hm_run run_name
4. To view the report, enter the following command:
show report hm_run run_name