OceanBase sql优化排查方法

IT那活儿发布于2023-01-11 13:20 / 1180人阅读

OceanBase sql优化排查方法

点击上方“IT那活儿”，关注后了解更多内容，不管IT什么活儿，干就完了！！！

文章前言

接触OB的项目也有一段时间了，除了环境的问题，在O迁移ob过程中也存在很多的sql的问题，具体的sql调优技巧我就暂时不做介绍了，OB的优化工程师的直播讲解很详细(https://open.oceanbase.com/docs/videoCenter/5900015)。我简单记录下自己学习到和生产用到的一点小场景。

场景一

项目割接之后的三天内是效率sql的高频出现时期，现场一般也会有ob的优化工程师一直在持续做优化，那如何来筛选问题sql呢？

-正在在执行的进程：

select user,tenant,host,db,info from __all_virtual_processlist where state=ACTIVE limit 3G

--历史SQL执行：

select sql_id,

hit_count,

avg_exe_usec,

slowest_exe_usec,

plan_id,

last_active_time,

slowest_exe_usec

from gv$plan_cache_plan_stat

where tenant_id = 1022  --租户id

and avg_exe_usec >= 1000000000        --执行时间筛选

and last_active_time > 2022-01-19 17:20:00.000000           --时间段筛选

order by hit_count desc limit 40;

select sql_id, hit_count, avg_exe_usec, timeout_count, plan_id, last_active_time, outline_id from gv$plan_cache_plan_stat p

where tenant_id = 1022  and (avg_exe_usec >= 100000000 or timeout_count > 0)

and last_active_time > 2021-05-19 08:00:00.000000

order by timeout_count desc, hit_count desc limit 100;

--查看sql语句：

select query_sql from gv$plan_cache_plan_stat where sql_id = CE9AXXXXX00FEA8ED885EB0;

因为一般outline创建名称会带有sqlid。

--确认是否已存在outline绑定：

MySQL [oceanbase]>

select * from gv$outline where tenant_id=1022  and outline_name like %B5BXXXXXXXXXXXXXXA5956F457%;

至于如何绑定outline，官网上有介绍，分为两种方式一种直接绑定sqlid还有一种绑定sql语句。

--检查优化结果（确认使用了outline）：

select p.last_active_time, p.sql_id, p.hit_count, p.avg_exe_usec from gv$plan_cache_plan_stat p where outline_id != -1;

当然上面绑定outline只是一种方式，要优先判断是否有合适索引，是否没有走索引，或者合适的连接方式。

那查看索引的方式与oracle有些类似：

--获得一张表的索引：

select index_name, listagg(column_name, ,) within group (order by column_position)

from all_ind_columns

where table_name = upper(TABLE_NAME)

group by index_name;

在oracle中有时会遇到索引失效，执行计划变化的情况，OB也可以判断：

select plan_id,sql_id from gv$plan_cache_plan_stat where sql_id = 712XXXXXXXXXX2BF976A;

如果我要查询历史的执行计划怎么办？

select plan_id, operator, name, rows, cost from gv$plan_cache_plan_explain where tenant_id=1020 and ip=10.xx.xxx.xxx and port=2882 and plan_id=45482904;

下面介绍一个ob工程师处理的执行计划抖动的案例：

MySQL [oceanbase]> select /*+parallel(32)*/ svr_ip,

-> plan_id,

-> sid,

-> elapsed_time,

-> usec_to_time(request_time) req_time,

-> memstore_read_row_count,

-> ssstore_read_row_count,

-> query_sql,

-> plan_type

-> from gv$sql_audit

-> where tenant_id = 1003

-> and query_sql like %INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT%

-> order by elapsed_time desc limit 10;

+-------------+-----------+------------+--------------+----------------------------+-------------------------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+

| svr_ip | plan_id | sid | elapsed_time | req_time | memstore_read_row_count | ssstore_read_row_count | query_sql | plan_type |

+-------------+-----------+------------+--------------+----------------------------+-------------------------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+

| ***.**.**.3 | 142892605 | 3222098456 | 686871515 | 2022-02-05 11:29:06.367558 | 876102 | 2 | INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT COL1,COL2.... FROM xxxxxxxx T WHERE 1 = 1 AND T.COL1 = 111    AND T.cccc = ? | 1 |

| ***.**.**.3 | 142892605 | 3222098456 | 686134803 | 2022-02-05 11:29:06.367558 | 876094 | 2 | INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT COL1,COL2.... FROM xxxxxxxx T WHERE 1 = 1 AND T.COL1 = 111    AND T.cccc = ? | 1 |

| ***.**.**.3 | 142892605 | 3222098456 | 685402617 | 2022-02-05 11:29:06.367558 | 876094 | 2 | INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT COL1,COL2.... FROM xxxxxxxx T WHERE 1 = 1 AND T.COL1 = 111    AND T.cccc = ? | 1 |

| ***.**.**.3 | 142892605 | 3222098456 | 684654700 | 2022-02-05 11:29:06.367558 | 876094 | 2 | INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT COL1,COL2.... FROM xxxxxxxx T WHERE 1 = 1 AND T.COL1 = 111    AND T.cccc = ? | 1 |

| ***.**.**.3 | 142892605 | 3222098456 | 683896972 | 2022-02-05 11:29:06.367558 | 876094 | 2 | INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT COL1,COL2.... FROM xxxxxxxx T WHERE 1 = 1 AND T.COL1 = 111    AND T.cccc = ? | 1 |

| ***.**.**.3 | 142892605 | 3222098456 | 683149813 | 2022-02-05 11:29:06.367558 | 876094 | 2 | INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT COL1,COL2.... FROM xxxxxxxx T WHERE 1 = 1 AND T.COL1 = 111    AND T.cccc = ? | 1 |

| ***.**.**.3 | 142892605 | 3222098456 | 682406192 | 2022-02-05 11:29:06.367558 | 876094 | 2 | INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT COL1,COL2.... FROM xxxxxxxx T WHERE 1 = 1 AND T.COL1 = 111    AND T.cccc = ? | 1 |

| ***.**.**.3 | 142892605 | 3222098456 | 681666012 | 2022-02-05 11:29:06.367558 | 876094 | 2 | INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT COL1,COL2.... FROM xxxxxxxx T WHERE 1 = 1 AND T.COL1 = 111    AND T.cccc = ? | 1 |

| ***.**.**.3 | 142892605 | 3222098456 | 680929101 | 2022-02-05 11:29:06.367558 | 876094 | 2 | INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT COL1,COL2.... FROM xxxxxxxx T WHERE 1 = 1 AND T.COL1 = 111    AND T.cccc = ? | 1 |

| ***.**.**.3 | 142892605 | 3222098456 | 680187165 | 2022-02-05 11:29:06.367558 | 876094 | 2 | INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT COL1,COL2.... FROM xxxxxxxx T WHERE 1 = 1 AND T.COL1 = 111    AND T.cccc = ? | 1 |

+-------------+-----------+------------+--------------+----------------------------+-------------------------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+

10 rows in set (18.54 sec)



MySQL [oceanbase]> select /*+parallel(32)*/ distinct sql_id,query_sql from gv$sql_audit where query_sql like %INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT%;



+----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| sql_id | query_sql |

+----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| 3DE6286E8DAABD362013C25C27603A9A | INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT COL1,COL2.... FROM xxxxxxxx T WHERE 1 = 1 AND T.COL1 = 111    AND T.cccc = ? |

+----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (18.08 sec)



MySQL [oceanbase]> select plan_id, operator, name, rows, cost from gv$plan_cache_plan_explain where tenant_id=1003 and ip=134.84.21.3 and port=2882 and plan_id=142892605;

+-----------+-------------------+------+--------+-------+

| plan_id | operator          | name | rows | cost |

+-----------+-------------------+------+--------+-------+

| 142892605 | PHY_INSERT | NULL | 100000 | 42291 |

| 142892605 | PHY_SUBPLAN_SCAN | NULL | 100000 | 40788 |

| 142892605 | PHY_TABLE_SCAN | T | 100000 | 39285 |

+-----------+-------------------+------+--------+-------+

3 rows in set (0.00 sec)



obclient> select index_name, listagg(column_name, ,) within group (order by column_position)

-> from all_ind_columns

-> where table_name = upper(xxxxxxxx)

-> group by index_name;

+--------------------------------+-------------------------------------------------------------+

| INDEX_NAME | LISTAGG(COLUMN_NAME,,)WITHINGROUP(ORDERBYCOLUMN_POSITION) |

+--------------------------------+-------------------------------------------------------------+

| IDX_xxxxxxxx | cccc |

+--------------------------------+-------------------------------------------------------------+

1 row in set (0.47 sec)


select index_name, listagg(column_name, ,) within group (order by column_position)

from all_ind_columns

where table_name = upper(xxxxxxxx)

group by index_name;


MySQL [oceanbase]> alter system flush plan cache tenant=crm;

Query OK, 0 rows affected (0.71 sec)


MySQL [oceanbase]> select /*+parallel(32)*/ svr_ip,

-> plan_id,

-> sid,

-> elapsed_time,

-> usec_to_time(request_time) req_time,

-> memstore_read_row_count,

-> ssstore_read_row_count,

-> query_sql,

-> plan_type

-> from gv$sql_audit

-> where tenant_id = 1003

-> and sql_id=3DE6286E8DAABD362013C25C27603A9A

-> order by request_time desc limit 10;

+-------------+-----------+------------+--------------+----------------------------+-------------------------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+

| svr_ip | plan_id | sid | elapsed_time | req_time | memstore_read_row_count | ssstore_read_row_count | query_sql | plan_type |

+-------------+-----------+------------+--------------+----------------------------+-------------------------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+

| ***.**.**.3 | 144370423 | 3222098456 | 14542 | 2022-02-05 12:02:22.498620 | 2 | 1 | INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT COL1,COL2.... FROM xxxxxxxx T WHERE 1 = 1 AND T.COL1 = 111    AND T.cccc = ? | 1 |

| ***.**.**.3 | 144370423 | 3222098456 | 9047 | 2022-02-05 12:02:22.498620 | 14 | 7 | INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT COL1,COL2.... FROM xxxxxxxx T WHERE 1 = 1 AND T.COL1 = 111    AND T.cccc = ? | 1 |

| ***.**.**.3 | 144370423 | 3222098456 | 13258 | 2022-02-05 12:02:22.498620 | 14 | 7 | INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT COL1,COL2.... FROM xxxxxxxx T WHERE 1 = 1 AND T.COL1 = 111    AND T.cccc = ? | 1 |

| ***.**.**.3 | 144370423 | 3222098456 | 4943 | 2022-02-05 12:02:22.498620 | 14 | 7 | INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT COL1,COL2.... FROM xxxxxxxx T WHERE 1 = 1 AND T.COL1 = 111    AND T.cccc = ? | 1 |

| ***.**.**.3 | 144370423 | 3222098456 | 7446 | 2022-02-05 12:02:22.498620 | 4 | 2 | INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT COL1,COL2.... FROM xxxxxxxx T WHERE 1 = 1 AND T.COL1 = 111    AND T.cccc = ? | 1 |

| ***.**.**.3 | 144370423 | 3222098456 | 10694 | 2022-02-05 12:02:22.498620 | 14 | 7 | INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT COL1,COL2.... FROM xxxxxxxx T WHERE 1 = 1 AND T.COL1 = 111    AND T.cccc = ? | 1 |

| ***.**.**.3 | 144370423 | 3222098456 | 11951 | 2022-02-05 12:02:22.498620 | 14 | 7 | INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT COL1,COL2.... FROM xxxxxxxx T WHERE 1 = 1 AND T.COL1 = 111    AND T.cccc = ? | 1 |

| ***.**.**.3 | 144370423 | 3222098456 | 2051 | 2022-02-05 12:02:22.498620 | 14 | 7 | INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT COL1,COL2.... FROM xxxxxxxx T WHERE 1 = 1 AND T.COL1 = 111    AND T.cccc = ? | 1 |

| ***.**.**.3 | 144370423 | 3222098456 | 3532 | 2022-02-05 12:02:22.498620 | 14 | 7 | INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT COL1,COL2.... FROM xxxxxxxx T WHERE 1 = 1 AND T.COL1 = 111    AND T.cccc = ? | 1 |

| ***.**.**.3 | 144370423 | 3222098456 | 6276 | 2022-02-05 12:02:22.498620 | 14 | 7 | INSERT INTO xxxxxxxxxxxxxxx (COL1,COL2....) SELECT COL1,COL2.... FROM xxxxxxxx T WHERE 1 = 1 AND T.COL1 = 111    AND T.cccc = ? | 1 |

+-------------+-----------+------------+--------------+----------------------------+-------------------------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+

10 rows in set (7.98 sec)

MySQL [oceanbase]> select plan_id, operator, name, rows, cost from gv$plan_cache_plan_explain where tenant_id=1003 and ip=***.**.**.3 and port=2882 and plan_id=144370423;

+-----------+-------------------+-----------------------------------+------+------+

| plan_id | operator          | name | rows | cost |

+-----------+-------------------+-----------------------------------+------+------+

| 144370423 | PHY_INSERT | NULL | 7 | 365 |

| 144370423 | PHY_SUBPLAN_SCAN | NULL | 7 | 364 |

| 144370423 | PHY_TABLE_SCAN | T(IDX_xxxxxxxx) | 7 | 364 |

+-----------+-------------------+-----------------------------------+------+------+

3 rows in set (0.00 sec)

场景二

有时业务需要看最近的sql成功次数，或者是否成功。

--可以查看近期执行结果，通过sqlid或者querysql筛选，通过ret_Code判断是否成功：

select SVR_IP,SVR_PORT,TRACE_ID,ret_code,usec_to_time(request_time),sql_id from gv$sql_audit where ret_code=-5708  and usec_to_time(request_time)>2022-02-10 09:40:00 order by usec_to_time(request_time);



| ***.**.**.9 |     2882 | YB4286541005-0005D37414A63C5F-0-0 |        0 | 2022-02-09 09:27:00.847607 | CB339EDEC37BABF3B01F6BF9B3E013F5 |

| ***.**.**.9 | 2882 | YB4286541005-0005D372F8259430-0-0 | 0 | 2022-02-09 09:27:00.853458 | CB339EDEC37BABF3B01F6BF9B3E013F5 |

| ***.**.**.8 |     2882 | YB4286541006-0005D374A14BDEA5-0-0 |    -5114 | 2022-02-09 09:36:46.380526 | CB339EDEC37BABF3B01F6BF9B3E013F5 |

| ***.**.**.8 | 2882 | YB4286541006-0005D374A4FDFE27-0-0 | -5114 | 2022-02-09 09:42:17.625995 | CB339EDEC37BABF3B01F6BF9B3E013F5 |

--可以查看当前执行的相关语句来自哪个客户端，debug错误语句来源时用到了。

select id,user,tenant,host,db,command,user_client_ip,sql_id from __all_virtual_processlist where command <> Sleep and sql_id=ECF34A6E77D9B646241eea8953db592f;

select query_sql from gv$sql_audit where sql_id = E85276AA267EF26692CBE6CE3F5CB436;

select SVR_IP,SVR_PORT,TRACE_ID,ret_code,usec_to_time(request_time),sql_id from gv$sql_audit where sql_id=ECF34A6E77D9B646241eea8953db592f and usec_to_time(request_time)>2022-02-09 09:15:00 order by  usec_to_time(request_time);

文章总结

因为对于OB的理解还有局限，所以可能整理的有些乱，但都是我们项目中用到过或者我觉的还有用的。也希望可以帮到奋斗在国产化道路上的同僚，因为这些资料能获取到的方式不多，只能通过自己的一点点积累来丰富，大家相互分享相互成长！

行之所向，莫问远方！

本文作者：张瑞远

本文来源：IT那活儿（上海新炬王翦团队）

云服务器 GPU云服务器 SQL优化方法云服务器ecs linux io占用高问题排查方法优化SQL sql优化

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129576.html

厉害了，蚂蚁金服！创造了中国自己的数据库OceanBase（下）

摘要：技术成就划时代的分布式数据库通过核心业务的不断上线，蚂蚁金服帮助渡过了自研基础软件产品最艰难的应用关。年天猫双十一，支付宝创造了万笔每秒支付峰值的业界新纪录，这对于数据库来说，意味着每秒需要同时运行万条。技术成就：划时代的分布式数据库通过核心业务的不断上线，蚂蚁金服帮助OceanBase渡过了自研基础软件产品最艰难的应用关。OceanBase不只是被研发出来的，更是被用出来的，是在...

shiina 2019-06-25 18:36 评论0 收藏0

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师

我要关注我要私信

TA的文章

消息中间件故障分析一例

阅读 1346·2023-01-11 13:20
RAC双节点crash回复一例

阅读 1684·2023-01-11 13:20
ORA-600处理一例

阅读 1132·2023-01-11 13:20
双节点RAC实例2 HANG 故障分析一例

阅读 1858·2023-01-11 13:20
RAC集群节点1重启分析一例

阅读 4100·2023-01-11 13:20
CRS启动报错CRS-1656处理分享

阅读 2704·2023-01-11 13:20
oracle 12CR2打补丁报错处理一例

阅读 1385·2023-01-11 13:20
分布式缓存组件故障分析及监控优化

阅读 3597·2023-01-11 13:20

资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

OceanBase sql优化排查方法

本文作者：张瑞远

本文来源：IT那活儿（上海新炬王翦团队）

相关文章

**厉害了，蚂蚁金服！创造了中国自己的数据库OceanBase（下）**

发表评论

0条评论

IT那活儿

男|高级讲师

TA的文章

消息中间件故障分析一例

RAC双节点crash回复一例

ORA-600处理一例

双节点RAC实例2 HANG 故障分析一例

RAC集群节点1重启分析一例

CRS启动报错CRS-1656处理分享

oracle 12CR2打补丁报错处理一例

分布式缓存组件故障分析及监控优化

最新活动