def sqlserver_performance(database): query = "\nselect COUNTER_NAME,CNTR_VALUE from sys.dm_os_performance_counters where\n(object_name like '%sql statistics%' and counter_name = 'batch requests/sec') or\n(object_name like '%sql statistics%' and counter_name = 'sql compilations/sec') or\n(object_name like '%sql statistics%' and counter_name = 'sql re-compilations/sec') or\n(object_name like '%buffer manager%' and counter_name = 'lazy writes/sec') or\n(object_name like '%buffer manager%' and counter_name = 'page life expectancy') or\n(object_name like '%memory manager%' and counter_name = 'connection memory (kb)') or\n(object_name like '%memory manager%' and counter_name = 'memory grants pending') or\n(object_name like '%memory manager%' and counter_name = 'sql cache memory (kb)') or\n(object_name like '%memory manager%' and counter_name = 'target server memory (kb)') or\n(object_name like '%memory manager%' and counter_name = 'total server memory (kb)') or\n(object_name like '%access methods%' and counter_name = 'full scans/sec') or\n(object_name like '%access methods%' and counter_name = 'forwarded records/sec') or\n(object_name like '%access methods%' and counter_name = 'mixed page allocations/sec') or\n(object_name like '%access methods%' and counter_name = 'page splits/sec') or\n(object_name like '%access methods%' and counter_name = 'table lock escalations/sec') or\n(object_name like '%general statistics%' and counter_name = 'logins/sec') or\n(object_name like '%general statistics%' and counter_name = 'logouts/sec') or\n(object_name like '%general statistics%' and counter_name = 'user connections') or\n(object_name like '%general statistics%' and counter_name = 'processes blocked') or\n(object_name like '%latches%' and counter_name = 'latch waits/sec') or\n(object_name like '%latches%' and counter_name = 'average latch wait time (ms)') or\n(object_name like '%access methods%' and counter_name = 'workfiles created/sec') or\n(object_name like '%access methods%' and counter_name = 'worktables created/sec') or\n(object_name like '%general statistics%' and counter_name = 'active temp tables') or\n(object_name like '%general statistics%' and counter_name = 'temp tables creation rate') or\n(object_name like '%general statistics%' and counter_name = 'temp tables for destruction') or\n(object_name like '%databases%' and counter_name ='active transactions' and instance_name = '_Total') or\n(object_name like '%databases%' and counter_name ='Transactions/sec' and instance_name = '_Total') or\n(object_name like '%databases%' and counter_name ='log flushes/sec' and instance_name = '_Total') or\n(object_name like '%databases%' and counter_name ='cache hit ratio' and instance_name = '_Total') or\n(object_name like '%SQLServer:Locks%' and counter_name like '%Lock%' and instance_name = '_Total')" match_patern = re.compile('/sec', re.IGNORECASE) 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) keys = ['COUNTER_NAME', 'CNTR_VALUE'] redis.set(key, json.dumps(json_data_current)) redis.set(date_key, 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 re.search(match_patern, name): p.value = round( (float(value) - float(json_data_prev[idx].get(keys[1]))) / INTERVAL, 1) else: p.value = float(value) p.save()
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 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 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 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 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 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