SQL Tuning - File I/O Performance

File I/O and Performance Tuning

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 priviledges (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 and 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

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

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

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

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

Step 5: If a free block is found move the datafile block to the free block and 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

 

Optimizer

Optimizer Steps: Each step of the execution plan returns a set of rows that are used either by the next step or in the last step.

The optimizer calculates the cost based on the estimated computer resources, including but not limited to I/O, CPU time, and memory.

The goal of the cost-based approach is the best throughput, or minimal resource usage necessary to process all rows accessed by the statement.

alter system set TIMED_STATISTICS=TRUE; 

execute sys.dbms_utility.analyze_schema

('APPLICATION_USER','COMPUTE');

Cost-Based Approach

The optimizer generates a set of execution plans based on the possible join orders, join operations, and available access paths. The optimizer estimates the costs of each plan and chooses the one with the lowest cost.

* A smaller sort area size is likely to increase the cost for sort-merge join. Increase the SORT_AREA_SIZE variable in the initialization file.

Indexes

Indexes improve the performance of queries that select a small percentage of rows from the table (2 to 4 percent).

* Only index columns with good selectivity. An index's selectivity is good if few rows have the same value.

Selectivity = number of row / number of distinct rows

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SQL Tuning - The Explain Plan

SQL Tuning - The Explain Plan

Purpose: The following three steps provide a method to get
information back on how the optimizer will evaluate your SQL.
Note, the optimizer uses statistics to determine an execution path.

Step 1 - Create the PLAN_TABLE

    create table PLAN_TABLE (

        statement_id    char(30),

        timestamp       date,

        remarks         char(80),

        operation       char(30),

        options         char(30),

        object_node     char(30),

        object_owner    char(30),

        object_name     char(30),

        object_instance numeric,

        object_type     char(30),

        search_columns  numeric,

        id        numeric,

        parent_id numeric,

        position  numeric,

        other           long

    )

    STORAGE 

    (

    INITIAL 300k 

    NEXT 1M 

    MINEXTENTS 1 

    MAXEXTENTS 121 

    PCTINCREASE 0

    )

    PCTFREE  10

    PCTUSED 70

    TABLESPACE {TABLESPACE_NAME}

Step 2 - Submit your SQL to the optimizer

    DELETE FROM PLAN_TABLE;

    COMMIT;

    EXPLAIN PLAN

    SET STATEMENT_ID = 'IDENTIFICATION STRING'

    INTO PLAN_TABLE

    FOR

    SELECT field1,field2 from table where field1=criteria;

    @PLAN1

Step 3 - Display the Explain Plan (Plan1.SQL)

    /*Run from SQL Plus*/

    CLEAR SCREEN

    PROMPT

    ACCEPT STATE_ID 

    PROMPT '  ENTER THE STATEMENT ID TO BE DISPLAYED: ';

    SET VERIFY OFF

    PROMPT

    COLUMN OPERATION FORMAT A20

    COLUMN OPTIONS FORMAT A15

    COLUMN OBJECT_NAME FORMAT A20

    COLUMN ID FORMAT 999 HEADING 'ID '

    COLUMN PARENT_ID FORMAT 999 HEADING 'PARENT|ID  '

    COLUMN POSITION FORMAT 999

    SELECT OPERATION,OPTIONS,OBJECT_NAME,ID,PARENT_ID,POSITION

    FROM PLAN_TABLE

    WHERE STATEMENT_ID = '&STATE_ID'

    ORDER BY ID;

    UNDEFINE STATE_ID

    SET VERIFY ON

 

Hints

CACHE - Specifies that the blocks retrieved for the table in the
hint are placed at the most recently used end of the LRU
list in the buffer cache when a full table scan is performed.

    select /*+ FULL(scott_emp) CACHE(scott_emp) */

    ename from scott_emp

INDEX(table INDEX) Table - Specifies the name of the table or
alias associated with the index. Index specifies the index
on which to scan.

    select /*+ INDEX(scott_emp emp_id_pk) */

    field1 from scott_emp where id=1;

    AND_EQUAL (table index index index)  

Uses an access path that merges the scans on several single-column
indexes. Table specifies the table associated with the indexes.

ORDERED - The ORDERED hint causes Oracle to join tables in the
order in which they appear in the From clause.

    SELECT /*+ ORDERED */ tab1.col1, tab2.col2

    from tab1, tab2

    where tab1.col1=tab2.col2

FIRST_ROWS - Returns a cursor after the first row is fetched.