通過ADDM進行SQL調優

declare

l_task_id varchar2(20);

l_sql varchar2(2000);

begin

l_sql := 'SELECT COUNT(*) QTY FROM SFCS_DEFECTS WHERE ORG_ID=:1 AND SN_KEY=:2 and rs_id is null';

dbms_sqltune.drop_tuning_task ('addm');

l_task_id := dbms_sqltune.create_tuning_task (

sql_text => l_sql,

user_name => 'SMP',

scope => 'COMPREHENSIVE',

time_limit => 120,

task_name => 'addm'

);

dbms_sqltune.execute_tuning_task ('addm');

end;

/ 

set serveroutput on size 999999

set long 999999

select dbms_sqltune.report_tuning_task ('addm') from dual;

實際操作如下：

#################

addm report:

Running the ADDM analysis on the specified pair of snapshots ...

Generating the ADDM report for this analysis ...

DETAILED ADDM REPORT FOR TASK 'TASK_24828' WITH ID 24828

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

Analysis Period: 26-OCT-2010 from 09:00:50 to 10:00:48

Database ID/Instance: 774218943/1

Database/Instance Names: STCSMES/STCSMES

Host Name: stcsmesfab2

Database Version: 10.2.0.4.0

Snapshot Range: from 5399 to 5400

Database Time: 45103 seconds

Average Database Load: 12.5 active sessions

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

FINDING 1: 72% impact (32345 seconds)

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

Host CPU was a bottleneck and the instance was consuming 47% of the host CPU.

All wait times will be inflated by wait for CPU.

RECOMMENDATION 1: Host Configuration, 58% benefit (26237 seconds)

ACTION: Consider adding more CPUs to the host or adding instances

serving the database on other hosts.

ACTION: Also consider using Oracle Database Resource Manager to

prioritize the workload from various consumer groups.

RECOMMENDATION 2: SQL Tuning, 23% benefit (10494 seconds)

ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID

"6u2js5kw4v66k".

RELEVANT OBJECT: SQL statement with SQL_ID 6u2js5kw4v66k and

PLAN_HASH 2068937292

select count(*) cnt from(select sn_key,i_time from SFCS_DEFECTS where

org_id=:1 and sn_key=:2 and rs_id is null group by sn_key,i_time)

ACTION: Investigate the SQL statement with SQL_ID "6u2js5kw4v66k" for

possible performance improvements.

RATIONALE: SQL statement with SQL_ID "6u2js5kw4v66k" was executed 15735

times and had an average elapsed time of 0.65 seconds.

RATIONALE: Average CPU used per execution was 0.26 seconds.

##########################

通過addm進行SQL調優：

SQL> declare

2 l_task_id varchar2(20);

3 l_sql varchar2(2000);

4 begin

5 l_sql := 'select count(*) cnt from(select sn_key, i_time from SFCS_DEFECTS where org_id=:1 and sn_key=:2 and rs_id is null group by sn_key, i_time)';

6 l_task_id := dbms_sqltune.create_tuning_task (

7 sql_text => l_sql,

8 user_name => 'SMP',

9 scope => 'COMPREHENSIVE',

10 time_limit => 60,

11 task_name => 'addm'

12 );

13 dbms_sqltune.execute_tuning_task ('addm');

14 end;

15 / 

PL/SQL procedure successfully completed.

SQL> set serveroutput on size 999999

SQL> set long 999999

SQL> select dbms_sqltune.report_tuning_task ('addm') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('ADDM')

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

GENERAL INFORMATION SECTION

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

Tuning Task Name : addm

Tuning Task Owner : SYS

Scope : COMPREHENSIVE

Time Limit(seconds): 60

Completion Status : COMPLETED

Started at : 10/26/2010 13:41:10

Completed at : 10/26/2010 13:41:14

Schema Name: SMP

SQL ID : 78nrgfjx0qkkv

SQL Text : select count(*) cnt from(select sn_key, i_time from SFCS_DEFECTS

where org_id=:1 and sn_key=:2 and rs_id is null group by sn_key,

i_time)

There are no recommendations to improve the statement.

參考網址：

http://www.hellodba.com/Doc/Oracle%2010G_addm_advisor.htm

http://space.itpub.net/?uid-12361284-action-viewspace-itemid-164945