Oracle 11g AWR 中 version_count 的显示
5墨值下载
Oracle 11g AWR 中 version_count 的显示
在 Oracle 11g 中一个诊断案例中，我们再一次遇到了和 VERSION_COUNT 相关的案
例，以下信息显示数据库版本等基本信息：
在 SQL 的 Version Count 部分可以看到，大量 SQL 有 50 个版本，但是注意到 SQL_ID
完全相同，这是什么原因导致的呢？
首先要指导 AWR 的信息来源，在生成 AWR 报告的时候，可以从 v$sql 中捕获其 SQL
查询语句：
select *
from (select /*+ ordered use_nl (b st) */
e.version_count,
sqt.exec,
e.sql_id,
decode(e.module, null, null, 'Module: ' || e.module),
nvl(st.sql_text, to_clob('** SQL Text Not Available **'))
from dba_hist_sqlstat e,
(select sql_id, sum(executions_delta) exec
from dba_hist_sqlstat
where dbid = :dbid
and instance_number = :inst_num
and :bid < snap_id
and snap_id <= :eid
group by sql_id) sqt,
dba_hist_sqltext st
where sqt.sql_id(+) = e.sql_id
and e.snap_id = :eid
and e.dbid = :dbid
and e.instance_number = :inst_num
and st.sql_id(+) = e.sql_id
and st.dbid(+) = :dbid
and e.version_count > :vcnt_thres
order by nvl(e.version_count, -1) desc, e.sql_id)
where rownum < :tsql_max
这段 SQL 显示，其数据来源为 dba_hist_sqlstat 视图，该视图的创建语句如下（来自
12c 版本的 catawrvw.sql）， 根 本 数 据 来 源 为 WRH$_SQLSTAT ：
/***************************************
* DBA_HIST_SQLSTAT
***************************************/
create or replace view DBA_HIST_SQLSTAT
container_data sharing=object
(SNAP_ID, DBID, INSTANCE_NUMBER,
SQL_ID, PLAN_HASH_VALUE,
OPTIMIZER_COST, OPTIMIZER_MODE, OPTIMIZER_ENV_HASH_VALUE,
SHARABLE_MEM, LOADED_VERSIONS, VERSION_COUNT,
MODULE, ACTION,
SQL_PROFILE, FORCE_MATCHING_SIGNATURE,
PARSING_SCHEMA_ID, PARSING_SCHEMA_NAME, PARSING_USER_ID,
FETCHES_TOTAL, FETCHES_DELTA,
END_OF_FETCH_COUNT_TOTAL, END_OF_FETCH_COUNT_DELTA,
SORTS_TOTAL, SORTS_DELTA,
EXECUTIONS_TOTAL, EXECUTIONS_DELTA,
PX_SERVERS_EXECS_TOTAL, PX_SERVERS_EXECS_DELTA,
LOADS_TOTAL, LOADS_DELTA,
INVALIDATIONS_TOTAL, INVALIDATIONS_DELTA,
PARSE_CALLS_TOTAL, PARSE_CALLS_DELTA, DISK_READS_TOTAL,
DISK_READS_DELTA, BUFFER_GETS_TOTAL, BUFFER_GETS_DELTA,
ROWS_PROCESSED_TOTAL, ROWS_PROCESSED_DELTA, CPU_TIME_TOTAL,
CPU_TIME_DELTA, ELAPSED_TIME_TOTAL, ELAPSED_TIME_DELTA,
IOWAIT_TOTAL, IOWAIT_DELTA, CLWAIT_TOTAL, CLWAIT_DELTA,
APWAIT_TOTAL, APWAIT_DELTA, CCWAIT_TOTAL, CCWAIT_DELTA,
DIRECT_WRITES_TOTAL, DIRECT_WRITES_DELTA, PLSEXEC_TIME_TOTAL,
PLSEXEC_TIME_DELTA, JAVEXEC_TIME_TOTAL, JAVEXEC_TIME_DELTA,
IO_OFFLOAD_ELIG_BYTES_TOTAL, IO_OFFLOAD_ELIG_BYTES_DELTA,
IO_INTERCONNECT_BYTES_TOTAL, IO_INTERCONNECT_BYTES_DELTA,
PHYSICAL_READ_REQUESTS_TOTAL, PHYSICAL_READ_REQUESTS_DELTA,
PHYSICAL_READ_BYTES_TOTAL, PHYSICAL_READ_BYTES_DELTA,
PHYSICAL_WRITE_REQUESTS_TOTAL, PHYSICAL_WRITE_REQUESTS_DELTA,
PHYSICAL_WRITE_BYTES_TOTAL, PHYSICAL_WRITE_BYTES_DELTA,
OPTIMIZED_PHYSICAL_READS_TOTAL, OPTIMIZED_PHYSICAL_READS_DELTA,
CELL_UNCOMPRESSED_BYTES_TOTAL, CELL_UNCOMPRESSED_BYTES_DELTA,
IO_OFFLOAD_RETURN_BYTES_TOTAL, IO_OFFLOAD_RETURN_BYTES_DELTA,
BIND_DATA, FLAG, CON_DBID, CON_ID)
as
select sql.snap_id, sql.dbid, sql.instance_number,
sql_id, plan_hash_value,
optimizer_cost, optimizer_mode, optimizer_env_hash_value,
sharable_mem, loaded_versions, version_count,
substrb(module,1,(select ksumodlen from x$modact_length)) module,
substrb(action,1,(select ksuactlen from x$modact_length)) action,
sql_profile, force_matching_signature,
of 12
5墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论