Hands-On 01 (Why tuning and what is Granule unit)

As a DBA you, are also responsible for detecting performance problems of your organization’s database. You need to know how to start investigating a performance problem and then solve it. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

1. Steps required when doing performance tuning

2. Granule definition

-- Hands-On 01 (Why tuning and what is Granule unit)
-- 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

--Start
CLEAR SCR
-- In this exercise you will learn about the GRANULE unit, 
-- and how to perform performance tuning.


pause
pause

CLEAR SCR

/*
When our clients complain about application performance,
We look at the problem with the following sequence.

1- SQL Statement tuning,
2- Optimizing sorting Operations,
3- Memory Allocation.
a- Operating System Memory size,
b- Oracle allocated Memory size (SGA-System Global Area),
4- I/O contentions,
5- Latches & Locks,
6- Network Load.

Remember that the SGA components are allocated and de-allocated
in units of contiguous memory called Granule. So it is very
important that the amount of allocated memory must be a product of 
the Granule size and an integer.

If the SGA is less than 128MB, then a granule is 4MB. 
If the SGA is larger than 128MB, then a granule is 16MB.

The minimum number of granules allocated at startup is 
1 for the buffer cache, 
1 for the shared pool, and 
1 for the fixed SGA, 
which includes redo buffers.
*/
pause
pause


CLEAR SCR

-- The rest of our Hands-On will focus on the above subjects to 
-- demonstrate to you how to find a performance problem.

-- Good luck.
--
pause
pause