Performance Tuning by Identifying Poor SQLs using v$
To a user, an application is never fast enough to satisfy his/her needs. Similarly, to a database administrator, an application is never fast enough to satisfy the needs of the user community. Perception and unreasonable expectations are definitely a cause. However, , insufficient hardware, poorly tuned application code, a poorly tuned database, or an architecture that doesn’t scale well are also to be blamed.
Regardless of the true cause – you’re likely to be asked to tune the application further.
As a PeopleSoft admin, you may find it harder because PeopleSoft is a delivered product and unless it is a customization, which your organization did, the delivered code is likely top notch and went through several rounds of testing before reaching your shores. Usually in such cases – finding the performance bottleneck can seem like searching for a needle in a haystack. Luckily, there are tactics available to quickly hone in on the most resource-intensive modules of a PeopleSoft application.
This post will take you behind the scenes instead of using OEM for performance tuning. We will try to make use of querying v$ views and using built-in PL/SQL packages in the Oracle Server to zero in on the exact SQL statement that is performing poorly. Once you know where the problem is, you can concentrate your tuning efforts there and turn things around.
Observe the V$ Views
Every Oracle database has a set of special objects owned by the SYS user with names that begin “v_$”. These objects are commonly referred to as “v$ fixed tables”, “dynamic performance views”, or more simply “v$ views”. Public synonyms make these objects accessible to users with the SELECT ANY TABLE privilege and also transform their names by dropping the first underscore. The v$ views may be queried just like read-only views, but in fact the query results come from data structures within the SGA instead of blocks in a data file.
The v$ views give real time information about the state of the Oracle instance—what statements are currently being processed, which sessions are waiting for locks, and so on. In this section we will look at the v$ views that are the most helpful in finding performance bottlenecks. Check the Oracle Server Reference manual for full details of all v$ views.
v$session contains one row for each session connected to the instance. Figure 1 shows some of the useful columns in v$session. You’ll find one row in v$session for each of the Oracle daemons (such as PMON and LGWR) as well as one row for each user connection.
|sid||Session identifier unique among all current sessions|
|serial#||Session identifier unique among all sessions that have existed since the instance was started|
|sql_address||The address of the statement currently executing in this session|
|status||The status of the session—typically ACTIVE, INACTIVE, or KILLED|
|username||The Oracle username the session has connected as|
|osuser||The user who initiated the application that connected to the instance, as provided by the operating system on the machine running the client application|
|machine||The name of the machine running the client application that has connected to the instance|
|program||The name of the client application that connected to the instance to create this session|
|module||Voluntary information provided by the client application to describe itself|
|action||Voluntary information provided by the client application to describe the current activity|
Figure 1: Useful columns in v$session
If you believe your Oracle database is consuming lots of system resources, you can look at v$session to determine which sessions are currently active. By examining the username, osuser, machine, program, module, and action columns of v$session, you may be able to isolate the resource usage to an individual user or a particular application module.
v$sqlarea contains one row for each statement (SQL or PL/SQL block) currently in the shared pool. Figure 2 shows some of the useful columns in v$sqlarea. You’ll find one row in v$sqlarea for each statement currently executing, as well as rows for recently executed statements that have not yet aged out of the shared pool. Note that if multiple users execute the exact same statement, or if one user executes the same statement multiple times, only one copy of the statement will be present in the shared pool and hence only one row will appear in v$sqlarea.
|address||The address of the statement within the shared pool—a unique identifier|
|sql_text||The text of the statement, possibly truncated if the statement is not short|
|executions||The number of times this statement has been executed|
|parse_calls||The number of times this statement has been parsed|
|rows_processed||The total number of rows processed (selected, inserted, etc.) by all executions of this statement|
|buffer_gets||The total number of blocks read by all executions of this statement (logical reads)|
|disk_reads||The total number of blocks read by all executions of this statement that resulted in physical reads from disk (physical reads)|
|optimizer_mode||The optimizer mode (rule, first_rows, etc.) used to create an execution plan for this statement|
Figure 2: Useful columns in v$sqlarea
For each statement, v$sqlarea shows the memory address at which its entry is located in the shared pool. This address can be used to uniquely identify each row in v$sqlarea. For each row, sql_text shows the first portion of the actual statement. If you wish to see the entire statement, check out v$sqltext.
By joining v$session and v$sqlarea, you can see the actual statement being executed by an active session and isolate the slow SQL in the application.
FROM v$session A, v$sqlarea B
WHERE A.osuser = ‘PSOFT’
AND B.address = A.sql_address;
In addition to showing the memory address and text of statements in the shared pool, v$sqlarea also shows key statistics such as how many times a statement has been executed, how many times it has been parsed, how many rows it has processed, how many data blocks already in the SGA have been read in the processing of the statement, and how many physical disk reads have been caused. You can use these statistics to answer many performance-related questions about your system.
v$statname, v$sysstat, and v$sesstat
Oracle maintains over 100 statistics in real time on an instance-wide and per-session basis. These are cumulative statistics since the instance was started or since the session began. These statistics cover many interesting areas such as CPU utilization, physical and logical I/O, network traffic, sorts, and full table scans.
Each statistic maintained by Oracle is identified by a unique number, and v$statname shows the names and unique identifiers for all statistics. Each row in v$sysstat contains the name and identifier for one statistic, along with the value of that statistic for the instance as a whole. Each row in v$sesstat, meanwhile, contains a session identifier and a statistic identifier, along with the value of that statistic for the session.
Figures 3, 4, and 5 show the useful columns that make up these three v$ views and Figure 6 lists some of the interesting statistics available.
|statistic#||The unique identifier for the statistic|
|name||The name of the statistic|
Figure 3: Useful columns in v$statname
|statistic#||The unique identifier for the statistic|
|name||The name of the statistic|
|value||The value of the statistic for the instance as a whole|
Figure 4: Useful columns in v$sysstat
|sid||The unique identifier for the session—join to v$session|
|statistic#||The unique identifier for the statistic—join to v$statname|
|value||The value of the statistic for the session|
Figure 5: Useful columns in v$sesstat
|CPU used by this session|
|table scans (long tables)|
|table scan rows gotten|
|parse time cpu|
|bytes sent via SQL*Net to client|
|bytes received via SQL*Net from client|
|SQL*Net roundtrips to/from client|
Figure 6: Some of the statistics maintained in v$sysstat and v$sesstat
By monitoring v$sysstat you can assess the activity in real time on the database as a whole. By monitoring v$sesstat in conjunction with v$session and v$statname you can analyze the activity of an individual session. Because these statistics are cumulative since the instance was started, you can query a statistic, perform an exercise, query the statistic again, and compute the delta to determine what activity occurred during the exercise.
These v$ views give you a lot of capability to peer into a black box and figure out what is happening. Without reviewing source code and application design, you can determine how an application is utilizing system resources. This can allow you to detect a variety of performance problems and identify the characteristics of individual bottlenecks.