ORACLE SQL PROFILE使用

sql profile 是从ORACLE 10G 才有的新特性，可以通过OEM和DBMS_SQLTUNE去管理。
automatic sql tuning:
从字面意思我们就能看出来是自动的来优化SQL 语句。 优化器可能因为缺少一些信息而生成效率不是很高的执行计划，这个时候可能需要我们手工干预如加HINTS让优化器作出正确的决定。但是对于打包的APPLICATION是不允许你修改CODE的，这个时候AUTOMATIC SQL TUNING 通过SQL PROFILE 就可以解决这个问题。首先需要对这个SQL STATEMENT 创建一个PROFILE。然后SQL PROFILE解决上面生成POOR EXECPLAN 通过收集额外的信息例如抽样，局部的执行技术。最后SQL PROFILE 会出一个REPORT.清楚的显示出来给出的建议。例如哪些字段需要建立INDEX,哪些TALBE 需要ANALYZE等等。
SQL PROFILE：收集的信息存放在数据字典里面。让优化器创建一个高效的执行计划。需要注意的是随着数据量的增加和INDEX的创建。可能我们固定的OUTLINE已经不适用啦。所以要过一个时间重新REGENERATE SQL PROFILE。

SQL PROFILE 有效范围：
select statements
update statements
insert statements(only with a select clause)
delete statements
create table statements(only with the as select clause)
merge statements(the update or insert operations)

declare
  my_task_name varchar2(30);
  mysqltext clob;
  begin
  mysqltext:='select * from t where object_id=100';
  my_task_name:=dbms_sqltune.create_tuning_task
  (sql_text=>mysqltext,
  user_name=>'SYSTEM',
  scope=>'COMPREHENSIVE',
  task_name=>'sql_tuning_test'
  );
  end;
  /

整个过程：
conn system/admin
Connected.
SQL> create table t as select object_id,object_name from dba_objects;

Table created.

SQL> set autotrace on;
SQL> select * from t where object_id=100;

 OBJECT_ID
----------
OBJECT_NAME
----------------------------------------------------------------------------------------------------
       100
ORA$BASE


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |   316 |    96   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |     4 |   316 |    96   (2)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=100)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         68  recursive calls
          0  db block gets
        429  consistent gets
        345  physical reads
          0  redo size
        497  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> declare
  my_task_name varchar2(30);
  mysqltext clob;
  begin
  mysqltext:='select * from t where object_id=100';
  my_task_name:=dbms_sqltune.create_tuning_task
  (sql_text=>mysqltext,
  user_name=>'SYSTEM',
  scope=>'COMPREHENSIVE',
  task_name=>'sql_tuning_test'
  );
  end;
  /  2    3    4    5    6    7    8    9   10   11   12   13  

PL/SQL procedure successfully completed.

SQL> exec dbms_sqltune.execute_tuning_task('sql_tuning_test');

PL/SQL procedure successfully completed.

SQL> SET LONG 999999
SQL> SET LINESIZE 100
SQL> set serveroutput on size 999999
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_test') FROM DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : sql_tuning_test
Tuning Task Owner  : SYSTEM
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 04/10/2012 19:27:31
Completed at       : 04/10/2012 19:27:32

-------------------------------------------------------------------------------
Schema Name: SYSTEM
SQL ID     : 5314t67qk27hg
SQL Text   : select * from t where object_id=100

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  Table "SYSTEM"."T" was not analyzed.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => 'SYSTEM', tabname =>
            'T', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt
            => 'FOR ALL COLUMNS SIZE AUTO');

  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table in order to
    select a good execution plan.

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: 96.86%)
  ------------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index SYSTEM.IDX$$_003C0001 on SYSTEM.T("OBJECT_ID","OBJECT_NAME");

  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.

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

1- Original
-----------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |   316 |    96   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |     4 |   316 |    96   (2)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=100)

2- Using New Indices
--------------------
Plan hash value: 2426277634

-----------------------------------------------------------------------------------
| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |     1 |    79 |     3   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX$$_003C0001 |     1 |    79 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   1 - access("OBJECT_ID"=100)

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



Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
       1596  recursive calls
        970  db block gets
        940  consistent gets
          1  physical reads
        572  redo size
      22597  bytes sent via SQL*Net to client
      14289  bytes received via SQL*Net from client
        100  SQL*Net roundtrips to/from client
         28  sorts (memory)
          0  sorts (disk)
          1  rows processed

执行分析：SQL> analyze table t compute statistics;

Table analyzed.

SQL> create index SYSTEM.IDX$$_003C0001 on SYSTEM.T("OBJECT_ID","OBJECT_NAME");

Index created.

删除任务：
exec dbms_sqltune.drop_tuning_task('sql_tuning_test');
 重建任务，并执行，才能再次查看到新的报告。 这为记录是放在数据字典里的，所以只有这样。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22740983/viewspace-734634/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22740983/viewspace-734634/