def ora_top_sql_detail():
#根据请求类型构建查询sql，如果是get，则列出所有实例
if request.method == 'GET':
sk1 = request.args.get('sk1')
sk2 = request.args.get('sk2')
inst_id = request.args.get('inst_id')
sql1 = '''select i.id,i.host_name,i.inst_name,t.sql_id,substring(t.sql_text,1,30) sql_text,
t.disk_reads_delta,t.sql_text sql_full_text,
t.buffer_gets_delta,round(t.cpu_time_delta/1000000,2) cpu_time_delta,round(t.elapsed_time_delta/1000000,2) elapsed_time_delta,t.user_io_wait_time_delta,t.executions_delta,
t.rows_processed_delta,t.snap_time from ora_inst i,ora_top_sql_tmp t
where i.id = t.inst_id and i.id = ''' + str(
inst_id) + ''' order by ''' + sk1 + ''' desc limit 10'''
elif request.method == 'POST':
sk1 = 'buffer_gets_delta'
sk2 = 'inst_name'
inst_id = request.form.get('inst_id')
sql1 = '''select i.id,i.host_name,i.inst_name,t.sql_id,substring(t.sql_text,1,30) sql_text,
t.disk_reads_delta,t.sql_text sql_full_text,
t.buffer_gets_delta,round(t.cpu_time_delta/1000000,2) cpu_time_delta,round(t.elapsed_time_delta/1000000,2) elapsed_time_delta,t.executions_delta,
t.rows_processed_delta,t.snap_time from ora_inst i,ora_top_sql_tmp t
where i.id = t.inst_id and i.id = ''' + str(
inst_id) + ''' order by ''' + sk1 + ''' desc limit 5'''
else:
sk1 = 'buffer_gets_delta'
sk2 = 'inst_name'
sql1 = '''select id,inst_name from ora_inst order by ''' + sk2
#print(sql1)
db = conn_mysql()
cursor = db.cursor()
cursor.execute(sql1)
rs = cursor.fetchall()
tag, inst_name = 'TOP SQL', rs[0]['inst_name']
#for k in rs:
#print('key ===',k,'**********')
return render_template('oracle/ora_top_sql_detail.html', **locals())
def ora_mon_target():
if session.get('logged_in'):
username = session['username']
tag = '监控指标'
db = conn_mysql()
cursor = db.cursor()
sql = '''select a.id,a.class,a.type,a.name,a.enable,a.level1,a.level2,a.level3,a.target_type
from ora_alert_define a where inst_id=0 order by class,type'''
cursor.execute(sql)
entries = cursor.fetchall()
print(entries)
cursor.close()
db.close()
return render_template('oracle/ora_mon_target.html', **locals())
else:
return render_template('login.html')
def ora_inst_list():
if session.get('logged_in'):
username = session['username']
db = conn_mysql()
cursor = db.cursor()
sql = 'select i.id,i.host_name,i.ip_addr,i.inst_name,i.db_name,i.db_type,g.gname' \
' from ora_inst i,app_group g' \
' where i.gid = g.id order by gname,inst_name'
cursor.execute(sql)
entries = cursor.fetchall()
print(entries)
cursor.close()
db.close()
return render_template('oracle/ora_inst_list.html', entries=entries)
else:
return render_template('login.html')
def ora_top_sql_report():
if request.method == 'GET':
period = request.args.get('period')
tick = request.args.get('tick')
print('ora_top_sql_report:period========', period, tick)
inst_id, sql_id = request.args.get('inst_id'), request.args.get(
'sql_id')
print('sql_id========', sql_id)
sql1 = ''' select sql_id,phv,sql_text,buffer_gets_delta,disk_reads_delta,
cpu_time_delta,elapsed_time_delta,executions_delta,user_io_wait_time_delta,
rows_processed_delta,snap_time from ora_top_sql
where sql_id =\'''' + sql_id + '''\'
and snap_time > date_sub(now(),interval ''' + period + ''' hour) order by snap_time desc'''
sql2 = '''select * from
(select sql_id,child_number,row_number() over(partition by plan_hash_value
order by child_number) rn
from v$sql where sql_id=\'''' + sql_id + '''\')
where rn=1'''
db = conn_mysql()
cursor = db.cursor()
cursor.execute(sql1)
rs_td = cursor.fetchall()
#连接到oracle数据库
dbora = conn_ora(inst_id)
curora = dbora.cursor()
# 获取该sql不同plan_hash_value的child_number
curora.execute(sql2)
rs_chdno = curora.fetchall()
#获取该sql所有执行计划
l_plan = []
for x in rs_chdno:
l_rs = []
sql_id, chdno = x[0], x[1]
sql3 = '''select * from table(dbms_xplan.display_cursor(\'''' + sql_id + '''\',''' + str(
chdno) + '''))'''
#sql3 = '''select * from table(dbms_xplan.display_cursor('7b2twsn8vgfsc',0))'''
curora.execute(sql3)
rs_plan = curora.fetchall()
#print('rs_plan**********&&&&&&&&&&&&&&&&',rs_plan)
for x in rs_plan:
l_rs.append(x[0].replace(' ', ' '))
#l_rs.append(x[0])
l_plan.append(l_rs)
#获取相关对象(表和索引)的统计信息
print(l_plan)
return render_template('oracle/ora_top_sql_report.html', **locals())
def ora_top_sql_range():
if request.method == 'POST':
sk1 = request.form.get('sk1')
sk2 = request.form.get('sk2')
print("sk1=======", sk1, "sk2========", sk2)
inst_id, begin_date, end_date = request.form.get(
'inst_id'), request.form.get('begin_date'), request.form.get(
'end_date')
print('inst_id====', inst_id, 'begin_date====', begin_date,
'end_date=====', end_date)
elif request.method == 'GET':
sk1 = request.args.get('sk1')
sk2 = request.args.get('sk2')
period = request.args.get('period')
begin_date = request.args.get('begin_date')
end_date = request.args.get('end_date')
print("sk1=======", sk1)
inst_id, period = request.args.get('inst_id'), request.args.get(
'period')
if period is not None and (begin_date is None and end_date is None):
end_date = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
begin_date = (datetime.datetime.now() - datetime.timedelta(
minutes=int(period))).strftime('%Y-%m-%d %H:%M:%S')
#return 'begin_time = ' + begin_date + 'end_time=' + end_date
else:
return 'error request!'
sql1 = '''select t.sql_id,substring(t.sql_text,1,30) sql_text,t.sql_text sql_full_text,
sum(t.disk_reads_delta) disk_reads_delta,
sum(t.buffer_gets_delta) buffer_gets_delta,round(sum(t.cpu_time_delta)/1000000,2) cpu_time_delta,
round(sum(t.elapsed_time_delta)/1000000,2) elapsed_time_delta,sum(t.user_io_wait_time_delta) user_io_wait_time_delta,
sum(t.executions_delta) executions_delta,
sum(t.rows_processed_delta) rows_processed_delta from ora_top_sql t
where t.inst_id = ''' + str(inst_id) + ''' and snap_time>=str_to_date(\'''' \
+ begin_date + '''\','%Y-%m-%d %H:%i:%s') and snap_time<=str_to_date(\'''' \
+ end_date + '''\','%Y-%m-%d %H:%i:%s') group by t.sql_id,substring(t.sql_text,1,40),t.sql_text \
order by ''' + sk1 + ''' desc limit 10'''
db = conn_mysql()
cursor = db.cursor()
cursor.execute(sql1)
rs = cursor.fetchall()
cursor.execute('select inst_name from ora_inst where id = ' + str(inst_id))
rs2 = cursor.fetchall()
inst_name = rs2[0]['inst_name']
tag = 'TOP SQL'
return render_template('oracle/ora_top_sql_detail_range.html', **locals())
def ora_alert_detail():
if request.method == 'GET':
inst_id = request.args.get('inst_id')
#print 'inst_id ================================' + inst_id
db = conn_mysql()
cursor = db.cursor()
sql = '''select i.id,i.host_name,i.ip_addr,i.inst_name,i.db_name,left(a.alert_info,200) alert_info,a.create_time,a.level
from ora_inst i,ora_alert_info a
where
a.inst_id = i.id
and i.id = ''' + inst_id + '''
order by id,level
'''
cursor.execute(sql)
entries = cursor.fetchall()
cursor.close()
db.close()
return render_template('oracle/ora_alert_detail.html', entries=entries)
def ora_alert():
db = conn_mysql()
cursor = db.cursor()
sql = '''select i.id,i.host_name,i.ip_addr,i.inst_name,i.db_name,i.db_type,g.gname,
sum(case a.`level` when 1 then 1 else 0 end) sum_lv1,
sum(case a.`level` when 2 then 1 else 0 end) sum_lv2,
sum(case a.`level` when 3 then 1 else 0 end) sum_lv3
from ora_inst i,ora_alert_info a ,app_group g
where
a.inst_id = i.id
and i.gid = g.id
group by i.host_name,i.ip_addr,i.inst_name,i.db_name,i.db_type,g.gname;'''
cursor.execute(sql)
entries = cursor.fetchall()
cursor.close()
db.close()
return render_template('oracle/ora_alert.html',
entries=entries,
tag='监控告警')