暂无图片
暂无图片
2
暂无图片
暂无图片
暂无图片

OceanBase 2.2集群实战第九篇——sql审计视图gv$sql_audit查看sql执行情况

原创 gelyon 2020-10-16
1880

本系列总的专栏:一步一步学习OceanBase系列


一、学习目的

本篇体验下在oceanbase集群中,如何通过sql审计视图gv$sql_audit来查看某个database(或schema用户)在某个时间段内的SQL执行细节。

二、SQL审计介绍

OceanBase在SQL性能诊断方面有个很有用的功能叫SQL审计视图(gv$sql_audit),可以方便开发运维排查在OceanBase运行过的任意一条SQL,不管这些SQL是成功还是失败,都有详细的运行信息记录。
如客户端和服务端ip端口、sql语句、执行时间、执行节点、执行计划id、会话id、执行时间、等待时间、总时间、排队时间、相关block读取信息、执行报错信息等。
这个视图的内容都保存在内存的一个FIFO的队列里,内存大小由参数sql_audit_memory_limit控制,默认值3G。还有另外一个参数sql_audit_queue_size控制记录数,默认值是1000万。
一个繁忙的数据库,SQL记录数或者这个大小很快就会超过这两个阈值。然后早期的SQL运行数据就没了。
如果不想用这个SQL审计功能,还可以关闭它。由参数enable_sql_audit控制,默认值是Ture。

以下参数,请根据实际情况修改。

show parameters like '%audit%'; alter system set enable_sql_audit=true; --开启或关闭sql审计,默认已开启true alter system set sql_audit_memory_limit=5G; --sql审计表最大可用内存,默认3G alter system set sql_audit_queue_size= 20000000; --sql审计表最大记录条数,默认1000万

三、SQL路由与执行

由于OceanBase是一个分布式集群数据库,有多个副本,多个zone,每个zone下有多个observer节点。
当客户端通过obproxy连接ob集群后,执行一个sql时,obproxy先会对sql语句进行简单的sql解析,从中获取数据库名和表名等信息,
然后根据用户的租户名、数据库名、表名以及分区ID等信息查询路由表,获取分区的主/从副本所在 OB Server节点。
然后OB Proxy 将SQL请求路由至该分区主副本所在的observer节点上,同时将副本的位置信息更新到自己的 Location Cache中,当再次访问时,会命中该 Cache 以加速访问。
当负载均衡被打破(如机器故障、集群扩容等),导致分区分布发生变化时,location cache 也会动态更新,应用无感知。

对于SQL的执行是不在obproxy端,而是下沉到数据库,在observer节点上执行,执行方式有本地执行、远程执行和分布式执行三种方式。


下面通过实操体验,如何通过sql审计视图gv$sql_audit来查看某个database(或schema用户)在某个时间段内的SQL执行细节。 这里有一个mysql类型的业务租户mysql_test_tent,租户下有个testdb数据库,该数据库下有两个表:一个非分区表test,一个hash分区表test_hash。 obclient> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(11) NOT NULL, `name` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL, `dt` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.0' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10 1 row in set (0.07 sec) obclient> show create table test_hash\G *************************** 1. row *************************** Table: test_hash Create Table: CREATE TABLE `test_hash` ( `id` int(11) NOT NULL, `name` varchar(20) NOT NULL, `age` int(11) DEFAULT NULL, `dt` datetime DEFAULT CURRENT_TIMESTAMP ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.0' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10 partition by hash(id) partitions 3 1 row in set (0.00 sec) obclient> 两个表的分区分布情况: obclient> SELECT t1.tenant_id,t1.tenant_name,t2.database_name,t3.table_id,t3.table_Name,t3.tablegroup_id,t3.part_num,t4.partition_Id, -> t4.zone,t4.svr_ip,t4.role, round(t4.data_size/1024/1024) data_size_mb -> from `gv$tenant` t1 -> join `gv$database` t2 on (t1.tenant_id = t2.tenant_id) -> join gv$table t3 on (t2.tenant_id = t3.tenant_id and t2.database_id = t3.database_id and t3.index_type = 0) -> left join `gv$partition` t4 on (t2.tenant_id = t4.tenant_id and ( t3.table_id = t4.table_id or t3.tablegroup_id = t4.table_id ) and t4.role in (1,2)) -> where t1.tenant_id =1001 -> order by t1.tenant_id,t3.tablegroup_id, t3.table_name,t4.partition_Id ,t4.role; +-----------+-----------------+---------------+------------------+------------+---------------+----------+--------------+-------+----------------+------+--------------+ | tenant_id | tenant_name | database_name | table_id | table_Name | tablegroup_id | part_num | partition_Id | zone | svr_ip | role | data_size_mb | +-----------+-----------------+---------------+------------------+------------+---------------+----------+--------------+-------+----------------+------+--------------+ | 1001 | mysql_test_tent | testdb | 1100611139453777 | test | -1 | 1 | 0 | zone3 | 192.168.26.200 | 1 | 0 | | 1001 | mysql_test_tent | testdb | 1100611139453777 | test | -1 | 1 | 0 | zone1 | 192.168.26.198 | 2 | 0 | | 1001 | mysql_test_tent | testdb | 1100611139453777 | test | -1 | 1 | 0 | zone2 | 192.168.26.199 | 2 | 0 | | 1001 | mysql_test_tent | testdb | 1100611139453778 | test_hash | -1 | 3 | 0 | zone1 | 192.168.26.198 | 1 | 0 | | 1001 | mysql_test_tent | testdb | 1100611139453778 | test_hash | -1 | 3 | 0 | zone2 | 192.168.26.199 | 2 | 0 | | 1001 | mysql_test_tent | testdb | 1100611139453778 | test_hash | -1 | 3 | 0 | zone3 | 192.168.26.200 | 2 | 0 | | 1001 | mysql_test_tent | testdb | 1100611139453778 | test_hash | -1 | 3 | 1 | zone2 | 192.168.26.199 | 1 | 0 | | 1001 | mysql_test_tent | testdb | 1100611139453778 | test_hash | -1 | 3 | 1 | zone1 | 192.168.26.198 | 2 | 0 | | 1001 | mysql_test_tent | testdb | 1100611139453778 | test_hash | -1 | 3 | 1 | zone3 | 192.168.26.200 | 2 | 0 | | 1001 | mysql_test_tent | testdb | 1100611139453778 | test_hash | -1 | 3 | 2 | zone3 | 192.168.26.200 | 1 | 0 | | 1001 | mysql_test_tent | testdb | 1100611139453778 | test_hash | -1 | 3 | 2 | zone1 | 192.168.26.198 | 2 | 0 | | 1001 | mysql_test_tent | testdb | 1100611139453778 | test_hash | -1 | 3 | 2 | zone2 | 192.168.26.199 | 2 | 0 | +-----------+-----------------+---------------+------------------+------------+---------------+----------+--------------+-------+----------------+------+--------------+ 12 rows in set (0.22 sec) obclient> 从以上可以看到: 非分区表test,只有一个p0分区,主副本在zone3下的200节点。 分区表test_hash,p0分区的主副本在zone1下的198节点,p1主副本在zone2下的199节点,p2主副本在zone3下的200节点。 --场景1:连接业务租户,模拟不同事务下的sql执行情况: $ obclient -h192.168.26.201 -P2883 -uaps2@mysql_test_tent#obdemo -paps2#12345 -c -A testdb --SQL1: obclient> select * from test where id=3; +----+--------+------+---------------------+ | id | name | age | dt | +----+--------+------+---------------------+ | 3 | 李四 | 32 | 1989-10-13 00:00:00 | +----+--------+------+---------------------+ 1 row in set (0.00 sec) --SQL1执行计划:Plan Type = LOCAL 说明是本地执行。由于test表是非分区表,只有一个分区p0,因此主副本在某个zone某个observer节点,sql路由直接找到主副本所在节点,然后把SQL路由到此主副本所在observer节点,因此为本地SQL执行。 obclient> explain extended select * from test where id=3\G *************************** 1. row *************************** Query Plan: ================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ---------------------------------- |0 |TABLE GET|test|1 |53 | ================================== Outputs & filters: ------------------------------------- 0 - output([test.id(0x2b1e2a7a8070)], [test.name(0x2b1e2a7a8340)], [test.age(0x2b1e2a7a8590)], [test.dt(0x2b1e2a7a87e0)]), filter(nil), access([test.id(0x2b1e2a7a8070)], [test.name(0x2b1e2a7a8340)], [test.age(0x2b1e2a7a8590)], [test.dt(0x2b1e2a7a87e0)]), partitions(p0), is_index_back=false, range_key([test.id(0x2b1e2a7a8070)]), range[3 ; 3], range_cond([test.id(0x2b1e2a7a8070) = 3(0x2b1e2a7a7ae0)]) Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "testdb.test"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- test:table_rows:3, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:local_storage, optimization_method=rule_based, heuristic_rule=unique_index_without_indexback level 0: *********** paths(@1100611139453777(ordering([test.id]), cost=52.137275)) Parameters ------------------------------------- 1 row in set (0.01 sec) obclient> --SQL2: obclient> select * from test_hash where id=1; +----+--------+------+---------------------+ | id | name | age | dt | +----+--------+------+---------------------+ | 1 | 张三 | 26 | 1998-06-17 00:00:00 | +----+--------+------+---------------------+ 1 row in set (0.01 sec) --SQL2执行计划:Plan Type = LOCAL 说明是本地执行。由于test_hash表是根据id进行hash分区表,id=1对应的p1分区主副本在某个zone某个observer节点,sql路由直接找到主副本所在节点,然后把SQL路由到此主副本所在observer节点,因此为本地SQL执行。 obclient> explain extended select * from test_hash where id=1\G *************************** 1. row *************************** Query Plan: ======================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ---------------------------------------- |0 |TABLE SCAN|test_hash|1 |37 | ======================================== Outputs & filters: ------------------------------------- 0 - output([test_hash.id(0x2ab48c5c7f50)], [test_hash.name(0x2ab48c5c8c50)], [test_hash.age(0x2ab48c5c8ea0)], [test_hash.dt(0x2ab48c5c90f0)]), filter([test_hash.id(0x2ab48c5c7f50) = 1(0x2ab48c5c8640)]), access([test_hash.id(0x2ab48c5c7f50)], [test_hash.name(0x2ab48c5c8c50)], [test_hash.age(0x2ab48c5c8ea0)], [test_hash.dt(0x2ab48c5c90f0)]), partitions(p1), is_index_back=false, filter_before_indexback[false], range_key([test_hash.__pk_increment(0x2ab48c5db540)], [test_hash.__pk_cluster_id(0x2ab48c5db790)], [test_hash.__pk_partition_id(0x2ab48c5db9e0)]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "testdb.test_hash"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- test_hash:table_rows:1, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[test_hash], estimation info[table_id:1100611139453778, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:0, version:1-1-9223372036854775807, logical_rc:1, physical_rc:1)]level 0: *********** paths(@1100611139453778(ordering([test_hash.__pk_increment], [test_hash.__pk_cluster_id], [test_hash.__pk_partition_id]), cost=36.205719)) Parameters ------------------------------------- 1 row in set (0.01 sec) obclient> --SQL3: obclient> select * from test_hash where id=1 or id=2; +----+--------+------+---------------------+ | id | name | age | dt | +----+--------+------+---------------------+ | 1 | 张三 | 26 | 1998-06-17 00:00:00 | | 2 | 李四 | 32 | 1989-10-13 00:00:00 | +----+--------+------+---------------------+ 2 rows in set (0.01 sec) obclient> --SQL3 执行计划:Plan Type = DISTRIBUTED 说明是分布式执行。由于test_hash表是根据id进行hash分区表,id=1和id=2对应的两个分区p1和p2主副本在不同zone下的不同observer节点,SQL涉及到跨zone访问,因此为分布式并行执行。 obclient> explain extended select * from test_hash where id=1 or id=2 \G *************************** 1. row *************************** Query Plan: ===================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------------------- |0 |PX COORDINATOR | |2 |38 | |1 | EXCHANGE OUT DISTR |:EX10000 |2 |37 | |2 | PX PARTITION ITERATOR| |2 |37 | |3 | TABLE SCAN |test_hash|2 |37 | ===================================================== Outputs & filters: ------------------------------------- 0 - output([test_hash.id(0x2ab48cbc82f0)], [test_hash.name(0x2ab48cbcab30)], [test_hash.age(0x2ab48cbcad80)], [test_hash.dt(0x2ab48cbcafd0)]), filter(nil) 1 - output([test_hash.id(0x2ab48cbc82f0)], [test_hash.name(0x2ab48cbcab30)], [test_hash.age(0x2ab48cbcad80)], [test_hash.dt(0x2ab48cbcafd0)]), filter(nil), dop=1 2 - output([test_hash.id(0x2ab48cbc82f0)], [test_hash.name(0x2ab48cbcab30)], [test_hash.age(0x2ab48cbcad80)], [test_hash.dt(0x2ab48cbcafd0)]), filter(nil) 3 - output([test_hash.id(0x2ab48cbc82f0)], [test_hash.name(0x2ab48cbcab30)], [test_hash.age(0x2ab48cbcad80)], [test_hash.dt(0x2ab48cbcafd0)]), filter([test_hash.id(0x2ab48cbc82f0) = 1(0x2ab48cbc8f30) OR test_hash.id(0x2ab48cbc82f0) = 2(0x2ab48cbc9900)(0x2ab48cbc8560)]), access([test_hash.id(0x2ab48cbc82f0)], [test_hash.name(0x2ab48cbcab30)], [test_hash.age(0x2ab48cbcad80)], [test_hash.dt(0x2ab48cbcafd0)]), partitions(p[1-2]), is_index_back=false, filter_before_indexback[false], range_key([test_hash.__pk_increment(0x2ab48cbde000)], [test_hash.__pk_cluster_id(0x2ab48cbde250)], [test_hash.__pk_partition_id(0x2ab48cbde4a0)]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "testdb.test_hash"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- test_hash:table_rows:2, physical_range_rows:2, logical_range_rows:2, index_back_rows:0, output_rows:2, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[test_hash], estimation info[table_id:1100611139453778, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:0, version:1-1-9223372036854775807, logical_rc:1, physical_rc:1)]level 0: *********** paths(@1100611139453778(ordering([test_hash.__pk_increment], [test_hash.__pk_cluster_id], [test_hash.__pk_partition_id]), cost=36.498148)) Parameters ------------------------------------- 1 row in set (0.00 sec) obclient> --通过sql审计视图gv$sql_audit,查看以上3个sql的执行详细信息: $ obclient -h192.168.26.201 -P2883 -uroot@mysql_test_tent#obdemo -padmin123 -c -A oceanbase obclient> SELECT /*+ read_consistency(weak) ob_querytimeout(100000000) */ s.transaction_hash,substr(usec_to_time(request_time),1,19) request_time_, s.svr_ip, s.client_Ip, s.sid,s.tenant_id, s.tenant_name, s.user_name, s.db_name, s.query _sql, s.affected_rows, s.return_rows, s.ret_code, s.event, s.elapsed_time, s.queue_time, s.execute_time, round(s.request_memory_used/1024/1024,2) req_mem_mb, plan_type, is_executor_rpc, is_inner_sql -> from gv$sql_audit s -> where tenant_id =1001 -> and user_name in ('aps2') -> and request_time >= time_to_usec('2020-10-16 16:00:00') -> and query_sql like 'select * from test%' -> order by request_time -> \G *************************** 1. row *************************** transaction_hash: 6514849369952938013 request_time_: 2020-10-16 16:03:42 svr_ip: 192.168.26.200 client_Ip: 192.168.26.201 sid: 3222801337 tenant_id: 1001 tenant_name: mysql_test_tent user_name: aps2 db_name: testdb query_sql: select * from test where id=3 affected_rows: 0 return_rows: 1 ret_code: 0 event: system internal wait elapsed_time: 417 queue_time: 13 execute_time: 317 req_mem_mb: 2.00 plan_type: 1 is_executor_rpc: 0 is_inner_sql: 0 *************************** 2. row *************************** transaction_hash: 9826127368565424050 request_time_: 2020-10-16 16:03:48 svr_ip: 192.168.26.199 client_Ip: 192.168.26.201 sid: 3222551921 tenant_id: 1001 tenant_name: mysql_test_tent user_name: aps2 db_name: testdb query_sql: select * from test_hash where id=1 affected_rows: 0 return_rows: 1 ret_code: 0 event: system internal wait elapsed_time: 532 queue_time: 40 execute_time: 254 req_mem_mb: 2.00 plan_type: 1 is_executor_rpc: 0 is_inner_sql: 0 *************************** 3. row *************************** transaction_hash: 8634281928412481605 request_time_: 2020-10-16 16:03:55 svr_ip: 192.168.26.199 client_Ip: 192.168.26.201 sid: 3222551921 tenant_id: 1001 tenant_name: mysql_test_tent user_name: aps2 db_name: testdb query_sql: select * from test_hash where id=1 or id=2 affected_rows: 0 return_rows: 2 ret_code: 0 event: sync rpc elapsed_time: 6552 queue_time: 42 execute_time: 3610 req_mem_mb: 2.00 plan_type: 3 is_executor_rpc: 0 is_inner_sql: 0 3 rows in set (0.05 sec) obclient> 从以上结果查询可以看到每个sql执行的详细信息:如客户端和服务端ip端口、sql语句、执行时间、执行节点、执行计划id、会话id、执行时间、等待时间、总时间、排队时间、相关block读取信息、执行报错信息等。 其中:plan_type: 1表示本地执行,2表示远程执行,3表示分布式执行。 3SQL对应的transaction_hash事务不同,即说明在不同的事务里执行的sql语句。 SQL1对应的服务端IP是200节点,本地执行,即SQL1语句被路由到200节点进行的本地执行。 SQL2对应的服务器IP是199节点,本地执行;即SQL2语句是被路由到199节点进行的本地执行。 SQL3对应的服务器IP是199节点,分布式执行,即SQL3语句是被路由到199节点进行的分布式并行执行,因为SQL3需要跨分区访问其它节点的分区数据。 --场景2:连接业务租户,模拟同一个事务下的sql执行情况:一个本地执行,一个远程执行,一个分布式执行。 obclient> begin; --开始事务 Query OK, 0 rows affected (0.00 sec) --SQL1: obclient> select * from test where id=3; +----+--------+------+---------------------+ | id | name | age | dt | +----+--------+------+---------------------+ | 3 | 李四 | 32 | 1989-10-13 00:00:00 | +----+--------+------+---------------------+ 1 row in set (0.00 sec) --SQL1执行计划:Plan Type = LOCAL 本地执行 obclient> explain extended select * from test where id=3\G *************************** 1. row *************************** Query Plan: ================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ---------------------------------- |0 |TABLE GET|test|1 |53 | ================================== Outputs & filters: ------------------------------------- 0 - output([test.id(0x2b1e2a3a8070)], [test.name(0x2b1e2a3a8340)], [test.age(0x2b1e2a3a8590)], [test.dt(0x2b1e2a3a87e0)]), filter(nil), access([test.id(0x2b1e2a3a8070)], [test.name(0x2b1e2a3a8340)], [test.age(0x2b1e2a3a8590)], [test.dt(0x2b1e2a3a87e0)]), partitions(p0), is_index_back=false, range_key([test.id(0x2b1e2a3a8070)]), range[3 ; 3], range_cond([test.id(0x2b1e2a3a8070) = 3(0x2b1e2a3a7ae0)]) Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "testdb.test"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- test:table_rows:3, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:local_storage, optimization_method=rule_based, heuristic_rule=unique_index_without_indexback level 0: *********** paths(@1100611139453777(ordering([test.id]), cost=52.137275)) Parameters ------------------------------------- 1 row in set (0.02 sec) --SQL2: obclient> select * from test_hash where id=1; +----+--------+------+---------------------+ | id | name | age | dt | +----+--------+------+---------------------+ | 1 | 张三 | 26 | 1998-06-17 00:00:00 | +----+--------+------+---------------------+ 1 row in set (0.00 sec) --SQL2执行计划:Plan Type = REMOTE 远程执行 obclient> explain extended select * from test_hash where id=1\G *************************** 1. row *************************** Query Plan: ================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------------- |0 |EXCHANGE IN REMOTE | |1 |37 | |1 | EXCHANGE OUT REMOTE| |1 |37 | |2 | TABLE SCAN |test_hash|1 |37 | ================================================== Outputs & filters: ------------------------------------- 0 - output([test_hash.id(0x2b1e2a3a7f50)], [test_hash.name(0x2b1e2a3a8c50)], [test_hash.age(0x2b1e2a3a8ea0)], [test_hash.dt(0x2b1e2a3a90f0)]), filter(nil) 1 - output([test_hash.id(0x2b1e2a3a7f50)], [test_hash.name(0x2b1e2a3a8c50)], [test_hash.age(0x2b1e2a3a8ea0)], [test_hash.dt(0x2b1e2a3a90f0)]), filter(nil) 2 - output([test_hash.id(0x2b1e2a3a7f50)], [test_hash.name(0x2b1e2a3a8c50)], [test_hash.age(0x2b1e2a3a8ea0)], [test_hash.dt(0x2b1e2a3a90f0)]), filter([test_hash.id(0x2b1e2a3a7f50) = 1(0x2b1e2a3a8640)]), access([test_hash.id(0x2b1e2a3a7f50)], [test_hash.name(0x2b1e2a3a8c50)], [test_hash.age(0x2b1e2a3a8ea0)], [test_hash.dt(0x2b1e2a3a90f0)]), partitions(p1), is_index_back=false, filter_before_indexback[false], range_key([test_hash.__pk_increment(0x2b1e2a3bb540)], [test_hash.__pk_cluster_id(0x2b1e2a3bb790)], [test_hash.__pk_partition_id(0x2b1e2a3bb9e0)]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "testdb.test_hash"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- REMOTE Optimization Info: ------------------------------------- test_hash:table_rows:1, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[test_hash], estimation info[table_id:1100611139453778, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:0, version:1-1-9223372036854775807, logical_rc:1, physical_rc:1)]level 0: *********** paths(@1100611139453778(ordering([test_hash.__pk_increment], [test_hash.__pk_cluster_id], [test_hash.__pk_partition_id]), cost=36.205719)) Parameters ------------------------------------- 1 row in set (0.00 sec) --SQL3: obclient> select * from test_hash where id=1 or id=2; +----+--------+------+---------------------+ | id | name | age | dt | +----+--------+------+---------------------+ | 1 | 张三 | 26 | 1998-06-17 00:00:00 | | 2 | 李四 | 32 | 1989-10-13 00:00:00 | +----+--------+------+---------------------+ 2 rows in set (0.01 sec) --SQL3执行计划:Plan Type = DISTRIBUTED 分布式并行执行 obclient> explain extended select * from test_hash where id=1 or id=2\G *************************** 1. row *************************** Query Plan: ===================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------------------- |0 |PX COORDINATOR | |2 |38 | |1 | EXCHANGE OUT DISTR |:EX10000 |2 |37 | |2 | PX PARTITION ITERATOR| |2 |37 | |3 | TABLE SCAN |test_hash|2 |37 | ===================================================== Outputs & filters: ------------------------------------- 0 - output([test_hash.id(0x2b1e291a82f0)], [test_hash.name(0x2b1e291aab30)], [test_hash.age(0x2b1e291aad80)], [test_hash.dt(0x2b1e291aafd0)]), filter(nil) 1 - output([test_hash.id(0x2b1e291a82f0)], [test_hash.name(0x2b1e291aab30)], [test_hash.age(0x2b1e291aad80)], [test_hash.dt(0x2b1e291aafd0)]), filter(nil), dop=1 2 - output([test_hash.id(0x2b1e291a82f0)], [test_hash.name(0x2b1e291aab30)], [test_hash.age(0x2b1e291aad80)], [test_hash.dt(0x2b1e291aafd0)]), filter(nil) 3 - output([test_hash.id(0x2b1e291a82f0)], [test_hash.name(0x2b1e291aab30)], [test_hash.age(0x2b1e291aad80)], [test_hash.dt(0x2b1e291aafd0)]), filter([test_hash.id(0x2b1e291a82f0) = 1(0x2b1e291a8f30) OR test_hash.id(0x2b1e291a82f0) = 2(0x2b1e291a9900)(0x2b1e291a8560)]), access([test_hash.id(0x2b1e291a82f0)], [test_hash.name(0x2b1e291aab30)], [test_hash.age(0x2b1e291aad80)], [test_hash.dt(0x2b1e291aafd0)]), partitions(p[1-2]), is_index_back=false, filter_before_indexback[false], range_key([test_hash.__pk_increment(0x2b1e291be000)], [test_hash.__pk_cluster_id(0x2b1e291be250)], [test_hash.__pk_partition_id(0x2b1e291be4a0)]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "testdb.test_hash"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- test_hash:table_rows:2, physical_range_rows:2, logical_range_rows:2, index_back_rows:0, output_rows:2, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[test_hash], estimation info[table_id:1100611139453778, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:0, version:1-1-9223372036854775807, logical_rc:1, physical_rc:1)]level 0: *********** paths(@1100611139453778(ordering([test_hash.__pk_increment], [test_hash.__pk_cluster_id], [test_hash.__pk_partition_id]), cost=36.498148)) Parameters ------------------------------------- 1 row in set (0.01 sec) obclient> obclient> commit; --事务结束 Query OK, 0 rows affected (0.00 sec) obclient> 通过sql审计视图gv$sql_audit,查看sql执行详细信息: $ obclient -h192.168.26.201 -P2883 -uroot@mysql_test_tent#obdemo -padmin123 -c -A oceanbase obclient> SELECT /*+ read_consistency(weak) ob_querytimeout(100000000) */ s.transaction_hash,substr(usec_to_time(request_time),1,19) request_time_, s.svr_ip, s.client_Ip, s.sid,s.tenant_id, s.tenant_name, s.user_name, s.db_name, s.query _sql, s.affected_rows, s.return_rows, s.ret_code, s.event, s.elapsed_time, s.queue_time, s.execute_time, round(s.request_memory_used/1024/1024,2) req_mem_mb, plan_type, is_executor_rpc, is_inner_sql -> from gv$sql_audit s -> where tenant_id =1001 -> and user_name in ('aps2') -> and request_time >= time_to_usec('2020-10-16 16:04:00') -> and query_sql like 'select * from test%' -> order by request_time -> \G *************************** 1. row *************************** transaction_hash: 391153182152797198 request_time_: 2020-10-16 16:07:48 svr_ip: 192.168.26.200 client_Ip: 192.168.26.201 sid: 3222801337 tenant_id: 1001 tenant_name: mysql_test_tent user_name: aps2 db_name: testdb query_sql: select * from test where id=3 affected_rows: 0 return_rows: 1 ret_code: 0 event: system internal wait elapsed_time: 263 queue_time: 23 execute_time: 167 req_mem_mb: 2.00 plan_type: 1 is_executor_rpc: 0 is_inner_sql: 0 *************************** 2. row *************************** transaction_hash: 391153182152797198 request_time_: 2020-10-16 16:07:57 svr_ip: 192.168.26.200 client_Ip: 192.168.26.201 sid: 3222801337 tenant_id: 1001 tenant_name: mysql_test_tent user_name: aps2 db_name: testdb query_sql: select * from test_hash where id=1 affected_rows: 0 return_rows: 1 ret_code: 0 event: sync rpc elapsed_time: 2559 queue_time: 154 execute_time: 1870 req_mem_mb: 2.00 plan_type: 2 is_executor_rpc: 0 is_inner_sql: 0 *************************** 3. row *************************** transaction_hash: 391153182152797198 request_time_: 2020-10-16 16:08:06 svr_ip: 192.168.26.200 client_Ip: 192.168.26.201 sid: 3222801337 tenant_id: 1001 tenant_name: mysql_test_tent user_name: aps2 db_name: testdb query_sql: select * from test_hash where id=1 or id=2 affected_rows: 0 return_rows: 2 ret_code: 0 event: sync rpc elapsed_time: 4367 queue_time: 56 execute_time: 4005 req_mem_mb: 2.00 plan_type: 3 is_executor_rpc: 0 is_inner_sql: 0 3 rows in set (0.03 sec) obclient> 从以上结果查询可以看到同一个事务下,不同sql执行详细信息,有本地执行、远程执行和分布式执行。其中:plan_type: 1表示本地执行,2表示远程执行,3表示分布式执行。 3SQL语句对应的transaction_hash相同,即说明是在同一个事务里执行的sql语句。 3SQL语句对应的服务端IP都是200节点,因为事务的第一条SQL1主副本所在的节点是200,因此此事务下的所有SQL语句的服务器IP都相同,即同一个事务中,3SQL都是被路由到同一个节点200. SQL1分区主副本正好是200节点,因此为本地执行。 SQL2分区主副本在199节点,因此SQL2需要远程访问SQL2主副本所在的199节点去获取数据,然后再由200节点返回给客户端,因此是远程执行。 SQL3两个分区主副本在199200两个节点,由于事务在200节点,因此SQL3还需要分布式并行访问另一个分区主副本所在的199节点去获取数据,然后再由200节点统一汇总返回给客户端,因此是分布式并行执行。 到此,OceanBase2.2集群sql审计视图gv$sql_audit查看sql执行体验到此结束。 一步一步学习OceanBase系列
最后修改时间:2020-10-19 09:32:01
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
1人已赞赏
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

文章被以下合辑收录

评论

TigerGraph
关注
暂无图片
获得了71次点赞
暂无图片
内容获得43次评论
暂无图片
获得了21次收藏
目录
  • 一、学习目的
  • 二、SQL审计介绍
  • 三、SQL路由与执行