PostgreSQL模板 pg_stat_statements的官方介绍/简单安装步骤F.28. pg_stat_statements

<a href="http://udn.yyuap.com/doc/postgresql9.3.1-cn-html-v1.1/pgstatstatements.html">postgresql 9.3.1 官方中文手册介绍pg_stat_statements</a>

pg_stat_statements模块提供一种跟踪执行统计服务器执行的所有sql语句的手段。

<b>表 f-20. pg_stat_statements 字段</b>

名字

类型

参考

描述

userid

oid

执行该语句的用户的oid

dbid

执行该语句的数据库的oid

query

text

calls

bigint

执行的次数

total_time

double precision

该语句花费的总时间，以毫秒计

rows

该语句恢复或影响的行的总数

shared_blks_hit

该语句命中的共享块缓存的总数

shared_blks_read

该语句读取的共享块的总数

shared_blks_dirtied

该语句弄脏的共享块的总数

shared_blks_written

该语句写入的共享块的总数

local_blks_hit

该语句命中的本地块缓存的总数

local_blks_read

该语句读取的本地块的总数

local_blks_dirtied

该语句弄脏的本地块的总数

local_blks_written

该语句写入的本地块的总数

temp_blks_read

该语句读取的临时块的总数

temp_blks_written

该语句写入的临时块的总数

blk_read_time

blk_write_time

这个视图和函数pg_stat_statements_reset，只有在通过安装 pg_stat_statements扩展特别安装到的数据库中可用。 <b>不过，当pg_stat_statements模块加载到服务器中时， 统计跟踪该服务器中的所有数据库，不管该视图是否存在。</b>

为了安全起见，不允许非超级用户查看其它用户执行的查询的文本。不过， 如果视图已经安装到他们的数据库中，那么他们可以看到统计。

可计划的查询（也就是，select, insert, update, 和 delete）组合成为一个pg_stat_statements， 当它们根据一个内部哈希计算有相同的查询结构时。典型的，如果两个查询语义上相等， 除了查询中字面常量的值之外，我们认为这两个查询相同。工具命令（也就是，所有其他命令） 是直接基于它们的文本查询字符串比较的。

当一个常量的值为了匹配其他查询而忽略时，该常量在pg_stat_statements 的显示中被?替代。查询文本的剩余部分是第一个查询特定散列值与 pg_stat_statements相关条目。

在一些情况下，带有明显不同文本的查询可能合并到一个pg_stat_statements。 通常这只在语义相等的查询上发生，但是有很小的可能哈希冲突导致不相关的查询被合并到一个条目。 （不过，这对于属于不同用户或数据库的查询来说是不会发生的。）

因为哈希值是基于分析查询的表示法之后来计算的，相反的也是可能的： 带有相同文本的查询可能表现为单独的条目，如果它们因为一个因素的结果有不同的含义， 比如不同的search_path设置。

<b>pg_stat_statements_reset() returns void   【执行select </b><b>pg_stat_statements_reset(); 相当于清空</b><b>pg_stat_statements系统视图</b><b>】</b>

pg_stat_statements_reset抛弃所有pg_stat_statements 到目前为止收集的统计。缺省的，这个函数只能被超级用户执行。

pg_stat_statements.max (integer)

pg_stat_statements.max是该模块追踪语句的最大值 （也就是，pg_stat_statements视图中的最大行数）。 <b>如果观察了比这更多的不同的语句，则会抛弃执行最少的语句的信息。 缺省值是1000。这个参数只能在服务器启动时设置。</b>

pg_stat_statements.track (enum)

pg_stat_statements.track控制哪个语句可以被该模块计数。 声明top来跟踪顶级的语句（直接通过客户端发出的语句）。all 也跟踪嵌套的语句（比如包含在函数中的语句），或none禁用语句状态收集。 缺省值是top。只有超级用户可以更改这个设置。

pg_stat_statements.track_utility (boolean)

pg_stat_statements.track_utility控制该模块是否追踪工具命令。 工具命令是除了select, insert, update 和 delete的所有命令。缺省值是on。只有超级用户可以更改这个设置。

<b>pg_stat_statements.save (boolean)  </b>

pg_stat_statements.save指定在服务器关闭时是否保存语句状态。 如果是off，那么在服务器关闭时不保存状态，在服务器启动时也不重新加载。 缺省值是on。这个参数只可以在postgresql.conf 文件中或者服务器命令行中设置。

<b>track_activity_query_size (integer)指定跟踪当前执行命令的预留字节数， 为了每个活动会话，以及pg_stat_activity.query字段。 默认值是1024。这个参数只能在服务器启动时设置。</b>

这些参数必须在postgresql.conf中设置。典型的用法是：

<b>简单安装步骤：</b>

1、编写$pgdata/postgresql.conf

       shared_preload_libraries = 'pg_stat_statements' 

       pg_stat_statements.max = 1000

     pg_stat_statements.track = all

2、编译安装pg_stat_statements模块

     进入postgresql的源码目录：

     cd ...postgresql-9.1.3/contrib/pg_stat_statements  【find / -name 找下...路径】

     make

     make install

     #如果..../share/extension目录下存在pg_stat_statements--1.*.sql，说明安装成功了

3、加载pg_stat_statements模块

     <b>#启动postgresql服务</b>

     pg_ctl start 

     <b>#加载sql文件</b>

     $ psql -f pg_stat_statements--1.2.sql

     use "create extension pg_stat_statements" to load this file.

    <b>#查看版本</b>

    postgres=# select * from pg_available_extensions where name = 'pg_stat_statements'; 

             name               | default_version | installed_version |                          comment                          

     --------------------+-----------------+-------------------+-----------------------------------------------------------

     pg_stat_statements | 1.2                     | 1.2                        | track execution statistics of all sql statements executed

     postgres=# select * from pg_available_extension_versions where name = 'pg_stat_statements';

             name        | version | installed | superuser | relocatable | schema | requires |                          comment                          

     --------------------+---------+-----------+-----------+-------------+--------+----------+--------------------------

     pg_stat_statements | 1.2     | t         | t         | t           |        |    | track execution statistics of all sql statements executed

     postgres=# 

    <b> #进入db创建系统视图：</b>

     postgres=# create extension pg_stat_statements;

     create extension

     postgres=# select * from pg_stat_statements;  --不报错就可以使用了，随意ddl/dml下~再查看pg_stat_statements会有跟踪记录

4、本人使用pg_stat_statements用来监控是否有drop index的情况.和慢sql...等，如下是查看drop index 情况；

     select pa.rolname,pd.datname,p.query,calls,total_time,rows

     from  pg_stat_statements p

     join pg_authid pa on pa.oid = p.userid

     join pg_database pd on pd.oid = p.dbid

     -- and datname like 'bi_edw_shoes%'

     and upper(query) like '%drop %index%'

     -- 过滤掉存储/函数中包含的drop index关键字

     and  upper(query) not like '%=%drop %index%' ;

    【源于本人笔记】 若有书写错误，表达错误，请指正...