You will find companies who run datawarehouse and face multiple performance issues which can be related to
1) Millions of Records
2) Tablespace Space Management
3) Hardware OR O/S

Suppose a database is running with a db_block_size = 8k and there are tables which has long rows, using 8k as db_block_size might result into a performance issue like reading, row chaining etc.

Possible Solution :-

You can create a block size of 16K or 32k for a new tablespace and move the table into this newly created tablespace and hence it improves the performance.

Please find below the steps to create a tablespace with 16K block size.

startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed SIZE 1218992 bytes
Variable SIZE 75499088 bytes
DATABASE Buffers 205520896 bytes
Redo Buffers 2973696 bytes
DATABASE mounted.
DATABASE opened.
SHOW parameter sga_target;
NAME TYPE VALUE
----------------
sga_target big INTEGER 272M

SHOW parameter sga_max_size;
NAME TYPE VALUE
----------------------------------
sga_max_size big INTEGER 272M

SHOW parameter db_block_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size INTEGER 8192

ALTER system SET db_cache_size=25m scope=BOTH;
System altered.

SHOW parameter db_cache_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big INTEGER 28M

ALTER system SET db_16k_cache_size=30m;
System altered.

SHOW parameter db_16k_cache_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big INTEGER 32M

SQL>SELECT name FROM v$database;
NAME
---------
PROD

SELECT name FROM v$datafile;
NAME
--------------------------------------------------------------------------------/u02/home/prod/system01.dbf
/u02/home/prod/undotbs01.dbf
/u02/home/prod/sysaux01.dbf
/u02/home/prod/users01.dbf

CREATE tablespace tbs2 datafile '/u02/home/prod/tbs2.dbf' SIZE 100m blocksize 16k;
Tablespace created.

DESC dba_tablespaces;
Name NULL? TYPE
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3)

SELECT block_size,tablespace_name FROM dba_tablespaces;
BLOCK_SIZE TABLESPACE_NAME
---------- ------------------------------
8192 SYSTEM
8192 UNDOTBS1
8192 SYSAUX
8192 TEMP
8192 USERS
16384 TBS2

6 ROWS selected.
Share →
0 comments
Skip to toolbar