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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26239116/viewspace-1076288/