OceanBase获取执行效率下降SQL

　　移动现场有一个原厂写的通过gv$plan_cache_plan_stat视图来查询执行计划突变的SQL，通过比较gv$plan_cache_plan_stat视图记录的不同执行计划来判断是否执行计划发生突变而导致的执行计划变差，但是在gv$plan_cache_plan_stat视图中只有一个执行计划的时候，且这个执行计划的效率低下，无法对执行计划进行flush刷新，为了解决这个问题，可以通过ocp中的ocp_monitor数据库中的相关视图来判断进行的TOPSQL较昨天是否出现性能回退，如果出现可以记录并flush，这里仅贴出如何找到执行计划较昨天性能出现回退的脚本

## Copyright (c) 2013-2024
## Author: [email protected]     
## Version:      V1.0
## History:
## date:   20240619 
## 针对获取执行计划相较昨天变差SQL，并记录
##################################################################################################
#!/bin/bash
set +x
#DIR_HOME=$(cd "$(dirname "$0")"; pwd)

#LogDaily=$DIR_HOME/worse_sql_`hostname`.log

while [ 1 ]
do
  DATE=`date +%Y%m%d`
  LogDaily=/home/shsnc/monitor/log/wrose_sql_`hostname`_$DATE.log
  TIME1=`date +"%Y-%m-%d %H:%M:%S"`
  TIME2=`date -d "-1800 second" +"%Y-%m-%d %H:%M:%S"`
  TIME3=`date -d "-90000 second" +"%Y-%m-%d %H:%M:%S"`
  TIME4=`date -d "-82800 second" +"%Y-%m-%d %H:%M:%S"`
  echo "$TIME1 $TIME2 $TIME3 $TIME4"
  echo "#### $TIME1  数据库最近30分钟SQL执行计划相较昨天变差SQL信息####">> $LogDaily
mysql -hxxxx -P3306 -uroot@ocp_monitor#obcluster -pH@I8%Yl+FA -Docp_monitor -A -c -t<<!|tee -a $LogDaily
  select * from (select /*+parallel(32)*/
         a.sql_id,sum(a.executions) execs,
         round(sum(a.elapsed_time / 1000), 2) "total",(select avg_exe_usec from (select sql_id, plan_id, avg(avg_exe_usec / 1000) avg_exe_usec
          from ob_hist_plan_cache_plan_stat_0
         where collect_time > time_to_usec('$TIME3')
           and collect_time < time_to_usec('$TIME4')
           and ob_tenant_id = xxx
           and ob_cluster_id = 1632654686 and sql_id=a.sql_id
         group by 1, 2
         order by 3 desc limit 1)) last_avg_elapsed,
         round(sum(a.elapsed_time) / sum(a.executions) / 1000, 2) today_avg_elapsed
          from ob_hist_sql_audit_stat_0 a
         where a.ob_cluster_id = 1632654686
           and a.begin_interval_time > time_to_usec('$TIME2')
           and a.begin_interval_time < time_to_usec('$TIME1')
           and a.ob_tenant_id = xxx
           and a.cluster_name = 'oxxx'
           and ob_server_id in (3, 4) and retry_cnt=0
         group by a.sql_id
         order by 3 desc limit 100) where   today_avg_elapsed-last_avg_elapsed>500 and last_avg_elapsed<1000; 
!
  echo
  sleep 120
done