OceanBase sql優化排查方法

IT那活兒發布于2023-01-11 13:20 / 1185人閱讀

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優化

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://m.specialneedsforspecialkids.com/yun/129576.html

厲害了，螞蟻金服！創造了中國自己的數據庫OceanBase（下）

摘要：技術成就劃時代的分布式數據庫通過核心業務的不斷上線，螞蟻金服幫助渡過了自研基礎軟件產品最艱難的應用關。年天貓雙十一，支付寶創造了萬筆每秒支付峰值的業界新紀錄，這對于數據庫來說，意味著每秒需要同時運行萬條。技術成就：劃時代的分布式數據庫通過核心業務的不斷上線，螞蟻金服幫助OceanBase渡過了自研基礎軟件產品最艱難的應用關。OceanBase不只是被研發出來的，更是被用出來的，是在...

shiina 2019-06-25 18:36 評論0 收藏0

發表評論

登陸后可評論

0條評論

IT那活兒

男|高級講師

我要關注我要私信

TA的文章

消息中間件故障分析一例

閱讀 1353·2023-01-11 13:20
RAC雙節點crash回復一例

閱讀 1699·2023-01-11 13:20
ORA-600處理一例

閱讀 1211·2023-01-11 13:20
雙節點RAC實例2 HANG 故障分析一例

閱讀 1904·2023-01-11 13:20
RAC集群節點1重啟分析一例

閱讀 4161·2023-01-11 13:20
CRS啟動報錯CRS-1656處理分享

閱讀 2751·2023-01-11 13:20
oracle 12CR2打補丁報錯處理一例

閱讀 1397·2023-01-11 13:20
分布式緩存組件故障分析及監控優化

閱讀 3664·2023-01-11 13:20

国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄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打補丁報錯處理一例

分布式緩存組件故障分析及監控優化

最新活動

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務器低至59元/年，更有多臺、長期優惠，快來選購！

OceanBase sql優化排查方法

本文作者：張瑞遠

本文來源：IT那活兒（上海新炬王翦團隊）

相關文章

發表評論

0條評論

男|高級講師

TA的文章

最新活動

上云采購季！| 2核2G4M爆款云服務器低至59元/年，更有多臺、長期優惠，快來選購！