Simple Analysis of Oracle Data Storage

Simple Analysis of Oracle Data Storage

In the database storage structure, we know that a table is generally stored in the corresponding data file, and the data file can be divided into multiple segments. Generally, a table corresponds to a data segment, when data segments are considered alone, data segments can be divided into multiple zones, each of which can be divided into several data blocks at the operating system level, there is a ing between the corresponding data block and the data block at the database level. For example, there can be many floors in a building, and each floor may have different companies, in this case, this building is similar to data files. Each layer of the building is similar to a data segment. For example, each layer can have up to four companies, one company has 40 people, and some companies are larger, if two layers are used, eight zones and 320 data blocks are used. If some companies are a little small, they will take up one part of the layer. This company is similar to a small data segment, occupies 1 partition and contains 40 data blocks.

At the storage level, currently, the database can only query information at the level of the zone.

In user_extents, you can only view the most basic partition information. In user_segment, you can obtain a general information.
SQL> desc user_extents
Name Null? Type
-----------------------------------------------------------------------------
SEGMENT_NAME VARCHAR2 (81)
PARTITION_NAME VARCHAR2 (30)
SEGMENT_TYPE VARCHAR2 (18)
TABLESPACE_NAME VARCHAR2 (30)
EXTENT_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER

SQL> select extent_id, blocks from user_extents where segment_name = 'data ';

EXTENT_ID BLOCKS
--------------------
0 8
1 8
2 8
3 8
4 8
5 8

SQL> select extents, blocks from user_segments where segment_name = 'data ';

EXTENTS BLOCKS
--------------------
6 48

If you want to check which data blocks are contained in each partition, you cannot do anything. In this case, dba_extents can be used as a supplement. You can see which data blocks are contained in which areas.
SQL> select block_id, extent_id, BLOCKS from dba_extents where owner = 'n1 'and segment_name = 'data ';

BLOCK_ID EXTENT_ID BLOCKS
------------------------------
12800 0 8
12808 5 8
3600 4 8
5224 3 8
12672 2 8
12672 1 8

Of course, this cannot satisfy the heat. Sometimes you can use rowid to help you check the size of some records.
Currently, the data type of the table data is as follows:
DATA_LENGTH DATA_TYPE
-----------------------------------------
10 VARCHAR2
22 NUMBER
22 NUMBER
22 NUMBER
22 NUMBER
22 NUMBER
22 NUMBER
22 NUMBER
22 NUMBER
22 NUMBER
SQL> select sum (data_length) from user_tab_cols where table_name = 'data ';

SUM (DATA_LENGTH)
----------------
208

So whether the space occupied by these data types is stored by a maximum of 208 bytes. In other words, there is a record in the table, which may be only number (2) in length ), but will it still occupy 22 bytes for storage.
We randomly extract a data block for a simple test.
We can see that the following two records are counted from 0, all of which are in the data block 12803.
Select dbms_rowid.ROWID_OBJECT (rowid) object_id,
Dbms_rowid.ROWID_RELATIVE_FNO (rowid) file_no,
Dbms_rowid.rowid_row_number (rowid) row_no,
Dbms_rowid.rowid_block_number (rowid) blk_number
From data where rownum <3;
OBJECT_ID FILE_NO ROW_NO BLK_NUMBER
----------------------------------------
18993 11 0 12803
18993 11 1 12803

Let's further check the records contained in the data block 12803. Other storage parameters of data blocks are not considered. According to the default value.
It contains approximately 148 records.

OBJECT_ID FILE_NO ROW_NO BLK_NUMBER
----------------------------------------
......
18993 11 143 12803
18993 11 144 12803
18993 11 145 12803
18993 11 146 12803
18993 11 147 12803

148 rows selected.

The average size of each record is 55 bytes.
SQL> select 1024*8/148 from dual;

1024*8/148
----------
55.3513514

In addition, the rownum and block number can give different data files different meanings.
In the same table, the records in Data File 5 are data block 12676, and the data block in the data file 7 is also 12767. In addition, the corresponding number of data rows is re-calculated from 0.
OBJECT_ID FILE_NO ROW_NO BLK_NUMBER
----------------------------------------
18993 5 143 12676
18993 5 144 12676
18993 5 145 12676
18993 5 146 12676
18993 5 147 12676
18993 5 148 12676
18993 7 0 12676
18993 7 1 12676
18993 7 2 12676
18993 7 3 12676
18993 7 4 12676

From the test above, we can conclude that:
Data-type storage has a certain shrinkage. For example, the data type is number (22) and supports a maximum of 22 Bits. However, the actual storage is allocated according to the actual storage data.
Another Data Segment can be stored in multiple data files. The data block number 12676 has different meanings in different data files, and the corresponding row_number has different meanings.
We can view the corresponding segment information from user_extents and get more information from dba_extents. However, for more in-depth analysis, we can use rowid to view the information, you can even export the corresponding data block dump for underlying analysis.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian