优化Oracle数据库性能：索引与访问路径专解（第2版）

需积分: 10 22 浏览量更新于2024-07-20 收藏 6.17MB PDF 举报

《 Expert Oracle Indexing and Access Paths, 2nd Edition》是一本由Darl Kuhn、Sam R. Alapati和Bill Padfield合著的专业IT书籍，专为Oracle数据库管理员和性能优化者设计。该书是关于Oracle索引和访问路径的权威指南，特别关注如何在数据库中实现最高性能以及解决性能和可扩展性挑战。作为第二版，它反映了作者们对Oracle技术的深入理解和实践经验，随着Oracle系统的不断发展，书中内容将包含最新的最佳实践和优化策略。本书的核心知识点包括： 1. **Oracle索引基础**：介绍Oracle索引的基本概念、类型（如B树、位图、函数索引等）、创建和维护索引的最佳做法，以及索引结构对查询性能的影响。 2. **访问路径分析**：详细讲解Oracle内部如何选择和使用不同的访问路径（如全表扫描、范围扫描、索引扫描等），以及如何通过SQL语句执行计划来识别和优化访问路径。 3. **性能调优技巧**：提供实用的方法，如如何创建复合索引来减少I/O操作，如何利用统计信息提高查询效率，以及如何避免常见的性能陷阱。 4. **分区和分片**：讨论如何根据数据量和查询模式合理分区和分片，以提高大规模数据的查询速度和管理效率。 5. **实时监控和诊断**：涵盖如何使用Oracle提供的工具（如ExPLAIN PLAN、SQL Tuning Advisor等）进行性能监控和诊断，找出潜在问题并进行修复。 6. **高可用性和扩展性**：针对大型企业环境，讲解如何设计和实施索引策略以支持分布式系统、读写分离和负载均衡。 7. **最佳实践案例**：书中包含多个实际案例分析，展示了如何在不同场景下应用所学知识，使读者能够理解和应用到自己的项目中。 8. **技术更新与演变**：鉴于Oracle数据库版本的迭代，书中会提及新版本中的关键改进和对旧方法可能产生的影响，确保读者紧跟技术发展趋势。《Expert Oracle Indexing and Access Paths, 2nd Edition》是一本深入浅出的资源，不仅适合初级Oracle用户了解索引和访问路径的基础，也对高级用户在解决实际性能问题时提供宝贵的参考。无论是希望通过索引优化提升现有数据库性能，还是希望学习最新Oracle技术的数据库管理员，这本书都是不可或缺的参考资料。

xvii

About the Technical Reviewer

Michelle Malcher ( @malcherm ) is a well-known volunteer leader in

security and database communities. She is currently on the board of

directors for the FUEL Palo Alto Network User Group and has served

on the board of directors for the Independent Oracle User Group

(IOUG). As an Oracle ACE director, she has had the opportunity to

present around the world at user group conferences. She continues

to participate on the conference committee for the IOUG. Her

technical expertise on topics from database to cybersecurity, as well

as her senior-level contributions as a speaker and author, have aided

many corporations across architecture and risk assessment,

purchasing and installation, and ongoing systems oversight. These

corporations include Wells Fargo & Company, where Michelle is

currently team lead for Enterprise Database Security.

xix

Acknowledgments

Thanks to Jonathan Gennick, Jill Balzano, and the Apress staff; it takes a quality team to produce a quality

book. Thanks also to Michelle Malcher who made numerous suggestions and modifications to improve

the quality of this book. Also thanks to the many people I’ve learned from over the years: Dave Jennings,

Scott Schulze, Andy Brown, Mark Lutze, Nitin Mittal, Venkatesh Ranganathan, Valerie Eipper, Mike Tanaka,

Simon Ip, Laurie Bourgeois, Scott Grover, John Finocchiaro, Kevin O’Grady, Jack Chung, Akshay Trivedi,

Suresh Raju, Mosharof Hossain, Brent Davis, Evangelia “Valia” Antonatou, Peter Compel, Petr Fojtik,

Tim Spangler, Jan Pewner, Michael Rohrbacher, Mingyan Fan, Ashwin Rao, Chandupavan Panditi,

Naveen Shankaranarayana, Dustin Garvey, Sampanna Salunke, Rahul Khandelwal, Siraj Sherriff,

Gaurav Mehta, Janet Bacon, Naomi Kuhn, Peter Schow, Todd Sherman, Patrick David, Carson Vowles,

Aaron Isom, Tim Gorman, Tom Kyte, Jaden Jackson, Brad Stucki, Scott Lovell, Terri Cannon, and Jim Stark.

—Darl Kuhn

I wish to acknowledge the great help provided by Jill Balzano, the coordinating editor, in seeing this book

through all the stages without a problem. I appreciate Jill’s patience and good cheer, to say nothing about her

efficiency in keeping track of all the changes in a book co-written by three authors.

As with all of my other books, I wish to thank the great help and support (and love) from my family:

Valerie, Nina, and Nicholas, as well as Shannon, Keith, Shawn, and Dale. I wish to acknowledge the great

debt to my other family as well: my mother, Swarna Kumari; my brothers, Hari and Siva Sankara Prasad; my

sisters-in-law, Aruna and Vanaja; and my nieces and nephews, Ashwin, Teja, Appu, and Soumya.

—Sam R. Alapati

As always, thanks to Apress for the opportunity to contribute to their great library of publications. Thanks

to Jonathan Gennick and Jill Balzano for their input, support, and patience. Thanks to my great co-authors,

Darl and Sam.

Doing this helps me reflect on all the help I’ve had in my career. This includes, but is not limited to the

following individuals. To some of my managers over the years—thanks to Bob Ranney, Beth Bowen, Larry

Wyzgala, John Zlamal, Linda Scheldrup, Amy Neff, Maureen Frazzini, Billie Ortega, and Chad Bower. Thanks

to my current team, which includes Dave Carter, Sandy Hass, Atul Shirke, Kevin Tomimatsu, John Townley,

Paul Watkins, Sunkarsh Guduthur, Rajendra Mishra, Ulagammal Kaliyappan, and Gopinath Kanagal.

Over the years, I’ve learned an awful lot from countless people. Some of the people that have helped

me along the way include Mark Nold, Mick McMahon, Sandra Montijo, Roby Sherman, Adric Norris, Brent

Wagner, Tony Arlt, Jerry Sanderson, Mike Hammontre, Pat Cain, Gary Whiting, Ron Fullmer, Becky Enter,

John Weber, Dianna Belangee, Randy Rezac, John Chatto, Pankaj Guleria, Avanish Gupta, Scott Bunker, Paul

Mayes, Rick Barry, Sue Wagner, Glenn Balanoff, Linda Lee Burau, Deborah Lieou-McCall, Kristi Sargent,

George Huner, Pad Kail, Curtis Gay, Ross Bartholomay, Carol Rosenow, Sheryl Gross, John Piel, Kim Lake,

Rob Grote, Zane Warton, Pat Wuller, Wil Minor, Jim Barclay, Jason Hermstad, Shari Plantz-Masters, Denise

Duncan, Bob Mason, Rob Coates, Debbie Chartier, Blair Christensen, Meera Ganesan, Kedar Panda, Kirsten

Franz, Valarie Martinez, Mallick Robyeat, Liping Zhang, Satish Pasupathy, John Lungerhausen, Richard

Rawles, Cindy Powell, Mary Ann Wooten, Deb Kingsley, Paula Obering, and Deb Sutton.

—Bill Padfield

D. Kuhn et al., Expert Oracle Indexing and Access Paths, DOI 10.1007/978-1-4842-1984-3_1

CHAPTER 1

Introduction to Oracle Indexes

An index is a combination of a value and an associated location that allows you to quickly locate an object.

If you think about it for a couple of seconds, you probably often use index structures to navigate throughout

the day, such as:

• Browsing a catalog menu to efficiently find a desired item

• Accessing a bookmark to retrieve a web page

• Glancing at the calendar to determine a date

Similar to the way you use indexes in everyday life, a database index primarily exists to enable fast

access to one or more rows of information stored in a database. In Oracle, an index stores a table column

value along with the table row’s physical address ( ROWID ). Each ROWID contains details on the location of a

table row (e.g., the data file number, block number, and row location).

With the column value and ROWID stored in the index, Oracle can efficiently retrieve table data with a

minimum of disk reads. In this way, indexes function like a shortcut to the table data.

■ Note In addition to improving performance, Oracle uses indexes to help enforce enabled primary key and

unique key constraints. Also, Oracle can better manage certain table-locking scenarios when indexes are placed

on foreign key columns.

Although it’s possible to build a database application devoid of indexes, without them you’re almost

guaranteeing poor performance. Indexes allow for excellent scalability, even with very large data sets.

So if indexes are so important to database performance, why not place them on all tables and column

combinations? The answer is short: indexes are not free. They consume disk space and system resources.

As column values are modified, any corresponding indexes must also be updated. In this way, indexes use

storage, I/O, CPU, and memory resources. A poor choice of indexes leads to wasted disk usage and excessive

consumption of system resources. This results in a decrease in database performance.

For these reasons, when you design and build an Oracle database application, expert consideration

must be given to your indexing strategy. As an application architect, you must understand the physical

properties of an index, what types of indexes are available, and strategies for choosing which table

and column combinations to index. A correct indexing methodology is central to achieving maximum

performance for your database.

Electronic supplementary material The online version of this chapter (doi:10.1007/978-1-4842-1984-3_1)

contains supplementary material, which is available to authorized users.

CHAPTER 1 ■ INTRODUCTION TO ORACLE INDEXES

Here’s a sample of what the data looks like in this table:

select * from cust;

CUST_ID LAST_NAME FIRST_NAME

---------- ------------------- -----------

1 STARK JIM

2 BAGAQWTD WWQLVECQ

3 FSNVCPCBH STBXLV

... <lots of rows here>

100000 DHFKB STNFDSI

Now you run daily reports against the CUST table and notice that performance has progressively

decreased when issuing queries like the following:

select cust_id, last_name, first_name

from cust

where last_name = 'STARK';

When there was minimal data in the table, the prior query returned in subseconds. Now, with

thousands of rows and growing, the prior query is taking longer and longer. What’s going on here?

When a SQL SELECT statement executes, the Oracle query optimizer quickly calculates a step-by-step

execution plan detailing how it will retrieve column values specified in the query. In calculating the plan, the

optimizer determines which tables and indexes will be used to retrieve data.

When no index exists, the table itself is the only access path available to satisfy the results of the query.

In this scenario, Oracle has no choice but to inspect every row within every used block in the table (this is

known as a full table scan ) to see if there are rows with the last name of STARK. As more data is inserted into

this table, the query takes longer. The cost of this query (as a measure of CPU, memory, and I/O resources

consumed) is proportional to the number of table blocks accessed.

When generating an execution plan, the query optimizer relies on table and index statistics to generate

an optimal execution plan. Statistics in this sense means information about the table and associated indexes,

such as number of rows, number of blocks, number of distinct values, and so on.

To illustrate an execution plan in action, first generate fresh statistics for the table:

exec dbms_stats.gather_table_stats( user, 'CUST' );

Next, generate an execution plan for the query:

explain plan for

select cust_id, last_name, first_name

from cust

where last_name = 'STARK';

select * from table(dbms_xplan.display(null,null,'BASIC +COST'));

The following execution plan output shows the cost associated with the full table scan of the table:

-----------------------------------------------

-----------------------------------------------

| 0 | SELECT STATEMENT | | 88 (2)|

| 1 | TABLE ACCESS FULL| CUST | 88 (2)|

-----------------------------------------------

剩余266页未读，继续阅读

niezhisheng

粉丝: 1
资源: 5

优化Oracle数据库性能：索引与访问路径专解（第2版）

优化Oracle数据库性能：索引与访问路径策略

Oracle Spatial 11g Developer's Guide: Indexing & Spatial App Dev

"索引与散列：数据库系统中的优化技术

Expert.Oracle.Indexing.and.Access.Paths.2nd.epub

Expert Performance Indexing in SQL Server 2019, 3rd Edition.pdf

Managing gigabytes - Compressing and indexing documents and images (2nd)

Managing gigabytes - Compressing and indexing documents and images (2nd) part4

Managing gigabytes - Compressing and indexing documents and images (2nd) part6

Managing gigabytes - Compressing and indexing documents and images (2nd) part3

Managing gigabytes - Compressing and indexing documents and images (2nd) part5

最新资源