oracle主目录自动检测,ORACLE ADDM数据库自动诊断测试

一、建表

SQL> create table bigtab tablespace jerry as select rownum as id,a.* from dba_objects a;

Table created

SQL> create table smalltab tablespace jerry as select rownum as id,a.* from dba_objects a;

Table created

二、插入数据

SQL> declare

2  num number;

3  begin

4  for num in 1..100 loop

5  insert into bigtab select rownum as id,a.* from dba_objects a ;

6  commit;

7  end loop;

8  end;

9  /

PL/SQL procedure successfully completed

三、采集一次工作快照

SQL> begin

2  dbms_workload_repository.create_snapshot('TYPICAL');

3  end;

4  /

PL/SQL procedure successfully completed

四、进行一些负荷操作

SQL> set timing on;

SQL> declare

2  v_var number;

3  begin

4  for n in 1..1 loop

5  select count(*) into v_var from bigtab b,smalltab a;

6  end loop;

7  end;

8  /

PL/SQL procedure successfully completed

五、隔一段时间(半小时)采集一次工作快照

SQL> begin

2  dbms_workload_repository.create_snapshot('TYPICAL');

3  end;

4  /

PL/SQL procedure successfully completed

六、创建一个优化任务并执行

6.1 查询两个 snap_id

SQL> select snap_id from (select * from dba_hist_snapshot order by snap_id desc) where rownum <=2;

SNAP_ID

----------

328

327

6.2查询DBID

SQL> select dbid from v$database;

DBID

----------

1310729769

6.3 创建任务并执行

SQL> declare

2  task_name varchar2(30) := 'DEMO_ADDM01';

3  task_desc varchar2(30) := 'ADDM Feature Test';

4  task_id number;

5  begin

6  dbms_advisor.create_task('ADDM',task_id,task_name,task_desc,null);

7  dbms_advisor.set_task_parameter(task_name,'START_SNAPSHOT',327);

8  dbms_advisor.set_task_parameter(task_name,'END_SNAPSHOT',328);

9  dbms_advisor.set_task_parameter(task_name,'INSTANCE',1);

10  dbms_advisor.set_task_parameter(task_name,'DB_ID',1310729769);

11  dbms_advisor.execute_task(task_name);

12  end;

13  /

PL/SQL procedure successfully completed

七、查询建议结果

SQL> set long 1000000 pagesize 0 longchunksize 1000

SQL> column get_clob fromat a80

SQL> select dbms_advisor.get_task_report('DEMO_ADDM01','TEXT','ALL') from dual;

DBMS_ADVISOR.GET_TASK_REPORT('

--------------------------------------------------------------------------------

ADDM Report for Task 'DEMO_ADDM01'

----------------------------------

Analysis Period

---------------

AWR snapshot range from 327 to 328.

Time period starts at 05-JAN-14 01.55.26 PM

Time period ends at 05-JAN-14 02.16.28 PM

Analysis Target

---------------

Database 'ORCL' with DB ID 1310729769.

Database version 11.2.0.1.0.

ADDM performed an analysis of instance orcl, numbered 1 and hosted at

pc-centos.

Activity During the Analysis Period

-----------------------------------

Total database time was 1149 seconds.

The average number of active sessions was .91.

Summary of Findings

-------------------

Description                               Active Sessions      Recommendation

Percent of Activity

----------------------------------------  -------------------  --------------

1  Top Segments by "User I/O" and "Cluster"  .07 | 8              2

2  Top SQL Statements                        .06 | 6.9            1

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Findings and Recommendations

----------------------------

Finding 1: Top Segments by "User I/O" and "Cluster"

Impact is .07 active sessions, 8% of total activity.

----------------------------------------------------

Individual database segments responsible for significant "User I/O" and

"Cluster" waits were found.

Recommendation 1: Segment Tuning

Estimated benefit is .04 active sessions, 4.45% of total activity.

------------------------------------------------------------------

Action

Investigate application logic involving I/O on TABLE "JERRY.SMALLTAB"

with object ID 76847.

Related Object

Database object with ID 76847.

Rationale

The I/O usage statistics for the object are: 1 full object scans, 298

physical reads, 0 physical writes and 0 direct reads.

Recommendation 2: Segment Tuning

Estimated benefit is .03 active sessions, 3.56% of total activity.

------------------------------------------------------------------

Action

Run "Segment Advisor" on TABLE "JERRY.BIGTAB" with object ID 76846.

Related Object

Database object with ID 76846.

Action

Investigate application logic involving I/O on TABLE "JERRY.BIGTAB" with

object ID 76846.

Related Object

Database object with ID 76846.

Action

Look at the "Top SQL Statements" finding for SQL statements consuming

significant I/O on this segment. For example, the SELECT statement with

SQL_ID "5v0mjsunmm5s4" is responsible for 100% of "User I/O" and

"Cluster" waits for this segment.

Rationale

The I/O usage statistics for the object are: 103 full object scans,

11041873 physical reads, 1 physical writes and 11041724 direct reads.

Symptoms That Led to the Finding:

---------------------------------

Wait class "User I/O" was consuming significant database time.

Impact is .07 active sessions, 8% of total activity.

Finding 2: Top SQL Statements

Impact is .06 active sessions, 6.9% of total activity.

------------------------------------------------------

SQL statements consuming significant database time were found. These

statements offer a good opportunity for performance improvement.

Recommendation 1: SQL Tuning

Estimated benefit is .06 active sessions, 6.9% of total activity.

-----------------------------------------------------------------

Action

Run SQL Tuning Advisor on the SELECT statement with SQL_ID

"5v0mjsunmm5s4".

Related Object

SQL statement with SQL_ID 5v0mjsunmm5s4.

SELECT COUNT(*) FROM BIGTAB B,SMALLTAB A

Rationale

The SQL spent 100% of its database time on CPU, I/O and Cluster waits.

This part of database time may be improved by the SQL Tuning Advisor.

Rationale

Database time for this SQL was divided as follows: 100% for SQL

execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java

execution.

Rationale

SQL statement with SQL_ID "5v0mjsunmm5s4" was executed 1 times and had

an average elapsed time of 1105 seconds.

Rationale

I/O and Cluster wait for TABLE "JERRY.BIGTAB" with object ID 76846

consumed 100% of the database time spent on this SQL statement.

Rationale

Top level calls to execute the PL/SQL statement with SQL_ID

"8acps6bm4uqdn" are responsible for 100% of the database time spent on

the SELECT statement with SQL_ID "5v0mjsunmm5s4".

Related Object

SQL statement with SQL_ID 8acps6bm4uqdn.

declare

v_var number;

begin

for n in 1..6 loop

select count(*) into v_var from bigtab b,smalltab a;

end loop;

end;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Additional Information

----------------------

Miscellaneous 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.

CPU was not a bottleneck for the instance.

Wait class "Network" 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 database's maintenance windows were active during 99% of the analysis

period.

阅读(1142) | 评论(0) | 转发(0) |