clog放大问题1：通过执行计划分析分区扫摸判断clog放大
青
志梁约 28 分钟
志梁约 28 分钟ob版本：3.2.3.3
一、测试环境准备
1. 创建测试表
CREATE TABLE tbcs.test_t1 ("OID" NUMBER(14) NOT NULL ENABLE,"REGION" NUMBER(5) NOT NULL ENABLE,"RECDATE" DATE,CONSTRAINT "PK_TEST_T1" UNIQUE ("OID", "RECDATE", "REGION") USING INDEX LOCAL) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0partition by range("REGION", "RECDATE")(partition P_530_MAX values less than (530,TO_DATE(' 2037-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_531_MAX values less than (531,TO_DATE(' 2037-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_532_MAX values less than (532,TO_DATE(' 2037-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_533_MAX values less than (533,TO_DATE(' 2037-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_534_MAX values less than (534,TO_DATE(' 2037-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_535_MAX values less than (535,TO_DATE(' 2037-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_536_MAX values less than (536,TO_DATE(' 2037-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_537_202504 values less than (537,TO_DATE(' 2025-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_537_202505 values less than (537,TO_DATE(' 2025-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_537_202506 values less than (537,TO_DATE(' 2025-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_537_202507 values less than (537,TO_DATE(' 2025-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_537_202508 values less than (537,TO_DATE(' 2025-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_537_202509 values less than (537,TO_DATE(' 2025-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_537_202510 values less than (537,TO_DATE(' 2025-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_537_202511 values less than (537,TO_DATE(' 2025-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_537_202512 values less than (537,TO_DATE(' 2026-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_537_MAX values less than (537,TO_DATE(' 2037-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_538_MAX values less than (538,TO_DATE(' 2037-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_539_MAX values less than (539,TO_DATE(' 2037-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_543_MAX values less than (543,TO_DATE(' 2037-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_546_MAX values less than (546,TO_DATE(' 2037-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_631_MAX values less than (631,TO_DATE(' 2037-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_632_MAX values less than (632,TO_DATE(' 2037-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_633_MAX values less than (633,TO_DATE(' 2037-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_634_MAX values less than (634,TO_DATE(' 2037-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_635_MAX values less than (635,TO_DATE(' 2037-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_999_202504 values less than (999,TO_DATE(' 2025-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_999_202505 values less than (999,TO_DATE(' 2025-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_999_202506 values less than (999,TO_DATE(' 2025-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_999_202507 values less than (999,TO_DATE(' 2025-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_999_202508 values less than (999,TO_DATE(' 2025-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_999_202509 values less than (999,TO_DATE(' 2025-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_999_202510 values less than (999,TO_DATE(' 2025-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_999_202511 values less than (999,TO_DATE(' 2025-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_999_202512 values less than (999,TO_DATE(' 2026-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),partition P_999_MAX values less than (999,TO_DATE(' 2037-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
2. 查看表的table_id和partition_id：
select a.svr_ip,c.database_name,TB.table_name,a.table_Id,b.part_id,b.part_name,a.row_countfrom __ALL_VIRTUAL_TABLE TB join__all_virtual_partition_table a on a.table_id = TB.table_id join__all_virtual_database c on c.database_id = tb.database_id left join__all_virtual_partition_item b on a.partition_id = b.partition_id and a.table_Id = b.table_idwhere c.database_name = 'USER'and TB.table_name = 'TEST_T1'and a.role=1 order by 5,6;+-------------+---------------+------------+------------------+---------+--------------+-----------+| svr_ip | database_name | table_name | table_id | part_id | part_name | row_count |+-------------+---------------+------------+------------------+---------+--------------+-----------+| 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 0 | P_530_MAX | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 1 | P_531_MAX | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 2 | P_532_MAX | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 3 | P_533_MAX | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 4 | P_534_MAX | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 5 | P_535_MAX | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 6 | P_536_MAX | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 7 | P_537_202504 | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 8 | P_537_202505 | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 9 | P_537_202506 | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 10 | P_537_202507 | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 11 | P_537_202508 | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 12 | P_537_202509 | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 13 | P_537_202510 | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 14 | P_537_202511 | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 15 | P_537_202512 | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 16 | P_537_MAX | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 17 | P_538_MAX | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 18 | P_539_MAX | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 19 | P_543_MAX | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 20 | P_546_MAX | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 21 | P_631_MAX | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 22 | P_632_MAX | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 23 | P_633_MAX | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 24 | P_634_MAX | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 25 | P_635_MAX | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 26 | P_999_202504 | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 27 | P_999_202505 | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 28 | P_999_202506 | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 29 | P_999_202507 | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 30 | P_999_202508 | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 31 | P_999_202509 | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 32 | P_999_202510 | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 33 | P_999_202511 | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 34 | P_999_202512 | 0 || 192.92.80.1 | USER | TEST_T1 | 1107208209549772 | 35 | P_999_MAX | 0 |+-------------+---------------+------------+------------------+---------+--------------+-----------+
3. 往表中插入数据
insert into test_t1 values (92079952031788,537,'2025-10-15 16:02:32');insert into test_t1 values (92079952040088,537,'2025-09-15 16:02:32');obclient> select * from test_t1;+----------------+--------+---------------------+| OID | REGION | RECDATE |+----------------+--------+---------------------+| 92079952040088 | 537 | 2025-09-15 16:02:32 || 92079952031788 | 537 | 2025-10-15 16:02:32 |+----------------+--------+---------------------+
二、开始测试：
1、 测试一：update不带任何分区键
1.1 查看执行计划
desc update test_t1 set oid=92079952039648 where oid=92079952031788 \G*************************** 1. row ***************************Query Plan: ================================================================|ID|OPERATOR |NAME |EST. ROWS|COST |----------------------------------------------------------------|0 |PX COORDINATOR | |35640 |49426||1 | EXCHANGE OUT DISTR |:EX10000 |35640 |49426||2 | PX PARTITION ITERATOR| |35640 |49426||3 | UPDATE | |35640 |49426||4 | TABLE SCAN |TEST_T1(PK_TEST_T1)|35640 |13786|================================================================Outputs & filters:-------------------------------------0 - output(nil), filter(nil)1 - output(nil), filter(nil), dop=12 - output(nil), filter(nil)3 - output(nil), filter(nil), table_columns([{TEST_T1: ({TEST_T1: (TEST_T1.REGION, TEST_T1.RECDATE, TEST_T1.__pk_increment, TEST_T1.OID)})}]),update([TEST_T1.OID=?])4 - output([TEST_T1.REGION], [TEST_T1.RECDATE], [TEST_T1.__pk_increment], [TEST_T1.OID], [?]), filter(nil),access([TEST_T1.REGION], [TEST_T1.RECDATE], [TEST_T1.OID], [TEST_T1.__pk_increment]), partitions(p[0-35])
1.2 方式一：获取clog大小
--获取会话session_idselect userenv('sid') from dual;+----------------+| USERENV('SID') |+----------------+| 3221603868 |+----------------+--提交事务，查询事务参与者(所有扫描的分区都是参与者)commit;select * from __all_virtual_trans_stat where session_id=3221603868;+-----------+-------------+----------+------------+------------+-------------------+------------+-----------------------------------------------------------------------------------------+------------+-------------+------------+------------+-------------------------+-----------------------------------------------------+--------------+------------+-------------------+----------------------------+----------------------------+------------+------------+-------+-------------------+---------------------------+-----------------+----------------+------------------+----------------+------------------+-------------------------+-----------+------------------+------------------+| tenant_id | svr_ip | svr_port | inc_num | session_id | proxy_id | trans_type | trans_id | is_exiting | is_readonly | is_decided | trans_mode | active_memstore_version | partition | participants | autocommit | trans_consistency | ctx_create_time | expired_time | refer | sql_no | state | part_trans_action | lock_for_read_retry_count | ctx_addr | prev_trans_arr | prev_trans_count | next_trans_arr | next_trans_count | prev_trans_commit_count | ctx_id | pending_log_size | flushed_log_size |+-----------+-------------+----------+------------+------------+-------------------+------------+-----------------------------------------------------------------------------------------+------------+-------------+------------+------------+-------------------------+-----------------------------------------------------+--------------+------------+-------------------+----------------------------+----------------------------+------------+------------+-------+-------------------+---------------------------+-----------------+----------------+------------------+----------------+------------------+-------------------------+-----------+------------------+------------------+| 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:8, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120266832 | NULL | 0 | NULL | 0 | 0 | 433061945 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:25, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120797648 | NULL | 0 | NULL | 0 | 0 | 433061965 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:26, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120404112 | NULL | 0 | NULL | 0 | 0 | 433061977 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:6, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120440720 | NULL | 0 | NULL | 0 | 0 | 433061968 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:3, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120431568 | NULL | 0 | NULL | 0 | 0 | 433061979 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:22, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120889168 | NULL | 0 | NULL | 0 | 0 | 433061962 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:2, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120248528 | NULL | 0 | NULL | 0 | 0 | 433061898 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:34, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120861712 | NULL | 0 | NULL | 0 | 0 | 433061949 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:5, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120449872 | NULL | 0 | NULL | 0 | 0 | 433061931 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:10, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120285136 | NULL | 0 | NULL | 0 | 0 | 433061970 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:9, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120687824 | NULL | 0 | NULL | 0 | 0 | 433061919 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:30, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120504784 | NULL | 0 | NULL | 0 | 0 | 433061961 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:28, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700121182032 | NULL | 0 | NULL | 0 | 0 | 433061981 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:0, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120413264 | NULL | 0 | NULL | 0 | 0 | 433061932 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:16, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120330896 | NULL | 0 | NULL | 0 | 0 | 433061934 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:23, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120349200 | NULL | 0 | NULL | 0 | 0 | 433061946 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:7, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120614608 | NULL | 0 | NULL | 0 | 0 | 433061951 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:15, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120642064 | NULL | 0 | NULL | 0 | 0 | 433061971 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:21, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120477328 | NULL | 0 | NULL | 0 | 0 | 433061998 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:27, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120513936 | NULL | 0 | NULL | 0 | 0 | 433061831 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:14, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120632912 | NULL | 0 | NULL | 0 | 0 | 433061978 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:31, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700121355920 | NULL | 0 | NULL | 0 | 0 | 433062008 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:18, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120275984 | NULL | 0 | NULL | 0 | 0 | 433061948 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:35, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120459024 | NULL | 0 | NULL | 0 | 0 | 433061960 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:29, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120907472 | NULL | 0 | NULL | 0 | 0 | 433061966 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:20, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120651216 | NULL | 0 | NULL | 0 | 0 | 433060679 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:4, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120257680 | NULL | 0 | NULL | 0 | 0 | 433061929 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:11, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120468176 | NULL | 0 | NULL | 0 | 0 | 433061933 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:1, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120422416 | NULL | 0 | NULL | 0 | 0 | 433061922 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:13, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120852560 | NULL | 0 | NULL | 0 | 0 | 433061974 | 215 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:24, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120303440 | NULL | 0 | NULL | 0 | 0 | 433061975 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:19, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120724432 | NULL | 0 | NULL | 0 | 0 | 433061969 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:12, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120660368 | NULL | 0 | NULL | 0 | 0 | 433061910 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:32, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120696976 | NULL | 0 | NULL | 0 | 0 | 433061976 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:17, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120559696 | NULL | 0 | NULL | 0 | 0 | 433061997 | 0 | 0 || 1010 | 192.92.80.1 | 2882 | 2540732988 | 3221603868 | "172.17.0.1:2883" | 2 | {hash:1428727307418497943, inc:2540732988, addr:"192.92.80.1:2882", t:1759739646633028} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:33, part_cnt:0} | NULL | 0 | 0 | 2025-10-06 16:34:06.635095 | 2025-10-06 18:34:06.631863 | 1073741825 | 4294967297 | 0 | 2 | 0 | 139700120376656 | NULL | 0 | NULL | 0 | 0 | 433061954 | 0 | 0 |+-----------+-------------+----------+------------+------------+-------------------+------------+-----------------------------------------------------------------------------------------+------------+-------------+------------+------------+-------------------------+-----------------------------------------------------+--------------+------------+-------------------+----------------------------+----------------------------+------------+------------+-------+-------------------+---------------------------+-----------------+----------------+------------------+----------------+------------------+-------------------------+-----------+------------------+------------------+--获取当前会话clog大小select `STATISTIC#`,(case `STATISTIC#` when 336 then 'clog flush task generate count' when 371 then 'clog trans log total size' when 372 then 'clog submit log total size' end) as statname,valuefrom gv$sesstatwhere `STATISTIC#` in (336,371,372) and sid=3221603868;--update前：+------------+--------------------------------+-------+| STATISTIC# | statname | value |+------------+--------------------------------+-------+| 336 | clog flush task generate count | 2 || 371 | clog trans log total size | 1197 || 372 | clog submit log total size | 935 |+------------+--------------------------------+-------+--update后：+------------+--------------------------------+-------+| STATISTIC# | statname | value |+------------+--------------------------------+-------+| 336 | clog flush task generate count | 38 || 371 | clog trans log total size | 30356 || 372 | clog submit log total size | 15357 |+------------+--------------------------------+-------+
1.3 方式二：解析clog
--查询TRANSACTION_HASHselect /*+ query_timeout(100000000) parallel(32)*/ TRANSACTION_HASH,QUERY_SQLfrom gv$sql_auditwhere query_sql like '%update test_t1 set oid=92079952039648 where oid=92079952031788%' and query_sql <> '' and query_sql not like '%gv$sql_audit%'and (usec_to_time(request_time)) > DATE_SUB(NOW(), INTERVAL 240 MINUTE) order by ELAPSED_TIME desc limit 30\G+----------------------+----------------------------------------------------------------+| TRANSACTION_HASH | QUERY_SQL |+----------------------+----------------------------------------------------------------+| 11330754558585932212 | update test_t1 set oid=92079952039648 where oid=92079952031788 |+----------------------+----------------------------------------------------------------+--解析clogcp /data/log1/obcrm1_bcv/clog/44752 /OBPHYBACKUP/Liuclob_admin clog_tool dump_filter 'table_id=1107208209549772' 44752 |grep 11330754558585932212 >table_clog1--计算该事务clog日志记录的长度cat table_clog1 |perl -ne 'print "$1\n" if /.*?len:(\d+)/|/^$/' |awk '{sum+=$1}END{print "sum= ",sum}'sum= 35809 字节
clog解析日志中记录的事务类型包括：
OB_LOG_TRANS_REDO_WITH_PREPARE+OB_LOG_TRANS_PREPARE=36个（分布式事务准备日志）
OB_LOG_TRANS_COMMIT：36个（分布式事务提交日志）
OB_LOG_TRANS_CLEAR： 36个（分布式事务释放日志）
小结：clog日志中记录的每种事务类型和执行计划中扫描的分区数量相等
2、测试二：只带1个分区键
2.1 查看执行计划
update test_t1 set oid=92079952039648 where oid=92079952031788 and region=537;*************************** 1. row ***************************Query Plan: ====================================================|ID|OPERATOR |NAME |EST. ROWS|COST|----------------------------------------------------|0 |PX COORDINATOR | |108 |4550||1 | EXCHANGE OUT DISTR |:EX10000|108 |4550||2 | PX PARTITION ITERATOR| |108 |4550||3 | UPDATE | |108 |4550||4 | TABLE SCAN |TEST_T1 |108 |4442|====================================================Outputs & filters:-------------------------------------0 - output(nil), filter(nil)1 - output(nil), filter(nil), dop=12 - output(nil), filter(nil)3 - output(nil), filter(nil), table_columns([{TEST_T1: ({TEST_T1: (TEST_T1.REGION, TEST_T1.RECDATE, TEST_T1.__pk_increment, TEST_T1.OID)})}]),update([TEST_T1.OID=?])4 - output([TEST_T1.REGION], [TEST_T1.RECDATE], [TEST_T1.__pk_increment], [TEST_T1.OID], [?]), filter([TEST_T1.OID = 92079952031788]),access([TEST_T1.REGION], [TEST_T1.RECDATE], [TEST_T1.OID], [TEST_T1.__pk_increment]), partitions(p[7-17])
2.2 方式一：获取clog大小
--获取会话session_idselect userenv('sid') from dual;+----------------+| USERENV('SID') |+----------------+| 3221654289 |+----------------+--查询事务参与者select * from __all_virtual_trans_stat where session_id=3221654289;--获取当前会话clog大小select `STATISTIC#`,(case `STATISTIC#` when 336 then 'clog flush task generate count' when 371 then 'clog trans log total size' when 372 then 'clog submit log total size' end) as statname,valuefrom gv$sesstatwhere `STATISTIC#` in (336,371,372) and sid=3221654289;+------------+--------------------------------+-------+| STATISTIC# | statname | value |+------------+--------------------------------+-------+| 336 | clog flush task generate count | 0 || 371 | clog trans log total size | 0 || 372 | clog submit log total size | 0 |+------------+--------------------------------+-------+--update后：+------------+--------------------------------+-------+| STATISTIC# | statname | value |+------------+--------------------------------+-------+| 336 | clog flush task generate count | 11 || 371 | clog trans log total size | 5045 || 372 | clog submit log total size | 3557 |+------------+--------------------------------+-------+
2.3 方式二：解析clog
--查询TRANSACTION_HASHselect /*+ query_timeout(100000000) parallel(32)*/ TRANSACTION_HASH,QUERY_SQLfrom gv$sql_auditwhere query_sql like '%update test_t1 set oid=92079952039648 where oid=92079952031788 and region=537%' and query_sql <> '' and query_sql not like '%gv$sql_audit%'and (usec_to_time(request_time)) > DATE_SUB(NOW(), INTERVAL 240 MINUTE) order by ELAPSED_TIME desc limit 30\G+----------------------+-------------------------------------------------------------------------------+| TRANSACTION_HASH | QUERY_SQL |+----------------------+-------------------------------------------------------------------------------+| 12069153525127770108 | update test_t1 set oid=92079952039648 where oid=92079952031788 and region=537 |+----------------------+-------------------------------------------------------------------------------+--解析clogcp /data/log1/obcrm1_bcv/clog/44753 /OBPHYBACKUP/Liuclob_admin clog_tool dump_filter 'table_id=1107208209549772' 44753 |grep 12069153525127770108 >table_clog2--计算该事务clog日志记录的长度cat table_clog2 |perl -ne 'print "$1\n" if /.*?len:(\d+)/|/^$/' |awk '{sum+=$1}END{print "sum= ",sum}'sum= 8492 字节
clog解析日志中记录的事务类型包括：
OB_LOG_TRANS_REDO_WITH_PREPARE+OB_LOG_TRANS_PREPARE=11个（分布式事务准备日志）
OB_LOG_TRANS_COMMIT：11个（分布式事务提交日志）
OB_LOG_TRANS_CLEAR： 11个（分布式事务释放日志）
小结：clog日志中记录的每种事务类型和执行计划中扫描的分区数量相等
3、 测试三：带上所有的分区键分区键
3.1 查看执行计划
update test_t1 set oid=92079952039648 where oid=92079952031788 and region=537 and RECDATE='2025-10-15 16:02:32';*************************** 1. row ***************************Query Plan: ===================================================|ID|OPERATOR |NAME |EST. ROWS|COST|---------------------------------------------------|0 |UPDATE | |1 |47 ||1 | TABLE SCAN|TEST_T1(PK_TEST_T1)|1 |46 |===================================================Outputs & filters:-------------------------------------0 - output(nil), filter(nil), table_columns([{TEST_T1: ({TEST_T1: (TEST_T1.REGION, TEST_T1.RECDATE, TEST_T1.__pk_increment, TEST_T1.OID)})}]),update([TEST_T1.OID=?])1 - output([TEST_T1.REGION], [TEST_T1.RECDATE], [TEST_T1.__pk_increment], [TEST_T1.OID], [?]), filter(nil),access([TEST_T1.REGION], [TEST_T1.RECDATE], [TEST_T1.OID], [TEST_T1.__pk_increment]), partitions(p13)
3.2 方式一：获取clog大小
--获取会话session_idselect userenv('sid') from dual;+----------------+| USERENV('SID') |+----------------+| 3221656572 |+----------------+--提交事务，查询事务参与者(只有一个分区参与)commit;select * from __all_virtual_trans_stat where session_id=3221656572;+-----------+-------------+----------+------------+------------+-------------------+------------+-----------------------------------------------------------------------------------------+------------+-------------+------------+------------+-------------------------+-----------------------------------------------------+-------------------------------------------------------+------------+-------------------+----------------------------+----------------------------+------------+------------+-------+-------------------+---------------------------+-----------------+----------------+------------------+----------------+------------------+-------------------------+-----------+------------------+------------------+| tenant_id | svr_ip | svr_port | inc_num | session_id | proxy_id | trans_type | trans_id | is_exiting | is_readonly | is_decided | trans_mode | active_memstore_version | partition | participants | autocommit | trans_consistency | ctx_create_time | expired_time | refer | sql_no | state | part_trans_action | lock_for_read_retry_count | ctx_addr | prev_trans_arr | prev_trans_count | next_trans_arr | next_trans_count | prev_trans_commit_count | ctx_id | pending_log_size | flushed_log_size |+-----------+-------------+----------+------------+------------+-------------------+------------+-----------------------------------------------------------------------------------------+------------+-------------+------------+------------+-------------------------+-----------------------------------------------------+-------------------------------------------------------+------------+-------------------+----------------------------+----------------------------+------------+------------+-------+-------------------+---------------------------+-----------------+----------------+------------------+----------------+------------------+-------------------------+-----------+------------------+------------------+| 1010 | 192.92.80.1 | 2882 | 2544437074 | 3221656572 | "172.17.0.1:2883" | 0 | {hash:3770687829342795757, inc:2544437074, addr:"192.92.80.1:2882", t:1759749549045642} | 0 | 0 | 0 | normal | 2-0-0 | {tid:1110506744436232, partition_id:13, part_cnt:0} | [{tid:1110506744436232, partition_id:13, part_cnt:0}] | 0 | 0 | 2025-10-06 19:19:09.045642 | 2025-10-06 21:19:09.043950 | 1073741826 | 4294967297 | 0 | 2 | 0 | 139700137225488 | NULL | 0 | NULL | 0 | 0 | 433568142 | 0 | 0 |+-----------+-------------+----------+------------+------------+-------------------+------------+-----------------------------------------------------------------------------------------+------------+-------------+------------+------------+-------------------------+-----------------------------------------------------+-------------------------------------------------------+------------+-------------------+----------------------------+----------------------------+------------+------------+-------+-------------------+---------------------------+-----------------+----------------+------------------+----------------+------------------+-------------------------+-----------+------------------+------------------+--获取当前会话clog大小select `STATISTIC#`,(case `STATISTIC#` when 336 then 'clog flush task generate count' when 371 then 'clog trans log total size' when 372 then 'clog submit log total size' end) as statname,valuefrom gv$sesstatwhere `STATISTIC#` in (336,371,372) and sid=3221656572;+------------+--------------------------------+-------+| STATISTIC# | statname | value |+------------+--------------------------------+-------+| 336 | clog flush task generate count | 0 || 371 | clog trans log total size | 0 || 372 | clog submit log total size | 0 |+------------+--------------------------------+-------+--update后：+------------+--------------------------------+-------+| STATISTIC# | statname | value |+------------+--------------------------------+-------+| 336 | clog flush task generate count | 0 || 371 | clog trans log total size | 0 || 372 | clog submit log total size | 0 |+------------+--------------------------------+-------+
3.3 方式二：解析clog
--查询TRANSACTION_HASHselect /*+ query_timeout(100000000) parallel(32)*/ TRANSACTION_HASH,QUERY_SQLfrom gv$sql_auditwhere query_sql like '%update test_t1 set oid=92079952039648 where oid=92079952031788 and region=537 and RECDATE=%' and query_sql <> '' and query_sql not like '%gv$sql_audit%'and (usec_to_time(request_time)) > DATE_SUB(NOW(), INTERVAL 240 MINUTE) order by ELAPSED_TIME desc limit 30\G+----------------------+----------------------------------------------------------------------------------------------------------------------+| TRANSACTION_HASH | QUERY_SQL |+----------------------+----------------------------------------------------------------------------------------------------------------------+| 9258629246165984116 | update test_t1 set oid=92079952039648 where oid=92079952031788 and region=537 and RECDATE='2025-10-15 16:02:32' |+----------------------+----------------------------------------------------------------------------------------------------------------------+--解析clogcp /data/log1/obcrm1_bcv/clog/44755 /OBPHYBACKUP/Liuclob_admin clog_tool dump_filter 'table_id=1107208209549772' 44755 |grep 9258629246165984116 >table_clog3--计算该事务clog日志记录的长度cat table_clog3 |perl -ne 'print "$1\n" if /.*?len:(\d+)/|/^$/' |awk '{sum+=$1}END{print "sum= ",sum}'sum= 549 字节
clog解析日志中记录的事务类型包括：
OB_LOG_SP_TRANS_COMMIT：1个（单分区事务提交日志）
小结：clog日志中记录的每种事务类型和执行计划中扫描的分区数量相等
三、总结
clog日志的长度的计算公式=扫描的分区数*（update日志量+PK数量*20字节）
分区数36*（update日志量+PK数量*20字节）=35809
分区数11*（update日志量+PK数量*20字节）=8492
分区数1 *（update日志量+PK数量*20字节）=549
对比扫描分区数和clog日志长度的关系，发现clog日志长度和扫描分区数成几何倍数增加，且分布式事务clog相比单分区事务又多出(3*扫描分区数)倍的数据量，我们在遇到update和delete操作时也可以通过观察执行计划中扫描分区数判断clog日志生成量。杜绝扫描全部分区，尽量使用ob的分区裁剪功能。