有时定位到存在问题的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)
最后修改时间：2024-11-06 10:03:35
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
