| 网站地图 |
CopyRight © 2004-2007 软讯网络 All Rigths Reserved.
|
第二章 工作采集、诊断过程
Oracle10g提供了一个图形化的界面（通过OEM），使这个工具使用起来非常简单。下面这里介绍一下如何通过sqlplus使用这个工具。这个工具的使用非常简单，它是不需要安装的。
第一步：创建测试用的表
SQL> CREATE TABLE bigtab AS SELECT rownum as "id", a.* FROM dba_objects a;
Table created.
SQL> create table smalltab as select rownum as "id", a.* FROM dba_tables a;
Table created.
SQL> ALTER TABLE bigtab MODIFY (empno NUMBER);
Table altered.
SQL> DECLARE
2 n NUMBER;
3 BEGIN
4 FOR n IN 1..100
5 LOOP
6 INSERT INTO bigtab SELECT rownum as "id", a.* FROM dba_objects a;
7 COMMIT;
8 END LOOP;
9 END;
/
PL/SQL procedure successfully completed.
第二步：采集一次工作量快照
SQL> begin
2 dbms_workload_repository.create_snapshot('TYPICAL');
3 end;
4 /
PL/SQL procedure successfully completed.
第三步：进行一些高负荷操作
DECLARE
v_var number;
BEGIN
FOR n IN 1..6
LOOP
select count(*) into v_var from bigtab b, smalltab a;
END LOOP;
END;
/
PL/SQL procedure successfully completed.
第四步：再次采集一次工作量快照
要注意的是：两次快照之间的间隔时间必须足够（一般推荐30分钟左右），否则得到的ADDM报告中就会提示：THERE WAS NOT ENOUGH DATABASE TIME FOR ADDM ANALYSIS.
SQL> begin
2 dbms_workload_repository.create_snapshot('TYPICAL');
3 end;
4 /
PL/SQL procedure successfully completed.
第五步：创建一个优化诊断任务并执行
先获取到两次快照的ID:
SQL> select snap_id from
2 (SELECT * FROM dba_hist_snapshot
3 ORDER BY snap_id desc)
4 where rownum <=2;
SNAP_ID
--------
66
65
然后创建优化任务，并执行。
DECLARE
task_name VARCHAR2(30) := 'DEMO_ADDM01';
task_desc VARCHAR2(30) := 'ADDM Feature Test';
task_id NUMBER;
BEGIN
dbms_advisor.create_task('ADDM', task_id, task_name, task_desc, null);
dbms_advisor.set_task_parameter(task_name, 'START_SNAPSHOT', 65);
dbms_advisor.set_task_parameter(task_name, 'END_SNAPSHOT', 66);
dbms_advisor.set_task_parameter(task_name, 'INSTANCE', 1);
dbms_advisor.set_task_parameter(task_name, 'DB_ID', 1712582900);
dbms_advisor.execute_task(task_name);
END;
/
PL/SQL procedure successfully completed.
其中，set_task_parameter是用来设置任务参数的。START_SNAPSHOT是起始快照ID，END_SNAPSHOT是结束快照ID，INSTANCE是实例号，对于单实例，一般是1，在RAC环境下，可以通过查询视图v$instance得到，DB_ID是数据库的唯一识别号，可以通过查询v$database查到。
|
|
|
第六步：查看优化建议结果
通知函数dbms_advisor.get_task_report可以得到优化建议结果。
SQL> SET LONG 1000000 PAGESIZE 0 LONGCHUNKSIZE 1000
SQL> COLUMN get_clob FORMAT a80
SQL> SELECT dbms_advisor.get_task_report('DEMO_ADDM01', 'TEXT', 'ALL') FROM DUAL;
DBMS_ADVISOR.GET_TASK_REPORT('
--------------------------------------------------------------------------------
DETAILED ADDM REPORT FOR TASK 'DEMO_ADDM01' WITH ID 243
-------------------------------------------------------
Analysis Period: 23-NOV-2005 from 15:02:27 to 16:06:42
Database ID/Instance: 1712582900/1
Database/Instance Names: EDGAR/edgar
Host Name: HUANGED
Database Version: 10.2.0.1.0
Snapshot Range: from 65 to 66
Database Time: 1463 seconds
Average Database Load: .4 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 100% impact (1463 seconds)
-------------------------------------
Significant virtual memory paging was detected on the host operating system.
RECOMMENDATION 1: Host Configuration, 100% benefit (1463 seconds)
ACTION: Host operating system was experiencing significant paging but no
particular root cause could be detected. Investigate processes that
do not belong to this instance running on the host that are consuming
significant amount of virtual memory. Also consider adding more
physical memory to the host.
FINDING 2: 100% impact (1463 seconds)
-------------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 68% benefit (998 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "064wqx7c5b81z". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID 064wqx7c5b81z
DECLARE
v_var number;
BEGIN
FOR n IN 1..10000
LOOP
select count(*) into v_var from bigtab b, smalltab a;
END LOOP;
END;
RECOMMENDATION 2: SQL Tuning, 67% benefit (986 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"fvqfghq71cqns".
RELEVANT OBJECT: SQL statement with SQL_ID fvqfghq71cqns and
PLAN_HASH 3281046854
SELECT COUNT(*) FROM BIGTAB B, SMALLTAB A
RATIONALE: SQL statement with SQL_ID "fvqfghq71cqns" was executed 6
times and had an average elapsed time of 166 seconds.
FINDING 3: 69% impact (1002 seconds)
------------------------------------
Time spent on the CPU by the instance was responsible for a substantial part
of database time.
RECOMMENDATION 1: SQL Tuning, 67% benefit (986 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"fvqfghq71cqns".
RELEVANT OBJECT: SQL statement with SQL_ID fvqfghq71cqns and
PLAN_HASH 3281046854
SELECT COUNT(*) FROM BIGTAB B, SMALLTAB A
RATIONALE: SQL statement with SQL_ID "fvqfghq71cqns" was executed 6
times and had an average elapsed time of 166 seconds.
RATIONALE: Average CPU used per execution was 162 seconds.
RECOMMENDATION 2: SQL Tuning, 2.1% benefit (30 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "2b064ybzkwf1y". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID 2b064ybzkwf1y
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
RATIONALE: SQL statement with SQL_ID "2b064ybzkwf1y" was executed 125
times and had an average elapsed time of 0.26 seconds.
RATIONALE: Average CPU used per execution was 0.24 seconds.
FINDING 4: 2.2% impact (33 seconds)
-----------------------------------
PL/SQL execution consumed significant database time.
RECOMMENDATION 1: SQL Tuning, 2.2% benefit (33 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "2b064ybzkwf1y". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID 2b064ybzkwf1y
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
RATIONALE: SQL statement with SQL_ID "2b064ybzkwf1y" was executed 125
times and had an average elapsed time of 0.26 seconds.
RATIONALE: Average time spent in PL/SQL execution was 0.26 seconds.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ADDITIONAL INFORMATION
----------------------
Wait class "Application" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
Wait class "Network" was not consuming significant database time.
Wait class "User I/O" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.
Hard parsing of SQL statements was not consuming significant database time.
The analysis of I/O performance is based on the default assumption that the
average read time for one database block is 10000 micro-seconds.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TERMINOLOGY
-----------
DATABASE TIME: This is the ADDM's measurement of throughput. From the user's
point of view: this is the total amount of time spent by users waiting for
a response from the database after issuing a call (not including
networking). From the database instance point of view: this is the total
time spent by forground processes waiting for a database resource (e.g.,
read I/O), running on the CPU and waiting for a free CPU (run-queue). The
target of ADDM analysis is to reduce this metric as much as possible,
thereby reducing the instance's response time.
AVERAGE DATABASE LOAD: At any given time we can count how many users (also
called 'Active Sessions') are waiting for an answer from the instance. This
is the ADDM's measurement for instance load. The 'Average Database Load' is
the average of the the load measurement taken over the entire analysis
period. We get this number by dividing the 'Database Time' by the analysis
period. For example, if the analysis period is 30 minutes and the 'Database
Time' is 90 minutes, we have an average of 3 users waiting for a response.
IMPACT: Each finding has an 'Impact' associated with it. The impact is the
portion of the 'Database Time' the finding deals with. If we assume that
the problem described by the finding is completely solved, then the
'Database Time' will be reduced by the amount of the 'Impact'.
BENEFIT: Each recommendation has a 'benefit' associated with it. The ADDM
analysis estimates that the 'Database Time' can be reduced by the 'benefit'
amount if all the actions of the recommendation are performed.
说明：
其中第五步到第六步可以直接执行$ORACLE_HOME/rdbms/admin/addmrpt.sql来得到，这个脚本的执行过程和statspack脚本执行过程类似：
SQL> @addmrpt
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1712582900 EDGAR 1 edgar
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1712582900 1 EDGAR edgar HUANGED
Using 1712582900 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Listing the last 3 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
edgar EDGAR 7 22 Nov 2005 00:00 1
... ...
64 23 Nov 2005 15:02 1
65 23 Nov 2005 16:00 1
66 23 Nov 2005 16:06 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 65
Begin Snapshot Id specified: 66
Enter value for end_snap: 66
End Snapshot Id specified: 66
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is addmrpt_1_65_66.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name addmrpt_1_65_66.txt
Running the ADDM analysis on the specified pair of snapshots ...
Generating the ADDM report for this analysis ...
... ...
此外，如果是RAC环境下，可以执行$ORACLE_HOME/rdbms/admin/addmrpti.sql，这脚本的执行，会多出要求输入DB ID和instance ID的要求。
| |