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.
==> 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.
No comments:
Post a Comment