ORACLE9i_性能调优基础八(SQL tuning)

最新推荐文章于 2024-02-02 14:48:54 发布

原创最新推荐文章于 2024-02-02 14:48:54 发布 · 510 阅读

0 ·

CC 4.0 BY-SA版权

本文深入探讨了数据库SQL优化器的工作原理及其配置方法。包括两种主要的优化模式：基于规则和基于成本；如何设置优化器模式；利用SQL提示提高响应时间和吞吐量；执行计划稳定性与SQL计划管理；以及诊断工具的使用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Optimizer Modes

There are two types of optimizer modes:

1. Rule-based:

a.Uses a ranking syste

b.Syntax-and data dictionary-driven

2.Cost-based:

a.Chooses the path with lowet cost

b.Statistics-driven

Understanding Optimizer

1.优化器基于一些可行的路径和提示，对即将执行的SQL语句产生很多plan,选择一个最优的

2.优化器是基于数据字典里的统计数据计算每一个plan,来计算执行成本

3.如果是串行优化器选择一个执行最低的成本，并行计算比较复杂

调整的目标：对于批处理应该选择最佳的吞吐量

对于交互式处理：响应时间

Setting the Mode

优化器的行为受以下因素影响：

1.OPTIMIZER_MODE initialization Parameter

2.Optimizer SQL Hints for Changing the Query Optimizer Goal

3.Query Optimizer Statistics in the Data Dictionary

QL> show parameter optimi                                                                                                   

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.2.0.1
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE
plsql_optimize_level                 integer     2

Statistics for Optimizer

用存储在数据字典里的统计指标，经常要保持这些指标的更新。如果没有可用的指标可参考，优化器会根据OPTMIZER_DYNAMIC_SAMPLING参数来动态临时采样，这个时候执行时间就会过长

Using Hints in SQL

1.FIRST_ROWS(n) 响应时间

2.ALL_ROWS : 吞吐率

SELECT /*+ Frist_rows */ from tab1;

Optimizer Plan Stability

1.用户可以指定SQL使用原来想要的SQL plans

2.数据库改变了，也使用原来的SQL plans

3.通过导入原数据库的 stored outline(已经过时)

4.OPTIMIZER_FEATURES_ENABLE 可以指定版本号如果指定9i,则目标数据库按照9i的性能来运行

SQL Plan Management

Stored outlines 已经被 SQL Plan Management所代替，以后要尽量使用对于Stored outlines 过度到 SQL plan management ,可以使用 DBMS_SPM包里的 LOAD_PLANS_FROM CURSOR_CACHE 或 LOAD_PLANS_FROM_SQLSET过程来执行，执行完毕之后就应该禁止或移除 stored outlines

Diagnostic Tools

1.Statspack

2.EXPLAIN PLAN

3.SQL trace and TKPROF

4.SQL*Plus autotrace feature

5.Oracle SQL Analyze

SQL Reports in Statspack

1.SQL ordered by gets

2.SQL ordered by reads

3.SQL ordered by executions

4.SQL ordered by parse calls

Viewing Execution Plan

1.EXPLAIN PLAN

2.SQL Trace

3.Automatic Workload Repository

4.V$SQL_PLAN

5.SQL*Plus AUTOTRACE

Uses of Execution Plan

1.Determing the current execution plan

2.Identifying the effect of indexes

3.Determing access paths

4.Verifying the use of idnexes

5.Verifying which execution plan may be used

Generate the Execution Plan

1.Can be used without tracing

2.Needs the plan_table table utlxplan.sql

3.Create the explain plan:

EXPLAIN plan FOR select last_name from hr.employees;

query:

1.Query plan_tabe directly

2.use script utlxpls.sql (hide parallel query information)

3.ue script utlxplp.sql(show parallel Query information)

4.Use te dbms_xplan package

select * from table(dbms_xplan);

Using SQL Trace & TKPROF

1.ALTER SESSION set sql_trace=true;

2.Format the trace file with TKPROF

3.Interpret the output

SQL> alter session set sql_trace=true;
SQL> select * from tt where rownum<30;
[root@localhost udump]# pwd
/u01/app/oracle/admin/king/udump
[root@localhost udump]# /u01/app/oracle/product/10.2.0/db_1/bin/tkprof king_ora_5097.trc  myoutput.txt