Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



v$sqlstats Performance View tips

Article by Rampant Author Chris Foot

v$sqlstats Performance View

Before we discuss the new v$sqlstats view, let me deviate for a minute to provide you with some quick SQL tuning advice. V$SQLAREA is one of my favorite SQL tuning views. Until v$sqlstats came along…

If I want to perform a traditional "top down" tuning approach and tune the highest resource consuming SQL, I'll use the statements below to identify the top resource consuming queries.

The following query identifies the SQL responsible for the most disk reads:

SELECT disk_reads, executions, disk_reads/executions, address, sql_text FROM v$sqlarea WHERE disk_reads > 5000 ORDER BY disk_reads;

The following query identifies the SQL responsible for the most buffer hits:

SELECT buffer_gets, executions, buffer_gets/executions, address, sql_text FROM v$sqlarea WHERE buffer_gets > 10000 ORDER BY buffer_gets;

You can create a more readable report in SQLPLUS by inserting report breaks between the output lines. To generate the report breaks in SQLPLUS, issue the following statement before running the query:

BREAK ON disk_reads SKIP 2 --- for the disk read report and
BREAK ON buffer_gets SKIP 2 --- for the buffer get report

The first query returns SQL statements responsible for generating disk reads greater than 5,000 while the second query returns SQL statements responsible for generating buffer reads greater than 10,000. I used these numbers just as an example but I sometimes use them as a starting point. I'll then adjust them up or down accordingly, based on their output. The numbers also depend on the system I'm reviewing. I'll use different numbers for OLTP environments than I would for data warehouses.

You'll notice that I divide the number of disk and buffer reads by the number of statement executions. If a statement is generating 1,000,000 disk reads but is executed 500,000 times, it probably doesn't need tuning.

Heavy disk reads per statement execution usually means a lack of proper indexing, poor selection criteria, etc.. Heavy buffer reads sometimes means the exact opposite - indexes are being used when they shouldn't be.

But I'm personally most interested in workload, that's why I most often use the buffer cache hits in my initial queries.

But the SQLTEXT column in V$SQLAREA does not provide the entire text of the SQL statement. That's why I include the address column in the report. I can use that value to dump the entire SQL statement from V$SQLTEXT using the statement below (where xxxxxxxx is the value in the address column from the V$SQLAREA reports above):

select sql_text from v$sqltext where address = 'xxxxxxxxx' order by piece;

Oracle 10G R2 provides a new view called v$sqlstats that contains a combination of columns that appear in V$SQL and V$SQLAREA. The benefits that v$sqlstats provides are as follows:

  • Since v$sqlstats contains the entire text of the SQL statement AND its associated performance statistics, we are no longer required to access both the V$SQLTEXT and V$SQLAREA to obtain the information we need.
  • Oracle states that v$sqlstats is faster and more scalable.

The data in V$SQLAREA has a tendency to get its contents flushed out just when you need to get additional information from it. The v$sqlstats view provides users with a longer access window. That's one of the key benefits to this view.




 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks