第一： 对表建立索引后 走的是索引范围扫描，如果要改成全表扫描，看以下步骤实现:
SQL> create index idx_paulyitest_id on paulyi_test(object_id);
Index created.
SQL>
SQL>
SQL> explain plan for select * from paulyi_test where object_id=:a;
Explained.
SQL> select * from table(dbms_xplan.display(null,null,‘outline‘));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 978531679
--------------------------------------------------------------------------------
-----------------
| Id | Operation | Name | Rows | Bytes | Cost (
%CPU)| Time |
--------------------------------------------------------------------------------
-----------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 134 | 27738 | 2
(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PAULYI_TEST | 134 | 27738 | 2
(0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_PAULYITEST_ID | 54 | | 1
(0)| 00:00:01 |
--------------------------------------------------------------------------------
-----------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "PAULYI_TEST"@"SEL$1" ("PAULYI_TEST"."OBJECT_ID"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION(‘11.2.0.4‘)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
OPTIMIZER_FEATURES_ENABLE(‘11.2.0.4‘)
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=TO_NUMBER(:A))
Note
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
- dynamic sampling used for this statement (level=2)
32 rows selected.
SQL>
第二：使用hint 固定住执行计划使用全表扫描:
SQL> explain plan for select /*+full(PAULYI_TEST) */* from paulyi_test where object_id=:a;
Explained.
SQL> select * from table(dbms_xplan.display(null,null,‘outline‘));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3957176904
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 134 | 27738 | 51 (0)| 00:00:01
|
|* 1 | TABLE ACCESS FULL| PAULYI_TEST | 134 | 27738 | 51 (0)| 00:00:01
|
--------------------------------------------------------------------------------
-
Outline Data
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "PAULYI_TEST"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION(‘11.2.0.4‘)
OPTIMIZER_FEATURES_ENABLE(‘11.2.0.4‘)
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=TO_NUMBER(:A))
Note
-----
- dynamic sampling used for this statement (level=2)
31 rows selected.
SQL>
第三：使用sql_profile 固定全表扫描执行计划
declare
v_hints sys.sqlprof_attr;
begin
v_hints:=sys.sqlprof_attr(‘FULL(@"SEL$1" "PAULYI_TEST"@"SEL$1")‘);
dbms_sqltune.import_sql_profile(‘select * from PAULYI_TEST where object_id=:a‘,v_hints,‘PAULYI_TEST‘,force_match=>true);
end;
/
SQL> explain plan for select * from paulyi_test where object_id=:a;
Explained.
SQL> select * from table(dbms_xplan.display(null,null,‘outline‘));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3957176904
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 134 | 27738 | 51 (0)| 00:00:01
|
|* 1 | TABLE ACCESS FULL| PAULYI_TEST | 134 | 27738 | 51 (0)| 00:00:01
|
--------------------------------------------------------------------------------
-
Outline Data
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "PAULYI_TEST"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION(‘11.2.0.4‘)
OPTIMIZER_FEATURES_ENABLE(‘11.2.0.4‘)
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=TO_NUMBER(:A))
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL profile "PAULYI_TEST" used for this statement
32 rows selected.
可以在最后看到
SQL profile "PAULYI_TEST" used for this statement 已经使用上了。
原文：http://blog.itpub.net/7199859/viewspace-1306192/