oracle 优化学习笔记_执行计划

1.查看执行计划

1.1 查看方式

查看方式	是否真实	备注
explain plan	否	配合dbms_xplan.display
dbms_xpaln包	是	display_awr和display_cursor是真实执行过的
autotrace	否	查询不实际执行，dml会执行。可以获取物理及逻辑读等信息
10046和10053	是	目前不熟悉，有机会再补充
sql_monitor	是	DBMS_SQLTUNE.report_sql_monitor；语句执行时间太短的不会被记录；11g以后能使用

1.2. 常用

SQL> alter system set statistics_level=all;

--历史执行计划
select * from table(dbms_xplan.display_awr('sql_id'));
--同一个会话中上一个执行的sql
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
--指定sql_id获取，要求在cursor中未被刷掉
select * from table(dbms_xplan.display_cursor('sql_id/hash_value',null,'allstats last'));

这里除了allstats last外，advanced也是常用的参数。

1.3. sql_monitor报告获取

这种方式不一定能获取所有sql的报告。在能获取的情况下，输出格式比较直观。

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 200
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '&sqlid', type => 'TEXT',REPORT_LEVEL => 'ALL') AS report FROM dual;

SELECT DBMS_SQLTUNE.report_sql_monitor_list(type =>'TEXT',report_level => 'ALL') AS report FROM dual;

2.执行计划的顺序

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |        |      5 |00:00:00.01 |       6 |       |       |          |
|   1 |  MERGE JOIN                  |       |      1 |      5 |      5 |00:00:00.01 |       6 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB_A |      1 |      5 |      5 |00:00:00.01 |       4 |       |       |          |
|   3 |    INDEX FULL SCAN           | IDX_A |      1 |      5 |      5 |00:00:00.01 |       2 |       |       |          |
|*  4 |   SORT JOIN                  |       |      5 |      5 |      5 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
|   5 |    TABLE ACCESS FULL         | TAB_B |      1 |      5 |      5 |00:00:00.01 |       2 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------

这个执行计划的执行顺序是 3-2-5-4-1-0
原则是从上到下，从里到外。
2和4在同一个级别，所以2这个分支相对4先执行，3和5在2和4的里面，分别先执行。

3.常见执行计划

访问或连接方法	执行计划
全表扫描	TABLE ACCESS FULL
ROWID扫描	TABLE ACCESS BY USER/INDEX ROWID
索引唯一扫描	INDEX UNIQUE SCAN
索引范围扫描	INDEX RANGE SCAN
索引全扫描	INDEX FULL SCAN
索引快速全扫描	INDEX FAST FULL SCAN
索引跳跃式扫描	INDEX SKIP SCAN
排序合并	SORT JOIN或MERGE JION
嵌套循环	NESTED LOOPS
哈希	HASH JION
反连接（取反）	ANTI
半连接（去重）	SEMI

针对索引（快速）全扫描，如果单键的索引，没有指定not null，则仍会走全表扫描。

4.稳定执行计划

4.1 sqltune

自动调优，给出自动调优结果，接受即可。

TEST@regan> create table test_sqltune as select * from dba_objects;

Table created.

TEST@regan> create index test_idx_tune on test_sqltune(object_id);

Index created.

TEST@regan> select /*+ no_index(t1 test_idx_tune) */ count(*) from test_sqltune t1 where object_id=100;

  COUNT(*)
----------
         1

TEST@regan> select * from table(dbms_xplan.display_cursor(null,null,'advanced')) ; 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  55854p4uu4jy3, child number 0
-------------------------------------
select /*+ no_index(t1 test_idx_tune) */ count(*) from test_sqltune t1
where object_id=100

Plan hash value: 2407236351

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |       |       |   385 (100)|          |
|   1 |  SORT AGGREGATE    |              |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_SQLTUNE |     1 |     5 |   385   (1)| 00:00:01 |
-----------------------------------------------------------------------------------

上面这里获取了故意走错的执行计划，以及sql_id：55854p4uu4jy3

TEST@regan> variable stmt_task VARCHAR2(64);
TEST@regan> EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '&sqlid');
Enter value for sqlid: 55854p4uu4jy3

PL/SQL procedure successfully completed.

TEST@regan> print stmt_task

STMT_TASK
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TASK_105   ---- 这个任务号记下来，后面要用到

TEST@regan> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name =>:stmt_task);

PL/SQL procedure successfully completed.

TEST@regan> SET LONG 10000000 LONGCHUNKSIZE 1000000 LINESIZE 150 pagesize 0 serveroutput on size 1000000
TEST@regan> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( :stmt_task) from dual;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : TASK_105
Tuning Task Owner  : TEST
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 03/27/2019 15:29:06
Completed at       : 03/27/2019 15:29:08

-------------------------------------------------------------------------------
Schema Name: TEST
SQL ID     : 55854p4uu4jy3
SQL Text   : select /*+ no_index(t1 test_idx_tune) */ count(*) from
             test_sqltune t1 where object_id=100

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 99.85%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_105',
            task_owner => 'TEST', replace => TRUE);

  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.

                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE
  Elapsed Time (s):              .01055           .000038      99.63 %
  CPU Time (s):                 .010314           .000038      99.63 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                     1385                 2      99.85 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                     1                 1
  Fetches:                            1                 1
  Executions:                         1                 1

  Notes
  -----
  1. Statistics for the original plan were averaged over 10 executions.
  2. Statistics for the SQL profile plan were averaged over 10 executions.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 2407236351

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |     5 |   385   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |              |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_SQLTUNE |     1 |     5 |   385   (1)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - filter("OBJECT_ID"=100)

2- Using SQL Profile
--------------------
Plan hash value: 2544668657

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |               |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST_IDX_TUNE |     1 |     5 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=100)

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


TEST@regan> exec dbms_sqltune.accept_sql_profile(task_name => 'TASK_105',replace => TRUE);

PL/SQL procedure successfully completed.

这里看到有了正确的执行计划：2544668657，然后接受即可。

再执行一次：

TEST@regan> select /*+ no_index(t1 test_idx_tune) */ count(*) from test_sqltune t1 where object_id=100;
         1


TEST@regan> select * from table(dbms_xplan.display_cursor('&sql_id',null,'advanced')); 
Enter value for sql_id: 55854p4uu4jy3
old   1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'advanced'))
new   1: select * from table(dbms_xplan.display_cursor('55854p4uu4jy3',null,'advanced'))
SQL_ID  55854p4uu4jy3, child number 0
-------------------------------------
select /*+ no_index(t1 test_idx_tune) */ count(*) from test_sqltune t1
where object_id=100

Plan hash value: 2544668657

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |               |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST_IDX_TUNE |     1 |     5 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T1@SEL$1

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      DB_VERSION('18.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "T1"@"SEL$1" ("TEST_SQLTUNE"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

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

   2 - access("OBJECT_ID"=100)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]

Note
-----
   - SQL profile SYS_SQLPROF_0169be0ecb910000 used for this statement

这里最后的Note显示，这个sql profile生效了。

SYS@regan> select EXECUTIONS,child_number, plan_hash_value, last_load_time from v$sql where sql_id='&sql_id';
Enter value for sql_id: 55854p4uu4jy3
old   1: select EXECUTIONS,child_number, plan_hash_value, last_load_time from v$sql where sql_id='&sql_id'
new   1: select EXECUTIONS,child_number, plan_hash_value, last_load_time from v$sql where sql_id='55854p4uu4jy3'

EXECUTIONS CHILD_NUMBER PLAN_HASH_VALUE LAST_LOAD_TIME
---------- ------------ --------------- --------------------------------------------------
         2            0      2544668657 2019-03-27/15:31:42
SYS@regan> SELECT status,ERROR_MESSAGE,STATUS_MESSAGE,CREATED from DBA_ADVISOR_TASKS WHERE task_name = 'TASK_105';

STATUS      ERROR_MESSAGE                  STATUS_MESSAGE       CREATED
----------- ------------------------------ -------------------- --------------------------------------------------
COMPLETED                                                       27-MAR-19         

附：
在接受时增加force_match=>true可以当字面量条件变化时仍有效

TEST@regan> exec dbms_sqltune.accept_sql_profile(task_name => 'TASK_105',replace => TRUE,force_match=>true);

删除sql_profile

TEST@regan> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_0169be2fe1600001');

4.2 coe_xfr_sql_profile.sql脚本

这个是官方提供的脚本，日常生产中，当执行计划发生抖动时，主要以此方式稳定执行计划。
常规使用比较简单，执行后要求输入sql_id和sql_hash_value，然后生成一个目标脚本，再执行这个脚本即可。
当原本没有有效的执行计划的hash_value时，需要手动生成执行计划并修改生成的目标脚本。这个后续再另开整理了。

4.3 sql plan management

11g以后引入，主动的稳定执行计划的手段，能够保证只有被验证过的执行计划才会被启用。

SYS@regan> show parameter sql_plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE  --默认关闭
optimizer_use_sql_plan_baselines     boolean     TRUE