postgresql执行计划查询 plsql查看执行计划并调优

转载

mob64ca13fe1aa6 2024-03-05 23:55:06

文章标签 postgresql执行计划查询 oracle 性能执行计划 SQL 文章分类 云原生云计算

这几天跑存储过程，有时间一跑就是几十分钟，等的确实有点辛苦；于是就想研究一下sql执行的性能，看看存储过程执行慢的问题出在什么地方。

要进行oracle性能调优，不用多说，第一步肯定是看执行计划，要是执行计划看不懂，那就直接歇菜了。

下面就简单的介绍一执行计划的基本概念，方便上手。

一、查看执行计划的方式

查看执行计划一般有三种方式：

1、设置autotrace，设置之后，执行sql就会显示执行计划

序号	命令	解释
1	SET AUTOTRACE OFF	此为默认值，即关闭Autotrace
2	SET AUTOTRACE ON EXPLAIN	只显示执行计划
3	SET AUTOTRACE ON STATISTICS	只显示执行的统计信息
4	SET AUTOTRACE ON	包含2,3两项内容
5	SET AUTOTRACE TRACEONLY	与ON相似，但不显示语句的执行结果

2、使用sql（这一种感觉用的比较多）

EXPLAIN PLAN FOR SQL语句

select * from table(dbms_xplan.display);

例如：

SQL> EXPLAIN PLAN FOR  select  *  from  test;
Explained

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |   216 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |     4 |   216 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
12 rows selected

3、通过工具（比较方便）

可以用pl/sql或者Toad

a、pl/sql 执行完语句后直接按F5就可以了，如下：

postgresql执行计划查询 plsql查看执行计划并调优_SQL

b、Toad 在Toad 里面，很清楚的显示了执行的顺序：

postgresql执行计划查询 plsql查看执行计划并调优_postgresql执行计划查询_02

二、执行计划解读

先看一个比较标准的执行计划：

执行计划
----------------------------------------------------------
Plan hash value: 992080948
---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    13 |   988 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |        |    13 |   988 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   532 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |        |    13 |   494 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMP    |    13 |   494 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("A"."EMPNO"="B"."MGR")
       filter("A"."EMPNO"="B"."MGR")
   5 - filter("B"."MGR" IS NOT NULL)

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       2091  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         13  rows processed

下面就来介绍执行计划中的字段到底是什么意思

a、普通字段解释

字段	解释
ID	一个序号，但不是执行的先后顺序。执行的先后根据缩进来判断
Operation	当前操作的内容
Rows	当前操作的Cardinality，Oracle估计当前操作的返回结果集
Cost（CPU）	Oracle 计算出来的一个数值（代价），用于说明SQL执行的代价
Time	估计当前操作的时间

b、谓词解释

Access: 表示这个谓词条件的值将会影响数据的访问路劲（表还是索引）。

Filter：表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。

在谓词中主要注意access，要考虑谓词的条件，使用的访问路径是否正确。

c、统计信息说明

序号	命令	解释
1	db block gets	从buffer cache中读取的block的数量
2	consistent gets	从buffer cache中读取的undo数据的block的数量
3	physical reads	从磁盘读取的block的数量
4	redo size	DML生成的redo的大小
5	sorts (memory)	在内存执行的排序量
6	sorts (disk)	在磁盘上执行的排序量

Physical Reads通常是我们最关心的，如果这个值很高，说明要从磁盘请求大量的数据到Buffer Cache里，通常意味着系统里存在大量全表扫描的SQL语句，这会影响到数据库的性能，因此尽量避免语句做全表扫描，对于全表扫描的SQL语句，建议增加相关的索引，优化SQL语句来解决。

关于physical reads ，db block gets 和consistent gets这三个参数之间有一个换算公式：

数据缓冲区的使用命中率=1 - ( physical reads / (db block gets + consistent gets) )。

用以下语句可以查看数据缓冲区的命中率：

SQL>SELECT name, value FROM v$sysstat WHERE name IN ('db block gets', 'consistent gets','physical reads');

查询出来的结果Buffer Cache的命中率应该在90％以上，否则需要增加数据缓冲区的大小。

d、动态分析

在开始的文章开始的执行计划中，会看到下面这个动态分析：

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

这提示用户CBO当前使用的技术，需要用户在分析计划时考虑到这些因素。当出现这个提示，说明当前表使用了动态采样。我们从而推断这个表可能没有做过分析。

三、性能优化的几个方面

查看了执行计划之后，我们根据问题要进行sql优化，下面是一些优化常见的方面：

a、表访问方式

全表扫描	Full Table Scan (FTS)
索引唯一扫描	index unique scan
索引局部扫描	index range scan
索引全局扫描	index full scan
索引快速全局扫描，不带order by情况下常发生	index fast full scan
索引跳跃扫描，where条件列是非索引的前导列情况下常发生	index skip scan
物理ID扫描	Rowid

b、表连接方式

Sort Merge Join (SMJ)	由于sort是非常耗资源的，所以这种连接方式要避免
Nested Loops (NL)	比较高效的一种连接方式
Hash Join	最为高效的一种连接方式
Cartesian Product	卡迪尔积，不算真正的连接方式，sql肯定写的有问题