How to calculate the actual size of a table

here are the example made by Toms kyte…there is the situation maybe lots of people had made the practice,u just stand on the shoulder of giants,and u need to read it carefully,that’s enough..

and thanks a lot, Tom,u did a lot to help countless person..

*********************************************************************************

for a discussion of the use of ANALYZE and DBMS_SPACE to this end.  Basically -- you can
find out how many blocks are allocated to the table (whether used or NOT), how many
blocks have never been used (subtract them from above) and on the blocks that are used --
the average free space.

For example, in the example from above that I linked to -- we see:

ops$tkyte@DEV8I.WORLD> create table t ( x int,
                                 y char(2000) default '*' )
  2  storage ( initial 40k next 40k minextents 5 )
  3  tablespace system;

Table created.

A table that will create ~2k rows for each row inserted.  makes it easy to do the
math

ops$tkyte@DEV8I.WORLD> insert into t (x) values ( 1 );
1 row created.

ops$tkyte@DEV8I.WORLD> analyze table t compute statistics;
Table analyzed.

ops$tkyte@DEV8I.WORLD> compute sum of blocks on report
ops$tkyte@DEV8I.WORLD> break on report
ops$tkyte@DEV8I.WORLD> select extent_id, bytes, blocks
  2    from user_extents
  3   where segment_name = 'T'
  4     and segment_type = 'TABLE'
  5  /

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         2      81920         10
         3     122880         15
         4     163840         20
         0      40960          5
         1      40960          5
                      ----------
sum                           55

ops$tkyte@DEV8I.WORLD> clear breaks
ops$tkyte@DEV8I.WORLD> select blocks, empty_blocks,
  2         avg_space, num_freelist_blocks
  3   from user_tables
  4  where table_name = 'T'
  5  /

    BLOCKS EMPTY_BLOCKS  AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
         1           53       6091                   1

Ok, the above shows us:

o we have 55 blocks allocated to the table
o 53 blocks are totally empty (above the HWM)
o 1 block contains data (the other block is used by the system)
o we have an average of about 6k free on each block used.

Therefore, our table

o consumes 1 block
o of which  1block * 8k blocksize - 1 block * 6k free = 2k is used for our data.

Now, lets put more stuff in there...


ops$tkyte@DEV8I.WORLD> insert into t (x)
  2  select rownum
  3    from all_users
  4   where rownum < 50
  5  /
49 rows created.

ops$tkyte@DEV8I.WORLD> analyze table t compute statistics;
Table analyzed.

ops$tkyte@DEV8I.WORLD> compute sum of blocks on report
ops$tkyte@DEV8I.WORLD> break on report
ops$tkyte@DEV8I.WORLD> select extent_id, bytes, blocks
  2    from user_extents
  3   where segment_name = 'T'
  4     and segment_type = 'TABLE'
  5  /

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         2      81920         10
         3     122880         15
         4     163840         20
         0      40960          5
         1      40960          5
                      ----------
sum                           55

ops$tkyte@DEV8I.WORLD> clear breaks
ops$tkyte@DEV8I.WORLD> select blocks, empty_blocks,
  2         avg_space, num_freelist_blocks
  3    from user_tables
  4   where table_name = 'T'
  5  /

    BLOCKS EMPTY_BLOCKS  AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
        19           35       2810                   3

Ok, the above shows us:

o we have 55 blocks allocated to the table (still)
o 35 blocks are totally empty (above the HWM)
o 19 blocks contains data (the other block is used by the system)
o we have an average of about 2.8k free on each block used.

Therefore, our table

o consumes 19 blocks of storage in total.
o of which  19 blocks * 8k blocksize - 19 block * 2.8k free = 98k is used for our data.

Given our rowsize, this is exactly what we expected.

size of table or index…all the objects in oracle

you can query the size of an object,such as table,index….from user_extents or dba_extents

because,a segment maybe cross muti extents,look at “TEST”,you need to sum all the bytes of each extent_id to get the total size of table “TEST”

SQL> select segment_name,segment_type,tablespace_name,extent_id,bytes from user_extents;

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME                 EXTENT_ID      BYTES
—————————— —————— —————————— ———- ———-
DEPT                           TABLE              USERS                                   0      65536
EMP                            TABLE              USERS                                   0      65536
BONUS                          TABLE              USERS                                   0      65536
SALGRADE                       TABLE              USERS                                   0      65536
TEST                           TABLE              USERS                                   0      65536
TEST                           TABLE              USERS                                   1      65536
TEST                           TABLE              USERS                                   2      65536
TEST                           TABLE              USERS                                   3      65536
TEST                           TABLE              USERS                                   4      65536
TEST                           TABLE              USERS                                   5      65536
TEST                           TABLE              USERS                                   6      65536
TEST                           TABLE              USERS                                   7      65536
TEST                           TABLE              USERS                                   8      65536
TEST                           TABLE              USERS                                   9      65536
TEST                           TABLE              USERS                                  10      65536
TEST                           TABLE              USERS                                  11      65536
TEST                           TABLE              USERS                                  12      65536
TEST                           TABLE              USERS                                  13      65536
TEST                           TABLE              USERS                                  14      65536
TEST                           TABLE              USERS                                  15      65536
TEST                           TABLE              USERS                                  16    1048576
TEST                           TABLE              USERS                                  17    1048576
TEST                           TABLE              USERS                                  18    1048576
TEST                           TABLE              USERS                                  19    1048576
TEST                           TABLE              USERS                                  20    1048576
USERS                          TABLE              USERS                                   0      65536
PK_DEPT                        INDEX              USERS                                   0      65536
PK_EMP                         INDEX              USERS                                   0      65536

28 rows selected.

SQL> select sum(bytes) from user_extents where segment_name=’TEST’;

SUM(BYTES)
———-
6291456

it’s the same as INDEX…..

analyze the memory consumption of one sql

there will be a easy sample of memory consumption caculation

check the block size of your oracle,here are 8192bytes
SQL> show parameter db_block_size;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_block_size                        integer     8192

//check the execution plan of sql
SQL> select * from scott.emp;

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH      CLERK           7902 17-DEC-80        800                    20
7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
7566 JONES      MANAGER         7839 02-APR-81       2975                    20
7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
7839 KING       PRESIDENT            17-NOV-81       5000                    10
7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7900 JAMES      CLERK           7698 03-DEC-81        950                    30
7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

Elapsed: 00:00:00.03

Execution Plan
———————————————————-
Plan hash value: 3956160932

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
————————————————————————–

Statistics
———————————————————-
1  recursive calls
0  db block gets
8  consistent gets
0  physical reads
0  redo size
1415  bytes sent via SQL*Net to client
385  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
14  rows processed

//8 consistent gets,you can calculate the exact memory consumption by:
//8 x 8192bytes=65536bytes=64kb

oracle asm startup with os

maybe u will meet such issues when setting up the self startup with os:

May 15 19:19:29 oel4u8 logger: Waiting for Oracle CSS service to be available before starting
May 15 19:19:29 oel4u8 logger:  ASM instance +ASM. Wait 1.
May 15 19:19:51 oel4u8 logger: Waiting for Oracle CSS service to be available before starting
May 15 19:19:51 oel4u8 logger:  ASM instance +ASM. Wait 2.
May 15 19:20:13 oel4u8 logger: Waiting for Oracle CSS service to be available before starting
May 15 19:20:13 oel4u8 logger:  ASM instance +ASM. Wait 3.
May 15 19:20:35 oel4u8 logger: Waiting for Oracle CSS service to be available before starting
May 15 19:20:35 oel4u8 logger:  ASM instance +ASM. Wait 4.
May 15 19:20:57 oel4u8 logger: Waiting for Oracle CSS service to be available before starting
May 15 19:20:57 oel4u8 logger:  ASM instance +ASM. Wait 5.
May 15 19:21:15 oel4u8 sshd(pam_unix)[6017]: session opened for user oracle by (uid=0)
May 15 19:21:18 oel4u8 logger: Waiting for Oracle CSS service to be available before starting
May 15 19:21:18 oel4u8 logger:  ASM instance +ASM. Wait 6.
May 15 19:21:40 oel4u8 logger: Waiting for Oracle CSS service to be available before starting
May 15 19:21:40 oel4u8 logger:  ASM instance +ASM. Wait 7.
May 15 19:22:02 oel4u8 logger: Waiting for Oracle CSS service to be available before starting
May 15 19:22:02 oel4u8 logger:  ASM instance +ASM. Wait 8.
May 15 19:22:24 oel4u8 logger: Waiting for Oracle CSS service to be available before starting
May 15 19:22:24 oel4u8 logger:  ASM instance +ASM. Wait 9.
May 15 19:22:46 oel4u8 logger: Waiting for Oracle CSS service to be available before starting
May 15 19:22:46 oel4u8 logger:  ASM instance +ASM. Wait 10.
May 15 19:23:08 oel4u8 logger: Waiting for Oracle CSS service to be available before starting
May 15 19:23:08 oel4u8 logger:  ASM instance +ASM. Wait 11.
May 15 19:23:29 oel4u8 logger: Waiting for Oracle CSS service to be available before starting
May 15 19:23:29 oel4u8 logger:  ASM instance +ASM. Wait 12.
May 15 19:23:51 oel4u8 logger: Waiting for Oracle CSS service to be available before starting
May 15 19:23:51 oel4u8 logger:  ASM instance +ASM. Wait 13.
May 15 19:24:13 oel4u8 logger: Waiting for Oracle CSS service to be available before starting
May 15 19:24:13 oel4u8 logger:  ASM instance +ASM. Wait 14.
May 15 19:24:35 oel4u8 logger: Timed out waiting to start ASM instance +ASM
May 15 19:24:35 oel4u8 logger:   CSS service is NOT available.

it’s a bug noted as Oracle Bug 3458327 in metalink,following behand to cover this problem:

add h1:35:respawn:/etc/init.d/init.cssd run >/dev/null 2>&1 </dev/null
between l2 and l3 will cover that

l0:0:wait:/etc/rc.d/rc 0
l1:1:wait:/etc/rc.d/rc 1
l2:2:wait:/etc/rc.d/rc 2
h1:35:respawn:/etc/init.d/init.cssd run >/dev/null 2>&1 </dev/null
l3:3:wait:/etc/rc.d/rc 3
l4:4:wait:/etc/rc.d/rc 4

rac实施的几个注意问题


本例子的环境是oracle 10.2.0.1+NFS+oracle linux 4 update 8,10.2.0.4的patch
1.rac在安装完毕CRS后,安装database,然后shutdown两个节点的CRS,准备打patch

2.patch的顺序
先把CRS升级到10.2.0.4然后再升级database

3.create instance,TAF配置
为整个cluster创建每个节点的instance,别忘记配置TAF,RAC的TAF原则是配置一个总体的对外服务的service name,这里的service name与之前配置的instance name
,gloabel name不是一个概念。在method的选择上,选择”basic”

4.service name and cluster name
service name与cluster name最好不要重名,否则在创建实例的时候,会报错:fail to create configuration for services “rac” of cluster database “rac”
service name cannot be same as the database default service name rac

use srvctl to handle the listener


start|stop lisntener
//you can use srvctl to stop the listener of nodes
oracle@rac2 crs$ srvctl stop listener -n rac1
oracle@rac2 crs$ crs_stat -t
Name Type Target State Host
————————————————————
ora.rac.db application ONLINE ONLINE rac1
ora….c1.inst application ONLINE ONLINE rac1
ora….c2.inst application ONLINE ONLINE rac2
*ora….C1.lsnr application OFFLINE OFFLINE*
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora….C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2

//you can see the status of listener in rac1
oracle@rac1 admin$ lsnrctl status listener_rac1

LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 15-MAR-2011 09:50:40

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip.localdomain)(PORT=1521)(IP=FIRST)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.201.128)(PORT=1521)(IP=FIRST)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused

//you can try to start the listener as “lsnrctl start listener_rac1” and check the effect
//from the following ,you can see that the offlince status of “ora….C1.lsnr” goes online again
//so,it’s the same as u do by “lsnrctl start” and “srvctl start listener -n”

oracle@rac1 admin$ lsnrctl start listener_rac1

LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 15-MAR-2011 09:52:16

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 10.2.0.1.0 – Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener_rac1.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.201.149)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.201.128)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip.localdomain)(PORT=1521)(IP=FIRST)))
STATUS of the LISTENER
————————
Alias listener_rac1
Version TNSLSNR for Linux: Version 10.2.0.1.0 – Production
Start Date 15-MAR-2011 09:52:18
Uptime 0 days 0 hr. 0 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener_rac1.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.201.149)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.201.128)(PORT=1521)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
oracle@rac1 admin$ crs_stat -t
Name Type Target State Host
————————————————————
ora.rac.db application ONLINE ONLINE rac1
ora….c1.inst application ONLINE ONLINE rac1
ora….c2.inst application ONLINE ONLINE rac2
*ora….C1.lsnr application ONLINE ONLINE rac1*
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora….C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2

//so,it’s the same effect as you type “lsnrctl” or “srvctl start listener -n node1” to handle the listener