Oracle数据库应该如何优化一个SQL

类型：数据库类大小：42.1M语言：中文评分：4.2标签:立即下载。这是终极问题，因为优化本身的复杂性很难概括。很多时候，优化方法不是高深莫测的技术，而是意识形态上的差异，可能会导致性能上的巨大差异。所以有时候我们要先搞清楚需求是什么，SQL本身是否合理。这些想法很可能会让优化工作事半功倍。但是，假设SQL本身是合理的，本文从Oracle提供的一些技术手段来简单介绍一下Oracle数据库，以及如何利用现有的一些技术来优化一个SQL的执行性能。

确定要优化的SQL文本和当前的SQL执行计划

确定SQL中涉及的所有表及其索引的信息

运行SQL优化顾问以获取优化参考的调整建议

收集表格信息

收集索引信息

SQL概要文件

物化视图

1.确定要优化的SQL文本和当前的SQL执行计划

在优化之前，确定要优化的SQL文本以及当前的SQL执行计划是什么。请注意，像PL/SQL Developer F5这样的工具看到的执行计划可能是不准确的。相关内容参考：

SQL调优基础概述01-自动跟踪的设置

SQL调优基础概述02-解释计划的使用

SQL调优基础概述03-使用sql_trace和10046事件跟踪执行计划

2.确定SQL中涉及的所有表及其索引的相关信息

确定查询中涉及的所有表及其索引的基本信息。比如：

每个表的数据表和索引类型表分区信息，每个分区索引字段的数据量索引分区信息表关联模式结果集编号

确定相关信息，以T2表为例：

-常规表/分区表信息选择*从DBA _ tablewertable _ name=' T2 '；选择* from DBA _ part _ table where _ table _ name=' T2 '；-普通表/分区表每个分区的大小约为_ _ G select(t . bytes/1024/1024)' MB '，t. *来自DBA _ segmentsthwenreset _ name=' T2 '；-表数据量信息-普通表的数据量选择计数(1)从2；-_ _ _ _左右数据-分区表的分区数据量从T2分区(p20160101)中选择计数(*)；-_ _ _ _来自T2分区(p20160102)的左右数据选择计数(*)；-表索引信息-公共表索引和每个索引的索引列从DBA _ indexeshheretable _ name=' t2 '中选择*；选择* from DBA _ ind _ columns heereindex _ name in(selectindex _ name from DBA _ indexes where table _ name=' T2 ')order by index _ name，column _ position-分区表索引和每个索引的索引列从DBA _ part _ indexesheretable _ name=' T2 '中选择*；选择* from DBA _ ind _ columns heereindex _ name in(selectindex _ name from DBA _ part _ indexes where table _ name=' T2 ')order by index _ name，column _ position-索引段大小信息-从DBA _ segmentstwhenreset _ namein中选择(t. bytes/1024/1024)' MB '、t. *(从DBA _ part _ indexed _ name=' T2 '中选择index _ name)按段名、分区_3. 运行SQL Tuning Advisor 得到调整建议供优化参考排序

运行SQL优化顾问，获取优化参考的调整建议。SQL调优顾问获得的优化建议仅供参考，如何做要结合实际业务情况。

4.收集表格信息

比如收集ZJY用户下T2表的统计信息。(T2是范围分区表，按天分区，日数据量约80w，存储半年)

SQLexecutedbms _ STATS . gather _ table _ STATS(own name=' ZJY '，tabname='T2 '，estimate_percent=DBMS_STATS。AUTO_SAMPLE_SIZE，method _ opt=' forallcolumnsizaeuto '，cascade=TRUE，度数=16)；PL/sqlprocedureccessfullycompletedexecutedin 5896.641秒

统计锁定/解锁

-锁定表execdbms _ stats的统计信息。lock _ table _ stats ('zjy '，' T2 ')；-解锁表execdbms _ stats的统计信息。unlock _ table _ stats ('zjy '，' T2 ')；

相关内容参考：

[重新打印] dbms_stats导入和导出表统计

5.收集索引信息

比如只收集ZJY用户下T2表的索引IDX_T2_1统计。(IDX_T2_1是一个包含4个字段的分区索引)

SQLexecutedbms _ STATS . gather _ index _ STATS(own name=' ZJY '，indname='IDX_T2_1 '，estimate_percent=DBMS_STATS。AUTO_SAMPLE_SIZE，度数=8)；PL/sqlprocedureccessfullycompletedexecutedin 44.312秒

有时，当您忙的时候，很可能需要在线创建一个新的索引。

-创建单列索引IDX_T2_2在不记录日志的情况下并行联机(并行度取决于生产环境的当前CPU资源使用情况，以确定合理的值)创建indexidx _ T2 _ 2ont2 (start _ time)表空间DBS _ I _ jingyunloggingparallel 12 online；altindexidx _ T2 _ 2 no parallel；altindexidx _ T2 _ 2日志；

6.SQL概要文件

作为自动SQL调优过程的一部分，SQL概要文件是10g中的一项新功能。“SQL概要文件”是一个对象，它包含的信息可以帮助查询优化器为特定的SQL语句找到有效的执行计划。该信息包括执行环境、对象统计信息和查询优化器进行的评估的校正信息。它最大的优点之一是，它会影响查询优化器的决策，而不会修改SQL语句和会话执行环境。“SQL概要文件”中包含的信息不是一个单独的执行计划，因此“SQL概要文件”不会修复SQL语句的执行计划。当表的数据增长或创建或删除索引时，使用相同的SQL概要文件的执行计划可能会改变，存储在SQL概要文件中的信息将继续工作。因此，时间长了，其信息可能会过时，需要重新生成。

相关内容参考：

[转载]使用sql_profile

[重新打印] sql_profile快速绑定脚本

7.物化视图

Oracle的实体化视图可用于预先计算和保存结果(这是耗时的操作，如表连接或聚合)。因此，如果物化视图使用合理，在查询执行过程中可以避免这些耗时的操作，快速获得结果。