OB怎么通过SQL_ID或SQL文本定位所属的存储过程

有时定位到存在问题的SQL，比如活动会话中抓到的并发高，执行时间长的SQL，分析SQL发现SQL执行很快，gv$plan_cache_Plan_stat中记录最慢也是毫秒级的，这种一般是存储过程中循环调用的SQL，但在OB中怎么通过SQL文本或SQL_ID等信息，定位是哪个存储过程？

通过sql_id或sql文本定位调用该SQL的存储过程、plsql匿名块的方法

1、通过execute immediate 执行的动态SQL，SQL文本不会改变，可以直接通过
ORACLE租户
​ select * from DBA_SOURCE where text like ‘%SQL文本关键字%’
MYSQL租户
select * from __all_routine where route_sql like ‘%SQL文本关键字%’

2、直接写的plsql的中的SQL会被重新改写，需要找关键信息检索，会比较困难。

obclient [SYS]> declare
 -> c number;
 -> begin
 -> for i in 1..1000000
 -> loop
 -> select count(*) into c from dba_objects;
 -> end loop;
 -> end;
 -> /


obclient [SYS]> select sql_id,query_sql from gv$plan_cache_plan_stat where query_sql like '%DBA_OBJECTS%' and query_sql not like '%plan_cache_plan_stat%';
+----------------------------------+------------------------------------------+
| SQL_ID                           | QUERY_SQL                                |
+----------------------------------+------------------------------------------+
| 880229F50E62AE83EC40D623714EB2E1 | select count(0) from "SYS"."DBA_OBJECTS" |
+----------------------------------+------------------------------------------+
1 row in set (0.077 sec)


select count(*) into c from dba_objects;
改写成了
select count(0) from "SYS"."DBA_OBJECTS" 
复制

虽然从官方文档V$PLAN_CACHE_PLAN_STAT中描述看到OBJECT_TYPE =ANONYMOUS 时为匿名块，但我测试执行中，执行后的匿名块查不到。

3、同一次请求TRACE_ID相同，存储过程或匿名块中的sql trace_id是相同的，尝试通过拿到SQL_iD,先定位到TRACE_ID，再通过TRACE_ID去查gv$sql_audit。

obclient [SYS]> select sql_id,query_sql,type from gv$plan_cache_plan_stat where query_sql like upper('%dba_objects%') and query_sql not like '%plan_cache_plan_stat%';
+----------------------------------+--------------------------------------------------------+------+
| SQL_ID                           | QUERY_SQL                                              | TYPE |
+----------------------------------+--------------------------------------------------------+------+
| 880229F50E62AE83EC40D623714EB2E1 | select count(0) from "SYS"."DBA_OBJECTS"               |    3 |
| 880229F50E62AE83EC40D623714EB2E1 | select count(0) from "SYS"."DBA_OBJECTS"               |    1 |
| A9CBE8A775DB1589532C3EA793F323AE | select /*+ FULL(t) */count(0) from "SYS"."DBA_OBJECTS" |    1 |
+----------------------------------+--------------------------------------------------------+------+


obclient [oceanbase]> select trace_id,sql_id,query_sql,count(*) from gv$sql_audit  where trace_id='YB4285607B24-000623024E9CD040-0-0' group by  trace_id,sql_id,query_sql ;
+-----------------------------------+----------------------------------+--------------------------------------------------------------------------------------------------------------------+----------+
| trace_id                          | sql_id                           | query_sql                                                                                                          | count(*) |
+-----------------------------------+----------------------------------+--------------------------------------------------------------------------------------------------------------------+----------+
| YB4285607B24-000623024E9CD040-0-0 | A9CBE8A775DB1589532C3EA793F323AE | select /*+ FULL(t) */count(0) from "SYS"."DBA_OBJECTS"                                                             |      100 |
| YB4285607B24-000623024E9CD040-0-0 | 3736A1F6F70EB94B55CE1E9FC4214730 | declare c number; begin for i in 1..100 loop select /*+ full(t) */ count(*) into c from dba_objects; end loop;end; |        1 |
+-----------------------------------+----------------------------------+--------------------------------------------------------------------------------------------------------------------+----------+
2 rows in set (0.767 sec)

obclient [oceanbase]> select query_sql from gv$plan_cache_plan_stat where sql_id='3736A1F6F70EB94B55CE1E9FC4214730';
Empty set (0.045 sec)


复制

注意：

SQL 执行报错，gvplan_cache_plan_stat 中executions不会增加，大量报错重试，或异常捕获后忽略错误，可能会看到SQL执行量不大，或gvplan_cache_plan_stat看不到慢SQL，但大量并发消耗大量资源产生性能问题

create table t111(cnt number);
insert into t111 values (1);
commit;

declare
cnt number;
begin
select cnt into cnt from t111 where ROWNUM=1;
delete t111;
commit;
for i in 1..100 
loop
BEGIN
select  cnt into cnt from t111 where ROWNUM=1;
EXCEPTION WHEN OTHERS THEN
  NULL;
END;
end loop;
end;
/

obclient [SYS]> select sql_id,query_sql,type,executions from gv$plan_cache_plan_stat  where query_sql like '%T111%' and query_sql not like '%plan_cache_plan_stat%';
+----------------------------------+----------------------------------------------------------------+------+------------+
| SQL_ID                           | QUERY_SQL                                                      | TYPE | EXECUTIONS |
+----------------------------------+----------------------------------------------------------------+------+------------+
| 14ECF283E1F8C3B24104D283AF62041B | select "SYS"."T111"."CNT" from "SYS"."T111" where (rownum = 1) |    1 |          1 |
| FD24C1348BCF229375A5B4E00FE8B20C | delete  from "SYS"."T111"                                      |    1 |          1 |
+----------------------------------+----------------------------------------------------------------+------+------------+
2 rows in set (0.066 sec)

该SQL在gv$plan_cache_plan_stat只记录了执行1次，实际执行了101次，其中100次RET_CODE=-4026 NO_DATA_FOUND,没有被记录，但实际执行了。
select "SYS"."T111"."CNT" from "SYS"."T111" where (rownum = 1)

select /*+parallel(10)*/USER_NAME,usec_to_time(request_time) request_time,round(EXECUTE_TIME/1000) exec_ms,sql_id,svr_ip,plan_id,PARAMS_VALUE,user_client_ip,IS_INNER_SQL,RET_CODE,RETURN_ROWS,AFFECTED_ROWS from gv$sql_audit where sql_id='14ECF283E1F8C3B24104D283AF62041B' and IS_EXECUTOR_RPC=0 and request_time>time_to_usec(now())-600000000 order by ELAPSED_TIME desc limit 50;


obclient [oceanbase]> select /*+parallel(10)*/USER_NAME,usec_to_time(request_time) request_time,round(EXECUTE_TIME/1000) exec_ms,sql_id,svr_ip,plan_id,PARAMS_VALUE,user_client_ip,IS_INNER_SQL,RET_CODE,RETURN_ROWS,AFFECTED_ROWS from gv$sql_audit where sql_id='14ECF283E1F8C3B24104D283AF62041B' and IS_EXECUTOR_RPC=0 and request_time>time_to_usec(now())-600000000 order by request_time;
+-----------+----------------------------+---------+----------------------------------+---------------+---------+--------------+----------------+--------------+----------+-------------+---------------+
| USER_NAME | request_time               | exec_ms | sql_id                           | svr_ip        | plan_id | PARAMS_VALUE | user_client_ip | IS_INNER_SQL | RET_CODE | RETURN_ROWS | AFFECTED_ROWS |
+-----------+----------------------------+---------+----------------------------------+---------------+---------+--------------+----------------+--------------+----------+-------------+---------------+
| SYS       | 2024-11-05 14:59:02.523639 |      19 | 14ECF283E1F8C3B24104D283AF62041B | 192.168.56.36 |   12532 | 1,1          | 192.168.56.43  |            1 |        0 |           1 |             0 |
| SYS       | 2024-11-05 14:59:02.546318 |       0 | 14ECF283E1F8C3B24104D283AF62041B | 192.168.56.36 |   12532 | 1,1          | 192.168.56.43  |            1 |    -4026 |           0 |             0 |
| SYS       | 2024-11-05 14:59:02.546561 |       0 | 14ECF283E1F8C3B24104D283AF62041B | 192.168.56.36 |   12532 | 1,1          | 192.168.56.43  |            1 |    -4026 |           0 |             0 |
| SYS       | 2024-11-05 14:59:02.546753 |       0 | 14ECF283E1F8C3B24104D283AF62041B | 192.168.56.36 |   12532 | 1,1          | 192.168.56.43  |            1 |    -4026 |           0 |             0 |
....
| SYS       | 2024-11-05 14:59:02.559788 |       0 | 14ECF283E1F8C3B24104D283AF62041B | 192.168.56.36 |   12532 | 1,1          | 192.168.56.43  |            1 |    -4026 |           0 |             0 |
| SYS       | 2024-11-05 14:59:02.559893 |       0 | 14ECF283E1F8C3B24104D283AF62041B | 192.168.56.36 |   12532 | 1,1          | 192.168.56.43  |            1 |    -4026 |           0 |             0 |
| SYS       | 2024-11-05 14:59:02.559999 |       0 | 14ECF283E1F8C3B24104D283AF62041B | 192.168.56.36 |   12532 | 1,1          | 192.168.56.43  |            1 |    -4026 |           0 |             0 |
+-----------+----------------------------+---------+----------------------------------+---------------+---------+--------------+----------------+--------------+----------+-------------+---------------+
101 rows in set (0.604 sec)



复制