sql优化—cpu耗时几万毫秒

OceanBase 技术问题
#1

SELECT GPH.BLORG_NO, GPH.CCY, GPH.CUSTOMER_NO, GPH.SUBJ_NO, GPH.AMT_TYPE, GPH.COR_SEQNO, GPH.ACCTNT_ENTRY_NO, GPH.PRFT_CNTR, GPH.SOURCE_MODULE_TYPE_CD, GPH.QRY_CHNL, GPH.TRANS_SEQ_IDNTFNO, GPH.TRANS_REFCNO, GPH.POST_DT, GPH.BOOKKEEPING_DT, GPH.NARRATIVE, GPH.AMT, GPH.REV_FLAG, GPH.STAT_CD, GPH.ACCTNO_KEYVAL, GPH.BAL_DEAL_FLAG, GPH.PROD_BUSI_EVENT_CD, GPH.PARATYPE_IND, GPH.GL_SYS_RUN_DT, GPH.TRANS_REMARK, GPH.ACCTNT_TERM, GPH.AFLT_LPR_CD, GPH.BASIC_ACCTNO, GPH.ACCTNT_BOOKKEEPING_DBTCRDT_IND, GPH.PRODC_TP, GPH.CHNL_DT, GPH.CLEAR_ENTRY_STANDARD, GPH.CHNL_SEQNO, GPH.MKTG_PROD_NO, GPH.MKTG_PROD_NM, GPH.BUSI_PROD_CD, GPH.INDEX_NO, GPH.DPSTRM_TERM, GPH.TERM_TYPE, GPH.SYS_ID, GPH.TRANS_TELLER, GPH.ACCT_CRSP_CUSTOMER_NO, GPH.COMMN_INDS_CD, GPH.TAILBOX_NO, GPH.CUSTOMER_MGR_NO, GPH.FNDRS_TYPE_CD, GPH.INTAMT_PCSMED_IND, GPH.DRAWINT_FREQ, GPH.TRANS_CD, GPH.TELLER_LANG_CD, GPH.ACCTNT_SQUANACCT_STAC_NO, GPH.MNL_BOOKKEEPING_IND, GPH.COLT_BOOKKEEPING_IND, GPH.CROSYS_DUTY_SYS_NO, GPH.INLAND_OVSEA_IND, GPH.ECON_CMPONT_CD, GPH.STAC_CD, GPH.RULE_NO, GPH.OFBS_SUBJ_FLAG, GPH.TRANS_VARTY_CD, GPH.GRP_INNER_NOS_ORG_NO, NULL AS CUSTOMER_TYPE, GPH.TRANS_TM FROM GL_POST_HIST GPH WHERE 1 = 1 AND ( GPH.PTXSP_MID_ENTRY_FLAG = ‘0’ OR GPH.PTXSP_MID_ENTRY_FLAG IS NULL ) AND GPH.BLORG_NO IN ( ‘0006’, ‘0021’, ‘0812’, ‘1111’, ‘1115’, ‘0111’, ‘0112’, ‘0113’, ‘0132’, ‘0351’, ‘0411’, ‘0412’, ‘0421’, ‘0432’, ‘1011’, ‘1032’, ‘1033’, ‘0211’, ‘0212’, ‘0413’, ‘0431’, ‘0511’, ‘0711’, ‘0712’, ‘0811’, ‘0821’, ‘1022’, ‘1031’, ‘1112’, ‘0121’, ‘0251’, ‘0341’, ‘0422’, ‘0522’, ‘0541’, ‘0751’, ‘0911’, ‘0913’, ‘0916’, ‘0921’, ‘0939’, ‘0115’, ‘0222’, ‘0231’, ‘0551’, ‘0571’, ‘0572’, ‘0721’, ‘0732’, ‘0931’, ‘1113’, ‘1212’, ‘0311’, ‘0331’, ‘0332’, ‘0333’, ‘0441’, ‘0822’, ‘0841’, ‘0842’, ‘0843’, ‘1114’, ‘0513’, ‘0641’, ‘1501’, ‘1502’, ‘1503’, ‘0133’, ‘0232’, ‘0233’, ‘0234’, ‘0512’, ‘0762’, ‘0941’, ‘1012’, ‘1021’, ‘1023’, ‘1211’, ‘0241’, ‘0242’, ‘0761’, ‘0932’, ‘1311’, ‘1312’, ‘1313’, ‘0312’, ‘0321’, ‘0442’, ‘0521’, ‘0523’, ‘0552’, ‘0561’, ‘0611’, ‘0666’, ‘0713’, ‘0722’, ‘0723’, ‘0831’, ‘1362’, ‘1363’, ‘1364’, ‘1365’, ‘1367’, ‘1369’, ‘1370’, ‘1372’, ‘1375’, ‘1378’, ‘0050’, ‘2001’, ‘2002’, ‘2003’, ‘2051’, ‘0051’, ‘2400’, ‘2401’, ‘2402’, ‘2403’, ‘0052’, ‘2200’, ‘2201’, ‘2202’, ‘0053’, ‘2600’, ‘2601’, ‘0054’, ‘2800’, ‘2801’, ‘0055’, ‘2900’, ‘0001’, ‘0003’, ‘0004’, ‘0005’, ‘0007’, ‘0008’, ‘0011’, ‘0016’, ‘0019’, ‘0020’, ‘0022’, ‘1358’, ‘1359’, ‘2203’, ‘9000’, ‘9001’, ‘9002’, ‘9003’, ‘9004’, ‘9005’, ‘1360’, ‘1361’, ‘1397’, ‘0000’ ) AND GPH.CCY = ‘CNY’ AND GPH.SUBJ_NO NOT IN ( ‘99999999’ ) AND GPH.POST_DT >= ‘2024-08-19’ AND GPH.POST_DT <= ‘2024-09-17’ AND GPH.AMT >= 4999 AND GPH.AMT <= 4999 AND GPH.REV_FLAG != ‘X’ AND ( GPH.CLEAR_ENTRY_STANDARD != ‘Y’ OR GPH.CLEAR_ENTRY_STANDARD IS NULL ) UNION ALL SELECT BGPH.BLORG_NO, BGPH.CCY, BGPH.CUSTOMER_NO, BGPH.SUBJ_NO, BGPH.AMT_TYPE, BGPH.COR_SEQNO, BGPH.ACCTNT_ENTRY_NO, BGPH.PRFT_CNTR, BGPH.SOURCE_MODULE_TYPE_CD, BGPH.QRY_CHNL, BGPH.TRANS_SEQ_IDNTFNO, BGPH.TRANS_REFCNO, BGPH.POST_DT, BGPH.BOOKKEEPING_DT, BGPH.NARRATIVE, BGPH.AMT, BGPH.REV_FLAG, BGPH.STAT_CD, BGPH.ACCTNO_KEYVAL, BGPH.BAL_DEAL_FLAG, BGPH.PROD_BUSI_EVENT_CD, BGPH.PARATYPE_IND, BGPH.GL_SYS_RUN_DT, BGPH.TRANS_REMARK, BGPH.ACCTNT_TERM, BGPH.AFLT_LPR_CD, BGPH.BASIC_ACCTNO, BGPH.ACCTNT_BOOKKEEPING_DBTCRDT_IND, BGPH.PRODC_TP, BGPH.CHNL_DT, BGPH.CLEAR_ENTRY_STANDARD, BGPH.CHNL_SEQNO, BGPH.MKTG_PROD_NO, BGPH.MKTG_PROD_NM, BGPH.BUSI_PROD_CD, BGPH.INDEX_NO, BGPH.DPSTRM_TERM, BGPH.TERM_TYPE, BGPH.SYS_ID, BGPH.TRANS_TELLER, BGPH.ACCT_CRSP_CUSTOMER_NO, BGPH.COMMN_INDS_CD, BGPH.CUSTOMER_MGR_NO, BGPH.TAILBOX_NO, BGPH.FNDRS_TYPE_CD, BGPH.INTAMT_PCSMED_IND, BGPH.DRAWINT_FREQ, BGPH.TRANS_CD, BGPH.TELLER_LANG_CD, BGPH.ACCTNT_SQUANACCT_STAC_NO, BGPH.MNL_BOOKKEEPING_IND, BGPH.COLT_BOOKKEEPING_IND, BGPH.CROSYS_DUTY_SYS_NO, BGPH.INLAND_OVSEA_IND, BGPH.ECON_CMPONT_CD, BGPH.STAC_CD, BGPH.RULE_NO, BGPH.OFBS_SUBJ_FLAG, BGPH.TRANS_VARTY_CD, BGPH.GRP_INNER_NOS_ORG_NO, BGPH.CUSTOMER_TYPE, BGPH.TRANS_TM FROM BS_GL_POST_HIST BGPH WHERE 1 = 1 AND ( BGPH.PTXSP_MID_ENTRY_FLAG = ‘0’ OR BGPH.PTXSP_MID_ENTRY_FLAG IS NULL ) AND BGPH.BLORG_NO IN ( ‘0006’, ‘0021’, ‘0812’, ‘1111’, ‘1115’, ‘0111’, ‘0112’, ‘0113’, ‘0132’, ‘0351’, ‘0411’, ‘0412’, ‘0421’, ‘0432’, ‘1011’, ‘1032’, ‘1033’, ‘0211’, ‘0212’, ‘0413’, ‘0431’, ‘0511’, ‘0711’, ‘0712’, ‘0811’, ‘0821’, ‘1022’, ‘1031’, ‘1112’, ‘0121’, ‘0251’, ‘0341’, ‘0422’, ‘0522’, ‘0541’, ‘0751’, ‘0911’, ‘0913’, ‘0916’, ‘0921’, ‘0939’, ‘0115’, ‘0222’, ‘0231’, ‘0551’, ‘0571’, ‘0572’, ‘0721’, ‘0732’, ‘0931’, ‘1113’, ‘1212’, ‘0311’, ‘0331’, ‘0332’, ‘0333’, ‘0441’, ‘0822’, ‘0841’, ‘0842’, ‘0843’, ‘1114’, ‘0513’, ‘0641’, ‘1501’, ‘1502’, ‘1503’, ‘0133’, ‘0232’, ‘0233’, ‘0234’, ‘0512’, ‘0762’, ‘0941’, ‘1012’, ‘1021’, ‘1023’, ‘1211’, ‘0241’, ‘0242’, ‘0761’, ‘0932’, ‘1311’, ‘1312’, ‘1313’, ‘0312’, ‘0321’, ‘0442’, ‘0521’, ‘0523’, ‘0552’, ‘0561’, ‘0611’, ‘0666’, ‘0713’, ‘0722’, ‘0723’, ‘0831’, ‘1362’, ‘1363’, ‘1364’, ‘1365’, ‘1367’, ‘1369’, ‘1370’, ‘1372’, ‘1375’, ‘1378’, ‘0050’, ‘2001’, ‘2002’, ‘2003’, ‘2051’, ‘0051’, ‘2400’, ‘2401’, ‘2402’, ‘2403’, ‘0052’, ‘2200’, ‘2201’, ‘2202’, ‘0053’, ‘2600’, ‘2601’, ‘0054’, ‘2800’, ‘2801’, ‘0055’, ‘2900’, ‘0001’, ‘0003’, ‘0004’, ‘0005’, ‘0007’, ‘0008’, ‘0011’, ‘0016’, ‘0019’, ‘0020’, ‘0022’, ‘1358’, ‘1359’, ‘2203’, ‘9000’, ‘9001’, ‘9002’, ‘9003’, ‘9004’, ‘9005’, ‘1360’, ‘1361’, ‘1397’, ‘0000’ ) AND BGPH.CCY = ‘CNY’ AND BGPH.SUBJ_NO NOT IN ( ‘99999999’ ) AND BGPH.POST_DT >= ‘2024-08-19’ AND BGPH.POST_DT <= ‘2024-09-17’ AND BGPH.AMT >= 4999 AND BGPH.AMT <= 4999 AND BGPH.REV_FLAG != ‘X’ AND ( BGPH.CLEAR_ENTRY_STANDARD != ‘Y’ OR BGPH.CLEAR_ENTRY_STANDARD IS NULL )

#3

sql+执行计划.txt (20.7 KB)

#4

1、 全链路诊断中的Show Trace功能，也能轻松发现性能瓶颈并进行进一步的分析和调优。
2、obdiag收集一下
----根据时间和执行语句查询trace_id（执行过的语句获取trace_id方式）
select query_sql,svr_ip,TRACE_ID,client_ip,TENANT_NAME,user_name,DB_NAME,ELAPSED_TIME,RET_CODE,FROM_UNIXTIME(ROUND(REQUEST_TIME/1000/1000),’%Y-%m-%d %H:%i:%S’) from GV$OB_SQL_AUDIT
WHERE REQUEST_TIME>=‘2024-04-05 14:34:00’ and lower(query_sql) like ‘%select%’;

收集SQL性能问题信息

obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-hxx -Pxx -uxx -pxx -Dxx’, trace_id=‘xx’}”
https://www.oceanbase.com/docs/common-obdiag-cn-1000000001491226

SQL 详情收集

obdiag gather plan_monitor --trace_id YB420BA2D99B-0005EBBFC45D5A00-0-0 --env"{db_connect=’-hxx -Pxx -uxx -pxx -Dxx’}"

  1. 以上两种方式可能并不是万能的，接下来可以按以下步骤尝试自查分析。
    a. 通过查看gv$ob_sql_audit审计视图确认影响执行耗时的等待事件。
    select query_sql,svr_ip,TRACE_ID,client_ip,TENANT_NAME,user_name,DB_NAME,ELAPSED_TIME,RET_CODE,FROM_UNIXTIME(ROUND(REQUEST_TIME/1000/1000),’%Y-%m-%d %H:%i:%S’) from GV$OB_SQL_AUDIT
    WHERE REQUEST_TIME>=‘2024-04-05 14:34:00’ and lower(query_sql) like ‘%select%’ order by elapsed_time desc limit 10;

b. 再获取 SQL 执行计划EXPLAIN EXTENDED，查看执行计划并分析。
c. 获取 sql plan monitor 信息，全过程可以参看如下操作。
1.登录sys租户设置 sql_plan_monitor 参数
确认 sql_plan_monitor 已经打开
show parameters like ‘enable_sql_audit’;

如果 enable_sql_audit = False 则将其打开
alter system enable_sql_audit = true;

  1. 登录业务租户，获取 sql 的执行计划
    EXPLAIN EXTENDED sql语句;

  2. 设置临时 trace 获取
    SET ob_enable_show_trace=‘ON’;

  3. 再次执行需要采集的 sql 语句

  4. 获取上一步执行的 sql 的 trace_id 信息
    select last_trace_id();

  5. 临时关闭 plan monitor 数据，防止信息被覆盖
    alter system enable_sql_audit = false;

  6. 获取 plan monitor 的 sql ，将 xxxxx 替换为第 6 步返回的 trace_id ，以获取每个算子的吐行信息
    select plan_line_id, plan_operation, sum(output_rows), sum(STARTS) rescan, min(first_refresh_time) open_time, max(last_refresh_time) close_time, min(first_change_time) first_row_time, max(last_change_time) last_row_eof_time, count(1) from oceanbase.gv$sql_plan_monitor where trace_id = ‘xxxxx’ group by plan_line_id, plan_operation order by plan_line_id;

8 恢复 sql_audit 参数
alter system enable_sql_audit = true;

1 个赞