0
Oracle 11g AWR 中 version_count 的显示
89
12页
1次
墨值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

评论

最新上传
暂无内容,敬请期待...
下载排行榜
周榜月榜总榜
近期活动
全部
暂无活动,敬请期待...
相关课程
全部
暂无课程,敬请期待...