一、建表
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.
阅读(2196) | 评论(0) | 转发(0) |
