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%' ;

    【源于本人筆記】 若有書寫錯誤，表達錯誤，請指正...