11 Multithreaded Applications
Có thể bạn quan tâm
Skip Headers
| Pro*C/C++ Programmer's Guide Release 9.2 Part Number A97269-03 |
|
Home
Book List
Contents
Index
Master Index
Feedback
| View PDF |
Previous
NextIf your development platform does not support threads, ignore this chapter. This chapter contains the following topics:
-
What are Threads?
-
Runtime Contexts in Pro*C/C++
-
Runtime Context Usage Models
-
User Interface Features for Multithreaded Applications
-
Multithreaded Example
-
Connection Pooling
Note:
When using XA with the Pro*C/C++ Precompiler, you must use multithreading provided by XA. Use of multithreading provided by Pro*C/C++ using the statement EXEC SQL ENABLE THREADS will result in an error.
11.1 What are Threads?
Multithreaded applications have multiple threads executing in a shared address space. Threads are "lightweight" subprocesses that execute within a process. They share code and data segments, but have their own program counters, machine registers and stack. Global and static variables are common to all threads, and a mutual exclusivity mechanism is often required to manage access to these variables from multiple threads within an application. Mutexes are the synchronization mechanism to insure that data integrity is preserved.
For further discussion of mutexes, see texts on multithreading. For more detailed information about multithreaded applications, see the documentation of your threads functions.
Pro*C/C++ supports development of multithreaded Oracle Server applications (on platforms that support multithreaded applications) using the following:
-
A command-line option to generate thread-safe code
-
Embedded SQL statements and directives to support multithreading
-
Thread-safe SQLLIB and other client-side Oracle libraries
Note:
While your platform may support a particular thread package, see your platform-specific Oracle documentation to determine whether Oracle supports it.
The chapter's topics discuss how to use the preceding features to develop multithreaded Pro*C/C++ applications:
-
Runtime contexts for multithreaded applications
-
Two models for using runtime contexts
-
User-interface features for multithreaded applications
-
Programming considerations for writing multithreaded applications with Pro*C/C++
-
An example multithreaded Pro*C/C++ application
11.2 Runtime Contexts in Pro*C/C++
To loosely couple a thread and a connection, Pro*C/C++ introduces the notion of a runtime context. The runtime context includes the following resources and their current states:
-
Zero or more connections to one or more Oracle Servers
-
Zero or more cursors used for the server connections
-
Inline options, such as MODE, HOLD_CURSOR, RELEASE_CURSOR, and SELECT_ERROR
Rather than simply supporting a loose coupling between threads and connections, Pro*C/C++ provides the ability to loosely couple threads with runtime contexts. Pro*C/C++ allows your application to define a handle to a runtime context, and pass that handle from one thread to another.
For example, an interactive application spawns a thread, T1, to execute a query and return the first 10 rows to the application. T1 then terminates. After obtaining the necessary user input, another thread, T2, is spawned (or an existing thread is used) and the runtime context for T1 is passed to T2 so it can fetch the next 10 rows by processing the same cursor. See Figure 11-1, " Loosely Coupling Connections and Threads".
Figure 11-1 Loosely Coupling Connections and Threads
Description of the illustration lnpcc021.gif 11.3 Runtime Context Usage Models
Two possible models for using runtime contexts in multithreaded Pro*C/C++ applications are shown here:
-
Multiple threads sharing a single runtime context
-
Multiple threads using separate runtime contexts
Regardless of the model you use for runtime contexts, you cannot share a runtime context between multiple threads at the same time. If two or more threads attempt to use the same runtime context simultaneously, a runtime error occurs.
11.3.1 Multiple Threads Sharing a Single Runtime Context
Figure 11-2 shows an application running in a multithreaded environment. The various threads share a single runtime context to process one or more SQL statements. Again, runtime contexts cannot be shared by multiple threads at the same time. The mutexes in Figure 11-2 show how to prevent concurrent usage.
Figure 11-2 Context Sharing Among Threads
Description of the illustration lnpcc023.gif 11.3.2 Multiple Threads Sharing Multiple Runtime Contexts
Figure 11-3 shows an application that executes multiple threads using multiple runtime contexts. In this situation, the application does not require mutexes, because each thread has a dedicated runtime context.
Figure 11-3 No Context Sharing Among Threads
Description of the illustration lnpcc022.gif 11.4 User Interface Features for Multithreaded Applications
The Pro*C/C++ Precompiler provides the following user-interface features to support multithreaded applications:
-
Command-line option, THREADS=YES|NO
-
Embedded SQL statements and directives
-
Thread-safe SQLLIB public functions
11.4.1 THREADS Option
With THREADS=YES specified on the command line, the Pro*C/C++ Precompiler ensures that the generated code is thread-safe, given that you follow the guidelines. With THREADS=YES specified, Pro*C/C++ verifies that all SQL statements execute within the scope of a user-defined runtime context. If your program does not meet this requirement, a precompiler error is returned.
| See Also: "Programming Considerations" for guidelines regarding the THREADS option |
11.4.2 Embedded SQL Statements and Directives
The following embedded SQL statements and directives support the definition and usage of runtime contexts and threads:
-
EXEC SQL ENABLE THREADS;
-
EXEC SQL CONTEXT ALLOCATE :context_var;
-
EXEC SQL CONTEXT USE { :context_var | DEFAULT};
-
EXEC SQL CONTEXT FREE :context_var;
For these EXEC SQL statements, context_var is the handle to the runtime context and must be declared of type sql_context as follows:
sql_context <context_variable>;Using DEFAULT means that the default (global) runtime context will be used in all embedded SQL statements that lexically follow until another CONTEXT USE statement overrides it.
11.4.2.1 EXEC SQL ENABLE THREADS
This executable SQL statement initializes a process that supports multiple threads. This must be the first executable SQL statement in your multithreaded application.
| Note: When using XA with the Pro*C/C++ Precompiler, you must use multithreading provided by XA. Use of multithreading provided by Pro*C using the statement EXEC SQL ENABLE THREADS will result in an error. |
| See Also: "ENABLE THREADS (Executable Embedded SQL Extension)" |
11.4.2.2 EXEC SQL CONTEXT ALLOCATE
This executable SQL statement allocates and initializes memory for the specified runtime context; the runtime-context variable must be declared of type sql_context.
| See Also: "CONTEXT ALLOCATE (Executable Embedded SQL Extension)" |
11.4.2.3 EXEC SQL CONTEXT USE
This directive instructs the precompiler to use the specified runtime context for subsequent executable SQL statements. The runtime context specified must be previously allocated using an EXEC SQL CONTEXT ALLOCATE statement.
The EXEC SQL CONTEXT USE directive works similarly to the EXEC SQL WHENEVER directive in that it affects all executable SQL statements which positionally follow it in a given source file without regard to standard C scope rules. In the following example, the UPDATE statement in function2() uses the global runtime context, ctx1:
sql_context ctx1; /* declare global context ctx1 */ function1() { sql_context :ctx1; /* declare local context ctx1 */ EXEC SQL CONTEXT ALLOCATE :ctx1; EXEC SQL CONTEXT USE :ctx1; EXEC SQL INSERT INTO ... /* local ctx1 used for this stmt */ ... } function2() { EXEC SQL UPDATE ... /* global ctx1 used for this stmt */ }To use the global context after using a local context, add this code to function1():
function1() { sql_context :ctx1; /* declare local context ctx1 */ EXEC SQL CONTEXT ALLOCATE :ctx1; EXEC SQL CONTEXT USE :ctx1; EXEC SQL INSERT INTO ... /* local ctx1 used for this stmt */ EXEC SQL CONTEXT USE DEFAULT; EXEC SQL INSERT INTO ... /* global ctx1 used for this stmt */ ... }In the next example, there is no global runtime context. The precompiler refers to the ctx1 runtime context in the generated code for the UPDATE statement. However, there is no context variable in scope for function2(), so errors are generated at compile time.
function1() { sql_context ctx1; /* local context variable declared */ EXEC SQL CONTEXT ALLOCATE :ctx1; EXEC SQL CONTEXT USE :ctx1; EXEC SQL INSERT INTO ... /* ctx1 used for this statement */ ... } function2() { EXEC SQL UPDATE ... /* Error! No context variable in scope */ }| See Also:
|
11.4.2.4 EXEC SQL CONTEXT FREE
This executable SQL statement frees the memory associated with the specified runtime context and places a null pointer in the host program variable.
| See Also: "CONTEXT FREE (Executable Embedded SQL Extension)" |
11.4.3 CONTEXT USE Examples
The following code fragments show how to use embedded SQL statements and precompiler directives for two typical programming models; they use thread_create() to create threads.
The first example showing multiple threads using multiple runtime contexts:
main() { sql_context ctx1,ctx2; /* declare runtime contexts */ EXEC SQL ENABLE THREADS; EXEC SQL CONTEXT ALLOCATE :ctx1; EXEC SQL CONTEXT ALLOCATE :ctx2; ... /* spawn thread, execute function1 (in the thread) passing ctx1 */ thread_create(..., function1, ctx1); /* spawn thread, execute function2 (in the thread) passing ctx2 */ thread_create(..., function2, ctx2); ... EXEC SQL CONTEXT FREE :ctx1; EXEC SQL CONTEXT FREE :ctx2; ... } void function1(sql_context ctx) { EXEC SQL CONTEXT USE :ctx; /* execute executable SQL statements on runtime context ctx1!!! */ ... } void function2(sql_context ctx) { EXEC SQL CONTEXT USE :ctx; /* execute executable SQL statements on runtime context ctx2!!! */ ... }The next example shows how to use multiple threads that share a common runtime context. Because the SQL statements executed in function1() and function2() potentially execute at the same time, you must place mutexes around every executable EXEC SQL statement to ensure serial, therefore safe, manipulation of the data.
main() { sql_context ctx; /* declare runtime context */ EXEC SQL CONTEXT ALLOCATE :ctx; ... /* spawn thread, execute function1 (in the thread) passing ctx */ thread_create(..., function1, ctx); /* spawn thread, execute function2 (in the thread) passing ctx */ thread_create(..., function2, ctx); ... } void function1(sql_context ctx) { EXEC SQL CONTEXT USE :ctx; /* Execute SQL statements on runtime context ctx. */ ... } void function2(sql_context ctx) { EXEC SQL CONTEXT USE :ctx; /* Execute SQL statements on runtime context ctx. */ ... }11.4.4 Programming Considerations
While Oracle ensures that the SQLLIB code is thread-safe, you are responsible for ensuring that your Pro*C/C++ source code is designed to work properly with threads; for example, carefully consider your use of static and global variables.
In addition, multithreaded applications require design decisions regarding the following:
-
Declaring the SQLCA as a thread-safe struct, typically an auto variable and one for each runtime context
-
Declaring the SQLDA as a thread-safe struct, like the SQLCA, typically an auto variable and one for each runtime context
-
Declaring host variables in a thread-safe fashion, in other words, carefully consider your use of static and global host variables.
-
Avoiding simultaneous use of a runtime context in multiple threads
-
Whether or not to use default database connections or to explicitly define them using the AT clause
Also, no more than one executable embedded SQL statement, for example, EXEC SQL UPDATE, may be outstanding on a runtime context at a given time.
Existing requirements for precompiled applications also apply. For example, all references to a given cursor must appear in the same source file.
11.5 Multithreaded Example
The following program is one approach to writing a multithreaded embedded SQL application. The program creates as many sessions as there are threads. Each thread executes zero or more transactions, that are specified in a transient structure called "records."
| Note: This program was developed specifically for a Sun workstation running Solaris. Either the DCE or Solaris threads package is usable with this program. See your platform-specific documentation for the availability of threads packages. |
11.6 Connection Pooling
Connection pool is a group of physical connections to a database that can be re-used by several named connections. The objective of the connection pooling feature is to improve performance, and reduce resource use by avoiding usage of dedicated connections by each named connection.
Figure 11-4 illustrates functionality of the connection pooling feature. In this example, four threads of the application are interacting with the database using the connection pool. The connection pool has two physical connections. The connection pool handle is used by four threads using different runtime contexts.
Figure 11-4 Connection Pooling
thread1() { EXEC SQL CONTEXT ALLOCATE :ctx1; EXEC SQL CONNECT :uid AT :TC1 USING :db_string; ... } thread2() { EXEC SQL CONTEXT ALLOCATE :ctx2; EXEC SQL CONNECT :uid AT :TC2 USING :db_string; ... } thread3() { EXEC SQL CONTEXT ALLOCATE :ctx3; EXEC SQL CONNECT :uid AT :TC3 USING :db_string; EXEC SQL AT :TC3 SELECT count(*) into :count FROM emp; ... } thread4() { EXEC SQL CONTEXT ALLOCATE :ctx4; EXEC SQL CONNECT :uid AT :TC4 USING :db_string; ... } In this example, four named connections TC1, TC2, TC3, and TC4 are virtual connections created by threads T1, T2, T3, and T4 respectively. Named connections TC1, TC2, TC3, and TC4 from different runtime contexts share the same connection pool, and share physical database connections available in the connection pool. Two physical connections, C1 and C2, serve four named connections and connect to the same database.
When the first connect request TC1 from thread T1 is received, SQLLIB creates a connection pool with one physical connection C1 to the database. When another connect request TC2 from thread T2 is sent to the same database, C1 serves the TC2 request to the database, if it is free. Otherwise, a new physical connection C2 is created to serve the request. If another connect request from thread T3 named TC3 comes in, TC3 either waits for a specified time or returns an error message, if both physical connections C1 and C2 are busy.
When thread T2 needs to select data using the TC2 named connection, it acquires any free physical connection, C1 or C2. After the request is served, the chosen connection will again be available in the connection pool, so that another named or virtual connection can utilize the same physical connection.
11.6.1 Using the Connection Pooling Feature
This section comprises the following topics:
-
How to Enable Connection Pooling
-
Command Line Options for Connection Pooling
-
Example
-
Performance Tuning
11.6.1.1 How to Enable Connection Pooling
To enable connection pooling while precompiling an application, user must set the command line option CPOOL=YES. Based on CPOOL=YES/NO, the connection pool feature is enabled or disabled.
| Note: By default, CPOOL is set to NO and hence the connection pool feature is disabled. This feature cannot be enabled or disabled inline. |
11.6.1.2 Command Line Options for Connection Pooling
Tab le 11-1 lists the command line options for connection pooling:
Table 11-1 Command Line Options for Connection Pooling
| Option | Valid Value | Default | Remarks |
|---|---|---|---|
| CPOOL | YES/NO | NO | Based on this option, the precompiler generates the appropriate code that directs SQLLIB to enable or disable the connection pool feature. Note: If this option is set to NO, other options will be ignored by the precompiler. |
| CMAX | Valid values are 1 and above | 100 | Specifies the maximum number of physical connections that can be opened for the database. CMAX value should be at least CMIN+CINCR. Note: Once this value is reached, more physical connections cannot be opened. In a typical application, running 100 concurrent database operations is more than sufficient. The user can set an appropriate value. |
| CMIN | Valid values are 1 and above | - | Specifies the minimum number of physical connections in the connection pool. Initially, all physical connections as specified through CMIN are opened to the server. Subsequently, physical connections are opened only when necessary. Users should set the total number of planned or expected concurrent statements to be run by the application to get optimum performance. The default value is set to 2, as it is expected that users writing multithreaded applications would create at least two threads. |
| CINCR | Valid values are 1 and above | 1 | Allows the application to set the next increment for physical connections to be opened to the database, if the current number of physical connections is less than CMAX. To avoid creating unnecessary extra connections, the default value is set to 1. |
| CTIMEOUT | Valid values are more than 1 | Will not be set; hence will not time out. | Physical connections that are idle for more than the specified time (in seconds) are terminated to maintain an optimum number of open physical connections. If this attribute is not set, the physical connections are never timed out. Hence, physical connections will not be closed until the connection pool is terminated. Note: Creating a new physical connection will cost a round trip to the server. |
| CNOWAIT | Valid values are 1 and above | Will not be set; hence waits for a free connection. | This attribute determines if the application must repeatedly try for a physical connection when all other physical connections in the pool are found busy, and the total number of physical connections has already reached its maximum. If physical connections are not available and no more physical connections can be opened, an error is thrown when this attribute is set. Otherwise, the call waits until it acquires another connection. By default, this is not to be set because a thread can wait until it acquires a free connection, instead of returning an error. |
A typical multithreaded application creates a pool of 'n' server handles. The 'n' value needs to be specified by providing the CMIN value during precompilation. A minimum number of physical connections (CMIN) to the database are created initially. For new incoming requests, the mapping from a virtual connection (named connection) to a physical connection is carried out as described in the following section:
Case 1: If a physical connection is available (among the already opened connections), a new request will be served by this connection.
Case 2: If all physical connections are in use then,
Case 2a: If the number of opened connections has not reached the maximum limit (CMAX), new CINCR connections are created, and one of these connections is used to serve the request.
Case 2b: If the number of opened connections has reached the maximum limit (CMAX) without the CNOWAIT being set, the request waits until it acquires a connection. Otherwise, the application displays an error message that all physical connections are busy and no more physical connections can be opened.
11.6.1.3 Example
Refer Figure 11-4 for an illustration of the following example.
Let CMIN be 1, CMAX be 2, and CINCR be 1.Consider the following scenario. When the first request TC1 comes in, SQLLIB creates the connection pool with one physical connection C1. When another request TC2 comes in, the application checks if C1 is free. As C1 is used to serve the first request (Case 1), a new physical connection C2 is created to serve the request (Case 2a). If another request TC3 comes in, and if both C1 and C2 are busy, then TC3 either waits for a specified time or returns with an error message (Case 2b).
11.6.1.4 Performance Tuning
Users can set the connection pooling parameters to get better performance, based on the application. The Performance Graph in Figure 11-5 illustrates performance gain by changing the CMIN value for the Pro*C/C++ Demo Program:1. Demo Program:2 illustrates performance gain by changing the CMAX parameter.
11.6.2 Demo Program:1
The following connection pool parameters are used while precompiling the Demo Program:1.
CMAX = 40 CINCR = 3 CMIN = 1..40 CTIMEOUT - Do not set(indicates that physical connection never times out)
CNOWAIT - Do not set(indicates that the thread waits until it gets a free connection; see Table 11-1, "Command Line Options for Connection Pooling", for more details)
Other command line options to precompile are based on the example provided in the following section:
userid = hr/hr sqlcheck = full dbms = v8 threads = yes| Note: In this example, No. of threads = 40, and database operations are done against the local database. |
It was observed that with CPOOL=NO (without connection pool), time taken by the application was 6.1 seconds. Whereas, with CPOOL=YES (with connection pool), minimum time taken by the application was 1.3 seconds, with CMIN=2.
In both cases, the time taken for database query operation may remain same. The connection pool does not reduce for database query operations except for CONNECT statements. The overall time taken for total connection operations when CPOOL=YES will be less compared to the case when CPOOL=NO. In the earlier example, when CPOOL=NO, the application will create 40 dedicated connections. When CPOOL=YES, it will create 2 connections initially and only if 2 threads access the connections concurrently, it will create more connections. Otherwise, all threads will share those 2 connections. So the application avoids 38 connections which in turn avoids 38 round trips to the server to establish connections. This is where the application gains performance by three times.
| Note: The earlier result was observed with a Sparc Ultra60 single CPU, 256 MB RAM machine, running one Oracle9i server on Solaris 2.6 operating system; the server and client were running on the same machine. |
Figure 11-5 Performance Graph
The CPOOL=YES curve represents the time taken by the application when connection pool is enabled. The CPOOL=NO curve represents the time taken by the application when connection pool is disabled.
11.6.2.1 Example
/* * cpdemo1.pc * * Description: * The program creates as many sessions as there are threads. * Each thread connects to the default database, calls COMMIT and * executes a simple SELECT statement. Each thread have its own * runtime contexts. * */ #include <stdio.h> #include <stdlib.h> #include <string.h> #include <sqlca.h> #define _EXC_OS_ _EXC__UNIX #define _CMA_OS_ _CMA__UNIX #define MAX_ROWS 256 #define BUFF_LEN 21 #ifdef DCE_THREADS #include <pthread.h> #else #include <thread.h> #endif /* Function prototypes */ void err_report(); #ifdef DCE_THREADS void do_transaction(); #else void *do_transaction(); #endif void get_transaction(); void logon(); void logoff(); #define CONNINFO "hr/hr" #define THREADS 40 struct parameters { sql_context * ctx; int thread_id; }; typedef struct parameters parameters; struct timeval tp1; struct timeval tp2; /*************************************** * Main ***************************************/ main() { sql_context ctx[THREADS]; #ifdef DCE_THREADS pthread_t thread_id[THREADS]; pthread_addr_t status; #else thread_t thread_id[THREADS]; int status; #endif parameters params[THREADS]; int i; EXEC SQL ENABLE THREADS; EXEC SQL WHENEVER SQLERROR DO err_report(sqlca); if(gettimeofday(&tp1) == -1) { perror("First: "); exit(0); } /* Create THREADS sessions by connecting THREADS times */ for(i=0;i<THREADS;i++) { printf("Start Session %d....",i); EXEC SQL CONTEXT ALLOCATE :ctx[i]; logon(ctx[i],CONNINFO); } /*Spawn threads*/ for(i=0;i<THREADS;i++) { params[i].ctx=ctx[i]; params[i].thread_id=i; #ifdef DCE_THREADS if (pthread_create(&thread_id[i],pthread_attr_default, (pthread_startroutine_t)do_transaction, (pthread_addr_t) ¶ms[i])) #else if (status = thr_create (NULL, 0, do_transaction, ¶ms[i], 0, &thread_id[i])) #endif printf("Cant create thread %d\n",i); else printf("Created Thread %d\n", i); } /* Logoff sessions....*/ for(i=0;i<THREADS;i++) { /*wait for thread to end */ #ifdef DCE_THREADS if (pthread_join(thread_id[i],&status)) printf("Error when waiting for thread % to terminate\n", i); else printf("stopped\n"); printf("Detach thread..."); if (pthread_detach(&thread_id[i])) printf("Error detaching thread! \n"); else printf("Detached!\n"); #else if (thr_join(thread_id[i], NULL, NULL)) printf("Error waiting for thread to terminate\n"); #endif logoff(ctx[i]); EXEC SQL CONTEXT FREE :ctx[i]; } if(gettimeofday(&tp2) == -1) { perror("Second: "); exit(0); } printf(" \n\nTHE TOTAL TIME TAKEN FOR THE PROGRAM EXECUTION = %f \n\n", (float)(tp2.tv_sec - tp1.tv_sec) + ((float)(tp2.tv_usec - tp1.tv_usec)/1000000.0)); } /*********************************************************************** * Function: do_transaction * Description: This function calls CMMIT and execute a simple SELECT * statement. ***********************************************************************/ #ifdef DCE_THREADS void do_transaction(params) #else void *do_transaction(params) #endif parameters *params; { struct sqlca sqlca; char empName[MAX_ROWS][BUFF_LEN]; int src_count; sql_context ctx=params->ctx; EXEC SQL WHENEVER SQLERROR DO err_report(sqlca); EXEC SQL CONTEXT USE :ctx; printf("Thread %d executing transaction\n",params->thread_id); EXEC SQL COMMIT; EXEC SQL SELECT FIRST_NAME into empName from EMPLOYEES where JOB_ID= (select JOB_ID from EMPLOYEES where EMPLOYEE_ID=205); } /************************************************************** * Function: err_report * Description: This routine prints out the most recent error **************************************************************/ void err_report(sqlca) struct sqlca sqlca; { if (sqlca.sqlcode < 0) printf("\n%.*s\n\n",sqlca.sqlerrm.sqlerrml,sqlca.sqlerrm.sqlerrmc); exit(1); } /************************************************************ * Function: logon * Description: Logs on to the database as USERNAME/PASSWORD ************************************************************/ void logon(ctx,connect_info) sql_context ctx; char * connect_info; { EXEC SQL WHENEVER SQLERROR DO err_report(sqlca); EXEC SQL CONTEXT USE :ctx; EXEC SQL CONNECT :connect_info; printf("Connected!\n"); } /*************************************************** * Function: logoff * Description: This routine logs off the database ***************************************************/ void logoff(ctx) sql_context ctx; { EXEC SQL WHENEVER SQLERROR DO err_report(sqlca); EXEC SQL CONTEXT USE :ctx; EXEC SQL COMMIT WORK RELEASE; }11.6.3 Demo Program:2
The following connection pool parameters are used while precompiling the Demo Program:2.
CMAX = 5..40
CINCR = 3
CMIN = 1..40
CTIMEOUT - Do not set
(indiactes that physical connection never times out)
CNOWAIT - Do not set
(indicates that thread waits until it gets a free connection; see Table 11-1, "Command Line Options for Connection Pooling", for more details)
Other command line options to precompile are based on the example provided in the following section:
userid = hr/hr
sqlcheck = full
dbms = v8
threads = yes
The following figure illustrates the performance graph for cpdemo2.
| Note: In this example, No. of threads = 40, and database operations are done against the local database. |
In this example, the demo program does UPDATE operation along with SELECT operation. The best performance is observed when CMIN=5 and CMAX=14. The performance improvement in this demo is around 2.3 times when CPOOL=YES is used over the use of CPOOL=NO option.
The "cpdemo1" shows more than 3 times performance improvement, whereas "cpdemo2" shows only 2.3 times performance improvement. The reason being "cpdemo1" uses simple SELECT statement whereas "cpdemo2" uses UPDATE and SELECT statements. "cpdemo1" spends more time in creating connections than the database operations. When connection pool is enabled, it saves time as lesser number of connections are created. So, overall performance becomes better. Since "cpdemo2" spends less time in creating connections compared to the database operations, the overall performance gain is less.
In the following graphs, the CPOOL=YES curve represents the time taken by the application when connection pool is enabled. The CPOOL=NO curve represents the time taken by the application when connection pool is disabled. The demo program "cpdemo2" creates 40 threads. With CPOOL=NO option, each thread establishes its own dedicated connection to the server. Hence 40 connections are created. The same demo program when built with CPOOL=YES option, and with CMAX set to 14, a maximum of 14 connections are created. So, at most it creates 14 connections and these 14 connections will be shared across 40 threads which will avoid another 26 dedicated connections, thereby the program avoids 26 round-trips to the server.
The following two graphs are drawn one with varying CMIN and other with varying CMAX respectively.
11.6.3.1 Case 1: By varying CMIN
Figure 11-6 Performance Graph Case 1
The application takes around 7.5 seconds for execution with CPOOL=NO. With CPOOL=YES, and CMIN=8 and CMAX=14, the execution time reduces to 4.5 seconds. So, the improvement of performance is about 1.7 times. The difference in performance is because of different database operations (SELECT vs UPDATE) which is purely a server side activity and beyond the scope of connection pool feature which is a client side feature.
11.6.3.2 Case 2: By varying CMAX
Figure 11-7 Performance Graph Case 2
For the preceding graph the demo program was run with CMIN=15 ,CINCR=3 and the best performance was observed at CMAX=14. It takes around 7.4 seconds for the execution when CPOOL=NO. With CPOOL=YES, when CMAX=14 the execution time reduces to around 3.1 seconds resulting in 2.3 times performance gain.
The performance improvement for cpdemo2 was more when CMAX was varied. So, to get the best performance for a given application, the user is expected to vary CMIN (case 1) and CMAX(case 2) suitably till the best performance is achieved.
11.6.3.3 Example
/* * Program to show the performance improvement when cpool option is used * Run this program with cpool=no. Record the time taken for the program to * execute * * Compare the execution time * * This program also demonstrates the impact of properly tuned CMAX * parameter on the performance * * Run the program with the following parameter values for best performance * * CMIN=5 * CINCR=2 * CMAX=14 * */ #include <stdio.h> #include <sqlca.h> #ifdef DCE_THREADS #include <pthread.h> #else #include <thread.h> #endif #define CONNINFO "hr/hr" #define THREADS 40 #define MAX_ROWS 256 #define BUFF_LEN 21 /***** prototypes ************** */ #ifdef DCE_THREADS void selectFunction(); void updateFunction(); #else void *selectFunction(); void *updateFunction(); #endif void err_report(struct sqlca sqlca); /* ************************* */ /***** parameter to the function selectFunction, updateFunction */ struct parameters { sql_context ctx; char connName[20]; char dbName[20]; int thread_id; }; typedef struct parameters parameters; /*******************************************/ parameters params[THREADS]; struct timeval tp1; struct timeval tp2; int main() { int i, status; thread_t thread_id[THREADS]; int thrNos[THREADS]; for(i=0; i<THREADS; i++) thrNos[i] = i; EXEC SQL ENABLE THREADS; /* Time before executing the program */ if(gettimeofday(&tp1) == -1){ perror("First: "); exit(0); } EXEC SQL WHENEVER SQLERROR DO err_report(sqlca); /* connect THREADS times to the data base */ for(i=0; i<THREADS; i++) { strcpy(params[i].dbName, "inst1"); sprintf(params[i].connName,"conn%d", i); params[i].thread_id = i; /* logon to the data base */ EXEC SQL CONTEXT ALLOCATE :params[i].ctx; EXEC SQL CONTEXT USE :params[i].ctx; EXEC SQL CONNECT :CONNINFO AT :params[i].connName USING :params[i].dbName; } /* create THREADS number of threads */ for(i=0;i<THREADS;i++) { printf("Creating thread %d \n", i); if(i%2) { /* do a select operation if the thread id is odd */ #ifdef DCE_THREADS if(pthread_create(&thread_id[i],pthread_attr_default, (pthread_startroutine_t)selectFunction, (pthread_addr_t) ¶ms[i])) #else if(thr_create(NULL, 0, selectFunction, ¶ms[i], 0, &thread_id[i])) #endif printf("Cant create thread %d \n", i); } else { /* othewise do an update operation */ #ifdef DCE_THREADS if(pthread_create(&thread_id[i],pthread_attr_default, (pthread_startroutine_t)updateFunction, (pthread_addr_t) ¶ms[i])) #else if(thr_create(NULL, 0, updateFunction, ¶ms[i], 0, &thread_id[i])) #endif printf("Cant create thread %d \n", i); } } for(i=0; i<THREADS; i++) { #ifdef DCE_THREADS if(pthread_join(thread_id[i],&status)) printf("Error when waiting for thread % to terminate\n", i); if(pthread_detach(&thread_id[i])) printf("Error detaching thread! \n"); #else if(thr_join(thread_id[i], NULL, NULL)) printf("Error waiting for thread i(%d) to terminate\n", i); #endif } if(gettimeofday(&tp2) == -1){ perror("Second: "); exit(0); } printf(" \n\nTHE TOTAL TIME TAKEN FOR THE PROGRAM EXECUTION = %f \n\n", (float)(tp2.tv_sec - tp1.tv_sec) + ((float)(tp2.tv_usec - tp1.tv_usec)/1000000.0)); /* free the context */ for(i=0; i<THREADS; i++) { EXEC SQL CONTEXT USE :params[i].ctx; EXEC SQL AT :params[i].connName COMMIT WORK RELEASE; EXEC SQL CONTEXT FREE :params[i].ctx; } return 0; } #ifdef DCE_THREADS void selectFunction(parameters *params) #else void *selectFunction(parameters *params) #endif { char empName[MAX_ROWS][BUFF_LEN]; printf("Thread %d selecting .... \n", params->thread_id); EXEC SQL CONTEXT USE :params->ctx; EXEC SQL AT : params->connName SELECT FIRST_NAME into empName from EMPLOYEES; printf("Thread %d selected ....\n", params->thread_id); return 0; } #ifdef DCE_THREADS void updateFunction(parameters *params) #else void *updateFunction(parameters *params) #endif { printf(" Thread %d Updating ... \n", params->thread_id); EXEC SQL CONTEXT USE :params->ctx; EXEC SQL AT :params->connName update EMPLOYEES set SALARY = 4000 where DEPARTMENT_ID = 10; /* commit the changes */ EXEC SQL AT :params->connName COMMIT; printf(" Thread %d Updated ... \n", params->thread_id); return 0; } /*********** Oracle error ***********/ void err_report(struct sqlca sqlca) { if (sqlca.sqlcode < 0) printf("\n%.*s\n\n",sqlca.sqlerrm.sqlerrml,sqlca.sqlerrm.sqlerrmc); exit(0); }
|  Copyright © 1996, 2003, Oracle Corporation All Rights Reserved. |
|
Từ khóa » đa Luồng C++
-
Đa Luồng (Multithread) Trong C++ - Học Lập Trình Web
-
Multithreading Trong C++ Là Gì? - CodeLearn
-
Tổng Quát Về Lập Trình đa Luồng Trong Modern C++
-
Làm Quen Với Multithreading Trong C++ - Viblo
-
Std::thread Trong C++ — Modern C++
-
Đa Luồng (Multithread) Trong C++ Mới Nhất - Tải Phần Mềm
-
Đa Luồng (Multithread) Trong C++ | 101 Bài Hướng Dẫn C++ Hay Nhất ...
-
Kỹ Thuật C++ Dùng đa Luồng Trong C++ Sao Lại Chạy Chậm đi! Help ...
-
Thread Trong C++ Là Gì
-
Multithread Phân Luồng Thread Trong C++ - YouTube
-
Giúp Mình Tìm Hiểu Về đa Luồng Trong C++ [Archive]
-
Tạo Thread Và đa Xử Lý Trong 1 Chương Trình - Toan Vnaking's Blog/site
-
[PDF] LẬP TRÌNH ĐA TUYẾN (MULTI –THREAD)