从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。(参考链接:https://cdn.modb.pro/db/88225)这个扩展利用了hook机制,可以动态查找当前正在执行中的sql的执行计划,通过pg_show_plans和pg_stat_activity联合查询,效果是比较好的。


免责声明:

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用,书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权,请联系本站,本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源,请您支持作者,购买正版内容。

6、资源失效,请下方留言,欢迎分享资源链接

文章评论

0条评论