def db2_performance(database):
query = '\n select\n TOTAL_CONS, APPLS_CUR_CONS, APPLS_IN_DB2, LOCKS_WAITING, NUM_ASSOC_AGENTS, ACTIVE_SORTS,\n LOCKS_HELD, LOCK_WAITS,\n TOTAL_SORTS, SORT_OVERFLOWS,\n POOL_DATA_L_READS, POOL_TEMP_DATA_L_READS, POOL_INDEX_L_READS, POOL_TEMP_INDEX_L_READS, POOL_XDA_L_READS, POOL_TEMP_XDA_L_READS, POOL_DATA_P_READS, POOL_TEMP_DATA_P_READS,\n POOL_INDEX_P_READS, POOL_TEMP_INDEX_P_READS, POOL_XDA_P_READS, POOL_TEMP_XDA_P_READS,\n POOL_DATA_WRITES, POOL_INDEX_WRITES, POOL_XDA_WRITES,\n DIRECT_READS, DIRECT_WRITES,\n COMMIT_SQL_STMTS, ROLLBACK_SQL_STMTS, DYNAMIC_SQL_STMTS, STATIC_SQL_STMTS, FAILED_SQL_STMTS, SELECT_SQL_STMTS, UID_SQL_STMTS, DDL_SQL_STMTS,\n ROWS_DELETED, ROWS_INSERTED, ROWS_UPDATED, ROWS_SELECTED, ROWS_READ,\n LOG_READS, LOG_WRITES\n from sysibmadm.snapdb'
stats_list_realtime = [
'APPLS_CUR_CONS', 'LOCKS_HELD', 'APPLS_IN_DB2', 'LOCKS_WAITING',
'NUM_ASSOC_AGENTS', 'ACTIVE_SORTS'
]
stats_list_delta = [
'TOTAL_CONS', 'LOCK_WAITS', 'TOTAL_SORTS', 'SORT_OVERFLOWS',
'POOL_DATA_L_READS', 'POOL_TEMP_DATA_L_READS', 'POOL_INDEX_L_READS',
'POOL_TEMP_INDEX_L_READS', 'POOL_XDA_L_READS', 'POOL_TEMP_XDA_L_READS',
'POOL_DATA_P_READS', 'POOL_TEMP_DATA_P_READS', 'POOL_INDEX_P_READS',
'POOL_TEMP_INDEX_P_READS', 'POOL_XDA_P_READS', 'POOL_TEMP_XDA_P_READS',
'POOL_DATA_WRITES', 'POOL_INDEX_WRITES', 'POOL_XDA_WRITES',
'DIRECT_READS', 'DIRECT_WRITES', 'COMMIT_SQL_STMTS',
'ROLLBACK_SQL_STMTS', 'DYNAMIC_SQL_STMTS', 'STATIC_SQL_STMTS',
'FAILED_SQL_STMTS', 'SELECT_SQL_STMTS', 'UID_SQL_STMTS',
'DDL_SQL_STMTS', 'ROWS_DELETED', 'ROWS_INSERTED', 'ROWS_UPDATED',
'ROWS_SELECTED', 'ROWS_READ', 'LOG_READS', 'LOG_WRITES'
]
date_current = get_10s_time()
flag, json_data_current = run_sql(database, query)
if not flag or not json_data_current:
print(str(build_exception_from_java(json_data_current)))
return
json_data_current = json_data_current[0]
key = str(database.id) + ':performance'
date_key = str(database.id) + ':performance_date'
json_data_str_prev = redis.get(key)
date_prev = redis.get(date_key)
redis.setex(key, MAX_INTERVAL, json.dumps(json_data_current))
redis.setex(date_key, MAX_INTERVAL, str(date_current))
if json_data_str_prev:
if date_prev:
if (date_current -
to_date(date_prev)).total_seconds() < MAX_INTERVAL:
json_data_prev = json.loads(json_data_str_prev)
for key, value in json_data_current.items():
p = Performance()
p.name = key
p.created_at = date_current
p.database = database
if key in stats_list_realtime:
p.value = value
else:
if key in stats_list_delta:
p.value = (float(value) - float(
json_data_prev.get(key))) / INTERVAL
p.save()
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 mysql_performance(database):
query = "show global status where VARIABLE_NAME in (\n 'Queries', 'Questions','Com_delete','Com_insert','Com_select','Com_update',\n 'Bytes_received','Bytes_sent',\n 'Thread_connected','Connections',\n 'Select_full_join', 'Select_full_range_join', 'Select_range', 'Select_range_check', 'Select_scan',\n 'Sort_merge_passes','Sort_scan','Sort_range','Sort_rows',\n 'Created_tmp_disk_tables','Created_tmp_files','Created_tmp_tables',\n 'Innodb_data_writes','Innodb_log_writes','Innodb_os_log_written',\n 'Innodb_rows_read','Innodb_rows_inserted','Innodb_rows_updated','Innodb_rows_deleted',\n 'Innodb_data_reads','Innodb_buffer_pool_read_requests'\n )"
none_delta_stats = 'Thread_connected'
date_current = get_10s_time()
flag, json_data_current = run_sql(database, query)
if not flag:
print(str(build_exception_from_java(json_data_current)))
return
key = str(database.id) + ':performance'
date_key = str(database.id) + ':performance_date'
json_data_str_prev = redis.get(key)
date_prev = redis.get(date_key)
k1, k2 = json_data_current[0].keys()
if re.search('name', k1, re.IGNORECASE):
keys = [k1, k2]
else:
keys = [k2, k1]
redis.setex(key, MAX_INTERVAL, json.dumps(json_data_current))
redis.setex(date_key, MAX_INTERVAL, str(date_current))
if json_data_str_prev and date_prev and (
date_current - to_date(date_prev)).total_seconds() < MAX_INTERVAL:
json_data_prev = json.loads(json_data_str_prev)
for idx, obj in enumerate(json_data_current):
name = obj.get(keys[0])
value = obj.get(keys[1])
p = Performance()
p.name = name
p.database = database
p.created_at = date_current
if name in none_delta_stats:
if not value:
print('value is None' + keys[1])
p.value = float(value)
else:
p.value = round(
(float(value) - float(json_data_prev[idx].get(keys[1]))) /
INTERVAL, 1)
p.save()
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()
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