Packt+ | Advance your knowledge in tech

You're reading from Oracle Database 11gR2 Performance Tuning Cookbook Shifting your Oracle Database into top gear takes a lot of know-how and fine-tuning ability. The 80+ recipes in this Cookbook will give you those skills along with the ability to troubleshoot if things starts running slowly.

Product type Paperback

Published in Jan 2012

Publisher Packt

ISBN-13 9781849682602

Length 542 pages

Edition 1st Edition

Languages

Java

Tools

Oracle Database

Concepts

Database Administration

Author (1):

Ciro Fiorillo

View More author details

Table of Contents (21) Chapters

Oracle Database 11gR2 Performance Tuning Cookbook

Credits

About the Author

Acknowledgement

About the Reviewers

www.PacktPub.com

Preface

1. Starting with Performance Tuning FREE CHAPTER

2. Optimizing Application Design

3. Optimizing Storage Structures

4. Optimizing SQL Code

5. Optimizing Sort Operations

6. Optimizing PL/SQL Code

7. Improving the Oracle Optimizer

8. Other Optimizations

9. Tuning Memory

10. Tuning I/O

11. Tuning Contention

Dynamic Performance Views

A Summary of Oracle Packages Used for Performance Tuning

Index

Using asynchronous I/O

In this recipe we will see how to use asynchronous I/O to obtain better performance from our I/O subsystem.

How to do it...

The following steps will describe the use of asynchronous I/O:

Connect to the database as SYSDBA:
```
CONNECT / AS SYSDBA
```
Verify whether asynchronous I/O is enabled:
```
SHOW PARAMETER FILESYSTEMIO_OPTIONS
```

Enable asynchronous I/O, if it is not enabled:

ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;

Shut down the database instance:
```
SHUTDOWN IMMEDIATE
```
Start and open the database:
```
STARTUP OPEN
```
Verify the change in system configuration:
```
SHOW PARAMETER FILESYSTEMIO_OPTIONS
```

How it works...

The Oracle database may use synchronous or asynchronous I/O calls. With synchronous I/O, when an I/O request is submitted to the Operating System, the write process will block until the operation is completed.

Using asynchronous I/O, while the I/O request is still executing, the calling process continues its work without blocking. This is the reason why asynchronous...