HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Simple script to evaluete average VSize from tables in etalon schema (sizing issue)
ORACLE STUFFS
03-Jul-2009
It is obviously possible get average size of each column from ALL_TAB_COLUMNS when statistics are up to date. Following script calculates it from data. It is useful when statistics are not reliable in the etalon.

set serveroutput on for wra
Declare
  c_owner varchar2(30):=user;
  ------------------
  TYPE t_nrry is TABLE of NUMBER index by binary_integer;  
  nrry t_nrry;
  v_stmt varchar2(32767);
  v_idx  integer;
  v_tot  number;
Begin
  DBMS_OUTPUT.ENABLE(1000000);
  for r1 in (select owner,table_name from all_tables where owner=c_owner)LOOP
    nrry.delete;
    v_stmt:='Select ';
    v_idx:=0;
    for r2 in (select table_name,column_name from all_tab_columns where owner=r1.owner and table_name=r1.table_name)LOOP
     v_idx:=v_idx+1;
     v_stmt:=concat(v_stmt,case when v_idx=1 then ' ' else ',' end||'avg(vsize('||r2.column_name||'))');
    end LOOP;--r2
    if v_idx>0 then
      for i in v_idx+1 .. 200 loop
         v_stmt:=concat(v_stmt,',0');
      end LOOP;    
      v_stmt:=concat(v_stmt,' from '||r1.owner||'.'||r1.table_name);
       execute immediate v_stmt into 
       nrry(1),nrry(2),nrry(3),nrry(4),nrry(5),nrry(6),nrry(7),nrry(8),nrry(9),
       nrry(10),nrry(11),nrry(12),nrry(13),nrry(14),nrry(15),nrry(16),nrry(17),nrry(18),nrry(19),
       nrry(20),nrry(21),nrry(22),nrry(23),nrry(24),nrry(25),nrry(26),nrry(27),nrry(28),nrry(29),
       nrry(30),nrry(31),nrry(32),nrry(33),nrry(34),nrry(35),nrry(36),nrry(37),nrry(38),nrry(39),
       nrry(40),nrry(41),nrry(42),nrry(43),nrry(44),nrry(45),nrry(46),nrry(47),nrry(48),nrry(49),
       nrry(50),nrry(51),nrry(52),nrry(53),nrry(54),nrry(55),nrry(56),nrry(57),nrry(58),nrry(59),
       nrry(60),nrry(61),nrry(62),nrry(63),nrry(64),nrry(65),nrry(66),nrry(67),nrry(68),nrry(69),
       nrry(70),nrry(71),nrry(72),nrry(73),nrry(74),nrry(75),nrry(76),nrry(77),nrry(78),nrry(79),
       nrry(80),nrry(81),nrry(82),nrry(83),nrry(84),nrry(85),nrry(86),nrry(87),nrry(88),nrry(89),
       nrry(90),nrry(91),nrry(92),nrry(93),nrry(94),nrry(95),nrry(96),nrry(97),nrry(98),nrry(99),
       nrry(100),nrry(101),nrry(102),nrry(103),nrry(104),nrry(105),nrry(106),nrry(107),nrry(108),nrry(109),
       nrry(110),nrry(111),nrry(112),nrry(113),nrry(114),nrry(115),nrry(116),nrry(117),nrry(118),nrry(119),
       nrry(120),nrry(121),nrry(122),nrry(123),nrry(124),nrry(125),nrry(126),nrry(127),nrry(128),nrry(129),
       nrry(130),nrry(131),nrry(132),nrry(133),nrry(134),nrry(135),nrry(136),nrry(137),nrry(138),nrry(139),
       nrry(140),nrry(141),nrry(142),nrry(143),nrry(144),nrry(145),nrry(146),nrry(147),nrry(148),nrry(149),
       nrry(150),nrry(151),nrry(152),nrry(153),nrry(154),nrry(155),nrry(156),nrry(157),nrry(158),nrry(159),
       nrry(160),nrry(161),nrry(162),nrry(163),nrry(164),nrry(165),nrry(166),nrry(167),nrry(168),nrry(169),
       nrry(170),nrry(171),nrry(172),nrry(173),nrry(174),nrry(175),nrry(176),nrry(177),nrry(178),nrry(179),
       nrry(180),nrry(181),nrry(182),nrry(183),nrry(184),nrry(185),nrry(186),nrry(187),nrry(188),nrry(189),
       nrry(190),nrry(191),nrry(192),nrry(193),nrry(194),nrry(195),nrry(196),nrry(197),nrry(198),nrry(199),
       nrry(200);
      v_tot:=0;
      for i in 1..v_idx loop
        v_tot:=v_tot+nvl(nrry(i),0);
      end loop;   
      DBMS_OUTPUT.PUT_LINE(r1.table_name||chr(9)||to_char(v_tot));
    end if;
  end LOOP;--r1
End;
/      

[Download]


The second version of the same script returns both count of column and average record size.

set serveroutput on for wra
Declare
  c_owner varchar2(30):=user;
  ------------------
  TYPE t_nrry is TABLE of NUMBER index by binary_integer;  
  nrry t_nrry;
  v_stmt varchar2(32767);
  v_idx  integer;
  v_tot  number;
Begin
  DBMS_OUTPUT.ENABLE(1000000);
  for r1 in (select owner,table_name from all_tables where owner=c_owner)LOOP
    nrry.delete;
    v_stmt:='Select count(1)';
    v_idx:=1;
    for r2 in (select table_name,column_name from all_tab_columns where owner=r1.owner and table_name=r1.table_name)LOOP
     v_idx:=v_idx+1;
     v_stmt:=concat(v_stmt,','||'avg(vsize('||r2.column_name||'))');
    end LOOP;--r2
    for i in v_idx+1 .. 200 loop
       v_stmt:=concat(v_stmt,',0');
    end LOOP;    
    v_stmt:=concat(v_stmt,' from '||r1.owner||'.'||r1.table_name);
     execute immediate v_stmt into 
     nrry(1),nrry(2),nrry(3),nrry(4),nrry(5),nrry(6),nrry(7),nrry(8),nrry(9),
     nrry(10),nrry(11),nrry(12),nrry(13),nrry(14),nrry(15),nrry(16),nrry(17),nrry(18),nrry(19),
     nrry(20),nrry(21),nrry(22),nrry(23),nrry(24),nrry(25),nrry(26),nrry(27),nrry(28),nrry(29),
     nrry(30),nrry(31),nrry(32),nrry(33),nrry(34),nrry(35),nrry(36),nrry(37),nrry(38),nrry(39),
     nrry(40),nrry(41),nrry(42),nrry(43),nrry(44),nrry(45),nrry(46),nrry(47),nrry(48),nrry(49),
     nrry(50),nrry(51),nrry(52),nrry(53),nrry(54),nrry(55),nrry(56),nrry(57),nrry(58),nrry(59),
     nrry(60),nrry(61),nrry(62),nrry(63),nrry(64),nrry(65),nrry(66),nrry(67),nrry(68),nrry(69),
     nrry(70),nrry(71),nrry(72),nrry(73),nrry(74),nrry(75),nrry(76),nrry(77),nrry(78),nrry(79),
     nrry(80),nrry(81),nrry(82),nrry(83),nrry(84),nrry(85),nrry(86),nrry(87),nrry(88),nrry(89),
     nrry(90),nrry(91),nrry(92),nrry(93),nrry(94),nrry(95),nrry(96),nrry(97),nrry(98),nrry(99),
     nrry(100),nrry(101),nrry(102),nrry(103),nrry(104),nrry(105),nrry(106),nrry(107),nrry(108),nrry(109),
     nrry(110),nrry(111),nrry(112),nrry(113),nrry(114),nrry(115),nrry(116),nrry(117),nrry(118),nrry(119),
     nrry(120),nrry(121),nrry(122),nrry(123),nrry(124),nrry(125),nrry(126),nrry(127),nrry(128),nrry(129),
     nrry(130),nrry(131),nrry(132),nrry(133),nrry(134),nrry(135),nrry(136),nrry(137),nrry(138),nrry(139),
     nrry(140),nrry(141),nrry(142),nrry(143),nrry(144),nrry(145),nrry(146),nrry(147),nrry(148),nrry(149),
     nrry(150),nrry(151),nrry(152),nrry(153),nrry(154),nrry(155),nrry(156),nrry(157),nrry(158),nrry(159),
     nrry(160),nrry(161),nrry(162),nrry(163),nrry(164),nrry(165),nrry(166),nrry(167),nrry(168),nrry(169),
     nrry(170),nrry(171),nrry(172),nrry(173),nrry(174),nrry(175),nrry(176),nrry(177),nrry(178),nrry(179),
     nrry(180),nrry(181),nrry(182),nrry(183),nrry(184),nrry(185),nrry(186),nrry(187),nrry(188),nrry(189),
     nrry(190),nrry(191),nrry(192),nrry(193),nrry(194),nrry(195),nrry(196),nrry(197),nrry(198),nrry(199),
     nrry(200);
    v_tot:=0;
    for i in 2..v_idx loop
      v_tot:=v_tot+nvl(nrry(i),0);
    end loop;   
    DBMS_OUTPUT.PUT_LINE(r1.table_name||';'||to_char(nrry(1))||';'||to_char(v_tot));
  end LOOP;--r1
End;
/      

[Download]
Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky