UNDERSTAND DYNAMIC SGA AND MULTIPLE BLOCK SIZE

 

Database Version

Oracle 9.0.1.x

Article Revision Date

21-OCT-2003

Web

www.SamOraTech.com

Author

Sameer Wadhwa

 

 

 

 

 

 

 

In this article, I am going to explain you, the powerful feature of oracle 9i,  DYNAMIC SGA and MULTIPLE BLOCK SIZE.   Both these feature are very important for tuning, performance and managing oracle database efficiently and effectively.

 

DYNAMIC SGA

 
In Oracle 9i you can grow and shrink the SGA depend upon your requirement without shutdown down the instance.
 
The Dynamic adjusted parameters are 
 
Parameter Name
 
Command
 
SHARED_POOL_SIZE
 
ALTER SYSTEM SET SHARED_POOL_SIZE=<SIZE>[M|K]
 
DB_32K_CACHE_SIZE
 
ALTER SYSTEM SET DB_32K_CACHE_SIZE=<SIZE>[M|K]
 
DB_16K_CACHE_SIZE
 
ALTER SYSTEM SET DB_16K_CACHE_SIZE=<SIZE>[M|K]
 
DB_8K_CACHE_SIZE
 
ALTER SYSTEM SET DB_8K_CACHE_SIZE=<SIZE>[M|K]
 
DB_4K_CACHE_SIZE
 
ALTER SYSTEM SET DB_4K_CACHE_SIZE=<SIZE>[M|K]
 
DB_2K_CACHE_SIZE
 
ALTER SYSTEM SET DB_2K_CACHE_SIZE=<SIZE>[M|K]
 
DB_CACHE_SIZE
 
ALTER SYSTEM SET DB_CACHE_SIZE=<SIZE>[M|K]
 
DB_CACHE_ADVICE
 
ALTER SYSTEM SET DB_CACHE_ADVICE=<SIZE>[M|K]
 
DB_KEEP_CACHE_SIZE
 
ALTER SYSTEM SET DB_KEEP_CACHE_SIZE=<SIZE>[M|K
 
DB_RECYCLE_CACHE_SIZE
 
ALTER SYSTEM SET DB_RECYCLE_CACHE_SIZE=<SIZE>[M|K]
 
LARGE_POOL_SIZE
 
ALTER SYSTEM SET LARGE_POOL_SIZE=<SIZE>[M|K]
 
 
 
 
 
 
 
Example (Change the SHARED_POOL_SIZE Dynamically):-
 
C:\>sqlplus /nolog
 
SQL*Plus: Release 9.0.1.0.1 - Production on Fri Apr 19 10:55:06 2002
 
(c) Copyright 2001 Oracle Corporation.  All rights reserved.
 
Or9i SamSQL>CONNECT SYS/CHANGE_ON_INSTALL AS  SYSDBA
Connected.
Or9i SamSQL>SHOW PARAMETER SHARED_POOL_SIZE
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------
shared_pool_size                     big integer 41943040
 
 
Or9i SamSQL>ALTER SYSTEM SET SHARED_POOL_SIZE = 60M;
System altered.
Or9i SamSQL>show parameter SHARED_POOL_SIZE
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------
shared_pool_size                     big integer 62914560
 
You have seen how we changed the SHARED_POOL_SIZE from 41943940 (40M) to 62914560 (60M).
 
Oracle limits the increase of size up to SGA_MAX_SIZE parameter defined in the initialization 
parameter file. SGA memory can not be increase beyond SGA_MAX_SIZE. If SGA_MAX_SIZE 
parameter is not enough for increasing the memory of dynamic parameters, you will get a 
ORA-00384 error. In this case either you have to increase the SGA_MAX_SIZE parameter or 
decrease the memory of dynamic parameter.
 
 
For Example
 
 
 
Or9i SamSQL>

Or9i SamSQL>alter system set db_cache_size=135m;

alter system set db_cache_size=135m

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00384: Insufficient memory to grow cache
 

 

Or9i SamSQL>alter system set  SGA_MAX_SIZE=150M scope=spfile;

System altered.

 

The above command change the  limit of max size  to 150M. You have to shutdown and restart the system to make this effect. As of Oracle 9.0.1.1.1, people mostly get ORA-03113: end-of-file on communication channel, when they  startup the database after shutdown immediate. The best solution for this is to exit from sqlplus and login again.

 

Or9i SamSQL>exit;

Disconnected from Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production

With the Partitioning option

JServer Release 9.0.1.1.1 - Production

 

 

C:\>SQLPLUS /NOLOG

SQL*Plus: Release 9.0.1.0.1 - Production on Fri Apr 19 11:51:51 2002

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Or9i SamSQL>CONNECT SYS/CHANGE_ON_INSTALL AS SYSDBA

Connected to an idle instance.

 

Or9i SamSQL>STARTUP

ORACLE instance started.

 

 

On windows NT when you set the SGA_MAX_SIZE, SGA has automatically set equal to the SGA_MAX_SIZE. In this case to find out the actual SGA size allocated to the instance is calculated by the formula 

 

SGA SIZE (Approx) =  (DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE +
                      DB_nk_CACHE_SIZE + SHARED_POOL_SIZE + LARGE_POOL_SIZE +
                      JAVA_POOL_SIZE + LOG_BUFFERS + 1MB )
 
The following SQL will help you to calculate the approximate size of SGA
 

 

Or9i SamSQL>select SUM(VALUE)+(1024*1024) from v$parameter where name in

  2  ('db_16k_cache_size','db_2k_cache_size','db_32k_cache_size',

  3  'db_4k_cache_size','db_8k_cache_size','db_cache_size',

  4  'db_keep_cache_size','db_recycle_cache_size',

  5  'java_pool_size','large_pool_size',

  6  'shared_pool_size','log_buffer')

  7  /

 

SUM(VALUE)+(1024*1024)

----------------------

             156762112

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

MULTIPLE BLOCK SIZES

 

Oracle 9i supports multiple block sizes, which means you can create tablespace(s) of desired block size which will help you to organize your database for better performance. You can put large objects which may have mostly full table scan in a tablespace(s) of larger block size and smaller objects  or comparable size objects , to other tablespace(s). The other advantage of multiple block size is in transportable tablespaces between databases of different block sizes.

 

For configuring multiple block sizes in your database, you have to configure   sub cache, which is nothing but the part of the memory buffers or block buffers.  The Oracle 9i introduces cache related parameters as follows :-

 

Or9i SamSQL>show parameter cache

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------------

db_16k_cache_size                    big integer 0

db_2k_cache_size                     big integer 0

db_32k_cache_size                    big integer 0

db_4k_cache_size                     big integer 0

db_8k_cache_size                     big integer 0

db_cache_advice                      string      OFF

db_cache_size                        big integer 33554432

 
DB_CACHE_SIZE is a replacement of DB_BLOCK_BUFFERS in the older version of oracle 8i and before. The other parameters db_8k_cache_size, db_4k_cache_size, db_32k_cache_size, db_2k_cache_size, db_16k_cache_size have the initial default value “0”. You can change values of these parameters dynamically.
 
To configure, the multiple block size feature,  alter the system as : -

 

Syntax :- ALTER SYSTEM SET <PARAMETER NAME> = < NEW SIZE> [M|K]

 

Let us say your block size is 4K and you want to configure database for creating a tablespace of block size 4K and 8K respectively.

 

For tablespace of blocksize 4K, alter the database as

 

Or9i SamSQL>ALTER SYSTEM SET DB_2K_CACHE_SIZE = 4M;

 

System altered.

 

For Tablespace of blocksize 8K, alter the database as

 

Or9i SamSQL>ALTER SYSTEM SET DB_8K_CACHE_SIZE = 8M;

 

System altered.

 

Or9i SamSQL>ALTER SYSTEM SET DB_8K_CACHE_SIZE = 50M;

 

System altered.

 

 

Or9i SamSQL>show parameter cache

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_16k_cache_size                    big integer 0

db_2k_cache_size                     big integer 4194304

db_32k_cache_size                    big integer 67108864

db_4k_cache_size                     big integer 0

db_8k_cache_size                     big integer 8388608

db_cache_advice                      string      OFF

db_cache_size                        big integer 33554432

 

DB_2K_CACHE_SIZE and DB_8K_CACHE_SIZE have now changed to 4M and 8M respectively as we already altered the system in step 1, but the changed will effect only after restart of the database.

 

Point to Note: I set the db_32k_Cache_size to 50M but actually it took 64M.  Why the Oracle added 14m  by itself. The answer is granule size.

 

The Total cache size is the sum of all the cache. You can query the V$BUFFER_POOL to find out the total allocated cache to SGA.

 

select sum(current_size) “Total cache Size”
from v$buffer_pool;

 

Now you can  create a tablespace of  required block size.

 

 

Or9i SamSQL>CREATE TABLESPACE TESTBLK2K DATAFILE 'E:\TEST1.DNF' SIZE 10M BLOCKSIZE 2K;

 

Tablespace created.

 

Here BLOCKSIZE clause is important which will tell oracle to create a tablespace of  size 2K.

 

Verify the results: -

 

Or9i SamSQL>SELECT TABLESPACE_NAME,BLOCK_SIZE FROM DBA_TABLESPACES ;

 

TABLESPACE_NAME                BLOCK_SIZE

------------------------------ ----------

SYSTEM                               4096

UNDOTBS                              4096

CWMLITE                              4096

DRSYS                                4096

EXAMPLE                              4096

INDX                                 4096

TOOLS                                4096

USERS                                4096

TESTBLK2K                            2048

 

The above results show that TSTBLK2K has created with block size 2K. Now let us all other possible errors while configuring multiple block size.

 

 

Possible Errors While Configuring Multiple Block Size

(1)

 

Or9i SamSQL>CREATE TABLESPACE TESTBLK2K DATAFILE 'E:\TEST1.DNF'  SIZE 10M BLOCKSIZE 2K;

CREATE TABLESPACE TESTBLK2K DATAFILE 'E:\TEST1.DNF'  SIZE 10M BLOCKSIZE 2K

*

ERROR at line 1:

ORA-29339: tablespace block size 2048 does not match configured block sizes

 

Solution :- Check the cache size by SHOW PARAMETER CACHE , you have to configure the required block size.

 

 

(2)

 

Or9i SamSQL>ALTER SYSTEM SET DB_2K_CACHE_SIZE = 8M;

alter system set db_2k_cache_size = 8M;

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00384: Insufficient memory to grow cache

 

Solution :-  To solve this problem, you have to increase the SGA_MAX_SIZE parameter through ALTER SYSTEM as

ALTER SYSTEM SET SGA_MAX_SIZE=<NEW SIZE> [M|K] Scope=spfile. You have to restart the database to make this effect.

 

 

 

(3)

 

Or9i SamSQL>STARTUP;

ORA-03113: end-of-file on communication channel

 

Solution :-  As of Oracle 9.0.1.1.1, after changing the parameter dynamically through alter system with scope=SPFILE, people mostly get this error when they startup the database after shutdown immediate. The best solution for this is to exit from sqlplus and login again.

 

Or9i SamSQL>exit;

 

C:\>SQLPLUS /NOLOG

Or9i SamSQL>CONNECT SYS/CHANGE_ON_INSTALL AS SYSDBA

Connected to an idle instance.

Or9i SamSQL>STARTUP

ORACLE instance started.

 

 

 

 

 

 

Reference: -

 

Oracle 9i Database Administrator’s Guide (Volume 1)

Oracle Metalink Support

 

 

 

Hope the above information will help …

-- Author

Sameer Wadhwa

 

Copyright©  Oracle Techniques www.SamOraTech.com

Disclaimer:

The author does not guarantee that this information is error-free.

 If any errors are found, please report them to author at  SamOracle@Yahoo.com

 

Ask and Solve Database Problems at  OraTechSupportGroup

What do you think about Oracle Techniques?