Monitor ORACLE database performance in complex application environments (1)

Preface

In a single application environment or system with relatively simple business, the bottleneck of system performance problems is often self-evident. The premise for solving the problem is that locating the problem is easier to solve, however, in a complex application environment, various application systems tend to share and compete with system resources, and application systems may also have symbiotic or restrictive relationships, the balance of resource interests is often due to this shift. Once resource competition occurs in application systems in such environments, the bottleneck of the system is hard to determine, there may even be conflicts between different Application designers. This article only explores the use of ORACLE databases in various application systems on the Linux platform, the ORACLE Database TUNING is not a topic that can be explained in a word. This article is not intended to be comprehensive and covers only the problem locating and the sharing and competition of database resources by various applications.

Environment for testing and problem forensics in this article

RedHat6.1 Web server (Apache1.3.9 + PHP4.0) + Client/Server (Pro * C) Server

RedHat6.2 + Oracle8.1.6.1.0

RedHat7.1 Web server (Apache1.3.20 + PHP4.06) + Oracle8.1.7.0.0

To facilitate the discussion, the application system has been simplified. competitors only include a daemon program of Pro * C as the server of the C/S mode, and WEB website services supported by Apache + PHP.

1. Processing of a single SQL statement

First, the simplest case is the analysis of a single SQL statement. The optimization of SQL statements is also the most direct and immediate factor for database optimization. The performance monitoring of SQL statements is generally provided by advanced languages and by ORACLE itself. The advanced languages take typical application of C language and WEB development language PHP as examples, in C, the gettimeofday function can be used to obtain a time value before and after a database operation. The difference between the two time values is used to measure the efficiency of the database operation. In PHP, you can also use gettimeofday. the operation method is certainly different from that in C. Of course, some other functions in PHP can achieve the same time precision. Considering the time precision, we cannot simply measure the time value in microseconds, because the interval of clock interruption fundamentally determines the accuracy of time calculation, in addition, the operating system itself allocates time slices of processes, the overhead of process switching and other factors also affect the time data to a certain extent. Therefore, the ideal condition for the calculation of the following time is to perform multiple loop operations on the same operation to avoid caching as much as possible, and average the total time value, to obtain a time value close to the actual situation.

C language example

# Define TV _START 0

# Define TV _END 1

Int how_long (int cmd, char * res );

Struct performance_time {

Int times;

/* Times occured within specified package number */

Struct timeval time;

/* Total time consumed by the cmd */

};

Void foo ()

{

Int id;

How_long (TV _START, NULL );

Exec SQL WHENEVER SQLERROR CONTINUE;

Exec SQL WHENEVER NOT FOUND CONTINUE;

Exec SQL select user_id into: id from users where name = 'slimzhao'; 2;

How_long (TV _END, time_consume );

Puts (time_consume );

}

Int how_long (int cmd, char * res)

/* Return value:-1 error, 0 sucess, res: 20 bytes is enough */

{

Static struct timeval before, after;

If (cmd = TV _START ){

Gettimeofday (& before, NULL );

Return 0;

} Else if (cmd = TV _END ){

Gettimeofday (& after, NULL );

If (res ){

If (after. TV _usec> before. TV _usec ){

Sprintf (res, "% ld", after. TV _sec-before. TV _sec,

After. TV _usec-before. TV _usec );

} Else {

Sprintf (res, "% ld ",

After. TV _sec-before. TV _sec-1,

1000000 + after. TV _usec-before. TV _usec );

}

}

Return 0;

} Else {

Return-1;

}

}