"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
Introduction
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:
Hands-on
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) |
Questions:
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?
|