Vision/Mission

 Services

 Biography

 Contact Us

 

FREE Training

 SQL

 PL/SQL

 Forms 

 Reports

 Other TOOLS

 Fundamentals

 Performance

 OEM

 Application Server

 Grid Control

 Articles

 Prepare for OCP

 

More to know...

Acknowledgement___

 Who is who

 University Directory

 Links...

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.

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.

A: The Server Parameter File is a binary file and Oracle uses it to change the most of its system parameters dynamically.

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.

A: The init.ora file is called initialized or parameter file. It is a configuration file.

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.

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.

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.

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.

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.

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.

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.

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).

A: The LGWR’s job is to write the redo user’s entries from the Redo Log Buffer.

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.

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.

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.

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.

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.

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.

A: SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

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.

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.

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.

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.

A: SQL> ALTER DATABASE BACKUP CONTROLFILE TO c:.bk;

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.

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).

A: To create the password file, you should run the ORAPWD utility from operating system.

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.

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.

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.

A: If the SGA is larger than 128MB, then a granule is 16MB.

A: If the SGA is less than 128MB, then a granule is 4MB.

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.

A: ALTER SYSTEM SET shared_pool_size=100M scope=SPFILE;

A: Use the KEEP procedure to pin the STANDARD package.
SQL> BEGIN

A: Use the UNKEEP procedure to pin the STANDARD package.
SQL> BEGIN

A: We should use the following SQL statement to calculate the overall dictionary cache hit ratio.
SQL> SELECT (SUM(gets - getmisses)) / SUM(gets)

A: The major components of the shared pool are: the library cache, the dictionary cache (row cache), and 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).

A: When you are running shared servers then the session information can be stored inside the user global area (UGA).

A: That the SHARED_POOL_RESERVED_SIZE parameter can be reserved for large objects.

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.

A: To increase or decrease the Buffer Cache size to a specific size.
SQL> ALTER SYSTEM SET db_cache_size=55M
/

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.

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.

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).

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.

A: SQL> SHOW PARAMETER sga

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

A: The buffers in the buffer cache are organized in two lists:

A: To calculate the Buffer Cache Hit Ratio from the V$SYSSTAT view.
SQL> SELECT 1- ((p.value - l.value - d.value) / s.value)

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)
/

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
/

A: To cache a table by hint in a SQL statement.
SQL> SELECT /*+ CACHE (iself.dept) */
*
FROM iself.dept
/

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.

A: To diagnose the FREELIST contention in the Buffer Cache.
SQL> SELECT s.segment_name, s.segment_type,

A: The DEFAULT pool is used the same way as the standard Buffer Cache block size.

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.

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.

A: It is a view that contains the Oracle system usages such as session logical reads, physical reads direct, etc.

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.

A: It is a view that contains the Oracle buffer pools statistic. You can calculate the Hit Ratio for multiple pools using this 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.

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.

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.

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.

A: ALTER SYSTEM SET log_buffer = 55M scope=SPFILE;

A: To check to see if there are any other sessions waiting for log buffer space to the following SQL statement.

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.

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.

A: In order to reduce the I/O contention we should at least consider the following steps:

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
/

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.

A: You should just increase the size of the REDO LOG files.

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.

A: We use the V$SYSTEM_EVENT directory view to monitor and tune a Redo Log file parallel write.

A: SQL> ALTER SYSTEM SET undo_management=AUTO SCOPE=spfile

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.

A: You should distribute the data files across multiple disks evenly.

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.

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.

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.

A: The ‘sorts (disk)’ value is a number of times that Oracle tables were sorted on the disk using the TEMPORARY tablespace.

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)'
/

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.

A: A latch is a permission that Oracle gives to one server process at a time.

A: A Latch may protect shared memory allocation, or may also protect shared data structures in the SGA.

A: Check latch contention in the shared pool and redo log buffer.

A: Oracle has two different types of latch requests: willing to wait or immediate.

A: The process waits briefly and then goes to sleep. And then, it requests the latch again.

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.

A: Always set the UNDO_MANAGEMENT parameter to AUTO. In the AUTO option, the database takes control of how to manage the UNDO segments.

A: The UNDO_RETENTION parameter indicates the number of seconds that the database keeps the UNDO segments.

A: The UNDO_TABLESPACE parameter indicates the UNDO tablespace.

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.

A: SQL> SELECT * FROM v$rollname
/

A: When the UNDO segment is not big enough.

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)

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.

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'
/

A: SQL> LOCK TABLE iself.emp IN EXCLUSIVE MODE
/

A: SQL> LOCK TABLE iself.emp IN SHARE MODE
/

A: SQL> ALTER SYSTEM KILL SESSION '<sid,serial#>'
/

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.

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.

A: By reading the list plan of execution created by EXPLAIN PLAN.

A: The EXPLAIN PLAN statement will be used, so that the database will list the plan of execution.

A: If PLAN_TABLE does not exist, run the utlxplan.sql script provided in the rdbmsfolder to create the PLAN_TABLE table.

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.

A: "SORT GROUP BY" means Oracle will perform a sort on the data obtained for the user.

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.

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.

A: From this script the PERFSTAT user and its schema (lots of tables, views, synonyms, packages, etc) will be created.

A: SQL> START %ORACLE_HOME%

A: It is a created user to perform the STATSPACK utility.

A: To clean all the STATSPACK tables, we should run the following script.
SQL> START %ORACLE_HOME%

A: SQL> START %ORACLE_HOME%

A: To take a snapshot, we should execute the following procedure.

A: We set the TIMED_STATISTICS parameter to TRUE to collect timing information in the V$ view.
SQL> ALTER SYSTEM SET TIMED_STATISTICS=TRUE;

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$_.

A: A 15 minutes in length for each snap shot intervals are reasonable.

A: If there is output that represented by #######, that indicates that its value is too large for the STATSPACK column.

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.

A: It contains the Oracle memory cache information such as the buffer cache, shared pool, standard block, and log buffer sizes.

A: It contains the load activities during our snapshots such as Redo Size, Logical Reads, Block Changes, Physical Reads, Physical Writes, etc.

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.

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.

A: The Buffer Pool statistics section can have multiple entries if multiple buffer pools are allocated such as Default, Keep, and Recycle Pools.

A: This section contains useful statistics for monitoring session memory usage on windows servers such as maximum PGA allocated, Total PGA allocated, etc.

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.

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).

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.

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.

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.

A: This section provides a breakdown of how the SGA memory is used at the time of the report.

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.

A: The final section shows the current init.ora parameter settings. It displays those that are more commonly used including some hidden.