查询某段时间内请求次数排在 TOP N 的 SQL

运行如下语句可以查询某段时间内请求次数排在 TOP N 的 SQL：

  1. obclient>SELECT/*+ PARALLEL(15)*/ SQL_ID, COUNT(*) AS QPS, AVG(t1.elapsed_time) RT 
  2.                 FROM oceanbase.gv$sql_audit t1 WHERE tenant_id = 1001 AND 
  3.                 IS_EXECUTOR_RPC = 0 AND request_time > (time_to_usec(now()) - 10000000)  AND 
  4.                 request_time < time_to_usec (now()) 
  5.                 GROUP BY t1.sql_id ORDER BY QPS DESC LIMIT 10;
  7. +----------------------------------+------+------------+
  8. | SQL_ID                           | QPS  | RT         |
  9. +----------------------------------+------+------------+
  10. | BF7AA13A28DF50BA5C33FF19F1DBD8A9 | 2523 |  4233.2085 |
  11. | CE7208ADDE365D0AB5E68EE24E5FD730 | 1268 |  5935.8683 |
  12. | E5C7494018989226E69AE7D08B3D0F15 | 1028 |  7275.7490 |
  13. | D0E8D8C937E44BC3BB9A5379AE1064C5 | 1000 | 12999.1640 |
  14. | 2D45D7BE4E459CFBEAE4803971F0C6F9 | 1000 |  8050.6360 |
  15. | C81CE9AA555BE59B088B379CC7AE5B40 | 1000 |  6865.4940 |
  16. | BDC4FE903B414203A04E41C7DDA6627D | 1000 | 12751.8960 |
  17. | B1B136047D7C3B6B9125F095363A9D23 |  885 | 13293.2237 |
  18. | 47993DD69888868E92A7CAB2FDE65380 |  880 |  7282.0557 |
  19. | 05C6279D767C7F212619BF4B659D3BAB |  844 | 11474.5438 |
  20. +----------------------------------+------+------------+

查询某段时间内平均 RT 排在 TOP N 的 SQL

运行如下语句可以查询某段时间内平均 RT 排在 TOP N 的 SQL：

  1. obclient>SELECT/*+ PARALLEL(15)*/ SQL_ID, COUNT(*) AS QPS, AVG(t1.elapsed_time) RT   
  2.                 FROM oceanbase.gv$sql_audit t1   
  3.                 WHERE tenant_id = 1001 AND IS_EXECUTOR_RPC = 0    
  4.                 AND request_time > (time_to_usec(now()) - 10000000)       
  5.                 AND request_time < time_to_usec(now())
  6.                 GROUP BY t1.sql_id ORDER BY RT DESC LIMIT 10;
  9. +----------------------------------+------+------------+
  10. | SQL_ID                           | QPS  | RT         |
  11. +----------------------------------+------+------------+
  12. | 0A3D3DCB3343BBBB10E4B4B9777B77FC |    1 | 53618.0000 |
  13. | A3831961C337545AF5BD1219BE29867A |    1 | 50764.0000 |
  14. | F3DC5EF627DA63AE52044FCE7732267C |    1 | 48497.0000 |
  15. | 39C63F143FDDACAEC090F480789DBCA5 |    1 | 47035.0000 |
  16. | A3BF306B02FF86E76C96C9CEFADBDB7E |    1 | 45553.0000 |
  17. | 7942E8D29BAFBF23EF3E3D29D55F428A |    1 | 45285.0000 |
  18. | 20989A74CC1703664BDE9D6EA7830C24 |    1 | 39143.0000 |
  19. | 80F40791E76C79D3DCD46FEEFFAB338E |    1 | 37654.0000 |
  20. | 07E2FE351E3DD82843E81930B84D3DDE |    1 | 37231.0000 |
  21. | 11B19DB5A1393590ABBE08005C155B2E |    1 | 37139.0000 |
  22. +----------------------------------+------+------------+

查询所有 SQL 中平均执行时间排在 TOP N 的 SQL

运行如下语句可以查询所有 SQL 中平均执行时间排在 TOP N 的 SQL：

  1. obclient>SELECT/*+ PARALLEL(15)*/avg_exe_usec, svr_ip, svr_port, sql_id, plan_id 
  2.                 FROM oceanbase.gv$plan_cache_plan_stat 
  3.                 WHERE tenant_id = 1001 
  4.                 ORDER BY avg_exe_usec DESC LIMIT 3\G;
  6. *************************** 1. row ***************************
  7. avg_exe_usec: 9795912
  8.       svr_ip: 10.183.76.140
  9.     svr_port: 2882
  10.       sql_id: C5D91E6C772D1B87C32BB3C9ED1435E1
  11.      plan_id: 4668689
  12. *************************** 2. row ***************************
  13. avg_exe_usec: 9435052
  14.       svr_ip: 10.103.229.107
  15.     svr_port: 2882
  16.       sql_id: 3B6EFEEC8332EB2A0822A3EA7B769500
  17.      plan_id: 4692858
  18. *************************** 3. row ***************************
  19. avg_exe_usec: 9335002
  20.       svr_ip: 11.180.113.7
  21.     svr_port: 2882
  22.       sql_id: 3B6EFEEC8332EB2A0822A3EA7B769500
  23.      plan_id: 4683085