本系列总的专栏:一步一步学习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表示分布式执行。
3个SQL对应的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表示分布式执行。
3个SQL语句对应的transaction_hash相同,即说明是在同一个事务里执行的sql语句。
3个SQL语句对应的服务端IP都是200节点,因为事务的第一条SQL1主副本所在的节点是200,因此此事务下的所有SQL语句的服务器IP都相同,即同一个事务中,3个SQL都是被路由到同一个节点200.
SQL1分区主副本正好是200节点,因此为本地执行。
SQL2分区主副本在199节点,因此SQL2需要远程访问SQL2主副本所在的199节点去获取数据,然后再由200节点返回给客户端,因此是远程执行。
SQL3两个分区主副本在199和200两个节点,由于事务在200节点,因此SQL3还需要分布式并行访问另一个分区主副本所在的199节点去获取数据,然后再由200节点统一汇总返回给客户端,因此是分布式并行执行。
到此,OceanBase2.2集群sql审计视图gv$sql_audit查看sql执行体验到此结束。
一步一步学习OceanBase系列