oceanbase 性能监控04-性能分析sql

1 . 查询是否存在sql 等待

select sql_id, elapsed_time, queue_time, get_plan_time, execute_time, application_wait_time, concurrency_wait_time, user_io_wait_time, schedule_time, event, wait_class, wait_time_micro, total_wait_time_micro 
from v$sql_audit 
 where trace_id = 'YB420B84FE35-0005648A67211DC9'\G

 
2.找到所有SQL中平均执行时间排在TOP N的SQL

select/*+ parallel(15)*/ avg_exe_usec, svr_ip, svr_port, sql_id, plan_id 
 from oceanbase.gv$plan_cache_plan_stat 
 where tenant_id = 1001 
 order by avg_exe_usec desc
 limit 3\G;

 
3.如何查看集群SQL请求流量是否均衡？

select/*+ parallel(15)*/t2.zone, t1.svr_ip,  count(*) as QPS, avg(t1.elapsed_time), avg(t1.queue_time)  
 from oceanbase.gv$sql_audit t1, __all_server t2  
 where t1.svr_ip = t2.svr_ip and IS_EXECUTOR_RPC = 0    
       and request_time > (time_to_usec(now()) - 1000000)    
       and request_time < time_to_usec(now())
 group by t1.svr_ip  
 order by t2.zone;
 

4. 如何查看分布式计划rpc执行数是否均衡？

select/*+ parallel(15)*/t2.zone, t1.svr_ip,  count(*) as RPC_COUNT, avg(t1.elapsed_time), avg(t1.queue_time)  
 from oceanbase.gv$sql_audit t1, __all_server t2  
 where t1.svr_ip = t2.svr_ip and IS_EXECUTOR_RPC = 1    
       and tenant_id = 1001
       and request_time > (time_to_usec(now()) - 1000000)    
      and request_time < time_to_usec(now()) 
 group by t1.svr_ip  
 order by t2.zone;

 
 5.查询SQL流量分布情况及QPS

 select/*+ parallel(15)*/t2.zone, t1.svr_ip,  count(*) as RPC_COUNT, avg(t1.elapsed_time), avg(t1.queue_time)  
from oceanbase.gv$sql_audit t1, __all_server t2  
where t1.svr_ip = t2.svr_ip 
      and tenant_id = 1001
      and SQL_ID = 'BF7AA13A28DF50BA5C33FF19F1DBD8A9'
      and IS_EXECUTOR_RPC = 0    
      and request_time > (time_to_usec(now()) - 1000000)    
     and request_time < time_to_usec(now())
group by t1.svr_ip

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

select/*+ parallel(15)*/ SQL_ID,   count(*) as QPS, avg(t1.elapsed_time) RT   from oceanbase.gv$sql_audit t1   where   tenant_id = 1001       and IS_EXECUTOR_RPC = 0           and request_time > (time_to_usec(now()) - 10000000)           and request_time < time_to_usec(now()) group by t1.sql_id order by QPS desc limit 10;

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

select/*+ parallel(15)*/ SQL_ID,   count(*) as QPS, avg(t1.elapsed_time) RT   
 from oceanbase.gv$sql_audit t1   
 where   tenant_id = 1001       and IS_EXECUTOR_RPC = 0    
        and request_time > (time_to_usec(now()) - 10000000)       
         and request_time < time_to_usec(now())
 group by t1.sql_id 
 order by RT desc 
 limit 10;
 

8.查询某段时间内执行时间排TOP N的请求

select/*+ parallel(15)*/ sql_id, elapsed_time , trace_id    
 from oceanbase.gv$sql_audit     
 where   tenant_id = 1001 
         and IS_EXECUTOR_RPC = 0 
        and request_time > (time_to_usec(now()) - 10000000) 
        and request_time < time_to_usec(now()) 
 order by elapsed_time desc  
 limit 10;

 
9.判断系统或某个SQL的执行是否出现大量请求不合理的使用了远程执行

select count(*), plan_type    
from oceanbase.gv$sql_audit     
where tenant_id = 1001          
      and IS_EXECUTOR_RPC = 0          
      and request_time > (time_to_usec(now()) - 10000000)         
      and request_time < time_to_usec(now()) 
group by plan_type ;

10.找出某个租户中全表扫描的SQL

select query_sql 
from oceanbase.gv$sql_audit 
where table_scan = 1 and tenant_id = 1001 
group by sql_id;

11.
分布式计划如何分析查询问题
1）先确定是否为分布式计划, (g)v$plan_cache_plan_stat, (g)v$sql_audit中均对执行计划类型有记录；
2）分析该执行计划是否正确；
3）通过trace_id关联查询gv$sql_audit, 查看所有执行的子计划耗时情况，每个子计划的RPC执行均对应一条sql_audit记录，分析该sql_audit记录来定位问题， 如下举例：is_executor_rpc ＝ 1表示子计划执行在sql_audit中记录，主要记录执行相关信息。is_executor_rpc ＝ 0表示接受sql请求的线程在sql_audit中的记录， 该记录含有SQL执行过程的信息，包括SQL信息，获取执行计划信息等。

select/*+ parallel(15)*/ sql_id, is_executor_rpc, elapsed_time 
 2from oceanbase.gv$sql_audit
 3where trace_id = 'YB420AB74FC6-00056349D323483A';

12.统计信息查询
目前，sys租户可以通过前述的内部表来查询表和列的统计信息，下面的SQL给出了一个使用内部表查询表、列的统计信息的示例。使用该SQL可以通过传入数据库名、表名和列名这三个信息的组合获取到包括表的行数、列的NDV、列的null值数量以及列的最大最小值信息。

-- 内部表使用16进制格式存储最大值最小值信息，使用des_hex_str()函数可以将它转化为可读的格式。

select
  r_c as row_count,
  s.num_distinct as NDV, 
  s.num_null as num_null,
  des_hex_str(s.min_value) as min,
  des_hex_str(s.max_value) as max
from
  __all_column_statistic s,
  __all_database d,
  __all_table t,
  __all_column c,
  (select max(row_count)as r_c, table_id from __all_meta_table group by table_id) m
where
  s.table_id= t.table_id
  and t.database_id= d.database_id
  and d.database_name= 'tpch_1g'  -- 数据库名
  and t.table_name= 'orders'      -- 表名
  and c.table_id= t.table_id
  and c.column_name= 'o_orderkey' -- 列名
  and s.column_id= c.column_id
  and s.column_id= c.column_id
  and s.table_id= m.table_id;

-- 查询结果示例
+-----------+---------+----------+------+---------+
| row_count | NDV     | num_null | min  | max     |
+-----------+---------+----------+------+---------+
|   1500000 | 1479709 |        0 | 1    | 6000000 |
+-----------+---------+----------+------+---------+
1 row in set (0.14 sec)