Oracle成本基础优化基础

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

"Apress.Cost.Based.Oracle.Fundamentals.Nov.2005" 是一本由 Jonathan Lewis 撰写的关于 Cost-Based Oracle 基础知识的书籍，出版于2005年11月，主要涵盖Oracle数据库的基于成本的查询优化。在Oracle数据库系统中，Cost-Based Oracle Fundamentals是理解数据库性能优化的关键概念。这本书深入讲解了如何Oracle数据库通过成本估算来选择最优的执行计划，以提高SQL查询的效率。基于成本的优化器（Cost-Based Optimizer，CBO）是Oracle数据库中的核心组件，它根据统计信息和系统资源消耗预测来决定最佳的查询执行路径。书中可能涵盖了以下几个重要的知识点： 1. **基于成本的优化器工作原理**：CBO评估每个可能的执行计划，估算每个计划的成本，包括I/O操作、CPU使用、排序和连接等操作的开销，然后选择成本最低的执行计划。 2. **统计信息的重要性**：CBO依赖于准确的表和列的统计信息来做出决策，包括行数、块数、唯一值数量等。因此，了解如何收集和维护这些统计信息对于优化查询至关重要。 3. **SQL查询优化**：书中可能会介绍如何通过调整查询结构、使用索引、物化视图、连接方法等技术来改进查询性能。 4. **绑定变量和执行计划稳定性**：探讨了绑定变量如何影响CBO的选择，以及如何确保相同查询的执行计划一致性。 5. **SQL执行计划分析**：学习如何使用EXPLAIN PLAN和其他工具来理解和解释Oracle生成的执行计划，以及如何根据这些信息进行性能调优。 6. **资源管理**：包括调度器（DBMS_SCHEDULER）、资源队列和负载平衡，这些都与优化器如何分配系统资源有关。 7. **性能监控与诊断**：介绍监控工具如V$视图，以及如何分析AWR（Automatic Workload Repository）和ASH（Active Session History）报告来识别性能问题。 8. **Oracle的最新特性**：考虑到书籍出版时间，可能还涵盖了当时的一些新特性，如10g或11g版本引入的优化改进。 9. **案例研究和最佳实践**：通过实际示例和建议，帮助读者将理论知识应用于实际工作场景。这本书的技术审查者包括Christian Antognini和Wolfgang Breitling，他们都是Oracle领域的知名专家，这保证了书中的内容具有高度的专业性和实用性。对于想要深入了解Oracle数据库性能优化的SQL和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页未读，继续阅读

wyx83coria

粉丝: 0
资源: 4

Oracle成本基础优化基础

《Apress.Pro.Expression.Blend.4》：精通微软WPF与Silverlight开发的全面指南

精通Web前端：Apress.Pro JavaScript实战指南

《Expert Oracle Database 11g Administration》：2009年权威指南

Apress.Cost.Based.Oracle.Fundamentals

Apress.Interactive.Csharp.Fundamentals.Core.Concepts.and.Patterns.2018

Apress.Python.Algorithms.Nov.2010.pdf

Apress.CRM.Fundamentals

Apress.Expert.JavaScript.Nov.2013

Apress.Beginning.Blender.Nov.2010

Apress.Foundations.of.WPF.Nov.2006.pdf

最新资源