Oracle SQLチューニング Season2（第１５回）第3章「Object情報分析及び活用方法」

[Function Based Indexの確認 Function生成］


CREATE OR REPLACE FUNCTION XM_FN_LONG_TO_CHAR (
   towner       VARCHAR2,
   tname        VARCHAR2,
   iname        VARCHAR2,
   cposition    VARCHAR2
)
   RETURN VARCHAR2
AS
   expression   VARCHAR2 (32767);
   query   VARCHAR2 (2000);

BEGIN
   query :=
         'select column_expression from  dba_ind_expressions'
         || ' where table_owner     = ' || '''' || towner    || ''''
         || ' and   table_name      = ' || '''' || tname     || ''''
         || ' and   index_name      = ' || '''' || iname     || ''''
         || ' and   column_position = ' || '''' || cposition || '''' ;

   EXECUTE IMMEDIATE query
                INTO expression;

   expression := SUBSTR (expression, 1, 4000);
   RETURN expression;

END;
/

[ INDEX 정보 조회 ]

SELECT CASE
           WHEN column_position = 1
           THEN index_name
           ELSE ''
       END index_name
     , case when column_name like 'SYS%'
            then replace(xm_fn_long_to_char(table_owner
                                          , table_name
                                          , index_name
                                          , column_position),'"','')
            else column_name end  column_name
FROM ( 
SELECT *
      FROM   dba_ind_columns
      WHERE  table_name  = upper(:v_tname)
      AND    index_owner = upper(:v_own)
      ORDER BY index_name, column_position
     );

INDEX_NAME                   COLUMN_NAME
---------------------------- ------------------------------------------------
WWV_FLOW_WORKSHEETS_PK       ID
WWV_FLOW_WORKSHEETS_UNQ_IDX  CASE  WHEN REGION_ID IS NULL THEN FLOW_ID END
                             CASE  WHEN REGION_ID IS NULL THEN OWNER END
                             CASE  WHEN REGION_ID IS NULL THEN FOLDER_ID END
                             CASE  WHEN REGION_ID IS NULL THEN NAME END
WWV_FLOW_WS_IDX1             FLOW_ID
                             PAGE_ID
WWV_FLOW_WS_IDX2             REGION_ID
WWV_FLOW_WS_UNQ_ALIAS_IDX    CASE  WHEN ALIAS IS NOT NULL THEN FLOW_ID END
                             ALIAS

with segs as (
    select /*+ inline */ owner, segment_name, sum(bytes) bytes
    from dba_segments
    where segment_name not like 'BIN%'
    and   owner not in ('SYS', 'SYSTEM', 'XDB', 'DBSNMP', 'OUTLN', 'ORDDATA')
    and   owner not like '%SYS%'
    and   owner not like 'APEX%'
    group by owner, segment_name
    )
, tbase  as (
    select s.owner
         , t.table_name
         , round(sum(s.bytes)/1024/1024,1) mb
         , max(last_analyzed) last_analyzed
    from segs s, dba_tables t
    where s.owner = t.owner
    and   s.segment_name = t.table_name
    group by s.owner, t.table_name
    )
, ibase as (
    select s.owner
         , i.table_name
         , count(distinct i.index_name) cnt
         , round(sum(bytes)/1024/1024,1) mb
    from segs s, dba_indexes i
    where i.index_name = s.segment_name
    and   i.owner = s.owner
    group by s.owner, i.table_name
     )
, seg_stat as (
       SELECT /*+ no_merge */
              owner
            , object_name
            , SUM( logical_reads_delta ) logical_reads_total
            , SUM( physical_reads_delta ) physical_reads_total
            , max(created) created
       FROM   dba_hist_seg_stat a, dba_hist_snapshot b, dba_objects o
       where  1=1
       and   a.snap_id = b.snap_id
       and   a.instance_number = b.instance_number
       and   b.begin_interval_time between sysdate -7
                                       and sysdate
       and   a.obj# = o.object_id
       GROUP  BY owner
                ,object_name
    )
select /*+ leading(s) use_nl(p c) use_hash(i) */
       s.table_name
     , s.owner
     , case when s.mb >= 1024 then round(s.mb/1024,1)||'G'
            when s.mb <= 1024 then s.mb ||'M'
            end tab_size
     , ss.logical_reads_total lread
     , ss.physical_reads_total pread
     , ROUND(( decode( physical_reads_total , 0 , 1 , physical_reads_total )
       /decode( logical_reads_total , 0 , 1 , logical_reads_total ) ) *100 , 1 ) LR_PR_RATE
     , p.partitioning_type              part_type
     , (select column_name
        from dba_PART_KEY_COLUMNS kc
        where kc.name = p.table_name
        and rownum <= 1		)  part_col
     , p.partition_count                part_cnt
     , p.subpartitioning_type           sub_type
     , (select column_name
        from dba_SUBPART_KEY_COLUMNS skc
        where skc.name = p.table_name
        and rownum <= 1		) sub_col
     , (select count(1) from DBA_TAB_SUBPARTITIONS dts
        where dts.table_name = p.table_name
        and   dts.table_owner = p.owner) sub_cnt
     , i.cnt idx_cnt
     , case when i.mb >= 1024 then round(i.mb/1024,1)||'G'
            when i.mb <= 1024 then i.mb ||'M'
            end idx_size
     , s.last_analyzed
     , comments
from tbase s
   , dba_part_tables p
   , all_tab_comments c
   , ibase i
   , seg_stat ss
where 1=1
and   s.mb > 0
and   s.table_name = p.table_name(+)
and   s.owner = p.owner(+)
and   s.table_name = c.table_name(+)
and   s.owner = c.owner(+)
and   s.table_name = i.table_name(+)
and   s.owner = i.owner(+)
and   s.owner = ss.owner(+)
and   s.table_name = ss.object_name(+)
order by to_number(s.mb) desc;

TABLE_NAME   OWNER      TAB_S      LREAD      PREAD LR_PR_RATE PART_TYPE  PART_COL  PART_CNT
------------ ---------- ----- ---------- ---------- ---------- ---------- --------- --------
SALES        SH         15.9M                                  RANGE      TIME_ID         28
CUSTOMERS    SH         12M         6048       1486       24.6                              
SUPPLEMENTAR SH         4M                                                                  
COSTS        SH         3.1M       56832         32         .1 RANGE      TIME_ID         28
PRODUCT_DESC OE         3M                                                                  
FWEEK_PSCAT_ SH         .6M

[ テーブルカラムの統計情報 ］

SELECT column_name 
, data_type 
, data_length 
, decode(data_precision  || '/' || data_scale,'/',null,data_precision  || '/' || data_scale) dpds 
, nullable  nn
, num_distinct
, density
, num_nulls
, num_buckets
, sample_size
, to_char(last_analyzed,'yyyy-mm-dd')  last_anal
FROM   all_tab_columns
WHERE  owner = upper(trim(:schname))
AND    table_name = upper(trim(':tname'));

COLUMN_NAME                    DATA_TYPE    LEN SCAL N        NFV      DENSITY  NUM_NULLS BUCKET SAMPLE_SIZE LAST_ANAL
------------------------------ ---------- ----- ---- - ---------- ------------ ---------- ------ ----------- ----------
PROD_ID                        NUMBER        22      N         72  0.013888889          0      1      918843 2015-06-25
CUST_ID                        NUMBER        22      N       7059  0.000141663          0      1      918843 2015-06-25
TIME_ID                        DATE           7      N       1460  0.000684932          0      1      918843 2015-06-25
CHANNEL_ID                     NUMBER        22      N          4  0.250000000          0      1      918843 2015-06-25
PROMO_ID                       NUMBER        22      N          4  0.250000000          0      1      918843 2015-06-25
QUANTITY_SOLD                  NUMBER        22 10/2 N          1  1.000000000          0      1      918843 2015-06-25
AMOUNT_SOLD                    NUMBER        22 10/2 N       3586  0.000278862          0      1      918843 2015-06-25

[ テーブル統計情報 ]

SELECT   table_name||'('||owner||')' || chr(10) ||tablespace_name||decode(partitioned,'YES','* Partitioned ','') || decode(temporary,'Y','* Temporary ','')  as "TAB_INFO"
, trunc(num_rows) num_rows
, avg_row_len
, blocks || chr(10) || empty_blocks as "BLK_INFO"
, trim(degree) degree
, avg_space
, chain_cnt
, pct_free || '/' || pct_used || '/' || pct_increase pct
, ini_trans || '/' ||max_trans tran
, decode(sign(floor(initial_extent/1024/1024)),1,round(initial_extent/1024/1024)||'m',round(initial_extent/1024)||'k') || 
'/' || decode(sign(floor(next_extent/1024/1024)),1,round(next_extent/1024/1024) || 'm',
round(next_extent/1024)||'k') || chr(10) || min_extents ||'/'||
decode(max_extents,2147483645,'Unlimit',max_extents) inext
, freelists || '/' || freelist_groups free
, to_char(last_analyzed,'yyyy-mm-dd:hh24:mi:ss')  last_anal
FROM   all_tables
WHERE  table_name  = upper(trim(:tname))
  AND   owner = upper(trim(:schname))
UNION ALL
SELECT   table_name || ':' || partition_name ||chr(10) || tablespace_name as "TAB_INFO"
, trunc(num_rows) num_rows
, avg_row_len
, blocks || chr(10) || empty_blocks as "BLK_INFO"
, (select trim(degree) degree from all_tables where owner=upper(trim('&schname')) and table_name=trim(upper('&&tname')))
, avg_space
, chain_cnt
, pct_free || '/' || pct_used || '/' || pct_increase pct
, ini_trans || '/' ||max_trans tran
, decode(sign(floor(initial_extent/1024/1024)),1,round(initial_extent/1024/1024)||'m',round(initial_extent/1024)||'k') || 
'/' || decode(sign(floor(next_extent/1024/1024)) ,1,round(next_extent/1024/1024)|| 'm',round(next_extent/1024)||'k')    
|| chr(10) || min_extent ||'/'|| decode(max_extent,2147483645,'Unlimit',max_extent) inext
, freelists || '/' || freelist_groups free
, to_char(last_analyzed,'yyyy-mm-dd')  last_anal
FROM   all_tab_partitions
WHERE  table_name = upper(trim(:tname))
AND    table_owner = upper(trim(:schname))
ORDER BY  "TAB_INFO";
                                                    AVG                              PCTFREE           Inital/
TABLE NAME                                       ROW BLOCKS    DEG   AVG    CHAIN PCTUSED   INITRAN Next/      FLst/
TABLESPACE NAME                       NUM_ROWS   LEN EMP.B     REE SPACE      CNT PCTINCR   MAXTRAN MIN/MAX    FGrp  LAST_ANAL
----------------------------------- ---------- ----- --------- --- ----- -------- --------- ------- ---------- ----- -------------------
SALES_IN(SH)                            918843    29 4513      1       0        0 10//      1/255   64k/1m     /     2015-06-25:13:32:19
USERS                                                0