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

資訊專(zhuān)欄INFORMATION COLUMN

OceanBase sql優(yōu)化排查方法

IT那活兒 / 1181人閱讀
OceanBase sql優(yōu)化排查方法

點(diǎn)擊上方“IT那活兒”，關(guān)注后了解更多內(nèi)容，不管IT什么活兒，干就完了！！！





文章前言



接觸OB的項(xiàng)目也有一段時(shí)間了，除了環(huán)境的問(wèn)題，在O遷移ob過(guò)程中也存在很多的sql的問(wèn)題，具體的sql調(diào)優(yōu)技巧我就暫時(shí)不做介紹了，OB的優(yōu)化工程師的直播講解很詳細(xì)(https://open.oceanbase.com/docs/videoCenter/5900015)。我簡(jiǎn)單記錄下自己學(xué)習(xí)到和生產(chǎn)用到的一點(diǎn)小場(chǎng)景。





場(chǎng)景一



項(xiàng)目割接之后的三天內(nèi)是效率sql的高頻出現(xiàn)時(shí)期，現(xiàn)場(chǎng)一般也會(huì)有ob的優(yōu)化工程師一直在持續(xù)做優(yōu)化，那如何來(lái)篩選問(wèn)題sql呢？

-正在在執(zhí)行的進(jìn)程：

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

--歷史SQL執(zhí)行：

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  --租戶(hù)id

and avg_exe_usec >= 1000000000        --執(zhí)行時(shí)間篩選

and last_active_time > 2022-01-19 17:20:00.000000           --時(shí)間段篩選

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語(yǔ)句：

select query_sql from gv$plan_cache_plan_stat where sql_id = CE9AXXXXX00FEA8ED885EB0;

因?yàn)橐话鉶utline創(chuàng)建名稱(chēng)會(huì)帶有sqlid。

--確認(rèn)是否已存在outline綁定：

MySQL [oceanbase]>

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

至于如何綁定outline，官網(wǎng)上有介紹，分為兩種方式一種直接綁定sqlid還有一種綁定sql語(yǔ)句。

--檢查優(yōu)化結(jié)果 （確認(rèn)使用了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;

當(dāng)然上面綁定outline只是一種方式，要優(yōu)先判斷是否有合適索引，是否沒(méi)有走索引，或者合適的連接方式。

那查看索引的方式與oracle有些類(lèi)似：

--獲得一張表的索引：

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中有時(shí)會(huì)遇到索引失效，執(zhí)行計(jì)劃變化的情況，OB也可以判斷：

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

如果我要查詢(xún)歷史的執(zhí)行計(jì)劃怎么辦？

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;

下面介紹一個(gè)ob工程師處理的執(zhí)行計(jì)劃抖動(dòng)的案例：

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)





場(chǎng)景二



有時(shí)業(yè)務(wù)需要看最近的sql成功次數(shù)，或者是否成功。

--可以查看近期執(zhí)行結(jié)果，通過(guò)sqlid或者querysql篩選，通過(guò)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 |

--可以查看當(dāng)前執(zhí)行的相關(guān)語(yǔ)句來(lái)自哪個(gè)客戶(hù)端，debug錯(cuò)誤語(yǔ)句來(lái)源時(shí)用到了。

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);






文章總結(jié)



因?yàn)閷?duì)于OB的理解還有局限，所以可能整理的有些亂，但都是我們項(xiàng)目中用到過(guò)或者我覺(jué)的還有用的。也希望可以幫到奮斗在國(guó)產(chǎn)化道路上的同僚，因?yàn)檫@些資料能獲取到的方式不多，只能通過(guò)自己的一點(diǎn)點(diǎn)積累來(lái)豐富，大家相互分享相互成長(zhǎng)！

行之所向，莫問(wèn)遠(yuǎn)方！



本文作者：張瑞遠(yuǎn)

本文來(lái)源：IT那活兒（上海新炬王翦團(tuán)隊(duì)）

文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請(qǐng)注明本文地址：http://specialneedsforspecialkids.com/yun/129576.html

相關(guān)文章

  • 厲害了，螞蟻金服！創(chuàng)造了中國(guó)自己的數(shù)據(jù)庫(kù)OceanBase（下）

    摘要：技術(shù)成就劃時(shí)代的分布式數(shù)據(jù)庫(kù)通過(guò)核心業(yè)務(wù)的不斷上線(xiàn)，螞蟻金服幫助渡過(guò)了自研基礎(chǔ)軟件產(chǎn)品最艱難的應(yīng)用關(guān)。年天貓雙十一，支付寶創(chuàng)造了萬(wàn)筆每秒支付峰值的業(yè)界新紀(jì)錄，這對(duì)于數(shù)據(jù)庫(kù)來(lái)說(shuō)，意味著每秒需要同時(shí)運(yùn)行萬(wàn)條。 技術(shù)成就：劃時(shí)代的分布式數(shù)據(jù)庫(kù) 通過(guò)核心業(yè)務(wù)的不斷上線(xiàn)，螞蟻金服幫助OceanBase渡過(guò)了自研基礎(chǔ)軟件產(chǎn)品最艱難的應(yīng)用關(guān)。OceanBase不只是被研發(fā)出來(lái)的，更是被用出來(lái)的，是在...

    shiina 評(píng)論0 收藏0

發(fā)表評(píng)論

0條評(píng)論

最新活動(dòng)
閱讀需要支付1元查看
<