module18-tuning.htm; updated July 14, 2013

Module 18 – Database Tuning
(and Other Performance Issues)

Objectives


TUNING APPLICATION DESIGN

Effective Table Design

 

Distribution of CPU Requirements

 

Effective Application Design

1.           limit the number of times that users access the database, and

2.           coordinate the requests of users for data.  This requires you to know how users tend to access data.

1.           The SQL Text for all procedures, functions, and packages can be viewed in the TEXT column of the DBA_SOURCE view.

2.           These objects (procedural code) are stored in the SYSTEM tablespace so you need to allocate more space to it -- usually double its size.

 


TUNING SQL

 

Indexes

CREATE INDEX City_state_zip_ndx
ON Employee (City, State, Zip)
TABLESPACE Indexes;

SELECT *
FROM Employee
WHERE State = 'NJ';

 

Ordering Data

SELECT *
FROM Employee
WHERE Empno BETWEEN 1 and 100;

 

·        You can physically sort table rows by SELECTing them to another file with use of the ORDER BY clause, then truncating the original table and loading the rows back into the original table. 

 

Clusters

 

Explain Plan

 

EXPLAIN PLAN
SET Statement_id = 'TEST'
FOR

SELECT *
FROM Employee
WHERE city > 'Y%';

 

 

SELECT LPAD(' ',2*LEVEL) || operation
       || ' ' || options ||
       ' ' || object_name Path_Plan
FROM Plan_Table
WHERE Statement_id = 'TEST'
CONNECT BY PRIOR Id = Parent_id
    AND Statement_id = 'TEST'

    START WITH Id=1;

Path_Plan
-----------------------------------
TABLE ACCESS BY ROWID EMPLOYEE
  INDEX RANGE SCAN CITY_ST_ZIP_NDX

 

 


TUNING MEMORY USAGE

You can use the Oracle Enterprise Manager software to analyze usage of memory by Oracle’s various memory caches.

 

Hit Ratio = (Logical Reads - Physical Reads) /
                    Logical Reads

 

 

 DB_BLOCK_LRU_STATISTICS = TRUE

 

 

SELECT Sum(Count) Lost_Hits
FROM Sys.X$Kcbcbh
WHERE indx >= New_Number_Of_Buffers;

(NOTE: You supply the value in the WHERE clause)

 

 

Hit Ratio =
  (Logical Reads - Physical Reads - Lost Hits)
              / Logical Reads

 



TUNING DATA STORAGE

Defragmentation of Segments

Fragmented tables with multiple extents will slow down query processing.  This can also slow down the storage of new records because the database may have to dynamically combine free extents to create a new extent large enough to meet the storage parameters of the object where data are being stored.

 

SELECT Tablespace_name TSName, Owner,
    Segment_Name SNName,
    Segment_type SNType, Extents,

    Blocks, Bytes
FROM Sys.DBA_Segments;
 

TSNAME OWNER SNNAME    SNTYPE EXTENTS BLOCKS BYTES
DATA   DBOCK LONGTIME  TABLE  1       15     61440
DATA   DBOCK MAGAZINE  TABLE  1       15     61440
DATA   DBOCK MATH      TABLE  1       15     61440
DATA   DBOCK WORKERANDSKILL CLUSTER 2 30    122880
 

 

SELECT Tablespace_name TSNAME, Owner,
    Segment_Name SNNAME,
    Segment_Type SNTYPE, Extent_id EID,
    File_id FID, Block_id BID, Bytes, Blocks
FROM Sys.DBA_Extents
WHERE Segment_type = 'segment_name'
ORDER BY Extent_id;

TSNAME OWNER SNNAME     SNTYPE EID FID BID Bytes Blocks
DATA   DBOCK SYS_C00890 INDEX  0   4   137 61440 15
DATA   DBOCK SYS_C00891 INDEX  0   4   152 61440 15
 

 

Defragmentation of Free Extents

A Free Extent is a collection of contiguous free blocks in a tablespace that are unused.

 

Identifying chained Rows

 

ANALYZE TABLE Table_Name
LIST CHAINED ROWS INTO Chained_Rows;

 


Increasing the Oracle Block Size

o   Using the next higher block size value may improve performance of query-intensive operations by up to 50 percent.

o   Problem:  You must rebuild the entire database to increase the block size.

o   Improvement comes because the block header does not increase significantly leaving more space in a block for transaction information and for data rows.

 

Bulk Deletes: The TRUNCATE Command

 

TRUNCATE TABLE Employee DROP STORAGE;

TRUNCATE CLUSTER Emp_Dept REUSE STORAGE;

 

 



END OF NOTES