怎么進行ORACLE SQL PROFILE使用

今天就跟大家聊聊有關怎么進行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');
 重建任務，并執行，才能再次查看到新的報告。 這為記錄是放在數據字典里的，所以只有這樣。

看完上述內容，你們對怎么進行ORACLE SQL PROFILE使用有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注億速云行業資訊頻道，感謝大家的支持。