Oracle High Performance Tuning for 9i and 10g

---

Gavin Powell

Digital Press is an imprint of Elsevier
200 Wheeler Road, Burlington, MA 01803, USA
Linacre House, Jordan Hill, Oxford OX2 8DP, UK

Copyright © 2004, Elsevier Inc. All rights reserved.

No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher.

Permissions may be sought directly from Elsevier's Science & Technology Rights Department in Oxford, UK: phone: [phone], fax: [phone], e-mail: [e-mail]. You may also complete your request on-line via the Elsevier homepage (http://elsevier.com), by selecting "Customer Support" and then "Obtaining Permissions."

Recognizing the importance of preserving what has been written, Elsevier prints its books on acid-free paper whenever possible.

Library of Congress Cataloging-in-Publication Data

British Library Cataloguing-in-Publication Data

A catalogue record for this book is available from the British Library.

ISBN: 1-55558-305-9

For information on all Digital Press publications visit our website at www.digitalpress.com and www.bh.com/digitalpress

03  04  05  06  07  08  10  9  8  7  6  5  4  3  2  1

There are three parts to tuning an Oracle database: data modeling, SQL code tuning followed by physical and configuration tuning.

A data model contains tables and relationships between tables. Tuning a data model involves Normalization and Denormalization. Different approaches are required depending on the application, such as for an Internet OLTP or data warehouse database. Inappropriate database design can make SQL code impossible to tune. Poor data modeling can have a most profound effect on data base performance since all SQL code is constructed from the data model. Changes to the data model are often the most difficult and expensive changes to implement.

Poorly written SQL code is often a culprit of performance prob lems and is expensive to rectify. However, tuning of SQL code is gen erally cheaper than changing the data model. SQL code tends to be contained inside independent blocks within applications or stored procedures.

Physical database tuning involves hardware resource usage, net working, and various other facets of Oracle such as configuration and file distribution. Inappropriate physical and configuration set tings are often a culprit of poor performance where Oracle is installed with defaults, and never altered by an expert.

---