Oracle 历史SQL语句执行计划的对比与分析

最新推荐文章于 2021-04-05 23:27:35 发布

清风智语

最新推荐文章于 2021-04-05 23:27:35 发布

阅读量9.9k

点赞数

分类专栏： -----Oracle性能优化

本文链接：https://blog.csdn.net/leshami/article/details/8947515

版权

-----Oracle性能优化专栏收录该内容

62 篇文章 37 订阅

订阅专栏

基于CBO优化器的环境中，SQL执行计划的生成依赖于统计信息的真实与完整。如列的离散度，列上的直方图，索引的可用性，索引上的聚簇因子。当这些信息是真实完整的情况下，CBO优化器通常都可以制定最优的执行计划。也正因此CBO优化器也灵活，难以控制，任一信息的不真实或缺失都可能导致执行计划发生变化而产生多个版本。经常碰到的情形是之前的某个SQL语句前阵子还不是TOP SQL，而最近变成了TOP SQL。或者说之前尽管是TOP SQL但，但最近尽然成了TOP 1。对于此情形，我们可以比对SQL语句的历史执行计划进行分析是何种原因导致SQL变慢或执行计划发生变化。下面通过例子来模拟SQL执行计划变异的情形。

1、创建演示环境

--演示环境
scott@SYBO2SZ> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

--创建1000000万记录的表
scott@SYBO2SZ> @cr_big_tb

check total rows  for big_table
====================================
  COUNT(*)
----------
   1000000

--为表创建索引
scott@SYBO2SZ> create index i_big_tb_owner on big_table(owner);

sys@SYBO2SZ> conn / as sysdba;

sys@SYBO2SZ> select snap_id from dba_hist_snapshot order by snap_id;

   SNAP_ID
----------
        30
        31

--清除awr的历史记录,shared pool及buffer cache        
sys@SYBO2SZ> exec dbms_workload_repository.drop_snapshot_range(30,31);

sys@SYBO2SZ> alter system flush shared_pool;

sys@SYBO2SZ> alter system flush buffer_cache;

--清除dba_hist_sql_plan视图,实际上清除wrh$_sql_plan,wrh$_sqltext,wrh$_sqlstat
sys@SYBO2SZ> truncate table wrh$_sql_plan;

--清除dba_hist_sql_sqltext以及dba_hist_sqlstat视图

sys@SYBO2SZ> truncate table wrh$_sqltext;

sys@SYBO2SZ> truncate table wrh$_sqlstat;

sys@SYBO2SZ> select count(*) from dba_hist_sql_plan;

  COUNT(*)
----------
         0

sys@SYBO2SZ> select count(*) from dba_hist_sqltext;

  COUNT(*)
----------
         0

2、生成历史SQL及其执行计划

sys@SYBO2SZ> conn scott/tiger

scott@SYBO2SZ> select count(*) from big_table where owner='GOEX_ADMIN';

  COUNT(*)
----------
     43560

scott@SYBO2SZ> @my_last_sql

ADDRESS          HASH_VALUE SQL_ID        COMMAND_TYPE      PIECE SQL_TEXT
---------------- ---------- ------------- ------------ ---------- ---------------------------------------------------------
000000007B9BB7D0  243468085 4hqyjwh7861tp            3          0 select count(*) from big_table where owner='GOEX_ADMIN'

--从awr中查询sql的执行计划，由于没有生成快照，所以无其执行计划
scott@SYBO2SZ> @sql_plan_disp_awr
Enter value for input_sqlid: 4hqyjwh7861tp

no rows selected

--创建快照
scott@SYBO2SZ> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

--查看SQL的历史执行计划
scott@SYBO2SZ> @sql_plan_disp_awr
Enter value for input_sqlid: 4hqyjwh7861tp

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 4hqyjwh7861tp
--------------------
select count(*) from big_table where owner='GOEX_ADMIN'

Plan hash value: 334839806

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |       |       |   139 (100)|          |
|   1 |  SORT AGGREGATE   |                |     1 |    17 |            |          |
|   2 |   INDEX RANGE SCAN| I_BIG_TB_OWNER | 10073 |   167K|   139   (0)| 00:00:02 |
------------------------------------------------------------------------------------

3、生成不同的历史SQL并对比执行计划

--对表big_table进行move操作
scott@SYBO2SZ> alter table big_table move;

--检查其表上的索引，如下，索引已经失效
scott@SYBO2SZ> @idx_info
Enter value for owner: scott
Enter value for table_name: big_table

TABLE_NAME                INDEX_NAME          CL_NAM               CL_POS STATUS   IDX_TYP         DSCD
------------------------- ------------------- -------------------- ------ -------- --------------- ----
BIG_TABLE                 BIG_TABLE_PK        ID                        1 UNUSABLE NORMAL          ASC
BIG_TABLE                 I_BIG_TB_OWNER      OWNER                     1 UNUSABLE NORMAL          ASC

--再次执行与之前相同的SQL语句
scott@SYBO2SZ> select count(*) from big_table where owner='GOEX_ADMIN';

  COUNT(*)
----------
     43560

scott@SYBO2SZ> @my_last_sql

ADDRESS          HASH_VALUE SQL_ID        COMMAND_TYPE      PIECE SQL_TEXT
---------------- ---------- ------------- ------------ ---------- ----------------------------------------------------------
000000007B9BB7D0  243468085 4hqyjwh7861tp            3          0 select count(*) from big_table where owner='GOEX_ADMIN'

--创建一个新的快照，使之成为历史SQL
scott@SYBO2SZ> exec dbms_workload_repository.create_snapshot();

--查看SQL的执行计划
scott@SYBO2SZ> @sql_plan_disp_awr
Enter value for input_sqlid: 4hqyjwh7861tp

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 4hqyjwh7861tp
--------------------
select count(*) from big_table where owner='GOEX_ADMIN'

Plan hash value: 334839806

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |       |       |   139 (100)|          |
|   1 |  SORT AGGREGATE   |                |     1 |    17 |            |          |
|   2 |   INDEX RANGE SCAN| I_BIG_TB_OWNER | 10073 |   167K|   139   (0)| 00:00:02 |
------------------------------------------------------------------------------------

SQL_ID 4hqyjwh7861tp
--------------------
select count(*) from big_table where owner='GOEX_ADMIN'

Plan hash value: 599409829

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |       |  3221 (100)|          |
|   1 |  SORT AGGREGATE    |           |     1 |    17 |            |          |
|   2 |   TABLE ACCESS FULL| BIG_TABLE | 10073 |   167K|  3221   (1)| 00:00:39 |
--------------------------------------------------------------------------------

28 rows selected.

--从上面的查询结果可以看到，同一条历史SQL语句有不同的plan_hash_value 以及使用了不同的执行计划
--最早的一个是走索引范围扫描，一个是全表扫描

--下面直接从dba_hist_sql_plan查看sql语句的执行计划
--该视图记录了所有被awr快照捕获的所有历史sql的执行计划以及执行计划的生成时间
scott@SYBO2SZ> run sql_plan_his
  1  SELECT id,
  2           operation,
  3           options,
  4           object_name,
  5           bytes,
  6           cpu_cost,                -----> Author : Robinson
  7           io_cost,                 -----> Blog   : http://blog.csdn.net/robinson_0612
  8           timestamp
  9      FROM dba_hist_sql_plan
 10     WHERE sql_id = '&input_sql_id'
 11* ORDER BY timestamp,id
Enter value for input_sql_id: 4hqyjwh7861tp

 ID OPERATION                 OPTIONS       OBJECT_NAME            BYTES   CPU_COST    IO_COST TIMESTAMP
--- ------------------------- ------------- ----------------- ---------- ---------- ---------- -----------------
  0 SELECT STATEMENT                                                                           20130517 11:23:20
  1 SORT                      AGGREGATE                               17                       20130517 11:23:20
  2 INDEX                     RANGE SCAN    I_BIG_TB_OWNER        171241    1789880        139 20130517 11:23:20
  0 SELECT STATEMENT                                                                           20130517 11:27:16
  1 SORT                      AGGREGATE                               17                       20130517 11:27:16
  2 TABLE ACCESS              FULL          BIG_TABLE             171241  325825194       3203 20130517 11:27:16

6 rows selected.

4、修正SQL执行计划

--如前面可知，由于索引不可用导致了SQL语句执行了全表扫描。
--事实上导致全表扫描的问题很多，若使用谓词列函数，谓词列数据类型转换，使用不等于，以及谓词列参与计算等，不一一列出
--针对上面的情形，我们应当收集统计信息以及重建索引
scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);
BEGIN dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true); END;

*
ERROR at line 1:
ORA-20000: index "SCOTT"."BIG_TABLE_PK"  or partition of such index is in unusable state
ORA-06512: at "SYS.DBMS_STATS", line 13182
ORA-06512: at "SYS.DBMS_STATS", line 13202
ORA-06512: at line 1

--上面再收集统计信息时，提示索引不可用，需要先rebulid

scott@SYBO2SZ> alter index i_big_tb_owner rebuild nologging;

scott@SYBO2SZ> alter index big_table_pk rebuild nologging;

scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);

--下面我们再次执行原SQL以及，由下可知，SQL已经使用了最优的执行计划
scott@SYBO2SZ> set autot trace exp;
scott@SYBO2SZ> select count(*) from big_table where owner='GOEX_ADMIN';

Execution Plan
----------------------------------------------------------
Plan hash value: 334839806

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |     6 |   108   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE   |                |     1 |     6 |            |          |
|*  2 |   INDEX RANGE SCAN| I_BIG_TB_OWNER | 44750 |   262K|   108   (1)| 00:00:02 |
------------------------------------------------------------------------------------

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

   2 - access("OWNER"='GOEX_ADMIN')

5、后记
a、示例中创建的big_table脚本，请参考:Oracle 测试常用表BIG_TABLE
b、alter table move 方式用于实现段收缩，移动高水位，但不会释放申请的空间，以及导致索引失效
c、对于历史SQL语句，需要执行snapshot之后，才会被填充到DBA_HIST_SQL_PLAN、DBA_HIST_SQLSTAT、DBA_HIST_SNAPSHOT数据字典中
d、如果你的测试无法获得历史SQL语句及其执行计划，通常是由于awr阀值设置所致，可参考：Oracle AWR 阙值影响历史执行计划
e、历史SQL语句的执行计划也可以通过$ORACLE_HOME/rdbms/admin/awrsqrpt.sql来生成txt或html文件
f、引起同一SQL执行计划发生变化的情形很多，如统计信息的缺失，索引失效，不同级别的参数发生变化等
h、对于实例，会话，语句级别环境变化导致同一SQL执行计划发变异，也可以对此跟踪。参考:使用优化器性能视图获取SQL语句执行环境