Oracle SQL Profile固定执行计划的方法

SQL> select * from v$version;   BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production   SQL> create table table_gl as select * from dba_objects;   Table created   SQL> create index idx_gl on table_gl(object_id);   Index created 手动搜集统计信息，以免执行计划不准确   SQL> EXEC DBMS_STATS.gather_table_stats(user,'table_gl',CASCADE=>TRUE);   PL/SQL procedure successfully completed 然后执行SET AUTO TRACE EXP 查看出执行计划，发现走的索引查询 SQL> EXPLAIN PLAN FOR SELECT OBJECT_NAME FROM table_gl WHERE OBJECT_ID=100;   Explained   SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);    PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 358855602 -------------------------------------------------------------------------------- | Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Ti -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |          |     1 |    30 |     2   (0)| 00 |   1 |  TABLE ACCESS BY INDEX ROWID| TABLE_GL |     1 |    30 |     2   (0)| 00 |*  2 |   INDEX RANGE SCAN          | IDX_GL   |     1 |       |     1   (0)| 00 -------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("OBJECT_ID"=100)   14 rows selected 使用hint参数强制该表走全表扫描 SQL> EXPLAIN PLAN FOR SELECT /*+ FULL(table_gl)*/OBJECT_NAME FROM table_gl WHERE OBJECT_ID=100;   Explained   SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3610250390 ------------------------------------------------------------------------------ | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT  |          |     1 |    30 |   294   (1)| 00:00:04 | |*  1 |  TABLE ACCESS FULL| TABLE_GL |     1 |    30 |   294   (1)| 00:00:04 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("OBJECT_ID"=100)   13 rows selected 把以下SQL拿到其他窗口执行 SELECT /*+ FULL(table_gl)*/OBJECT_NAME FROM table_gl WHERE OBJECT_ID=100; 然后查看该SQL的SQL_ID SQL> SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%/*+ FULL(table_gl)*/%';   SQL_ID        SQL_TEXT ------------- -------------------------------------------------------------------------------- c073nzyv9h0s3  EXPLAIN PLAN FOR SELECT /*+ FULL(table_gl)*/OBJECT_NAME FROM table_gl WHERE OBJ 2qvpsar7w0k85  SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%/*+ FULL(table_gl)*/%' chwqmxqgyqqpg SELECT /*+ FULL(table_gl)*/OBJECT_NAME FROM table_gl WHERE OBJECT_ID=100 获得对应Outline SQL> select * from table(dbms_xplan.display_cursor('chwqmxqgyqqpg',null,'outline'));   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID  chwqmxqgyqqpg, child number 0 ------------------------------------- SELECT /*+ FULL(table_gl)*/OBJECT_NAME FROM table_gl WHERE OBJECT_ID=100 Plan hash value: 3610250390 ------------------------------------------------------------------------------ | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT  |          |       |       |   294 (100)|          | |*  1 |  TABLE ACCESS FULL| TABLE_GL |     1 |    30 |   294   (1)| 00:00:04 | ------------------------------------------------------------------------------ Outline Data -------------   /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.4')   PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------       DB_VERSION('11.2.0.4')       ALL_ROWS       OUTLINE_LEAF(@"SEL$1")       FULL(@"SEL$1" "TABLE_GL"@"SEL$1")       END_OUTLINE_DATA   */ Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("OBJECT_ID"=100)   32 rows selected 可以另找窗口执行，创建sql profile declare  v_hints sys.sqlprof_attr;  begin  v_hints:=sys.sqlprof_attr(       'BEGIN_OUTLINE_DATA',       'IGNORE_OPTIM_EMBEDDED_HINTS',       'OPTIMIZER_FEATURES_ENABLE(''11.2.0.4'')',       'DB_VERSION(''11.2.0.4'')',       'ALL_ROWS',       'OUTLINE_LEAF(@"SEL$1")',       'FULL(@"SEL$1" "TABLE_GL"@"SEL$1")',   --这个是由于hint产生,其实我们需要的就是这个       'END_OUTLINE_DATA'); dbms_sqltune.import_sql_profile( 'SELECT OBJECT_NAME FROM table_gl WHERE OBJECT_ID=100', v_hints,'SQLPROFILE_gl',                 --sql profile 名称 force_match=>true,replace=>true); end; / 验证sql profile结果，发现即使不加hint参数，执行计划也是全表扫描 SQL> EXPLAIN PLAN FOR SELECT OBJECT_NAME FROM table_gl WHERE OBJECT_ID=100;   Explained   SQL>  SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3610250390 ------------------------------------------------------------------------------ | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT  |          |     1 |    30 |   294   (1)| 00:00:04 | |*  1 |  TABLE ACCESS FULL| TABLE_GL |     1 |    30 |   294   (1)| 00:00:04 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("OBJECT_ID"=100) Note -----    - SQL profile "SQLPROFILE_gl" used for this statement   17 rows selected