select a.hash_value, b.object_owner, b.object_name, a.EXECUTIONS
from v$sql a, v$sql_plan b
where a.hash_value = b.HASH_VALUE
and a.PLAN_HASH_VALUE = b.plan_hash_value
and a.CHILD_NUMBER = b.CHILD_NUMBER
and b.object_type = 'TABLE'
and b.options like 'FULL%'
and b.object_owner not in ('SYS', 'SYSTEM', 'DBMGR')
group by a.hash_value, b.OBJECT_OWNER, b.object_name, a.executions;
SELECT a.snap_id,
TO_CHAR(begin_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss'),
a.stat_name,
a.VALUE
FROM dba_hist_sysstat a, dba_hist_snapshot b
WHERE a.snap_id = b.snap_id
and a.stat_name = 'table scan rows gotten'
and a.snap_id >= '52251'
order by snap_id desc;
------
from ft_scan_0907_1230_rows
Results_sql;
from ft_scan_0910_0200PM a, ft_scan_0910_0215PM b, ft_scan_0910_rows c
where a.hash_value = b.hash_value
and a.table_name = b.table_name
and a.table_name = c.table_name
group by a.owner,a.table_name,c.counts
order by a.table_name;
______________________
(
timstamp date,
hash_value number (15,0),
plan_hash_value number (15,0),
child_number number (15,0),
owner VARCHAR2(30),
table_name VARCHAR2(30),
executions NUMBER (15,0)
)
TABLESPACE USERS
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
Results_10Sep_14;00-14;30_2snapshot.xls - -
-----------------------------------------------
select t1.owner,
t1.table_name,
(t1.exec_by_counts + t2.exec_by_counts)
from (select a.owner,
a.table_name,
(b.sum_executions - a.sum_executions),
c.counts,
(b.sum_executions - a.sum_executions) * c.counts exec_by_counts
from ft_scan_0910_0200PM_sum a,
ft_scan_0910_0215PM_sum b,
ft_scan_0910_rows c
where a.table_name = b.table_name
and a.table_name = c.table_name
group by a.owner,
a.table_name,
(b.sum_executions - a.sum_executions),
c.counts) t1,
(select a.owner,
a.table_name,
(b.sum_executions - a.sum_executions),
c.counts,
(b.sum_executions - a.sum_executions) * c.counts exec_by_counts
from ft_scan_0910_0215PM_sum a,
ft_scan_0910_0230PM_sum b,
ft_scan_0910_rows c
where a.table_name = b.table_name
and a.table_name = c.table_name
group by a.owner,
a.table_name,
(b.sum_executions - a.sum_executions),
c.counts) t2
and t1.table_name = t2.table_name
group by t1.owner, t1.table_name,(t1.exec_by_counts + t2.exec_by_counts)
order by (t1.exec_by_counts + t2.exec_by_counts)desc;
================================================================================
创建2个中间过程view,
as
select a.owner,
a.table_name,
(b.sum_executions - a.sum_executions),
c.counts,
(b.sum_executions - a.sum_executions) * c.counts exec_by_counts
from ft_scan_0910_0200PM_sum a,
ft_scan_0910_0215PM_sum b,
ft_scan_0910_rows c
where a.table_name = b.table_name
and a.table_name = c.table_name
and (b.sum_executions - a.sum_executions) >=0
group by a.owner,
a.table_name,
(b.sum_executions - a.sum_executions),
c.counts ;
-----------------------------------------------------------------------------------
as
select a.owner,
a.table_name,
(b.sum_executions - a.sum_executions),
c.counts,
(b.sum_executions - a.sum_executions) * c.counts exec_by_counts
from ft_scan_0910_0215PM_sum a,
ft_scan_0910_0230PM_sum b,
ft_scan_0910_rows c
where a.table_name = b.table_name
and a.table_name = c.table_name
and (b.sum_executions - a.sum_executions) >=0
group by a.owner,
a.table_name,
(b.sum_executions - a.sum_executions),
c.counts ;
两个snapshot时间段内的所有table 的 delta_sum_executions.
from ft_view_t1 t1, ft_view_t2 t2
where t1.table_name = t2.table_name(+)
group by t1.owner, t1.table_name
union
select t2.owner, t2.table_name, sum(nvl(t1.delta_sum_executions,0)+nvl(t2.delta_sum_executions,0)) delta_sum_executions
from ft_view_t1 t1, ft_view_t2 t2
where t1.table_name(+) = t2.table_name
group by t2.owner, t2.table_name
select t.owner,
t.table_name,
t.delta_sum_executions,
c.counts,
t.delta_sum_executions * c.counts
from (select t1.owner,
t1.table_name,
sum(nvl(t1.delta_sum_executions, 0) +
nvl(t2.delta_sum_executions, 0)) delta_sum_executions
from ft_view_t1 t1, ft_view_t2 t2
where t1.table_name = t2.table_name(+)
group by t1.owner, t1.table_name
union
select t2.owner,
t2.table_name,
sum(nvl(t1.delta_sum_executions, 0) +
nvl(t2.delta_sum_executions, 0)) delta_sum_executions
from ft_view_t1 t1, ft_view_t2 t2
where t1.table_name(+) = t2.table_name
group by t2.owner, t2.table_name) T,
ft_scan_0910_rows c
where t.table_name = c.table_name
group by t.owner,t.table_name,t.delta_sum_executions,c.counts
order by t.delta_sum_executions * c.counts desc;
from DBMGR.ft_size_analzyed M,
ft_scan_blocks_sum S,
(select t.owner,
t.table_name,
t.delta_sum_executions,
c.counts,
t.delta_sum_executions * c.counts
from (select t1.owner,
t1.table_name,
sum(nvl(t1.delta_sum_executions, 0) +
nvl(t2.delta_sum_executions, 0)) delta_sum_executions
from ft_view_t1 t1, ft_view_t2 t2
where t1.table_name = t2.table_name(+)
group by t1.owner, t1.table_name
union
select t2.owner,
t2.table_name,
sum(nvl(t1.delta_sum_executions, 0) +
nvl(t2.delta_sum_executions, 0)) delta_sum_executions
from ft_view_t1 t1, ft_view_t2 t2
where t1.table_name(+) = t2.table_name
group by t2.owner, t2.table_name) T,
ft_scan_0910_rows c
where t.table_name = c.table_name
group by t.owner, t.table_name, t.delta_sum_executions, c.counts) N
and m.table_name = s.table_name(+)
order by m.owner,m.table_name,m.counts,m.num_rows,m.last_analyzed,n.delta_sum_executions,n.counts,n.delta_sum_executions * n.counts,m.size_m
实例001----- 通过Oracle的包dbms_space.space_usage 计算表ft_scan_tables 中所列出的tables的blocks. 然后将结果输出至 ft_scan_blocks.
select * from ft_scan_tables;
OWNER | TABLE_NAME | TYPE |
FGLPA | QUEST_ADV_MON_VERSION | TABLE |
FGLPA | QUEST_ADV_SNAPSHOT_CTRL | TABLE |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
for i in( select owner,table_name,type from dbmgr.ft_scan_tables where owner not in ('OUTLN','FOGLIGHT','LBACSYS')) loop
dbms_space.space_usage (i.owner,i.table_name,i.type, v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
insert into dbmgr.ft_scan_blocks(owner,table_name,type,Unformatted, FS1 ,FS2 ,FS3 ,FS4 ,Full)
select i.owner,i.table_name,i.type, v_unformatted_blocks ,v_fs1_blocks , v_fs2_blocks ,v_fs3_blocks ,v_fs4_blocks , v_full_blocks from dual;
end loop;
commit;
end;
/
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--- 上述循环结果输出至表 ft_scan_blocks.
select * from dbmgr.ft_scan_blocks
OWNER | TABLE_NAME | TYPE | UNFORMATTED | FS1 | FS2 | FS3 | FS4 | FULL |
LIFEDATA | L_PM_RANK_INFO | TABLE | 0 | 0 | 4 | 4 | 38 | 14 |
LIFEBASE | LCS_QUALITY_TYPE_TBL | TABLE | 28 | 0 | 0 | 0 | 32 | 0 |
LIFEDATA | LDC_CLIENT_SPLIT_REPORT | TABLE | 0 | 0 | 0 | 0 | 5 | 0 |
select * from dbmgr.ft_scan_blocks;
truncate table dbmgr.ft_scan_blocks;
Partition tables 要采取下面的方法计算blocks.
++++++++++++++++++++++++++++++++++++++++++
-- Create table
create table TEST1
(
owner VARCHAR2(200),
table_name VARCHAR2(500),
type VARCHAR2(100),
partition_name varchar2(100),
unformatted NUMBER,
fs1 NUMBER,
fs2 NUMBER,
fs3 NUMBER,
fs4 NUMBER,
full NUMBER
);
create table ft_scan_blocks1(owner,table_name,type,partition_name) as
select table_owner,table_name,'TABLE PARTITION',partition_name
from dba_tab_partitions where (table_owner = 'LIFEDATA' and table_name in ('LCS_BONUS_DETAIL')) or (table_owner = 'LIFEDATAARC' and table_name = 'MONTHLY_SALARY_PROVIDE_H')
union all
select table_owner,table_name,'TABLE SUBPARTITION',subpartition_name
from dba_tab_subpartitions where (table_owner = 'LIFEDATA' and table_name in ('E_LETTER_REPOSITORY'))
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
for i in( select owner,table_name,type,partition_name from dbmgr.ft_scan_blocks1 ) loop
dbms_space.space_usage (i.owner,i.table_name,i.type, v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes,i.partition_name);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
insert into dbmgr.test1(owner,table_name,type,partition_name,Unformatted, FS1 ,FS2 ,FS3 ,FS4 ,Full)
select i.owner,i.table_name,i.type,i.partition_name, v_unformatted_blocks ,v_fs1_blocks , v_fs2_blocks ,v_fs3_blocks ,v_fs4_blocks , v_full_blocks from dual;
commit;
end loop;
end;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22578826/viewspace-745252/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22578826/viewspace-745252/