oracle 固定执行计划(一）之 sql_profile

时间：2022-08-25 04:31:43

到oracle11G 时，已经有三种方式来固定执行计划了

1.10G以前用的是存储大纲

2.10G多了一种，sql_profile

3.11G又多了sql plan management


今天先讲讲sql_profile

1.先创建一张测试表，

SQL> create table emp as select * from scott.emp;

Table created.

2.选择要固定执行计划的sql语句

这里选择 select * from emp where empno=7521;

SQL> select * from emp where empno=7521;

     EMPNO ENAME  JOB     MGR HIREDATESAL  COMM   DEPTNO
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------
      7521 WARD   SALESMAN    7698 22-FEB-81       1250   500       30


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  | |     1 |    87 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("EMPNO"=7521)

Note
-----
   - dynamic sampling used for this statement (level=2)
可以看到这里没有创建索引，所以走的是全表扫描


3.查询出sql的sql_id

SQL> select sql_id,sql_text from v$sql where sql_text like '%emp where empno%';

SQL_ID     SQL_TEXT
-------------------- --------------------------------------------------
<span style="color:#ff6666;">9th5zrrtnxkjy</span>     select * from emp where empno=7521

4.根据sql_id获取 outline 

---------------------------------------------------------------------------------------------------------------
SQL_ID9th5zrrtnxkjy, child number 0
-------------------------------------
select * from emp where empno=7521

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  | | | |     2 (100)|  |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Outline Data
-------------

  /*+
      <span style="color:#ff0000;">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" "EMP"@"SEL$1")
      END_OUTLINE_DATA</span>
  */

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

   1 - filter("EMPNO"=7521)

Note
-----
   - dynamic sampling used for this statement (level=2)


36 rows selected.


5.创建sql profile(SQLPROFILE_001) 
SQL> 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', 
     'OUTL  2    3    4    5    6    7    8    9   10  INE_LEAF(@"SEL$1")',
     'FULL(@"SEL$1" "EMP"@"SEL$1")', 
     'END_OUTLINE_DATA'); 

   dbms_sqltune.import_sql_profile( 
     'select * from emp where empno=7521', 
     v_hints,'SQLPROFILE_001', 
     force_match=>true,replace=>false); 
  end; 
/ 11   12   13   14   15   16   17   18   19  

PL/SQL procedure successfully completed.

5.查看是否使用sql profile
set autot trace explain 
select * from emp where empno=7521; 

SQL> set autot trace explain 
select * from emp where empno=7521;SQL> 

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  | |     1 |    87 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("EMPNO"=7521)

Note
-----
   - SQL profile "<span style="color:#ff0000;">SQLPROFILE_001</span>" used for this statement
可以看到，已经使用了sql_profile

6.在表字段empno上创建索引，再执行SQL看看执行计划是否有变化

SQL> create index idx_empno on emp(empno);

Index created.

SQL> select * from emp where empno=7521;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  | |     1 |    87 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("EMPNO"=7521)

Note
-----
   - SQL profile "SQLPROFILE_001" used for this statement
从上图可以看到，执行计划仍然使用的是sql_profile中的执行计划


7.删除sql_profile再看看执行计划是否有变化（按照最优计划来说，应该会走index的）

删除sql_profile 

SQL> BEGIN 
  DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SQLPROFILE_001'); 
END; 
/  2    3    4  

PL/SQL procedure successfully completed.

SQL> select * from emp where empno=7522;

Execution Plan
----------------------------------------------------------
Plan hash value: 2426388914

-----------------------------------------------------------------------------------------
| Id  | Operation    | Name| Rows| Bytes | Cost (%CPU)| Time|
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    ||     1 |    87 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP|     1 |    87 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN    | IDX_EMPNO |     1 ||     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - access("EMPNO"=7522)

Note
-----
   - dynamic sampling used for this statement (level=2)

果然，执行计划改变了，这次SQL走的是index了.




标签：

相关文章