2014-12-25 Created By BaoXinjian
一、摘要
在ORACLE数据库应用调优中,一个SQL的执行次数/频率也是常常需要关注的,因为某个SQL执行太频繁,要么是由于应用设计有缺陷,需要在业务逻辑上做出优化处理,要么是业务特殊性所导致。
如果执行频繁的SQL,往往容易遭遇一些并发性的问题。
那么如何查看ORACLE数据库某个SQL的执行频率/次数,潇湘隐者同学整理如下,借花献佛了 :)
方法1: 通过查询VSQLAREA或VSQL的EXECUTIONS来查看SQL的执行次数;
方法2:通过DBA_HIST_SQLSTAT关联DBA_HIST_SNAPSHOT找出某些SQL的执行次数;
方法3:AWR报告查看某个SQL的执行次数;
二、三种方法解析
1. 通过查询VSQLAREA或VSQL的EXECUTIONS来查看SQL的执行次数
(1). 缺点
但是这个值的有效性需要结合FIRST_LOAD_TIME来判断,因为VSQLAREA或VSQL中不保存历史数据,
具有一定的时效性,所以如果要查询很久以前的某个SQL执行次数是办不到的。
(2). 关于V$SQLAREA 栏位介绍
FIRST_LOAD_TIME VARCHAR2(19) Timestamp of the parent creation time
EXECUTIONS NUMBER Total number of executions, totalled over all the child cursors
(3). 如何查询
代码语言:javascript复制SELECT SQL_ID, SQL_TEXT,FIRST_LOAD_TIME, EXECUTIONS FROM V$SQLAREA WHERE SQL_ID = '497wh6n7hu14f'
(4). 总结
如果此时清空共享池,那么你会发现V$SQLAREA中对应的SQL的EXECUTIONS次数清零了。
如果要查看某个时间段该SQL语句执行了多少次,那么必须在这两个时间段执行上面SQL语句,两次EXECUTIONS的差值表示这段时间内SQL语句的执行次数。
EXECUTIONS是全局的,往往不能查看某个会话或用户执行了多少次。这也是其局限性之一。
2. 通过DBA_HIST_SQLSTAT关联DBA_HIST_SNAPSHOT找出某些SQL的执行次数。
(1). 缺点
但是部分快照如果没有捕获到有些SQL。这样也就无法通过下面SQL语句查看执行次数。
也是就说这种方法是有缺陷的。执行越频繁的语句,也越容易被SNAPSHOT抓取到.
(2). 执行语法
代码语言:javascript复制 SELECT M.SQL_ID, TO_CHAR (N.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD') "DATETIME", SUM (M.EXECUTIONS_DELTA) EXECUTIONS FROM DBA_HIST_SQLSTAT M, DBA_HIST_SNAPSHOT N WHERE M.SNAP_ID = N.SNAP_ID AND M.DBID = N.DBID AND M.INSTANCE_NUMBER = N.INSTANCE_NUMBER AND M.INSTANCE_NUMBER = 1
AND TO_CHAR (N.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD') = '2014-12-25'
AND M.SQL_ID = '497wh6n7hu14f'
GROUP BY M.SQL_ID, TO_CHAR (N.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD') ORDER BY M.SQL_ID
3. AWR报告查看某个SQL的执行次数,同上面一样,AWR报告也受SNAPSHOT影响。不一定捕获了你需要查询的SQL
4. 查看当前数据库执行次数最多的SQL,例如,查询执行最频繁的TOP 15的SQL语句。
代码语言:javascript复制SELECT SQL_TEXT, EXECUTIONS FROM (SELECT SQL_TEXT, EXECUTIONS, RANK () OVER (ORDER BY EXECUTIONS DESC) EXEC_RANK FROM V$SQLAREA) WHERE EXEC_RANK <= 15;
5. 查看执行SQL的历史记录信息
代码语言:javascript复制 SELECT DBMS_LOB.SUBSTR (sql_text, 100, 1) SQL_SHORT,
tab1.sql_id,
DBMS_LOB.getlength (sql_text) SQL_Len,
ROUND (TOTAL_WAIT / 1000000, 2) TOTAL_WAIT_SECS,
ROUND (ELAPSED_TIME_DELTA / 1000000, 2) TOTAL_TIME_SECS,
TO_CHAR (BEGIN_INTERVAL_TIME, 'DAY', 'NLS_DATE_LANGUAGE=AMERICAN')
Week_Day,
BEGIN_INTERVAL_TIME,
END_INTERVAL_TIME,
EXEC_COUNT,
AVG_CPU_TIME_SECS,
AVG_ELAPSED_SECS,
ROUND (ELAPSED_TIME_DELTA / 1000000, 0) ELAPSED_TIME_DELTA,
ROUND (AVG_ROWS_PROCESSED, 1) AVG_ROWS_PROCESSED,
PLAN_HASH_VALUE,
MODULE,
ACTION,
PARSING_SCHEMA_NAME,
(SELECT username
FROM dba_users
WHERE user_id = PARSING_USER_ID)
PARSING_USER,
AVG_BUFFER_GETS,
AVG_DISK_READS,
AVG_IOWAIT AVG_iowai_secs,
AVG_CCWAIT AVG_ccwait_secs,
AVG_CLWAIT AVG_clwait_secs,
AVG_APWAIT AVG_apwait_secs,
AVG_PX_SERVERS,
AVG_PARSE_CALLS,
tab1.SNAP_ID,
tab1.INSTANCE_NUMBER,
AVG_CELL_UNCOMPRESSED_BYTES,
AVG_DIRECT_WRITES,
AVG_IO_INTERCONNECT_BYTES,
AVG_IO_OFFLOAD_ELIG_BYTES,
AVG_IO_OFFLOAD_RETURN_BYTES,
AVG_JAVEXEC_TIME,
AVG_OPTIMIZED_PHYSICAL_READS,
AVG_PLSEXEC_TIME_DELTA,
AVG_SORTS_DELTA,
-- BIND_DATA, -- -- (Falta arreglar) listagg( (select * from table(dbms_sqltune.extract_binds(bind_data)), '#') WITHIN GROUP (ORDER BY 1)) Binds ,
DBMS_LOB.SUBSTR (sql_text, 2000, 1) SQL_TEXT1,
DBMS_LOB.SUBSTR (sql_text, 2000, 2001) SQL_TEXT2,
DBMS_LOB.SUBSTR (sql_text, 2000, 4001) SQL_TEXT3,
DBMS_LOB.SUBSTR (sql_text, 2000, 6001) SQL_TEXT4,
DBMS_LOB.SUBSTR (sql_text, 2000, 8001) SQL_TEXT5,
DBMS_LOB.SUBSTR (sql_text, 2000, 10001) SQL_TEXT6,
DBMS_LOB.SUBSTR (sql_text, 2000, 12001) SQL_TEXT7,
DBMS_LOB.SUBSTR (sql_text, 2000, 14001) SQL_TEXT8,
DBMS_LOB.SUBSTR (sql_text, 2000, 16001) SQL_TEXT9,
DBMS_LOB.SUBSTR (sql_text, 2000, 18001) SQL_TEXT10
FROM DBA_HIST_SQLTEXT D,
DBA_HIST_SNAPSHOT N,
( SELECT S.SQL_ID SQL_ID,
instance_number,
SUM (EXECUTIONS) EXEC_COUNT,
ROUND ( (SUM (CPU_TIME_DELTA) / SUM (EXECUTIONS)) / 1000000,
4)
AVG_CPU_TIME_SECS,
ROUND (
(SUM (ELAPSED_TIME_DELTA) / SUM (EXECUTIONS)) / 1000000,
4)
AVG_ELAPSED_SECS,
SUM (ELAPSED_TIME_DELTA) ELAPSED_TIME_DELTA,
ROUND ( (SUM (ROWS_PROCESSED_DELTA) / SUM (EXECUTIONS)), 4)
AVG_ROWS_PROCESSED,
PLAN_HASH_VALUE,
MODULE,
ACTION,
PARSING_SCHEMA_NAME,
PARSING_USER_ID,
ROUND (SUM (BUFFER_GETS_DELTA) / SUM (EXECUTIONS), 4)
AVG_BUFFER_GETS,
ROUND (SUM (DISK_READS_DELTA) / SUM (EXECUTIONS), 0)
AVG_DISK_READS,
SNAP_ID,
ROUND ( (SUM (IOWAIT_DELTA) / SUM (EXECUTIONS)) / 1000000, 4)
AVG_IOWAIT,
ROUND ( (SUM (CCWAIT_DELTA) / SUM (EXECUTIONS)) / 1000000, 4)
AVG_CCWAIT,
ROUND ( (SUM (CLWAIT_DELTA) / SUM (EXECUTIONS)) / 1000000, 4)
AVG_CLWAIT,
ROUND ( (SUM (APWAIT_DELTA) / SUM (EXECUTIONS)) / 1000000, 4)
AVG_APWAIT,
ROUND (SUM (PX_SERVERS_EXECS_DELTA) / SUM (EXECUTIONS), 4)
AVG_PX_SERVERS,
ROUND (SUM (PARSE_CALLS_DELTA) / SUM (EXECUTIONS), 4)
AVG_PARSE_CALLS,
ROUND (SUM (CELL_UNCOMPRESSED_BYTES_DELTA) / SUM (EXECUTIONS),
4)
AVG_CELL_UNCOMPRESSED_BYTES,
ROUND (SUM (DIRECT_WRITES_DELTA) / SUM (EXECUTIONS), 4)
AVG_DIRECT_WRITES,
ROUND (SUM (IO_INTERCONNECT_BYTES_DELTA) / SUM (EXECUTIONS),
4)
AVG_IO_INTERCONNECT_BYTES,
ROUND (SUM (IO_OFFLOAD_ELIG_BYTES_DELTA) / SUM (EXECUTIONS),
4)
AVG_IO_OFFLOAD_ELIG_BYTES,
ROUND (SUM (IO_OFFLOAD_RETURN_BYTES_DELTA) / SUM (EXECUTIONS),
4)
AVG_IO_OFFLOAD_RETURN_BYTES,
ROUND (SUM (JAVEXEC_TIME_DELTA) / SUM (EXECUTIONS), 4)
AVG_JAVEXEC_TIME,
ROUND (
SUM (OPTIMIZED_PHYSICAL_READS_DELTA) / SUM (EXECUTIONS),
4)
AVG_OPTIMIZED_PHYSICAL_READS,
ROUND (SUM (PLSEXEC_TIME_DELTA) / SUM (EXECUTIONS), 4)
AVG_PLSEXEC_TIME_DELTA,
ROUND (SUM (SORTS_DELTA) / SUM (EXECUTIONS), 4)
AVG_SORTS_DELTA,
SUM (
IOWAIT_DELTA CCWAIT_DELTA CLWAIT_DELTA APWAIT_DELTA)
TOTAL_WAIT,
MAX (BIND_DATA) BIND_DATA
FROM (SELECT S.*,
DECODE (EXECUTIONs_DELTA, 0, 1, EXECUTIONS_DELTA)
EXECUTIONS
FROM DBA_HIST_SQLSTAT S) S
WHERE 1 = 1 -- and ( PARSING_SCHEMA_NAME like 'EUS%' ) -- Filter by schema name
GROUP BY S.SQL_ID,
instance_number,
S.SNAP_ID,
PLAN_HASH_VALUE,
MODULE,
ACTION,
PARSING_SCHEMA_NAME,
S.INSTANCE_NUMBER,
PARSING_USER_ID
HAVING ROUND (
(SUM (ELAPSED_TIME_DELTA) / SUM (EXECUTIONS)) / 1000000,
4) >= 10 -- Filter >= n secs
ORDER BY 5 DESC) TAB1
WHERE D.SQL_ID = TAB1.SQL_ID AND TAB1.snap_id = N.snap_id -- and upper(tab1.module) like '%PKG_BATCH_PROCESSES_SDM%'
AND d.sql_id IN ('6tmcqrydfgdtc') -- AND D.SQL_ID in ( select distinct sql_id from DBA_HIST_ACTIVE_SESS_HISTORY where top_level_sql_id = '85xkhugz5kt8h' )
-- AND upper(DBMS_LOB.SUBSTR (sql_text, 100, 1)) LIKE 'INSERT%'
-- AND upper(DBMS_LOB.SUBSTR (sql_text, 1000, 1)) LIKE 'SELECT%'
-- AND upper(DBMS_LOB.SUBSTR (sql_text, 3000, 1)) LIKE '%INACTIVE%'
AND BEGIN_INTERVAL_TIME > SYSDATE - 60
-- and BEGIN_INTERVAL_TIME between to_date('05/09/2013 07:50:00', 'DD/MM/YYYY hh24:mi:ss') AND to_date('05/09/2013 16:10:00', 'DD/MM/YYYY hh24:mi:ss')
-- and BEGIN_INTERVAL_TIME >= (SELECT job_start_datetime - (2/24) FROM job_status WHERE job_name = 'RAVLDBSK' AND cob_date = (SELECT current_cob_date FROM eus_cob))
-- and BEGIN_INTERVAL_TIME <= (SELECT job_end_datetime (2/24) FROM job_status WHERE job_name = 'RAVLDBSK' AND cob_date = (SELECT current_cob_date FROM eus_cob))
-- order by AVG_ELAPSED_SECS DESC
-- order by ELAPSED_TIME_DELTA DESC
ORDER BY BEGIN_INTERVAL_TIME DESC
-- order by TOTAL_WAIT_SECS desc;
Thanks and Regards
部分参考:潇湘隐者 – http://www.cnblogs.com/kerrycode/p/4111746.html
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/109606.html原文链接:https://javaforall.cn