抓取全表掃描的表，篩選和分析

 ---- 抓取某一時間點的進行全表掃描的表和執行的次數.
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;
 

----- 查詢相鄰的snap_id中 table scan rows gotten (總掃描的行數）的值，該值為累計值.
 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;

------

select 'update ft_scan_0907_1230_rows set counts=(select count(*) from ' ||owner ||  '.' ||table_name|| ') where wner=''' ||owner||  ''' and table_name=''' ||table_name||''';'
from ft_scan_0907_1230_rows

----------------------------------
Results_sql;

select a.owner,a.table_name, sum(b.executions - a.executions),c.counts, sum(b.executions - a.executions)*c.counts exec_by_counts
  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;

______________________

step1:

CREATE TABLE DBMGR.ft_scan_0910_0200PM
(
  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

   where t1.owner = t2.owner
   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,

create view FT_VIEW_T1 (owner,table_name,delta_sum_executions,counts,exec_by_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_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 ;
         
-----------------------------------------------------------------------------------

create view FT_VIEW_T2 (owner,table_name,delta_sum_executions,counts,exec_by_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.

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

-------------------------------------------------------------------------------
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;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

按照全表掃描的表，

A指標：表的實際行數*執行次數 / sum ( 表的實際行數*執行次數）                                               百分比

B指標：表的高水位以下的blocks數*執行次數 / sum (表的高水位以下的blocks數*執行次數)  百分比

取A指標 和 B指標統計的表的交集而且 百分比大於1%.

進行後期最佳化.

 

select 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,n.counts,s.blocks
  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

 where m.table_name = n.table_name
 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

 

=========================

表的高水位下blocks數的計算：

 

例項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;

/