在ORACLE數(shù)據(jù)庫應(yīng)用調(diào)優(yōu)中，一個(gè)SQL的執(zhí)行次數(shù)/頻率也是常常需要關(guān)注的，因?yàn)槟硞€(gè)SQL執(zhí)行太頻繁，要么是由于應(yīng)用設(shè)計(jì)有缺陷，需要在業(yè)務(wù)邏輯上做出優(yōu)化處理，要么是業(yè)務(wù)特殊性所導(dǎo)致。如果執(zhí)行頻繁的SQL，往往容易遭遇一些并發(fā)性的問題。 那么如何查看ORACLE數(shù)據(jù)庫某個(gè)SQL的執(zhí)行頻率/次數(shù)呢？ 下面來看看完整的示例代碼。
select *
from (select sa.SQL_TEXT,
sa.SQL_FULLTEXT,
sa.EXECUTIONS "執(zhí)行次數(shù)",
round(sa.ELAPSED_TIME / 1000000, 2) "總執(zhí)行時(shí)間",
round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均執(zhí)行時(shí)間",
sa.COMMAND_TYPE,
sa.PARSING_USER_ID "用戶ID",
u.username "用戶名",
sa.HASH_VALUE
from v$sqlarea sa
left join all_users u
on sa.PARSING_USER_ID = u.user_id
where sa.EXECUTIONS > 0
order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
where rownum = 50;
select *
from (select s.SQL_TEXT,
s.EXECUTIONS "執(zhí)行次數(shù)",
s.PARSING_USER_ID "用戶名",
rank() over(order by EXECUTIONS desc) EXEC_RANK
from v$sql s
left join all_users u
on u.USER_ID = s.PARSING_USER_ID) t
where exec_rank = 100;
