Thursday, January 17, 2013

ORACLE DBA - Get segment size, compare table and index size

Creating simple function we can get table`s size.

CREATE OR REPLACE FUNCTION ULFET.CHECK_TABLE_SIZE(TAB_NAME VARCHAR) RETURN varchar
IS
   tab_size number;
   str varchar2(50);
BEGIN  
     SELECT  SUM (bytes) / (1024 * 1024)  INTO tab_size
     FROM sys.dba_extents WHERE segment_type = 'TABLE' AND segment_name = TAB_NAME GROUP BY segment_name;
str := tab_name||' size is '||tab_size||' MB';
RETURN str;
END;
/

P.S: If compilation will error grant select on sys.dba_extents to desired user and repeat creation.

SQL> conn /as sysdba

SQL> grant select on sys.dba_extents to ulfet;

Grant succeeded.

SQL>



--call
select ULFET.CHECK_TABLE_SIZE('TEST_TAB') from dual;

or

set serveroutput on
declare
l_answvarchar2(100);
begin
l_answ:=ULFET.CHECK_TABLE_SIZE('TEST_TAB');
dbms_output.put_line(l_answ);
end;
select ULFET.CHECK_TABLE_SIZE('TEST_TAB') from dual;

P.S: Please note that table_name should be entered with upper case.

Above function will return for tables which gathered statistic.
To get statistics you can use several methods : ANALYZE or DBMS_STATS.GATHER_TABLE_STATS

Example:


exec ANALYZE TABLE employees COMPUTE STATISTICS; --it computes actual statistics
exec ANALYZE TABLE employees ESTIMATE STATISTICS; --estimates the stats
exec DBMS_STATS.GATHER_TABLE_STATS ('HR','EMPLOYEES');


DBMS_STATS package available on Oracle 10g and later versions.

Sometimes we getting request something like that, is it possible index size bigger than table`s size?
Yes, possible, let`s simulate.

SQL> create table test_tbl (id number, col1 varchar2(2000));

Table created.

SQL> create index idx_test_tbl on test_tbl(col1);

Index created.

SQL> begin
        for i in 1..10000 loop
                insert into test_tbl values (i, lpad(i,2000, 'z'));
        end loop;
        commit;
end;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.


SQL> delete from test_tbl;

10000 rows deleted.

SQL> commit;



Check table and index`s size


SQL> col segment_name format a20
SQL> SELECT segment_name, SUM (bytes) / (1024 * 1024) size_mb
FROM sys.dba_extents
WHERE segment_type in('TABLE','INDEX')
AND segment_name in('TEST_TBL', 'IDX_TEST_TBL') and owner='ULFET'
group by segment_name;

SEGMENT_NAME            SIZE_MB
--------------------              ----------
TEST_TBL                          27
IDX_TEST_TBL                 46

SQL>

SQL> with tabs as (select segment_name tablename,bytes tablesize from user_segments where segment_type='TABLE'),
inds as (select i.index_name indexname,i.table_name tablename,s.bytes indexsize from user_indexes i join user_segments s on (i.index_name=s.segment_name))
select * from tabs natural join inds where indexsize > tablesize;  2    3  


 TABLENAME    TABLESIZE  INDEXNAME     INDEXSIZE  
 ---------    ---------  ------------  ---------  
 TEST_TBL     28311552   IDX_TEST_TBL  48234496  
 SQL> 

List of indexes which size bigger than tables


SQL> col tablename format a23
SQL> WITH tabs AS (SELECT segment_name tablename, bytes tablesize FROM dba_segments

  WHERE segment_type = 'TABLE' AND owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'PUBLIC')),
        inds AS (SELECT i.index_name indexname, i.table_name tablename, s.bytes indexsize FROM dba_indexes i  JOIN dba_segments s ON (i.index_name = s.segment_name)
AND s.owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'PUBLIC')
)
SELECT * FROM tabs NATURAL JOIN inds WHERE indexsize > tablesize;



For performance it is good to check table`s which size more than 2 GB and not partitioned.

Get list of tables which size more than 2 GB and not partitioned.

SELECT A.OWNER,
       A.SEGMENT_NAME,
       BYTES,
       ROUND (BYTES / 1024 / 1024 / 1024, 2) SIZE_GB,
       B.OBJECT_TYPE,
       PARTITIONED
  FROM DBA_SEGMENTS A, DBA_OBJECTS B, DBA_TABLES C
 WHERE     A.SEGMENT_NAME = B.OBJECT_NAME
       AND A.OWNER NOT IN ('SYS', 'SYSTEM')
       AND BYTES > 2147483648
       AND OBJECT_TYPE = 'TABLE'
       AND C.TABLE_NAME = A.SEGMENT_NAME
       --AND PARTITIONED = 'YES'
       ORDER by size_gb,  segment_name


2 comments:

  1. when index size>table size. how to fix this issue. It is a prod db. Is there any impact to db?
    Can you pl reply

    ReplyDelete
  2. Hello Krish. First of all I think you must check index statistic is stale ? Check also extents count for index. You may shrink index
    ALTER INDEX testidx SHRINK SPACE COMPACT;
    Also move to another tablespace (it will effect extent count - reduce)
    Is you index compact (used more than one column) ?
    Please share here your result of query:

    SELECT segment_name, SUM (bytes) / (1024 * 1024) size_mb
    FROM sys.dba_extents
    WHERE segment_type in('TABLE','INDEX')
    AND segment_name in('YOUTTABLE', 'YOURINDEX') and owner='YOURNEEDEDUSER'
    group by segment_name;

    ReplyDelete

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...