Develop C and C++ Applications with Oracle Database 11g Using OCI

Purpose

In this tutorial, you analyze and execute C programs in several stages to learn about the features and functionality of OCI.

You learn how to create and use reports (ADDM reports - Automatic Database Diagnostic Monitor and AWR reports - Automatic Workload Repository) to analyze the performance and bottleneck of a sample application. As the reports are run, you analyze and refine the sample in several stages. The end result is an efficient application.

Time to Complete

Approximately 60 minutes.

Prerequisites

Before you perform this tutorial, you should:

Overview

In this tutorial you use a step by step approach to run and tune the application in several stages. Starting with non-optimized code, each stage adds up to this non-optimized stage showing the performance improvement by using features like statement caching, client side result set caching, etc. A few stages show specific features like LOB handling, and fetching through a ref-cursor.

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 important statistics and workload information and stores in AWR.

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 can use the reports.sql script provided with this tutorial. For each of the stages that you want to examine, the executable generated from corresponding C file is provided to the script (reports.sql) with a few additional parameters.

Developing C Application with the OCI Drivers

OCI Overview

For OCI analysis, the C source code files are name as stage1.c stage2.c stage3.c stage4.c stage5.c, stage6.c, and stage7.c with a common library file named helper.c that is used for all of the stages shown. These files are stored in the /home/oci/ocilab location.

In this section, you examine 7 stages of an OCI application. Stages 2, 3, 4, 5 illustrate performance related OCI features, while stage6 and stage7 illustrate PLSQL and (secure) Lob support in OCI. By generating the ADDM and AWR reports on the sample applications, you can analyze performance and bottlenecks. These reports are done for stages 1,2,3,4, and 5.

Each stage builds on the code changes done in previous stage. To see the code changes, you use the tkdiff utility to view a stage compared with the previous stage.

The Sample Application

The sample application uses the ocihol/welcome schema. It generates random numbers to simulate web inputs to update and query the MYEMP table.

The following objects are defined within the ocihol schema:

The MYEMP table is defined as:

CREATE TABLE myemp (
 empno number(4) primary key,
 ename varchar2(10),
 job varchar2(9),
 mgr number(4),
 hiredate date,
 sal number(7,2),
 comm number(7,2),
 deptno number(2))
; 

CREATE TABLE regions (
 region_id NUMBER CONSTRAINT region_id_nn NOT NULL,
 region_name varchar2(25)) ; 
CREATE OR REPLACE VIEW empview as
 select * from myemp
;

To demonstrate the LOB features, the following table is defined:

CREATE TABLE lob_table (
 lobid number primary key,
 lobcol clob) LOB(lobcol) 
  STORE AS SECUREFILE (TABLESPACE SYSAUX COMPRESS low CACHE)
;

The following three procedures are used in the OCI application:

• update_salary() is used to perform DML
• query_salary() is used for single row queries
• multirow_fetch() is used for fetches multiple rows of the result set

The main C functions are:

• For multithreaded demos: main()=>thread_function()=>do_workload()
• Random Input to simulate input: random_input()

To compile and link all of the stages of the source code, you can use the make utility.

Stage 1: Setting Up

In this section, no optimizations are performed. The main thread spawns the requested number of threads with a thread function (thread_function()) as an initial routine that each thread should be started with. The 'main' thread, spawns new threads.

Each thread does the following:

• Allocates its own run time context
• Creates its own connection using the its run time context
• Calls the workload routine (do_workload()) a mentioned number of times (defined by iterations). Each workload does the following:
  • query_salary (): fetches a single row
  • multirow_fetch(): each query execution has multiple rows in a result set but this fetches one row at a time.
    Note that the OCI prefetching feature is enabled with a default value as 1. Hence for every user row fetched from the server, another row is fetched and stored internally on client. On next OCIStmtFetch(), this cached row is returned thereby saving roundtrip to database.
    This method internally uses multirow_fetch_from_emp( ) to do the defines and fetch of the rows. The separation is to make it easier to see the difference in code changes when you compare stage5 and stage6 (PLSQL ref cursor)
  • update_salary(): updates the salary field in the loop
• For each of the above database operations, the total time taken is calculated by noting down the time counter before and after of each function call. The routine joins the threads and calculates and prints the performance statistics for each query and total time taken by the application.
  

chmod 777 stage*
exit 

cd $HOME/oci/ocilab
gmake stage1 

cd $HOME/oci/ocilab/sql 

@reports.sql "../stage1 -i 10" 

stage1_oci 

Stage 2: Using Array Fetch and Array DML

In this section, the C code is optimized for array fetch and array DML. The multi-row fetch is performed using an array, which can hold an ARRAY_SIZE number of rows for each fetch.

• multirow_fetch(): fetches the ARRAY_SIZE (this number is defined in this function itself) number of rows in a loop until fetch returns OCI_NO_DATA. In the program the ARRAY_SIZE is defined to 3, meaning that the program fetches 3 rows at a time. For the first loop it would fetch first 3 rows, for the second loop - the next 3 rows, so on until all the rows are fetched. Note that you have to use the OCI_ATTR_ROWS_FETCHED attribute to check how many rows were actually received in that fetch call. Otherwise, you will access define memory that is not set to valid values from that fetch call.
• update_salary(): Here instead of preparing and executing the statement ‘update_num’ times, we instead prepare and execute the statement once and pass array of bind values. And change the number of iterations in execute call from 1 to update_num.

Note: The example uses regular forward scrolling cursor. OCI also supports readonly scrollable cursor that allows fetches in any direction (backward, forward) with random access. The result set is based on snapshot at query execution time. You have to set a special mode in OCIStmtExecute() to mark the query result-set as scrollable. Then use OCIStmtFetch2() to scroll the results. See OCI documentation for details.

tkdiff stage1.c stage2.c 

cd $HOME/oci/ocilab
gmake stage2

@reports.sql "../stage2 -i 500"

stage2_oci

Stage 3: OCI Client-Side Session Pooling

This stage is same as stage2, but with OCI session pooling enabled. This is achieved by using the following configurations options set dynamically using the OCISessionPooling APIs in new method create_session_pool().

In the thread_function(), when each thread get a session per iteration, it comes from the pool instead of creating and destroying sessions (and hence connections) per iteration per thread.

• min=thread_num: Minimum number of physical sessions (and hence connections) to be created at the time of session pool creation
• max=thread_num + 1: Maximum number of sessions that are allowed in a pool
• incr=1: Number of sessions to be increased

Notes:

• You can use remote DB (over TCP) so that user are able to see the clear difference in total time taken during the physical connection creation
• You can vary above parameters and show the variation in total time taken

tkdiff stage2.c stage3.c 

chmod 777 stage*
exit 

cd $HOME/oci/ocilab
gmake stage3

@reports.sql "../stage3 -i 1000

stage3_oci

Stage 4: OCI with Client-Side Statement Caching Enabled

This stage is same as stage3, but with client-side statement caching enabled. This is achieved by setting statement cache size on the OCI Session Pool and indicating using this feature in the OCISessionPoolCreate() call.

When the call to OCIStmtPrepare2() is done, it checks to see if that SQL text exists in the client cache. There is one cache per client session. If no match is found, it goes to server to make a (soft) parse call and caches this statement. The next call to OCIStmtPrepare2() on this same session (by the same thread in our application) will re-use this already parsed statement. This saves soft parses at the server.

tkdiff stage3.c stage4.c

chmod 777 stage*
exit 

cd $HOME/oci/ocilab
gmake stage4

@reports.sql "../stage4 -i 1000"

stage4_oci

Stage 5: OCI Client Result-Set Caching Enabled

This stage is same as stage4, but with client result set caching enabled. This is achieved by:

• Adding SQL query hints to the select queries. Only the SELECT statement in the multirow_fetch( ) function is modified in this stage. You can use the ‘myemp’ table but the example shown uses a view called ‘empview’ instead.
• Using result_cache table annotations – This is done in the createtables.sql file on the regions table. The same application (query_salary()) has its results cached automatically on the client. This client cache is across all sessions in the process and is internally kept consistent with changes on server. See the OCI documentation for more details.

Notes:

1. The client result cache needs to be enabled on the server using the initialization parameters shown below:
   • CLIENT_RESULT_CACHE_SIZE
   • CLIENT_RESULT_CACHE_LAG
   • Set COMPATIBLE to 11.2.0.0.0 (to get caching on views) otherwise, setting compatible to 11.1.0.0.0 will suffice. Note that you cannot change compatible to 11.1.0.0.0 once the database is started using a higher value 11.2.0.0.0.
2. You can change the result cache setting for the regions table by executing this statement:
   'alter table regions result_cache (mode default)'
   This will make the OCI client look for any query level hints and since there are none, the query results in query_salary() will now not be cached.
3. Query level hints over-ride the table annotations:
   • /*+ no_result_cache */ – this will ensure queries are not cached. The default is not cache the queries unless there is a
   • /*+ result_cache */ – query level hint or all the tables in the query are annotated for result caching.
4. The method query_salary( ) has added an extra fetch call to get the error OCI_NO_DATA. This is to ensure that we have fetched all of the rows so the cached result set is available for cache matches subsequently.

-- STAGE 5
-- annotate table for result caching so queries on it can be cached on 
-- client result cache
-- Doing below part of stage5 in separate script so can see 
-- performance difference in AWR reports.
ALTER TABLE regions result_cache (mode force);
QUIT;

CONNECT ocihol/welcome
@stage5.sql

tkdiff stage4.c stage5.c

chmod 777 stage*
exit 

cd $HOME/oci/ocilab
gmake stage5

@reports.sql "../stage5 -i 1000"

stage5_oci

Stage 6: Using PL/SQL procedure and REF Cursors

• plsql_exec(): This executes a anonymous PLSQL procedure RAISE_SALARY() in the database. It randomly retrieves an employee number and updates the corresponding salary value in the salary column via PLSQL in binds. It returns updated salary value through PLSQL out binds.
• plsql_refcursor(): This executes the method open_cur() in PLSQL the package emp_demo_pkg in the database. It opens a cursor for the SQL statement, which retrieves all of the employees in the myemp table whose employee number is greater than the passed-in employee number and returns the ref-cursor back.

Note that this ref cursor returned back to the OCI client is like an executed OCI statement handle. You can define and start fetching rows. It calls multirow_fetch_from_emp() to get the rows which is same method used by previous stages to fetch rows using SQL queries.

The OCI prefetching is also available when fetching from a PLSQL ref cursor.

.	Examine the contents for the stage6.c file and compare it to stage5.c. You can use the tkdiff utility to view the differences. Execute the following command to view the stage6.c and stage5.c files. tkdiff stage5.c stage6.c Note the differences in the files. In the stage6.c file, a PL/SQL REF cursor is used with prefetching. Note: If you are working in a Linux environment, you may need to change the permission to execute the stagen files. To do so, execute the following at the command prompt: chmod 777 stage* exit
.	Use the gmake utility to build the executable C programs and library for the source code for stage6. Start a terminal window, navigate (by using the 'cd' command) to the location where the stage files are (/home/oci/ocilab) and execute the gmake utility cd $HOME/oci/ocilab gmake stage6
.	In a terminal window, execute the stage6 file. ./stage6 -i 10 Note: No AWR and ADDM reports are required for the stage6 example.

Stage 7: OCI Secure File LOB Usage

In this stage we demonstrate SecureFile LOBs (the next generation LOBs) with these scenarios:

• lob_write(): This does the following operations:
  • Inserts an empty locator into the lob_table table. DML with returning is used so that another SQL statement is not needed to select this locator. The lobid column value is based off of the thread number.
  • Once you have the locator (OCIDescriptor) selected, you can modify (such as, append, update, trim, or read) and access the LOB. Because the LOB is initially 0 in length, we read a local file and insert that as the LOB's contents using the OCILobWrite() function.
  • The LOB length is returned and then committed. The LOB update works similar to a DML statement - you must issue the COMMIT statement to make the changes visible to other sessions.
• lob_read(): This does following operation:
  • Based on the thread number as LOBID, it queries the lob_table and selects the locator. Then using the LOB locator with OCILobRead(), it reads the LOB locally a piece at a time.

The lob_table has these properties:

• It is defined as SecureFile LOB.
• It is stored in sysaux tablespace
• It uses low compression for quicker read/write
The lobid column to mark each row having the LOB with a unique ID

-- STAGE 7 
-- creating a table to show LOB usage
DROP TABLE lob_table;


create table lob_table (lobid number primary key, lobcol clob)
  LOB(lobcol) STORE AS SECUREFILE (TABLESPACE SYSAUX
  COMPRESS low 
  CACHE );




.	Examine the contents for the stage7.c file and compare it to stage6.c. You can use the tkdiff utility to view the differences. Execute the following command to view the stage7.c and stage6.c files. tkdiff stage6.c stage7.c Note the differences in the files. In the stage7.c file, OCI SecureFile LOB access is demonstrated. Note: If you are working in a Linux environment, you may need to change the permission to execute the stagen files. To do so, execute the following at the command prompt: chmod 777 stage* exit
.	Use the gmake utility to build the executable C programs and library for the source code for stage6. Start a terminal window, navigate (by using the 'cd' command) to the location where the stage files are (/home/oci/ocilab) and execute the gmake utility cd $HOME/oci/ocilab gmake stage7
.	In a terminal window, execute the stage7 file. ./stage7 -i 10 Note: No AWR and ADDM reports are required for the stage7 example.

Summary

In this tutorial, you learned how to:

• Setup and Review of no Optimization with OCI (stage1)
• Optimize by Using Array Fetch and Array DML
• Optimize by Using OCI Client-Side Session Pooling
• Optimize by Using OCI with Client-Side Statement Caching Enabled
• Optimize by Enabling Client Result-Set Caching Enabled
• Use PL/SQL inside OCI
• Use OCI SecureFile LOBs



	About Oracle |Oracle and Sun | | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Your Privacy Rights