從Oracle的SQL_ID到PG14引入核心的QUERY_ID
對於習慣了ORACLE運維的DBA們,在進行SQL最佳化以及問題排查的時候,不可避免地會用到SQL_ID,從ORACLE到PostgreSQL過渡的時候,可能在分析問題的時候第一反應可能還是想獲取到對應SQL的SQL_ID,然後根據SQL_ID去獲取執行計劃從而進行分析。
而PostgreSQL與ORACLE類似的SQL_ID功能,是PostgreSQL-14版本從pg_stat_statements外掛的QUERY_ID計算模組剝離到核心中, 使得內部可以直接使用query_id功能。
一、ORACLE從Wait Events到SQL_ID
在ORACLE資料庫遇到一些效能問題的時候,我們通常會檢視日誌,檢視等待事件,檢視資料庫,伺服器資源使用情況等等。
其中等待事件是一個標誌性的指標,不管是直接在資料庫裡檢視等待事件,還是透過生成AWR、ASH報告去檢視,究其根本,都是為了分析等待事件所對應的資料庫行為,在發生效能問題或者故障的時候,很有可能伴隨著某個等待事件的出現或者數量飆升,亦或是等待事件在DB time佔了很大的比重。
比如出現了
gc cr multi block request等待事件,代表可能存在對資料塊的請求是跨例項的全表掃描和全索引掃描。又或者出現
enq:TX - row lock contention等待事件,可能有應用程式碼邏輯層有問題,導致同時修改相同資料引發鎖等待、或者主鍵或者唯一鍵衝突引發鎖等待等相關問題。
如果是生成了awr,我們可能很快就能根據Top的Wait Events找到可能導致效能問題的SQL_TEXT進一步分析,而awr是一個歷史的採集報告,雖然可以給我們找到問題的源頭提供幫助,但如果需要分析當前正在發生的問題,例如鎖情況的時候,直接用SQL去檢視應該是一個更好的選擇。
下面是一個ORACLE根據等待事件進一步分析的流程,可供參考:
1.在發生問題的時候,我們可以檢視資料庫的等待事件,看看哪些等待事件數量較多
(這裡只是給大家舉個分析的例子,該資料庫暫沒資料庫效能問題)
SQL> col WAIT_CLASS for a15 SQL> col event for a25 SQL> select inst_id, event#, event,WAIT_CLASS, count(*) from gv$session where wait_class# <> 6 group by inst_id, event#, event,WAIT_CLASS order by 1,5 desc; INST_ID EVENT# EVENT WAIT_CLASS COUNT(*) ---------- ---------- ------------------------- --------------- ---------- 1 450 SQL*Net message to client Network 1
2.找到關鍵或者懷疑的等待事件,根據等待事件查詢這些SQL的SQL_ID
SQL> set lines 200 SQL> set pages 999 SQL> col username for a10 SQL> Select s.event,s.username,q.sql_id,substrb(q.sql_text,1,30) sqltext,count(1) from gv$session s, gv$sql q where q.sql_id=s.sql_id and s.event='&wait_event' group by s.event, s.username,substrb(q.sql_text,1,30),q.sql_id order by count(1) desc; Enter value for wait_event: SQL*Net message to client old 1: Select s.event,s.username,q.sql_id,substrb(q.sql_text,1,30) sqltext,count(1) from gv$session s, gv$sql q where q.sql_id=s.sql_id and s.event='&wait_event' group by s.event, s.username,substrb(q.sql_text,1,30),q.sql_id order by count(1) desc new 1: Select s.event,s.username,q.sql_id,substrb(q.sql_text,1,30) sqltext,count(1) from gv$session s, gv$sql q where q.sql_id=s.sql_id and s.event='SQL*Net message to client' group by s.event, s.username,substrb(q.sql_text,1,30),q.sql_id order by count(1) desc EVENT USERNAME SQL_ID SQLTEXT COUNT(1) ------------------------- ---------- ------------- ------------------------------ ---------- SQL*Net message to client OUSER 81ppgaramj8gu Select s.event,s.username,q.sq 1
3.這些獲取到的SQL裡,有的可能就是導致問題的關鍵,甚至可能都是同型別的SQL
我們可以使用如下幾種方式取獲取SQL的執行計劃,我比較常用的是前兩個,使用 dbms_xplan.display_awr能夠檢視AWR中的語句的執行計劃,使用 dbms_xplan.display_cursor能夠檢視當前記憶體中游標的執行計劃。
select * from table(dbms_xplan.display_cursor('&sql_id')); select * from table(dbms_xplan.display_awr('&sql_id')); explain plan for select * from table(dbms_xplan.display); set autotrace on select * from table(dbms_xplan.display_cursor('&sql_id',null,'advanced'));
舉例如下所示:
SQL> select * from table(dbms_xplan.display_cursor('&sql_id')); Enter value for sql_id: 81ppgaramj8gu old 1: select * from table(dbms_xplan.display_cursor('&sql_id')) new 1: select * from table(dbms_xplan.display_cursor('81ppgaramj8gu')) PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 81ppgaramj8gu, child number 0 ------------------------------------- Select s.event,s.username,q.sql_id,substrb(q.sql_text,1,30) sqltext,count(1) from gv$session s, gv$sql q where q.sql_id=s.sql_id and s.event='SQL*Net message to client' group by s.event, s.username,substrb(q.sql_text,1,30),q.sql_id order by count(1) desc Plan hash value: 3212660850 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT ORDER BY | | 1 | 578 | 2 (100)| 00:00:01 | | 2 | HASH GROUP BY | | 1 | 578 | 2 (100)| 00:00:01 | | 3 | NESTED LOOPS | | 1 | 578 | 0 (0)| | | 4 | NESTED LOOPS | | 1 | 55 | 0 (0)| | | 5 | NESTED LOOPS | | 1 | 25 | 0 (0)| | | 6 | FIXED TABLE FULL | X$KSLWT | 92 | 736 | 0 (0)| | |* 7 | FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) | 1 | 17 | 0 (0)| | |* 8 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 30 | 0 (0)| | |* 9 | FIXED TABLE FIXED INDEX | X$KGLCURSOR_CHILD (ind:2) | 1 | 523 | 0 (0)| | ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 7 - filter(("S"."KSUSESQI" IS NOT NULL AND "S"."INDX"="W"."KSLWTSID" AND BITAND("S"."KSUSEFLG",1)<>0 AND BITAND("S"."KSSPAFLG",1)<>0 AND INTERNAL_FUNCTION("S"."CON_ID"))) 8 - filter(("E"."KSLEDNAM"='SQL*Net message to client' AND "W"."KSLWTEVT"="E"."INDX")) 9 - filter((INTERNAL_FUNCTION("CON_ID") AND "KGLOBT03"="S"."KSUSESQI")) 32 rows selected.
然後看執行計劃是否在某個環節發生了問題,可以看情況選擇利用10053或者10046兩個Oracle的內部事件去分析:
透過
10053瞭解CBO怎樣工作,最佳化器根據什麼選擇了這個執行計劃。
透過
10046幫助我們解析一條/多條SQL、PL/SQL語句的執行狀態,這些狀態包括 :Parse/Fetch/Execute三個階段中遇到的等待事件、消耗的物理和邏輯讀、CPU時間、執行計劃等等,簡而言之10046 告訴我們SQL(執行計劃)執行地如何。
二、PostgreSQL的QUERY_ID
在PostgreSQL的早期版本中,並沒有ORACLE的SQL_ID這個概念,而外掛要使用一個演算法計算QUERY_ID,即查詢的唯一標識,但任何擴充套件都可以使用自己的演算法。因此在PostgreSQL-14版本,把pg_stat_statements擴充套件的QUERY_ID計算模組剝離到核心中,使所有工具/擴充套件都使用核心中計算的QUERY_ID,因此,這無需工具/擴充套件再重複計算,對於擴充套件來說算是一個效能提升,PostgreSQL-14用一個新的GUC控制引數 compute_query_id來啟用這個特性。
postgres=# select * from pg_settings where name='compute_query_id'; -[ RECORD 1 ]---+--------------------------- name | compute_query_id setting | auto unit | category | Statistics / Monitoring short_desc | Compute query identifiers. extra_desc | context | superuser vartype | enum source | default min_val | max_val | enumvals | {auto,on,off} boot_val | auto reset_val | auto sourcefile | sourceline | pending_restart | f
當我們開啟這個引數的時候,可以在執行某些SQL的時候,在pg_stat_activity、explain、pg_stat_statments這些工具裡共享一個QUERY_ID。例如一個session執行select pg_sleep(100);另開一個session,去檢視pg_stat_activity,就有了如下內容,下面的query_id就是一個共享的"SQL_ID"
-[ RECORD 2 ]----+--------------------------------------------------------------------------- datid | 13023 datname | postgres pid | 3375467 leader_pid | usesysid | 10 usename | xmaster application_name | psql client_addr | client_hostname | client_port | -1 backend_start | 2022-09-13 15:34:58.949586+08 xact_start | 2022-09-13 15:41:46.977953+08 query_start | 2022-09-13 15:41:46.977953+08 state_change | 2022-09-13 15:41:46.977956+08 wait_event_type | Timeout wait_event | PgSleep state | active backend_xid | backend_xmin | 1912 query_id | 440101247839410938 query | select pg_sleep(100); backend_type | client backend
除此之外,例如使用explain命令,在帶上了verbose選項後,也會在執行計劃的下邊,顯示出該SQL的query_id。
postgres=# explain select 1; QUERY PLAN ------------------------------------------ Result (cost=0.00..0.01 rows=1 width=4) (1 row) postgres=# explain (verbose) select 1; QUERY PLAN ------------------------------------------ Result (cost=0.00..0.01 rows=1 width=4) Output: 1 Query Identifier: 1147616880456321454 (3 rows)
但其實對於PostgreSQL正在執行的SQL的執行計劃,我更建議使用它的另外一個擴充套件————pg_show_plans。(參考連結: )這個擴充套件利用了hook機制,可以動態查詢當前正在執行中的sql的執行計劃,透過pg_show_plans和pg_stat_activity聯合查詢,效果是比較好的。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/69990629/viewspace-2918603/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- CSS 從入門到放棄系列:CSS的引入方式CSS
- Redis核心解讀-從Master到Slave的ReplicantionRedisAST
- Playmaker Input篇教程之引入的核心概念
- 淺析《死亡擱淺》的“連線”——從玩法到核心
- Java之美[從菜鳥到高手演變]之執行緒同步的引入Java執行緒
- Oracle檢視sql_id 的歷史執行計劃OracleSQL
- 從《免費》到Oracle的攻勢分析薦Oracle
- 從Mixin到hooks,談談對React16.7.0-alpha中即將引入的hooks的理解HookReact
- PG14中的idle_session_timeoutSession
- 認識node核心模組--從Buffer、Stream到fs
- 從MySQL到ORACLE程式遷移的注意事項(轉)MySqlOracle
- Oracle SQL_ID轉換成SQL_HASH_VALUEOracleSQL
- Oracle如何根據SQL_TEXT生成SQL_IDOracleSQL
- 引入gitlab倉庫程式碼到npm包的教程GitlabNPM
- 有相同sql_id的sql語句SQL
- 從DBA到Oracle Applications DBA的轉變過程 (轉)OracleAPP
- 初識Linux滲透:從列舉到核心利用Linux
- Combine 框架,從0到1 —— 1.核心概念框架
- Unity3D - 如何引入包到指定的資料夾Unity3D
- 從Mysql轉到Oracle前需瞭解的50件事MySqlOracle
- 從Oracle轉到Mysql前需瞭解的50件事OracleMySql
- 把資料從MySQL遷到Oracle的幾點注意事項MySqlOracle
- JavaScript的for從懵懂到辨明JavaScript
- 大家說說oracle引入extent概念的目的是什麼?Oracle
- 將ffmpeg引入到Android工程中Android
- Linux從頭學08:Linux 是如何保護核心程式碼的?【從真實模式到保護模式】Linux模式
- 問題:V$SESSION SQL_ID 為空,找不到SQL_IDSessionSQL
- oracle從10.2.0.4升級到11.2.0.1的三種升級方法Oracle
- ash報告中無sql_id的情況SQL
- 從 xcarchive 到分發的 ipaHive
- 從element-ui按需引入去探索UI
- 從工具的奴隸到工具的主人
- 更快的機器學習即將來到 Linux 核心機器學習Linux
- 從DTFT到DFS,從DFS到DFT,從DFT到FFT,從一維到二維FFT
- Linux核心net模組引入了Rust程式碼 - PhoronixLinuxRust
- 從 Oracle 日誌解析學習資料庫核心原理Oracle資料庫
- GitOps 應用實踐系列 - Argo CD 從入門到核心GitGo
- PG14:adminpack 外掛原始碼分析原始碼