使用python進行Oracle資料庫效能趨勢分析

sb5533發表於2018-06-14
PythonOracle資料庫

一、   概述


隨著資訊系統業務需求快速增長，業務系統關聯日益複雜，資訊系統效能問題逐漸突顯，一旦出現資訊系統效能問題及不可用問題，將嚴重影響資訊系統的穩定執行及使用者體驗。

     結合運維實踐，資料庫效能問題是造成資訊系統效能下降和非停的重要原因之一，如何進行常態化的資料庫效能趨勢分析，及時發現資料庫效能衰減“病灶”,常態化提升資訊系統效能，避免救火式效能最佳化，成為衡量資訊系統管理部門運營能力的重要指標之一。

二、研究目標


使用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平均執行時間(秒)

 

五、效果展示


(1)、系統效能趨勢：


(2)、TOPSQL效能趨勢分析


(3)、TOPSQL日效能趨勢分析


(4)、TOPSQL月效能趨勢分析


 

六、核心程式碼


核心程式碼分為資料採集層、資料轉換層、web展示層。

(1)、資料採集層：


點選(此處)摺疊或開啟

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

  3.     s_top10 = ''

  5.     #s_snap_id = 0

  7.     print oracle_link_target

  9.    

  11.     if c_type == 'sql_topsql':

  13.          sql_topsql="

  15.          select round(Elapsed_Time, 2) Elapsed_Time,

  17.        round(cpu_time, 2) cpu_time,

  19.        Executions,

  21.        round(elap_per_exec, 2) elap_per_exec,

  23.        round(total_db_time, 2) total_db_time,

  25.        sql_id,

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

  29.        sql_text

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

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

  35.                sqt.exec Executions,

  37.                decode(sqt.exec,

  39.                       0,

  41.                       to_number(null),

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

  45.                (100 *

  47.                (sqt.elap /

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

  51.                     FROM DBA_HIST_SYS_TIME_MODEL e, DBA_HIST_SYS_TIME_MODEL b

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

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

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

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

  61.                      AND B.INSTANCE_NUMBER =

  63.                          (select instance_number from v$instance)

  65.                      AND E.INSTANCE_NUMBER =

  67.                          (select instance_number from v$instance)

  69.                      and e.STAT_NAME = 'DB time'

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

  73.                sqt.sql_id,

  75.                to_char(decode(sqt.module,

  77.                               null,

  79.                               null,

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

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

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

  87.           from (select sql_id,

  89.                        max(module) module,

  91.                        sum(elapsed_time_delta) elap,

  93.                        sum(cpu_time_delta) cput,

  95.                        sum(executions_delta) exec

  97.                   from dba_hist_sqlstat

  99.                dba_hist_sqltext st

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

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

  105.  where rownum < 100

  107.          "

  109.     elif c_type == 'top10':

  111.          #a list of top10: m_top10

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

  115.         

  117.          #after get top10

  119.         

  121.          #end get top10

  123.          for h_sql_id in m_top10:

  125.              l_sql_id = h_sql_id[0]

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

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

  131.          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+')'

  133.     else:

  135.          cmd=sql_tablespace

  137.    

  139.     #print s_top10

  141.     #print log_cmd_i

  143.  

  145.     cmd =""

  147.     if c_type == 'sql_topsql':

  149.         cmd=sql_topsql

  151.     elif c_type == 'top10':

  153.         cmd=sql_hsql_top10

  155.     else:

  157.         cmd=sql_tablespace

  159.     #print len(m_top10)

  161.    

  163.     print 'before get topsql exe sql: '

  165.     print cmd

  167.     print 'get db shell: '

  169.     conn = cx_Oracle.connect(oracle_link_target)

  171.     cursor = conn.cursor()

  173.     cur = cursor.execute(cmd)

  175.     db_list = cur.fetchall()

  177.     #print 'before return db_list'

  179.     #print db_list

  181.     return db_list

  183.       

  185.     cursor.close()

  187. conn.close()


 

(2)、資料轉換層


點選(此處)摺疊或開啟

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

  3.                ip,

  5.                db_name,

  7.                sql_id,

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

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

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

  15.                       1,

  17.                       'up',

  19.                       -1,

  21.                       'down',

  23.                       'equ') sql_status,

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

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

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

  31.                      2) sql_cont,

  33.                executions,

  35.                total_db_time,

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

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

  41.                ch_date

  43.           from (select rownum rn,

  45.                        d.ip,

  47.                        d.db_name,

  49.                        d.sql_id,

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

  53.                        e.elap_avg_exec,

  55.                        d.executions,

  57.                        d.sql_module,

  59.                        d.sql_text,

  61.                        d.ch_date,

  63.                        d.total_db_time

  65.                   from hsql.h_topsql d,

  67.                        (select b.ip,

  69.                                b.sql_id,

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

  73.                                      2) elap_avg_exec

  75.                           from hsql.h_topsql_bak b

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

  79.                  where d.sql_id = e.sql_id

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


 

(3)、web展示層


點選(此處)摺疊或開啟

  1. def topsql_line_servlet(request):

  3.          cursor = conn.cursor()

  5.          query = "select ip,

  7.        (select service_name

  9.           from hsql.h_instance h

  11.          where h.ip = b.ip

  13.            and rownum = 1) service_name,

  15.        sql_id,

  17.        executions,

  19.        elap_per_exec,

  21.        to_char(ch_date, 'hh24:mi') sj,

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

  25.   from hsql.h_topsql b

  27.  where ch_date > trunc(sysdate)

  29.  order by sj"

  31.         

  33.          print query

  35.          cursor.execute(query)

  37.          resultset = cursor.fetchall()

  39.          cursor.close()

  41.          conn.close()


 

七、總結


       透過Oracle效能趨勢分析工具的應用可以進行細粒度的資料庫效能管理，及時發現潛在的資訊系統效能衰減隱患，透過持續性、常態化的資訊系統效能最佳化，最佳化資訊系統提升，提升使用者體驗。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31442014/viewspace-2151671/，如需轉載，請註明出處，否則將追究法律責任。

相關文章