Oracle Performance Tuning Fundamental Exam Answers
NOTE:
The answers go with their sequences. If a question was not answer, that means
that it a repeating question and the answer was given by the previous questions
or it is not in the scope of this subject.
“What we see depends mainly on what we look for.”
--Sir John Lubbock
|
Q:
What are the Oracle Architectural components?
A:
The Oracle Architectural components are:
-
Memory (SGA) such as Buffer
Cache, Shared Pool, Redo Log Buffer, Large Pool, Java Pool, etc.
-
Background Processes such as Log
Writer, DB Writer, Checkpoint, Archiver, SMON, etc.
-
Oracle Physical Layout such as
Datafiles, Controlfiles, Online Redo log files, Parameter file, Password
file, etc.
Q:
What are the Oracle Memory Components?
A:
All components such as Shared Pool
(Library
Cache
, Dictionary Cache
), Buffer Cache
, Online Redo Log file
, Large Pool
, Java Pool
as well
as a few other items are referred to as the System Global Area
(SGA
). And the place stores information like bind
variable values
, sort areas, cursor handling, etc for a specific
user is called Program Global Area
(PGA
). The PGA is used to store only real values in place
of bind variables for executing SQL statements. The combination of these two
memories structure while they are running is called Oracle Instance.
Q:
What is the Server Parameter File?
A:
The Server Parameter File is a binary file and
Oracle uses it to change the most of its system parameters dynamically.
Q:
What is the Parameter File?
A:
The Parameter file is a configuration file and
it contains all the Oracle instance and database configuration parameters. When
you change any parameter using this file, you should shutdown and startup the
Oracle Database.
Q:
How do you use the init.ora file?
A:
The init.ora file is called initialized or
parameter file. It is a configuration file.
Q:
What is the System Global Area (SGA)?
A: The SGA contains of Shared Pool
(Library
Cache
, Dictionary Cache
), Buffer Cache
, Online Redo Log file
, Large Pool
, Java Pool
as well
as a few other items.
Q:
What is the Shared Pool in SGA?
A:
The Shared Pool
contains
the Library Cache
and the
Dictionary Cache
as well
as a few other items, which are not in the scope of this section. The Library
Cache holds all users’ SQL statements, Functions, Procedures, and Packages. It
stores parsed
SQL
statement with its execution plan for reuse. The Dictionary Cache, sometimes
also referred to as the Row Cache
, holds the Oracle repository
data
information such as tables, indexes, and columns definitions, usernames,
passwords, synonyms, views, procedures, functions, packages, and privileges
information.
Q:
What does the Buffer Cache hold in SGA?
A:
The Buffer Cache
holds
users’ data. Users query their data while they are in the Buffer Cache. If
user’s request is not in the Buffer Cache then server process has to bring it
from disk. The smallest unit in the buffer cache is an Oracle block. The buffer
cache can be increased or decreased by granule unit. The smallest Granule Unit
is 4Meg if the SGA
size is
less than 128Meg and the smallest Granule Unit become 16Meg is the SGA size is
more than 128Meg.
Q:
What are the differences between the Library Cache and Dictionary Cache?
A:
The Library Cache holds user’s SQL
statements, PL/SQL programs, but the Dictionary Cache holds only repository
information such as user’s table name, its access privileges, and etc.
Q:
What is the Redo Log Buffer in SGA?
A:
The Redo Log Buffer
holds
users’ entries such as INSERT, UPDATE, DELETE, etc (DML) and CREATE TABLE,
DROP TABLE (DDL). The Redo Entries are information that will be used to
reconstruct, or redo, changes made to a database. The Log Writer
writes the entries into the
Online Redo Log files
when a
COMMIT occurs, every 3 seconds, or when one third of the Redo Log Buffer is
full. That will guarantee a database recovery to a point of failure if an Oracle
database failure occurred.
Q:
Describe the Large Pool component in SGA.
A:
The Large Pool
holds
information about the Recovery Manager (RMAN) utility when RMAN is running. If
you use the Multi-threaded Server (MTS) process, you may allocate the Oracle
Memory structure such that you can get advantage of using Large Pool instead of
the Shared Pool
. Notice that when you use dedicated servers, user
session information is housed in the PGA
Q:
Describe the Multi-threaded Server process.
A:
The Multi-threaded Server process will be used
when a user send his/her request by using a shared server. A user’s request
will be assigned to a dispatcher based on the availability of dispatchers. Then
the dispatcher will send or receive request from an assigned shared server.
Q:
What are PGA and UGA?
A: When you are running
dedicated servers then the session information can be stored inside the process
global area (PGA). The UGA
is the user
global area, which holds session-based
information. When you are running shared servers then the session
information can be stored inside the user global area (UGA)
Q:
Describe the log writer background process (LGWR).
A:
The LGWR’s job is to write the redo user’s
entries from the Redo Log Buffer
Q:
How often does LGWR write user’s entries to the Online Redo Log Buffer
files?
A:
It writes user’s entries when the
buffer exceeds one third of the Redo Log Buffer, every 3 seconds, or when a user
executes the commit SQL statement.
Q:
Describe the Checkpoint process.
A:
The Checkpoint signals DB writers to write all
dirty blocks into the disk. The Checkpoint will occurred either by a specific
defined time, size of the Online Redo Log file
used by
DBA, or when an Online Redo log file will be switched from on log file to
another.
Q:
How do you automatically force the Oracle to perform a checkpoint?
A:
The following are the parameters that will be
used by a DBA to adjust time or interval of how frequently its checkpoint should
occur on its database.
LOG_CHECKPOINT_TIMEOUT
= 3600
# every one hour
LOG_CHECKPOINT_INTERVAL
=1000 #
number of OS blocks
Q:
What is the Recovery Process?
A:
The RECO
will be
used only if you have a distributed database. You use this process to recover a
database if a failure occurs due to physical server problem or communication
problem.
Q:
What is the Lock Background Process?
A:
The LCKn
background
process will be used if you have multiple instances accessing to only one
database. An example of that is a Parallel Server or a Real Application
Clusters.
Q:
How does the Archive Process work?
A:
This background process archives the Online
Redo Log file
when you
are manually or automatically switching an Online Redo Log file. An example of
manually switching is: ALTER SYSTEM SWITCH LOGFILE or ALTER SYSTEM ARCHIVE LOG
CURRENT.
Q:
How do you configure your database to do an automatic archiving?
A:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
Q:
What is the System Monitor Process?
A:
The SMON job is: when you start your database,
it will make sure that all datafiles, controlfiles, and log files are
synchronized before opening a database. If they are no, it will perform an
instance recovery. It will check the last SCN that was performed against the
datafiles. If it finds that there are transactions that were not applied against
the datafile, then it will recover or synchronize the datafile from either the
Online Redo Log files
or the
Archive Log files. The smaller Online Redo log files will bring a faster
database recovery.
Q:
Describe the Program Monitor Process Job.
A:
A user may be disconnected either by canceling
its session or by communication link. In either act, the PMON
will
start and perform an action to clean the reminding memory allocation that was
assigned to the user.
Q:
What are the differences between the SPFILE and PFILE startup?
A:
You can read or change the init.ora file (PFILE)
for startup of the Oracle database. It contains all Oracle parameters file to
configure a database instance. In this file, you can reset and change the Buffer
Cache
size,
Shared Pool
size,
Redo Log Buffer
size,
etc. You also can change the location of your control files, mode of a database
such as archivelog mode or noarchivelog mode, and many other parameter options
that you will learn them in the course of this book.
But
using Server Parameter File-SPFILE, you can not read the file. It is in a binary
format. If you want to change database parameters dynamically, you should create
the Server Parameter file (CREATE SPFILE
FROM
PFILE
) and startup your database using the SPFILE file.
There are some few parameters that you still need to shutdown and startup the
database, if you want to make the parameter in effect.
Q:
What is the controlfile?
A:
You cannot read this file and it is in a
binary format. If you want to see the content of control file or the layout of
your database, you should use the ALTER DATABASE BACKUP CONTROLFILE TO TRACE
statement.
It writes a trace file into the %ORACLE_BASE\admin\<db-name>\UDUMP
directory.
Q:
How do you backup your database controlfiles?
A:
SQL> ALTER DATABASE BACKUP CONTROLFILE TO
c:\ctnlrfl.bk;
Q:
What does a controlfile contain?
A:
It contains information the structure of your
database layout, database name, last System Change Number (SCN) number, your
database mode (archivelog mode or noarchivelog mode), maximum number of log
files, maximum number of log members, maximum number of instances, maximum of
number of datafiles, the location of the database Online Redo Log files
, and backup information.
Q:
Describe the password file.
A:
The password file is an external file that
contains the password of sysdba or sysoper. To use the password file you should
set the REMOTE_LOGIN_PASSWORD
parameter to exclusive or
shared mode in the Parameter File
(Example: REMOTE_LOGIN_PASSWORD=EXCLUSIVE).
Q:
How do you create a password file?
A:
To create the password file, you should run
the ORAPWD utility from operating system.
For
example:
MS-DOS>
ORAPWD FILE=%ORACLE_HOME\dbs\orapw<sid>.pwd
\
PASSWORD=mypass ENTRIES=3
The
ENTRIES parameter specifying the number of user entries allowed for the password
file. Now, the DBA can be connected to the database as a user with sysdba
privilege.
Q:
Describe the Online Redo Log file.
A:
The Online Redo Log files
hold the
Redo Entries. You should have at least two or more Redo Log Groups. Each group
may have more than one member. It is a good practice to multiplex Online Redo
Log members. The Redo Entries are information that will be used to reconstruct,
or redo, changes made to a database. The Log Writer
writes the entries into the
Online Redo Log files when a COMMIT occurs, every 3 seconds, or when one third
of the Redo Log Buffer
is full.
That will guarantee a database recovery to a point of failure if an Oracle
database failure occurred.
Q:
How do you perform tuning on your database?
A:
When there are complain about application
performance, we should 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
Q:
What is a Granule Unit?
A:
The Granule Unit is the smallest unit that the
SGA
components
are allocated and de-allocated in units of contiguous memory. So it is very
important that the amount of allocated memory must be a product of the
Granule size and an integer. If it is not then the Oracle database will round
them.
Q: How does a granule
unit work in an increasing or decreasing the database memory?
A:
You only can increase or decrease the SGA
based on the Granule Unit. Therefore, the size you allocate or de-allocate must
be a multiple of a Granule Unit size. For example: if your granule unit size is
4Meg bytes and you increate the size of your buffer cache with 9Mg bytes more
memory space then your Buffer cache will be allocated either only 8Meg bytes of
memory.
Q:
If the size of your SGA is greater than 128M, what is the size of your
database granule unit?
A:
If the SGA is larger than 128MB, then a
granule is 16MB.
Q:
If the size of your SGA is less than 128M, what is the size of your
database granule unit?
A:
If the SGA is less than 128MB, then a granule
is 4MB.
Q:
What is the minimum number of granules allocated to the buffer cache, and
the shared pool?
A: 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.
Q:
How do you change a size of the shared pool?
A:
ALTER SYSTEM SET shared_pool_size=100M
scope=SPFILE;
Q:
How do you keep an object in the Shared Pool memory?
A:
Use the KEEP procedure to pin the STANDARD
package.
SQL> BEGIN
SYS.DBMS_SHARED_POOL.KEEP('SYS.STANDARD')
;
END;
Q:
How do you remove an object from the Shared Pool memory?
A:
Use the UNKEEP procedure to pin the STANDARD
package.
SQL> BEGIN
SYS.DBMS_SHARED_POOL.UNKEEP('SYS.STANDARD')
;
END;
Q:
How do you calculate the Dictionary Cache Hit ratio value?
A:
We should use the following SQL statement to
calculate the overall dictionary cache hit ratio.
SQL> SELECT (SUM(gets - getmisses)) / SUM(gets)
AS "Dictionary Hit Ratio
"
FROM v$rowcache
/
Q:
What are the Major components of the Shared Pool Memory?
A:
The major components of the shared pool are:
the library cache, the dictionary cache (row cache), and the User Global Area (UGA).
Q:
What is the User Global Area (UGA)?
A:
The UGA
is the user
global area, which holds session-based
information. When you are running shared servers then the session
information can be stored inside the user global area (UGA) and when your
session does some sorting, some of the memory allocated for sorting -
specifically the amount defined by parameter sort_area_retained_size -
comes from the SGA and the rest (up to sort_area_size)
comes from the PGA (Snnn).
Q:
When does the Oracle database use UGA?
A:
When you are running shared servers then the
session information can be stored inside the user global area (UGA).
Q:
What does the SHARED_POOL_RESERVED_SIZE parameter?
A:
That the SHARED_POOL_RESERVED_SIZE
parameter can
be reserved for large objects.
Q:
What does the DBMS_SHARED_POOL package?
A:
The DBMS_SHARD_POOL package contains the
stored procedures (functions and/or procedures) that provide ease of use for the
developers to manipulate size of the shared pool, allocate objects in the shared
pool, etc.
Q: How do you change the size of buffer cache in the SGA
memory?
A: To increase or
decrease the Buffer Cache size to a specific size.
SQL> ALTER SYSTEM SET db_cache_size
=55M
/
Q: What is the Dynamic Buffer Cache Advisory parameter?
A: To assist you in the
proper configuration, Oracle provided you with the DB_CACHE_ADVICE
parameter. When this parameter sets to ON, Oracle begins collecting
statistics about cache utilization and projects the physical I/O for 20 cache
sizes, ranging from 10 to 200 percent of the current size.
Q: What is the Least Recently Used (LRU) list in the
buffer cache memory?
A: The Least Recently
Used (LRU) list is a list of blocks that have been used at the least amount of
time recently at the SGA memory.
Q: What is a Dirty Buffer in the Buffer cache memory?
A: The dirty buffers
are
blocks in the buffer cache that have been changed. The dirty buffers are moved
to the dirty list and written to data files by DB Writer processes
(DBWn).
Q: How do you perform tuning on the Buffer Cache Memory?
A: SQL> ALTER SYSTEM
SET db_cache_advice
=ON;
Notice that there are three possible values:
1 -- ON - allocates memory and gathers statistics.
2 -- OFF - disables advice statistic gathering.
3 -- READY - allocates memory, but statistics are not gathered.
Q: How do you check a SGA memory size?
A: SQL> SHOW
PARAMETER
sga
Q: How do you use the V$PARAMETER view?
A: You can use the
V$PARAMETER view to query all the information that relate to the SGA components.
SQL> SELECT SUM(value) as "SGA Size"
FROM v$parameter
WHERE name in
('shared_pool_size','db_cache_size','log_buffer','java_pool_size')
/
Q: How many lists are the buffers organized in the buffer
cache?
A: The buffers in the
buffer cache are organized in two lists:
1 -- The Least Recently Used
(LRU) list, and
2 -- the Dirty list.
Q: How do you measure the buffer cache hit ratio?
A: To calculate the
Buffer Cache Hit Ratio from the V$SYSSTAT
view.
SQL> SELECT 1- ((p.value - l.value -
d.value) / s.value)
AS "Buffer Cache
Hit
Ratio
"
FROM
v$sysstat s, v$sysstat l, v$sysstat d, v$sysstat p
WHERE
s.name = 'session logical reads'
AND d.name = 'physical reads direct'
AND
l.name = 'physical reads direct (lob)'
AND
p.name = 'physical reads'
/
Q: How do you create a cache table?
A: To create a table to
be kept in the KEEP buffer pool
SQL> CREATE TABLE iself.mykeep
(col1 NUMBER,
col2
VARCHAR2(10))
STORAGE
(BUFFER_POOL KEEP)
/
Q: How do you calculate a hit ratio for multiple pools?
A: To calculate the Hit
Ratio for multiple pool:
SQL> SELECT name,
1-(physical_reads/(db_block_gets
+ consistent_gets)) "Hit Ratio"
FROM
v$buffer_pool_statistics
WHERE
db_block_gets + consistent_gets > 0
/
Q: How do you cache an object into the buffer pools using
hint in a SQL statement?
A: To cache a table by
hint in a SQL statement.
SQL> SELECT /*+ CACHE
(iself.dept)
*/
*
FROM
iself.dept
/
Q:
What is a FREELIST?
A:
The
FREELIST space is an allocated space in a table that contains all the blocks’
references which are candidate for more inserted records. Any contentions on the
FREELIST allocation will create a performance problem.
Q: How do you diagnose the FREELIST contentions in the
buffer cache?
A: To diagnose the
FREELIST contention in the Buffer Cache.
SQL> SELECT s.segment_name, s.segment_type,
s.FREELISTs, w.wait_time,
w.seconds_in_wait, w.state
FROM
dba_segments s, v$session_wait w
WHERE
w.event='buffer busy waits'
AND w.p1=s.header_file
AND w.p2=s.header_block
/
Q: How do you use the DEFAULT pool?
A: The DEFAULT pool
is used
the same way as the standard Buffer Cache block size
Q: How do you use the KEEP pool?
A: The KEEP buffer pool
is used
to keep buffers in the pool as long as possible for data blocks that are
likely to be reused.
Q: When do you use the RECYCLE pool?
A: The RECYCLE buffer
pool
is used
as a temporary host block from segments that you don't want to interfere
with blocks in the DEFAULT Buffer Pool.
Q: What is the V$SYSSTAT view?
A: It is a view that
contains the Oracle system usages such as session logical reads, physical reads
direct, etc.
Q: What is the V$BUFFER_POOL view?
A: It is a view that
contains the Oracle buffer pool configurations. You can use this view to query
the buffer pool configurations information such
as DEFAULT, KEEP, or RECYCLE pools.
Q: What is the V$BUFFER_POOL_STATISTICS dictionary view?
A: It is a view that
contains the Oracle buffer pools statistic. You can calculate the Hit Ratio for
multiple pools using this view.
Q: Describe the session logical reads, physical reads
direct, and physical reads direct (lob), and physical reads in the V$SYSSTAT
view.
A: The ‘physical
reads’ value is a number of read that Oracle physically performs from hard
disk including all the ‘physical reads direct’ and ‘physical read direct
(lob).’ You want to be sure that the ‘physical reads direct’ values be as
high as possible in a respect to the ‘physical reads’ value. Also, you want
to be sure that the ‘session logical reads’ value is very high. The
‘session logical reads’ value is the number of times that Oracle reads a
block from the memory (Buffer Cache) rather than a disk.
Q: What is an acceptable range for a buffer cache hit
ratio?
A: If the Buffer Cache
Hit Ratio is more than 90% then there is no problem. If the Buffer Cache Hit
Ratio is between 70% and 90% then there could be a problem. And if the Buffer
Cache Hit Ratio is less than 70%, there is definitely a problem and the Buffer
Cache size needs to be increased.
Q: What does the following SQL statement?
SQL> SELECT
s.segment_name, s.segment_type,
s.FREELISTs, w.wait_time,
w.seconds_in_wait, w.state
FROM dba_segments s, v$session_wait w
WHERE w.event='buffer busy waits'
AND w.p1=s.header_file
AND w.p2=s.header_block
/
A:
It will diagnose the FREELIST contention in
the Buffer Cache. The DBA_SEGMENTS
view contains all the created users’ segments such as tables, indexes, etc.
The V$SESSION_WAIT view contains dynamic information for that instance
and for that specific time. Its content will be regenerated when you restart an
instance. It contains the contentions information such as ‘buffer busy
waits’ for a file or a block, etc.
Q:
How do you monitor the redo log buffer memory size?
A:
Keep your eyes on the redo buffer allocation
entries.
SQL> SELECT name, value
FROM v$sysstat
WHERE name = 'redo buffer allocation entries'
/
Note that if you have a positive number, that means that you may have a
problem. Be sure that you have compared the above positive number with the Redo
entries and it should not be more than 1%.
Also, you should query the redo
allocation buffer entries ratio. Be sure that your ratio is not more than 1%.
SQL> SELECT a.value/b.value "redo buffer entries ratio"
FROM v$sysstat a, v$sysstat b
WHERE a.name = 'redo buffer allocation entries'
AND b.name = 'redo entries'
/
If the number is greater than 1%, you should increase the size of the Redo
Log buffer
Q:
How do you re-size the redo log buffer memory size?
A:
ALTER SYSTEM SET log_buffer = 55M
scope=SPFILE;
Q:
How do you monitor a waiting session in the redo log buffer?
A:
To check to see if there are any other
sessions waiting for log buffer space to the following SQL statement.
SQL> SELECT sid, event, seconds_in_wait, state
FROM v$session_wait
WHERE event = 'log buffer space'
/
If
the Log Buffer space waits exist, consider increasing the size of the redo log.
Also you should check the speed of the disk that the Online Redo Log files are
in.
Q:
How do you monitor your online full redo log file?
A:
To check to see if that Online Redo Log file
is full and the server is waiting for the next Redo Log file do the following
SQL statement.
SQL> SELECT name, value
FROM v$sysstat
WHERE name = 'redo log space requests'
/
Q:
Describe the redo log entries.
A:
The redo entries in the redo log files are
used for database recovery. The buffer is usually flushed by reaching: one
third of the redo log buffer size, frequent COMMITs, and every 3 seconds.
Q: How do you reduce a database I/O problem?
A: In order to reduce
the I/O contention we should at least consider the following steps:
01 -- Use Oracle to distribute the data files
across
multiple disks evenly.
02
-- Use the Oracle partitioning
03 -- Use the locally managed tablespace
option,
unless you have a reason not to do so.
04 -- Use only the Redo Log files, controlfiles
, and dump files on the same disk.
05 -- Use all UNDO or ROLLBACK Segments
on the
same disk.
06 -- Use the Rollback and redo log files
on a
separate disk.
07 -- Use the data, index, SYSTEM, and UNDO tablespaces
on a
separate disk.
08 -- Use the data and temporary tablespaces
on a
separate disk.
09 -- Use the Redundant Array
of
Inexpensive Disks.
10 -- Use the raw device if possible.
Q: How do you monitor a database I/O problem?
A: To monitor the I/O
transaction activity in the data files across multiple disks we should write the
following SQL statement.
SQL> SELECT file_name, phyrds, phywrts
FROM v$filestat a, dba_data_files b
WHERE a.file# = b.file_id
/
Q: How do you monitor the checkpoint process activities of
a database?
A: To monitor the
checkpoint process activities we should perform the following SQL statement.
SQL> SELECT name, value
FROM v$sysstat
WHERE name like 'background check%'
/
If the "background check started" value is greater than the "background
check completed" value, you should increase the size of the REDO LOG files.
Q: How do you tune the checkpoint process activities?
A: You should just increase
the size of the REDO LOG files.
Q: How do you use the V$SYSSTAT view?
A: You may use the
V$SYSSTAT view to see how many times you have to scan the short and long tables.
If you the number of ‘table scans (long tables)’ was high then you have done
more IOs.
SQL> SELECT name, value
FROM v$sysstat
WHERE name IN ('table scans (short tables)',
'table scans (long tables)')
/
Try to reduce the number by creating proper table indexes. Note that the count
for 'Long Tables scan' must be very small.
Q: How do you use the V$SYSTEM_EVENT view?
A: We use the
V$SYSTEM_EVENT directory view to monitor and tune a Redo Log file parallel
write.
SQL> SELECT event, total_waits, time_waited
FROM v$system_event
WHERE event = 'log file parallel write'
/
The
"Waits" column indicates a possible I/O problem.
Q: How do you set the UNDO_MANAGEMENT parameter?
A: SQL> ALTER SYSTEM
SET undo_management=AUTO SCOPE=spfile
/
Q: What does the UNDO_MANAGEMENT parameter?
A: When the system is in
AUTO mode, and the transaction needs more space, Oracle automatically will
borrow more space from other undo segments that have extra space.
Q: Why and how do you distribute your tablespaces on
different disks?
A: You should
distribute the data files
across multiple disks
evenly.
Q: Describe RAID?
A: The RAID (Redundant
Array
of
Inexpensive Disks) is some type of redundancy that you can build in your system
a part from Oracle in order to provided data duplication. You can use RAID
supported by hardware or software application. It is used in the case of a disk
crash or failure. Multiple disks can be formatted in a RAID format such that if
one of them fail, when you replace the bad disk with a new disk then all its
data will be regenerated from other disks.
Q: What does the SCOPE=spfile mean in the ALTER SYSTEM SET
statement?
A:
It means apply changes on the Server Parameter File only not on the
memory. You should restart the database using SPFILE in order to activate the
changes.
Q:
How do you optimize a sort operation in the Oracle SGA memory?
A:
Query the V$SYSSTAT
view to
track the number of in-memory and to-disk sorts, as well as the total
number of rows sorted.
SQL> SELECT name, value
FROM v$sysstat
WHERE name like 'sorts%'
/
the sorts (disk) number must be very low, and the sorts (memory) number can be
as high as possible.
Q:
Describe the ‘sorts (disk)’ value in the V$SYSSTAT view.
A:
The ‘sorts (disk)’ value is a number of
times that Oracle tables were sorted on the disk using the TEMPORARY tablespace.
Q:
How do you calculate the sort ratio value in the SGA sort area?
A:
To calculate the sort ratio of the in-memory
vs. to-disk sorts.
SQL> SELECT 100*(a.value-b.value)/(a.value) AS "Sort Ratio"
FROM v$sysstat a, v$sysstat b
WHERE a.name = 'sorts (memory)'
AND b.name ='sorts (disk)'
/
Q:
What is an acceptable range for the sort ratio in the SGA sort area?
A:
The sort ratio should be greater than 95%. If
you are not using the automatic PGA memory and the number is less than 95
percent, you should greatly consider increasing the value of the SORT_AREA_SIZE
parameter.
If you are using the automatic PGA memory and the number is less than 95
percent, you should greatly consider increasing the value of the
PGA_AGGREGATE_TARGET
parameter.
Q: Describe a latch in the SGA memory.
A: A latch is a
permission that Oracle gives to one server process at a time.
Q: What does a latch protect?
A: A Latch may protect
shared memory allocation, or may also protect shared data structures in the
SGA
Q: How do you diagnose contention for latches?
A: Check latch
contention in the shared pool and redo log buffer.
1-
To check latch contentions in the shared pool, we should do the following SQL
Statement:
SQL>
SELECT name, (1-(misses/gets))*100
AS
"Ratio", sleeps
FROM
v$latch
WHERE
name in ('library cache', 'shared pool')
/
The
ratio must be above 99 percent.
2-
To check the the Redo Allocation Latch and the Redo Copy Latch wait ratios.
SQL>
SELECT h.pid, n.name, (l.misses/l.gets)*100 wait_ratio
FROM
v$latchholder h, v$latchname n, v$latch l
WHERE
h.laddr = l.addr
AND
l.latch# = n.latch#
AND
n.name in ('redo allocation', 'redo copy')
/
Notice
that if there was an output and the wait ratio was more than 1, there is a
problem. Then, you will need to increase the Redo Log Buffer size.
Q: How many types of latch request does Oracle have?
A: Oracle has two
different types of latch requests: willing to wait or immediate.
Q: What does a process do when a latch is willing to wait
for a request and does not get a latch?
A: The process waits
briefly and then goes to sleep. Then, it requests the latch again.
Q: What does a process do when a latch is not willing to
wait for a request and does not get a latch?
A: In the immediate
request, if the process cannot obtain the latch requested in the immediate
mode, it does not wait and does other jobs when it is finished, then it attempts
to obtain the latches again.
Q:
How do you tune the UNDO segments?
A:
Always set the UNDO_MANAGEMENT parameter to
AUTO. In the AUTO option, the database takes control of how to manage the
UNDO segments.
Q:
Describe the UNDO_RETENTION parameter.
A: The UNDO_RETENTION
parameter indicates the number of seconds that the database keeps the UNDO
segments.
Q:
Describe the UNDO_TABLESPACE parameter.
A:
The UNDO_TABLESPACE parameter
indicates
the UNDO tablespace.
Q:
Describe the V$UNDOSTAT
view.
A:
It is a view that contains all the undo
segments statistics. You use it to calculate an estimate of undo spaces to
meet the undo retention requirement for an specific time such as 15 minutes.
SQL> SELECT (xx*(ups*overhead) + overhead) AS "Bytes"
FROM (SELECT value AS xx
FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM((end_time-begin_time)*86400))
AS ups
FROM
v$undostat),
(SELECT value AS overhead
FROM v$parameter
WHERE name = 'db_block_size')
/
The result of this query shows how much UNDO space we need to meet the UNDO
retention
requirement.
Q: How do you get a list of UNDO segments?
A: SQL> SELECT * FROM
v$rollname
/
Q: When do you get the following undo segment error
message?
ORA-01555:
snapshot too old.
A: When the UNDO segment
is not big enough.
Q: What does the following SQL statement?
SQL> SELECT name, value
FROM v$sysstat
WHERE name in ('db block gets','consistent gets');
A:
It queries the 'db block gets' and 'consistent
gets' values. We use these two values to calculate cache buffer hit ratio:
Hit
Ratio = (db block gets + consistent gets - undo header) /
(db block gets + consistent gets)
Q:
What is a lock contention in the Oracle database?
A:
The Oracle server automatically manages object
locking, so most application developers don't need to focus on lock
management. You should avoid any lock contention. It does affect your
performance very significantly. The lock contention is a delay that Oracle is
not able to lock a record or records due to exclusive use of that object or
objects.
Q:
How do you monitor and detect a lock contention?
A:
SQL> SELECT o.owner, o.object_name,
o.object_type, l.type
FROM dba_objects o, v$lock l
WHERE o.object_id = l.id1
AND o.owner = 'ISELF'
/
Q:
How do you lock a table in the exclusive mode?
A:
SQL> LOCK TABLE iself.emp IN EXCLUSIVE
MODE
/
Q:
How do you lock a table in the shared mode?
A:
SQL> LOCK TABLE iself.emp IN SHARE MODE
/
Q:
How do you kill a session?
A:
SQL> ALTER SYSTEM
KILL SESSION
'<sid,serial#>'
/
Q:
Describe the different types of user locks.
A:
The lock type can be TX
, TM
, and UL
. If TYPE equals TX, it means TRANSACTION ENQUEUE. If
TYPE equals TM, it means DML ENQUEUE. If TYPE equals UL, it means USER SUPPLIED.
Q:
How do you optimize a SQL statement?
A:
In order to optimize a SQL statement, you
execute the EXPLAIN PLAN statement to populate a list plan of execution in
PLAN_TABLE. Then you write a SQL statement against the table to query a plan of
execution list generated by EXPLANIN PLAN.
Q:
How do you identify that a SQL statement is not optimized?
A:
By reading the list plan of execution created
by EXPLAIN PLAN.
Q:
Describe the EXPLAIN PLAN statement.
A:
The EXPLAIN PLAN statement will be used, so
that the database will list the plan of execution.
Q:
How do you create the PLAN_TABLE table?
A:
If PLAN_TABLE
does not
exist, run the utlxplan.sql script provided in the rdbms\admin folder to create
the PLAN_TABLE table.
Q:
Describe the use of the SET STATEMENT_ID clause.
A:
We use the SET STATEMENT_ID clause to identify
the plan for later review. We should have one single unique statement_id for
each specific SQL statement that we want to optimize.
Q:
Describe the following operation in PLAN_TABLE.
TABLE
ACCESS FULL
TABLE
ACCESS BY INDEX
INDEX
UNIQUE SCAN
NESTED
LOOPS
MERGE
JOIN
FILTER
SORT
AGGREGATE
A: "SORT GROUP
BY" means Oracle will perform a sort on the data obtained for the user.
"FILTER
" means that this is an operation that adds
selectivity to a TABLE ACCESS FULL operation, based on the contents of the where
clause.
"NESTED LOOPS
" indicates that the join statement is
occurring.
"MERGE JOIN
" indicates that the join statement is
occurring.
"SORT JOIN" indicates that the join statement is sorting.
"TABLE ACCESS FULL" means that Oracle will look at every row in
the table (slowest way).
"TABLE ACCESS BY INDEX
" means that Oracle will use the ROWID method to
find a row in the table. It is very fast.
"INDEX UNIQUE SCAN" means Oracle will use the primary or unique
key. This is the most efficient way to search an index.
"SORT AGGREGATE
" means Oracle will perform a sort on the data
obtained for the user.
Q:
Describe the STATSPACK utility.
A:
STATSPACK was created in response to a need
for more relevant and more extensive statistical reporting beyond what was
available via UTLBSTAT/UTLESTAT reports. These statistics can be stored
permanently in the database so that historical data is available for comparison
and diagnosis.
Q:
How do you install the STATSPACK utility?
A:
Create the PERFSTAT
user
with its objects. Make the TOOLS tablespace as its default tablespace and the
TEMP tablespace as its temporary tablespace. Then, run the following script.
Make sure that you have at least approximately 75 Megabytes of disk space for
the installation.
Q:
Describe the SPCREATE script.
A:
From this script the PERFSTAT user and its
schema (lots of tables, views, synonyms, packages, etc) will be created.
Q:
How do you run the SPCREATE script?
A:
SQL> START
%ORACLE_HOME%\rdbms\admin\spcreate
Q:
Describe the PERFSTAT user.
A:
It is a created user to perform the STATSPACK
utility.
Q:
How do you clean the STATSPACK tables?
A:
To clean all the STATSPACK
tables,
we should run the following script.
SQL> START %ORACLE_HOME%\rdbms\admin\sptrunc
Q:
How do you produce a performance report using the STATSPACK utility?
A:
SQL> START
%ORACLE_HOME%\rdbms\admin\spreport
Q:
How do you perform a snapshot in the STATSPACK utility?
A:
To take a snapshot, we should execute the
following procedure.
SQL> EXECUTE statspack.snap
;
Q:
Why and how do you set the TIMED_STATISTICS
parameter?
A:
We set the TIMED_STATISTICS parameter to TRUE
to collect timing information in the V$
view.
SQL> ALTER SYSTEM SET TIMED_STATISTICS
=TRUE;
Q:
Describe the V$FIXED_TABLE view?
A:
You can use the V$FIXED_TABLE view to query
information about tables owned by the SYS user. Normally, they are started with
X$, X$_, V$, and V$_.
Q: What is a reasonable snap shots interval for the
STATSPACK utility?
A: A 15 minutes in length for each snap shot intervals are reasonable.
Q: What does it mean if an output be represented by
#######?
A: If there is output
that represented by #######, that indicates that its value is too large for the
STATSPACK
column.
Q: What does the Instance Workload Information section
contain in the STATSPACK report output?
A: It contains the
Database name, DB ID, Instance name, and tell us the release of our database,
hostname, and the time we started our snap, and ended it with its elapsed time.
Q: What does the Instance Cache Information section
contain in the STATSPACK report utility?
A: It contains the
Oracle memory cache information such as the buffer cache, shared pool, standard
block, and log buffer sizes.
Q: What does the Load Profile Information section contain
in the STATSPACK report utility?
A: It contains the
load activities during our snapshots such as Redo Size, Logical Reads, Block
Changes, Physical Reads, Physical Writes, etc.
Q: What does the Instance Efficiency Ratios section
contain in the STATSPACK report utility?
A: It contains the
system hit ratios. It is very important to keep our eyes on the hit ratios
information, although a database tuning never should be driven by hit ratios.
For example, in a DSS system a low cache hit ratio may be acceptable due the
amount of recycling needed due the large volume of data accessed.
**
Instance Efficiency Percentages (Target 100%)
Q: What does the Foreground and Background Wait Events
section contain in the STATSPACK report utility?
A:
The Foreground wait events section contains a
list of event associated with a session or client process waiting for a resource
such as log file sync, global cache open x, etc. On the other hand, the
Background wait events section contains a list of event associated with a client
process such as latch free, enqueue, row cache lock, etc.
**
Order by such that the idle
events will be at last. Check the first record.
Q: What does the Buffer Pool and Buffer Wait Statistics
section contain in the STATSPACK report utility?
A: The Buffer Pool statistics section can have multiple entries if multiple
buffer pools are allocated such as Default, Keep, and Recycle Pools.
In
the Buffer Wait Statistics section, we should see a breakdown of each
type of object waited for such as undo header, undo block, data block, segment
header, etc.
**
Check the ‘Wait’ column ordered by desc.
Q: What does the PGA Memory Statistics section contain in
the STATSPACK report utility?
A: This section contains
useful statistics for monitoring session memory usage on windows servers such as
maximum PGA allocated, Total PGA allocated, etc.
Q: What does the ‘Rollback Segment Stats/Storage/Summary
for DB’ section contain in the STATSPACK report utility?
A: The Rollback Segment Stats contains statistics for each segments check
the Pct Waits column and it should be almost zero. If there are not zero that
indicates contention on the segments.
**
Watch the Pct Waits column and a high value for "Pct Waits" suggests
more rollback segments may be required.
The
Rollback Segment Storage section contains the size of segments. The Undo
Segment Summary contains the description of the V$UNDOSTAT view and shows
the segment status such as unexpired (Stolen, Released, reUsed), or expired
(Stolen, Released, and reused).
**
The Optimal Size value should be larger than the Avg Active value.
Q: What does the Latch Activity section contain in the
STATSPACK report utility?
A:
This section is particularly useful for
determining latch contention on an instance.
Latch contention is indicated by a Pct Miss of greater than 1.0% or a
relatively high value in Avg Sleeps/Miss.
**
Watch the Pct Miss column.
**
"Get Requests", "Pct Get Miss" and "Avg Slps/Miss"
are statistics for willing-to-wait latch get requests
**
"NoWait Requests", "Pct NoWait Miss" are for no-wait latch
get requests
**
"Pct Misses" for both should be very close to 0.0
Q: What does the Latch Sleep Breakdown and Miss Sources
section contain in the STATSPACK report utility?
A: The Latch Sleep breakdown
section
contains a list of latches that are candidate of contentions. Check the Sleeps
column. It should be very low. Any positive number in respect to the Get
Requests columns indicates a possible contention. The Miss
Sources section provides a detailed breakdown of which latches are missing
and sleeping. Search on the latch
child name experiencing high misses or sleeps and you can often find the bug
responsible.
**
ordered by misses descending
Q: What does the Dictionary Cache and Library Cache
Statistics section contain in the STATSPACK report utility?
A: The Dictionary Cache and Library
Cache sections contain the Pct Misses column that should be very low (less
than .02). If the column value is
more you may have to increase the shared pool size.
**
"Pct Misses" should be very low (< 2% in most cases)
Q: What does the SGA Memory Summary section contain in the
STATSPACK report utility?
A: This section provides
a breakdown of how the SGA
memory
is used at the time of the report.
Q: What does the SGA Memory Detail section contain in the
STATSPACK report utility?
A: This section shows a
detailed breakdown of memory usage (such as java pool free memory, PX msg pool, Checkpoint queue, KGFF heap,
etc) by the SGA
at the
beginning and ending of the reporting period.
Q: What does the INIT.ora Parameter Summary section
contain in the STATSPACK report utility?
A: The final section shows the current init.ora
parameter settings. It displays
those that are more commonly used including some hidden.
|