def wait_for_execution(t):
"""Note that a query started manually from IDE will stay "executing" until you fetch all it's rows.""" \
""" In such case "Wait for session" can be helpful."""
if not t.data:
r = execute(t.target, "select min(sql_exec_start) from v$sql_monitor"
" where sql_id = :sql_id and status = 'EXECUTING'"
, t.parameters
, 'one'
, False)
if not r[0]:
return t.abort(f"SQL {t.parameters['sql_id']} Not found")
else:
t.data = r[0]
return
r = execute(t.target
, "select nvl(sum(case when status = 'EXECUTING' then 1 else 0 end), 0) e"
", nvl(sum(case when status like 'DONE%' then 1 else 0 end), 0) d"
", max(status) s"
" from v$sql_monitor where sql_id = :sql_id and sql_exec_start >= :start_time"
, {'sql_id': t.parameters['sql_id'], 'start_time': t.data}
, 'one'
, False)
if r[0] + r[1] == 0:
return t.abort(f"SQL {t.parameters['sql_id']} Not found")
if r[0] > 0:
return
if t.reply_to_message_id:
return t.finish(r[2].lower())
return t.finish('{} on {} is {}.'.format(t_link(f"{t.target}/Q/{t.parameters['sql_id']}", t.parameters['sql_id'])
, t.target
, r[2].lower()))
def wait_for_sql_error(t):
"""This task shows sql errors, stored in sql_monitor cache. Errors, displaced from the cache, will be lost.""" \
""" A good approach is creating a trigger "after servererror"."""
if not t.data:
t.data = {'start_date': t.create_date}
end_date = datetime.now()
r = execute(
t.target,
"select username, sql_id, sid, error_message"
" from v$sql_monitor"
" where status = 'DONE (ERROR)'"
" and error_number not in (1013, 28, 604, 24381)" # cancel, kill, recursive, DML array
" and last_refresh_time between :start_date and :end_date"
" and (username not like :user_name or username is null)",
{
'start_date': t.data['start_date'],
'end_date': end_date,
'user_name': t.optional.get('ignore_user', '---')
},
'many',
False)
t.data['start_date'] = end_date
return t.get_message(
r, lambda o, i: '{} ({}, {}) on {} is failed ({}).'.format(
t_link(f'{o.target}/Q/{i[1]}', i[1]), i[2], i[0], o.target, i[3].
replace('\n', ' ')))
def wait_for_heavy(t):
r = execute(
t.target,
"select username, sql_id, exec_time_min, temp_usage_gb, exec_id, sid from"
" (select s.username, m.sql_id, to_char(round(elapsed_time / 60000000)) exec_time_min, s.sid,"
" m.sql_id || to_char(m.sql_exec_id) || to_char(m.sql_exec_start, 'yyyymmddhh24miss') exec_id,"
" rtrim(to_char(((nvl(sum(u.blocks), 0) * min(p.value)) / 1024 / 1024 / 1024), 'fm999990d99')"
", to_char(0,'fmd')) temp_usage_gb"
" from v$session s"
" left join v$sort_usage u on s.saddr = u.session_addr"
" join v$parameter p on p.name = 'db_block_size'"
" join v$sql_monitor m on m.sid = s.sid and m.session_serial# = s.serial#"
" where m.status = 'EXECUTING'{}{}"
" group by s.username, m.sql_id, round(elapsed_time / 60000000), s.sid,"
" m.sql_id || to_char(m.sql_exec_id) || to_char(m.sql_exec_start, 'yyyymmddhh24miss'))"
" where exec_time_min >= :exec_time_min or temp_usage_gb >= :temp_usage_gb"
.format(
' and s.username like :user_name' if t.optional.get(
'user_name', None) else '',
' and s.username not like :ignore_user' if t.optional.get(
'ignore_user', None) else ''), {
**t.parameters,
**t.optional
}, 'many', False)
return t.get_message(
r, lambda o, i:
'{} ({}, {}) on {} is executing {} minutes and consumes {} Gb of temp space.'
.format(t_link(f'{o.target}/Q/{i[1]}', i[1]), i[5], i[0], o.target, i[
2], i[3]), None, 4)
def check_session_stats(t):
"""Please see "Activity -> Session monitor -> Session -> Session stats" to find all available statistic names."""
r = execute(
t.target,
"select s.sid, n.name, s.value from v$sesstat s join v$statname n on s.statistic# = n.statistic#"
" where n.name = :statistic_name and s.value >= :value order by s.value desc",
{**t.parameters}, 'many', False)
return t.get_message(
r, lambda o, i: 'Session {} on {} has {} = {}.'.format(
t_link(f'{o.target}/S/{str(i[0])}', str(i[0])), o.target, i[1],
get_num_str(i[2])), None, 0)
def check_frequent_sql(t):
"""This task based on v$sqlarea which accumulate statistics since SQL statement had been cached."""
r = execute(
t.target, "select sql_id, parsing_schema_name, executions,"
" to_char(to_date(first_load_time, 'yyyy-mm-dd/hh24:mi:ss'), 'dd.mm hh24:mi')"
" from v$sqlarea where parsing_schema_name not like '%SYS%'"
" and executions > :executions order by executions desc",
{**t.parameters}, 'many', False)
return t.get_message(
r, lambda o, i: '{} ({}) executed {} times since {}.'.format(
t_link(f'{o.target}/Q/{i[0]}', i[0]), i[1], get_num_str(i[2]), i[3]
), lambda o: f'Frequent executions on {o.target}', 0)
def wait_for_zombie(t):
"""User sessions could stay active and being waiting for an event that never comes.""" \
""" They must be killed to free locked resources."""
r = execute(t.target
, "select sid, username from v$session where type = 'USER' and ("
"(sysdate - last_call_et / 86400 < sysdate - :last_call_minutes * 1 / 24 / 60 and status = 'ACTIVE')"
" or (event = 'SQL*Net break/reset to client' and status = 'INACTIVE'))"
, {**t.parameters}
, 'many'
, False)
return t.get_message(r
, lambda o, i: 'Session {} ({}) on {} seems to be a zombie.'
.format(t_link(f'{o.target}/S/{str(i[0])}', str(i[0])), i[1], t.target), None, 0)
def wait_for_queued(t):
pt = t.period[-1:]
pv = t.period[:-1]
t.parameters['start_date'] = datetime.now() - get_offset(pv, pt)
r = execute(t.target
, "select nvl(sql_id, 'Unknown sql') || ' ' || event || ' ' || to_char(session_id), "
" nvl(sql_id, 'Unknown sql'), event, session_id, machine, count(1) waits"
" from v$active_session_history"
" where event like 'enq:%' and sample_time > :start_date"
" and event not like :ignore_event"
" group by nvl(sql_id, 'Unknown sql') || ' ' || event || ' ' || to_char(session_id),"
" sql_id, event, session_id, machine"
" having count(1) > :queued_time_sec"
, {'start_date': t.parameters['start_date']
, 'queued_time_sec': t.parameters['queued_time_sec']
, 'ignore_event': t.optional.get('ignore_event', '---')}
, 'many'
, False)
return t.get_message(r, lambda o, i: '{} ({}, {}) on {} has been queued for {} seconds ({}).'
.format(t_link(f'{t.target}/Q/{i[1]}', i[1]), i[4], i[3], t.target, i[5], i[2]), None, 0)
def run(self):
while self.active:
try:
updates = loads(
get_updates(self.offset).read().decode('utf-8'))
if not updates.get('ok', False):
app.logger.error(
updates.get('description',
'Unknown bot error (getUpdates)'))
break
for update in updates['result']:
self.offset = update['update_id'] + 1
if not update.get('message'):
continue
if not update['message'].get('text'):
continue
if update['message']['text'] == '/id':
send_message({
'chat_id':
update['message']['chat']['id'],
'text':
f'id = {update["message"]["chat"]["id"]}.'
})
continue
user_list = tuple(
k for k, v in app.config['USERS'].items()
if v[1] and v[1] == update['message']['from']['id'])
user_name = None
if len(user_list) == 1:
user_name = user_list[0]
if not user_name:
if update['message']['chat']['id'] in app.config[
'BOT_CHAT_LIST'].keys():
send_message({
'chat_id':
update['message']['chat']['id'],
'text':
f'I don\'t know who you are.'
f' Please open @{app.config["BOT_NAME"]} and send me /id command.'
f' Then forward my reply to system administrator.'
})
continue
if update['message']['text'] == '/help':
send_message({
'chat_id': update['message']['chat']['id'],
'text': 'God help you.'
})
continue
rr, endpoint, target, parameters = parse_command(
update['message']['text'])
if rr:
send_message({
'chat_id': update['message']['chat']['id'],
'text': f'Incorrect value: {rr}.'
})
continue
if target and target not in app.config['USERS'][user_name][
2]:
send_message({
'chat_id':
update['message']['chat']['id'],
'text':
f'Target "{target}" does not exist or not allowed.'
})
continue
task = Task(endpoint=endpoint,
user_name=user_name,
target=target,
parameters=parameters,
chat_id=str(update['message']['chat']['id']),
reply_to_message_id=str(
update['message']['message_id']))
task_pool[task.uuid] = task
send_message({
'chat_id':
update['message']['chat']['id'],
'text':
'{} added.'.format(
t_link(f'adm?task_id={task.uuid}', 'Task')),
'parse_mode':
'HTML'
})
except Exception as e:
app.logger.error(e, exc_info=True)
break
