[bbk2300] 第43集 - Chapter 11-SQL Statement Tuning(02)

Hint帮助Optimizer得到最优的查询结果.

Using Hints in SQL

SELECT * FORM employees;

SELECT /*+ First_rows */ FROM employees;

Optimizer Plan Stability

• Users can stabilize execution plans,to force applications to use a desired SQL access path.
• A consistent execution path is thereby maintained through database changes.
• This is done by creating a stored outline consisting of hints.
• The OPTIMIZER_FEATURES_ENABLE parameter enables the optiimizer to retain CBO features of previous versions.

同样一个SQL语句，在不同版本的数据库环境下，运行效率可能千差万别.

Optimizer Plan Stability原理：在系统迁移的过程中，导出原先运行稳定的执行计划，进而导入到新环境中，从而实现SQL Statements 执行计划稳定性.

在oracle 9i中，管理维护Optimizer Plan Stability的工具为stored outline,在10g,11g之后使用SQL PLAN Management代替stored outline.

parameter OPTIMIZER_FEATURES_ENABLE的作用：通过设置参数值，可以使数据库按照指定参数值的数据库版本对应的优化器版本进行优化功能的操作.

SQL Plan Management

Note:Stored outlines will be desupported in a furture release in favor of SQL plan management.In Oracle Databaes 11g Release 1(11.1) ,stored outlines continue to function as in past releases.However,Oracle strongly recommends that you use SQL plan management for new applications.SQL plan management creates SQL plan baselines,which offer superior SQL performance and stability compared with stored outlines.

If you have existing stored outlines,consider migrating them to SQL plan baselines by using the LOAD_PLANS_FROM_CURSOR_CACHE or LOAD_PLANS_FROM_SQLSET procedure of the DBMS_SPM package.When the migration is comlete,you should disable or remove the stored outlines.

如果有之前通过stored outline创建的execution plan, 现在可以通过 LOAD_PLANS_FROM_CURSOR_CACHE or LOAD_PLANS_FROM_SQLSET函数进行转换，支持新版本的；Di

Diagnostic Tools

• Statspack
• Explain plan
• SQL trace and TKPROF(TKPROF用来解读trace文件的)
• SQL*Plus autotrace feature
• Oracle SQL Analyze(OEM里面的功能)

SQL Reports in Statspack

The following reports on statements are provided by statspack:

• SQL ordered by gets(从Data buffer cache中读数据)
• SQL ordered by reads(从block中读数据到data buffer cache) 
• SQL ordered by executions
• SQL ordered by parse calls

通过上述四个报表，DBA就可以把最值得关注的、最值得怀疑的SQL语句给揪出来. 

有关statspack的详细情况，可以参考官方权威文档:$ORACLE_HOME/rdbms/admin/spdoc.txt 

PM:Top SQL

 

What is Execution Plan?

Execution Plan实际上跟一棵树一样，将具体的执行计划给分解成原子操作.

HJ->Hash Join 

An execution plan is a set of steps that the optimizer performs when executing a SQL statement and performing an operation.

 

Viewing Execution Plan

• EXPLAIN PLAN(command line)
• SQL Trace
• Automatic Workload Repository
• V$SQL_PLAN
• SQL*Plus AUTOTRACE

Uses of Execution Plan

• Determining the current execution plan
• Identifying the effect of indexes
• Determining access paths
• Verifying the use of indexes
• Verifying which execution plan may be used.

 

转载于:https://www.cnblogs.com/arcer/archive/2013/05/04/3059636.html