Listen Software’s How To: SQL Tuning – File I/O Performance

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

1. Check if the SQL is in the SQL Area. Reload if possible.

2. Parse the SQL for syntax errors.

3. Check the data dictionary for objects.

   (
   tables, 
   views, 
   snapshots, 
   functions,
   packages, 
   and procedures
   )

4. Check the object privileges (based on roles or ownership).

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

6. Measure SQL Area performance by the following indicators:

   • Pins: hits in memory

   • Gets: hits from file

   • Invalidations: Errors in the sql that prevent execution. Suppose an object in the SQL is modified. The shared SQL area becomes invalid and the statement must be reparsed before execution. This process is called reloading.

   Model

   • Reloads->High and Invalidations
     ->Low means increase Memory

   • Reloads->Low and Invalidations
     ->High means misses

Getting the byte sizes of the SGA

select * from v$sgastat

Library Cache Performance

select 
namespace, 
gethitratio, 
pinhitratio, 
reloads, 
invalidations
from v$librarycache

The gethitratio is equal to the GETHITS/GETS. This ratio needs to remain in the high nineties.

Calculating the Reload Ratio

select 
sum(pins) 'Executions', 
sum(reloads) 'Cache Misses',
sum(reloads)/sum(pins)
from v$librarycache

Reloads should never be more than 1% of the pins.

Data Dictionary Cache

select parameter,getmisses,
((gets-getmisses)/gets)*100 'ratio' from
v$rowcache
where gets>0

• 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,
v$sysstat con,
v$sysstat phy
where
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.

Model

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

1. Look for the data block in the db buffer using a hash algorithm.

2. If not found read the datablock in from the datafile.

3. Search Least Recently Used (LRU) list for a free block. Move dirty blocks to the dirty block list.

4. If the dirty block threshold or search threshold is exceeded flush the dirty blocks.

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

SELECT NAME,
PHYRDS,
PHYWRTS,
PHYBLKRD,
PHYBLKWRT,
READTIM,
WRITETIM
FROM V$Datafile d, v$filestat f
where d.file#=f.file#

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

Sorting

select disk.value "Disk"
, mem.value "Mem"
, (disk.value/mem.value)*100 "Ratio"
from v$sysstat mem, v$sysstat disk
where mem.name='sorts(memory)'
and disk.name='sorts(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

Execute sys.dbms_utility.analyze_schema
('APPLICATION_USER','COMPUTE');

The ANALYZE command populates the following tables with statistics:

Table data storage

DBA_TABLES
DBA_TAB_COLUMNS

Cluster data storage

DBA_CLUSTERS
INDEX_STATS

table and index paritions data storage

DBA_TAB_PARTITIONS
DBA_IND_PARTITIONS
DBA_PART_COL_STATISTICS

Non-indexed and index columns

DBA_HISTOGRAMS
INDEX_HISTOGRAMS

System wide statistics

V$PROCESS: active processes
V$WAITSTAT: contention statistics
V$SYSTEM_EVENT: waits for particular events

Transactions

select a.sid,
a.type,
a.id1,
a.lmode,
a.request,
b.oracle_username,
c.serial#
from v$lock a,
v$locked_object b,
v$session c
where
a.sid=b.session_id
and
a.sid=c.sid;

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

EXECUTE sys.dbms_system.set_sql_trace_in_session
(session_id, serial_id, TRUE | FALSE)

Enabling Tracing at the instance level

Turning tracing off requires shutting down the instance and restarting the instance.

Windows NT

tkprof80 sys=no ora_9999.trc myfile.txt

Trace statistics

• Count: times the procedure was executed

• CPU: Seconds to process

• Elasped: Seconds to execute

• Disk: Physical reads

Back to the LSS “How To” Series Main Page