ORACLE sql调整（一）

sql profile:
基本使用方法：
fy@ORCL> drop table t;

Table dropped.

fy@ORCL> create table t(id constraint t_pk primary key,pad) as
2 select rownum,lpad('*',4000,'*')
3 from all_objects
4 where rownum<=10000; Table created. sys@ORCL> grant advisor to fy;

fy@ORCL> var tn varchar2(30)
fy@ORCL> declare
2 l_sqltext clob:='select count(*) from t where id+42=126';
3 begin
4 :tn:=dbms_sqltune.create_tuning_task(sql_text=>l_sqltext);
5 dbms_sqltune.execute_tuning_task(:tn);
6 end;
7 /

PL/SQL procedure successfully completed.

fy@ORCL> select dbms_sqltune.report_tuning_task(:tn) from dual;


DBMS_SQLTUNE.REPORT_TUNING_TASK(:TN)
-------------------------------------------------------------------------------

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_122
Tuning Task Owner : FY
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 03/10/2009 10:34:02
Completed at : 03/10/2009 10:34:02

-------------------------------------------------------------------------------
Schema Name: FY
SQL ID : 8m7h2qakgcajz
SQL Text : select count(*) from t where id+42=126

-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit<=10%) --------------------------------------- - Consider accepting the recommended SQL profile. execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_122',
task_owner => 'FY', replace => TRUE);

Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.

Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time(ms): 0 0
CPU Time(ms): 0 0
User I/O Time(ms): 0 0
Buffer Gets: 23 21 8.69%
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1

2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.

Recommendation (estimated benefit: 95.23%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index FY.IDX$$_007A0001 on FY.T("ID"+42);

Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.

3- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
The predicate "T"."ID"+42=126 used at line ID 2 of the execution plan
contains an expression on indexed column "ID". This expression prevents the
optimizer from selecting indices on table "FY"."T".

Recommendation
--------------
- Rewrite the predicate into an equivalent form to take advantage of
indices. Alternatively, create a function-based index on the expression.

Rationale
---------
The optimizer is unable to use an index if the predicate is an inequality
condition or if there is an expression or an implicit data type conversion
on the indexed column.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 454320086

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX FAST FULL SCAN| T_PK | 13 | 52 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("ID"+42=126)

2- Original With Adjusted Cost
------------------------------
Plan hash value: 454320086

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim


使用以下语句可以生成类似EM的建议：
select a.command as type,
f.message as findings,
a.message as recommendation,
t.message as rationable
from dba_advisor_actions a,
dba_advisor_recommendations r,
dba_advisor_findings f,
dba_advisor_rationale t
where
a.task_id=122
and a.task_id=r.task_id
and a.rec_id=r.rec_id
and a.task_id=t.task_id
and a.rec_id=t.rec_id
and f.task_id=r.task_id
and f.finding_id=r.finding_id;

fy@ORCL> alter session set sqltune_category=TEST;

Session altered.

fy@ORCL> begin
2 dbms_sqltune.accept_sql_profile(
3 task_name=>'TASK_122',
4 name=>'frist_rows.sql',
5 category=>'TEST',
6 force_match=>true
7 );
8 end;
9 /

PL/SQL procedure successfully completed.

dbms_sqltune.alter_sql_profile(
name=>'fisrt_rows.sql',
attribute_name=>'CATEGORY',
value=>'DEFAULT'
)

关于sql profile的基本操作，可以看看dbms_sqltune这个包（好像可以直接导入），明天研究一下sqlprofile的机理。