Diagnosing and Tuning Programs for Optimal Database Performance

Purpose

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. An application is provided in C and in Java.

Time to Complete

Approximately 60 minutes

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites

Analysis of the OCI Application Written in C:

 Stage 1 - Setting Up (C)
 Stage 2 - Using Session Pool (C)
 Stage 3 - Using Binds (C)
 Stage 4- Using Array DMLs and Fetches (C)
 Stage 5 - Using Statement Caching and Client Result-Set Caching (C)
 Summary

Analysis of the OCI Application Written in Java:

 Stage 1 - Setting Up (Java)
 Stage 2 - Using Connection Caching (Java)
 Stage 3 - Using Binds (Java)
 Stage 4- Batching and Fetch Counts (Java)
 Stage 5 - Using OCI Result Set Cache (Java)
 Summary

Viewing Screenshots

 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.

Overview

In this tutorial you examine two sample OCI applications, one is written in C, and the other is written in Java. By generating the ADDM and AWR reports on the sample OCI applications, you analyze performance and bottlenecks. You use a step by step approach and tune the application in 5 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 can use the make utility.

On the database side, three procedures are used:

For the application in C, the names are:

For the application in Java, the names are:

OCI/JDBC Installation and Use

If you are running a client on a separate machine from the server (such as a client/server configuration), you can install Instant Client. Instant Client allows you to run your applications without installing the standard Oracle client or having an ORACLE_HOME.

You can download the Instant Client files from: http://www.oracle.com/technology/software/tech/oci/instantclient/index.html

(Note: If you are running this tutorial from the database on the server, you do not need the above Instant Client files, as the required files are on the server by default.)

Command line parameters (used in the various stages in this tutorial):

  • -t is the thread: the number of threads, the default is 20 threads
  • -i is the iteration: the number of units of workload in each thread, the default iteration is 120
  • -u is the number of updates in a unit of workload, the default number of updates is 80
  • -w is the think time in seconds between units of workload, the default is 0 second

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 the reports, you will use the reports.sql script provided with this tutorial. For each of the 5 stages that you examine, a C file and a Java file are provided.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install Oracle Database 11g

2.

Download and unzip the ociobe.zip file into your working directory (i.e.wkdir)

Back to Topic List

Using the C OCI Application

Stage 1: Setting Up (C)

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 the /wkdir directory. Examine the contents for the stage1.c file. You can use a text editor to view the contents.

Execute the following command in your terminal window:

sqlplus sys/oracle as sysdba

 

2.

From your SQL*Plus window, 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:

  • stage1_results_addm.txt - the ADDM report.
  • stage1_results_awr.html - the AWR report.

To view the latest files generated, execute the following statement:

host ls -lt|head

Generated sample results are provided to you for each stage. The names of the sample results files are located in the /wkdir directory:

  • stage1_addm.txt - the ADDM report.
  • stage1_awr.html - the AWR report.

Note: Your results may differ from the sample results provided due to environment differences. For consistency in this tutorial, the generated sample results are examined, however, you can examine the reports you generate.

 

4.

Examine the contents in the /wkdir/stage1_addm.txt report.

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 (Note: by pooling sessions, you can reduce the number of session connects and disconnects.)

Finding 3: Soft Parse (Note: Statement caching can reduce soft parse time.)

Finding 4: Hard Parse Due to Literal Usage (Note: You can reduce the number of literals by using binds.)

Finding 5: Top SQL by DB Time

Finding 6: Commits and Rollbacks


5.

Examine the contents in the stage1_awr.htm report.

Open your browser. From the File menu item, select Open File option and locate the /wkdir/stage1_awr.html file. Click Open. Review the report .

Scroll through the report and examine the contents.

 

Back to Topic List

Stage 2: Using Session Pool (C)

In this section, you view the difference between the sample program in stage 1 verses stage 2. In stage2, session connection pool is enabled.

1.

Examine the contents for the stage2.c file. You can use tkdiff to compare this file to the stage1.c file.

Open another terminal window, switch to the /wkdir directory. Execute the following command:

                               
tkdiff stage1.c stage2.c &
                            

Note the differences in the file. In stage2.c connection pooling is enabled.

Exit the tkdiff utility.

 

2.

From your SQL*Plus window, run the reports.sql script with the following command. 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:

  • stage2_results_addm.txt - the ADDM report.
  • stage2_results_awr.html - the AWR report.

To view the latest files generated, execute the following statement:

host ls -lt|head

Generated sample results are provided to you for each stage.The names of the sample results files are located in the /wkdir directory:

  • stage2_addm.txt - the ADDM report.
  • stage2_awr.html - the AWR report.

Note: Your results may differ from the sample results provided due to environment differences. For consistency in this tutorial, the generated sample results are examined, however, you can examine the reports you generate.

 

4.

Examine the contents in the /wkdir/stage2_addm.txt report.

Note the summary of findings.

Scroll through the report and examine the details for each finding and the recommendations.

Finding 1: Undersized Shared Pool

Finding 2: CPU Usage

Finding 3: Soft Parse

Notice that hard parses due to an inadequately sized shared pool consumed significant database time.


5.

Examine the contents in the stage2_awr.htm report.

Open your browser. From the File menu item, select Open File option and locate the /wkdir/stage2_awr.html file. Click Open. Review the report.

Scroll through the report and examine the contents.

 

Back to Topic List

Stage 3: Using Binds (C)

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 tkdiff to compare this file to the stage2.c file.

From your terminal window, switch to the /wkdir directory. Execute the following command:

                               
tkdiff stage2.c stage3.c &
                            

Note the differences in the file. In stage3.c binds are enabled.

Exit the tkdiff utility.

 

2.

From your SQL*Plus window, 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:

  • stage3_results_addm.txt - the ADDM report.
  • stage3_results_awr.html - the AWR report.

To view the latest files generated, execute the following statement:

host ls -lt|head

Generated sample results are provided to you for each stage. The names of the sample results files are located in the /wkdir directory:

  • stage3_addm.txt - the ADDM report.
  • stage3_awr.html - the AWR report.

Note: Your results may differ from the sample results provided due to environment differences. For consistency in this tutorial, the generated sample results are examined, however, you can examine the reports you generate.

 

4.

Examine the contents in the /wkdir/stage3_addm.txt report.

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: Buffer Busy

Finding 6: Unusual "Concurrency" Wait Event


5.

Examine the contents in the stage3_awr.htm report.

Open your browser. From the File menu item, select Open File option and locate the /wkdir/stage3_awr.html file. Click Open. Review the report.

Scroll through the report and examine the contents.

 

Back to Topic List

Stage 4: Using Array DML and Fetches (C)

In this section, you examine array fetching. You reduce the number of transactions by setting the array size to a value larger than 1. Too many transactions can decrease performance. Array DML enables you to group the DML into a single transaction.

1.

Examine the contents for the stage4.c file. You can use tkdiff to compare this file to the stage3.c file.

From your terminal window, switch to the /wkdir directory. Execute the following command:

                               
tkdiff stage3.c stage4.c &
                            

Note the differences in the file. In stage 4, array fetches are used.

Exit the tkdiff utility.

 

2.

From your SQL*Plus window, run the reports.sql script. Pass to it the stage4.c file. Add the parameter to iterate 1000 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:

  • stage4_results_addm.txt - the ADDM report.
  • stage4_results_awr.html - the AWR report.

To view the latest files generated, execute the following statement:

host ls -lt|head

Generated sample results are provided to you for each stage. The names of the sample results files are located in the /wkdir directory:

  • stage4_addm.txt - the ADDM report.
  • stage4_awr.html - the AWR report.

Note: Your results may differ from the sample results provided due to environment differences. For consistency in this tutorial, the generated sample results are examined, however, you can examine the reports you generate.

 

4.

Examine the contents in the /wkdir/stage4_addm.txt report.

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 (Note: you may want to partition the table to enable concurrent DMLs.)


5.

Examine the contents in the stage4_awr.htm report.

Open your browser. From the File menu item, select Open File option and locate the /wkdir/stage4_awr.html file. Click Open. Review the report.

Scroll through the report and examine the contents.

 

Back to Topic List

Stage 5: Using Statement Caching and Client Result-Set Caching (C)

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 tkdiff to compare this file to the stage4.c file.

From your terminal window, switch to the /wkdir directory. Execute the following command:

                               
tkdiff stage4.c stage5.c &
                            

Note the differences in the file. In stage 5, result set caching is used.

Exit the tkdiff utility.

 

2.

Enable result-set caching on the server. The resultcache.ora file has been created for you that contains the following parameter

                               
CLIENT_RESULT_CACHE_SIZE=10M
                            

From your SQL*Plus window, shutdown the database by issuing this command:

                               
                                 
shutdown immediate
                              
                            
Then startup the database by issuing the following command:
                               
                                 
startup pfile=app/oracle/product/11.1.0/db_1/dbs/resultcache.ora
                              
                            

 

3.

From your SQL*Plus window, run the reports.sql script. Pass to it the stage1 file. Add the parameter to iterate 2000 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:

  • stage5_results_addm.txt - the ADDM report.
  • stage5_results_awr.html - the AWR report.

To view the latest files generated, execute the following statement:

host ls -lt|head

Generated sample results are provided to you for each stage. The names of the sample results files are located in the /wkdir directory:

  • stage5_addm.txt - the ADDM report.
  • stage5_awr.html - the AWR report.

Note: Your results may differ from the sample results provided due to environment differences. For consistency in this tutorial, the generated sample results are examined, however, you can examine the reports you generate.

 

5.

Examine the contents in the /wkdir/stage5_addm.txt report.

Note the summary of findings.

Scroll through the report and examine the details for each finding and the recommendations. At this stage, the application is well tuned and the remaining issues identified may be due to server and OS processing.

Finding 1: CPU usage (Note that CPU usage is very high due to running the client and server on the same box)

Finding 2: Top SQL by DB Time

Finding 3: Buffer Busy (due to hitting the same row at the same time)

Finding 4: Buffer Busy

Finding 5: Commits and Rollbacks

Finding 6: Buffer Cache Latches


6.

Examine the contents in the stage5_awr.htm report.

Open your browser. From the File menu item, select Open File option and locate the /wkdir/stage5_awr.html file. Click Open. Review the report.

Scroll through the report and examine the contents.

 

Back to Topic List

Summary (for the C OCI application)

In this section of the tutorial, you learned how to:

 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 Caching

Back to Topic List

Using the Java OCI Application

Stage 1: Setting Up (Java)

In this section, you examine the base performance of the Stage1 application with the ADDM and AWR reports.

1.

Open a terminal window.

Change to the /wkdir directory. Examine the contents for the stage1.java file. You can use a text editor to view the contents.

Execute the following command in your terminal window:

sqlplus sys/oracle as sysdba

 

2.

From your SQL*Plus window, run the reports.sql script. Pass to it the Stage1 file.

                               
@reports.sql "ant Stage1"
                            

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:

  • stage1_results_addm.txt - the ADDM report.
  • stage1_results_awr.html - the AWR report.

To view the latest files generated, execute the following statement:

host ls -lt|head

Generated sample results are provided to you for each stage. The names of the sample results files are located in the /wkdir directory:

  • Stage1_java_addm.txt - the ADDM report.
  • Stage1_java_awr.html - the AWR report.

Note: Your results may differ from the sample results provided due to environment differences. For consistency in this tutorial, the generated sample results are examined, however, you can examine the reports you generate.

 

4.

Examine the contents in the /wkdir/stage1_java_addm.txt report.

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: Hard Parse Due to Literal Usage

Finding 4: "Scheduler" Wait Class

Finding 5: Soft Parse

Finding 6: Session Connect and Disconnect

Finding 7: I/O Throughput


5.

Examine the contents in the stage1_java_awr.htm report.

Open your browser. From the File menu item, select Open File option and locate the /wkdir/stage1_java_awr.html file. Click Open. Review the report.

Scroll through the report and examine the contents.

 

Back to Topic List

Stage 2: Using Connection Caching (Java)

In this section, you view the differences between the sample program in stage 1 verses stage 2. In stage 2, we enable implicit connection cache.

1.

Examine the contents for the Stage2.java file. You can use tkdiff to compare this file to the Stage1.java file.

From your terminal window, switch to the /wkdir directory. Execute the following command:

                               
tkdiff Stage1.java Stage2.java &
                            

Note the differences in the files.

Exit the tkdiff utility.

 

2.

From your SQL*Plus window, run the reports.sql script. Pass to it the Stage2 file.

                               
@reports.sql "ant Stage2"
                            

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:

  • stage2_results_addm.txt - the ADDM report.
  • stage2_results_awr.html - the AWR report.

To view the latest files generated, execute the following statement:

host ls -lt|head

Generated sample results are provided to you for each stage. The names of the sample results files are located in the /wkdir directory:

  • stage2_java_addm.txt - the ADDM report.
  • stage2_java_awr.html - the AWR report.

Note: Your results may differ from the sample results provided due to environment differences. For consistency in this tutorial, the generated sample results are examined, however, you can examine the reports you generate.

 

4.

Examine the contents in the /wkdir/stage2_java_addm.txt report.

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: Hard Parse Due to Literal Usage

Finding 4: Soft Parse


5.

Examine the contents in the stage2_java_awr.htm report.

Open your browser. From the File menu item, select Open File option and locate the /wkdir/stage2_java_awr.html file. Click Open. Review the report.

Scroll through the report and examine the contents.

 

Back to Topic List

Stage 3: Using Binds (Java)

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.java file. You can use tkdiff to compare this file to the Stage2.java file.

From your terminal window, switch to the /wkdir directory. Execute the following command:

                               
tkdiff Stage2.java Stage3.java &
                            

Note the differences in the files.

Exit the tkdiff utility.

 

2.

From your SQL*Plus window, run the reports.sql script. Pass to it the Stage3 file.

                               
@reports.sql "ant Stage3"
                            

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:

  • stage3_results_addm.txt - the ADDM report.
  • stage3_results_awr.html - the AWR report.

To view the latest files generated, execute the following statement:

host ls -lt|head

Generated sample results are provided to you for each stage. The names of the sample results files are located in the /wkdir directory:

  • Stage3_java_addm.txt - the ADDM report.
  • Stage3_java_awr.html - the AWR report.

Note: Your results may differ from the sample results provided due to environment differences. For consistency in this tutorial, the generated sample results are examined, however, you can examine the reports you generate.

 

4.

Examine the contents in the /wkdir/stage3_java_addm.txt report.

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 Statements

Finding 4: Shared Pool Latches


5.

Examine the contents in the stage3_java_awr.html report.

Open your browser. From the File menu item, select Open File option and locate the /wkdir/stage3_java_awr.html file. Click Open. Review the report.

Scroll through the report and examine the contents.

 

Back to Topic List

Stage 4: Batching and Fetch Counts (Java)

In this section, you examine array fetching. You reduce the number of transactions by setting the array size to a value larger than 1. Too many transactions can decrease performance. Array DML enables you to group the DML into a single transaction.

1.

Examine the contents for the Stage4.java file. You can use tkdiff to compare this file to the Stage3.java file.

From your terminal window, switch to the /wkdir directory. Execute the following command:

                               
tkdiff Stage3.java Stage4.java &
                            

Note the differences in the files.

Exit the tkdiff utility.

 

2.

From your SQL*Plus window, run the reports.sql script. Pass to it the Stage4 file.

                               
@reports.sql "ant Stage4"
                            

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:

  • stage4_results_addm.txt - the ADDM report.
  • stage4_results_awr.html - the AWR report.

To view the latest files generated, execute the following statement:

host ls -lt|head

Generated sample results are provided to you for each stage. The names of the sample results files are located in the /wkdir directory:

  • Stage4_java_addm.txt - the ADDM report.
  • Stage4_java_awr.html - the AWR report.

Note: Your results may differ from the sample results provided due to environment differences. For consistency in this tutorial, the generated sample results are examined, however, you can examine the reports you generate.

 

4.

Examine the contents in the /wkdir/stage4_java_addm.txt report.

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: Unusual "Other" Wait Event

Finding 5: Shared Pool Latches

Finding 6: Buffer Busy


5.

Examine the contents in the Stage4_java_awr.htm report.

Open your browser. From the File menu item, select Open File option and locate the /wkdir/stage4_java_awr.html file. Click Open. Review the report.

Scroll through the report and examine the contents.

 

Back to Topic List

Stage 5: Using OCI Result Set Cache (Java)

In this section, you enabling result-set caching and then examine the results.

1.

Examine the contents for the Stage5.java file. You can use tkdiff to compare this file to the Stage4.java file.

From your terminal window, switch to the /wkdir directory. Execute the following command:

                               
tkdiff Stage4.java Stage5.java &
                            

Note the differences in the files.

Exit the tkdiff utility.

 

2.

From your SQL*Plus window, run the reports.sql script. Pass to it the Stage5 file.

                               
@reports.sql "ant Stage5"
                            

As the report runs, you are shown the percentage completed.

 

3.

When prompted for the report name, enter:

                               
stage5_results
                            

This generates two reports which are saved in the current directory:

  • stage5_results_addm.txt - the ADDM report.
  • stage5_results_awr.html - the AWR report.

To view the latest files generated, execute the following statement:

host ls -lt|head

Generated sample results are provided to you for each stage. The names of the sample results files are located in the /wkdir directory:

  • Stage5_java_addm.txt - the ADDM report.
  • Stage5_java_awr.html - the AWR report.

Note: Your results may differ from the sample results provided due to environment differences. For consistency in this tutorial, the generated sample results are examined, however, you can examine the reports you generate.

 

4.

Examine the contents in the /wkdir/stage5_java_addm.txt report.

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: Unusual "Other" Wait Event

Finding 6: Unusual "Application" Wait Event

Finding 7: Shared Pool Latches


5.

Examine the contents in the Stage5_java_awr.htm report.

Open your browser. From the File menu item, select Open File option and locate the /wkdir/stage5_java_awr.html file. Click Open. Review the report.

Scroll through the report and examine the contents.

 

Back to Topic List

Summary (for the Java OCI application)

In this tutorial, you learned how to:

 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 Caching

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document