9i Performance Tuning Guide and Reference

9i Performance Tuning Guide and Reference PDF

9i Performance Planning

Notes from studying for the 9i Performance Tuning test. Information on this webpage came from one of the links provided

buffer busy waits

Check point flushes the modified data from the database buffer cache to the disk 
and updates the headers on the datafile and control files


a check point occurs when -
the database is shutdown except with shutdown abort
alter system checkpoint
a log switch occurs - size to switch logs at most every 20 minutes
log_checkpoint_interval is reached set this value to 0 -9i
log_checkpoint_timeout is reached  set this value to 0 -9i


FAST_START_MTTR_TARGET = 0 
FAST_START_IO_TARGET=0


"CHECKPOINT NOT COMPLETE" - check points are occuring to frequently,
Log writer has to wait. 
Log writer LGWn needs the file but DBWn did not finish checkpointing the file.

v$system_event

When a delete is performed, which type of undo is generated?

Oracle9i Database: Performance Tuning

Exam Topics

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_exam_id=1Z0_033

Make sure that there is no serialization or single threading going on within the application

Long-term, it is generally more valuable to increase the efficiency of your application in terms of the number of physical resources used for each business transaction.

designers must create a design that uses the same resources, regardless of user populations and data volumes, and does not put loads on the system resources beyond their limits

v$sysstat
v$sess_io
v$session



SELECT 1 – ( physical.value / (blockgets.value+consistent.value)) “Buffer Cache Hit Ratio”
FROM v$sysstat physical
, v$sysstat blockgets
, v$sysstat consistent 
WHERE physical.name = ’physical reads’
AND blockgets.name = ’db block gets’
AND consistent.name = ’consistent gets’;


Sessions waiting for I/O



SELECT s.sql_address, s.sql_hash_value
FROM V$SESSION s, V$SESSION_WAIT w
WHERE w.event LIKE 'db file%read'
AND w.sid = s.sid ;



Object being accessed

SELECT segment_owner, segment_name
FROM DBA_EXTENTS
WHERE file_id = &p1
AND &p2 between block_id AND block_id + blocks - 1 ;

B-Tree Indexes

These are the standard index type, and they are excellent for primary key and highly-selective indexes. Used as concatenated indexes, B-tree indexes can be used to retrieve data sorted by the index columns.

Bitmap Indexes

These are suitable for low cardinality data. Through compression techniques, they can generate a large number of rowids with minimal I/O. Combining bitmap indexes on non-selective columns allows efficient AND and OR operations with a great number of rowids with minimal I/O. Bitmap indexes are particularly efficient in queries with COUNT(), because the query can be satisfied within the index.

Function-based Indexes

These indexes allow access through a B-tree on a value derived from a function on the base data. Function-based indexes have some limitations with regards to the use of nulls, and they require that you have the cost-based optimizer enabled.

Function-based indexes are particularly useful when querying on composite columns to produce a derived result or to overcome limitations in the way data is stored in the database. An example of this is querying for line items in an order exceeding a certain value derived from (sales price - discount) x quantity, where these were columns in the table. Another example is to apply the UPPER function to the data to allow case-insensitive searches.

Partitioned Indexes

Partitioning a global index allows partition pruning to take place within an index access, which results in reduced I/Os. By definition of good range or list partitioning, fast index scans of the correct index partitions can result in very fast query times.

Reverse Key Indexes

These are designed to eliminate index hot spots on insert applications. These indexes are excellent for insert performance, but they are limited in that they cannot be used for index range scans.

Default temporary tablespace for a databaase


SELECT * FROM DATABASE_PROPERTIES 
where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

select * from v$tempfile
select * from dba_temp_files

check free space in temp tablespace


select TABLESPACE_NAME, BYTES_USED, BYTES_FREE
from V$TEMP_SPACE_HEADER;




CREATE TEMPORARY TABLESPACE temp         
TEMPFILE '/oradata/mytemp_01.tmp' SIZE 20M        
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; - must be a multiple of sort_area_sixe


ALTER TABLESPACE temp ADD TEMPFILE '/oradata/temp03.dbf' SIZE 100M;


CREATE USER scott DEFAULT TABLESPACE data TEMPORARY TABLESPACE temp;  
ALTER USER scott TEMPORARY TABLESPACE temp;

v$session_wait -
p1-file_id for the read call
p2 - Start block_id for the read call
p3 - Number of blocks in the read call


- sorts don't fit into memory and go to disk. 
- Parallel slaves are used for scanning data

- processing buffers faster than the I/O system



v$tempseg_usage - to find the sql statement generating the sort


v$sesstat - for session to determine the size of the sort




workarea_size_policy - manual - increase the sort_area_size



workarea_size_policy - auto - increase PGA_AGGREGATE_TARGET



Check the following V$SESSION_WAIT parameter columns:
 P1 - File_id for the write call
 P2 - Start block_id for the write call
 P3 - Number of blocks in the write call




enqueue


Check the following V$SESSION_WAIT parameter columns:
n P1 - Lock TYPE (or name) and MODE
n P2 - Resource identifier ID1 for the lock
n P3 - Resource identifier ID2 for the lock
Check the comparison with V$LOCK columns:
n V$LOCK.ID1 = P2
n V$LOCK.ID2 = P3



Performing the following SQL transformation of the P1 column results in the same
value displayed in V$LOCK.TYPE:
V$LOCK.TYPE = chr(bitand(P1,-16777216)/16777215)||
chr(bitand(P1,16711680)/65535)
To obtain the mode in which the enqueue is being requested, issue the following
statement:
request = mod(P1, 65536);

Hardware Components

The main hardware components are the following:

• CPU
• Memory
• I/O Subsystem
• Network -bandwidth (volume) and latency (speed).

Software Components

Most applications involve the following components:

• Managing the User Interface
• Implementing Business Logic
• Managing User Requests and Resource Allocation
• Managing Data and Transactions

All you data entry punks, here's why your computers are so slow.

What is the user response time requirement?

Consideration of the user type is important. If the user is an executive who requires accurate information to make split second decisions, then user response time cannot be compromised. Other types of users, such as users performing data entry activities, might not need such a high level of performance.

http://education.oracle.com/pls/web_prod-plq-dad/show_desc.redirect?dc=D11299

Course:Objectives:

• Design and configure an Oracle database with an emphasis on good performance
• Perform day-to-day monitoring on an operational database using Oracle Enterprise Manager or Statspack
• List the important steps in a tuning methodology
• Employ Oracle tools to diagnose performance problems
• Perform tuning tasks with Oracle Enterprise Manager
• Perform tuning tasks using the command line interface
• Reconfigure file structures for performance
• Identify and resolve I/O, data storage and database configuration problems
• Optimize sort operations
• Configure Oracle Shared Server
• Configure Resource Manager to control resource usage

Course Topics:

Overview of Oracle 9i Database Performance Tuning

• Tuning questions, phases, and goals
• Common performance problems
• Tuning during development
• Tuning steps for a production database
• Performance and availability trade-offs

Diagnostic and Tuning Tools

• Log and trace files
• Tuning with Oracle Enterprise Manager
• Statspack
• Statistics, troubleshooting data, and performance data in the data dictionary
• Database and self-built performance statistics collection
• Collecting performance statistics with assorted tools

Database Configuration and I/O Issues

• Distributing files across devices
• Tablespace considerations
• Full table scan performance
• Checkpoint performance
• Redo management and performance
• Analyzing I/O activity
• Analyzing checkpoint performance

Tuning the Shared Pool

• Shared pool components and latches
• Monitor and tune the library cache
• Manage SQL statements and PL/SQL blocks to improve library cache performance
• Monitor and tune the data dictionary cache
• Reserved pool and large pool management
• Using diagnostic tools to monitor and tune the shared pool

Tuning the Buffer Cache

• Buffer cache sizing parameters
• Buffer cache advisory
• Buffer cache tuning views and statistics
• Buffer caches for different purposes and non-standard block sizes
• Automatic segment space and free space management
• Multiple database writing processes
• Monitoring and tuning database buffer cache with available diagnostic tools

Dynamic Instance Resizing

• Dynamic allocation units in the SGA
• Resize SGA components
• Resizing the buffer cache and the shared pool

Sizing Other SGA Structures

• Manage redo log buffer
• Control redo operations
• Manage Java pool memory
• Managing the redo log buffer

Tuning the Oracle Shared Server

• Use and performance of shared server processes
• Shared servers and memory usage
• Monitor and troubleshoot shared server activity

Optimizing Sort Operations

• Automatic PGA memory management
• PGA sizing advisor
• Sorts and temporary segments
• Tuning, diagnostics, and guidelines for sorts
• Reduce sorting
• Configuring and monitoring sort space options
• Configuring automatic PGA memory management

Using Resource Manager

• Database Resource Manager concepts
• Administering the Database Resource Manager
• Define resource plans limits
• Assigning users to consumer groups
• Manual and automatic consumer group switching
• Examine resource plan information in the data dictionary

SQL Statement Tuning

• Optimizer modes
• Hints
• Plan stability and stored outlines
• SQL tuning diagnostic tools
• Interpret results from SQL tuning diagnostic tools

Managing Statistics

• Automatic segment-level statistics collection
• Dynamic sampling
• Table statistics, index statistics, and index tuning wizard
• Column statistics and histograms
• Estimated statistics and automatic statistics collection
• Optimizer cost model
• System statistics
• Copying statistics between databases

Using Oracle Blocks Efficiently

• Database storage components
• Extent management
• High water-mark
• Space management for tables
• Index management

Using Oracle Data Storage Structures Efficiently

• Data storage structures
• Selecting physical storage characteristics for performance
• Data access methods and structures

Application Tuning

• Role of the database administrator
• Table movement and redefinition
• Creating and managing indexes and index-organized tables
• Statistics collection
• Considerations for OLTP systems
• Considerations for decision support systems and data warehouses
• Considerations for hybrid systems

Using Materialized Views

• Create and refresh materialized views
• Nested materialized views
• Query rewrites
• The DBMS_MVIEW package

Monitoring and Detecting Lock Contention

• Locking mechanisms and management
• Data concurrency
• Enqueues and lock types
• Lock monitoring and resolution of conflicts
• Deadlocks
• Use diagnostic tools to monitor lock contention

Query V$LOCK to find the sessions holding the lock.

If there are enqueue waits, you can see these using the following statement:
SELECT * FROM V$LOCK WHERE request > 0:

To show only holders and waiters for locks being waited on, use the following:

SELECT DECODE(request,0,'Holder: ','Waiter: ')|| sid sess, id1, id2, lmode,
request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;

ST enqueue If the contended-for enqueue is the ST enqueue, then the problem is
     most likely to be dynamic space allocation. 

- use locally managed tablespace
- check to see if temporary tablespace uses temp files. if not switch to temp files


- Change the next extent to sizes of the growing object to be large enough to avoid constance spacea llocation.- Alter

Tuning the Operating System

Workshop Overview

• Workshop configuration
• Workshop procedure
• Workshop scenarios