Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Training

    Home      .Services     Login       Start Learning     Certification      .                 .Share your BELIEF(s)...


. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       Email2aFriend    |


Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Performance


Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 |


Lesson 02

"To sit alone with my conscience will be judgment enough for me." - Charles William Stubbs


Read first then play the video:

   PRF-VIDEO -Why tuning


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:


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

Performance Tuning Steps

When your clients complain about application performance, you 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.

Granule Unit

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.


"Never mistake motion for action." - Ernest Hemingway (1899-1961)



Q: How do you perform tuning on your database?

Q: What is a Granule Unit?

Q: How does a granule unit work in an increasing or decreasing the database memory?

Q: When a client complain about his/her application performance, what are the list of steps you go through?

Q: If the size of your SGA is greater than 128M, what is the size of your database granule unit?

Q: If the size of your SGA is less than 128M, what is the size of your database granule unit?

Q: What is the minimum number of granules allocated to the buffer cache, and the shared pool?