Oracle成本基础：Jonathan Lewis深度解析

需积分: 34 135 浏览量更新于2024-07-26 收藏 10.48MB PDF 举报

"Cost Based Oracle Fundamentals - 由APRESS出版社出版，作者Thomas Kyte，深入探讨Oracle的成本基础优化器（CBO）" 《Cost Based Oracle Fundamentals》是Oracle数据库领域的一本经典著作，由知名专家Jonathan Lewis撰写。本书专注于解释和理解Oracle数据库中的成本基础优化器（CBO），这是Oracle在查询优化过程中的核心组件。CBO是一种自动选择最佳执行计划的策略，它通过估算不同执行路径的成本来决定如何执行SQL语句。在Oracle数据库中，CBO扮演着至关重要的角色，因为它直接影响到查询性能和系统整体效率。书中详细介绍了CBO的工作原理，包括如何分析和估算查询的代价，如表扫描、索引访问、排序、连接操作等的成本。读者将了解到如何评估和调整统计信息，以帮助CBO做出更准确的决策。此外，还涉及了优化器如何处理并行执行、复杂查询结构以及如何应对性能问题。内容中可能涵盖以下几个关键知识点： 1. **CBO的基本概念**：理解CBO是如何基于成本模型选择执行计划的，以及这个模型中的主要因素，如CPU时间、I/O操作和内存使用。 2. **统计信息收集**：学习如何使用DBMS_STATS包来收集和维护表、索引的统计信息，这些信息对CBO估算成本至关重要。 3. **执行计划分析**：如何阅读和理解执行计划，以及如何通过EXPLAIN PLAN或V$SQL_PLAN视图来查看和分析CBO生成的执行计划。 4. **优化器模式和参数**：探讨不同的优化器模式（如RULE和COST），以及影响CBO行为的参数设置，如optimizer_mode。 5. **索引策略**：深入理解索引对CBO的影响，包括何时使用索引，何时避免索引，以及不同类型的索引（如B树、位图、函数索引）的优缺点。 6. **并行执行**：CBO如何处理并行查询，包括并行度的选择和并行执行的风险与收益。 7. **查询重写和优化提示**：了解如何使用优化提示（hints）来影响CBO的行为，以及何时应该避免使用提示。 8. **性能调优技巧**：学习如何识别和解决由CBO引起的性能问题，包括如何进行性能监控和诊断。 9. **案例研究**：书中可能会包含实际的案例研究，展示如何应用所学理论来解决实际的性能问题。这本书对于数据库管理员（DBA）、开发人员以及任何需要理解Oracle查询优化机制的人来说都是宝贵的资源。通过深入阅读和实践，读者能够提升对CBO的理解，从而更好地优化和管理他们的Oracle数据库系统。

About the Author

■JONATHAN LEWIS is a qualified teacher with a mathematics degree from

Oxford University. Although his interest in computers came to light at

the tender age of about 12—in the days when high-technology meant

you used a keyboard, rather than a knitting needle to punch holes in

cards—it wasn’t until he was four years out of university that he moved

into computing professionally. Apart from an initial year as an incompetent

salesman, he has been self-employed his entire career in the computer

industry.

His initiation into Oracle was on version 5.1 running on a PC, which he used to design and

build a risk-management system for the crude-trading floor of one of the major oil companies.

He had written the first version of the system using a PC program called dBase III—which did

use tables and indexes and made some claims to being a relational database management

system. Suddenly he found out what a proper Relational Database Management System ought

to be able to do.

Since that day of revelation, Jonathan has focused exclusively on the use and abuse of the

Oracle RDBMS. After three or four years of contract work helping to build large systems, he

decided to move from the “contractor” market to the “consultant” market, and now shares his

time evenly between short-term consultancy work, running seminars, and “research.”

At the time of writing, he is one of the directors of the UK Oracle User Group (www.ukoug.com),

and a regular contributor to their quarterly magazine and presenter at their Special Interest

Groups (particularly the RDBMS and UNIX groups). Whenever the opportunity arises, he tries

to find time for speaking to user groups outside the UK, sometimes as short evening events at

the end of a day’s consultancy work. He also maintains a web site (www.jlcomp.demon.co.uk) of

articles and notes on things that you can do with the Oracle RDBMS.

Jonathan has been married to Diana (currently a primary school teacher after many years

of being an accountant) for nearly 20 years, and has two children: Anna (actress and trumpet

player) and Simon (rugby and saxophone player). Given the choice between working on a

Saturday and supporting a rugby match or school concert—it’s the school event that comes first.

Lewis_636-6Front.fm Page xv Thursday, October 6, 2005 5:36 AM

xvii

About the Technical

Reviewers

■CHRISTIAN ANTOGNINI has focused on understanding how the Oracle database engine works

since 1995. His main interests range from logical and physical database design, the integration of

databases with Java applications, to the cost based optimizer, and basically everything else

related to performance management and tuning. He is currently working as a senior consultant

and trainer at Trivadis AG (www.trivadis.com) in Zürich, Switzerland. If he is not helping one of

his customers to get the most out of Oracle, he is somewhere lecturing on optimization or new

Oracle database features for developers.

Christian lives in Ticino, Switzerland, with his wife, Michelle, and their two children, Sofia

and Elia. He spends a great deal of his spare time with his wonderful family and, whenever

possible, reading books, enjoying a good movie, or riding one of his BMX bikes.

■WOLFGANG BREITLING was born in Stuttgart, Germany, and studied mathematics, physics, and

computer sciences at the University of Stuttgart. After graduating, he joined the QA department

of IBM Germany’s development laboratory in Böblingen. There he became one of a team of two

to develop an operating system to test the hardware of the /370 model machines developed in

the Böblingen lab.

Wolfgang’s first direct foray into the field of performance evaluation/tuning was the devel-

opment of a program to test the speed of individual operating codes of the /370 architecture.

After IBM Germany, he worked as a systems programmer on IBM’s hierarchical databases DL/1

and IMS in Switzerland before emigrating to his current home in Calgary, Canada. Following

several years as systems programmer for IMS and later DB2 databases on IBM mainframes, he

got on the project to implement Peoplesoft on Oracle.

In 1996, he became an independent consultant specializing in administering and tuning

Peoplesoft on Oracle. In that capacity, Wolfgang has been engaged in several Peoplesoft instal-

lation and upgrade projects. The particular challenges in tuning Peoplesoft, with often no access to

the SQL, motivated him to explore Oracle’s cost based optimizer in an effort to better understand

how it works and use that knowledge in tuning. He has shared the findings from this research

in papers and presentations at IOUG, UKOUG, local Oracle user groups, and other conferences

and newsgroups dedicated to Oracle performance topics.

Wolfgang has been married to his wife Beatrice for over 30 years and has two children,

Magnus and Leonie. When he is not trying to decipher the CBO, Wolfgang enjoys canoeing and

hiking in the Canadian prairies and Rocky Mountains.

Lewis_636-6Front.fm Page xvii Thursday, October 6, 2005 5:36 AM

xix

Acknowledgments

First and foremost, I have to thank my wife and children for putting up with my distant expres-

sions, long silences, and my habit of being physically present while mentally absent. They have

been very patient with my mantra to “give me just five more minutes.”

I would like to thank those Oracle experts and adventurers around the world who (know-

ingly or unknowingly) have extended my knowledge of the database. In particular, though not

exclusively, I owe thanks to (in alphabetical order) Steve Adams, Wolfgang Breitling, Julian Dyke,

K. Gopalakrishnan, Stephan Haisley, Anjo Kolk, Tom Kyte, James Morle, and Richmond Shee.

Each of them has, at various times, supplied me with insights that have allowed me to open up

whole new areas of investigation and understanding. Insights are so much more important

than answers.

Christian Antognini and Wolfgang Breitling merit special thanks for reading the draft chap-

ters, and for making a number of suggestions to improve the quality of the book. Any outstanding

errors are, of course, all my own work.

There are many more individuals who deserve to be named as sources of ideas and infor-

mation, but the more I name, the greater the injustice to those whose names I let slip. So let me

cover them all by thanking those who post on the comp.databases.oracle newsgroups, those

who post on the Oracle-L mailing list, and those who post on Oracle Corporation’s MetaLink

forums. And, of course, there are the members of the Oak Table Network who can be relied

upon to provide entertaining and stimulating discussions.

I would also like to mention the audiences who attend my seminar Optimizing Oracle:

Performance by Design. At the end of each of these three-day events, I always have several new

questions to answer, points to clarify, and strange cases to investigate.

The most important part of what I do is to deal with production database systems, so I also

want to thank the many companies who have decided to call me in for a few days to examine

their problems, validate their designs, or just give them some guidelines. Without continuous

input and challenge from real users, real requirements, and real applications, it would be

impossible to write a book that could be of practical benefit to its readers.

Finally, I owe a big thank you to the Apress editorial and production teams who worked

incredibly hard to beat a tough deadline: Tony Davis, Ami Knox, Katie Stence, and Grace Wong.

Lewis_636-6Front.fm Page xix Thursday, October 6, 2005 5:36 AM

剩余536页未读，继续阅读

yaserchen

粉丝: 16

Oracle成本基础：Jonathan Lewis深度解析

Oracle成本基础：Jonathan Lewis详解

Oracle成本基础：解析与优化

深入理解Cost-Based Oracle：Jonathan Lewis著作精华

Cost-Based Oracle Fundamentals

cost-based_oracle_fundamentals

Cost.Based.Oracle.Fundamentals

Apress.Cost.Based.Oracle.Fundamentals

cost-base oracle foundamentals

Oracle参考书籍

Oracle成本基础调优指南

最新资源