Friday, May 4, 2012

Indexes ... indexes, life story

Recently I had a tipical life situation in mine ordinary DBA tuning task. I was asked to see if there is anything tunable in ad hoc way. Do not want to elaborate about ASH and it's known queries, but to explain one other, mine, approach, following thought hat biggest tables probably makes biggest problems.

This approach is sometimes fastest way to tune unknown database in relatively quick way.

To find biggest (in all mean ... by records and size) I use custom made tool, db_size_stat, explained in previous topic Monitor database size (part II).

Here are starting two queries for that:
SQL> select * from table(db_size_pkg.TOP_N_TABLES_BY_SIZE_NOW_PR (5));

OWNER      SEGMENT_NAME                   SEGMENT_TYPE            SIZE_MB    RECORDS
---------- ------------------------------ -------------------- ---------- ----------
TAB        XX_CASH_UP_DETAIL_T            TABLE                34786.4375  344092877
TAB        XX_TRANSACTION_OK_T            TABLE                14176.4375   49417035
YYY        XX_TRANSACTION_T               TABLE                13763.6875   35368073
YYY        XX_TRANSACTION_ULAZ_T          TABLE                  8469.875   28162972
TOOLS      AUD$_ARCH                      TABLE                      5293    5569440

SQL> select * from table(db_size_pkg.TOP_N_TABLES_BY_RECORDS_NOW_PR (5));

OWNER      SEGMENT_NAME                   SEGMENT_TYPE            SIZE_MB    RECORDS
---------- ------------------------------ -------------------- ---------- ----------
YYY        XX_CASH_UP_DETAIL_T            TABLE                34786.4375  344092877
YYY        XX_TRANSACTION_OK_T            TABLE                14176.4375   49417035
YYY        XX_TRANSACTION_T               TABLE                13763.6875   35368073
YYY        XX_SUBSCRIPTION_SALDO          TABLE                      4529   32349603
YYY        XX_TRANSACTION_PRIHOD_T        TABLE                 3735.0625   30577170
Normally I choose first one and start to see it's indexes and other important details:
SQL> @tbl_info YYY XX_CASH_UP_DETAIL_T
TABLE:XX_CASH_UP_DETAIL_T
TABLE/YYY/XX_CASH_UP_DETAIL_T
RECS:   324,254,534 BLKS:     4,186,049   MB: 32,703.51   AVGLEN:86
SMPL:   324,254,534 ANAL: 02.03.2012 20:29:05
----------------------------------------------
COL  1:ID_RESEAU                      TYP:VARCHAR2   VALS:           3 DENS:       0 NULLS:           0 HIST:   3
COL  2:ID_SITE                        TYP:VARCHAR2   VALS:          35 DENS:       0 NULLS:           0 HIST:  35
COL  3:MATRICULE                      TYP:VARCHAR2   VALS:         716 DENS:   .0015 NULLS:           0 HIST: 254
COL  4:DATE_REDDITION                 TYP:DATE       VALS:     370,912 DENS:       0 NULLS:           0 HIST:   1
COL  5:SAC                            TYP:NUMBER     VALS:       9,999 DENS:   .0001 NULLS:           0 HIST:   1
COL  6:CORRECTION_STATUS              TYP:VARCHAR2   VALS:           8 DENS:    .125 NULLS:           0 HIST:   1
COL  7:ID_PAIEMENT                    TYP:NUMBER     VALS:           7 DENS:   .1429 NULLS:           0 HIST:   1
COL  8:LIBELLE_PAIEMENT               TYP:VARCHAR2   VALS:       1,480 DENS:   .0007 NULLS:           0 HIST:   1
COL  9:ID_CLASSE                      TYP:NUMBER     VALS:           6 DENS:   .1667 NULLS:           0 HIST:   1
COL 10:MONTANT_PAIEMENT               TYP:NUMBER     VALS:         502 DENS:    .002 NULLS:           0 HIST:   1
COL 11:DEVISE_PAIEMENT                TYP:VARCHAR2   VALS:          16 DENS:   .0625 NULLS:           0 HIST:   1
COL 12:NB_PAIEMENT                    TYP:NUMBER     VALS:         694 DENS:   .0014 NULLS:           0 HIST:   1
COL 13:MONTANT_TOTAL                  TYP:NUMBER     VALS:       6,856 DENS:   .0001 NULLS:           0 HIST:   1
COL 14:INSERT_DATE                    TYP:DATE       VALS:   1,784,326 DENS:       0 NULLS:           0 HIST:   1
COL 15:DATKRE                         TYP:DATE       VALS:      98,474 DENS:       0 NULLS:           0 HIST:   1
COL 16:KORKRE                         TYP:VARCHAR2   VALS:           1 DENS:       1 NULLS:           0 HIST:   1
COL 17:DATPRO                         TYP:DATE       VALS:           0 DENS:       0 NULLS: 324,254,534 HIST:   0
COL 18:KORPRO                         TYP:VARCHAR2   VALS:           0 DENS:       0 NULLS: 324,254,534 HIST:   0
COL 19:IR_BROJ                        TYP:NUMBER     VALS:           0 DENS:       0 NULLS: 324,254,534 HIST:   0
COL 20:DATE_MESSAGE                   TYP:DATE       VALS:   1,142,770 DENS:       0 NULLS:           0 HIST:   1
----------------------------------------------
Indexes:
XXD_CASH_UP_I                  NORMAL                    LBLKS: 2,407,524    KEYS:   1,172,204    CLUSTR:   8,790,457
             20,578.125 MB  Index quality:5 (Excellent)  Analyzed:03.03.2012 00:43:53
                1 ID_RESEAU
                2 ID_SITE
                3 MATRICULE
                4 DATE_REDDITION
                5 SAC
                6 DATE_MESSAGE
XXD_CASH_UP_IX_REDD            NORMAL                    LBLKS: 1,026,283    KEYS:     370,912    CLUSTR:   8,599,816
              8,795.625 MB  Index quality:5 (Excellent)  Analyzed:03.03.2012 02:55:40
                1 DATE_REDDITION
XXD_PAIEMENT_I                 NORMAL                    LBLKS:   788,011    KEYS:           7    CLUSTR:  13,541,831
              6,760.375 MB  Index quality:5 (Excellent)  Analyzed:03.03.2012 01:39:54
                1 ID_PAIEMENT
------------------
Total: 32,703.51/ 36,134.13 MB
Index goodnes:-0.105
                Score is -1.000 (worst) to 1.000 (best)
============================================================================================
where description of colums are:
         VALS - number of different non null values!
         DENS - http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2969235095639
                Density is a statistic used by the Cost Based Optimizer to give selectivity 
                estimates for columns where better information is unavailable (i.e. from histograms etc.). 
                Density is a column statistic and provides selectivity estimates for equi-join predicates 
                (e.g. and A.COL1 = B.COL1) and equality predicates (e.g. COL1 = 5).
                The density is expressed as a decimal number between 0 and 1. 
                    Values close to 1 indicate that this column is unselective
                    Values close to 0 indicate that this column is highly selective 
                When the WHERE clause includes a column(s) with a bind variable; 
                    for example: column x = :variable_y                      
         NULLS- Number of nulls values in column (according table statistic)
         HIST - represent num_buckets
                num_buckets = 1 ( = max(endpoint_number) ==> no histogram)
                num_buckets = num_distinct-1 ==> frequency histogram
                num_buckets = max(endpoint_number) ==> HB histogram without popular values 
                             ( this includes case one, treating the entire value range as one big bucket)
                num_buckets < max(endpoint_number) ==> HB histogram with popular values           
                Verifying Histogram Statistics
Here is mine tbl_info script:
/* ---------------------------------------------------------------------------
           
          Copyright(C) 1997-2011 TEB Informatika d.o.o.


 CR/TR#  : tbl_info.sql
 Purpose : Shows important details for chosen table
 Usage   : tbl_info OWNER table <table array> 
           i.e.
           1 table    : tbl_info TAB NC_SUBSCRIPTION_SALDO
           Many tables: tbl_info TAB NC_SUBSCRIPTION_SALDO','NC_BESPLATNI
 Example: 
 Changes (DD.MM.YYYY, Name, CR/TR#):
 --------------------------------------------------------------------------- */
set serveroutput on size 123456;
set linesize 140
set pagesize 120

DECLARE
  t_tabs   SYS.DBMS_DEBUG_VC2COLL;
  v_tab    VARCHAR2 (100);
  v_own    VARCHAR2 (100);
  v_typ    VARCHAR2 (100);
  v_user   VARCHAR2 (34) ;
  v_long   VARCHAR2 (32000);
  v_ind    NUMBER;
  i PLS_INTEGER;
  v_sum_indx_size NUMBER;
  v_sum_tbl_size NUMBER;
  --
  CURSOR c_obj IS
    SELECT object_type, owner
      FROM dba_objects
     WHERE object_name = v_tab AND 
           owner IN ('PUBLIC', v_own)
    ORDER BY DECODE (owner, v_own, 1, 2);

  --
  CURSOR c_syn IS
    SELECT table_owner, table_name
      FROM dba_synonyms
     WHERE synonym_name = v_tab AND 
           owner IN ('PUBLIC', v_own)
    ORDER BY DECODE (owner, v_own, 1, 2);

  --
  CURSOR c_vw IS
    SELECT text
      FROM dba_views
     WHERE owner = v_own AND 
           view_name = v_tab;

  --
  CURSOR c_tab IS
    SELECT table_name,
           num_rows,
           blocks,
           blocks*tblspc.block_size/1048576 size_mb,
           avg_row_len,
           sample_size,
           last_analyzed
      FROM dba_tables, dba_tablespaces tblspc
     WHERE table_name = v_tab AND 
           owner = v_own AND
           tblspc.TABLESPACE_NAME=dba_tables.TABLESPACE_NAME;

  --
  CURSOR c_tcols IS
    SELECT column_name,
           data_type,
           num_distinct,
           density,
           num_nulls,
           num_buckets,
           last_analyzed,
           avg_col_len,
           RAWTOHEX (low_value) low,
           RAWTOHEX (high_value)
      FROM dba_tab_columns
     WHERE table_name = v_tab AND 
           owner = v_own;

  --
  CURSOR c_ind IS
    SELECT 
          i.index_name,
          i.index_type, 
          i.distinct_keys,
          i.leaf_blocks,
          o.bytes/1048576 size_MB,
          i.avg_data_blocks_per_key, 
          i.avg_leaf_blocks_per_key,
          i.clustering_factor,
          i.last_analyzed,
          CASE when nvl(i.clustering_factor,0) = 0                                     then '0 (????)'  -- function index!?
               when nvl(t.num_rows,0) = 0                                              then '0 (No Stats)'
               when (round(i.clustering_factor / t.num_rows * 100)) < 6                then '5 (Excellent)'
               when (round(i.clustering_factor / t.num_rows * 100)) between 7 and 11   then '4 (Very Good)'
               when (round(i.clustering_factor / t.num_rows * 100)) between 12 and 15  then '2 (Good)'
               when (round(i.clustering_factor / t.num_rows * 100)) between 16 and 25  then '2 (Fair)'
               else                                                                         '1 (Poor)' 
          END Index_Quality
     FROM dba_indexes i, 
          dba_segments o, 
          dba_tables t
    WHERE i.owner = v_own              AND
          i.table_name = v_tab         AND 
          i.owner=t.owner              AND
          i.table_name = t.table_name  AND 
          i.owner=o.owner              AND
          i.index_name = o.segment_name
    ORDER BY index_name
  ;

  --
  CURSOR c_icols (p_iname VARCHAR2) IS
    SELECT c.index_name,
           c.column_position,
           c.column_name,
           e.column_expression
      FROM dba_ind_columns c, dba_ind_expressions e
     WHERE e.index_owner(+) = c.index_owner           AND
           e.index_name(+) = c.index_name             AND
           e.column_position(+) = c.column_position   AND
           c.table_name = v_tab                       AND
           c.table_owner = v_own                      AND
           c.index_name = p_iname
    ORDER BY 1, 2;

  --
  PROCEDURE sp_out (p_text IN VARCHAR2, p_wrap IN NUMBER DEFAULT 1)
  IS
    v_text       VARCHAR2 (32500);
    v_point      NUMBER;
    e_overflow   EXCEPTION;
    PRAGMA EXCEPTION_INIT (e_overflow, -20000);
    v_temp_arr   DBMS_OUTPUT.chararr;
    v_lines      NUMBER := 150;
  BEGIN
    DBMS_OUTPUT.PUT_LINE  (p_text);
    return;
    v_text := p_text;

    --Break it down into lengths up 248 characters in length
    -- If p_wrap is 1, it will try to 'wrap' the line at word breaks
    WHILE v_text IS NOT NULL LOOP
       v_point := LENGTH (v_text) + 1;

       IF v_point > 248 AND p_wrap = 1 THEN
         v_point := INSTR (SUBSTR (v_text, 1, 248), CHR (10), -1);
       END IF;

       IF v_point = 0 AND p_wrap = 1 THEN
         v_point := INSTR (SUBSTR (v_text, 1, 248), ' ', -1);
       END IF;

       IF v_point = 0 AND p_wrap = 1 THEN
         v_point := INSTR (SUBSTR (v_text, 1, 248), '/', -1);
       END IF;

       IF v_point = 0 THEN
         v_point := 248;
       END IF;

       BEGIN
         DBMS_OUTPUT.PUT_LINE (SUBSTR (v_text, 1, v_point - 1));
       EXCEPTION
         WHEN e_overflow THEN
           IF SUBSTR (SQLERRM, 12, 9) = 'ORU-10027' THEN
             --Clear out the oldest 150 lines to make room
             --in the DBMS_OUTPUT buffer
             DBMS_OUTPUT.GET_LINES (V_TEMP_ARR, V_LINES);
             DBMS_OUTPUT.PUT_LINE (SUBSTR (v_text, 1, v_point - 1));
           ELSE
             RAISE;
           END IF;
       END;
       v_text := SUBSTR (v_text, v_point + 1);
    END LOOP;
  END;
-- mai part
BEGIN
  v_user := '&1';
  t_tabs := sys.DBMS_DEBUG_VC2COLL ('&2');

  v_ind := t_tabs.FIRST;

  WHILE v_ind IS NOT NULL LOOP
    v_tab := t_tabs (v_ind);
    sp_out ('TABLE:' || v_tab);
    v_own := v_user;

    OPEN c_obj;

    FETCH c_obj INTO v_typ, v_own;

    IF c_obj%NOTFOUND THEN
      CLOSE c_obj;
      RAISE NO_DATA_FOUND;
    END IF;

    CLOSE c_obj;

    --
    WHILE v_typ = 'SYNONYM' LOOP
      OPEN c_syn;
      FETCH c_syn INTO v_own, v_tab;
      CLOSE c_syn;
      sp_out ('SYN:' || v_typ || '/' || v_own || '/' || v_tab);
      OPEN c_obj;
      FETCH c_obj INTO v_typ, v_own;
      CLOSE c_obj;
    END LOOP;

    --
    sp_out (v_typ || '/' || v_own || '/' || v_tab);

    IF v_typ = 'VIEW' THEN
      OPEN c_vw;
      FETCH c_vw INTO v_long;
      CLOSE c_vw;
      sp_out (v_long);
    ELSE
      FOR rec IN c_tab LOOP
        v_sum_indx_size := 0;
        sp_out (
                'RECS:'
             || to_char(rec.num_rows, '9G999G999G990')
             || ' BLKS:'
             || to_char(rec.blocks, '9G999G999G990')
             || '   MB:'
             || to_char(rec.size_mb, '99G990D90')
             || '   AVGLEN:'
             || rec.avg_row_len);
        sp_out (
                'SMPL:'
             || to_char(rec.sample_size, '9G999G999G990')
             || ' ANAL: '
             || TO_CHAR (rec.last_analyzed, 'DD.MM.YYYY HH24:MI:SS'));
        sp_out ('----------------------------------------------');
        v_sum_tbl_size := rec.size_mb;
      END LOOP;
      i:=0;
      FOR rec IN c_tcols LOOP
        i := i+1;
        sp_out (
                'COL' || to_char(i,'90') || ':'
             || RPAD (rec.column_name, 30)
             || ' TYP:'
             || RPAD (rec.data_type, 10)
             || ' VALS:'
             || TO_CHAR (rec.num_distinct, '999G999G990')
             || ' DENS:'
             || LPAD (ROUND (rec.density, 4), 8)
             || ' NULLS:'
             || TO_CHAR (rec.num_nulls, '999G999G990')
             || ' HIST:'
             || LPAD (rec.num_buckets, 4));
       END LOOP;
       
       sp_out ('----------------------------------------------');
       
       sp_out ('Indexes:');
       FOR rec IN c_ind LOOP
           v_sum_indx_size := v_sum_indx_size + rec.size_MB;
           sp_out (
                RPAD (rec.index_name, 30)
             || ' '
             || RPAD (rec.index_type, 20)
             || '      LBLKS:'
             || TO_CHAR (rec.leaf_blocks, '9G999G990')
             || '    KEYS:'
             || TO_CHAR (rec.distinct_keys, '999G999G990')
             || '    CLUSTR:'
             || TO_CHAR (rec.clustering_factor, '999G999G990'));             
           sp_out (
                CHR (9)
             || LPAD(TO_CHAR (rec.size_MB, '99G990D990'),15)
             || ' MB  Index quality:'
             || RPAD (rec.index_quality,14)
             || ' Analyzed:'
             || TO_CHAR (rec.last_analyzed, 'DD.MM.YYYY HH24:MI:SS'));
         FOR rec2 IN c_icols (rec.index_name) LOOP
           v_long := rec2.column_expression;
           IF v_long IS NOT NULL THEN
             v_long := '         ' || SUBSTR (v_long, 1, 150);
           END IF;
           sp_out (
                CHR (9)
             || CHR (9)
             -- || ' POS:'
             || rec2.column_position
             || ' '
             || RPAD (rec2.column_name, 30)
             || v_long);
         END LOOP;
       END LOOP;
       sp_out ('------------------');
       sp_out ('Total:'|| to_char(v_sum_tbl_size,'99G990D90')||'/'||to_char(v_sum_indx_size,'99G990D90') ||' MB');
       sp_out ('Index goodnes:'||to_char(((1-(v_sum_indx_size/v_sum_tbl_size))),'0D990'));
       sp_out (chr(9)||chr(9)||'Score is -1.000 (worst) to 1.000 (best)');
    END IF;
    sp_out ('============================================================================================');
    sp_out(chr(9));
    v_ind := t_tabs.NEXT (v_ind);
  END LOOP;
  DBMS_OUTPUT.put_line ('Done.');
END;
/

PROMPT usage table_info OWNER table_name 
PROMPT usage table_info OWNER table_name','table_name2 ...
To see all picture I called db_size_pkg.GROW_HIST_ONE_SEGMENT for mentioned 4 segments (table+3 indxes). Result is here:
YYY.XX_CASH_UP_DETAIL_T(TABLE)
         Period     SIZE MB   TREND MB        TREND %      CUMUL %          RECORDS      TREND REC        TREND %         CUMUL %
        --------  --------------------------------------------------    -----------------------------------------------------------
        20100401   14.080,00   14.080,00          0,00          0,00      138.283.379   138.283.379            0,00            0,00
        20100501   15.104,00    1.024,00          7,27          7,27      148.038.809     9.755.430            7,05            7,05
        20100601   16.000,00      896,00          5,93         13,64      157.881.982     9.843.173            6,65           14,17
        20100701   16.959,00      959,00          5,99         20,45      167.597.811     9.715.829            6,15           21,20
        20100801   18.047,00    1.088,00          6,42         28,17      131.642.170   -35.955.641          -21,45           -4,80
        20100901   18.047,00        0,00          0,00         28,17      142.593.105    10.950.935            8,32            3,12
        20101001   18.047,00        0,00          0,00         28,17      152.581.707     9.988.602            7,00           10,34
        20101101   18.047,00        0,00          0,00         28,17      162.457.621     9.875.914            6,47           17,48
        20101201   18.047,00        0,00          0,00         28,17      171.849.364     9.391.743            5,78           24,27
        20110101   18.431,00      384,00          2,13         30,90      181.811.901     9.962.537            5,80           31,48
        20110201   19.341,44      910,44          4,94         37,37      191.463.063     9.651.162            5,31           38,46
        20110301   20.236,44      895,00          4,63         43,72      200.237.474     8.774.411            4,58           44,80
        20110401   21.260,44    1.024,00          5,06         51,00      210.132.211     9.894.737            4,94           51,96
        20110501   22.205,44      945,00          4,44         57,71      219.391.022     9.258.811            4,41           58,65
        20110601   23.136,44      931,00          4,19         64,32      229.119.594     9.728.572            4,43           65,69
        20110701   24.234,44    1.098,00          4,75         72,12      238.971.012     9.851.418            4,30           72,81
        20110801   25.386,44    1.152,00          4,75         80,30      250.716.669    11.745.657            4,92           81,31
        20110901   26.474,44    1.088,00          4,29         88,03      262.031.476    11.314.807            4,51           89,49
        20111001   27.562,44    1.088,00          4,11         95,76      272.403.807    10.372.331            3,96           96,99
        20111101   28.586,44    1.024,00          3,72        103,03      282.374.433     9.970.626            3,66          104,20
        20111201   29.602,44    1.016,00          3,55        110,24      292.654.644    10.280.211            3,64          111,63
        20120101   30.626,44    1.024,00          3,46        117,52      303.123.028    10.468.384            3,58          119,20
        20120201   31.714,44    1.088,00          3,55        125,24      313.630.238    10.507.210            3,47          126,80
        20120301   32.674,44      960,00          3,03        132,06      323.487.403     9.857.165            3,14          133,93
        20120401   33.762,44    1.088,00          3,33        139,79      334.051.188    10.563.785            3,27          141,57
        20120501   34.786,44    1.024,00          3,03        147,06      344.092.877    10.041.689            3,01          148,83
        ===========================================================================================================================


YYY.XXD_CASH_UP_I(INDEX)
         Period     SIZE MB   TREND MB        TREND %      CUMUL %
        --------  --------------------------------------------------
        20100401    8.704,00    8.704,00          0,00          0,00
        20100501    8.960,00      256,00          2,94          2,94
        20100601    7.808,00   -1.152,00        -12,86        -10,29
        20100701    8.512,00      704,00          9,02         -2,21
        20100801    9.344,00      832,00          9,77          7,35
        20100901    9.344,00        0,00          0,00          7,35
        20101001    9.344,00        0,00          0,00          7,35
        20101101    9.344,00        0,00          0,00          7,35
        20101201    9.984,00      640,00          6,85         14,71
        20110101   10.736,00      752,00          7,53         23,35
        20110201    9.433,13   -1.302,88        -12,14          8,38
        20110301   10.073,13      640,00          6,78         15,73
        20110401   10.827,13      754,00          7,49         24,39
        20110501   11.467,13      640,00          5,91         31,75
        20110601   12.171,13      704,00          6,14         39,83
        20110701   12.875,13      704,00          5,78         47,92
        20110801   13.729,13      854,00          6,63         57,73
        20110901   14.561,13      832,00          6,06         67,29
        20111001   15.335,13      774,00          5,32         76,18
        20111101   16.039,13      704,00          4,59         84,27
        20111201   16.807,13      768,00          4,79         93,10
        20120101   17.511,13      704,00          4,19        101,18
        20120201   18.279,13      768,00          4,39        110,01
        20120301   18.983,13      704,00          3,85        118,10
        20120401   19.746,13      763,00          4,02        126,86
        20120501   20.514,13      768,00          3,89        135,69
        ============================================================

YYY.XXD_CASH_UP_IX_REDD(INDEX)
         Period     SIZE MB   TREND MB        TREND %      CUMUL %
        --------  --------------------------------------------------
        20100401    3.584,00    3.584,00          0,00          0,00
        20100501    3.840,00      256,00          7,14          7,14
        20100601    3.392,00     -448,00        -11,67         -5,36
        20100701    3.712,00      320,00          9,43          3,57
        20100801    4.032,00      320,00          8,62         12,50
        20100901    4.032,00        0,00          0,00         12,50
        20101001    4.032,00        0,00          0,00         12,50
        20101101    4.032,00        0,00          0,00         12,50
        20101201    4.288,00      256,00          6,35         19,64
        20110101    4.608,00      320,00          7,46         28,57
        20110201    4.047,63     -560,38        -12,16         12,94
        20110301    4.367,63      320,00          7,91         21,86
        20110401    4.635,63      268,00          6,14         29,34
        20110501    4.955,63      320,00          6,90         38,27
        20110601    5.211,63      256,00          5,17         45,41
        20110701    5.531,63      320,00          6,14         54,34
        20110801    5.979,63      448,00          8,10         66,84
        20110901    6.171,63      192,00          3,21         72,20
        20111001    6.555,63      384,00          6,22         82,91
        20111101    6.875,63      320,00          4,88         91,84
        20111201    7.195,63      320,00          4,65        100,77
        20120101    7.451,63      256,00          3,56        107,91
        20120201    7.835,63      384,00          5,15        118,63
        20120301    8.091,63      256,00          3,27        125,77
        20120401    8.411,63      320,00          3,95        134,70
        20120501    8.667,63      256,00          3,04        141,84
        ============================================================

YYY.XXD_PAIEMENT_I(INDEX)
         Period     SIZE MB   TREND MB        TREND %      CUMUL %
        --------  --------------------------------------------------
        20100401    2.688,00    2.688,00          0,00          0,00
        20100501    2.944,00      256,00          9,52          9,52
        20100601    2.496,00     -448,00        -15,22         -7,14
        20100701    2.752,00      256,00         10,26          2,38
        20100801    3.008,00      256,00          9,30         11,90
        20100901    3.008,00        0,00          0,00         11,90
        20101001    3.008,00        0,00          0,00         11,90
        20101101    3.008,00        0,00          0,00         11,90
        20101201    3.200,00      192,00          6,38         19,05
        20110101    3.520,00      320,00         10,00         30,95
        20110201    3.005,38     -514,63        -14,62         11,81
        20110301    3.265,38      260,00          8,65         21,48
        20110401    3.495,38      230,00          7,04         30,04
        20110501    3.687,38      192,00          5,49         37,18
        20110601    3.943,38      256,00          6,94         46,70
        20110701    4.135,38      192,00          4,87         53,85
        20110801    4.455,38      320,00          7,74         65,75
        20110901    4.711,38      256,00          5,75         75,27
        20111001    4.967,38      256,00          5,43         84,80
        20111101    5.287,38      320,00          6,44         96,70
        20111201    5.479,38      192,00          3,63        103,85
        20120101    5.735,38      256,00          4,67        113,37
        20120201    5.991,38      256,00          4,46        122,89
        20120301    6.183,38      192,00          3,20        130,04
        20120401    6.504,38      321,00          5,19        141,98
        20120501    6.696,38      192,00          2,95        149,12
        ============================================================
Obviously chosen table has along with indexes around 70.67GB in disk size with more then 344 M records. If you compare data from whole database perspective, then you realize what this numbers represent:
SQL> exec db_size_pkg.get_db_size;

PERIOD         SIZE MB        TREND MB           RECORDS        TREND REC         REC/1 MB     TR REC/1 MB
==========================================================================================================
20100401     212.396,06           0,00         547.508.368               0        2.577,77            0,00
20100501     214.708,25       2.312,19         560.250.058      12.741.690        2.609,36        5.510,66
20100601     216.058,38       1.350,13         570.327.760      10.077.702        2.639,69        7.464,27
20100701     222.598,19       6.539,81         583.152.070      12.824.310        2.619,75        1.960,96
20100801     231.681,31       9.083,13         563.291.109     -19.860.961        2.431,32       -2.186,58
20100901     238.611,50       6.930,19         586.688.618      23.397.509        2.458,76        3.376,17
20101001     242.191,75       3.580,25         596.948.818      10.260.200        2.464,78        2.865,78
20101101     251.025,25       8.833,50         613.029.785      16.080.967        2.442,10        1.820,45
20101201     268.175,00      17.149,75         644.685.678      31.655.893        2.403,97        1.845,85
20110101     294.427,38      26.252,38         656.266.145      11.580.467        2.228,96          441,12
20110201     258.931,69     -35.495,69         666.924.336      10.658.191        2.575,68         -300,27
20110301     259.844,75         913,06         675.320.474       8.396.138        2.598,94        9.195,58
20110401     263.566,19       3.721,44         690.339.150      15.018.676        2.619,23        4.035,72
20110501     272.683,69       9.117,50         709.499.198      19.160.048        2.601,91        2.101,46
20110601     275.960,56       3.276,88         724.469.828      14.970.630        2.625,27        4.568,57
20110701     290.073,06      14.112,50         748.612.143      24.142.315        2.580,77        1.710,70
20110801     298.008,38       7.935,31         777.900.233      29.288.090        2.610,33        3.690,86
20110901     309.598,00      11.589,63         803.806.703      25.906.470        2.596,29        2.235,32
20111001     320.487,94      10.889,94         822.130.066      18.323.363        2.565,24        1.682,60
20111101     314.488,81      -5.999,13         838.907.362      16.777.296        2.667,53       -2.796,62
20111201     323.648,50       9.159,69         865.764.316      26.856.954        2.675,01        2.932,08
20120101     337.712,44      14.063,94         883.382.388      17.618.072        2.615,78        1.252,71
20120201     349.180,06      11.467,63         904.223.659      20.841.271        2.589,56        1.817,40
20120301     360.276,38      11.096,31         941.912.168      37.688.509        2.614,42        3.396,49
20120401     362.167,56       1.891,19         978.799.520      36.887.352        2.702,62       19.504,86
20120501     353.547,19      -8.620,38         967.132.407     -11.667.113        2.735,51        1.353,43
==========================================================================================================
The whole database has 353.6 GB with 967M records. According previous number, mine nr. one candidate has 20% of disk size and 33% of all records. So this is really very important table for starting point. And this is not all, if you look closely, you'll see that tables is getting cca 10M records a month, what gives us 333k records per day. And this all is performed by updating 3 indexes with more then 35GB in it's size! we'll this is not small at all. In this moment, I didn't want to see when this inserts are happening but to understand those big index numbers with adequate usage. For that, next I look in gv$sql to see which queries are using this table. The query is rather simple:
select distinct substr(sql_text,1,100) sql 
  from gv$sql
 where upper(SQL_FULLTEXT) LIKE '%XX_CASH_UP_DETAIL_T%'
Result was like:
SQL
---------------------------------------------------------------------------------------------------
SELECT /*+ NOPARALLEL (GV_$SQL) */ INST_ID, SQL_TEXT, SQL_FULLTEXT,     SQL_ID, SHARABLE_MEM, PERSIS 
select count('1') from xx_cash_up_detail_t where date_reddition >= to_date('01.11.2011', 'dd.mm.yyyy 
select substr(sql_text,1,100) sql  from gv$sql where upper(SQL_FULLTEXT) LIKE '%XX_CASH_UP_DETAIL_T% 
select sql_text  from gv$sql where upper(SQL_FULLTEXT) LIKE '%XX_CASH_UP_DETAIL_T%'                  
select distinct substr(sql_text,1,100) sql  from gv$sql where upper(SQL_FULLTEXT) LIKE '%XX_CASH_UP_ 
select min(date_reddition) from xx_cash_up_detail_t   
So if we extract system calls (gather_stats or db_size snapshot), only two queries remains:
select count('1') from xx_cash_up_detail_t where date_reddition >= to_date('01.11.2011', 'dd.mm.yyyy 
select min(date_reddition) from xx_cash_up_detail_t   
Making count on number of executions, we get the clearer picture when this table is used:
  • 1 time min (date_reddition)
  • 2 times count('1')
Cool-not too much. But this raise other questions and douobts. Let see complete sql's, executions plans, and execution timings for each sql with normal and changed plan.

Case 1

This example is far from ideal measuring (clearing buffer cache was not performed) but the result will be representative enough to clear (confirm) mine doubts. Here is original first sql:
select count('1') from xx_cash_up_detail_t where date_reddition >= to_date('01.11.2011', 'dd.mm.yyyy')
Execution plan was using index:
Plan
SELECT STATEMENT ALL_ROWS Cost: 118 K Bytes: 8 Cardinality: 1 
  2 SORT AGGREGATE Bytes: 8 Cardinality: 1 
    1 INDEX FAST FULL SCAN INDEX YYY.XXD_CASH_UP_IX_REDD Cost: 118 K Bytes: 310 M Cardinality: 41 M 
And when you run the query, here is the result:
SQL> select count('1') from xx_cash_up_detail_t where date_reddition >= to_date('01.11.2011', 'dd.mm.yyyy');

COUNT('1')
----------
  62014296

Elapsed: 00:04:00.62
SQL>
So, 4 minutes for such a big table is not too much and especially when have in mind that DR was only 404.746. In the meantime (while sql was running), I checked in another session and see that index was really used:
 IID SID_SERIAL  USERNAME        TARGET                                SEC MESSAGE                        % Complete
---- ----------- --------------- ----------------------------------- ----- ------------------------------ ----------
   4 672,23628   SYS             YYY.XX_CASH_UP_DETAIL_T               165 Index Fast Full Scan:  YYY.XX_      66.16
                                                                           CASH_UP_DETAIL_T: 735860 out o
                                                                           f 1112291 Blocks done
I wanted to force to run the same query but not allowing to use index...forcing FTS on such a big table:
select /*+ FULL(xx_cash_up_detail_t) */ 
       count('1') 
  from xx_cash_up_detail_t 
 where date_reddition >= to_date('01.11.2011', 'dd.mm.yyyy');
Plan for that sql was showing really, as expected full table scan, on table:
Plan
SELECT STATEMENT ALL_ROWS Cost: 439 K Bytes: 8 Cardinality: 1 
  2 SORT AGGREGATE Bytes: 8 Cardinality: 1 
    1 TABLE ACCESS FULL TABLE YYY.XX_CASH_UP_DETAIL_T Cost: 439 K Bytes: 310 M Cardinality: 41 M 
Real run gave me this result:
SQL> select /*+ FULL(xx_cash_up_detail_t) */ 
  2         count('1') 
  3    from xx_cash_up_detail_t 
  4   where date_reddition >= to_date('01.11.2011', 'dd.mm.yyyy');

COUNT('1')
----------
  62014296

Elapsed: 00:09:22.47
SQL>
So 9,5 minutes. As previously, I checked that FTS is really performing in this sql:
 IID SID_SERIAL  USERNAME        TARGET                                SEC MESSAGE                        % Complete
---- ----------- --------------- ----------------------------------- ----- ------------------------------ ----------
   4 672,23628   SYS             YYY.XX_CASH_UP_DETAIL_T               379 Table Scan:  YYY.XX_CASH_UP_DE      55.74
                                                                           TAIL_T: 2488405 out of 4464305
                                                                            Blocks done
6 minutes saving opposite sql which use index is not small but it depends how often query is run. To be fair, I repeat the same first query (which was using some kind of buffer cache-but this was best case scenario). Result was:
SQL> select count('1') from xx_cash_up_detail_t where date_reddition >= to_date('01.11.2011', 'dd.mm.yyyy');

COUNT('1')
----------
  62014296

Elapsed: 00:03:03.45
SQL>
So 3 minutes is the best time scenario. BC has gained for more then 15%. So far result didn't impressed me.

Case 2

Now come to test second sql. Here is execution plan as well as timing:
SQL> select min(date_reddition) from xx_cash_up_detail_t;

MIN(DATE
--------
01.10.09

Elapsed: 00:00:29.78
SQL>

Plan
SELECT STATEMENT ALL_ROWS Cost: 434 K Bytes: 8 Cardinality: 1 
  2 SORT AGGREGATE Bytes: 8 Cardinality: 1 
    1 INDEX FULL SCAN (MIN/MAX) INDEX YYY.XXD_CASH_UP_IX_REDD Bytes: 2 G Cardinality: 324 M 
According size of underlying table, this sql runs like hell. I tried to avoid usage of index again, to see run time results:
SQL> select /*+ FULL(xx_cash_up_detail_t) */ min(date_reddition) from xx_cash_up_detail_t;

MIN(DATE
--------
01.10.09

Elapsed: 00:13:09.98
SQL>
This was really slow, but again, number of executions is important (keep in mind all the time size of maintained indexes). So far in this case testing is enough. Let see AWR data which will cover time that ASH cannot.

AWR data

As explaind in Automated AWR reports in Oracle 10g/11g post, mine settings in database are defined as 46 days window retention with 15 minutes snapshot interval. This can be reviewed as:
SQL> col snap_interval format a30
SQL> col retention format a30
SQL> select snap_interval
  2  , retention
  3  from dba_hist_wr_control
  4  /

SNAP_INTERVAL                  RETENTION
------------------------------ ------------------------------
+00000 00:15:00.0              +00046 00:00:00.0

SQL> 
So 46 days, gave me nice time frame to picture is there anything happening with focused table. Just to mention that this queries were performed before running all previous tests, just to avoid wrong entries, which doesn't happened in real DB life. Query is again very simple:
select sql_text 
from dba_hist_sqltext
where upper(SQL_TEXT) LIKE '%XX_CASH_UP_DETAIL_T%'
Query gave me next results:
Nr ExecsSQL statement
1INSERT INTO YYY.XX_CASH_UP_DETAIL_T INS_TBL (ID_RESEAU, ID_SITE, MATRICULE, DATE_REDDITION, SAC, CORRECTION_STATUS, ID_PAIEMENT, LIBELLE_PAIEMENT, ID_CLASSE, MONTANT_PAIEMENT, DEVISE_PAIEMENT, NB_PAIEMENT, MONTANT_TOTAL, INSERT_DATE, DATE_MESSAGE) SELECT ID_RESEAU, ID_SITE, MATRICULE, DATE_REDDITION, SAC, CORRECTION_STATUS, ID_PAIEMENT, LIBELLE_PAIEMENT, ID_CLASSE, MONTANT_PAIEMENT, DEVISE_PAIEMENT, NB_PAIEMENT, MONTANT_TOTAL, INSERT_DATE, DATE_MESSAGE FROM TR_CASH_UP_DETAIL@db_link.hr WHERE CORRECTION_STATUS IS NOT NULL
2select count('1') from xx_cash_up_detail_t where date_reddition >= to_date('01.11.2011', 'dd.mm.yyyy')
1SELECT count(*) FROM "YYY"."XX_CASH_UP_DETAIL_T"
1select count(*), ID_RESEAU from yyy.XX_CASH_UP_DETAIL_T group by ID_RESEAU
1select * from yyy.XX_CASH_UP_DETAIL_T where ID_RESEAU='01' and ID_SITE='22' AND MATRICULE='115515' AND DATE_REDDITION BETWEEN '01.01.2011 00:00:00' AND '31.12.2011 23:59:59' AND SAC=731 AND DATE_MESSAGE IS NOT NULL
Let us look for plans that might use indexes. I'm using count on substr (sql_text1,100) to get more precise results:
Nr
Execs
SQL statementIndex use?
1INSERT INTO YYY.XX_CASH_UP_DETAIL_TN
2select count('1') from xx_cash_up_detail_t where date_reddition >= to_date('01.11.2011', 'dd.mm.yyyy')Y
1SELECT count(*) FROM "YYY"."XX_CASH_UP_DETAIL_T"N (FTS)
1select count(*), ID_RESEAU from yyy.XX_CASH_UP_DETAIL_T group by ID_RESEAU?
1select * from yyy.XX_CASH_UP_DETAIL_T where ID_RESEAU='01' and ID_SITE='22' AND MATRICULE='115515' AND DATE_REDDITION BETWEEN '01.01.2011 00:00:00' AND '31.12.2011 23:59:59' AND SAC=731 AND DATE_MESSAGE IS NOT NULL?
Pogledajmo planove za te upite (insert nema smisla :
select count(*), ID_RESEAU from yyy.XX_CASH_UP_DETAIL_T group by ID_RESEAU

Plan
SELECT STATEMENT  ALL_ROWSCost: 552 K  Bytes: 9  Cardinality: 3    
 2 HASH GROUP BY  Cost: 552 K  Bytes: 9  Cardinality: 3   
  1 TABLE ACCESS FULL TABLE YYY.XX_CASH_UP_DETAIL_T Cost: 428 K  Bytes: 928 M  Cardinality: 324 M  
Not using index at all. And last one:
select * from yyy.XX_CASH_UP_DETAIL_T where ID_RESEAU='01' and ID_SITE='22' AND MATRICULE='115515' AND DATE_REDDITION BETWEEN '01.01.2011 00:00:00' AND '31.12.2011 23:59:59'  AND  SAC=731 AND DATE_MESSAGE IS NOT NULL

Plan
SELECT STATEMENT  ALL_ROWSCost: 8  Bytes: 86  Cardinality: 1     
 3 FILTER    
  2 TABLE ACCESS BY INDEX ROWID TABLE YYY.XX_CASH_UP_DETAIL_T Cost: 8  Bytes: 86  Cardinality: 1   
   1 INDEX RANGE SCAN INDEX YYY.XXD_CASH_UP_I Cost: 6  Cardinality: 1  
Let see in praxis:
SQL> select * from yyy.XX_CASH_UP_DETAIL_T where ID_RESEAU='01' and ID_SITE='22' AND MATRICULE='115515' AND DATE_REDDITION BETWEEN '01.01.2011 00:00:00' AND '31.12.2011 23:59:59'  AND  SAC=731 AND DATE_MESSAGE IS NOT NULL;

no rows selected

Elapsed: 00:00:13.17
SQL>
Index is used and pretty quick. So let's test our FTS on that sql:
SQL> select /*+ FULL(xx_cash_up_detail_t) */ 
  2  * from yyy.XX_CASH_UP_DETAIL_T where ID_RESEAU='01' and ID_SITE='22' AND MATRICULE='115515' AND DATE_REDDITION BETWEEN '01.01.2011 00:00:00' AND '31.12.2011
 23:59:59'  AND  SAC=731 AND DATE_MESSAGE IS NOT NULL;

no rows selected

Elapsed: 00:16:26.84
Uff ... 16 minutes for query and this is really long. Haven't measured DR but should be the same as previous time for FTS ... irrelevant what I want to proove.

Conclusion

All mine doubts about this table were confirmed. In best case scenario in 46 days, there was 3 queries that have used one of indexes that are 36GB in total size. For those purposes, indexes are totally unnecessary in any mean in this context of use.

So I advised to drop all of indexes-do you agree? Performance gain against maintaining indexes against 3 runs in 46 days are worth full.

The end

Keep in mind that another big questions is here to be asked:

Why 33% of all db records and 20% of db disk size, are used only 5 times in a 46 days, while in the same time table grows by 1GB/10mil records a month?

What is the purpose of such a table?

Last but not least ... 330 M records and no primary key. Maybe if there is Guinness for that ... this will be probably in topmost results.


And mine approach that biggest tables makes the biggest problems in this case are totally missed. This case shows that they are not used!! Obviously, somewhere db design went terribly wrong... Still think mine approach will work in other ... better designed database.

Cheers!

6 comments :

  1. That's exactly what happened when developers has ability to design db and indexes.

    God helps you.

    ReplyDelete
  2. Nice analysis Damir.
    Yes, it's little odd that such big table is practically not used. Maybe the usage is planned in the near future.

    At the end of the post you've noted questions that I would ask developers about specified table.

    If this table is supposed to be underused than dropping indexes would be my next move also. Maintaining such indexes without proper reason is simply too expensive and not necessary.


    We have several big log tables that aren't queried very much. I've dropped indexes on that tables to save some storage and resources.

    Regards,
    Marko

    ReplyDelete
  3. @Marko,

    glad you agree with me.
    After mine intervention, db size (monitored with db_size solution) drop from 363 GB 50 195 GB.
    90% were indexes!!!
    Imagine what was these unused data doing in RAC interconnect ...
    Rg
    Damir
    P.S.
    Number of ORA-00600 is drop for more 50% now also!

    ReplyDelete
  4. Hi Damir

    Execellent post.

    Can you please share the tbl_info script?

    Thank you
    haris

    ReplyDelete
  5. @Hasan ALi
    Where is your post-this is second time with you.
    I do not communicate by mail-only publicly

    Please send the picture of your WEB page so I can see your post.

    Rg
    Damir

    ReplyDelete
    Replies
    1. Hi Damir

      I do not know for sure why my post was not published. Everytime I click "Publish" button i have to key in the special code maybe i keyed wrongly that cause the problem.

      Anyway, you have posted an excellent stuff. I learned a lot from your blog.

      My request is, would you be able to share the tbl_info script? Will the script reports statistics information too?

      Thank you
      Haris

      Delete

Zagreb u srcu!

Copyright © 2009-2018 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign