文章目录
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