sql_audit中按disk_reads排名第一的是set autocommit

OceanBase
#1

版本4.2.1.4 ,如题，

ebc907d7114f4a1c8d79e173c25f71d7
ebc907d7114f4a1c8d79e173c25f71d71393×385 21.5 KB

#3

老师 是像表达什么呢。

#4

audit_sql里记录的set autocommit=1的 disk reads 最高 这不是正常的吧？ 啥原因呢？

#5

你好，你提的这个技术问题牵涉到OceanBase企业版范围内的功能细节。建议你通过以下方式寻求帮助：
1.如你所在的企业客户已签署OceanBase企业版销售合同，请你联系客户经理；
2.如你所在的企业客户尚未签署OceanBase企业版销售合同，你可通过OceanBase官网商务咨询页面留下你的联系方式，OceanBase企业版的业务顾问会在一个工作日内与你联系。
OceanBase官网商务咨询

#7

我只是好几层外的终端用户，接触不到企业版的ob技术支持，否则也不会跑这来发帖子

#8

找从sql_audit中找set autocommit 语句的trace_id，然后全集群去查一下这个trace_id还关联了哪些SQL，猜测是一个事务中包含了很多的语句，其中set autocommit 是其中一条，disk_reads统计的时候算到了一起

#9

感谢， 这里面还是只能找到autocommit

obclient [ICT]> select * from (select sql_id,sum(disk_reads) from gv$ob_sql_audit group by sql_id order by 2 desc) where rownum<11;
+----------------------------------+-----------------+
| SQL_ID                           | SUM(DISK_READS) |
+----------------------------------+-----------------+
| 1A5EEC33E9DCED3A280414657E1132FE |          127869 |
| CE24605B761C41943C6BA08A35ADE81F |           50160 |
| 96AC06ACB82065F17700A58DFD24944F |            9221 |
| 72EA0B3E21C2C7CA1A18DC88F0A7C248 |            8795 |
| DF06254DC268BCF8C03AE2F289C6D029 |            6758 |
| EC9DAC90EA7F31E6D9C99560F69C1DF9 |            6658 |
| 1005CF3C11CC2E1DCAA4ECCFB74472AC |            6332 |
| 5708AE022493A7AABCA7A410B07E2931 |            6083 |
| 9E9CCD2CA41AAFAAA7B9182CFC91388C |            3853 |
| B538EB9AAD60DDEB4953D77E33A3469F |            3762 |
+----------------------------------+-----------------+
10 rows in set (4.789 sec)

obclient [ICT]> select query_sql from gv$ob_sql_audit where sql_id='1A5EEC33E9DCED3A280414657E1132FE' and rownum=1;
+--------------------------------------------------------------------------------------------------------------------------+
| QUERY_SQL                                                                                                                |
+--------------------------------------------------------------------------------------------------------------------------+
| select  t.*, ROWID AS "__ODC_INTERNAL_ROWID__"  from  PM_PROJECT_COMPLEX_TEMP t where t.PROJECT_NUMBER='Z9L62300IW0060'; |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (3.721 sec)

obclient [ICT]>  select query_sql from gv$ob_sql_audit where sql_id='CE24605B761C41943C6BA08A35ADE81F' and rownum=1;
+--------------------+
| QUERY_SQL          |
+--------------------+
| set autocommit = 1 |
+--------------------+
1 row in set (0.023 sec)

obclient [ICT]> select max(trace_id) from gv$ob_sql_audit where sql_id='CE24605B761C41943C6BA08A35ADE81F';
+-----------------------------------+
| MAX(TRACE_ID)                     |
+-----------------------------------+
| YB420AAD2284-000613DD01B1AD4B-0-0 |
+-----------------------------------+
1 row in set (5.523 sec)

obclient [ICT]> select sql_id,query_sql from gv$ob_sql_audit where  TRACE_ID='YB420AAD2284-000613DD01B1AD4B-0-0';
+----------------------------------+--------------------+
| SQL_ID                           | QUERY_SQL          |
+----------------------------------+--------------------+
| CE24605B761C41943C6BA08A35ADE81F | set autocommit = 1 |
+----------------------------------+--------------------+
1 row in set (8.747 sec)

obclient [ICT]> select count(distinct trace_id) from  gv$ob_sql_audit where sql_id='CE24605B761C41943C6BA08A35ADE81F';
+-------------------------+
| COUNT(DISTINCTTRACE_ID) |
+-------------------------+
|                  618286 |
+-------------------------+
1 row in set (6.273 sec)

obclient [ICT]> select trace_id from gv$ob_sql_audit where sql_id='CE24605B761C41943C6BA08A35ADE81F' and rownum<3;
+-----------------------------------+
| TRACE_ID                          |
+-----------------------------------+
| YB420AAD2278-000613DD448FD5C0-0-0 |
| YB420AAD2278-000613DD48AE9891-0-0 |
+-----------------------------------+
2 rows in set (0.037 sec)

obclient [ICT]>  select sql_id,query_sql from gv$ob_sql_audit where  TRACE_ID='B420AAD2278-000613DD448FD5C0-0-0';
Empty set (8.759 sec)

obclient [ICT]>  select sql_id,query_sql from gv$ob_sql_audit where  TRACE_ID='YB420AAD2278-000613DD48AE9891-0-0';
+----------------------------------+--------------------+
| SQL_ID                           | QUERY_SQL          |
+----------------------------------+--------------------+
| CE24605B761C41943C6BA08A35ADE81F | set autocommit = 1 |
+----------------------------------+--------------------+
1 row in set (8.774 sec)
#10

多找几条看看

#11 
select trace_id,count(*) from gv$ob_sql_audit where  TRACE_ID in (select trace_id from gv$ob_sql_audit where sql_id='CE24605B761C41943C6BA08A35ADE81F')  group by trace_id having(count(*)>1) order by 2;
Empty set (13.380 sec)
``