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');
重建任務，並執行，才能再次檢視到新的報告。 這為記錄是放在資料字典裡的，所以只有這樣。
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/28211342/viewspace-2137742/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- sql profile使用SQL
- sql profile的使用SQL
- SQL Server profile使用技巧SQLServer
- Oracle profile的使用Oracle
- sql_profile的使用(一)SQL
- Oracle Profile 使用詳解Oracle
- 使用SQL Profile進行SQL優化案例SQL優化
- 【SQL Profile】coe_xfr_sql_profile.sql內容SQL
- sql profileSQL
- 檢視SQL PROFILE使用的HINTSQL
- oracle 通過sql profile為sql語句加hintOracleSQL
- 使用SQL Profile進行SQL最佳化案例SQL
- Oracle Profile 使用詳解(轉)Oracle
- 使用sql profile固定執行計劃SQL
- sql最佳化：使用sql profile最佳化sql語句SQL
- Oracle使用者profile詳解Oracle
- Oracle 使用者 profile 屬性Oracle
- Oracle SQL Profile固定執行計劃的方法OracleSQL
- Oracle profileOracle
- MySQL Profile檢視SQL的資源使用MySql
- SQL PROFILE 測試SQL
- 使用sql profile實現outline的功能SQL
- sql tuning task和sql profileSQL
- Oracle SQL_Profile手動生成及繫結sql執行計劃OracleSQL
- SQL Profile 實驗03SQL
- SQL Profile 實驗02SQL
- SQL Profile 實驗01SQL
- .Oracle固定執行計劃之SQL PROFILE概要檔案OracleSQL
- Oracle OCP 1Z0-053 Q80(SQL Profile)OracleSQL
- 自動/手動型別sql_profile(dbms_sqltune/coe_xfr_sql_profile.sql)型別SQL
- 使用coe_xfr_sql_profile固定執行計劃SQL
- 使用SQL PROFILE 給出合理的執行計劃SQL
- 【PROFILE】使用Oracle PROFILE限制會話中每一次呼叫所使用的CPU資源Oracle會話
- Oracle OCP(29)：PROFILEOracle
- oracle profile 試驗Oracle
- oracle .bash_profileOracle
- Oracle Profile學習Oracle
- 使用SQL Profile及SQL Tuning Advisor固定執行計劃SQL