0

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

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.

Column	Description
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

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.

Column	Description
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.

Example: #1:

SELECT B.sql_text
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.

Column	Description
statistic#	The unique identifier for the statistic
name	The name of the statistic

Figure 3: Useful columns in v$statname

Column	Description
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

Column	Description
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

Number	Name
12	CPU used by this session
39	Physical reads
40	physical writes
119	table scans (long tables)
123	table scan rows gotten
129	parse time cpu
131	parse count
133	bytes sent via SQL*Net to client
134	bytes received via SQL*Net from client
135	SQL*Net roundtrips to/from client
140	sorts (disk)
141	sorts (rows)

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.