Using 10G’s V$SESS_TIME_MODEL and V$SYS_TIME_MODEL Performance Views to Evaluate Database Performance

One of the common mistakes that is often made during a tuning exercise is not gathering enough high level information during the initial stages of the problem determination process. As I stated in my introduction, the key to success is to begin the analysis at a high level and then narrowing the scope of information collection until the problem is identified.

Once the problem is identified, we tune and tweak, record the same statistics we used to identify the problem and compare the before and after information. This iteration of recording/changing/recording/comparing is done again (and again) until the problem is corrected.

Almost every component in Oracle that has an affect on database performance is measured by the database system. One of the most common measurements is the amount of time the user processes and system spend performing a particular operation.

V$SESS_TIME_MODEL and V$SYS_TIME_MODEL Statistics Views
V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views provide information on exactly that - where the user processes are spending their time. All of the timings are recorded in microseconds. One of the key indicators recorded in the time model views is DB Time, which is the total amount of time spent performing database operations.

The time model views differ from each other in that the V$SESS_TIME_MODEL view stores timing information for individual sessions while the V$SYS_TIME_MODEL view provides information at instance level. As a result, you won't find the column for SESSION_ID in the V$SYS_TIME_MODEL view. In addition, V$SYS_TIME_MODEL records information historically from instance startup, so don't be concerned if you add up all of the time spent by the current indiviudal sessions and it doesn't match the DBTIME value in V$SYS_TIME_MODEL view. One last thing, use the timings as a relative reference, they may not add up exactly because of the way they are recorded by Oracle.

Let's take a look at the contents of these views:

V$SESS_TIME_MODEL and V$SYS_TIME_MODEL Contents

• SID (NUMBER) - Session Identififer. This is the same value that you will find in all views that record information about individual sessions. This column is useful if you want to join V$SESS_TIME_MODEL to V$SESSION to retrieve additional information on the session being evaluated. As stated previously, since V$SYS_TIME_MODEL records information at the instance level, you won't find this column in the view.
  
  
• STAT_ID (NUMBER) Statistic identifier for the time statistic.
  
  
• STAT_NAME (VARCHAR2 64) Name of the statistic being recorded. A listing of all of the statistics is provided below.
  
  
• VALUE (NUMBER) - Amount of time, in microseconds, the session has spent performing the operation identified in the STAT_NAME column.

Values for STAT_NAME:

Knowing the hierarchy of the the contents is important. I have included a hierarchy chart that will tell you what timings consist of sub-timings.

• DB Time - Amount of time spent performing operations in the database. The V$SYS_TIME_MODEL view does not record and add any time spent by the database background processes. As a result, the V$SYS_TIME_MODEL view records the total user workload, while the V$SESS_TIME_MODEL view records information about the workload produced by the individual session identified in the SID column. You compare this value against all of the other values contained in this table to determine where the bulk of the time is being spent.
  
  
• DB CPU - Amount of CPU time spent performing operations in the database. Like DB TIME, DB CPU only records user workload. If you see a relatively high value in this time look for complex calculations and poor SQL plans that perform a high level of buffer gets.
  
  
• Connection Management Call Elapsed Time - This value represents the amount of time processes spent performing CONNECT and DISCONNECT calls. This time should be much lower than most of the other values contained in this table. If it is high in V$SYS_TIME_MODEL, review program code to ensure that the application isn't attempting to make a connection and disconnection for each interaction with the database. This is a very common problem for applications built using a middle tier application server.
  
  
• Sequence Load Elapsed Time - Amount of time spent obtaining the next sequence number from Oracle's Data Dictionary. If the sequence is cached, the time is not recorded. A sequence is a user created object that generates numbers according to a specific pattern. It is most often used to generate unique identifiers to identify a specific object.
  
  
• SQL Execute Elapsed Time - This is a very important measurement. It records the amount of time that SQL statements are executing. This will also record the amount of time SELECT statements spend fetching the query results. Typically, user processes spend the bulk of their time accessing data. If it is extremely high, look for poorly performing SQL statements. If the user process is having performance problems and this indicator does not make up the majority of overall DB Time, check for poorly written programs, parsing problems, poor connection management, network issues.
  
  
• Repeated Bind Elapsed Time - Elapsed time spent on re-binding.
  
  
• Parse Elapsed Time - This records the amount of time the process spent hard parsing and soft parsing SQL statements. Oracle parses each statement before it is executed. The parse process includes syntax checking (making sure you spelled "WHERE" right), ensuring the objects being accessed are actually in the database, security checking, execution plan creation and loading the parsed representations into the shared pool.
  
  
• Hard Parse Elapsed Time - The amount of time spent hard parsing a SQL statement before execution. Before a statement enters the parse phase, Oracle matches the statement being executed to statements that are already parsed and stored in the shared pool. When Oracle finds a matching statement in the shared pool, it will do a soft parse on the SQL statement. If Oracle does not find any matching SQL in the shared pool, it will perform a hard parse, which requires that more steps be performed than its soft parse counterpart. As with almost everything else, fewer steps = faster performance.
  
  The key to obtaining a high number of soft parses is to match as many incoming statements to statements already stored in the shared pool as possible. This is trickier than it sounds and requires vigilant and rigorous SQL coding standards. When Oracle looks for a matching SQL statement in the shared pool, the statements must match exactly.
  
  If you see a high percentage of hard parses being performed, the most common culprit is SQL statements that don't use bind variables. A bind variable holds values that are passed from application programs. It allows the programs to change the search values in the WHERE clauses without having to perform a hard parse. Application programs that don't take advantage of bind variables continues to be a common problem.
  
  
• Hard Parse (Sharing Critieria) Elapsed Time - This value represents the amount of elapsed time the database was forced to hard parse a SQL statement because it was unable to find an existing cursor in the SQL Cache. It is a subset of the Hard Parse Elapsed time. If this value is high, look for programs that don't use bind variables.
  
  
• Hard Pase (Bind Mismatch) Elapsed Time - Signifies the amount of elapsed time spent performing hard parses because the bind variable's type or bind size did not match existing cursors in the cache. Oracle documentation also states that bind type mismatches often causes indexes not to be used. If this value is high, look for poor programing standards.
  
  
• Failed Parse Elapsed Time - Records the time spent attempting, and ultimately failing, to parse a statement before execution. Check the program code to ensure that the statements are syntactially correct and review Metalink notes for issues.
  
  
• Failed Parse (Out of Shared Memory) - Pretty self explanatory. The amount of elapsed time recorded when a parse failed because of a lack of adequate resources allocated to the shared pool. If this time is high, you need to add more memory to the shared pool, identify if bind variables are being used, etc.
  
  
• PL/SQL Execution Elapsed Time - Amount of elapsed time spent running the PL/SQL interpreter. It does not include the time executing and parsing SQL statements or the amount of time the process spent recursively executing the Java VM.
  
  
• PL/SQL Compilation Elapsed Time - Elapsed time spent running the PL/SQL compiler. It is the compiler's job to transform PL/SQL source code into machine-readable code (m-code).
  
  
• Inbound PL/SQL RPC Elapsed Time - Records the elapsed time PL/SQL remote procedure calls spent executing including executing SQL and JAVA.
  
  
• Java Execution Elapsed Time - Amount of time spent running the JAVA machine. This time does not include the time spent executing and parsing SQL statements or recursively executing PL/SQL.

Quick Demo
If you look at this attached text file, you'll see a series of statements that I executed against the time model views to analyze a performance problem.

I hope you enjoyed reading this article on Oracle's time model views.