使用DBMS_SQLTUNE包绑定指定执行计划 - 《Oracle》

创建测试表
执行测试语句
找到语句相关信息
验证

创建测试表

SQL> create table test as select from dba_objects;

Table created.

SQL> insert into test select from test;

86301 rows created.

SQL> insert into test select from test;

172602 rows created.

SQL> insert into test select from test;

345204 rows created.

执行测试语句

SQL> set autot on
SQL> var c varchar2(10)
SQL> exec :c :='SYS'
PL/SQL procedure successfully completed.
SQL> select sum(OBJECT_ID) from test where owner=:c;
SUM(OBJECT_ID)
--------------
    1.0168E+10
Execution Plan
----------------------------------------------------------
Plan hash value: 1420412369
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    11 |   253   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE              |                |     1 |    11 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST           |  8698 | 95678 |   253   (0)| 00:00:04 |
|*  3 |    INDEX RANGE SCAN          | IDX_TEST_OWNER |  8698 |       |    21   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("OWNER"=:C)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       9830  consistent gets
          0  physical reads
          0  redo size
        537  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> select /*+ full(test) */sum(OBJECT_ID) from test where owner=:c;
SUM(OBJECT_ID)
--------------
    1.0168E+10
Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    11 |   669   (1)| 00:00:09 |
|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST |  8698 | 95678 |   669   (1)| 00:00:09 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OWNER"=:C)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       9830  consistent gets
          0  physical reads
          0  redo size
        537  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

找到语句相关信息

SQL>  SELECT SQL_ID, PLAN_HASH_VALUE, SQL_TEXT FROM V$SQLAREA WHERE SQL_TEXT LIKE '%owner=:c%';
SQL_ID        PLAN_HASH_VALUE
------------- ---------------
SQL_TEXT
--------------------------------------------------------------------------------
4t3mf9trnnm7q      1950795681
select sum(OBJECT_ID) from test where owner=:c
3jj8pqrprp35c       232555890
SELECT SQL_ID, PLAN_HASH_VALUE, SQL_TEXT FROM V$SQLAREA WHERE SQL_TEXT LIKE '%ow
ner=:c%'
015uu3n2hg575      1950795681
select /*+ full(test) */sum(OBJECT_ID) from test where owner=:c

使用outline找到oracle生成的hint信息（红色部分）
SQL> select plan_table_output from table(dbms_xplan.display_cursor(‘&sql_id’,null,format=> ‘+OUTLINE’));
Enter value for sql_id: 015uu3n2hg575
old 1: select plan_table_output from table(dbms_xplan.display_cursor(‘&sql_id’,null,format=> ‘+OUTLINE’))
new 1: select plan_table_output from table(dbms_xplan.display_cursor(‘015uu3n2hg575’,null,format=> ‘+OUTLINE’))

PLAN_TABLE_OUTPUT
————————————————————————————————————————
SQL_ID 015uu3n2hg575, child number 0
——————————————————-
select /+ full(test) /sum(OBJECT_ID) from test where owner=:c

Plan hash value: 1950795681

—————————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————————————-
| 0 | SELECT STATEMENT | | | | 670 (100)| |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | TABLE ACCESS FULL| TEST | 76370 | 820K| 670 (1)| 00:00:09 |
—————————————————————————————————————-

Outline Data
——————-

/+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘11.2.0.4’)
DB_VERSION(‘11.2.0.4’)

PLAN_TABLE_OUTPUT
————————————————————————————————————————
ALL_ROWS
OUTLINE_LEAF(@”SEL$1”)
FULL(@”SEL$1” “TEST”@”SEL$1”)
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
—————————————————————————-

2 - filter(“OWNER”=:C)

锁定执行计划

SQL> DECLARE
2 SQLFTEXT CLOB;
3 BEGIN
4 SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = ‘&sql_id’;
5

6 DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
7 SQL_TEXT => SQL_FTEXT,
8 PROFILE => SQLPROF_ATTR(‘ ALL_ROWS OUTLINE_LEAF(@”SEL$1”) FULL(@”SEL$1” “TEST”@”SEL$1”)‘),
9 NAME => ‘PROFILE&sqlid’,
10 REPLACE => TRUE,
11 FORCE_MATCH => TRUE
12 );
13 END;
14 /
Enter value for sql_id: 4t3mf9trnnm7q
old 4: SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = ‘&sql_id’;
new 4: SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = ‘4t3mf9trnnm7q’;
Enter value for sql_id: 4t3mf9trnnm7q
old 9: NAME => ‘PROFILE&sql_id’,
new 9: NAME => ‘PROFILE_4t3mf9trnnm7q’,

PL/SQL procedure successfully completed.

验证

SQL> var c varchar2(20)
SQL> exec :c :='SYS'
PL/SQL procedure successfully completed.
SQL> select sum(OBJECT_ID) from test where owner=:c;
SUM(OBJECT_ID)
--------------
    1.0168E+10
Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    11 |   669   (1)| 00:00:09 |
|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST |  8698 | 95678 |   669   (1)| 00:00:09 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OWNER"=:C)
Note
-----
   - SQL profile "PROFILE_4t3mf9trnnm7q" used for this statement
Statistics
----------------------------------------------------------
         34  recursive calls
          0  db block gets
       9841  consistent gets
          1  physical reads
          0  redo size
        537  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

如果有问题，可以禁用或者删除
禁用
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(‘&PROFILE_NAME’,’STATUS’,’DISABLED’);

删除
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(‘&PROFILE_NAME’);