def sqlserver_analysis(audit_job):
try:
schema, max_rows, order_by_pred, time_span = (audit_job.schema,
audit_job.max_rows,
audit_job.order_by,
audit_job.time_span)
if time_span:
begin_time, end_time = datetime.now(), datetime.now() - (timedelta(
hours=1))
else:
begin_time, end_time = audit_job.snapshot_begin_time, audit_job.snapshot_end_time
rule_list = []
database = audit_job.database
strategy_dict = audit_job.strategy
audit_rule_queryset = (Audit_Rule.objects.filter(
database=database)).filter(enabled=True)
for k, v in strategy_dict.items():
rule_list += list((audit_rule_queryset.filter(
audit_type=k)).filter(target__in=v))
logger.error('sql_audit_analysis begin build query')
query_total_json = build_total_query(
TotalTemplateJSON.get(database.db_type), database, schema)
query_detail_json = {}
query_problem_json = {}
object_rules_list = [
rule for rule in rule_list if rule.audit_type == ''
]
for rule in set(object_rules_list):
query_detail_json[rule.name] = build_rule_query(
DetailTemplateJson, database, rule, schema, max_rows,
order_by_pred)
query_problem_json[rule.name] = build_rule_query(
ProblemTemplateJSON, database, rule, schema, max_rows,
order_by_pred)
logger.error('sql_audit_analysis begin run total')
flag, total_result = run_batch_sql(database, query_total_json, schema)
if not flag:
logger.error(total_result)
raise build_exception_from_java(total_result)
logger.error('sql_audit_analysis begin run detail')
flag, detail_result = run_batch_sql(database, query_detail_json,
schema)
if not flag:
logger.error(detail_result)
raise build_exception_from_java(detail_result)
logger.error('sql_audit_analysis begin run problem')
flag, problem_result = run_batch_sql(database, query_problem_json,
schema)
if not flag:
logger.error(problem_result)
raise build_exception_from_java(problem_result)
logger.error('sql_audit_analysis begin build result')
return build_audit_result(database, rule_list, total_result,
problem_result, detail_result, audit_job)
except Exception as e:
logger.error(str(e))
def get_mysql_summary(pk):
conn = Database.objects.get(pk=pk)
db_type = conn.db_type
query = reprocess_query(Space_Total_Query, {'pk': pk})
total_space = execute_return_json(query)
if not total_space:
get_space(conn)
total_space = execute_return_json(query)
query = SummaryQuery.get(db_type)
flag, json_data = run_batch_sql(conn, query)
if not flag:
raise build_exception_from_java(json_data)
database_json = {
x.get('Variable_name'): x.get('Value')
for x in json_data.get('database')
if x.get('Variable_name') in ('version', 'version_comment',
'version_compile_machine',
'version_compile_os',
'default_storage_engine', 'general_log',
'log_bin', 'slow_query_log')
}
memory_json = {
x.get('Variable_name'): x.get('Value')
for x in json_data.get('database')
if x.get('Variable_name') in ('innodb_buffer_pool_size',
'join_buffer_size', 'key_buffer_size',
'query_cache_size', 'sort_buffer_size',
'thread_cache_size')
}
summary_data = {
'space': total_space,
'database': database_json,
'memory': memory_json
}
return summary_data
def get_ash_report(pk, instance_id, begin_time, end_time):
try:
database = Database.objects.get(pk=pk)
json_data = []
flag, json_data = run_sql(database, DBID_Query)
if not flag:
raise build_exception_from_java(json_data)
db_id = json_data[0].get('DBID')
key, inst_str = get_key_inst_str(database, instance_id)
query_ash = ASH_Query.get(key)
options = {
'db_id': db_id,
'inst_str': inst_str,
'begin_time': timestamp_to_char(begin_time),
'end_time': timestamp_to_char(end_time)
}
query_ash['report'] = query_ash.get('report').format(**options)
flag, report_data = run_batch_sql(database, query_ash)
if not flag:
raise build_exception_from_java(report_data)
report_html = ('').join([
x.get('OUTPUT') for x in report_data.get('report')
if x.get('OUTPUT')
]) if report_data.get('report') else ''
return {'report_html': report_html}
except ObjectDoesNotExist:
return {'error_message': ''}
except Exception as err:
return {'error_message': str(err)}
def get_sqlmon_report(pk, sql_id, inst_id, sql_exec_id, report_type, time_span):
try:
database = Database.objects.get(pk=pk)
sqlmon = None
sqlmon_data = ((((SQLMON.objects.filter(database=database)).filter(sql_id=sql_id)).filter(inst_id=inst_id)).filter(sql_exec_id=sql_exec_id)).first()
if sqlmon_data:
sqlmon = sqlmon_data.sqlmon
else:
query_json = {'alter_session':"ALTER SESSION SET EVENTS '31156 trace name context forever, level 0x400'",
'sqlmon':f'''
select
dbms_sqltune.report_sql_monitor(
type=>'{report_type}',
inst_id=>{inst_id},
sql_id=>'{sql_id}',
sql_exec_id=>{sql_exec_id},
report_level=>'ALL')
MONITOR_REPORT
from dual'''}
flag, sqlmon_data = run_batch_sql(database, query_json)
sqlmon = sqlmon_data.get('sqlmon')[0].get('MONITOR_REPORT') if sqlmon_data.get('sqlmon') else ''
sqlmon = sqlmon.replace('http://download.oracle.com', '/static') if sqlmon else ''
return {'report_html': sqlmon}
except ObjectDoesNotExist:
return {'error_message': ''}
except Exception as err:
return {'error_message': str(err)}
def oracle_analysis(audit_job):
try:
schema, max_rows, order_by_pred = audit_job.schema, audit_job.max_rows, audit_job.order_by
rule_list = []
database = audit_job.database
strategy_dict = audit_job.strategy
audit_rule_queryset = ((Audit_Rule.objects.filter(
database=database)).filter(enabled=True)).filter(
is_static_rule=False)
for k, v in strategy_dict.items():
rule_list += list((audit_rule_queryset.filter(
audit_type=k)).filter(target__in=v))
logger.error('sql_audit_analysis begin build query')
query_total_json = build_total_query(
TotalTemplateJSON.get(database.db_type), database, schema)
query_detail_json = {}
query_problem_json = {}
for rule in set(rule_list):
query_detail_json[rule.name] = build_rule_query(
DetailTemplateJson, database, rule, schema, max_rows,
order_by_pred)
query_problem_json[rule.name] = build_rule_query(
ProblemTemplateJSON, database, rule, schema, max_rows,
order_by_pred)
logger.error('sql_audit_analysis begin run total')
flag, total_result = run_batch_sql(database, query_total_json)
if not flag:
logger.error(total_result)
raise build_exception_from_java(total_result)
logger.error('sql_audit_analysis begin run detail')
flag, detail_result = run_batch_sql(database, query_detail_json)
if not flag:
logger.error(detail_result)
raise build_exception_from_java(detail_result)
logger.error('sql_audit_analysis begin run problem')
flag, problem_result = run_batch_sql(database, query_problem_json)
if not flag:
logger.error(problem_result)
raise build_exception_from_java(problem_result)
logger.error('sql_audit_analysis begin build result')
return build_audit_result(database, rule_list, total_result,
problem_result, detail_result, audit_job)
except Exception as e:
logger.error(str(e))
def detail_info(pk, name, days=7, limit=200):
try:
database = Database.objects.get(pk=pk)
db_type = database.db_type
options = {'pk': pk, 'days': days, 'limit': limit, 'name': name}
space_detail = (Space_Detail.objects.filter(database=database)).first()
if not space_detail:
get_space(database)
space_detail = (Space_Detail.objects.filter(
database=database)).first()
name_detail = {}
for x in space_detail.detail:
if x.get('TABLESPACE_NAME') == name:
name_detail = x
break
query = reprocess_query(Space_Detail_Lag_Query, options)
space_trend = execute_return_json(query)
query = reprocess_query(Space_Detail_Realtime_Query.get(db_type),
options)
if not is_temp(db_type, name_detail):
if db_type in ('db2', 'oracle'):
query.pop('temp')
if db_type == 'sqlserver':
flag, json_data = run_batch_sql(database, query, name)
else:
flag, json_data = run_batch_sql(database, query)
if not flag:
raise build_exception_from_java(json_data)
detail_data = {
'space_detail': name_detail,
'space_trend': {
'name':
'(MB)',
'data':
[[x.get('CREATED_AT'), x.get('DELTA')] for x in space_trend]
},
'table_data': json_data.get('segment') if json_data else [],
'temp': json_data.get('temp') if json_data else []
}
if db_type != 'mysql':
detail_data['datafile'] = json_data.get(
'datafile') if json_data.get('datafile') else []
return detail_data
except ObjectDoesNotExist:
return {'error_message': ''}
def get_backup(pk):
days = 7
query = {
'config':
'select * from v$rman_configuration',
'history':
f'''select
j.session_recid, --j.session_stamp,
to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
round((j.output_bytes/1024/1024),2) output_mbytes, j.status, j.input_type,
decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
3, 'Tuesday', 4, 'Wednesday',
5, 'Thursday', 6, 'Friday',
7, 'Saturday') WEEK,
round(j.elapsed_seconds,-1) ELAPSED_TIME_SEC, j.TIME_TAKEN_DISPLAY,
x.cf, x.df, x.i0, x.i1, x.l,
ro.inst_id output_instance,x.device_type
from V$RMAN_BACKUP_JOB_DETAILS j
left outer join (select
d.session_recid, d.session_stamp,
sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
sum(case when d.controlfile_included = 'NO'
and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
sum(case when d.backup_type = 'L' then d.pieces else 0 end) L,d.device_type
from
V$BACKUP_SET_DETAILS d
join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where s.input_file_scan_only = 'NO'
group by d.session_recid, d.session_stamp,d.device_type) x
on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
from GV$RMAN_OUTPUT o
group by o.session_recid, o.session_stamp)
ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-{days}
order by j.start_time''',
'long':
'select sid || \',\'|| serial# || \'@\' || inst_id as SESSION_ID,to_char(start_time, \'yyyy-mm-dd hh24:mi:ss\') start_time,ELAPSED_SECONDS,sofar,totalwork,\nopname, round(sofar/totalwork*100,-1) "PCT"\n from gv$session_longops\n where opname like \'RMAN:%\'\n and opname not like \'RMAN: aggregate%\'\n and totalwork!=0'
}
try:
database = Database.objects.get(pk=pk)
flag, json_data = run_batch_sql(database, query)
if not flag:
raise build_exception_from_java(json_data)
else:
return json_data
except ObjectDoesNotExist:
return {'error_message': ''}
except Exception as err:
return {'error_message': str(err)}
def execute_sqltuning_task(pk, sql_id, timeout):
try:
database = Database.objects.get(pk=pk)
import re
dt_postfix = datetime.now().strftime('%Y-%m-%d_%H:%M')
taskname = sql_id + '_' + dt_postfix
query_submit_job = f'''
declare
a varchar2(100);
begin
BEGIN
dbms_sqltune.execute_tuning_task('%s');
EXCEPTION when others then
null;
end;
a := dbms_sqltune.create_tuning_task(
task_name=>'{taskname}',
description=>'{taskname}',
scope=>dbms_sqltune.scope_comprehensive,
time_limit=>{timeout},
sql_id=>'{sql_id}'
);
dbms_sqltune.execute_tuning_task('{taskname}');
end;'''
query_report = {'report':f'''select dbms_sqltune.report_tuning_task('{taskname}') report FROM dual''', 'benefit':f'''
select hint, benefit from (
select case when attr5 like 'OPT_ESTIMATE%' then cast(attr5 as varchar2(4000)) when attr1 like 'OPT_ESTIMATE%' then attr1 end hint,benefit
from dba_advisor_recommendations t join dba_advisor_rationale r using (task_id,rec_id)
where t.task_name = '{taskname}' and t.type='SQL PROFILE'
--and r.message='This attribute adjusts optimizer estimates.'
) where hint is not null order by to_number(regexp_replace(hint,'^.*=([0-9.]+)[^0-9]*$','\1'))'''}
flag, result = run_plsql(database, query_submit_job)
if not flag:
raise build_exception_from_java(result)
flag, sqltune_data = run_batch_sql(database, query_report)
accept_sql_profile = False
sql_list = None
report_data = sqltune_data.get('report')
sqltune_report = report_data[0].get('REPORT') if report_data else ''
if re.search('accept_sql_profile', sqltune_report, re.IGNORECASE):
sql_list = re.findall('execute dbms_sqltune.accept_sql_profile[^;]+;', sqltune_report, re.IGNORECASE)
for idx, val in enumerate(sql_list):
sql_list[idx] = re.sub('$', '\n end;', re.sub('execute', 'begin \n', val))
accept_sql_profile = True
sqltune_result = {'report':report_data,
'action':sql_list,
'accept_sql_profile':accept_sql_profile}
return sqltune_result
except ObjectDoesNotExist:
return {'error_message': ''}
except Exception as err:
return {'error_message': str(err)}
def analysis_from_post(request):
database_id = request.data.get('database_id')
schema = request.data.get('schema', None)
max_rows = request.data.get('max_rows', None)
order_by_pred = request.data.get('order_by_pred', '')
audit_result = {}
try:
database = Database.objects.get(pk=database_id)
rule_list = ((Audit_Rule.objects.filter(database=database)).filter(
enabled=True)).filter(is_static_rule=False)
query_total_json = build_total_query(TotalTemplateJSON, database,
schema)
query_detail_json = {}
query_problem_json = {}
for rule in rule_list:
query_detail_json[rule.name] = build_rule_query(
DetailTemplateJson, database, rule, schema, max_rows,
order_by_pred)
query_problem_json[rule.name] = build_rule_query(
ProblemTemplateJSON, database, rule, schema, max_rows,
order_by_pred)
flag, detail_result = run_batch_sql(database, query_detail_json)
if not flag:
raise build_exception_from_java(detail_result)
flag, problem_result = run_batch_sql(database, query_problem_json)
if not flag:
raise build_exception_from_java(problem_result)
flag, total_result = run_batch_sql(database, query_total_json)
if not flag:
raise build_exception_from_java(total_result)
audit_result = build_audit_result(database, rule_list, total_result,
problem_result, detail_result, None)
collect_sql_text(database, schema)
return Response(audit_result, status=status.HTTP_200_OK)
except ObjectDoesNotExist:
return Response({'error_message': ''},
status=status.HTTP_500_INTERNAL_SERVER_ERROR)
def get_sqlserver_summary(pk):
conn = Database.objects.get(pk=pk)
db_type = conn.db_type
query = reprocess_query(SQLServer_Space_Total_Query, {'pk': pk})
total_space = execute_return_json(query)
if not total_space:
get_space(conn)
total_space = execute_return_json(query)
query = SummaryQuery.get(db_type)
flag, json_data = run_batch_sql(conn, query)
if not flag:
raise build_exception_from_java(json_data)
space = {'space': total_space}
summary_data = {**space, **json_data}
return summary_data
def get_lock_session(pk, time_span=None):
try:
database = Database.objects.get(pk=pk)
db_type = database.db_type
json_data = {}
command_list = []
if time_span == 'realtime':
query = get_lock_query(database)
flag, json_data = run_batch_sql(database, query)
if not flag:
raise build_exception_from_java(json_data)
cmd_data = get_unlock_data(database)
command_list = [x.get('CMD') for x in cmd_data]
else:
query_lock = Lock_History_Local_Query[
database.db_type].value.format(pk, time_span)
query_trans = Local_Transaction_Query.format(pk, time_span)
json_data['lock'] = execute_return_json(query_lock)
trans = execute_return_json(query_trans)
if trans:
json_data['transaction'] = trans[0].get('TRANSACTIONS')
return {
'lock': {
'blocker_header':
Blocker_Header[database.db_type].value,
'waiter_header':
Waiter_Header[database.db_type].value,
'blocker_id': ['B_BLOCKER', 'W_WAITER'],
'waiter_id':
'W_WAITER',
'session_detail_keys': ['B_BLOCKER', 'W_WAITER'],
'sql_detail_keys':
['B_SQL_ID', 'B_PREV_SQL_ID', 'W_SQL_ID', 'W_PREV_SQL_ID'],
'data':
json_data.get('lock'),
'advice':
command_list
},
'transaction': {
'data': json_data.get('transaction'),
'sql_detail_keys': ['SQL_ID'],
'session_detail_keys': ['SESSION_ID']
}
}
except ObjectDoesNotExist:
return {'error_message': ''}
except Exception as err:
return {'error_message': str(err)}
def get_db2_sql_audit(database, sql_id, sql_text=None, plans=[]):
schema_list = [("('{}','{}')").format(x.get('OBJECT_OWNER'), x.get('OBJECT_NAME')) for x in plans if x.get('OBJECT_NAME')]
schema_list_str = (',').join(schema_list) if schema_list else "('','')"
audit_rule_queryset = Audit_Rule.objects.filter((Q(database=database)) & (Q(enabled=True)) & (Q(audit_type='')) & (Q(single=True)))
query_detail_json = {}
for rule in audit_rule_queryset:
query = build_db2_rule_single_query(SingleTemplateJson.get(database.db_type), database, rule, schema_list_str)
if query:
query_detail_json[rule.name] = query
flag, audit_data = run_batch_sql(database, query_detail_json)
audit_result = build_single_audit_result(audit_rule_queryset, audit_data)
sql_audit_plan_result = get_db2_single_sql_plan_audit(database, sql_text, plans)
sql_audit_text_result = get_db2_single_sql_text_audit(database, sql_text, plans)
audit_result = {**audit_result, **sql_audit_plan_result, **sql_audit_text_result}
print(audit_result)
return audit_result
def space_info(pk, days=7):
try:
database = Database.objects.get(pk=pk)
db_type = database.db_type
options = {'pk': pk, 'days': days}
space_detail = (Space_Detail.objects.filter(database=database)).first()
if not space_detail:
get_space(database)
space_detail = (Space_Detail.objects.filter(
database=database)).first()
space_detail = space_detail.detail if space_detail else {}
space_total_query = Space_Total_Query if db_type != 'sqlserver' else SQLServer_Space_Total_Query
query = reprocess_query(space_total_query, options)
total_space = execute_return_json(query)
query = reprocess_query(Space_Total_Lag_Query, options)
total_trend = execute_return_json(query)
if db_type == 'oracle':
query = reprocess_query(Space_Realtime_Query.get(db_type), options)
flag, json_data = run_batch_sql(database, query)
if not flag:
raise build_exception_from_java(json_data)
else:
json_data = {}
local_data = {
'space_detail': space_detail,
'total_space': total_space[0] if total_space else {},
'total_trend': {
'name':
'(MB)',
'data':
[[x.get('CREATED_AT'), x.get('DELTA')] for x in total_trend]
}
}
json_data['switch_trend'] = {
'name':
'',
'data': [[x.get('TIME'), x.get('COUNT')]
for x in json_data.get('switch_trend', [])]
}
space_summary = {**local_data, **json_data}
return space_summary
except ObjectDoesNotExist:
return {'error_message': ''}
except Exception as err:
return {'error_message': str(err)}
def gen_sql_mononitor_and_binds(database, sqlmon_list):
sqlmon_filter_list = (',').join([
("({},'{}',{})").format(x.get('INST_ID'), x.get('SQL_ID'),
x.get('SQL_EXEC_ID')) for x in sqlmon_list
])
query = {
'binds':
f'''
SELECT xt.*, (select child_number from gv$sql sql where sql.inst_id = x.inst_id and sql.address = x.SQL_CHILD_ADDRESS) CHILD_NUMBER, to_char(x.SQL_EXEC_START, 'yyyy-mm-dd hh24:mi:ss')LAST_CAPTURED, null REAL_DATA
FROM (select xmltype(binds_xml) xml_data, SQL_EXEC_START, inst_id, SQL_CHILD_ADDRESS, sql_id from gv$sql_monitor
where binds_xml is not null and (inst_id, sql_id, sql_exec_id) in ({sqlmon_filter_list})) x,
XMLTABLE('/binds/bind'
PASSING x.xml_data
COLUMNS
name VARCHAR2(100) PATH '@name',
pos number PATH '@pos',
DATATYPE_STRING VARCHAR2(100) PATH '@dtystr',
VALUE_STRING VARCHAR2(100) PATH '/'
) xt
'''
}
flag, sqlmon_data = run_batch_sql(database, query)
if not flag:
return sqlmon_data
else:
if sqlmon_data.get('sqlmon'):
for x in sqlmon_data.get('sqlmon'):
m = SQLMON()
m.inst_id = x.get('INST_ID')
m.sql_id = x.get('SQL_ID')
m.status = x.get('STATUS')
m.username = x.get('USERNAME')
m.elapsed_time = x.get('ELAPSED_TIME')
m.db_time = x.get('DB_TIME')
m.db_cpu = x.get('DB_CPU')
m.sql_exec_id = x.get('SQL_EXEC_ID')
m.sql_exec_start = x.get('SQL_EXEC_START')
m.sql_plan_hash_value = x.get('SQL_PLAN_HASH_VALUE')
m.sql_text = x.get('SQL_TEXT')
m.sqlmon = x.get('SQLMON')
m.database = database
m.created_at = datetime.now().replace(microsecond=0)
m.save()
return sqlmon_data.get('binds')
def get_oracle_session_detail(database, session_id):
detail_format = get_default_detail_format()
import re
prog = re.compile('([0-9]+),([0-9]+)@([0-9]+)')
m = prog.search(session_id)
sid, serial, inst_id = (1, 2, 3)
if m:
sid, serial, inst_id = m.group(1), m.group(2), m.group(3)
detail_format['instance_id'] = inst_id
options = {'inst_id':inst_id,
'sid':sid,
'serial':serial}
detai_query = Detai_Query.format(**options)
cursur_query = Old_Cursor_Query.format(**options)
query = {'detail':detai_query,
'cursor':cursur_query}
flag, json_data = run_batch_sql(database, query)
if not flag:
raise build_exception_from_java(json_data)
detail_data = json_data.get('detail')
cursor_data = json_data.get('cursor')
if detail_data == None:
detai_query = Detai_Query_Without_IP.format(**options)
flag, json_data = run_sql(database, detai_query)
if not flag:
raise build_exception_from_java(json_data)
detail_data = json_data
if detail_data:
detail_data = detail_data[0]
detail_info = {u'\u8fde\u63a5\u4fe1\u606f':{x:detail_data[x] for x in ('SID', 'SERIAL#', 'STATUS', 'USERNAME', 'SPID', 'LOGON_TIME',
'SERVER') if x in detail_data},
u'\u5ba2\u6237\u7aef\u4fe1\u606f':{x:detail_data[x] for x in ('OSUSER', 'PROCESS', 'MACHINE', 'IP') if x in detail_data},
u'\u5e94\u7528\u4fe1\u606f':{x:detail_data[x] for x in ('SQL_ID', 'PREV_SQL_ID', 'LAST_CALL_ET', 'PROGRAM', 'MODULE',
'ACTION', 'SERVICE_NAME') if x in detail_data},
u'\u7b49\u5f85\u4fe1\u606f':{x:detail_data[x] for x in ('EVENT', 'WAIT_CLASS', 'P1', 'P2', 'P3') if x in detail_data},
u'\u963b\u585e\u4f1a\u8bdd':{x:detail_data[x] for x in ('BLOCKING_INSTANCE', 'BLOCKING_SESSION') if x in detail_data},
u'\u4e8b\u52a1\u4fe1\u606f':{x:detail_data[x] for x in ('XIDUSN', 'XIDSLOT', 'XIDSQN', 'TRX_STARTED', 'USED_UBLK',
'USED_UREC') if x in detail_data}}
detail_format['detail'] = detail_info
detail_format['cursor'] = cursor_data
return detail_format
def get_oracle_summary(pk):
key = 'oracle:%s:index' % pk
read_data = redis.get(key)
if read_data:
return json.loads(read_data)
else:
conn = Database.objects.get(pk=pk)
db_type = conn.db_type
query = reprocess_query(Space_Total_Query, {'pk': pk})
total_space = execute_return_json(query)
if not total_space:
get_space(conn)
total_space = execute_return_json(query)
query = SummaryQuery.get(db_type)
flag, json_data = run_batch_sql(conn, query)
if not flag:
raise build_exception_from_java(json_data)
space = {'space': total_space}
summary_data = {**space, **json_data}
redis.setex(key, 86400, json.dumps(summary_data))
return summary_data
def get_oracle_sql_audit(database, sql_id, only_tune=False):
audit_rule_queryset = []
if only_tune:
audit_rule_queryset = Audit_Rule.objects.filter((Q(database=database)) & (Q(enabled=True)) & (Q(single=True)) & (Q(is_static_rule=False)) & (Q(name__in=['', '', ''])))
else:
audit_rule_queryset = Audit_Rule.objects.filter((Q(database=database)) & (Q(enabled=True)) & (Q(single=True)) & (Q(is_static_rule=False)))
tables_query = build_tables_query(database, sql_id)
flag, tables = run_sql(database, tables_query)
if not flag:
return tables
else:
schema_list = [("('{}','{}')").format(x.get('OBJECT_OWNER'), x.get('OBJECT_NAME')) for x in tables]
schema_list_str = (',').join(schema_list) if schema_list else "('','')"
query_detail_json = {}
for rule in audit_rule_queryset:
query = build_rule_single_query(SingleTemplateJson.get(database.db_type), database, rule, sql_id, schema_list_str)
if query:
query_detail_json[rule.name] = query
flag, audit_data = run_batch_sql(database, query_detail_json)
audit_result = build_single_audit_result(audit_rule_queryset, audit_data)
return audit_result
def oracle_sql_detail(pk, sql_id, sql_text=None, instance_id=None, time_span=None, begin_time=None, end_time=None, cache=True, activity=True, sql_audit=True, only_tune=False):
database = Database.objects.get(pk=pk)
if instance_id == 'null':
instance_id = database.db_name
inst_id = database.instance_id_list.split(',')[0] if not instance_id or instance_id == database.db_name or instance_id == '0' else instance_id
if sql_audit:
inst_id = database.instance_id_list
key_audit = f'''{pk}:sql_detail:{sql_id}:audit'''
audit_data = None
audit_data_json = {}
if cache:
audit_data = redis.get(key_audit)
if audit_data != None:
audit_data_json = json.loads(audit_data)
sql_detail = get_default_sql_detail_format(database.db_type)
if sql_id != 'null':
if time_span == 'realtime':
sqldetail_sql = get_realtime_sql(sql_id, inst_id)
if only_tune:
sqldetail_sql.pop('binds')
flag, sqldetail_data = run_batch_sql(database, sqldetail_sql)
if not flag:
return sqldetail_data
stat_data = sqldetail_data.get('stats')
plan_data = sqldetail_data.get('plans')
sqlmon_data = sqldetail_data.get('sqlmon')
bind_data = only_tunesqldetail_data.get('binds')[]
for x in stat_data:
key = ('{}-{}-{}').format(x.get('INST_ID'), x.get('CHILD_NUMBER'), x.get('PLAN_HASH_VALUE'))
child_summary = {k:v for k, v in x.items() if k in ('CHILD_NUMBER',
'PLAN_HASH_VALUE',
'PARSING_SCHEMA_NAME',
'LAST_LOAD_TIME',
'MODULE', 'ACTION',
'SERVICE')}
pie_chart_data = {k:v for k, v in x.items() if k in ('ON CPU', 'Application',
'Cluster', 'Concurrency',
'User I/O')}
execution_stats = {k:v for k, v in x.items() if k in ('EXECUTIONS',
'ELAPSED_TIME',
'CPU_TIME',
'BUFFER_GETS',
'DISK_READS',
'DIRECT_WRITES',
'ROWS_PROCESSED',
'FETCHES')}
metric_dict = {'EXECUTIONS':'',
'ELAPSED_TIME':'()',
'CPU_TIME':'CPU()',
'BUFFER_GETS':'',
'DISK_READS':'',
'DIRECT_WRITES':'',
'ROWS_PROCESSED':'',
'FETCHES':''}
total_executions = execution_stats.get('EXECUTIONS') if execution_stats.get('EXECUTIONS') != 0 else 1
total_rows = execution_stats.get('ROWS_PROCESSED') if execution_stats.get('ROWS_PROCESSED') != 0 else 1
execution_data = [{u'\u6307\u6807':metric_dict.get(k), u'\u603b\u6570':v, u'\u5e73\u5747\u6bcf\u6b21\u6267\u884c':round(v / total_executions), u'\u5e73\u5747\u6bcf\u884c\u8bb0\u5f55':round(v / total_rows)} for k, v in execution_stats.items()]
sql_detail['stats'][key] = {'child_summary':child_summary,
'pie_chart_data':pie_chart_data,
'execution_stats':{'header':[
'', '', '', ''],
'data':execution_data}}
plan_dic = defaultdict(list)
for x in plan_data:
key = ('{}-{}-{}').format(x.get('INST_ID'), x.get('CHILD_NUMBER'), x.get('PLAN_HASH_VALUE'))
x.pop('INST_ID')
plan_dic[key].append(x)
sql_detail['plans']['data'] = plan_dic
if sql_audit:
if sqlmon_data:
sqlmon_data = sqlmon_data[:MAX_SQLMON_FOR_SQL_AUDIT]
binds_from_sqlmon = gen_sql_mononitor_and_binds(database, sqlmon_data)
bind_data = bind_data + binds_from_sqlmon
sql_detail['sqlmon']['data'] = sqlmon_data
sql_detail['binds']['data'] = bind_data
else:
sqldetail_sql = get_hist_sql(sql_id, inst_id, begin_time, end_time)
query_sqlmon = f'''
select
ID,
STATUS,
SQL_ID,
ELAPSED_TIME,
DB_TIME,
DB_CPU,
SQL_EXEC_ID,
SQL_EXEC_START,
SQL_PLAN_HASH_VALUE,
INST_ID,
USERNAME
from monitor_sqlmon
where created_at BETWEEN to_timestamp({begin_time}) and to_timestamp({end_time})
and sql_id = '{sql_id}' and database_id = '{pk}'
'''
flag, sqldetail_data = run_batch_sql(database, sqldetail_sql)
if not flag:
return sqldetail_data
stat_data = sqldetail_data.get('stats')
plan_data = sqldetail_data.get('plans')
bind_data = sqldetail_data.get('binds')
sqlmon_data = execute_return_json(query_sqlmon)
exec_delta = defaultdict(list)
avg_elapse_time = defaultdict(list)
avg_cpu_time = defaultdict(list)
avg_crs = defaultdict(list)
avg_reads = defaultdict(list)
plan_dic = defaultdict(list)
stats_dict = defaultdict(dict)
for x in stat_data:
phv = str(x.get('PLAN_HASH_VALUE'))
snap_time = x.get('SNAP_TIME')
exec_delta[phv].append([snap_time, x.get('EXEC_DELTA')])
avg_elapse_time[phv].append([snap_time, x.get('AVG_ELAPSE_TIME')])
avg_cpu_time[phv].append([snap_time, x.get('AVG_CPU_TIME')])
avg_crs[phv].append([snap_time, x.get('AVG_CRS')])
avg_reads[phv].append([snap_time, x.get('AVG_READS')])
stats_dict[''] = exec_delta
stats_dict['(s)'] = avg_elapse_time
stats_dict['CPU(s)'] = avg_elapse_time
stats_dict[''] = avg_crs
stats_dict[''] = avg_reads
for x in plan_data:
phv = str(x.get('PLAN_HASH_VALUE'))
plan_dic[phv].append(x)
sql_detail['stats'] = stats_dict
sql_detail['plans']['data'] = plan_dic
sql_detail['sqlmon']['data'] = sqlmon_data
sql_detail['binds']['data'] = bind_data
if cache == True:
if audit_data != None:
if not only_tune:
sql_detail['audit'] = audit_data_json
audit_data_json = get_sql_audit(pk, sql_id, only_tune=only_tune)
sql_detail['audit'] = audit_data_json
redis.setex(key_audit, SQLTEXT_RETENTION, json.dumps(audit_data_json))
if audit_data:
new_plan_dict = {}
if plan_dic:
new_plan_dict = {k[k.rfind('-') + 1:]:v for k, v in plan_dic.items()}
tune_data = get_sql_tune(database, audit_data_json, new_plan_dict)
if tune_data:
sql_detail['tune'] = tune_data
return sql_detail
def lock_history(database):
query = get_lock_query(database)
flag, json_data = run_batch_sql(database, query)
if not flag:
print(str(build_exception_from_java(json_data)))
return
created_at = datetime.now.replace(microsecond=0)
session_list = []
if json_data.get('lock'):
session_list = get_blocking_session_detail(database)
lock_list = json_data.get('lock') if json_data.get('lock') else []
save_lock_history(database, lock_list, created_at)
transactions = json_data.get('transaction')
trans = Transaction
trans.database = database
trans.created_at = created_at
trans.transactions = transactions
trans.save
db_type = database.db_type
locks = len(json_data.get('lock')) if json_data.get('lock') else 0
warn = WARN_ENUM.get(db_type).Blocking_Session_Warn
p = Performance(inst_id=database.db_name, name=warn.name, value=locks, created_at=created_at)
customized_warn_scanner(warn, p, database, False)
if transactions:
warn = WARN_ENUM.get(db_type).Long_Transaction_Warn
p = Performance(inst_id=database.db_name, name=warn.name, created_at=created_at)
for t in transactions:
options = {'SESSION_ID':t.get('SESSION_ID'), 'MACHINE':t.get('MACHINE'),
'TRX_STARTED':t.get('TRX_STARTED')}
if t.get('INST_ID'):
p.inst_id = t.get('INST_ID')
p.value = t.get('TRX_SECONDS')
if customized_warn_scanner(warn, p, database, False, options, True):
session_list.append(t.get('SESSION_ID'))
warn = WARN_ENUM.get(db_type).Transaction_Warn
p = Performance(inst_id=database.db_name, name=warn.name, value=len(transactions), created_at=created_at)
customized_warn_scanner(warn, p, database, False, {}, True)
warn = WARN_ENUM.get(db_type).Big_Transaction_Warn
p = Performance(inst_id=database.db_name, name=warn.name, created_at=created_at)
big_transaction_key_dict = {'oracle':'USED_UBLK',
'mysql':'TRX_ROWS_MODIFIED',
'db2':'UOW_LOG_SPACE_USED',
'sqlserver':'LOG_BYTES'}
for x in transactions:
options = {'session_id':x.get('SESSION_ID'),
'start_time':x.get('TRX_STARTED')}
if t.get('INST_ID'):
p.inst_id = t.get('INST_ID')
p.value = x.get(big_transaction_key_dict.get(db_type))
if customized_warn_scanner(warn, p, database, False, options, True):
session_list.append(t.get('SESSION_ID'))
save_session_detail_list(database, list(set(session_list)))
if db_type == 'mysql':
lock_tables = json_data.get('tables')
if lock_tables:
if len(lock_tables):
warn = WARN_ENUM.get(database.db_type).Locked_Table_Warn
p = Performance(inst_id=database.db_name, name=warn.name, value=len(lock_tables), created_at=created_at)
table_list = [('{}.{}').format(x.get('Database'), x.get('Table')) for x in lock_tables]
options = {'table_list': (' ').join(table_list)}
customized_warn_scanner(warn, p, database, False, options)
def apply_sql_profile(pk, sql_id, plan_hash_value):
import re
try:
database = Database.objects.get(pk=pk)
instance_id_list = database.instance_id_list
query_json = {'SQLTEXT':f'''select * from (select REPLACE(sql_fulltext, CHR(00), ' ') SQLTEXT from gv$sql where inst_id in ({instance_id_list}) and sql_id='{sql_id}' and rownum = 1 union all
select REPLACE(SQL_TEXT, CHR(00), ' ') from DBA_HIST_SQLTEXT where sql_id='{sql_id}' and rownum = 1) where rownum=1''', 'HINT':f'''
select /*+ opt_param('parallel_execution_enabled', 'false') */ 'q''[' || SUBSTR(EXTRACTVALUE(VALUE(h), '/hint'), 1, 4000) || ']'',' HINT
from
(
select other_xml from
(
SELECT other_xml
FROM gv$sql_plan
WHERE sql_id = '{sql_id}'
and inst_id in ({instance_id_list})
AND plan_hash_value = {plan_hash_value}
AND other_xml IS NOT NULL
and rownum = 1
union all
SELECT other_xml
FROM dba_hist_sql_plan
WHERE sql_id = '{sql_id}'
AND plan_hash_value = {plan_hash_value}
AND other_xml IS NOT NULL
and rownum = 1
) where rownum = 1
) p, table(xmlsequence(extract(xmltype(p.other_xml),'/*/outline_data/hint'))) h'''}
flag, result = run_batch_sql(database, query_json)
if not flag:
raise build_exception_from_java(result)
sqltext = ''
if result.get('SQLTEXT'):
sqltext = result.get('SQLTEXT')[0].get('SQLTEXT') if result.get('SQLTEXT') else ''
sqltext = ('\n').join((line.strip() for line in re.findall('.{1,160}(?:\\s+|$)', sqltext)))
profile_name = 'coe_' + sql_id + '_' + plan_hash_value
outlines = []
outlines.append('DECLARE')
outlines.append('sql_txt CLOB;')
outlines.append('h SYS.SQLPROF_ATTR;')
outlines.append('BEGIN')
outlines.append("sql_txt := q'[")
outlines.append(sqltext)
outlines.append("]';")
outlines.append('h := SYS.SQLPROF_ATTR(')
outlines.append("q'[BEGIN_OUTLINE_DATA]',")
for hint in result.get('HINT'):
tmp = ''
if len(hint.get('HINT')) > 500:
tmp = ("]',\nq'[").join((line.strip() for line in re.findall('.{1,160}(?:\\s+|$)', hint.get('HINT'))))
else:
tmp = hint.get('HINT')
outlines.append(tmp)
outlines.append("q'[END_OUTLINE_DATA]');")
outlines.append('DBMS_SQLTUNE.IMPORT_SQL_PROFILE (')
outlines.append('sql_text => sql_txt,')
outlines.append('profile => h,')
outlines.append("name => '" + profile_name + "',")
outlines.append("description => '" + profile_name + "',")
outlines.append("category => 'DEFAULT',")
outlines.append('validate => FALSE,')
outlines.append('replace => TRUE,')
outlines.append('force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );')
outlines.append('END;')
import_sql_profile = ('\n').join(outlines)
flag, result = run_plsql(database, import_sql_profile)
if not flag:
raise build_exception_from_java(result)
return {'OK': True}
except ObjectDoesNotExist:
return {'error_message': ''}
except Exception as err:
return {'error_message': str(err)}
def db2_analysis(audit_job):
try:
schema, max_rows, order_by_pred, time_span = (audit_job.schema,
audit_job.max_rows,
audit_job.order_by,
audit_job.time_span)
if time_span:
begin_time, end_time = datetime.now(), datetime.now() - (timedelta(
hours=1))
else:
begin_time, end_time = audit_job.snapshot_begin_time, audit_job.snapshot_end_time
rule_list = []
database = audit_job.database
strategy_dict = audit_job.strategy
audit_rule_queryset = (Audit_Rule.objects.filter(
database=database)).filter(enabled=True)
for k, v in strategy_dict.items():
rule_list += list((audit_rule_queryset.filter(
audit_type=k)).filter(target__in=v))
logger.error('sql_audit_analysis begin build query')
query_total_json = build_total_query(
TotalTemplateJSON.get(database.db_type), database, schema)
query_detail_json = {}
query_problem_json = {}
object_rules_list = [
rule for rule in rule_list if rule.audit_type == ''
]
for rule in set(object_rules_list):
query_detail_json[rule.name] = build_rule_query(
DetailTemplateJson, database, rule, schema, max_rows,
order_by_pred)
query_problem_json[rule.name] = build_rule_query(
ProblemTemplateJSON, database, rule, schema, max_rows,
order_by_pred)
logger.error('sql_audit_analysis begin run total')
flag, total_result = run_batch_sql(database, query_total_json)
if not flag:
logger.error(total_result)
raise build_exception_from_java(total_result)
logger.error('sql_audit_analysis begin run detail')
flag, detail_result = run_batch_sql(database, query_detail_json)
if not flag:
logger.error(detail_result)
raise build_exception_from_java(detail_result)
logger.error('sql_audit_analysis begin run problem')
flag, problem_result = run_batch_sql(database, query_problem_json)
if not flag:
logger.error(problem_result)
raise build_exception_from_java(problem_result)
logger.error('sql_audit_analysis begin build result')
sql_list = ((((DB2_ASH.objects.filter(database=database)).filter(
created_at__range=(begin_time, end_time))).filter(
db_name__in=schema.split(','))).values(
'sql_id', 'sql_text',
'db_name').annotate(total=Count('sql_id'))
).order_by('-total')[:MAX_SQL_LIMIT]
sql_count = sql_list.count()
sql_rules_list = [
rule for rule in rule_list if rule.audit_type == 'SQL'
]
for rule in sql_rules_list:
total_result[rule.target] = [{'COUNT': sql_count}]
problem_result[rule.name] = [{'COUNT': 0}]
for x in sql_list:
sql_id = x.get('sql_id')
sql_text = x.get('sql_text')
sql_schema = x.get('db_name')
if sql_id:
if sql_text:
flag, json_data = get_sql_plan(database, sql_text,
sql_schema)
if not flag:
print(sql_text)
print(str(build_exception_from_java(json_data)))
if json_data:
try:
plan_audit_data = get_db2_single_sql_plan_audit(
database, sql_text, json_data)
text_audit_data = get_db2_single_sql_text_audit(
database, sql_text, json_data)
audit_data = {**plan_audit_data, **text_audit_data}
for k, v in plan_audit_data.items():
if not detail_result.get(k):
detail_result[k] = []
detail_result[k].append({
'SQL_ID': sql_id,
'SQL_TEXT': sql_text,
'SCHEMA': sql_schema
})
current_problem_count = problem_result[k][0].get(
'COUNT')
problem_result[k] = [{
'COUNT': current_problem_count + 1
}]
except Exception as e:
print(e)
return build_audit_result(database, rule_list, total_result,
problem_result, detail_result, audit_job)
except Exception as e:
logger.error(str(e))
def object_detail(pk,
owner,
object_name,
object_type=None,
subobject_name=None,
cache=False):
try:
schema_name = owner
key = f'''{pk}:schema:{owner}:{object_name}:{subobject_name}'''
if cache:
cache_data = redis.get(key)
if cache_data:
return json.loads(cache_data)
database = Database.objects.get(pk=pk)
db_type = database.db_type
type_map = Type_TO_CN.get(database.db_type)
schema_name = owner
db_name = None
if db_type == 'sqlserver':
db_name, owner = owner.split('.')
options = {
'OWNER': owner,
'OBJECT_NAME': object_name,
'SUBOBJECT_NAME': subobject_name
}
if not object_type:
object_type = get_object_type(database, owner, object_name,
options, db_name)
if not object_type:
raise Exception('.')
else:
object_type = type_map.get(object_type)
options['OBJECT_TYPE'] = CN_TO_Type.get(db_type).get(
object_type)
detail_query = {}
if Object_Detail_Query.get(db_type):
if Object_Detail_Query.get(db_type).get(object_type):
detail_query = Object_Detail_Query.get(db_type).get(
object_type)
ddl_query = DDL_Query.get(db_type) if DDL_Query.get(
db_type) else {}
if not subobject_name:
query = {**detail_query, **ddl_query}
else:
query = detail_query
if db_type == 'sqlserver':
if detail_query:
query.pop('DDL')
query = {k: (v.format(**options)) for k, v in query.items()}
flag, schema_data = run_batch_sql(database, query, db_name)
if not flag:
raise build_exception_from_java(schema_data)
if schema_data.get('DDL'):
if db_type != 'mysql':
schema_data['DDL'] = schema_data.get('DDL')[0].get(
'DDL') if schema_data.get('DDL') else ''
if schema_data.get('DDL'):
if db_type == 'mysql':
ddl_data = schema_data.get('DDL')[0]
schema_data['DDL'] = None
for k, v in ddl_data.items():
if 'create ' in k.lower():
schema_data['DDL'] = v
if not schema_data['DDL']:
for k, v in ddl_data.items():
if 'SQL Original Statement' in k:
schema_data['DDL'] = v
delta_list = []
total_list = []
if object_type == '':
query_delta = f'''
select extract(epoch from created_at)*1000 created_at, rows - lag(rows) over (order by created_at) as rows
from monitor_table_rows where database_id = '{pk}'
and owner = '{schema_name}' and table_name = '{object_name}'
order by created_at
'''
query_total = f'''
select extract(epoch from created_at)*1000 created_at, rows
from monitor_table_rows where database_id = '{pk}'
and owner = '{schema_name}' and table_name = '{object_name}'
order by created_at
'''
delta_list = execute_return_json(query_delta)
total_list = execute_return_json(query_total)
new_schema = OrderedDict()
for x in Ordered_List:
if x in schema_data:
new_schema[x] = schema_data.get(x)
if delta_list:
new_schema[''] = {
'delta': [[x.get('CREATED_AT'),
x.get('ROWS')] for x in delta_list
if x.get('ROWS') != None],
'total': [[x.get('CREATED_AT'),
x.get('ROWS')] for x in total_list
if x.get('ROWS') != None]
}
redis.set(key, json.dumps(new_schema))
return new_schema
except ObjectDoesNotExist:
return {'error_message': ''}
except Exception as err:
return {'error_message': str(err)}
def sqlserver_gen_sql_detail(database, sql_id, sql_text=None, schema=None, get_detail=True):
sql_detail = get_default_sql_detail_format(database.db_type)
time_str = get_10s_time_str()
plan_dic = {}
query = {'detail':f'''
select
CONVERT(VARCHAR(24), creation_time, 120) CREATION_TIME,
CONVERT(VARCHAR(24), last_execution_time, 120) LAST_EXECUTION_TIME,
EXECUTION_COUNT,
convert(bigint, total_elapsed_time/1000) TOTAL_ELAPSED_TIME,
convert(bigint, total_worker_time/1000) TOTAL_WORKER_TIME,
TOTAL_LOGICAL_READS,
TOTAL_PHYSICAL_READS,
TOTAL_LOGICAL_WRITES
--total_rows
from
sys.dm_exec_query_stats
where sql_handle = cast('' as xml).value('xs:hexBinary("{sql_id}")', 'varbinary(max)')
order by total_elapsed_time desc
''', 'plan':f'''
select
QUERY_PLAN
from
sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
where sql_handle = cast('' as xml).value('xs:hexBinary("{sql_id}")', 'varbinary(max)') '''}
if sql_text and schema:
flag, json_data = run_batch_sql(database, query)
if not flag:
print(str(build_exception_from_java(json_data)))
return sql_detail
plan_data = json_data.get('plan')
detail_data = json_data.get('detail')
plan_dic = plan_data[0].get('QUERY_PLAN') if plan_data else ''
if detail_data:
metric_dict = {'TOTAL_ELAPSED_TIME':'()', 'TOTAL_WORKER_TIME':'()',
'TOTAL_LOGICAL_READS':'',
'TOTAL_PHYSICAL_READS':'',
'TOTAL_LOGICAL_WRITES':'',
'TOTAL_ROWS':'',
'EXECUTION_COUNT':''}
for idx, x in enumerate(detail_data):
pie_chart_data = {k:v for k, v in x.items() if k in ('TOTAL_ELAPSED_TIME',
'TOTAL_WORKER_TIME')}
total_executions = x.get('EXECUTION_COUNT') if x.get('EXECUTION_COUNT') != 0 else 1
execution_data = [{u'\u6307\u6807':metric_dict.get(k), u'\u603b\u6570':v, u'\u5e73\u5747\u6bcf\u6b21\u6267\u884c':round(v / total_executions)} for k, v in x.items() if k in metric_dict]
key = f'''{time_str}-{idx}'''
sql_detail['stats'][key] = {'child_summary':[
{'CREATION_TIME':x.get('CREATION_TIME'),
'LAST_EXECUTION_TIME':x.get('LAST_EXECUTION_TIME')}],
'pie_chart_data':pie_chart_data,
'execution_stats':{'header':[
'', '', '', ''],
'data':execution_data}}
sql_detail['sql_text'] = sql_text
if plan_dic:
sql_detail['plans'] = plan_dic
detail = SQL_Detail()
detail.created_at = datetime.now().replace(microsecond=0)
detail.sql_detail = sql_detail
detail.sql_id = sql_id
detail.database = database
detail.save()
return sql_detail
def oracle_performance(database):
query = {
'stats':
"\nselect inst_id, name, value\nfrom\n (\n select ss.inst_id\n , sn.name\n , ss.value\n from v$statname sn\n , gv$sysstat ss\n where sn.statistic# = ss.statistic#\n and sn.name in (\n 'execute count', 'logons cumulative',\n 'parse count (hard)', 'parse count (total)', 'parse count (failures)',\n 'physical read total IO requests', 'physical read total bytes',\n 'physical write total IO requests', 'physical write total bytes',\n 'redo size', --'session cursor cache hits',\n 'session logical reads', 'user calls', 'user commits', 'user rollbacks','logons current',\n 'gc cr blocks received','gc current blocks received',\n 'gc cr block receive time', 'gc current block receive time')\n union all\n select inst_id\n , STAT_NAME\n , VALUE\n from gv$osstat\n where STAT_NAME in ('BUSY_TIME','IDLE_TIME')\n union all\n select\n (select min(INSTANCE_NUMBER) from gv$instance),\n 'SCN GAP Per Minute',\n current_scn\n from v$database\n )\norder by 1,2",
'wait':
"\n select inst_id, event, TIME_WAITED, TOTAL_WAITS\n from gv$system_event\n where\n event in (\n 'log file sync',\n 'log file parallel write',\n 'db file parallel write',\n 'db file sequential read',\n 'db file scattered read',\n 'direct path read',\n 'direct path read temp'\n )\n order by 1,2",
'dg':
"\n select\n INST_ID,\n NAME,\n VALUE\n from gv$dataguard_stats\n where name in ('apply lag','transport lag')"
}
stats_list = {
'session cursor cache hits': 'session cursor cache hits',
'BUSY_TIME': 'Host CPU Utilization (%)',
'physical write total IO requests':
'Physical Write IO Requests Per Sec',
'physical write total bytes': 'Physical Write Total Bytes Per Sec',
'physical read total IO requests': 'Physical Read IO Requests Per Sec',
'physical read total bytes': 'Physical Read Total Bytes Per Sec',
'SCN GAP Per Minute': 'SCN GAP Per Minute',
'execute count': 'Executions Per Sec',
'logons cumulative': 'Logons Per Sec',
'logons current': 'Session Count',
'parse count (failures)': 'Parse Failure Count Per Sec',
'parse count (hard)': 'Hard Parse Count Per Sec',
'parse count (total)': 'Total Parse Count Per Sec',
'redo size': 'Redo Generated Per Sec',
'session logical reads': 'Logical Reads Per Sec',
'user rollbacks': 'User Rollbacks Per Sec',
'user calls': 'User Calls Per Sec',
'user commits': 'User Commits Per Sec',
'gc cr blocks received': 'GC CR Block Received Per Second',
'gc current blocks received': 'GC Current Block Received Per Second',
'gc cr block receive time': 'Global Cache Average CR Get Time',
'gc current block receive time':
'Global Cache Average Current Get Time'
}
none_delta_stats = ('BUSY_TIME', 'IDLE_TIME', 'gc cr block receive time',
'gc current block receive time', 'logons current')
date_current = get_10s_time()
if not database.dg_stats:
query.pop('dg')
flag, json_data = run_batch_sql(database, query)
if not flag:
print(str(build_exception_from_java(json_data)))
return
json_data_1_current = json_data.get('stats')
json_data_2_current = json_data.get('wait')
json_data_dg = json_data.get('dg')
key1 = str(database.id) + ':performance1'
key2 = str(database.id) + ':performance2'
date_key = str(database.id) + ':performance_date'
json_data_str_1 = redis.get(key1)
json_data_str_2 = redis.get(key2)
date_prev = redis.get(date_key)
keys1 = ['NAME', 'VALUE']
keys2 = ['EVENT', 'TIME_WAITED', 'TOTAL_WAITS']
redis.setex(key1, MAX_INTERVAL, json.dumps(json_data_1_current))
redis.setex(key2, MAX_INTERVAL, json.dumps(json_data_2_current))
redis.setex(date_key, MAX_INTERVAL, str(date_current))
if json_data_str_1:
if json_data_str_2:
if date_prev:
if (date_current -
to_date(date_prev)).total_seconds() < MAX_INTERVAL:
json_data_1_prev = json.loads(json_data_str_1)
json_data_2_prev = json.loads(json_data_str_2)
for idx, obj in enumerate(json_data_1_current):
name = obj.get(keys1[0])
if name == 'IDLE_TIME':
continue
value = obj.get(keys1[1])
inst_id = obj.get('INST_ID')
p = Performance()
p.name = stats_list.get(name)
p.database = database
p.created_at = date_current
p.inst_id = inst_id
delta = float(value) - float(json_data_1_prev[idx].get(
keys1[1]))
total = 1
if name in none_delta_stats:
if name in 'BUSY_TIME':
total = delta + float(
json_data_1_current[idx + 1].get(
keys1[1])) - float(
json_data_1_prev[idx + 1].get(
keys1[1]))
value = round(
delta / (total if total != 0 else 1) * 100,
1)
else:
if name in ('gc cr block receive time',
'gc current block receive time'):
delta = 10.0 * delta
total = float(
json_data_1_current[idx + 1].get(
keys1[1])) - float(
json_data_1_prev[idx + 1].get(
keys1[1]))
value = round(
delta / (total if total != 0 else 1),
1)
p.value = value
else:
p.value = round(delta / INTERVAL, 1)
p.save()
for idx, obj in enumerate(json_data_2_current):
name = obj.get(keys2[0])
value1 = obj.get(keys2[1])
value2 = obj.get(keys2[2])
inst_id = obj.get('INST_ID')
delta = 10.0 * (float(value1) - float(
json_data_2_prev[idx].get(keys2[1])))
total = float(value2) - float(
json_data_2_prev[idx].get(keys2[2]))
value = round(delta / (total if total != 0 else 1), 1)
p = Performance()
p.name = name
p.database = database
p.created_at = date_current
p.inst_id = inst_id
p.value = value
p.save()
if json_data_dg:
for x in json_data_dg:
p = Performance()
p.name = x.get('NAME')
p.inst_id = x.get('INST_ID')
p.value = convert_oracle_interval_to_secodns(
x.get('VALUE'))
p.database = database
p.created_at = date_current
p.save()
