sql tuning task和sql profile
sql tuning task和sql profile的關係
如果希望oracle對指定sql提出最佳化建議，需要如下步驟：
1 找出sql的sql_id
2 利用sql_id建立sql tuning task
3 執行sql tuning task
4 檢視sql tuning task報告
接下來會有兩種情況：
1 如果是缺少統計資訊，或者oracle的建議是建立索引只來的，則需要手工按照建議操作。
2 如果oracle發現sql語句本身寫法有問題導致執行計劃不合理，則會建立一個“sql profile”，建議使用者接受該profile。並且給出使用profile前後執行計劃的對比。
如果使用者接受了sql profile，則不需要修改sql語句，oracle會在適當的時候選擇最佳化後的執行計劃。
SQL TUNING TASK
SQL> select count(*) from big;
COUNT(*)
----------
4238336
SQL> select count(*) from small;
COUNT(*)
----------
4140
COUNT(*)
----------
4238336
SQL> select count(*) from small;
COUNT(*)
----------
4140
執行sql查詢
select * from big b, small s where s.object_id = b.object_id and b.status = 'INVALID'
尋找該sql的sql_id
SQL> select sql_id, sql_text from v$sqlarea where sql_text like 'select * from big b, small s where s.object_id = b.object_id and b.status = ''INVALID''';
SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
gb35pax1n9t9w select * from big b, small s where s.object_id = b.object_id and b.status = 'INVALID'
SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
gb35pax1n9t9w select * from big b, small s where s.object_id = b.object_id and b.status = 'INVALID'
利用sql_id檢視該sql執行時的執行計劃
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR( 'gb35pax1n9t9w', null, null));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID gb35pax1n9t9w, child number 0
-------------------------------------
select * from big b, small s where s.object_id = b.object_id and
b.status = 'INVALID'
Plan hash value: 3626037456
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 14296 (100)| |
|* 1 | HASH JOIN | | 577 | 144K| 14296 (1)| 00:02:52 |
|* 2 | TABLE ACCESS FULL| BIG | 577 | 73856 | 14280 (1)| 00:02:52 |
| 3 | TABLE ACCESS FULL| SMALL | 4140 | 517K| 16 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("S"."OBJECT_ID"="B"."OBJECT_ID")
2 - filter("B"."STATUS"='INVALID')
Note
-----
- dynamic sampling used for this statement
26 rows selected.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID gb35pax1n9t9w, child number 0
-------------------------------------
select * from big b, small s where s.object_id = b.object_id and
b.status = 'INVALID'
Plan hash value: 3626037456
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 14296 (100)| |
|* 1 | HASH JOIN | | 577 | 144K| 14296 (1)| 00:02:52 |
|* 2 | TABLE ACCESS FULL| BIG | 577 | 73856 | 14280 (1)| 00:02:52 |
| 3 | TABLE ACCESS FULL| SMALL | 4140 | 517K| 16 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("S"."OBJECT_ID"="B"."OBJECT_ID")
2 - filter("B"."STATUS"='INVALID')
Note
-----
- dynamic sampling used for this statement
26 rows selected.
利用sql_id建立sql tuning task(記住它生成的task名，或者在建立task的時候執行task_name)
set serverout on
declare
tuning_task varchar2(30);
begin
tuning_task:=dbms_sqltune.create_tuning_task(sql_id => 'gb35pax1n9t9w');
dbms_output.put_line(tuning_task);
end;
7 /
declare
tuning_task varchar2(30);
begin
tuning_task:=dbms_sqltune.create_tuning_task(sql_id => 'gb35pax1n9t9w');
dbms_output.put_line(tuning_task);
end;
7 /
TASK_12
PL/SQL procedure successfully completed.
執行sql tuning task
SQL> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'TASK_12');
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
檢視sql tuning結果
SQL> set long 99999
SQL>
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_12') from dual;
SQL>
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_12') from dual;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_13
Tuning Task Owner : SYS
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 11/28/2013 14:55:49
Completed at : 11/28/2013 14:55:53
Number of Index Findings : 1
-------------------------------------------------------------------------------
Schema Name: LS
SQL ID : gb35pax1n9t9w
SQL Text : select * from big b, small s where s.object_id = b.object_id and
b.status = 'INVALID'
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- 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: 100%)
----------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index LS.IDX$$_000D0001 on LS.BIG('STATUS');
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index LS.IDX$$_000D0002 on LS.SMALL('OBJECT_ID');
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: 3626037456
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 166 | 14291 (1)| 00:02:52 |
|* 1 | HASH JOIN | | 1 | 166 | 14291 (1)| 00:02:52 |
|* 2 | TABLE ACCESS FULL| BIG | 1 | 83 | 14275 (1)| 00:02:52 |
| 3 | TABLE ACCESS FULL| SMALL | 4140 | 335K| 16 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("S"."OBJECT_ID"="B"."OBJECT_ID")
2 - filter("B"."STATUS"='INVALID')
2- Using New Indices
--------------------
Plan hash value: 1262730234
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 166 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | SMALL | 1 | 83 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 166 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| BIG | 1 | 83 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX$$_000D0001 | 1 | | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX$$_000D0002 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("B"."STATUS"='INVALID')
5 - access("S"."OBJECT_ID"="B"."OBJECT_ID")
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Tuning Task Name : TASK_13
Tuning Task Owner : SYS
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 11/28/2013 14:55:49
Completed at : 11/28/2013 14:55:53
Number of Index Findings : 1
-------------------------------------------------------------------------------
Schema Name: LS
SQL ID : gb35pax1n9t9w
SQL Text : select * from big b, small s where s.object_id = b.object_id and
b.status = 'INVALID'
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- 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: 100%)
----------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index LS.IDX$$_000D0001 on LS.BIG('STATUS');
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index LS.IDX$$_000D0002 on LS.SMALL('OBJECT_ID');
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: 3626037456
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 166 | 14291 (1)| 00:02:52 |
|* 1 | HASH JOIN | | 1 | 166 | 14291 (1)| 00:02:52 |
|* 2 | TABLE ACCESS FULL| BIG | 1 | 83 | 14275 (1)| 00:02:52 |
| 3 | TABLE ACCESS FULL| SMALL | 4140 | 335K| 16 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("S"."OBJECT_ID"="B"."OBJECT_ID")
2 - filter("B"."STATUS"='INVALID')
2- Using New Indices
--------------------
Plan hash value: 1262730234
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 166 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | SMALL | 1 | 83 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 166 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| BIG | 1 | 83 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX$$_000D0001 | 1 | | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX$$_000D0002 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("B"."STATUS"='INVALID')
5 - access("S"."OBJECT_ID"="B"."OBJECT_ID")
-------------------------------------------------------------------------------
報告顯示，建議增加兩個索引。這樣一來，執行計劃就會由原來的“1- Original”變成“2- Using New Indices”。
這裡建立三個索引測試用
create index idx1 on big(status);
create index idx2 on big(status, object_id);
create index idx3 on big(object_id);
SQL PROFILE
將上面的SQL改寫，不讓它走索引
select /*+ no_index(b IDX1,IDX2) */ * from ls.big b, ls.small s where s.object_id = b.object_id and b.status = 'INVALID';
列印執行計劃
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR( 'bhw1175zgq048', null, null));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID bhw1175zgq048, child number 0
-------------------------------------
select /*+ no_index(b IDX1,IDX2) */ * from ls.big b, ls.small s where
s.object_id = b.object_id and b.status = 'INVALID'
Plan hash value: 566152358
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 14278 (100)| |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| SMALL | 1 | 83 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 166 | 14278 (1)| 00:02:52 |
|* 3 | TABLE ACCESS FULL | BIG | 1 | 83 | 14276 (1)| 00:02:52 |
|* 4 | INDEX RANGE SCAN | IDX3 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("B"."STATUS"='INVALID')
4 - access("S"."OBJECT_ID"="B"."OBJECT_ID")
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
23 rows selected.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID bhw1175zgq048, child number 0
-------------------------------------
select /*+ no_index(b IDX1,IDX2) */ * from ls.big b, ls.small s where
s.object_id = b.object_id and b.status = 'INVALID'
Plan hash value: 566152358
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 14278 (100)| |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| SMALL | 1 | 83 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 166 | 14278 (1)| 00:02:52 |
|* 3 | TABLE ACCESS FULL | BIG | 1 | 83 | 14276 (1)| 00:02:52 |
|* 4 | INDEX RANGE SCAN | IDX3 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("B"."STATUS"='INVALID')
4 - access("S"."OBJECT_ID"="B"."OBJECT_ID")
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
23 rows selected.
建立sql tuning task報告
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_task') from DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_task
Tuning Task Owner : SYS
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 11/28/2013 16:16:37
Completed at : 11/28/2013 16:16:38
Number of SQL Profile Findings : 1
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : bhw1175zgq048
SQL Text : select /*+ no_index(b IDX1,IDX2) */ * from ls.big b, ls.small s
where s.object_id = b.object_id and b.status = 'INVALID'
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 99.85%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'my_task', replace
=> TRUE);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 566152358
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 16434 | 14342 (1)| 00:02:53 |
| 1 | TABLE ACCESS BY INDEX ROWID| SMALL | 1 | 83 | 2 (0)| 00:00:01 |
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
| 2 | NESTED LOOPS | | 99 | 16434 | 14342 (1)| 00:02:53 |
|* 3 | TABLE ACCESS FULL | BIG | 33 | 2739 | 14276 (1)| 00:02:52 |
|* 4 | INDEX RANGE SCAN | IDX3 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("B"."STATUS"='INVALID')
4 - access("S"."OBJECT_ID"="B"."OBJECT_ID")
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
2- Using SQL Profile
--------------------
Plan hash value: 2031531658
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 16434 | 21 (5)| 00:00:01 |
|* 1 | HASH JOIN | | 99 | 16434 | 21 (5)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| BIG | 33 | 2739 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX1 | 1 | | 3 (0)| 00:00:01 |
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
| 4 | TABLE ACCESS FULL | SMALL | 4140 | 335K| 16 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("S"."OBJECT_ID"="B"."OBJECT_ID")
3 - access("B"."STATUS"='INVALID')
-------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_task
Tuning Task Owner : SYS
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 11/28/2013 16:16:37
Completed at : 11/28/2013 16:16:38
Number of SQL Profile Findings : 1
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : bhw1175zgq048
SQL Text : select /*+ no_index(b IDX1,IDX2) */ * from ls.big b, ls.small s
where s.object_id = b.object_id and b.status = 'INVALID'
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 99.85%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'my_task', replace
=> TRUE);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 566152358
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 16434 | 14342 (1)| 00:02:53 |
| 1 | TABLE ACCESS BY INDEX ROWID| SMALL | 1 | 83 | 2 (0)| 00:00:01 |
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
| 2 | NESTED LOOPS | | 99 | 16434 | 14342 (1)| 00:02:53 |
|* 3 | TABLE ACCESS FULL | BIG | 33 | 2739 | 14276 (1)| 00:02:52 |
|* 4 | INDEX RANGE SCAN | IDX3 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("B"."STATUS"='INVALID')
4 - access("S"."OBJECT_ID"="B"."OBJECT_ID")
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
2- Using SQL Profile
--------------------
Plan hash value: 2031531658
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 16434 | 21 (5)| 00:00:01 |
|* 1 | HASH JOIN | | 99 | 16434 | 21 (5)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| BIG | 33 | 2739 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX1 | 1 | | 3 (0)| 00:00:01 |
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
| 4 | TABLE ACCESS FULL | SMALL | 4140 | 335K| 16 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("S"."OBJECT_ID"="B"."OBJECT_ID")
3 - access("B"."STATUS"='INVALID')
-------------------------------------------------------------------------------
報告中也發現執行計劃變成全表掃描了，oracle生成了一個sql profile，建議我們執行下面語句來接受sql profile，這樣oracle就可以在適當的時候使用最佳化後的“2- Using SQL Profile”執行計劃：
execute dbms_sqltune.accept_sql_profile(task_name => 'my_task', replace => TRUE);
接受profile後，不修改sql，執行計劃卻發生了變化，說明sql profile起作用了
SQL> set lines 120
SQL> set autot traceonly
SQL> select /*+ no_index(b idx1) */ * from ls.big b, ls.small s where s.object_id = b.object_id and b.status = 'INVALID';
99 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4150764910
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 166 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | SMALL | 1 | 83 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 166 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| BIG | 1 | 83 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX2 | 1 | | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX3 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("B"."STATUS"='INVALID')
5 - access("S"."OBJECT_ID"="B"."OBJECT_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
142 consistent gets
0 physical reads
0 redo size
8251 bytes sent via SQL*Net to client
450 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99 rows processed
SQL> set autot traceonly
SQL> select /*+ no_index(b idx1) */ * from ls.big b, ls.small s where s.object_id = b.object_id and b.status = 'INVALID';
99 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4150764910
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 166 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | SMALL | 1 | 83 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 166 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| BIG | 1 | 83 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX2 | 1 | | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX3 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("B"."STATUS"='INVALID')
5 - access("S"."OBJECT_ID"="B"."OBJECT_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
142 consistent gets
0 physical reads
0 redo size
8251 bytes sent via SQL*Net to client
450 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99 rows processed
檢視tuning任務和執行狀態
select * from dba_advisor_log
select * from dba_advisor_log
檢視SQL PROFILE
SQL> SELECT NAME, CATEGORY, SQL_TEXT FROM DBA_SQL_PROFILES;
NAME CATEGORY SQL_TEXT
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS_SQLPROF_0150a772d1540000 DEFAULT select /*+ no_index(b IDX1,IDX2) */ * from ls.big b, ls.small s where s.object_i
NAME CATEGORY SQL_TEXT
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS_SQLPROF_0150a772d1540000 DEFAULT select /*+ no_index(b IDX1,IDX2) */ * from ls.big b, ls.small s where s.object_i
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26239116/viewspace-1076288/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- sql tuning advisor(STA) 建議 建立sql profileSQL
- 使用SQL Profile及SQL Tuning Advisor固定執行計劃SQL
- Sql最佳化(二十二) 自動調優工具：sql tuning advisor和sql profile介紹SQL
- 【SQL Profile】coe_xfr_sql_profile.sql內容SQL
- sql profileSQL
- sql tuningSQL
- sql profile使用SQL
- Automatic SQL Tuning and SQL ProfilesSQL
- sql tuning set/sql tuning advisor(待完善)SQL
- sql tuning setSQL
- 熟悉SQL tuningSQL
- ORACLE SQL PROFILE使用OracleSQL
- SQL PROFILE 測試SQL
- sql profile的使用SQL
- 【筆記】SQL tuning筆記SQL
- Oracle SQL Perfomance TuningOracleSQL
- sql tuning advisor和sql access advisor區別SQL
- 自動/手動型別sql_profile(dbms_sqltune/coe_xfr_sql_profile.sql)型別SQL
- SQL Server profile使用技巧SQLServer
- SQL Profile 實驗03SQL
- SQL Profile 實驗02SQL
- SQL Profile 實驗01SQL
- 使用SQL Profile進行SQL優化案例SQL優化
- 手工執行sql tuning advisor和sql access advisorSQL
- SPA_SQL Performance Analyzer_SQL Tuning SetSQLORM
- Oracle OCP 1Z0 053 Q118(Automatic SQL Tuning Task)OracleSQL
- SQL TUNING ADVISORSQL
- Automatic SQL Tuning 原理SQL
- sql tuning之變通SQL
- sql最佳化：使用sql profile最佳化sql語句SQL
- 1223 result cache,sql profile,sql patchSQL
- 使用SQL Profile進行SQL最佳化案例SQL
- 使用sql tuning advisor最佳化sqlSQL
- Oracle SQL優化之sql tuning advisorOracleSQL優化
- 快速SQL TUNING——1分鐘搞定超慢SQLSQL
- oracle實用sql(3)--sql tuning advisorOracleSQL
- SQL Profile（第四篇）SQL
- SQL Profile（第三篇）SQL