V$* views for Database Performance Troubleshooting

Views to Look at for Database Performance Troubleshooting

Recursive views present various data dictionary information in a readable form.  In Oracle, real-time information can generally be monitored using the V$ dictionary views.  Oracle tracks literally everything going on both within the database and with your applications within the database.  It’s just a matter of finding the information, and knowing that it exists.  There is another set of views that store information, called the DBA_ views.  These views are used to track various types of metadata (data about data).

There are so many different things you can monitor and follow up on using these views, so this article will focus on finding the SQL associated with a particular performance issue.  If you can get the SQL and its execution plan, you can generally solve your application performance issues.  Sometimes the issue is with the database itself being extremely busy.  It is beyond the scope of this article to go into this level of detail but watch for other blog posts and articles from me on finding other database performance issues.

1. V$SQL
1. V$SQL_AREA
2. V$SQL_PLAN
2. DBA_HIST_SQLTEXT
3. V$SESS_IO
4. V$FILESTAT
1. V$TABLESPACE
2. V$DATAFILE
5. V$TEMPSTAT
1. V$TABLESPACE
2. V$DATAFILE
6. V$SYSTEM_EVENT
7. V$SESSION_EVENT
8. V$SESSION_WAIT
1. DBA_EXTENTS
9. V$SQL_BIND_CAPTURE
10. V$SGASTAT

• V$SQL

I start with this topic because all performance issues are caused by some kind of application SQL.  Sometimes it’s contention (Oracle is not allowed to work), in that poorly designed applications are trying to access/change the same information at the same time.  Sometimes it’s I/O problems or just long-running SQL.  The SQL needs to be tuned.  Going back to contention, it’s amazing how many locking issues simply go away if the offending SQL just runs a little faster.

So, to do SQL tuning, you need explain plans.  I have plenty of blog posts on getting execution plans and what the content means.  This article will show you how to find the offending SQL, not necessarily what to do about it…maybe we can cover those topics in a future article.

The following two scripts will be useful to get the explain plans for particular offensive SQL, once you have found some or all of the SQL text.

Select plan_table_output

From v$sql s,

table(dbms_xplan.display_cursor(s.sql_id, s.child_number)) t

Where s.sql_text like '%&Enter_SQL_Text%'

This SQL will return the explain plan that the SQL executed with.  It will prompt you for partial SQL text.

Select sql_text

From v$sql_area a, v$sql_plan p

Where a.address = p.address

And p.owner not in (‘SYS’,’SYSTEM’)

And p.operation = ‘TABLE ACCESS’

And p.options like ‘FULL%’;

This SQL will find specific explain plan operations using V$SQL_AREA and V$SQL_PLAN.   Take the SQL text that is returned, or partial SQL text…and put it into the first query above to find the explain plan.  Enhance this SQL by changing the last two lines to prompts.

SQL in the v$SQL area are currently in the library cache. 

• DBA_HIST_SQLTEXT  

Select plan_table_output

From dba_hist_sqltext s,

Table(dbms_xplan.display_cursor(s.sql_id)) t

Where s.sql_text like '%&Enter_SQL_Text%'

This query does the same thing as the first explain plan query above except that it looks into the automated workload repository (AWR)

These explain plans are collected and stored for the past seven days.

• V$SESS_IO

OK, now that we can get explain plans for our offending SQL, let me show you some clever ways of finding the offending SQL.

Of course, you can use Toad or SQL Developers reports showing the Top SQL, top this/top that/etc…but this series will help you track down the SQL causing a particular performance issue.

desc V$sess_io

Name                     Null Type  

------------------------ ---- ------

SID                           NUMBER

BLOCK_GETS                    NUMBER

CONSISTENT_GETS               NUMBER

PHYSICAL_READS                NUMBER

BLOCK_CHANGES                 NUMBER

CONSISTENT_CHANGES            NUMBER

OPTIMIZED_PHYSICAL_READS      NUMBER

Using V$SESS_IO, you can get an idea of what kind of ‘working too hard’ problem your SQL is causing. 

1. Block Gets are blocks being changed (lots of DML perhaps).  
2. Consistent Gets are logical reads (blocks already in buffer cache),  
3. Block Changes (similar to Block Gets…but these are the blocks getting the changes), 
4. Consistent Changes (you have readers bumping into long running DML, causing read consistency to kick in…readers and writers bumping into each other…).  
5. Physical Reads (getting blocks from tablespaces).

Use this information in the next series to look for the SQL contributing to your performance issue.

• V$FILESTAT

select t.name, d.name, d.file# "file#", phyrds, phywrts, phyblkrd, phyblkwrt

from v$tablespace t, v$datafile d, v$filestat s

where d.file# = s.file#

and t.ts# = d.ts#

order by 4 desc;

This information will show physical I/O operations across the table-spaces and their associated data files.  Adjust the ORDER BY clause (last line) to have the top I/O operations appear at the top of the report.

select b.name object, a.dbarfil "File#", count(a.dbablk) "Num Blocks", sum(a.tch) "Touch Count"

from x$bh a, obj$ b

where a.obj = b.dataobj#

and a.tch > 0

and a.file# = &file_no

group by b.name, a.dbarfil

order by 4 desc;

Take the file number in the above report as input into this SQL (you have to run this as SYS…be careful out there!!!).  This will show you the tables with a lot of I/O on those particular data files.

 The X$BH IS the buffer cache.  X$ tables are the underlying Oracle dictionary tables used to run the database.  I used V$ view descriptions to help interpret the column and table names in the X$ tables.

Take the table name from this report into the V$SQL or AWR history to get your explain plans.  This table name is indeed partial SQL text.

V$TEMPSTAT

Your problem could be that your temporary tablespaces are being used too much.  Oracle10g gave us the ability to have tablespace groups with several temporary tablespaces assigned to each group.  The group is assigned to the users.  As they connect, Oracle RDBMS round-robins assigns them to a temporary tablespace within the group.

 Temporary tablespaces are doing the sorting/group result set storage for larger queries…and have a lot of physical I/O all the time.
 

select t.name, d.name,s.PHYRDS, s.PHYWRTS, s.PHYBLKRD, s.PHYBLKWRT, dd.logging

from v$tablespace t,v$tempfile d, v$filestat s, dba_tablespaces dd

where d.file# = s.file#

and t.ts# = d.ts#

and dd.contents = 'TEMPORARY'

and dd.TABLESPACE_NAME = t.NAME

order by 1,2;

If you are I/O bound on your temporary tablespaces, simply add more temporary tablespaces…or…use the Temporary Tablespace Groups to help divvy up the I/O operations across physical files.

V$SYSTEM_EVENT

select      event, 

      total_waits, 

      total_timeouts, 

      (time_waited / 100) time_waited, 

      (average_wait / 100) average_wait 

from  sys.v_$system_event 

where total_waits > 0 

order by time_waited desc;

Wait events are where Oracle logs its time when it’s doing something.  These events show up in a number of places including Stats Pack and SQL Trace.  What you are looking for are the events with larger numbers, as this shows the time logged on those events by various Oracle processes, and processes your applications are working with.

  

Oracle System Wait Events

V$SESSION_EVENT 

select      username, event, 

      total_waits, 

      total_timeouts, 

      (time_waited / 100) time_waited, 

      (average_wait / 100) average_wait 

from  sys.v_$session_event e, v$session s

where e.sid = s.sid

and username IS NOT null

and   total_waits > 0 

order by time_waited desc;

This is the same information, except that it has been gathered by connected user.  This will probably be more useful information to your SQL and your application issues.

 

 Wait Events by User

In this screen shot, notice the ‘enqueue’ wait event.  This indicates a problem with row-level locking.  Another user has the data block locked while this user is trying to update it as well. The other user’s SQL is blocking this particular SQL that is getting the enqueue wait event time and causing this particular SQL to wait in a blocked state.  Ask me for the blocking SQL information. Toad shows the blocking user as well, to see the SQL that is actually causing the issue.  This SQL is paying the price for another SQL that is holding the resource (the lock) too long. 

V$SESSION_WAIT

The wait events DB FILE Sequential Read (index or single block reads) and DB FILE Scattered Read (full table scans or fast full index scans) deal with physical reads.  These wait events are also logged in V$SESSION_WAIT.

  

 

V$SESSION_WAIT

Notice there are P1, P2, and P3 parameters that get populated.  When you are dealing with wait events that work with data, P1 is the File number and P2 is the block number of the object working on the wait event.  Use these numbers to query DBA_EXTENTS for the object name. 

Now, you are back to the original two SQL statements…you have partial SQL text and can look up the SQL in either the library cache (V$SQL) or AWR (seven days of history) and get explain plans.

V$SQL_BIND_CAPTURE

If your offending SQL statement has bind variables, having the data associated with these bind variables is VERY useful in the SQL tuning process.

You take the SQL_ID from your SQL statement and look up the bind variable content that was submitted with your SQL using V$SQL_BIND_CAPTURE.  If you have access to AWR, this information is also stored for seven days in DBA_HIST_SQLBIND.

  

 

V$SQL_BIND_CAPTURE

The key fields are SQL_ID and VALUE_STRING.  The datatype might be of interest depending on how you run your explain plans.

You can also query V$SQL_BIND_DATA.

V$SGASTAT

And finally number 10.  I use this query when I need more physical memory; for the buffer cache, perhaps. 

This query shows the size and available space in the shared pool; that is mostly the library cache.  The library cache holds all the SQL, unless it gets full, then it ages out.  This pool used to be rather small but starting in Oracle9, it started growing…and growing.  Now, this pool is usually way big and has the potential to share available memory with other processes.

SELECT to_number(p.value) psize, s.bytes fspace, (s.bytes/p.value) * 100 pfree

FROM v$parameter p, v$sgastat s

WHERE p.name = 'shared_pool_size'

AND   s.name = 'free memory'

and   s.pool = 'shared pool'

 

Shared Pool Size

This script is showing me that we have an 11MB shared pool size, of which most is free.  If this were a busy system, I’d say there is easily 8MB of space that could be used for other Oracle processes.