SQL> select * from V$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
1. 正常的访问
SQL> create table t2 as select * from dba_objects;
SQL> create index ind_t2 on t2(object_id);
SQL> set autot trace exp
SQL>select * from t2 where object_id=111;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 177 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 177 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T2 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
2.强制走全表的访问
SQL> select /*+ full(t2) */ * from t2 where object_id=111;Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 177 | 35 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 1 | 177 | 35 (0)| 00:00:01 |
--------------------------------------------------------------------------
3.查看走全表的outline
SQL> set autot offSQL> select sql_id,sql_text from V$sql where sql_text
2 like '%full(t2)%';
0cy34v3m5s2dn
select /*+ full(t2) */ * from t2 where object_id=:"SYS_B_0"
0qjqkfrtv9rmz
EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select sql_id,sql_text from V$sql where sql_text like '%full(t2)%'
38t0zqfwmbfpx
EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select /*+ full(t2) */ * from t2 where object_id=111
SQL> select * from table(dbms_xplan.display_cursor('0cy34v3m5s2dn',null,'outline'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0cy34v3m5s2dn, child number 0
-------------------------------------
select /*+ full(t2) */ * from t2 where object_id=:"SYS_B_0"
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 35 (100)| |
|* 1 | TABLE ACCESS FULL| T2 | 1 | 177 | 35 (0)| 00:00:01 |
--------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T2"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=:SYS_B_0)
Note
-----
- dynamic sampling used for this statement
35 rows selected.
4.创建sql profile
declare
v_hints sys.sqlprof_attr;
begin
v_hints:=sys.sqlprof_attr(
'BEGIN_OUTLINE_DATA',
'IGNORE_OPTIM_EMBEDDED_HINTS',
'OPTIMIZER_FEATURES_ENABLE(''10.2.0.1'')',
'ALL_ROWS',
'OUTLINE_LEAF(@"SEL$1")',
'FULL(@"SEL$1" "T2"@"SEL$1")',
'END_OUTLINE_DATA');
dbms_sqltune.import_sql_profile(
'select * from t2 where object_id=111',
v_hints,'SQLPROFILE_T2',
force_match=>true,replace=>true);
end;
/
5.查看使用SQL PROFILE后的效果
SQL> set autot trace exp
SQL> select * from t2 where object_id=111;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 17523 | 35 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 99 | 17523 | 35 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=111)
Note
-----
- SQL profile "SQLPROFILE_T2" used for this statement