温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

使用python进行Oracle数据库性能趋势分析

发布时间:2020-08-09 22:39:22 来源:ITPUB博客 阅读:221 作者:dbasdk 栏目:关系型数据库

一、   概述



随着信息系统业务需求快速增长,业务系统关联日益复杂,信息系统性能问题逐渐突显,一旦出现信息系统性能问题及不可用问题,将严重影响信息系统的稳定运行及用户体验。

     结合运维实践,数据库性能问题是造成信息系统性能下降和非停的重要原因之一,如何进行常态化的数据库性能趋势分析,及时发现数据库性能衰减“病灶”,常态化提升信息系统性能,避免救火式性能优化,成为衡量信息系统管理部门运营能力的重要指标之一。

二、研究目标



使用python语言进行Oracle数据库性能趋势分析。

三、工具介绍



开发语言:python 2.7

数据库:Oracle 11.2.0.4

Web框架:Django

图形展示工具:echart

四、算法介绍



       核心算法由运行可靠率、资源竞争率、进程等待率和SQL稳定率四部分组成,如下图所示,本文主要以SQL稳定率为例:

Trend =100-100*sum(( c_time-h_time) /h_time)

说明:

Trend: 表示信息系统性能趋势(%)

c_time: 前一小时SQL平均执行时间()

h_time: 3个月内SQL平均执行时间()

 使用python进行Oracle数据库性能趋势分析

五、效果展示



(1)、系统性能趋势:

使用python进行Oracle数据库性能趋势分析

(2)TOPSQL性能趋势分析

使用python进行Oracle数据库性能趋势分析

(3)TOPSQL日性能趋势分析

使用python进行Oracle数据库性能趋势分析

(4)TOPSQL月性能趋势分析

使用python进行Oracle数据库性能趋势分析

 

六、核心代码



核心代码分为数据采集层、数据转换层、web展示层。

(1)、数据采集层:


点击(此处)折叠或打开

  1. def get_topsql_info(username,password,ip,port,dbname,c_type,param=0,b_param=0):

  2.     s_top10 = ''

  3.     #s_snap_id = 0

  4.     print oracle_link_target

  5.    

  6.     if c_type == 'sql_topsql':

  7.          sql_topsql="

  8.          select round(Elapsed_Time, 2) Elapsed_Time,

  9.        round(cpu_time, 2) cpu_time,

  10.        Executions,

  11.        round(elap_per_exec, 2) elap_per_exec,

  12.        round(total_db_time, 2) total_db_time,

  13.        sql_id,

  14.        substr(nvl(sql_module, ' ** SQL module Not Available ** '), 1, 30) sql_module,

  15.        sql_text

  16.   from (select nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time,

  17.                nvl((sqt.cput / 1000000), to_number(null)) CPU_Time,

  18.                sqt.exec Executions,

  19.                decode(sqt.exec,

  20.                       0,

  21.                       to_number(null),

  22.                       (sqt.elap / sqt.exec / 1000000)) Elap_per_Exec,

  23.                (100 *

  24.                (sqt.elap /

  25.                (SELECT sum(e.VALUE) - sum(b.value)

  26.                     FROM DBA_HIST_SYS_TIME_MODEL e, DBA_HIST_SYS_TIME_MODEL b

  27.                    WHERE B.SNAP_ID = "+str(b_param)+"

  28.                      AND E.SNAP_ID = "+str(param)+"

  29.                      AND B.DBID = (select dbid from v$database)

  30.                      AND E.DBID = (select dbid from v$database)

  31.                      AND B.INSTANCE_NUMBER =

  32.                          (select instance_number from v$instance)

  33.                      AND E.INSTANCE_NUMBER =

  34.                          (select instance_number from v$instance)

  35.                      and e.STAT_NAME = 'DB time'

  36.                      and b.stat_name = 'DB time'))) Total_DB_Time,

  37.                sqt.sql_id,

  38.                to_char(decode(sqt.module,

  39.                               null,

  40.                               null,

  41.                               'Module: ' || sqt.module)) SQL_Module,

  42.                nvl(to_char(substr(st.sql_text, 1, 30)),

  43.                    ' ** SQL Text Not Available ** ') SQL_Text

  44.           from (select sql_id,

  45.                        max(module) module,

  46.                        sum(elapsed_time_delta) elap,

  47.                        sum(cpu_time_delta) cput,

  48.                        sum(executions_delta) exec

  49.                   from dba_hist_sqlstat

  50.                dba_hist_sqltext st

  51.          where st.sql_id(+) = sqt.sql_id

  52.          order by nvl(sqt.elap, -1) desc, sqt.sql_id)

  53.  where rownum < 100

  54.          "

  55.     elif c_type == 'top10':

  56.          #a list of top10: m_top10

  57.          m_top10=get_hsql_info(t,'top10')

  58.         

  59.          #after get top10

  60.         

  61.          #end get top10

  62.          for h_sql_id in m_top10:

  63.              l_sql_id = h_sql_id[0]

  64.              s_top10 = s_top10+",'"+l_sql_id+"'"

  65.          s_top10 = s_top10.strip(',')

  66.          sql_hsql_top10="select sql_id,to_char(substr(sql_text,1,2000)) sql_text,length(sql_text) sql_length,command_type from dba_hist_sqltext t where t.sql_id in ("+s_top10+')'

  67.     else:

  68.          cmd=sql_tablespace

  69.    

  70.     #print s_top10

  71.     #print log_cmd_i

  72.  

  73.     cmd =""

  74.     if c_type == 'sql_topsql':

  75.         cmd=sql_topsql

  76.     elif c_type == 'top10':

  77.         cmd=sql_hsql_top10

  78.     else:

  79.         cmd=sql_tablespace

  80.     #print len(m_top10)

  81.    

  82.     print 'before get topsql exe sql: '

  83.     print cmd

  84.     print 'get db shell: '

  85.     conn = cx_Oracle.connect(oracle_link_target)

  86.     cursor = conn.cursor()

  87.     cur = cursor.execute(cmd)

  88.     db_list = cur.fetchall()

  89.     #print 'before return db_list'

  90.     #print db_list

  91.     return db_list

  92.       

  93.     cursor.close()

  94. conn.close()


 

(2)、数据转换层


点击(此处)折叠或打开

  1. select row_number() over(partition by ip order by to_number(total_db_time) desc) rn,

  2.                ip,

  3.                db_name,

  4.                sql_id,

  5.                decode(elap_per_exec, '0', 0.01, elap_per_exec) elap_per_exec,

  6.                decode(elap_avg_exec, '0', 0.01, elap_avg_exec) elap_avg_exec,

  7.                decode(sign(decode(elap_avg_exec, '0', 0.01, elap_avg_exec) - decode(elap_per_exec, '0', 0.01, elap_per_exec)),

  8.                       1,

  9.                       'up',

  10.                       -1,

  11.                       'down',

  12.                       'equ') sql_status,

  13.                round((decode(elap_avg_exec, '0', 0.01, elap_avg_exec) -

  14.                      decode(elap_per_exec, '0', 0.01, elap_per_exec)) /

  15.                      decode(elap_avg_exec, '0', 0.01, elap_avg_exec),

  16.                      2) sql_cont,

  17.                executions,

  18.                total_db_time,

  19.                substr(sql_module, 1, 12) sql_module,

  20.                substr(sql_text, 1, 12) sql_text,

  21.                ch_date

  22.           from (select rownum rn,

  23.                        d.ip,

  24.                        d.db_name,

  25.                        d.sql_id,

  26.                        replace(d.elap_per_exec, 'None', 0) elap_per_exec,

  27.                        e.elap_avg_exec,

  28.                        d.executions,

  29.                        d.sql_module,

  30.                        d.sql_text,

  31.                        d.ch_date,

  32.                        d.total_db_time

  33.                   from hsql.h_topsql d,

  34.                        (select b.ip,

  35.                                b.sql_id,

  36.                                round(avg(replace(b.elap_per_exec, 'None', 0)),

  37.                                      2) elap_avg_exec

  38.                           from hsql.h_topsql_bak b

  39.                          group by b.ip, b.sql_id) e

  40.                  where d.sql_id = e.sql_id

  41.                    and d.ip = e.ip)));


 

(3)web展示层


点击(此处)折叠或打开

  1. def topsql_line_servlet(request):

  2.          cursor = conn.cursor()

  3.          query = "select ip,

  4.        (select service_name

  5.           from hsql.h_instance h

  6.          where h.ip = b.ip

  7.            and rownum = 1) service_name,

  8.        sql_id,

  9.        executions,

  10.        elap_per_exec,

  11.        to_char(ch_date, 'hh34:mi') sj,

  12.        to_char(ch_date, 'yyyy-mm-dd') rq

  13.   from hsql.h_topsql b

  14.  where ch_date > trunc(sysdate)

  15.  order by sj"

  16.         

  17.          print query

  18.          cursor.execute(query)

  19.          resultset = cursor.fetchall()

  20.          cursor.close()

  21.          conn.close()


 

七、总结



       通过Oracle性能趋势分析工具的应用可以进行细粒度的数据库性能管理,及时发现潜在的信息系统性能衰减隐患,通过持续性、常态化的信息系统性能优化,优化信息系统提升,提升用户体验。
向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI