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 30577170Normally 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')
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 Execs | SQL statement |
| 1 | INSERT 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 |
| 2 | select count('1') from xx_cash_up_detail_t where date_reddition >= to_date('01.11.2011', 'dd.mm.yyyy') |
| 1 | SELECT count(*) FROM "YYY"."XX_CASH_UP_DETAIL_T" |
| 1 | select count(*), ID_RESEAU from yyy.XX_CASH_UP_DETAIL_T group by ID_RESEAU |
| 1 | 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 |
| Nr Execs | SQL statement | Index use? |
| 1 | INSERT INTO YYY.XX_CASH_UP_DETAIL_T | N |
| 2 | select count('1') from xx_cash_up_detail_t where date_reddition >= to_date('01.11.2011', 'dd.mm.yyyy') | Y |
| 1 | SELECT count(*) FROM "YYY"."XX_CASH_UP_DETAIL_T" | N (FTS) |
| 1 | select count(*), ID_RESEAU from yyy.XX_CASH_UP_DETAIL_T group by ID_RESEAU | ? |
| 1 | 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 | ? |
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 MNot 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: 1Let 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.84Uff ... 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!
