Oracle SQL调优实战指南

需积分: 15 176 浏览量更新于2024-07-30 收藏 981KB PDF 举报

"《Oracle SQL调优袖珍指南》由Mark Gurry撰写，由O'Reilly出版社出版，出版日期为2002年1月，共108页。本书涵盖了Oracle数据库SQL调优的各种主题，包括SQL优化器、基于规则的优化器问题与解决方案、基于成本的优化器问题与解决方案、以及如何使用SQL提示、DBMS_STATS管理统计信息和使用大纲来实现一致的执行计划。" 在Oracle数据库环境中，SQL调优是提高系统性能的关键环节，它涉及到优化查询以减少处理时间、减少资源消耗，并提升整体系统响应速度。以下是书中的主要知识点： 1. **SQL优化器**：Oracle数据库有两种主要的查询优化策略：基于规则的优化器（RBO）和基于成本的优化器（CBO）。RBO根据预定义的规则选择执行计划，而CBO则基于统计信息和成本估算来决定最佳执行路径。 2. **基于规则的优化器问题与解决方案**：RBO有时可能导致不理想的执行计划，因为它可能忽视了实际的数据分布。解决方法包括切换到CBO或使用SQL提示来指导优化器。 3. **基于成本的优化器问题与解决方案**：CBO依赖于准确的统计信息，如果这些信息过时或不准确，可能会选择错误的执行计划。解决方案包括定期更新统计信息，使用DBMS_STATS包进行维护。 4. **共同问题与解决方案**：无论使用哪种优化器，都可能遇到如索引不当、全表扫描过多或并行执行策略不合适等问题。解决这些问题需要深入理解SQL语句的执行过程，可能需要调整索引策略、优化查询结构或调整并行度。 5. **SQL提示**：SQL提示可以强制优化器采取特定的执行策略，这对于调试和性能调整非常有用，但过度使用或误用可能引入新的性能问题。 6. **DBMS_STATS管理统计信息**：这个PL/SQL包用于收集和维护表和索引的统计信息，帮助CBO做出更好的决策。正确使用DBMS_STATS可以显著改善查询性能。 7. **使用大纲（Outlines）**：大纲是预定义的执行计划，可以确保特定SQL语句始终按照相同的逻辑执行，提供一致性，特别是在复杂或动态数据环境下的性能稳定。通过学习这些章节，数据库管理员和开发人员将能够识别和解决SQL性能问题，优化查询以适应不断变化的业务需求，同时确保数据库系统的高效运行。

Prior to the introduction of histograms in Oracle 7.3, The cost-based optimizer could not

distinguish grossly uneven key data spreads.

System resource usage

By default, the cost-based optimizer assumes that you are the only person accessing the

database. Oracle9i gives you the ability to store information about system resource usage,

and can make much better informed decisions based on workload (read up on the

DBMS_STATS.GATHER_SYSTEM_STATS package).

Current statistics are important

The cost-based optimizer can make poor execution plan choices when a table has been

analyzed but its indexes have not been, or when indexes have been analyzed but not the

tables.

You should not force the database to use the cost-based optimizer via inline hints when no

statistics are available for any table involved in the SQL.

Using old (obsolete) statistics can be more dangerous than estimating the statistics at

runtime, but keep in mind that changing statistics frequently can also blow up in your face,

particularly on a mission-critical system with lots of online users. Always back up your

statistics before you re-analyze by using DBMS_STATS.EXPORT_SCHEMA_STATS.

Analyzing large tables and their associated indexes with the COMPUTE option will take a

long, long time, requiring lots of CPU, I/O, and temporary tablespace resources. It is often

overkill. Analyzing with a consistent value, for example, estimate 3%, will usually allow the

cost-based optimizer to make optimal decisions

Combining the information provided by the selectivity rules with other database I/O information

allows the cost-based optimizer to calculate the cost of an execution plan.

1.2.2.4 EXPLAIN PLAN for the cost-based optimizer

Oracle provides information on the cost of query execution via the EXPLAIN PLAN facility.

EXPLAIN PLAN can be used to display the calculated execution cost(s) via some extensions to the

utility. In particular, the plan table's COST column returns a value that increases or decreases to

show the relative cost of a query. For example:

EXPLAIN PLAN FOR

SELECT count(*)

FROM winners, horses

WHERE winners.owner=horses.owner

This is totally false. Certain publications mentioned this some time ago, but Oracle now

assures us that this is definitely not true.

Hints can't be used with the rule-based optimizer

The large majority of hints can indeed be applied to SQL statements using the rule-based

optimizer.

SQL tuned for rule will run well in cost

If you are very lucky it may, but when you transfer to cost, expect a handful of SQL

statements that require tuning. However, there is not a single site that I have transferred and

been unable to tune.

SQL tuned for cost will run well in rule

This is highly unlikely, unless the code was written with knowledge of the rule-based

optimizer.

You can't run rule and cost together

You can run both together by setting the INIT.ORA parameter OPTIMIZER_MODE to

CHOOSE, and having some tables analyzed and others not. Be careful that you don't join

tables that are analyzed with tables that are not analyzed.

1.2.4 Which Optimizer to Use?

If you are currently using the rule-based optimizer, I strongly recommend that you transfer to the

cost-based optimizer. Here is a list of the reasons why:

• The time spent coding is reduced.

• Coders do not need to be aware of the rules.

• There are more features, and far more tuning tools, available for the cost-based optimizer.

• The chances of third-party packages performing well has been improved considerably.

Many third-party packages are written to run on DB2, Informix, and SQL*Server, as well as

on Oracle. The code has not been written to suit the rule-based optimizer; it has been written

in a generic fashion.

• End users can develop tuned code without having to learn a large set of optimizer rules.

• The cost-based optimizer has improved dramatically from one version of Oracle to the next.

Development of the rule-based optimizer is stalled.

• There is less risk from adding new indexes.

• There are many features that are available only with the cost-based optimizer. These

features include recognition of materialized views, star transformation, the use of function

indexes, and so on. The number of such features is huge, and as time goes on, the gap

between cost and rule will widen.

剩余89页未读，继续阅读

五柳-先生

粉丝: 251
资源: 167

Oracle SQL调优实战指南

自动SQL优化工具Tosska SQL Tuning Expert for Oracle 64位

sqltuning 英文版

Tosska SQL Tuning Expert for Oracle 1.5.0_x64.exe

oracle sql解析器

PLSQL 如何格式化 sql语句

掌握SQL知识，熟悉SQL优化流程，精通oracle数据开发

oracle参数表配sql

优化oracle sql语句详细

oracle查看sql的执行计划

最新资源