Olá pessoal,
No post de hoje vou escrever sobre um tema que é muito comum os alunos dos treinamentos de SQL Tuning questionarem: como descobrir quais SQL estão sendo executados e quais devem ser otimizados? Descobrir quais instruções SQL estão sendo executadas em um banco de dados Oracle é uma tarefa relativamente simples, sem grande complexidade. No entanto, avaliar quais delas devem ser otimizadas exige mais conhecimento e experiência, e é isso que vou abordar de forma resumida neste post.
É possível utilizar diversas ferramentas ou utilitários para descobrir quais SQLs estão sendo executados, tais como: oratop, EM (Enterprise Manager), ASH (Active Session History) etc. A minha sugestão neste post é consultar os dados direto na fonte, ou seja, consultar algumas visões de performance dinâmicas (VPDs) que contém informações detalhadas sobre os SQLs, sem necessitar instalar qualquer ferramenta/utilitário e sem necessitar de licencimento adicional (como no caso do ASH, que precisa de licença da option "Diagnostic Pack").
Veja no SQL abaixo que estamos consultando diversas VPDs, tais como: V$SESSION, V$PROCESS, V$SQL, V$SESS_IO, V$SQL_BIND_CAPTURE e V$SQL_SHARED_CURSOR:
-- Retorna dados das sessoes ativas e seus respectivos SQLs, junto com o hash_value e cursor_id do plano de execucao sendo utilizado
SELECT /*+ ALL_ROWS */
p.spid, -- id do processo no SO
a.sid,
a.serial#,
a.program,
a.username,
B.SQL_ID, b.child_number,
b.optimizer_cost,
b.SQl_PLAN_BASELINE,
a.status,
to_char(a.logon_time, 'DD-MON-YYYY HH24:MI:SS') as logon_time,
to_char(sysdate - last_call_et / 86400,'DD-MON-YYYY HH24:MI:SS') as last_activity,
a.osuser,
a.machine,
round( b.cpu_time/(1000000), 2) as cpu_time,
b.executions,
round( b.elapsed_time/(1000000), 2) as elapsed_time,
round( case
when b.executions > 0 then (b.elapsed_time/(1000000)) / b.executions
else 0
end, 2) as elapsed_time_per_exec,
b.parse_calls,
b.loads, -- num. de X q o objeto foi lido ou relido
b.rows_processed,
b.open_versions, -- num. de cursores abertos
b.users_opening, -- num. de usuarios com cursores abertos
b.buffer_gets,
b.disk_reads,
round( b.application_wait_time/(1000000), 2) as application_wait_time_sec,
round( b.concurrency_wait_time/(1000000), 2 ) as concurrency_wait_time_sec,
round( b.user_io_wait_time/(1000000), 2) as user_io_wait_time_sec, b.OPTIMIZER_MODE,
(b.sharable_mem + b.persistent_mem + b.runtime_mem) /1024/1024 "used_memory (mb)",
b.first_load_time,
B.PLAN_HASH_VALUE,
--SP.COST AS PLAN_SQL_TOTAL_COST,
--SP.CHILD_NUMBER AS PLAN_CHILD_NUMBER,
sc.SQL_TYPE_MISMATCH,
sc.OPTIMIZER_MISMATCH,
sc.STATS_ROW_MISMATCH,
sc.LITERAL_MISMATCH,
sc.FORCE_HARD_PARSE,
sc.TYPECHECK_MISMATCH,
sc.AUTH_CHECK_MISMATCH,
sc.BIND_MISMATCH,
sc.DESCRIBE_MISMATCH,
sc.BIND_EQUIV_FAILURE,
SC.ROLL_INVALID_MISMATCH,
SC.LOAD_OPTIMIZER_STATS,
SC.USE_FEEDBACK_STATS,
sc.PURGED_CURSOR,
dbms_lob.substr(sc.REASON, 4000, 1) as reason,
B.IS_BIND_SENSITIVE,
B.IS_BIND_AWARE,
b.SQL_TEXT,
listagg(bc.value_string, ', ' ) within group (order by BC.POSITION) AS SQL_BIND_VALUES,
a.event, A.SECONDS_IN_WAIT
FROM V$SESSION A
LEFT JOIN V$PROCESS P
ON a.PADDR = P.ADDR
left join v$sql b
on a.sql_address = b.address
and a.sql_hash_value = b.hash_value
and a.sql_child_number = b.child_number
--LEFT JOIN V$SQL_PLAN sp
--on sp.SQL_ID = b.sql_id
--and sp.PLAN_HASH_VALUE = b.PLAN_HASH_VALUE
--and sp.id = 0
LEFT join v$sess_io c
ON A.SID = C.SID
LEFT JOIN v$sql_bind_capture bC
ON b.address = bc.address
AND b.hash_value = bc.hash_value
left join V$SQL_SHARED_CURSOR sc
on sc.SQL_ID = a.SQL_ID
and sc.child_number = b.child_number
WHERE a.status = 'ACTIVE'
and a.username is not null
GROUP BY p.spid,
a.sid,
a.serial#,
a.program,
a.username,
a.status,
a.osuser,
a.machine,
to_char(a.logon_time, 'DD-MON-YYYY HH24:MI:SS'),
to_char(sysdate - last_call_et / 86400,'DD-MON-YYYY HH24:MI:SS'),
b.executions,
b.loads,
b.rows_processed,
b.open_versions,
b.users_opening,
b.first_load_time,
b.parse_calls,
b.disk_reads,
b.buffer_gets,
b.application_wait_time/(1000000),
b.concurrency_wait_time/(1000000),
b.user_io_wait_time/(1000000),
b.optimizer_cost,
b.SQl_PLAN_BASELINE,
b.OPTIMIZER_MODE,
b.cpu_time/(1000000),
b.elapsed_time/(1000000),
case
when b.executions > 0 then (b.elapsed_time/(1000000)) / b.executions
else 0
end,
(b.sharable_mem + b.persistent_mem + b.runtime_mem) /1024/1024,
B.SQL_ID, b.child_number,
B.PLAN_HASH_VALUE,
--SP.COST,
--SP.CHILD_NUMBER,
sc.SQL_TYPE_MISMATCH,
sc.OPTIMIZER_MISMATCH,
sc.STATS_ROW_MISMATCH,
sc.LITERAL_MISMATCH,
sc.FORCE_HARD_PARSE,
sc.TYPECHECK_MISMATCH,
sc.AUTH_CHECK_MISMATCH,
sc.BIND_MISMATCH,
sc.DESCRIBE_MISMATCH,
sc.BIND_EQUIV_FAILURE,
SC.ROLL_INVALID_MISMATCH,
SC.LOAD_OPTIMIZER_STATS,
SC.USE_FEEDBACK_STATS,
sc.PURGED_CURSOR,
dbms_lob.substr(sc.REASON, 4000, 1),
B.IS_BIND_SENSITIVE,
B.IS_BIND_AWARE,
b.SQL_TEXT,
a.event,
A.SECONDS_IN_WAIT
order by a.sid, B.SQL_ID, b.child_number;
Para ver o SQL acima e mais outros SQLs similares que podem te ajudar a investigar a performance de SQLs, baixe o script DESSE LINK.
Para aprender mais assista ao vídeo abaixo:
0 comments:
Postar um comentário