L
o
a
d
i
n
g
.
.
.

ホーム

お知らせ

製品・ソリューション

サービス

導入事例・パートナー

EXEM Academy・ブログ

会社情報

採用情報

2024.06.11

SQLチューニング 2nd Season(第15回) 第3章 Object情報分析及び活用方法

今回のSQLチューニングブログ 2nd season(第15回)は「 object情報分析及び活用方法 」について解説していきます。

3.2 Object情報分析及び活用方法

SQLチューニングを行う時、SQLに使用されるObject情報を把握することは必然的なプロセスです。
SQLで使用するテーブル、パーティション、インデックス、関数、プロシージャー、ビューなどに関する情報をきちんと把握していない
状態で性能改善を行うことは簡単な作業ではありません。 

— Table とそれに伴うIndex情報、TableのPartitionの有無などを事前に把握せずに性能改善をすることも不可能です。
更には、Indexに含まれるカラムとその順序、TableのサイズとPartitioningの有無などについても事前に把握しておきましょう。
これらのプロセスを行わないままチューニングを進めてしまうと、正しい性能改善を行うことができません。

3.2.1 インデックス構成情報を把握

SQLの性能改善においてIndexの存在は絶対的です。
Where節でカラムの比較がどうなるかによってIndexのScanが可能であるかどうか、Indexの効率性などが決定されます。
まずIndexがどのように構成されているかを把握することはとても基本的なことで、当然通過しなければならないプロセスです。
テーブルに生成されたIndex構成情報は、Dictionary Viewを通じて確認することができます。
dba_indexes、dba_ind_columns、dba_ind_expressionsなどを通じてIndex構成情報を確認することができます。

尚、抽出する方法は下記の通りとなります。

[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

3.2.2 テーブル構成情報を活用する

テーブルの構成情報を把握することも性能改善時には、非常に重要な情報となります。
当該テーブルがパーティションになっているか、パーティションテーブルであれば、パーティションキーはどのようなカラムで
構成されているかについては、きちんと確認しておく必要があります。
また、非パーティションテーブルの場合、当該テーブルのサイズとSQLの使用目的、I/O量、Where節に共通的に入力される
条件などを考慮して、今後パーティションに変更する時、テーブル構成情報を確認することは非常に重要です。
dba_tables、dba_tab_columns、dba_tab_parititons、dba_part_key_columns、dba_subpart_key_columns、dba_segments、dba_hist_seg_statなどを通じてテーブル構成情報を確認することができ、抽出方法は下記の通りです。

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

また、テーブルの統計情報も非常に重要です。
該当カラムの効率を判断することができれば、テーブルのIndexを構成する場合、多くの助けを得ることができます。
テーブル構成カラムの情報を確認することができる抽出方法は下記の通りです。

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

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             
SQLチューニングブログ 2nd Season(第15回) 終
次回SQLチューニングブログは・・・

SQLチューニングブログ 2nd Season(第16回)
「業務情報分析及び活用方法」について

私たちは、日本のITインフラにおける
プロジェクト運営 ~ システム運用 の安定化と効率化を推進します。

日本エクセムのデータベースソリューション

最新情報は公式SNSでも好評発信中!!

画像に alt 属性が指定されていません。ファイル名: 日本エクセム公式Xロゴ.png
画像に alt 属性が指定されていません。ファイル名: 日本エクセム公式Facebookロゴ.png

SQLチューニングブログについてのお問合せは

日本エクセム株式会社
営業推進部 まで
sales@ex-em.co.jp


PHP Code Snippets Powered By : XYZScripts.com