Performance

The Oracle Performance Tuning Case Study consists of the numbers of the most common ways to detect an Oracle performance problem.

In your organization, you are expected at least know how to: Change memory allocation on the fly; size the Shared Pool; size the Buffer Cache; Measure the Buffer Cache Hit Ratio; Monitor and re-size the Redo Log buffer; Detect Database I/O problems; Optimize Sort Operations; Diagnose Contentions for Latches; Tune rollback or undo segments; Monitor and Detect Lock Contentions; Optimize a SQL statement; and Use Diagnostic and Tuning Tools.

In this section, you will learn about the Oracle Architectural components such as Log Writer, DB Writer, etc (Background Processes), SGA, Buffer Cache, Shared Pool, etc (Memory Layout - Memory Buffer), Datafiles, Controlfiles, etc (Physical Oracle Layout). All these components, running together play an important part in the Oracle Architecture.

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.

All components such as Log Writer (LGWR), DB Writer (DBWR), Checkpoint (CKPT), Recovery Process (RECO), Lock Process (LCKn), Archive Process (ARCH), System Monitor (SMON), and Program Monitor (PMON) are referred to as an Oracle Background processes.

All components such as Server Parameter File (SPFILE), Parameter File (PFILE the INIT.ORA file), Controlfiles, Datafiles, Password File, Archives and Online Redo Log files are referred to as an Oracle Database Components.

All these Oracle components running together allow users to read, write, and modify data in an Oracle database.

Now, the following are brief job descriptions for above components.

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.

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.

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.

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.

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.

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) 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). This is because the sort_area_retained_size may have to be held open as the pipeline to return results to the front-end, so it has to be located where the session can find it again as the session migrate from server to server. On the other hand, the sort_area_size is a complete throwaway, and by locating it in the PGA, Oracle can make best use of available memory without soaking the SGA. To avoid sessions grabbing too much memory in the SGA when running MTS/shared server, you can set the private_sga value in the resource_limit for the user. This ensures that any particularly greedy SQL that (for example) demands multiple allocations of sort_area_retained_size will crash rather than flushing and exhausting the SGA.

The LGWR�s job is to write the redo user�s entries from the Redo Log Buffer when the buffer exceeds one third of the Redo Log Buffer, every 3 seconds, or when a user executes the commit SQL statement.

The DBWR�s job is to write all the blocks that were marked as dirty block to the Oracle database on disks (datafiles) whenever the checkpoint process signals it. Notice that when the Online Redo Log files are filled the checkpoint process will signals a DBWR to write all the dirty block into the Oracle database.

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

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.

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.

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.

When you start your database, the SMON 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 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.

You can read or change this file. The file 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 use this file to increase or decrease the size of System Global Area (SGA). 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.

This file is in binary format and you cannot read this file. You should create the Server Parameter file (CREATE SPFILE FROM PFILE) and startup your database using the spfile file, if you want to change database parameters dynamically. There are some few parameters that you still need to shutdown and startup the database, if you want to make the parameter in effect. You will learn all about these parameters in the course of this book.

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

All the Oracle data information will be stored in the Oracle datafiles. A datafile is one of the physical layout components of a database. A tablespace (logical database layout) contains one or more datafiles (physical database layout). You may have one or more extents in a datafile. An extent is a collection of blocks. A block is a smallest unit in an Oracle. A tablespace is a collection of segments. Think of a segment like an object in an Oracle database. A Segment is a collection of Oracle blocks.

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 password file is an external Oracle file and to create it you should run the ORAPWD utility from operating system.

For example:

MS-DOS> ORAPWD FILE=%ORACLE_HOME\b0 orapw<sid>.pwd \par PASSWORD=mypass ENTRIES=3

The ENTRIES parameter specifying the number of user entries allowed for the password file. Now, the DBA can be connected to the database as a user with sysdba privileges as shown here:

SQL> connect sys as sysdba

Password:

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.

When an Online Redo Log File fills out, the checkpoint will force DBWR to write into the Oracle datafiles and also the archive process copies the log file to an archive destination directory. That will guarantee a database recovery to a point of failure if an Oracle database failure occurred.

Assuming that you have a user is updating a record from her SQLPLUS. The following SQL statement is her SQL transaction:

SQL> UPDATE emp SET sal = 1000 WHERE empno = 100;

Let us see what would be happen when oracle process it.

The user will type the above SQL statement and press enter key. This user either is connect to the database by dedicated server or shared server (MTS). If the user is using multi-threaded servers then her request will be given to a dispatcher and the dispatcher will give the request to shared server. If the user is using dedicated server then the dedicated server will be all hers. Now, her user process is talking to shared or dedicated server.

Now, the user�s SQL statement will be parsed and assigned an executed plan to be compiled in the Library Cache in the Shared Pool. In order the SQL statement be compiled, Oracle need to make sure its table and columns are valid and the user did not violated any security information. It goes to the Dictionary Cache known as Raw Cache to get all necessary information about the table. If there was no syntax problem and its table and columns were valid, then the SQL statement will be parsed successfully and the execution plan will be perform.

Now, there is no problem. The Server process fetches the record. If the data or record is in the Buffer Cache then an update process will be applied to it and the block will be marked as dirty block. Notice that before the user save the update, the before block images are in the UNDO segment. When the user executes commit statement or more than one third of the Redo Log buffer have filled out, then LGWR writes the user�s entries from the redo log buffer to the Online Redo Log files. Still the block may not been stored in the database.

In the case that the record is not in the buffer cache, the server process read the block containing the record from the datafile (disk) and places it into the buffer cache.

Now, the checkpoint process will be activated based on the LOG_CHECKPOINT_INTERVAL, LOG_CHECKPOINT_TIMEOUT parameters, or may be due to a log switch. This action will force DBWR or CKPT to write all dirty block in the database (datafile).

Q: What are the Oracle Architectural components?

Q: What are the Oracle Memory Components?

Q: What is the Server Parameter File?

Q: What is the Parameter File?

Q: How do you use the init.ora file?

Q: What is the System Global Area (SGA)?

Q: What is the Shared Pool in SGA?

Q: What is the Buffer Cache in SGA?

Q: What does the Buffer Cache hold in SGA?

Q: What are the differences between the Library Cache and Dictionary Cache?

Q: What is the Redo Log Buffer in SGA?

Q: Describe the Large Pool component in SGA?

Q: Describe the Multi-threaded Server process?

Q: What are PGA and UGA?

Q: Describe the log writer background process (LGWR)?

Q: How often LGWR writes user�s entries to the Online Redo Log Buffer files?

Q: Describe the Checkpoint process?

Q: How do you automatically force the Oracle to perform a checkpoint?

Q: What is the Recovery Process?

Q: What is the Lock Background Process?

Q: How does the Archive Process work?

Q: How do you configure your database to do an automatic archiving?

Q: What is the System Monitor Process?

Q: Describe the Program Monitor Process Job?

Q: What are the differences between the SPFILE and PFILE startup?

Q: What is the controlfile?

Q: How do you backup your database controlfiles?

Q: What does a controlfile contain?

Q: Describe the password file?

Q: How do you create a password file?

Q: Describe the Online Redo Log file?