Questions and Answers:
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 LGWR writes
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<db-name> directory.
Q: How do you backup your
database controlfiles?
A:
SQL> ALTER DATABASE BACKUP CONTROLFILE TO
c:.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\b0 orapw<sid>.pwd \par
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 oracle.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
(oracle.dept) */
*
FROM oracle.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. And 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 = 'ORACLE'
/
Q: How do you lock a table in
the exclusive mode?
A:
SQL> LOCK TABLE oracle.emp IN EXCLUSIVE MODE
/
Q: How do you lock a table in
the shared mode?
A:
SQL> LOCK TABLE oracle.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
rdbmsfolder 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%
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%
Q: How do you produce a
performance report using the STATSPACK
utility?
A:
SQL> START %ORACLE_HOME%
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.
|