EXPERIENCE WITH V$SQLAREA

1
EXPERIENCE WITH VSQLAREA

• Bill Hoehne
• 314-234-5727

10/11/2001
2
Background

• Upgrade to Vendor Application
• Bogged Down the Larger Database
• Buffer Hit Ratio Stuck at 40
• VSQLAREA Query
• Repetitive Full Table Scan
• Added Index
• VSQLAREA Fan

3
Simple Query

• select sql_text, executions, users_executing,
  disk_reads, buffer_gets
• from vsqlarea
• where disk_reads gt 10000
• order by disk_reads
• SQL_TEXT
• --------------------------------------------------
  --------------------------------------------------
  ----------------------------
• EXECUTIONS USERS_EXECUTING DISK_READS
  BUFFER_GETS
• ------------------- -----------------------------
  ------------------- ---------------------
• SELECT FROM emp WHERE nameb01
• 713
  0 743832
  751360

4
Variation Example - Parsing User and Ratio

• select SQL_TEXT,EXECUTIONS, USERS_EXECUTING,
• DISK_READS, BUFFER_GETS, USERNAME,
• round(((BUFFER_GETS-DISK_READS)/BUFFER_GETS)100
  ,1) RATIO
• from VSQLAREA, DBA_USERS
• where VSQLAREA.PARSING_USER_ID
  DBA_USERS.USER_ID
• and BUFFER_GETS gt 0
• and ((BUFFER_GETS-DISK_READS)/BUFFER_GETS)100
  lt 50
• and DISK_READS gt 1000
• order by ((BUFFER_GETS-DISK_READS)/BUFFER_GETS)
  100 desc
• SQL_TEXT
• --------------------------------------------------
  --------------------------------------------------
  -
• EXECUTIONS USERS_EXECUTING DISK_READS
  BUFFER_GETS USERNAME RATIO
• ------------------- ----------------------------
  -------------------- ---------------------
  ------------------ ---------
• SELECT FROM emp WHERE dept SALES
• 1
  0 21173
  39147 SCOTT 45.9
• SELECT FROM emp WHERE dept ACCOUNTING
• 1
  0 22342
  40317 SCOTT 44.6

5
Be Aware
Shared Pool
Parse Unique Statement
Least Recently Used Flush Shared Pool Shutdown
Instance

• Variable Age of Statistics
• May Need Detective Work (e.g. Not Use Bind
  Variables)
• I/O Statistics in Oracle Blocks
• Occasionally Cryptic Sql Statements
• Goal is to Find Problem Areas

6
SQL Tuning Brief Look
Obsolete or Partial Statistics
Inefficient SQL Processing
Missing Where Condition
Missing Index
Inefficient Subquery
Demanding Joins
Other

• Explain Plan, SQL Trace, or Tkprof
• Books and Classes

7
Variations of Order By Selection

• Disk Reads Big Hitter
• Ratio (e.g. If Not using Bind Variables)
• Buffer_Gets Activity in Memory or Scalability
  Issues
• Executions Shows Repetition of Statements
• Rows_Processed - Network Load?

8
Other Variations

• What is currently Running
• USERS_EXECUTING gt 0
• When Parsed
• FIRST_LOAD_TIME
• SQL Running for a Session Script
• select SQL_TEXT, USERS_OPENING, EXECUTIONS,
  USERS_EXECUTING,
• DISK_READS, BUFFER_GETS,
• USERNAME, SID, LOCKWAIT, STATUS
• from vsqlarea, vsession
• where sid '1'
• and vsqlarea.address vsession.sql_address
• and vsqlarea.hash_value vsession.sql_hash_va
  lue
• order by disk_reads

9
Summary

• Useful View
• What is Happening in the Instance
• Highlight Problem Areas
• Experiment with Variations
• May Need to Run Queries Multiple Times
• Get to Know What is Normal in an Instance
• Can Have Significant Impact SQL Tuning
• Complement to Other Tuning Techniques

10
Discussion

• Questions Comments
• Similar Experiences
• Automated Tools
• e.g. Enterprise Manager Top Session
• Experience with Wait Monitoring
• As Presented in August SLOUG Meeting