This tutorial shows you how to create and use ADDM & AWR reports to analyze the performance and bottleneck of an OCI sample application. As the reports are run, you analyze and refine the sample in 5 stages. The end result is an efficient OCI application.
Approximately 30 minutes
This tutorial covers the following topics:
|
Overview |
|
Prerequisites |
|
Stage 1 - Setting Up |
|
Stage 2 - Using Session Pool |
|
Stage 3 - Using Binds |
|
Stage 4- Using Array DMLs and Fetches |
|
Stage 5 - Using Statement Caching |
|
Summary |
Place the cursor over this icon to load and view all the screenshots for this tutorial.
(Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.
In this OBE you examine a sample OCI application. By generating the ADDM and AWR reports on the sample OCI application, you anaylyze performance and bottlenecks. You use a step by step approach and tune the application in 5 stages. The source code files for stages are named as stage1.c stage2.c stage3.c stage4.c and stage5.c. A common library file named helper.c is used for all of the stages.
The Sample Application
The sample application uses random numbers to simulate web inputs to update and query the EMPLOYEES table in the HR Sample Schema. The main functions are:
To compile and link all five stages of the source code, you use the make utitlity.
On the database side, three procedures are used:
OCI Installation and Use
The Easy Installation of OCI is done by placing three libraries on the operating system's shared library path. For example, on Unix this is done by placing the directory containing libociei.so, libclntsh.so, and libnnz.so on the LD_LIBRARY_PATH variable. If the operating system can load libociei.so, then OCI assumes Easy Installation environment and a full ORACLE_HOME based environment is not needed.
For JDBC OCI driver, you also need the JDBC OCI shared library, for example libocijdbc.so on Unix. Libraries for any special features such as specialized network protocols or security options, if used, are also needed.
Command line parameters:
The AWR and ADDM Reports
Automatic Workload Repository (AWR) is a built-in repository in every Oracle Database. At regular intervals, the Oracle Database makes a snapshot of all its vital statistics and workload information and stores them here.
The Automatic Database Diagnostic Monitor (ADDM) enables the Oracle Database to diagnose its own performance and determine how identified problems could be resolved. It runs automatically after each AWR statistics capture, making the performance diagnostic data readily available.
To run a the reports, you can use the reports.sql script provided with this OBE. For each of the 5 stages that you examine, a c file is provided. For example, to run the first stage example for this OBE, you would execute:
@reports.sql "./stage1 -i 500"
Before you perform this tutorial, you should:
| 1. |
Install Oracle Database 11g |
|
| 2. |
Download and Install JDK 6.0 from Sun |
|
| 3. |
Download and unzip the ociobe..zip file into your working directory (i.e.wkdir) |
|
In this section, you get the session without session pool and you examine the impact of using literals in SQL statements. In this stage, connection pooling is not used. Every session gets a new connection.
| 1. |
Open a terminal window. Change to your wkdir directory and start SQL*Plus. Logon as sysdba:
Examine the contents for the stage1.c file. You can use a text editor to view the contents.
Note: You may need to change the permissions to access the stagen files. To do so, host out of SQL*Plus and execute the following at the command prompt: host chmod 777 stage1 exit
|
|
| 2. | Run the reports.sql script. Pass to it the stage1 file. Add the parameter to iterate 500 times to increase the workload and generate findings. @reports.sql "./stage1 -i 500"
As the report runs, you are shown the percentage completed.
|
|
| 3. |
When prompted for the report name, enter: stage1_results
This generates two reports which are saved in the current directory:
To view the latest files generated, execute the following statement: host ls -lt|head
|
|
| 4. | Examine the contents in the stage1_results_addm.txt report. (You can either open the file in a text editor, or you can scroll up in your SQL*Plus session.) Note the summary of findings.
Scroll through the report and examine the details for each finding and the recommendations. Finding 1: CPU usage:
Finding 2: Session Connect and Disconnect
Finding 3: Shared Pool Latches
Finding 4: Soft Parse
Finding 5: Top SQL by DB Time
Finding 6: Commits and Rollbacks
Finding 7: Buffer Busy
|
|
| 5. |
Examine the contents in the stage1_results_awm.htm report. Open your browser.
From the File menu item, select Open file option.
Locate the stage1_results_awm.html file. Click Open.
Review the report .
Scroll through the report and examine the contents.
|
|
In this section, you view the difference between the sample program in stage 1 verses stage 2
| 1. |
Examine the contents for the stage2.c file. You can use a text editor to view the contents.
Note: You may need to change the permissions to access the stagen files. To do so, host out of SQL*Plus and execute the following at the command prompt: host chmod 777 stage2 exit
|
|
| 2. | Run the reports.sql script. Pass to it the stage2 file. Add the parameter to iterate 500 times to increase the workload and generate findings. @reports.sql "./stage2 -i 500"
As the report runs, you are shown the percentage completed.
|
|
| 3. |
When prompted for the report name, enter: stage2_results
This generates two reports which are saved in the current directory:
To view the latest files generated, execute the following statement: host ls -lt|head
|
|
| 4. |
Examine the contents in the stage2_results_addm.txt report. (You can either open the file in a text editor, or you can scroll up in your SQL*Plus session.) Note the summary of findings.
Scroll through the report and examine the details for each finding and the recommendations. Finding 1: CPU usage:
Finding 2: Hard Parse Due to Literal Usage Notice that hard parses due to an inadequately sized shared pool consumed significant database time.
Finding 3: Top SQL by DB Tim
Finding 4: Soft Parse
|
|
| 5. |
Examine the contents in the stage1_results_awm.htm report. Open your browser.
From the File menu item, select Open file option.
Locate the stage2_results_awm.html file. Click Open.
Review the report .
Scroll through the report and examine the contents.
|
|
In this section, you enable binds. Note that this not only improves the performance but also makes the program secure by avoiding sql injections.
| 1. |
Examine the contents for the stage3.c file. You can use a text editor to view the contents.
Note: You may need to change the permissions to access the stagen files. To do so, host out of SQL*Plus and execute the following at the command prompt: host chmod 777 stage3 exit
|
|
| 2. | Run the reports.sql script. Pass to it the stage3 file. Add the parameter to iterate 500 times to increase the workload and generate findings. @reports.sql "./stage3 -i 500"
As the report runs, you are shown the percentage completed.
|
|
| 3. |
When prompted for the report name, enter: stage3_results
This generates two reports which are saved in the current directory:
To view the latest files generated, execute the following statement: host ls -lt|head
|
|
| 4. |
Examine the contents in the stage3_results_addm.txt report. (You can either open the file in a text editor, or you can scroll up in your SQL*Plus session.) Note the summary of findings.
Scroll through the report and examine the details for each finding and the recommendations. Finding 1: CPU usage:
Finding 2: Commits and Rollbacks
Finding 3: Top SQL by DB Time
Finding 4: Buffer Busy
Finding 5: Shared Pool Latches
|
|
| 5. |
Examine the contents in the stage3_results_awm.htm report. Open your browser.
From the File menu item, select Open file option.
Locate the stage3_results_awm.html file. Click Open.
Review the report .
Scroll through the report and examine the contents.
|
|
In this section, you examine array fetching. You reduce the number of transacations by setting the array size to a value larger than 1. Too many transactions can decrease performance. Arry DML enables you to group the DML into a single transacation.
| 1. |
Examine the contents for the stage4.c file. You can use a text editor to view the contents.
Note: You may need to change the permissions to access the stagen files. To do so, host out of SQL*Plus and execute the following at the command prompt: host chmod 777 stage4 exit
|
|
| 2. | Run the reports.sql script. Pass to it the stage1 file. Add the parameter to iterate 500 times to increase the workload and generate findings. @reports.sql "./stage4 -i 1000"
As the report runs, you are shown the percentage completed.
|
|
| 3. |
When prompted for the report name, enter: stage4_results
This generates two reports which are saved in the current directory:
To view the latest files generated, execute the following statement: host ls -lt|head
|
|
| 4. |
Examine the contents in the stage4_results_addm.txt report. (You can either open the file in a text editor, or you can scroll up in your SQL*Plus session.) Note the summary of findings.
Scroll through the report and examine the details for each finding and the recommendations. Finding 1: CPU usage:
Finding 2: Top SQL and DB Time
Finding 3: Commits and Rollbacks
Finding 4: Buffer Busy
Finding 5: Buffer Busy
Finding 6: High Watermark Waits
|
|
| 5. |
Examine the contents in the stage4_results_awm.htm report. Open your browser.
From the File menu item, select Open file option.
Locate the stage4_results_awm.html file. Click Open.
Review the report .
Scroll through the report and examine the contents.
|
|
In this section, you enabling result-set caching and then examine the results.
| 1. |
Examine the contents for the stage5.c file. You can use a text editor to view the contents.
Note: You may need to change the permissions to access the stagen files. To do so, host out of SQL*Plus and execute the following at the command prompt: host chmod 777 stage5 exit
|
|
| 2. | Enable result-set caching on the serve by executing the following command in SQL*Plus: alter session set result_cache_mode = 'FORCE';
|
|
| 3. | Run the reports.sql script. Pass to it the stage1 file. Add the parameter to iterate 500 times to increase the workload and generate findings. @reports.sql "./stage5 -i 2000"
As the report runs, you are shown the percentage completed.
|
|
| 4. |
When prompted for the report name, enter: stage5_results
This generates two reports which are saved in the current directory:
To view the latest files generated, execute the following statement: host ls -lt|head
|
|
| 5. |
Examine the contents in the stage5_results_addm.txt report. (You can either open the file in a text editor, or you can scroll up in your SQL*Plus session.) Note the summary of findings.
Scroll through the report and examine the details for each finding and the recommendations. Finding 1: CPU usage:
Finding 2: Top SQL by DB Time
Finding 3: Commits and Rollbacks
Finding 4: Buffer Busy
Finding 5: Buffer Busy
Finding 6: High Watermark Waits
|
|
| 6. |
Examine the contents in the stage5_results_awm.htm report. Open your browser.
From the File menu item, select Open file option.
Locate the stage5_results_awm.html file. Click Open.
Review the report .
Scroll through the report and examine the contents.
|
|
In this tutorial, you learned about the following:
|
Set Up and Review of No Optimization |
|
Optimize by Enabling Connection Pool |
|
Optimize by Enabling Binds |
|
Optimize by Using Array Fetches |
|
Optimize by Enabling Result-Set Cachine |
Place the cursor over this icon to hide all screenshots.