BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Oracle Tuning Factors that Influence Disk I/O

Oracle Tips by Burleson Consulting

As you know, one of the primary goals of all Oracle tuning activities is to reduce disk I/O. We will be discussing these techniques in Chapter 9, but we need to mention them here so you will understand how the instance parameters can affect disk I/O. There are three areas where the settings for Oracle have a direct impact on the amount of disk I/O. The settings for the Oracle instance (init.ora) impact disk I/O, the settings for Oracle objects (tables and indexes) affect disk I/O, and the execution plans for Oracle SQL also have a direct impact on disk I/O.

1.      Oracle instanceThere are several database instance parameters that have a direct impact on lowering physical disk I/O:

  • Large db_block_sizeThe block size of the database has a dramatic effect on the amount of disk I/O. As a general rule, the larger the block size, the less the disk I/O.

  • Large db_cache_sizeThe greater the number of data buffers, the smaller the chance that Oracle will need to perform disk I/O.

  • Utilize multiple blocksizes The largest supported blocksizes in Oracle9i for your platform should be reserved for index tablespaces, and smaller blocksizes help the speed of bitmap index DML.

  • Multiple database writers (DBWR) processes Multiple database writer background processes allow for more efficient writing to the datafiles.

  • Large sort_area_sizeThe greater the sort_area_size in RAM, the less disk sorting will take place in the TEMP tablespace.

  • Large online redo logs The larger the online redo logs, the less frequent the log switches.

2.      Oracle objectsInside the database, settings for tables and indexes can reduce physical disk I/O

  •  Low pctusedThe smaller the value of pctused, the less I/O will occur on subsequent SQL inserts.

  • Low pctfreeIf pctfree is set to allow all rows to expand without fragmenting, the less disk I/O will occur on subsequent SQL selects.

  • Reorganizing tables to cluster rows with indexes If tables are placed in the same physical order as the most frequently used index, disk I/O will drop dramatically. This can be done with single table clusters or by re-sequencing the table rows with CTAS.

3.      Oracle SQLWithin SQL statements, there are many techniques to reduce physical disk I/O:

  •  Preventing unnecessary full table scans using indexes or hints This is the most important way to reduce disk I/O because many SQL queries can use indexes to reduce disk I/O.

  • Using bitmapped indexes The use of bitmapped indexes will reduce full table scans on tables with low-cardinality columns, thereby reducing disk I/O. For highly-updated bitmap index columns, using smaller blocksizes with bitmaps reduces the costs for DML.

  • Applying SQL hints Many hints make SQL run faster and with less disk I/O. For example, the USE_HASH hint will reduce disk I/O by performing joins within SGA memory, reducing calls for database blocks.

Now that we have reviewed some of the things that we can do within Oracle to reduce disk I/O, let's take a close look at the nature of disk I/O and examine the internal workings of the disk I/O subsystem.

Oracle Internals and Disk I/O

From an Oracle perspective, most databases can be characterized as either online transaction processing (OLTP) systems or decision support (DSS) systems. The patterns of I/O vary greatly between a data warehouse and decision support type of application and one that processes online transactions. While OLTP may appear random, upon closer inspection, we will see clear areas of impact to the Oracle database, and understand methods to alleviate I/O contention.

Oracle File Organization Techniques

Regardless of whether or not you use RAID, it is very important for the Oracle Remote DBA to identify all high-volume and high-activity tables and move them into isolated tablespaces. By keeping the high-volume tables in a separate tablespace, the Oracle administrator can manipulate the datafiles in the tablespace to minimize I/O contention on the disk, as shown in Figure 8-1.

Figure 8-39: Segregating Oracle tables into separate tablespaces

Without segregation, some tablespaces may have hundreds of tables and indexes, and it is impossible to tell which objects are experiencing the high I/O. The stats$filestatxs table will provide details about read and write I/O at the file level, but it is often difficult to tell which tables are causing the high I/O because a file may contain many objects.

With segregation, the Remote DBA can generate STATSPACK file I/O reports from the stats$filestatxs table that show all read and write I/O for the datafile. If the Oracle datafile maps to only one table, we can easily see the total I/O for the table. Later in this chapter we will see a STATSPACK script called rpt_io_pct.sql that serves this purpose.

Because of the high-speed transaction-oriented nature of most Oracle applications, we generally see high activity in specific areas within each tablespace. While this chapter describes the basics of I/O load balancing for Oracle datafiles and tablespaces, the settings for the individual tables will also have a profound influence on the performance of the entire database. We will look at disk I/O within Oracle and examine several areas:

  • Transient disk hot spots

  • Disk I/O patterns within highly active tables

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.


If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter