Oracle成本基础：解析Jonathan Lewis的著作

5星 · 超过95%的资源需积分: 34 158 浏览量更新于2024-07-29 收藏 10.48MB PDF 举报

"Cost-Based Oracle Fundamentals.pdf" 是一本由 Jonathan Lewis 编著的书籍，主要探讨了Oracle数据库中的成本基础优化器（Cost-Based Optimizer, CBO）的基础知识。这本书详细介绍了CBO的工作原理、如何计算成本以及如何根据这些成本来决定最优执行计划。在Oracle数据库系统中，CBO是其查询优化器的一种策略，它根据预计的执行成本来选择最佳的SQL查询执行路径。CBO的核心思想是将每一种可能的执行计划转化为一个成本值，然后选择成本最低的计划来执行，以提高查询效率和整体系统性能。书中可能会涵盖以下关键知识点： 1. **CBO的基本概念**：解释了CBO是如何评估查询的不同执行路径，包括表扫描、索引访问、连接操作等，并如何通过统计信息来估算这些操作的成本。 2. **统计信息**：CBO依赖于准确的表和列统计信息来计算成本，如行数、块数、分布模式等。这部分会讨论如何收集和维护这些统计信息，以及它们对优化器决策的重要性。 3. **执行计划**：介绍CBO如何生成和选择执行计划，包括访问路径的选择、排序和并行处理的考虑。 4. **成本模型**：深入解析CBO的成本模型，包括CPU成本、I/O成本和网络成本等，以及这些因素如何影响最终的执行计划选择。 5. **优化器模式**：CBO可以工作在不同的模式下，比如规则基础优化器（Rule-Based Optimizer, RBO）和CBO之间的切换，以及何时选择哪种模式更合适。 6. **SQL提示和调优**：讨论如何通过使用SQL提示来影响CBO的行为，以及在特定场景下的查询优化技巧。 7. **案例分析**：书中可能包含实际的查询优化案例，展示了CBO在面对复杂查询时如何运作，并提供了解决性能问题的策略。 8. **技术审阅者**：书中有两位技术审阅者——Christian Antognini 和 Wolfgang Breitling，他们都是Oracle领域的专家，这增加了书中的内容的专业性和可靠性。此书对于理解Oracle数据库的性能优化，特别是对那些负责数据库管理和性能调优的DBA或开发人员来说，是一份非常有价值的参考资料。通过学习，读者可以掌握如何有效地利用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页未读，继续阅读

WTK870424

粉丝: 71

Oracle成本基础：解析Jonathan Lewis的著作

Cost-Based.Oracle.Fundamentals

cost-based_oracle_fundamentals

Cost.Based.Oracle.Fundamentals

Apress.Cost.Based.Oracle.Fundamentals

Cost-Based Oracle Fundamentals

高效ORACLE_之_索引(完整).pdf

CostBasedOracleFundamentalsFreePdfBook.pdf 英文原版

cost-base oracle foundamentals

Oracle参考书籍

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

最新资源