Top 10 Recursive Views to Look at for Database Performance Troubleshooting

    May 15, 2017 10:34:56 AM by Dan Hotka

    Hi,

    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).

    I will discuss how many of these views are used to assist with performance issues.  Several of my examples use information from one view as input to another.  I will supply useful scripts you can use to track similar issues in your Oracle database.

    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.

    My Top 10 Views (with their related views) will include:

    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$PARAMETER
      1. 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.

    *** Tip *** I’ve long had an idea that if you can give each SQL a name in a comments field (/*  */ after the SELECT, UPDATE, INSERT, DELETE syntax), it would make it easier to use scripts like this to produce the proper explain plans.

    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.  Adjust the last two lines for the access type and operations you desire to seek.  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)

    ***Note***  You need to be licensed to look at this information…check with your DBA staff before running this query.

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

     

    V$SESS_I

    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. 

    Block Gets are blocks being changed (lots of DML perhaps).  Consistent Gets are logical reads (blocks already in buffer cache), Block Changes (similar to Block Gets…but these are the blocks getting the changes), Consistent Changes (you have readers bumping into long running DML, causing read consistency to kick in…readers and writers bumping into each other…). 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 tablespaces 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.

    ***Note*** This was a particularly fun exercise for me years ago, to figure out how to see items in the buffer cache!!!  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.

    ***Tip*** I have advocated using some of the newer flash drive technology with these temporary tablespaces.  Temporary tablespaces are doing the sorting/group bys/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

    Shifting gears a little, ‘Oracle not allowed to work’ is generally a contention issue.  These kinds of performance issues show up pretty quickly using views like V$SYSTEM_EVENT and V$SESSION_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

    It is beyond the scope of this article to discuss wait events.  Perhaps another article!?!!??

     

    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.

     

    Summary

    I've just scratched the surface with Oracle Problem Finding.  I use a variety of scripts and techniques depending on the type of problem people are having.  This article covered finding various issues and tracing the information back to the SQL that is causing the problem.  Once you have the offending SQL, you can fix the offending SQL.

    Next month I’m writing an article for release here on Toad World about finding problems in the Oracle database.  These scripts are actually part of my problem-solving methodology that I use on-site when helping solve Oracle performance issues.  I cover all of this material in my SQL performance tuning classes and I simply give away these scripts. 

     

    Dan Hotka

    Author/Instructor/Oracle Expert

    www.DanHotka.com

    Dan@DanHotka.com

     

    Tags: Oracle

    Dan Hotka

    Written by Dan Hotka

    Dan Hotka is an Author/Instructor/Expert/Oracle ACE Director Alumni who uses TOAD regularly in his web-based and on-line courses. He is well published with many books still available on Amazon or www.DanHotka.com. Dan is available for user groups and company educational events. He regularly speaks at user groups around the world. Check his website for course listings, references, and discount codes for his online courses based in Safari. Dan's most recent book is Toad for Oracle Unleashed