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

这是个终极问题,因为优化本身的复杂性实在是难以总结的,很多时候优化的方法并不是用到了什么高深莫测的技术,而只是一个思想意识层面的差异,而这些都很可能连带导致性能表现上的巨大差异。 所以有时候我们应该先搞清楚需求到底是什么,SQL本身是否合理,这些思考很可能会使优化工作事半功倍。而本文是假设SQL本身合理,从Oracle提供给我们的一些技术手段来简单介绍下Oracle数据库,该如何使用一些现有的技术来优化一个SQL执行的性能。

  1. 确定需要优化的SQL文本及当前SQL执行计划
  2. 确定SQL涉及的所有表及其索引的相关信息
  3. 运行SQL Tuning Advisor 得到调整建议供优化参考
  4. 收集表信息
  5. 收集索引信息
  6. SQL Profile
  7. 物化视图

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

优化之前先确定好需要优化的SQL文本以及当前SQL的执行计划是什么样,注意PL/SQL Developer这类工具F5看到的执行计划很可能并不准确。 相关内容参考:

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

确定查询涉及到的所有表及其索引的相关基础信息。比如:

各表的数据量 表和索引类型 表分区信息,每个分区的数据量 索引字段 索引分区信息 表关联方式 结果集的数量

确定相关信息,以T2表为例:

--普通表/分区表信息
select * from dba_tables where table_name = 'T2';
select * from dba_part_tables where table_name = 'T2'; 
--普通表/分区表的每个分区大约__G大小
select (t.bytes/1024/1024) "MB", t.* from dba_segments t where segment_name = 'T2';  

--表数据量信息
--普通表的数据量
select count(1) from T2; --____数据左右
--分区表的某个分区数据量
select count(*) from T2 partition(P20160101);  --____数据左右
select count(*) from T2 partition(P20160102);

--表索引信息
--普通表索引及各个索引的索引列
select * from dba_indexes where table_name = 'T2';
select * from dba_ind_columns where index_name in (select index_name from dba_indexes where table_name = 'T2')order by index_name, column_position;
--分区表索引及各个索引的索引列
select * from dba_part_indexes where table_name = 'T2';
select * from dba_ind_columns where index_name in (select index_name from dba_part_indexes where table_name = 'T2') order by index_name, column_position;
--索引段大小信息
--select (t.bytes/1024/1024) "MB", t.* from dba_segments t where segment_name in (select index_name from dba_part_indexes where table_name = 'T2') order by segment_name, partition_name;

相关内容参考:

3. 运行SQL Tuning Advisor 得到调整建议供优化参考

运行SQL Tuning Advisor 得到调整建议供优化参考, SQL Tuning Advisor得到的优化建议仅供参考,具体如何做还需要结合业务实际情况。

相关内容参考:

4. 收集表信息

例如收集ZJY用户下T2表的统计信息。(T2是range分区表,按天分区,每天数据量大概80w,存放半年)

SQL> execute dbms_stats.gather_table_stats(ownname => 'ZJY', tabname => 'T2', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE, degree => 16);
 
PL/SQL procedure successfully completed
 
Executed in 5896.641 seconds

统计信息加锁/解锁

--锁住表的统计信息
exec dbms_stats.lock_table_stats('ZJY','T2'); 
--解锁表的统计信息
exec dbms_stats.unlock_table_stats('ZJY','T2'); 

相关内容参考:

5. 收集索引信息

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

SQL> execute dbms_stats.gather_index_stats(ownname => 'ZJY', indname => 'IDX_T2_1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 8);
 
PL/SQL procedure successfully completed
 
Executed in 44.312 seconds

有时还很可能需要在业务闲时在线创建新的索引

--不记录日志在线并行创建单列索引IDX_T2_2(并行度视生产环境当前的CPU资源使用情况来确定合理的值)
create index IDX_T2_2 on T2(start_time) tablespace DBS_I_JINGYU nologging parallel 12 online;
alter index IDX_T2_2 noparallel;
alter index IDX_T2_2 logging;

6. SQL Profile

SQL Profile是10g中的新特性,作为自动SQL调整过程的一部分。SQL Profile是一个对象,它包含了可以帮助查询优化器为一个特定的SQL语句找到高效执行计划的信息。这些信息包括执行环境、对象统计和对查询优化器所做评估的修正信息。它的最大优点之一就是在不修改SQL语句和会话执行环境的情况下影响查询优化器的决定。SQL Profile中包含的并非单个执行计划的信息,SQL Profile不会固定一个SQL语句的执行计划。当表的数据增长或者索引创建、删除,使用同一个SQL Profile的执行计划可能会改变,而存储在SQL Profile中的信息会继续起作用。所以,经过一段很长的时间之后,它的信息有可能会过时,需要重新生成。

相关内容参考:

7. 物化视图

Oracle的物化视图可以用于预先计算并保存(表连接或聚集等耗时较多的操作的)结果,所以合理使用物化视图,会在执行查询时避免进行这些耗时的操作,从而快速的得到结果。

相关内容参考:

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏互扯程序

数据库SQL优化大总结之 百万级数据库优化方案

网上关于SQL优化的教程很多,但是比较杂乱。近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充。

1134
来自专栏Java面试通关手册

Mysql锁机制简单了解一下

Java面试通关手册(Java学习指南,欢迎Star,会一直完善下去,欢迎建议和指导):https://github.com/Snailclimb/Java_G...

16511
来自专栏小怪聊职场

MySQL(七)|MySQL分库分表的那点事(小怪的Java群第一次话题讨论)

2624
来自专栏Hadoop实操

如何使用Phoenix在CDH的HBase中创建二级索引

3032
来自专栏数据和云

【云和恩墨大讲堂】谈Oracle表新增字段的影响

作者简介 ? 刘晨,网名bisal,Oracle 10g/11g OCM,并国内首批Oracle YEP成员,博客:blog.itpub.net/bisal 很...

3037
来自专栏后端技术探索

mysql 水平分表的几种方法

当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,我想有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。

1022
来自专栏杨建荣的学习笔记

MySQL和Oracle对比学习之事务(r5笔记第4天)

MySQL中的存储引擎很是丰富,常用的有InnoDB,MyISAM等,也查看了不少的资料,基本也有所了解,从一些参考书中看MySQL中的sql部分也是一扫而过,...

4508
来自专栏数据和云

循序渐进:Oracle 12c新特性Sharding技术解读

引言 数据库构架设计中主要有 Shared Everthting、Shared Nothing 和 Shared Disk: Shared Everthting...

4537
来自专栏后端技术探索

mysql 水平分表的几种方法

当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,我想有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。

1.1K2
来自专栏Java后端技术栈

MySQL 清除表空间碎片

(1)表的存储会出现碎片化,每当删除了一行内容,该段空间就会变为空白、被留空,而在一段时间内的大量删除操作,会使这种留空的空间变得比存储列表内容所使用的空间更大...

984

扫码关注云+社区