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

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------

------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------

------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------

------------------------------------------------------------------------------

| 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

Oracle SQL Profile固定執行計劃的方法

相關文章