q_frag

admin

set linesize 160 pagesize 999;

-- 数据库总空间
select sum(bytes)/(1024*1024*1024) from dba_data_files;
select sum(bytes)/(1024*1024*1024) from dba_segments;
select sum(bytes)/(1024*1024*1024) from dba_free_space;

-- FSFI值
select a.tablespace_name,
       sqrt(max(a.blocks) / sum(a.blocks)) * (100 / sqrt(sqrt(count(a.blocks)))) FSFI
  from dba_free_space a, dba_tablespaces b
  where a.tablespace_name = b.tablespace_name
and b.contents not in ('TEMPORARY','UNDO')
group by a.tablespace_name
order by FSFI;

-- 按表空间显示连续的空闲空间
========
Script : tfstsfgm
========
SET ECHO off
REM NAME:TFSTSFRM.SQL
REM USAGE:"@path/tfstsfgm"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT ON DBA_FREE_SPACE
REM ------------------------------------------------------------------------
REM PURPOSE:
REM    The following is a script that will determine how many extents
REM    of contiguous free space you have in Oracle as well as the  
REM total amount of free space you have in each tablespace. From  
REM    these results you can detect how fragmented your tablespace is.  
REM   
REM    The ideal situation is to have one large free extent in your  
REM    tablespace. The more extents of free space there are in the  
REM    tablespace, the more likely you  will run into fragmentation  
REM    problems. The size of the free extents is also  very important.  
REM    If you have a lot of small extents (too small for any next   
REM    extent size) but the total bytes of free space is large, then  
REM    you may want to consider defragmentation options.  
REM ------------------------------------------------------------------------
REM DISCLAIMER:
REM    This script is provided for educational purposes only. It is NOT  
REM    supported by Oracle World Wide Technical Support.
REM    The script has been tested and appears to work as intended.
REM    You should always run new scripts on a test instance initially.
REM ------------------------------------------------------------------------
REM Main text of script follows:

create table SPACE_TEMP (   
TABLESPACE_NAME        CHAR(30),   
CONTIGUOUS_BYTES       NUMBER)   
/   
   
declare   
  cursor query is select *   
          from dba_free_space   
                  order by tablespace_name, block_id;   
  this_row        query%rowtype;   
  previous_row    query%rowtype;   
total           number;   
   
begin   
  open query;   
  fetch query into this_row;   
  previous_row := this_row;   
  total := previous_row.bytes;   
  loop   
fetch query into this_row;   
     exit when query%notfound;   
     if this_row.block_id = previous_row.block_id + previous_row.blocks then   
        total := total + this_row.bytes;   
        insert into SPACE_TEMP (tablespace_name)   
                  values (previous_row.tablespace_name);   
     else   
        insert into SPACE_TEMP values (previous_row.tablespace_name,   
               total);   
        total := this_row.bytes;   
     end if;   
previous_row := this_row;   
  end loop;   
  insert into SPACE_TEMP values (previous_row.tablespace_name,   
                           total);   
end;   
/   
   
set pagesize 60   
set newpage 0   
set echo off   
ttitle center 'Contiguous Extents Report'  skip 3   
break on "TABLESPACE NAME" skip page duplicate   
spool contig_free_space.lis   
rem   
column "CONTIGUOUS BYTES"       format 999,999,999,999   
column "COUNT"                  format 999999   
column "TOTAL BYTES"            format 999,999,999,999   
column "TODAY"   noprint new_value new_today format a1   
rem   
select TABLESPACE_NAME  "TABLESPACE NAME",   
       CONTIGUOUS_BYTES "CONTIGUOUS BYTES"   
from SPACE_TEMP   
where CONTIGUOUS_BYTES is not null   
order by TABLESPACE_NAME, CONTIGUOUS_BYTES desc;   
   
select tablespace_name, count(*) "# OF EXTENTS",   
         sum(contiguous_bytes) "TOTAL BYTES"   
from space_temp   
group by tablespace_name;   
   
spool off   
   
drop table SPACE_TEMP   
/   

-- 普通表碎片评估方法
col frag format 99.99
col owner format a20;
col table_name format a30;
select * from (
select a.owner,
       a.table_name,
       a.num_rows,
       a.avg_row_len * a.num_rows space,
       b.bytes,
       (a.avg_row_len * a.num_rows / b.bytes) frag,
       to_char(a.last_analyzed,'YYYY-MM-DD')
  from dba_tables a, dba_segments b
where a.table_name = b.segment_name
and a.owner= b.owner
   and a.owner not in
       ('SYS', 'SYSTEM', 'OUTLN', 'DMSYS', 'TSMSYS', 'DBSNMP', 'WMSYS',
        'EXFSYS', 'CTXSYS', 'XDB', 'OLAPSYS', 'ORDSYS', 'MDSYS', 'SYSMAN')
  and (a.avg_row_len * a.num_rows / b.bytes) < 0.7
--  and PARTITIONED='NO'
  order by b.bytes desc)
where rownum <= 50;

-- 单表碎片情况
select a.owner,
       a.table_name,
       a.num_rows,
       a.avg_row_len * a.num_rows space,
       b.bytes,
       (a.avg_row_len * a.num_rows / b.bytes) frag,
       to_char(a.last_analyzed,'YYYY-MM-DD')
  from dba_tables a, dba_segments b
where a.table_name = b.segment_name
and a.owner= b.owner
   and a.owner not in
       ('SYS', 'SYSTEM', 'OUTLN', 'DMSYS', 'TSMSYS', 'DBSNMP', 'WMSYS',
        'EXFSYS', 'CTXSYS', 'XDB', 'OLAPSYS', 'ORDSYS', 'MDSYS', 'SYSMAN')
  and PARTITIONED='NO'
  and a.table_name='TF_B_TRADE_BOOKING'
  and a.owner='UCR_CRM1'
  order by b.bytes desc

-- 分区表碎片评估方法
col frag format 99.99
col table_owner format a20;
col table_name format a30;
col partition_name format a15;

select * from (
select a.table_owner,
       a.table_name,
       a.partition_name,
       a.num_rows,
       a.avg_row_len * a.num_rows space,
       b.bytes,
       (a.avg_row_len * a.num_rows / b.bytes) frag,
       to_char(a.last_analyzed,'YYYY-MM-DD')
  from dba_tab_partitions a, dba_segments b
where a.table_name = b.segment_name
and a.table_owner= b.owner
and a.partition_name = b.PARTITION_NAME
   and a.table_owner not in
       ('SYS', 'SYSTEM', 'OUTLN', 'DMSYS', 'TSMSYS', 'DBSNMP', 'WMSYS',
        'EXFSYS', 'CTXSYS', 'XDB', 'OLAPSYS', 'ORDSYS', 'MDSYS', 'SYSMAN','SH')
  and (a.avg_row_len * a.num_rows / b.bytes) < 0.7
  and b.segment_type='TABLE PARTITION'
  order by b.bytes desc)
where rownum <= 50;


-- 单个分区表碎片情况
select a.table_owner,
       a.table_name,
       a.partition_name,
       a.num_rows,
       a.avg_row_len * a.num_rows space,
       b.bytes,
       (a.avg_row_len * a.num_rows / b.bytes) frag,
       to_char(a.last_analyzed,'YYYY-MM-DD')
  from dba_tab_partitions a, dba_segments b
where a.table_name = b.segment_name
and a.table_owner= b.owner
and a.partition_name = b.PARTITION_NAME
  and b.segment_type='TABLE PARTITION'
  and a.table_name='SERV'
  and a.table_owner='USER_PRODUCT'
  order by 1,2,3

-- 索引碎片评估方法
col tablespace_name format a20;
col owner format a10;
col index_name format a30;
select *
  from (select id.tablespace_name,
               id.owner,
               id.index_name,
               id.blevel,
               sum(sg.bytes) / 1024 / 1024 "size(M)",
               sg.blocks,
               sg.extents
          from dba_indexes id, dba_segments sg
         where id.owner = sg.owner
           and id.index_name = sg.segment_name
           and id.tablespace_name = sg.tablespace_name
           and id.owner not in
               ('SYS', 'SYSTEM', 'USER', 'DBSNMP', 'ORDSYS', 'OUTLN')
           and sg.extents > 100
           and id.blevel >= 3
         group by id.tablespace_name,
                  id.owner,
                  id.index_name,
                  id.blevel,
                  sg.blocks,
                  sg.extents
        having sum(sg.bytes) / 1024 / 1024 > 100
        order by sum(sg.bytes) / 1024 / 1024 desc )
where rownum <= 50;

-- 单索引碎片情况
select id.tablespace_name,
       id.owner,
       id.index_name,
       id.blevel,
       sum(sg.bytes) / 1024 / 1024,
       sg.blocks,
       sg.extents
  from dba_indexes id, dba_segments sg
where id.owner = sg.owner
   and id.index_name = sg.segment_name
   and id.tablespace_name = sg.tablespace_name
   and id.owner ='UCR_CRM1'
   and id.index_name = 'PK_TF_B_TRADE_4G_VAC_PRODUCT'
group by id.tablespace_name,
          id.owner,
          id.index_name,
          id.blevel,
          sg.blocks,
          sg.extents;

-- 索引大小>80%表大小的索引列表
select * from (
         WITH Q AS (
                 SELECT
                        S.OWNER                  A_OWNER,
                        TABLE_NAME               A_TABLE_NAME,
                        INDEX_NAME               A_INDEX_NAME,
                        INDEX_TYPE               A_INDEX_TYPE,
                        I.PARTITIONED             A_PART,                                                                                                
                        SUM(S.bytes) / 1048576   A_MB
                   FROM DBA_SEGMENTS S,
                        DBA_INDEXES  I
                  WHERE S.OWNER = I.owner
                    AND INDEX_NAME = SEGMENT_NAME
                  GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE,PARTITIONED
                 HAVING SUM(S.BYTES) > 1048576 * 100
         )
         SELECT /*+ NO_QUERY_TRANSFORMATION(S) */
                A_OWNER                                    OWNER,
                A_TABLE_NAME                               TABLE_NAME,
                A_INDEX_NAME                               INDEX_NAME,
                A_INDEX_TYPE                               INDEX_TYPE,
               A_PART                       PARTITIONEND,
               A_MB                                       IND_MB,
                sum(bytes)/1024/1024               tab_MB
          FROM  Q,
                dba_segments d
          WHERE
                D.owner(+)= q.A_OWNER AND
                D.segment_NAME = q.A_TABLE_NAME
         GROUP BY
                A_OWNER,
                A_TABLE_NAME,
                A_INDEX_NAME,
                A_INDEX_TYPE,
                A_PART,
                A_MB  HAVING A_MB>0.8*(SUM(bytes) / 1024/1024  )   
         ORDER BY
                A_OWNER,
                A_TABLE_NAME,
                A_INDEX_NAME,
                A_INDEX_TYPE,
                A_MB DESC  
) where rownum <=50;

/*+ 查看Automatic Segment Advisor的Finding结果 */
col task
select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message
  from dba_advisor_findings af, dba_advisor_objects ao
  where ao.task_id = af.task_id
  and ao.object_id = af.object_id

select af.*, ao.*
  from dba_advisor_findings af, dba_advisor_objects ao
  where ao.task_id = af.task_id
  and ao.object_id = af.object_id order by af.task_name, af.object_id

select af.*, ao.*
  from dba_advisor_findings af, dba_advisor_objects ao
  where ao.task_id = af.task_id
  and ao.object_id = af.object_id
  and af.task_name = 'SYS_AUTO_SPCADV_420142062014'

/*+ 只查询可以进行shrink操作的对象 */
select * from DBA_AUTOTASK_CLIENT;

select f.impact, o.type, o.attr1, o.attr2, f.message, f.more_info
  from dba_advisor_findings f, dba_advisor_objects o
where f.object_id = o.object_id
   and f.task_name = o.task_name
   and f.message like '%shrink%'
order by f.impact desc;

select f.*, o.*
  from dba_advisor_findings f, dba_advisor_objects o
where f.object_id = o.object_id
   and f.task_name = o.task_name
   and f.message like '%shrink%'
order by f.impact desc;

/*+ 查看Automatic Segment Advisor的Recommendations结果 */
select tablespace_name, segment_name, segment_type, partition_name,
recommendations, c1 from
table(dbms_space.asa_recommendations('TRUE', 'FALSE', 'FALSE'))
order by segment_name,segment_type

select * from tmp1 order by segment_name,segment_type


-- clustering_factor应该是介于表 block数量 和 表记录数之间 的一个值
-- 若clustering_factor接近表block数量，则说明表中数据具有比较好的跟索引字段一样排序顺序的存储，
-- 通过索引进行 range scan 的代价比较小（需要读取的表块可能比较少）,
-- 若clustering_factor 接近row数量，则说明表中数据和索引字段排序顺序差异很大，杂乱无张,
-- 则通过索引进行 range scan 的代价比较大（需要读取的表块可能更多）。

select sys.clustering_factor('ISAP','TF_NE_ORDER','IS_SUB_PRO') from dual;

select owner,blocks from dba_segments where segment_name='TF_NE_ORDER';
select owner,blocks from dba_segments where segment_name='IDX_TNO_ISSUB';
select sys.clustering_factor('ISAP','TF_NE_ORDER','STATE_CODE') from dual;

select to_char(last_ddl_time,'yyyy-mm-dd hh24:mi:ss') from dba_objects where object_name='TF_NE_ORDER' and owner='ISAP'


select to_char(CREATED,'yyyy-mm-dd hh24:mi:ss') from dba_objects where object_name='TF_NE_ORDER' and owner='ISAP';


select owner, segment_name, sum(bytes) / 1024 / 1024
  from dba_segments
where segment_name in ('PK_PC_INJ_WELL_DOWN_CLASS_DAIL',
                        'UN_DBA01',                     
                        'PK_PC_PRO_WELL_SHUT_CLASS_MON',
                        'IN_PC_PRO_WELL_STAT_DAILY_W',  
                        'TEMP_TEST',                     
                        'IDX_LH_TEMP_PC_WELL_V_0414_5',  
                        'IDX_ORA_5',                     
                        'PK_PC_WELL_GROUP_WELL_DAILY',   
                        'IN_DBA01_RQJH',                 
                        'PK_PC_HEAV_WELL_PROD_DAILY',   
                        'PK_PC_PRO_WELL8',               
                        'PK_PC_PRO_WELL_VOLFACTOR_DAILY',
                        'PK_PC_PROD_CHANGE_FACTOR_DAILY',
                        'PK_PC_PRO_WELL_SHUT_CLA_M_BK16',
                        'PK_DBA01',                     
                        'IN_DBA01_RQ',                  
                        'PK_PC_INJ_PRO_DAILY',           
                        'PK_PC_INJ_VOL_CHAN_FACTOR_DAIL',
                        'PK_PC_OIL_PROD_DAILY',         
                        'IN_DBA01_JH',                  
                        'IN_DBA01_RQ1',                  
                        'PK_PC_HEAV_WELL_PROD_DAILY',   
                        'PK_PC_PRO_WELL_SHUT_CLASS_MON',
                        'PK_PC_STIM_PRO_ENHANCE_MON',   
                        'PK_PC_PRO_WELL_W_N_P',         
                        'PK_PC_PRO_WELL_SHUT_CLASS_MON')
group by owner, segment_name;

select owner, segment_name, sum(bytes) / 1024 / 1024
  from dba_segments
where segment_name in ('TEMP_WELL_MECH_ALL')
group by owner, segment_name;