如何找出哪些表访问最多或经常在Oracle 10g中使用

Question

我在使用Oracle 10g中最常用的表时遇到了麻烦。 我正在使用Oracle 10g版本10.2.0.4.0和EBS R12.1.3应用程序。

请帮我整理我的数据库中最常用的表格。

如果可能我希望得到TableName，所有者以及它在一个时间范围内访问的次数。

我需要这个用于调整目的。

请提供查询以获得相同的结果。

在此先感谢！

Answer 1

我知道2种方式，其中之一是对所有表格进行监视，然后使用该统计信息，或使用v$segment_statistics来分析块访问统计信息，类似这样，按照访问类型表的总值显示前50 ，您可以在查询中提供目标架构名称<YOURSCHEMA>。

select * 
    from (select rownum RN, T.* 
      from (select stat.OBJECT_NAME, stat.STATISTIC_NAME, stat.VALUE AcsValue, 
         sum(value) over(partition by stat.OBJECT_NAME) Total 
        from v$segment_statistics stat 
        where owner = <YOURSCHEMA> 
        and stat.OBJECT_TYPE = 'TABLE' 
        and stat.STATISTIC_NAME in 
         ('logical reads', 'pptimized physical reads', 
          'physical read requests', 'physical reads', 
          'physical reads direct', 'physical write requests', 
          'physical writes', 'physical writes direct') 
        order by sum(value) over(partition by stat.OBJECT_NAME) desc) T) TOrd 
where TOrd.RN < 50 

Answer 2

如果您需要此调整一个Oracle EBS环境，我建议从应用程序启动或SQL，而不是表，并用段层看着它。

例如，AWR包含特定日期范围内IO密集度最高的查询，如DBA AWR SQL Performance Summary所示。此SQL使用的函数xxen_util.responsibility和xxen_util.apps_module可以下载here。

select 
decode(:order_by, 
'elapsed time',x.elapsed_time/sum(x.elapsed_time) over()*100, 
'IO',x.buffer_io/sum(x.buffer_io) over()*100, 
'executions',x.executions/sum(x.executions) over()*100 
) percentage, 
xxen_util.responsibility(x.action) responsibility, 
xxen_util.apps_module(x.module) apps_module, 
x.module, 
x.program, 
x.program_line#, 
x.sql_id, 
x.plan_hash_value, 
(select dhst.sql_text from dba_hist_sqltext dhst where x.dbid=dhst.dbid and x.sql_id=dhst.sql_id) sql_text, 
x.executions, 
x.elapsed_time, 
xxen_util.time(x.elapsed_time) time, 
x.user_io_wait_time, 
x.cpu_time, 
x.plsql_exec_time, 
x.concurrency_wait_time, 
x.application_wait_time, 
x.elapsed_time/decode(x.executions,0,null,x.executions) time_exec, 
x.buffer_io, 
x.disk_io, 
x.buffer_io/decode(x.executions,0,null,x.executions) io_exec, 
x.rows_processed/decode(x.executions,0,null,x.executions) rows_exec, 
x.buffer_io/decode(x.rows_processed,0,null,x.rows_processed) io_row, 
x.buffer_io/decode(x.elapsed_time,0,null,x.elapsed_time) io_sec, 
case when x.executions>100 then x.buffer_io/(decode(x.last_load_time,x.first_load_time,to_date(null),x.last_load_time)-x.first_load_time)/24/3600 end io_sec_avg, 
(x.buffer_io-x.disk_io)/xxen_util.zero_to_null(x.cpu_time) buffer_rate, 
x.disk_io/xxen_util.zero_to_null(x.user_io_wait_time) disk_rate, 
100*x.disk_io/xxen_util.zero_to_null(x.buffer_io) disk_percentage, 
case when x.executions>100 then x.executions/(decode(x.last_load_time,x.first_load_time,to_date(null),x.last_load_time)-x.first_load_time)/24 end execs_per_hour, 
case when x.executions>100 then 100*x.elapsed_time/(decode(x.last_load_time,x.first_load_time,to_date(null),x.last_load_time)-x.first_load_time)/24/3600 end time_percentage, 
x.is_bind_sensitive, 
x.is_bind_aware, 
x.parsing_schema_name, 
x.parse_calls, 
x.first_load_time, 
x.last_load_time, 
x.command_type, 
x.action 
from 
(
select /*+ cardinality(dhs 100) */ distinct 
case when :aggregate_level like '% per day' then dhs.date_ end date_, 
case when :aggregate_level like 'SQL%' then max(dhss.module) over (partition by dhss.sql_id, dhss.plan_hash_value) else dhss.module end module, 
case when :aggregate_level like 'SQL%' then max(dhss.action) over (partition by dhss.sql_id, dhss.plan_hash_value) else dhss.action end action, 
sum(dhss.elapsed_time_delta) over (partition by dhss.sql_id, dhss.plan_hash_value)/1000000 elapsed_time, 
sum(dhss.iowait_delta) over (partition by dhss.sql_id, dhss.plan_hash_value)/1000000 user_io_wait_time, 
sum(dhss.cpu_time_delta) over (partition by dhss.sql_id, dhss.plan_hash_value)/1000000 cpu_time, 
sum(dhss.plsexec_time_delta) over (partition by dhss.sql_id, dhss.plan_hash_value)/1000000 plsql_exec_time, 
sum(dhss.ccwait_delta) over (partition by dhss.sql_id, dhss.plan_hash_value)/1000000 concurrency_wait_time, 
sum(dhss.apwait_delta) over (partition by dhss.sql_id, dhss.plan_hash_value)/1000000 application_wait_time, 
vp.value*sum(dhss.buffer_gets_delta) over (partition by dhss.sql_id, dhss.plan_hash_value)/1000000 buffer_io, 
sum(dhss.physical_read_bytes_delta) over (partition by dhss.sql_id, dhss.plan_hash_value)/1000000 disk_io, 
sum(dhss.executions_delta) over (partition by dhss.sql_id, dhss.plan_hash_value) executions, 
sum(dhss.rows_processed_delta) over (partition by dhss.sql_id, dhss.plan_hash_value) rows_processed, 
min(dhss.parsing_schema_name) over (partition by dhss.sql_id, dhss.plan_hash_value) parsing_schema_name, 
sum(dhss.parse_calls_delta) over (partition by dhss.sql_id, dhss.plan_hash_value) parse_calls, 
min(dhs.begin_interval_time_) over (partition by dhss.sql_id, dhss.plan_hash_value) first_load_time, 
max(dhs.end_interval_time_) over (partition by dhss.sql_id, dhss.plan_hash_value) last_load_time, 
case when :aggregate_level like 'SQL%' then dhss.sql_id end sql_id, 
case when :aggregate_level like 'SQL%' then dhss.plan_hash_value end plan_hash_value, 
case when :aggregate_level like 'SQL%' then decode(dhst.command_type,1,'create table',2,'insert',3,'select',6,'update',7,'delete',9,'create index',11,'alter index',26,'lock table',42,'alter session',44,'commit',45,'rollback',46,'savepoint',47,'pl/sql block',48,'set transaction',50,'explain',62,'analyze table',90,'set constraints',170,'call',189,'merge','other') end command_type, 
case when :aggregate_level like 'SQL%' then gsa.is_bind_sensitive end is_bind_sensitive, 
case when :aggregate_level like 'SQL%' then gsa.is_bind_aware end is_bind_aware, 
case when :aggregate_level like 'SQL%' then gsa.object_name end program, 
case when :aggregate_level like 'SQL%' then gsa.program_line# end program_line#, 
dhs.dbid 
from 
(
select trunc(dhs.begin_interval_time) date_, 
cast(dhs.begin_interval_time as date) begin_interval_time_, 
cast(dhs.end_interval_time as date) end_interval_time_, 
dhs.* 
from 
dba_hist_snapshot dhs 
) dhs, 
dba_hist_sqlstat dhss, 
dba_hist_sqltext dhst, 
(
select 
gsa.sql_id, 
gsa.plan_hash_value, 
gsa.is_bind_sensitive, 
gsa.is_bind_aware, 
do.object_name, 
case when gsa.program_line#>0 then gsa.program_line# end program_line# 
from 
gv$sqlarea gsa, 
dba_objects do 
where 
2=2 and 
'Y'='Y' and 
gsa.program_id=do.object_id(+) 
) gsa, 
(select vp.value from v$parameter vp where vp.name like 'db_block_size') vp 
where 
dhs.begin_interval_time>=:date_from and 
dhst.command_type not in (47) and 
1=1 and 
dhs.dbid=(select vd.dbid from v$database vd) and 
dhs.dbid=dhss.dbid and 
dhs.instance_number=dhss.instance_number and 
dhs.snap_id=dhss.snap_id and 
dhss.dbid=dhst.dbid and 
dhss.sql_id=dhst.sql_id and 
dhss.sql_id=gsa.sql_id(+) and 
dhss.plan_hash_value=gsa.plan_hash_value(+) 
) x 
order by 
case when :aggregate_level in ('Module per day','SQL per day') then x.date_ end desc, 
decode(:order_by,'elapsed time',x.elapsed_time,'IO',x.buffer_io,'executions',x.executions) desc 

-- binds -- 
:aggregate_level = SQL 
:order_by = IO