Администратору в копилку. Немного запросов для настройки sql-предложений

dbstalker, 21 октября

Часто администратору приходиться заниматься тюнингом запросов. Надеюсь, что эти запросы помогут в этом безнадежном занятии.


--Сессии, интенсивно использующие ресурсы процессора
select v.sid, v.value,USERNAME 
from v$statname s, v$sesstat v,V$SESSION S 
where s.name = 'CPU used by this session' 
and v.statistic# = s.statistic# 
and v.value > 0 AND V.SID=S.SID 
order by 2 desc;
--статистика по запросам. ограничивайте по нужной вам статистике

SELECT  username, disk_reads_per_exec, buffer_gets, disk_reads,
parse_calls, sorts, executions, rows_processed, hit_ratio, first_load_time,
sharable_mem, persistent_mem, runtime_mem, cpu_time, elapsed_time, address,
hash_value,sql_text
FROM (SELECT sql_text, b.username, ROUND(( a.disk_reads / DECODE(a.executions, 0, 1, a.executions)), 2) disk_reads_per_exec,
 a.disk_reads, a.buffer_gets, a.parse_calls,
a.sorts, a.executions, a.rows_processed, 100 - ROUND(100 * a.disk_reads / GREATEST(a.buffer_gets, 1), 2) hit_ratio,
a.first_load_time, sharable_mem, persistent_mem, runtime_mem,
cpu_time, elapsed_time, address, hash_value
FROM sys.v_$sqlarea a, sys.all_users b
WHERE a.parsing_user_id = b.user_id
AND b.username ='ANALIZ')
order by  first_load_time desc
 
-- обратите внимание на запросы, у которых наибольшее отношением gets/exec или значением buffer_gets
--address, hash_value -однозначно идентифицируют запрос, находящийся в памяти 
select address, hash_value, buffer_gets, executions, 
TRUNC(buffer_gets/executions) "gets/exec", sql_text 
from v$sqlarea 
where executions > 0 
order by 5 desc;

-- По v$sqlarea анализируем сколько строк выбрал основной запрос (ROWS_PROCESSED) или сколько раз срабатывал дочерний запрос (EXECUTIONS).
-- статистика по загрузке сервера запросами
select 
sql_text,
sum(executions)/greatest((sysdate-min(to_date(last_load_time,'YYYY-MM-DD/HH24:MI:SS'))),1) executions,
sum(rows_processed)/greatest((sysdate-min(to_date(last_load_time,'YYYY-MM-DD/HH24:MI:SS'))),1) rows_day, 
round(sum(disk_reads)/sum(executions)) disk,
round(sum(buffer_gets)/sum(executions)) buffer,
round((sum(disk_reads)+sum(buffer_gets))/sum(executions)) total_reads,
round(sum(rows_processed)/sum(executions)) rows_per_execution, 
round(sum(cpu_time)/sum(executions)/1000000,2) cpu, 
round(sum(elapsed_time)/sum(executions)/1000000,2) ela,
round((sum(disk_reads)+sum(buffer_gets))/greatest((sysdate-min(to_date(last_load_time,'YYYY-MM-DD/HH24:MI:SS'))),1)) total_reads_day,
round(sum(elapsed_time)/greatest(sysdate-min(to_date(last_load_time,'YYYY-MM-DD/HH24:MI:SS')),1)/1000000,2) ela_day, 
min(to_date(first_load_time,'YYYY-MM-DD/HH24:MI:SS')) first_load_time,
max(last_load_time) last_load_time, 
hash_value,
max(address),
round((sum(disk_reads)+sum(buffer_gets))/greatest((sysdate-min(to_date(last_load_time,'YYYY-MM-DD/HH24:MI:SS'))),1)/sum((sum(disk_reads)+sum(buffer_gets))/greatest((sysdate-min(to_date(last_load_time,'YYYY-MM-DD/HH24:MI:SS'))),1)) over (partition by 1)) reads_day_perc,
round(sum(elapsed_time)/greatest(sysdate-min(to_date(last_load_time,'YYYY-MM-DD/HH24:MI:SS')),1)/1000000/sum(sum(elapsed_time)/greatest(sysdate-min(to_date(last_load_time,'YYYY-MM-DD/HH24:MI:SS')),1)/1000000) over (partition by 1)) ela_day_perc 
from 
(select min(sql_text) sql_text,
sum(executions) executions, 
sum(disk_reads) disk_reads,
sum(buffer_gets) buffer_gets,
sum(cpu_time) cpu_time, 
sum(elapsed_time) elapsed_time,
sum(rows_processed) rows_processed,
first_load_time,
max(last_load_time) last_load_time, 
hash_value,
address 
from v$sql
group by hash_value,address,first_load_time
)
where executions>0 and buffer_gets>=0 and disk_reads>=0
group by sql_text,hash_value
order by 9 desc

--сколько времени запрос выполнелся в среднем за запуск, за период присутствия в shared pool, для конкретного childa-a.
select 
decode(t.EXECUTIONS,0,0,t.ELAPSED_TIME/(nvl(t.EXECUTIONS,1))) time_p_e /*elapsed time per execution*/,
decode(t.EXECUTIONS,0,0, t.DISK_READS/(nvl(t.EXECUTIONS,1)) ) reads_p_e /*physical reads per execution*/,
 t.EXECUTIONS,
decode(t.EXECUTIONS,0,0, t.BUFFER_GETS/(nvl(t.EXECUTIONS,1))) cr_p_e /*buffer gets per execution*/
, decode(t.EXECUTIONS,0,0,t.ROWS_PROCESSED/(nvl(t.EXECUTIONS,1))) r_p_e /*rows processed per execution*/
from v$sql t where t.HASH_VALUE = [конкретное_значение_hash_value]
                 and child_number = [конкретное_значение_child_number];
                 
--тяжелые запросы

select SQL_TEXT,ADDRESS,FETCHES,EXECUTIONS,USERS_EXECUTING,PARSE_CALLS,TRUNC(DISK_READS/EXECUTIONS,3) DISK_READS_EXEC,
ROWS_PROCESSED,trunc(CPU_TIME/1000000/EXECUTIONS,5) CPU_TIME_EXEC,trunc(ELAPSED_TIME/1000000/EXECUTIONS,5) 
ELAPS_TIME_EXEC,CHILD_LATCH from v$sqlarea where ELAPSED_TIME/1000000/EXECUTIONS>0.1 AND EXECUTIONS>100

-- Основные статистические данные по запросам, много читающим с диска  
SELECT sqL_text, executions, buffer_gets, disk_reads, rows_processed 
from v$sql
order by disk_reads desc;

--Запросы, использующие избыточное логическое чтение. (аналогичный запрос смотри выше) см пост

SELECT * FROM
(SELECT substr(sql_text,1,200) sql,
buffer_gets, executions, buffer_gets/executions "Gets/Exec",
hash_value,address
FROM V$SQLAREA
WHERE buffer_gets > 10000
ORDER BY buffer_gets DESC)
WHERE rownum <= 10;
--или вот так:
select    s.BUFFER_GETS,    s.DISK_READS,    s.ROWS_PROCESSED,    s.EXECUTIONS,    substr(u.NAME,1,10) Username,    s.SQL_TEXT
from   v$sqlarea s, sys.user$ u
where  s.buffer_gets > 1111111 and   s.parsing_user_id = u.user#
order by s.buffer_gets desc

--запросы, требующее много физического чтения (аналогичный смотри выше)  

SELECT * FROM
(SELECT substr(sql_text,1,200) sql,
disk_reads, executions, disk_reads/executions "Reads/Exec",
hash_value,address
FROM V$SQLAREA
WHERE disk_reads > 1000
ORDER BY disk_reads DESC)
WHERE rownum <= 10;

--Top 10 by Executions:

SELECT * FROM
(SELECT substr(sql_text,1,200) sql,
executions, rows_processed, rows_processed/executions "Rows/Exec",
hash_value,address
FROM V$SQLAREA
WHERE executions > 100
ORDER BY executions DESC)
WHERE rownum <= 10;

--SQL-предложения, подвергающиеся наиболее частым разборам
SELECT * FROM
(SELECT substr(sql_text,1,200) sql,
parse_calls, executions, hash_value,address
FROM V$SQLAREA
WHERE parse_calls > 1000
ORDER BY parse_calls DESC)
WHERE rownum <= 10;

--запросы, использующие наибольший объем разделяемой памяти

SELECT * FROM 
(SELECT substr(sql_text,1,200) sql,
sharable_mem, executions, hash_value,address
FROM V$SQLAREA
WHERE sharable_mem > [необходимый объем]
ORDER BY sharable_mem DESC)
WHERE rownum <= 10;

--запросы , имеющие наибольшее число подчиненных открытых курсоров
-- свидетельство того, что курсоры не разделяются между сессиями - большие и постоянно растущие значения поля VERSION_COUNT

SELECT * FROM 
(SELECT substr(sql_text,1,200) sql,
version_count, executions, hash_value,address
FROM V$SQLAREA
WHERE version_count > 10
ORDER BY version_count DESC)
WHERE rownum <= 10;

--плюс еще вот такие запросы
--в таблице планов full table scans
select p.object_name ,sql_text from v$sqlarea t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation='TABLE ACCESS'
and p.options='FULL'
order by p.hash_value

--full scans  для индексов
select sql_text from v$sqlarea t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation='INDEX'
and p.options='FULL SCAN'
order by p.hash_value

--  full table scans по сессиям
SELECT s.sid,s.machine, total_waits, time_waited
FROM v$session_event e, v$session s
WHERE s.event='db file scattered read'
and total_waits>0
and s.sid=e.sid
ORDER BY 3,2 

 

Новый комментарий

Я не спамер: введите суму 0+0



 

От авторов блога

О Блоге - прочитай перед началом.

Задать вопрос и получить ответ - уже решено 94 вопросов

Глоссарий - список терминов и сокращений


 
 

Бизнес форум

Последние темы:

Досуг для взрослых
19 червня, 1 ответа
авто
19 червня, 1 ответа
Отдых
18 червня, 2 ответа