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 desclimit 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 = 1001and 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 = 1001and 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_audit3where trace_id = 'YB420AB74FC6-00056349D323483A';

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

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

selectr_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
wheres.table_id= t.table_idand t.database_id= d.database_idand d.database_name= 'tpch_1g'  -- 数据库名and t.table_name= 'orders'      -- 表名and c.table_id= t.table_idand c.column_name= 'o_orderkey' -- 列名and s.column_id= c.column_idand s.column_id= c.column_idand 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)