ITPub博客

首页 > 数据库 > Oracle > Oracle SQL Profile固定执行计划的方法

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

原创 Oracle 作者:巡完南山巡南山 时间:2019-01-21 13:55:55 0 删除 编辑

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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26964624/viewspace-2564286/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2016-04-11

  • 博文量
    44
  • 访问量
    46823