Hands-On 01 (Memory Space Allocation on the Fly)

As a DBA, you are responsible to monitor and change a memory space allocation of your organization’s database in case of performance problems. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Oracle Memory Structures

Using the V$SGA dictionary view

Fixed Size

Variable Size

Oracle Database Files

Database Size

Datafiles

Redo Buffers

Useful parameters and views to resize memory

SHARED_POOL_SIZE

LARGE_POOL_SIZE

JAVA_POOL_SIZE

DB_BLOCK_BUFFERS

BLOCK_SIZE.

DB_CACHE_SIZE

SHARED_POOL_SIZE

LARGE_POOL_SIZE

DB_CACHE_SIZE

SHO PARAMETER

ALTER SYSTEM SET

BUFFER POOL

KEEP

RECYCLE

DEFAULT

DB_KEEP_CACHE_SIZE

DB_RECYCLE_CACHE_SIZE

HOST ERASE

DB_8K_CACHE_SIZE

Views

V$SGA

V$BUFFER_POOL

Commands:

SHO PARAMETER

ALTER SYSTEM SET

CREATE TABLESPACE

DROP TABLESPACE

-- Hands-On 01 (Memory Space Allocation on the Fly)
-- Preparation
set echo on
connect system/manager as sysdba
SET linesize 1000 pagesize 55
COL name FORMAT a40
col parameter format a40
col username format a10
pause

--Start

CLEAR SCR
-- In this exercise you will learn how to adjust some of
-- the memory space allocation on the fly without
-- shutting down the Oracle database.

-- Now, let's connect to SQLPlus as the SYSTEM/MANAGER user.

pause

CONNECT system/manager AS SYSDBA

pause

CLEAR SCR
-- Make some Memory tuning adjustments while the database is up and running. 
-- Query the V$SGA dictionary view and make a note on the below output list.
pause
SELECT * FROM v$sga
/
-- SOME IMPORTANT NOTE:
-- Total System Global Area: is the total, in bytes, that make up the SGA.

-- Fixed Size: It contains general information about the 
-- state of the database and the instance, which the background
-- processes need to access. Remember that there is no user
-- data and it's usually less than 300k in size.

-- Variable Size: It is sized by the SHARED_POOL_SIZE, 
-- LARGE_POOL_SIZE, and JAVA_POOL_SIZE memory allocation.

-- Database Size: It holds copies of data blocks read from
-- datafiles. Its size is calculated by the DB_BLOCK_BUFFERS
-- multiplied by the BLOCK_SIZE.

-- Redo Buffers: It is a circular buffer and holds information
-- about changes made to the database.
pause

CLEAR SCR
-- In the Oracle9i database, you can make memory tuning adjustments
-- on the DB_CACHE_SIZE, SHARED_POOL_SIZE, and LARGE_POOL_SIZE 
-- parameter values without shutting down the database.

-- Let's display the SHARED_POOL_SIZE, and DB_CACHE_SIZE
-- parameter values.

pause


SHO PARAMETER shared_pool_size


SHO PARAMETER db_cache_size



-- Take some notes about them.

pause


CLEAR SCR
-- Let's change the SHARED_POOL_SIZE parameter to 50 megabytes and
-- the DB_CACHE_SIZE parameter to 40 megabytes.

pause


ALTER SYSTEM SET shared_pool_size=50m
/

ALTER SYSTEM SET db_cache_size=40m
/

pause

CLEAR SCR
-- Query the V$SGA dictionary view again.

pause


SELECT * FROM v$sga
/

-- Notice that the DATABASE BUFFERS and VARIABLE size
-- were changed as a result of the memory re-allocation.

pause

CLEAR SCR
-- Now, query the V$BUFFER_POOL dictionary view.

pause


SELECT name, block_size, resize_state,
current_size, buffers
FROM v$buffer_pool
/

-- Notice that we only have one DEFAULT pool.

-- Remember that the BLOCK_SIZE times the BUFFERS gives the
-- entire buffer pool size in BYTES.

pause

CLEAR SCR
-- Configure the BUFFER POOL area to add two more buffer pools
-- (KEEP and RECYCLE) using the DB_KEEP_CACHE_SIZE and the 
-- DB_RECYCLE_CACHE_SIZE parameters.

pause


ALTER SYSTEM SET db_keep_cache_size=8000k
/

ALTER SYSTEM SET db_recycle_cache_size=8000k
/

-- Notice that the objects that needed to be kept in 
-- the memory will be stored in the BUFFER KEEP POOL
-- area and the objects that can be recycled, will use 
-- the BUFFER RECYCLE POOL area.

pause

CLEAR SCR
-- Query the V$BUFFER_POOL dictionary view.

pause

SELECT name, block_size, resize_state,
current_size, buffers
FROM v$buffer_pool
/

-- Notice that the KEEP and RECYCLE pools were added.

pause

CLEAR SCR
-- The database can have objects with different buffer sizes.

-- It can have a table using a 4k's block size and a table
-- using 8k's block size. 

-- Prior to Oracle9i, we could have only one type of block size and
-- we were not able to change it unless we were to recreate the
-- database again.

-- Allocate the buffer size to create a tablespace that can 
-- contain 8k in block size. 

pause


ALTER SYSTEM SET db_8k_cache_size=8000k
/

-- Notice that first we need to allocate memory space
-- in the buffer cache.

pause

CLEAR SCR
-- Query the V$BUFFER_POOL dictionary view.

pause


SELECT name, block_size, resize_state,
current_size, buffers
FROM v$buffer_pool
/

-- Notice that the 8K Buffer cache pool was added.

pause

CLEAR SCR
-- Query the V$SGA dictionary view again.

pause


SELECT * FROM v$sga
/

-- Notice that the VARIABLE size was reduced since
-- the buffer size was increased.

pause

CLEAR SCR
-- Now, create a tablespace that contains 8k in block size.

pause


CREATE TABLESPACE my_8k_tablespace 
DATAFILE 'c:\newfolder\my_8k_tabespace.dbf' SIZE 8000k 
BLOCKSIZE 8k 
/

-- No problem.

pause

CLEAR SCR
-- Drop the tablespace.

pause

DROP TABLESPACE my_8k_tablespace
/

pause

CLEAR SCR
-- Don't forget. Since we did not use Oracle-Managed Files, 
-- you should remove the datafile from the system.

pause


HOST ERASE c:\newfolder\my_8k_tabespace.dbf

pause

CLEAR SCR
-- Reset the DB_8K_CACHE_SIZE parameter to the NO usage value. 

pause


ALTER SYSTEM SET db_8k_cache_size=0k
/

pause

CLEAR SCR
-- Now, query the V$BUFFER_POOL dictionary view.

pause

SELECT name, block_size, resize_state,
current_size, buffers
FROM v$buffer_pool
/

pause

CLEAR SCR
-- Configure the memory so that it does not use
-- the KEEP and RECYCLE buffer pools.

pause


ALTER SYSTEM SET db_keep_cache_size=0k
/

ALTER SYSTEM SET db_recycle_cache_size=0k
/

-- From now on, these two buffer pools (KEEP and RECYCLE)
-- will not be used any longer.

pause

CLEAR SCR
-- Query the V$BUFFER_POOL dictionary view one more time.

pause

SELECT name, block_size, resize_state,
current_size, buffers
FROM v$buffer_pool
/

-- Notice that these two buffer pools are no longer 
-- active in the memory.

pause

CLEAR SCR
-- Let's change the SHARED_POOL_SIZE parameter back to 40 megabytes
-- and the DB_CACHE_SIZE parameter back to 50mega bytes.

pause


ALTER SYSTEM SET shared_pool_size=40m
/

ALTER SYSTEM SET db_cache_size=50m
/

pause

CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause