优化数据库性能监控：SQL响应时间与CPU使用指标

--查看数据库响应状况
select *
  from SYS.V_$SYSMETRIC
 where METRIC_NAME IN ( 'Database CPU Time Ratio',
 'Database Wait Time Ratio')
   AND INTSIZE_CSEC = (select max(INTSIZE_CSEC) from
 SYS.V_$SYSMETRIC)

--查看数据库的总性能
select *
  from sys.v_$sysmetric_history
 where metric_name = 'Database CPU Time Ratio'
 order by 1
 
--获得数据库整体性能效率的最大、最小和平均值
select CASE METRIC_NAME
         WHEN 'SQL Service Response Time' then
          'SQL Service Response Time (secs)'
         WHEN 'Response Time Per Txn' then
          'Response Time Per Txn (secs)'
         ELSE
          METRIC_NAME
       END METRIC_NAME,
       CASE METRIC_NAME
         WHEN 'SQL Service Response Time' then
          ROUND((MINVAL / 100), 2)
         WHEN 'Response Time Per Txn' then
          ROUND((MINVAL / 100), 2)
         ELSE
          MINVAL

       END MININUM,
       CASE METRIC_NAME
         WHEN 'SQL Service Response Time' then
          ROUND((MAXVAL / 100), 2)
         WHEN 'Response Time Per Txn' then
          ROUND((MAXVAL / 100), 2)
         ELSE
          MAXVAL
       END MAXIMUM,
       CASE METRIC_NAME
         WHEN 'SQL Service Response Time' then
          ROUND((AVERAGE / 100), 2)
         WHEN 'Response Time Per Txn' then
          ROUND((AVERAGE / 100), 2)
         ELSE
          AVERAGE
       END AVERAGE
  from SYS.V_$SYSMETRIC_SUMMARY
 where METRIC_NAME in
       ('CPU Usage Per Sec', 'CPU Usage Per Txn', 'Database CPU Time Ratio',
        'Database Wait Time Ratio', 'Executions Per Sec',
        'Executions Per Txn', 'Response Time Per Txn',
        'SQL Service Response Time', 'User Transaction Per Sec')
 ORDER BY 1

--查看究竟是什么类型的用户活动影响数据库的响应速度,找到相应的主要花费时间处理的部分
select case db_stat_name
         when 'parse time elapsed' then
          'soft parse time'
         else