Listen Software's How To: SQL Tuning - File I/O Performance
August 8, 2002
The following concepts explain the relationship between SQL Area and Shared Pool.
The data dictionary takes its stake from the shared pool first, then the library cache takes the remainder of the pool.
The Library Cache is used to store SQL statements and PL/SQL blocks to be shared by users. The library Cache prevents statement reparsing, which increases response performance.
High reloads means that the SQL Area is reloading sqls from disk.
Steps for evaluating the SQL Area (Library Cache):
Check if the SQL is in the SQL Area. Reload if possible.
Parse the SQL for syntax errors.
Check the data dictionary for objects.
Check the object privileges (based on roles or ownership).
Execute the SQL.
Update, Insert, and Delete use the data buffer to the store block header information. The block header information references the data store in the rollback segment. The data buffer contains the before and after image of the data.
A commit causes the log writer to flush the redo buffer to the redo logs,redo logs are checkpointed, and data writer then writes the data cache to disk and checkpoints the data.
If the log buffer fills up to a third full, the log write flushes the buffer to the redo logs. Therefore, redo logs can contain both committed and uncommitted transactions.
Measure SQL Area performance by the following indicators:
Getting the byte sizes of the SGA
select * from v$sgastat
Library Cache Performance
The gethitratio is equal to the GETHITS/GETS. This ratio needs to remain in the high nineties.
Calculating the Reload Ratio
sum(reloads) 'Cache Misses',
Reloads should never be more than 1% of the pins.
Data Dictionary Cache
((gets-getmisses)/gets)*100 'ratio' from
- GETS: Number of requests on a object.
- GETMISSES: Number of requests resulting in cache misses.
The ratio during normal running needs to be greater than 85 percent.
If there are too many getmisses, increase the SHARED_POOL_SIZE.
Data Cache Hit Ratios
select 1-(phy.value/(cur.value + con.value))
"CACHE HIT RATIO"
from v$sysstat cur,
cur.name='db block gets'
and con.name = 'consistent gets'
and phy.name = 'physical reads'
Ratio success is dependant on your development environment. OLTP should be 90 percent.
The buffer cache holds copies of the data blocks from datafiles. The data blocks are shared, since they are in the SGA.
The server processes reads data from the datafiles into the buffer cache; so performance may be low in the morning because data is being loading into the buffer cache.
The Database Writer (DBWR) writes data from the buffer cache into the data files. Every three seconds DBWR wakes up to check the dirty list for blocks to write.
Each buffer holds a single database block. Therefore, it is critical to know the OS block size and set the db_block_size to match the OS block size.
If the data block is modified a dirty bit is set.
Pinned buffers are memory blocks that are currently being referenced.
Server Process relationship to the DB Buffer Cache
Look for the data block in the db buffer using a hash algorithm.
If not found read the datablock in from the datafile.
Search Least Recently Used (LRU) list for a free block. Move dirty blocks to the dirty block list.
If the dirty block threshold or search threshold is exceeded flush the dirty blocks.
If a free block is found move the datafile block to the free blockand add to the end of the LRU list.
Statistics for File I/O
FROM V$Datafile d, v$filestat f
Rollback wait stats
select usn, waits from v$rollstat;
Rollback Segment Contention
select rn.name, sum(rs.waits)/sum(rs.gets) "Ratio",
sum(rs.waits) "waits", sum(rs.gets) "Gets"
from v$rollstat rs, v$rollname rn
where rs.usn = rn.usn
group by rn.name
select disk.value "Disk"
, mem.value "Mem"
, (disk.value/mem.value)*100 "Ratio"
from v$sysstat mem, v$sysstat disk
Utlbstat and utlestat scripts
Gather performance over a defined period.
Produces a hard-copy report.
- utlbstat.sql->stores statistics in BEGIN tables
- utlestat.sql->stores statistics in END tables
Examines both tables and produces a report
- Library cache statistics
- System statistics
- Wait event statistics
- Rollback contention statistics
- Buffer Busy Wait Statistics
- Dictionary cache statistics
- I/O Statistics per datafile/tablespace
- Period of measurement
Set TIME_STATISTICS TO TRUE
Alter system set TIMED_STATISTICS=TRUE;
ANALYZE AN APPLICATION USER SCHEMA
The ANALYZE command populates the following tables with statistics:
- Table data storage
- Cluster data storage
- table and index paritions data storage
- Non-indexed and index columns
- System wide statistics
V$PROCESS: active processes
V$WAITSTAT: contention statistics
V$SYSTEM_EVENT: waits for particular events
from v$lock a,
alter system kill session
where type is TX or TM meaning
- TX-> exclusive row lock
- TM-> shared lock
Using TKProf (analyzing the trace file)
Enabling tracing at the session level
ALTER SESSION SET SQL_TRACE=TRUE
Enabling tracing at the session user level
(session_id, serial_id, TRUE | FALSE)
Enabling Tracing at the instance level
Turning tracing off requires shutting down the instance and restarting the instance.
tkprof80 sys=no ora_9999.trc myfile.txt
Back to the LSS "How To" Series Main Page