Shulou(Shulou.com)06/01 Report--

I. Overview

With the rapid growth of the business demand of the information system, the relationship between the business system is becoming more and more complex, and the performance problems of the information system are becoming more and more prominent. once there are performance problems and unavailability problems of the information system, it will seriously affect the stable operation of the information system and user experience.

Combined with the practice of operation and maintenance, the problem of database performance is one of the important reasons for the decline and non-stop of information system performance. how to analyze the trend of database performance and find the "focus" of database performance decline in time. Normalizing to improve the performance of the information system and avoid fire-fighting performance optimization has become one of the important indicators to measure the operational capacity of the information system management department.

II. Research objectives

Use python language to analyze the performance trend of Oracle database

III. Introduction of tools

Development language: python 2.7

Database: Oracle 11.2.0.4

Web framework: Django

Graphic display tool: echart

4. Introduction of the algorithm

The core algorithm consists of four parts: operation reliability, resource competition rate, process waiting rate and SQL stability rate. As shown in the following figure, this paper mainly takes the SQL stability rate as an example:

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

Description:

Trend: indicates the trend of information system performance (%)

C_time: average SQL execution time in the previous hour (seconds)

H_time: average SQL execution time within 3 months (seconds)

Fifth, effect display

(1) system performance trend:

(2). TOPSQL performance trend analysis.

(3). Daily performance trend analysis of TopSQL.

(4). Monthly performance trend analysis of TopSQL.

VI. Core code

The core code is divided into data acquisition layer, data conversion layer and web presentation layer.

(1) data acquisition layer:

Click (here) to collapse or open

Def get_topsql_info (username,password,ip,port,dbname,c_type,param=0,b_param=0):

S_top10 =''

# s_snap_id = 0

Print oracle_link_target

If c_type = 'sql_topsql':

Sql_topsql= "

Select round (Elapsed_Time, 2) Elapsed_Time

Round (cpu_time, 2) cpu_time

Executions

Round (elap_per_exec, 2) elap_per_exec

Round (total_db_time, 2) total_db_time

Sql_id

Substr (nvl (sql_module,'* * SQL module Not Available * *), 1,30) sql_module

Sql_text

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

Nvl ((sqt.cput / 1000000), to_number (null)) CPU_Time

Sqt.exec Executions

Decode (sqt.exec

0

To_number (null)

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

(100 *

(sqt.elap /

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

FROM DBA_HIST_SYS_TIME_MODEL e, DBA_HIST_SYS_TIME_MODEL b

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

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

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

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

AND B.INSTANCE_NUMBER =

(select instance_number from v$instance)

AND E.INSTANCE_NUMBER =

(select instance_number from v$instance)

And e.STAT_NAME ='DB time'

And b.stat_name ='DB time') Total_DB_Time

Sqt.sql_id

To_char (decode (sqt.module)

Null

Null

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

Nvl (to_char (substr (st.sql_text, 1,30))

'* * SQL Text Not Available * *) SQL_Text

From (select sql_id

Max (module) module

Sum (elapsed_time_delta) elap

Sum (cpu_time_delta) cput

Sum (executions_delta) exec

From dba_hist_sqlstat

Dba_hist_sqltext st

Where st.sql_id (+) = sqt.sql_id

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

Where rownum

< 100 " elif c_type == 'top10': #a list of top10: m_top10 m_top10=get_hsql_info(t,'top10') #after get top10 #end get top10 for h_sql_id in m_top10: l_sql_id = h_sql_id[0] s_top10 = s_top10+",'"+l_sql_id+"'" s_top10 = s_top10.strip(',') 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+')' else: cmd=sql_tablespace #print s_top10 #print log_cmd_i cmd ="" if c_type == 'sql_topsql': cmd=sql_topsql elif c_type == 'top10': cmd=sql_hsql_top10 else: cmd=sql_tablespace #print len(m_top10) print 'before get topsql exe sql: ' print cmd print 'get db shell: ' conn = cx_Oracle.connect(oracle_link_target) cursor = conn.cursor() cur = cursor.execute(cmd) db_list = cur.fetchall() #print 'before return db_list' #print db_list return db_list cursor.close() conn.close() (2)、数据转换层 点击(此处)折叠或打开 select row_number() over(partition by ip order by to_number(total_db_time) desc) rn, ip, db_name, sql_id, decode(elap_per_exec, '0', 0.01, elap_per_exec) elap_per_exec, decode(elap_avg_exec, '0', 0.01, elap_avg_exec) elap_avg_exec, decode(sign(decode(elap_avg_exec, '0', 0.01, elap_avg_exec) - decode(elap_per_exec, '0', 0.01, elap_per_exec)), 1, 'up', -1, 'down', 'equ') sql_status, round((decode(elap_avg_exec, '0', 0.01, elap_avg_exec) - decode(elap_per_exec, '0', 0.01, elap_per_exec)) / decode(elap_avg_exec, '0', 0.01, elap_avg_exec), 2) sql_cont, executions, total_db_time, substr(sql_module, 1, 12) sql_module, substr(sql_text, 1, 12) sql_text, ch_date from (select rownum rn, d.ip, d.db_name, d.sql_id, replace(d.elap_per_exec, 'None', 0) elap_per_exec, e.elap_avg_exec, d.executions, d.sql_module, d.sql_text, d.ch_date, d.total_db_time from hsql.h_topsql d, (select b.ip, b.sql_id, round(avg(replace(b.elap_per_exec, 'None', 0)), 2) elap_avg_exec from hsql.h_topsql_bak b group by b.ip, b.sql_id) e where d.sql_id = e.sql_id and d.ip = e.ip))); (3)、web展示层 点击(此处)折叠或打开 def topsql_line_servlet(request): cursor = conn.cursor() query = "select ip, (select service_name from hsql.h_instance h where h.ip = b.ip and rownum = 1) service_name, sql_id, executions, elap_per_exec, to_char(ch_date, 'hh34:mi') sj, to_char(ch_date, 'yyyy-mm-dd') rq from hsql.h_topsql b where ch_date >

Trunc (sysdate)

Order by sj "

Print query

Cursor.execute (query)

Resultset = cursor.fetchall ()

Cursor.close ()

Conn.close ()

VII. Summary

Through the application of Oracle performance trend analysis tool, we can carry out fine-grained database performance management, find potential hidden dangers of information system performance degradation in time, optimize information system performance optimization and enhance user experience through continuous and normalized information system performance optimization.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.