【 使用环境 】生产环境 or 测试环境
【 OB or 其他组件 】OB
【 使用版本 】4.3.5
【问题描述】OB相同SLQ_ID对应多个不同的SQL文本，什么原因
【复现路径】问题出现前后相关操作
OB mysql租户，SQL为存储过程调用的，存储过程内调用的某个SQL_ID，竟然查出来几十个不同的SQL文本，什么原因？
【附件及日志】
SELECT
sql_id,
COUNT(*) AS exec_count, – 执行次数
SUM(ROUND(elapsed_time / 1000000, 3)) AS total_elapsed_s, – 总耗时（秒）
AVG(ROUND(elapsed_time / 1000000, 3)) AS avg_elapsed_s, – 平均耗时
MAX(ROUND(elapsed_time / 1000000, 3)) AS max_elapsed_s, – 最大耗时
MIN(ROUND(elapsed_time / 1000000, 3)) AS min_elapsed_s, – 最小耗时
LEFT(query_sql, 80) AS sql_text – SQL 片段
FROM oceanbase.gv$ob_sql_audit t
WHERE
pl_trace_id = ‘YB420A005670-0006527FF81A8FC4-0-0’
AND query_sql NOT LIKE ‘%explain%’
AND query_sql NOT LIKE ‘%request_time%’
GROUP BY sql_id, sql_text – 按 sql_id 分组
ORDER BY total_elapsed_s DESC – 按【汇总总耗时】降序
;
±---------------------------------±-----------±----------------±--------------±--------------±--------------±---------+
| sql_id | exec_count | total_elapsed_s | avg_elapsed_s | max_elapsed_s | min_elapsed_s | sql_text |
±---------------------------------±-----------±----------------±--------------±--------------±--------------±---------+
| 7AC4D6EA379FEECBAA5DAD19EF266AD2 | 1 | 44.451 | 44.4510000 | 44.451 | 44.451 | |
| E6FD0D94097E9EB55F5A88546BFD4781 | 60 | 43.800 | 0.7300000 | 1.924 | 0.002 | |
| 53E8DF693BA9114763C1940BFE198276 | 28 | 27.687 | 0.9888214 | 1.291 | 0.001 | |
| 6DB258E664C091891A8BD726E028CF4B | 28 | 8.125 | 0.2901786 | 0.511 | 0.001 | |
| 9148E1810D6AA52C78EEB9F993247455 | 29 | 2.212 | 0.0762759 | 0.139 | 0.000 | |
| 9585670BA2A9CC12F3912C7FB86FA175 | 29 | 1.145 | 0.0394828 | 0.079 | 0.000 | |
| BA882399BB03B41FB6306E8165EE283F | 28 | 0.583 | 0.0208214 | 0.033 | 0.000 | |
| B9A9B40613CC55B8527CAD7C7D810573 | 28 | 0.447 | 0.0159643 | 0.027 | 0.000 | |
| DAC069B5B0E27463D6BC2A4833E6D53D | 28 | 0.431 | 0.0153929 | 0.026 | 0.000 | |
| FA1BFFCDE0441CD51D9FDD9704DAE8AF | 28 | 0.362 | 0.0129286 | 0.025 | 0.000 | |
| 1D0BA376E273B9D622641124D8C59264 | 305 | 0.273 | 0.0008951 | 0.010 | 0.000 | |
| A0533C13BC288F8B86550E6B05525572 | 28 | 0.173 | 0.0061786 | 0.010 | 0.000 | |
| 34854D1205F75BF7DEE52E07C59A3F22 | 28 | 0.157 | 0.0056071 | 0.010 | 0.000 | |
| 36C07740907BC001FF65C7C8143698C2 | 28 | 0.108 | 0.0038571 | 0.007 | 0.000 | |
| 24A5B316913A3DAAE8B9C01D1EA852E6 | 60 | 0.082 | 0.0013667 | 0.002 | 0.000 | |
| 65A98A463B5E103423577BA24C18E27D | 60 | 0.081 | 0.0013500 | 0.005 | 0.001 | |
…
±---------------------------------±-----------±----------------±--------------±--------------±--------------±---------+
128 rows in set (7.030 sec)
查询sql_id对应的SQL文本：
obclient(root@sys)[oceanbase]> select distinct QUERY_SQL from oceanbase.gv$ob_sql_audit where sql_id = ‘E6FD0D94097E9EB55F5A88546BFD4781’ \G
*************************** 1. row ***************************
QUERY_SQL: delete from roctmpdb.tmp_private_sheettypesum_bill where tmpsessionid=:8
*************************** 2. row ***************************
QUERY_SQL: SET @errormsg=’’
*************************** 3. row ***************************
QUERY_SQL: SELECT (SELECT concat(@debugmsg,’\n’,CURRENT_TIMESTAMP(2),’ receiptohterdays数据生成’) AS SUBQUERY) as ‘subquery’
*************************** 4. row ***************************
QUERY_SQL: SELECT (SELECT concat(@debugmsg,’\n’,CURRENT_TIMESTAMP(2),’ 汇总日志及通讯’) AS SUBQUERY) as ‘subquery’
*************************** 5. row ***************************
QUERY_SQL: SELECT (SELECT concat(@debugmsg,’\n’,CURRENT_TIMESTAMP(2),’ 清理环境’) AS SUBQUERY) as ‘subquery’
*************************** 6. row ***************************
QUERY_SQL: select (select CONCAT(IFNULL(:0,‘空值’),replace(replace(@errormsg,:1,’’),:0,’’),:1) AS SUBQUERY) AS subquery
*************************** 7. row ***************************
QUERY_SQL: delete from roctmpdb.tmp_sys_jxcgoodslistday1 where tmpsessionid=:14
*************************** 8. row ***************************
QUERY_SQL: SELECT (SELECT concat(@debugmsg,’\n’,CURRENT_TIMESTAMP(2),’ 删除临时表’) AS SUBQUERY) as ‘subquery’
*************************** 9. row ***************************
QUERY_SQL: SELECT (SELECT concat(@debugmsg,’\n’,CURRENT_TIMESTAMP(2),’ 清理临时表’) AS SUBQUERY) as ‘subquery’
*************************** 10. row ***************************
QUERY_SQL: update cronjobdb.cronjob set topic = (mod(jobid,@topicnum)+@topicfrom) where jobid>:7 and dbname = :9
*************************** 11. row ***************************
QUERY_SQL: delete from roctmpdb.tmp_sys_notsalecostdetail where tmpsessionid = :14
*************************** 12. row ***************************
QUERY_SQL: delete from roctmpdb.tmp_private_csgpc_ct_shopgrplist where tmpsessionid=:12
*************************** 13. row ***************************
QUERY_SQL: SELECT (SELECT concat(@debugmsgmain,’\n’,‘清理环境’,now()) AS SUBQUERY) as ‘subquery’
*************************** 14. row ***************************
QUERY_SQL: SELECT (SELECT concat(@debugmsg,’\n’,now(),’ 清理临时表’) AS SUBQUERY) as ‘subquery’
*************************** 15. row ***************************
QUERY_SQL: SELECT !EXISTS (select 1 as “1” from pricechangelog_eslshop
where entid=:0 and shopid=:1) as ‘subquery’
*************************** 16. row ***************************
QUERY_SQL: delete from roctmpdb.tmp_private_csgpc_vender_gdid where tmpsessionid=:9
*************************** 17. row ***************************
QUERY_SQL: INSERT INTO mdmgoodsdb.taskcmd ( cmdinfo, tasktype, cmdstatus, cmdpara1, cmdpara2, dbsplitcode, entid )
SELECT DISTINCT
tk.itemname,
tk.tasktype,
0 as “0”,
cp.entid,
cp.gdid,
cp.dbsplitcode,
cp.entid
FROM
mdmgoodsdb.goodsbar cp
LEFT JOIN glsdb.tasktypeitem tk ON 1 = 1
WHERE cp.modifytime >= NOW()-interval 40 minute AND
tk.itemtable = ‘goodsbar’ and tk.tasktype = ‘ROC_TO_ZY_GOODSBAR’ and cp.entid = 1
*************************** 18. row ***************************
QUERY_SQL: delete from roctmpdb.tmp_jobkey where tmpsessionid = :5
*************************** 19. row ***************************
QUERY_SQL: SELECT id,entid,shopid, billid
from costtag_shopbill
where scanflag = 0 and divgroup = :0
*************************** 20. row ***************************
QUERY_SQL: delete from roctmpdb.tmp_private_popsheetlist where tmpsessionid=:14
*************************** 21. row ***************************
QUERY_SQL: insert into cronjob
( dbname, jobtype, topic, procname,procpara,proctype,
tcontroller, maxtries, reference,description,
launchtime,priority )
values
( :0,‘A’, :16, :2,:3,:4,
:5,5,:6,:7,:21,:20 )
*************************** 22. row ***************************
QUERY_SQL: SELECT sheetid,promflag,startdate
from weightpopsheetlist a
where entid=:0 and shopid=:1 and startdate<=:2 and exeflag=0
– and f_is_omp_valid(a.entid,a.shopid,a.sheetid)
order by startdate,checkdate,sheetid
*************************** 23. row ***************************
QUERY_SQL: delete from popsheetlist
where entid=:0 and shopid=:1
and enddate<DATE_ADD(current_date,INTERVAL -30 DAY)
*************************** 24. row ***************************
QUERY_SQL: INSERT INTO mdmgoodsdb.popsheetlist ( entid , dbsplitcode , sheetid , sheettype , checkdate , startdate , enddate , shopid , exedate , exeflag , orderid , modifytime , canceldate , cancelflag , cancelexeflag )
select DISTINCT :0 as “i_entid”, ‘1’, :2 as “i_billid”,501101 sheettype,:15 as “v_curdatetime”,a.sta_date, a.end_date,:1 as “i_shopid”,null as exedate,0 as exeflag ,0 orderid , now() modifytime ,null as canceldate, 0 as cancelflag, 0 as cancelexeflag
from pricetag_roc a
where a.org_code = :1
and a.billid = :2
and a.ent_id = :0
and a.prcmode = ‘1’
*************************** 25. row ***************************
QUERY_SQL: select count(1) as “count(1)” from mdmgoodsdb.send_service_log
*************************** 26. row ***************************
QUERY_SQL: delete from roctmpdb.tmp_currpopricesheet_all where tmpsessionid=:8
*************************** 27. row ***************************
QUERY_SQL: delete from roctmpdb.tmp_private_popcostlist where tmpsessionid=:19
*************************** 28. row ***************************
QUERY_SQL: INSERT INTO mdmgoodsdb.taskcmd ( cmdinfo, tasktype, cmdstatus, cmdpara1, dbsplitcode, entid )
VALUES(‘changeunitgoodsexc传牵牛花’,‘BATCH_CHANGEUNITGOODSEXC_DCN’,0,1,1,1)
*************************** 29. row ***************************
QUERY_SQL: INSERT INTO mdmgoodsdb.taskcmd ( cmdinfo, tasktype, cmdstatus, cmdpara1, cmdpara2, dbsplitcode, entid )
SELECT DISTINCT
tk.itemname,
tk.tasktype,
0 as “0”,
cp.entid,
cp.gdid,
cp.dbsplitcode,
cp.entid
FROM
mdmgoodsdb.goodspicts cp
LEFT JOIN glsdb.tasktypeitem tk ON 1 = 1
WHERE cp.gdid in (select gdid from mdmgoodsdb.goods where modifytime > NOW()-interval 30 minute) and tk.itemtable = ‘goodspicts’
*************************** 30. row ***************************
QUERY_SQL: update mdmgoodsdb.promdeductratelist a INNER JOIN mdmgoodsdb.goodsshop b on a.shopid = b.shopgrpid and a.gdid = b.gdid and a.entid = b.entid and b.defaultflag = 1
set a.venderid = b.venderid
where sheetid = :2 and shopid = :1
and a.entid = :0
and a.venderid = ‘#’
*************************** 31. row ***************************
QUERY_SQL: delete from roctmpdb.tmp_private_pluchangelist where tmpsessionid=:10
*************************** 32. row ***************************
QUERY_SQL: delete from mdmgoodsdb.popsheetlist a where a.shopid = :1 and a.sheetid = :2 and a.entid = :0
*************************** 33. row ***************************
QUERY_SQL: delete from roctmpdb.tmp_private_sendgoodscost_dup where tmpsessionid=:8
*************************** 34. row ***************************
QUERY_SQL: select shopid
from sync_ireportdb.rptsummaryctrl a
where entid=:0 and sdate=:8
and (
(rpttype=‘autopur_calcformula_0’
and not EXISTS(select 1 as “1” from autopur_synlog b
where a.entid=b.entid and a.shopid=b.shopid
and a.sdate=b.sdate and b.rpttype=‘autopur_calcformula_0’))
or (rpttype=‘autopur_calcformula_1’
and not EXISTS(select 1 as “1” from autopur_synlog b
where a.entid=b.entid and a.shopid=b.shopid
and a.sdate=b.sdate and b.rpttype=‘autopur_calcformula_1’))
)
limit 200
*************************** 35. row ***************************
QUERY_SQL: delete from roctmpdb.tmp_private_goodscost_negative
where tmpsessionid=:14
*************************** 36. row ***************************
QUERY_SQL: select 1 as “1” from roctmpdb.tmp_private_residuallist_pc
where tmpsessionid=:7 limit 1
*************************** 37. row ***************************
QUERY_SQL: delete from roctmpdb.tmp_private_residuallist_avg where tmpsessionid=:7
*************************** 38. row ***************************
QUERY_SQL: delete from roctmpdb.tmp_private_negagoodscostlist where tmpsessionid=:7
*************************** 39. row ***************************
QUERY_SQL: delete from roctmpdb.tmp_private_sendaccsheetlist where tmpsessionid=:9
*************************** 40. row ***************************
QUERY_SQL: SELECT (SELECT concat(@debugmsg,’\n’,CURRENT_TIMESTAMP(2),’ 开始’) AS SUBQUERY) as ‘subquery’
*************************** 41. row ***************************
QUERY_SQL: insert into checkdata_u51toroc_roc(sdate,datasource,sheettype,outtablename,intablename,inshopid,outshopid,venderid,
sheetnum,goodsnum,askqty,ingoodsqty,incostvalue,outgoodsqty,outcostvalue,entid)
select date(:8) sdate,a.refsource datasource,‘u51销售处理’ sheettype,‘salecost,salecostitem’ outtablename,‘salecost,salecostitem’ intablename,a.shopid,null outshopid,null venderid,
count(distinct a.sheetid) sheetnum,count(distinct gdid) goodsnum,null askqty,sum(-1directflagqty) ingoodsqty,sum(-1directflagcostvalue) incostvalue,null outgoodsqty,sum(-1directflag(salevalue-discvalue)) outcostvalue,:0 as “i_entid”
from somdb.salecost a,somdb.salecostitem b
where date(a.sdate)=date(:8) and a.sheetid=b.sheetid and a.entid=:0 and a.entid = b.entid
group by a.refsource,a.shopid
*************************** 42. row ***************************
QUERY_SQL: update store_replenishment_fail a
inner join sync_mdmdb.currpopcost b
on a.entid=b.entid and a.shopid=b.shopid
and a.gdid=b.gdid and a.venderid=b.venderid
and a.sdate between b.popstartdate and b.popenddate
set a.popcost=case when b.popcost is null then a.cost else b.popcost end,
a.popnetcost=case when b.popcost is null then a.netcost else b.popcost/(1+a.costtaxrate*0.01) end
where a.entid=:0 and a.shopid=:1 and a.sdate=:2
*************************** 43. row ***************************
QUERY_SQL: delete from nextpurdatemode
where entid=:0 and sdate<DATE_ADD(current_date,INTERVAL -10 DAY)
*************************** 44. row ***************************
QUERY_SQL: SELECT (SELECT @TESTFLAG is null or @TESTFLAG<>‘TEST’ AS SUBQUERY) as ‘subquery’
*************************** 45. row ***************************
QUERY_SQL: delete from roctmpdb.tmp_private_datelist_saledate where tmpsessionid=:11
*************************** 46. row ***************************
QUERY_SQL: update cronjobdb.cronjob set topic = (mod(jobid,@topicnum)+@topicfrom) where jobid>:13 and dbname=:12
*************************** 47. row ***************************
QUERY_SQL: delete from roctmpdb.tmp_sys_returnbasesumcatday where tmpsessionid=:14
*************************** 48. row ***************************
QUERY_SQL: delete from roctmpdb.tmp_private_rationnotifyitem_exp where tmpsessionid=:14
*************************** 49. row ***************************
QUERY_SQL: select 1 as “1” from dataconfigdb.data_send_target
where entid=:0 and cmdcode=upper(:4) limit 1
*************************** 50. row ***************************
QUERY_SQL: SELECT (SELECT concat(@debugmsg,’\n’,CURRENT_TIMESTAMP(2),’ 环境准备’) AS SUBQUERY) as ‘subquery’
*************************** 51. row ***************************
QUERY_SQL: delete from autopurbalancedms
where entid=:0 and shopid=:1 and sdate <= :11
*************************** 52. row ***************************
QUERY_SQL: SELECT (SELECT concat(@debugmsg,’\n’,CURRENT_TIMESTAMP(2),’ 完成’) AS SUBQUERY) as ‘subquery’
*************************** 53. row ***************************
QUERY_SQL: SELECT (SELECT concat(@debugmsg,’\n’,CURRENT_TIMESTAMP(2),’ 删除历史数据’) AS SUBQUERY) as ‘subquery’
*************************** 54. row ***************************
QUERY_SQL: SELECT (SELECT @FASTFLAG IS NULL or @FASTFLAG<>‘FAST’ AS SUBQUERY) as ‘subquery’
*************************** 55. row ***************************
QUERY_SQL: delete from roctmpdb.tmp_sys_dashboard_shopchannelsum where tmpsessionid = :19
*************************** 56. row ***************************
QUERY_SQL: call dcsrcdb.sp_create_sellpaymx(?,?,?);
*************************** 57. row ***************************
QUERY_SQL: SELECT (SELECT concat(@debugmsg,’\n’,CURRENT_TIMESTAMP(2),’ 清理临时表’) AS SUBQUERY) as ‘subquery’
*************************** 58. row ***************************
QUERY_SQL: SELECT id,entid,shopid, billid
from pricetag_shopbill
where scanflag = 0 and divgroup = :0
*************************** 59. row ***************************
QUERY_SQL: update weightpopsheetlist
set exedate=now(),exeflag=1
where entid=:0 and shopid=:1 and sheetid=:9
59 rows in set (12.915 sec)
