oracle数据库优化一则

简介: 本次优化涉及到oracle数据库大量insert 数据索引带来的开销,使用append、parallel的方式进行改善性能。
本次优化涉及到oracle数据库大量insert 数据索引带来的开销,使用append、parallel的方式进行改善性能。
一、首先熟悉一下oracle hint的方法
Oracle Hints是一种机制,用来告诉优化器按照我们的告诉它的方式生成执行计划。我们可以用Oracle Hints来实现:
1) 使用的优化器的类型
2) 基于代价的优化器的优化目标,是all_rows还是first_rows。
3) 表的访问路径,是全表扫描,还是索引扫描,还是直接利用rowid。
4) 表之间的连接类型
5) 表之间的连接顺序
6) 语句的并行程度
本次案例会用到2个hint,append 和parallel,环境是一个数据仓库场景,业务数据处理具有单一性,非常适合通过并行提高单次大业务的速度。

二、APPEND方式加载数据
/*+ append */以直接加载的方式将数据加载入库,它 是从HWM的位置开始插入,可能会造成空间浪费的。 非归档模式下可以减少redo的产生;归档模式下,一般情况下数据库会强制logging,归档减少不了。
insert into t /*+ append */ select * from t;

三、再来说一下oracle的并行
对于OLTP类型的数据库,除非只用于做统计、报表类的表或索引,建议不对相关表或索引调置并行度。在数据库有开启并行查询的情况下,在表或索引上存在默认并行度,将导致数据库优先采用全表或全索引扫描的执行计划,另外将生成多个并行子进程,对于OLTP类应用将反而降低相关SQL的执行效率。
   Oracle在并行处理时,会启动多少个并行进程来同时执行任务,并行度越高, 并行进程越多,执行速度 会越快,默认是noparallel,如果我们设置并行度为default值,那么此时的并行度是:
服务器CPU数*每个CPU启用的线程数(PARALLEL_THREADS_PER_CPU),OLTP环境 所以一般建议使用Noparallel,或者将并行度设置为1,而不是default。
并行度hint的使用方法:
/*+ parallel(table_name n) */  
在sql中指定执行的并行度,这个值将会覆盖自身的并行度
select /*+ parallel(t 4) */ count(*)  from t;
/*+ no_parallel(table_name) */  
在sql中指定执行的不使用并行
select /*+ no_parallel(t) */ count(*)  from t;
四、索引和insert的关系
oracle在insert数据的过程中,会同时更新索引,索引过多对insert非常不利,索引要按需创建,加载数据时虽然推荐禁用索引,但一般表上会同时存在查询操作,所以索引也必须存在。

五、在优化客户系统前,先做一个测试。
1、准备表和数据
testtb 表从dba_objects中取数86381 条数据,testtb 将作为数据加载到testtb1的数据来源,testtb 上面没有索引,在业务上使用insert into select 计算数据时,select段要首先控制其性能,这里测试咱不涉及select段的性能。

SQL> create table testtb as select * from dba_objects where 1=0;
SQL> create table testtb1 as select * from dba_objects where 1=0;
SQL> insert into testtb1 select * from testtb;
345524 rows created.
Elapsed: 00:00:01.49
我们看到插入数据<2s钟,此时增加一个索引
create index ind_OBJECT_NAME_testtb on testtb1(OBJECT_NAME);
SQL> insert into testtb1 select * from testtb;

345524 rows created.

Elapsed: 00:00:14.46 <<插入入数据将近15s
再增加索引
create index ind_OBJECT_ID_testtb on testtb1(OBJECT_ID);
SQL> insert into testtb1 select * from testtb;
345524 rows created.
Elapsed: 00:00:20.00<<插入入数据将近20s
再增加索引
create index ind_CREATED_testtb on testtb1(CREATED);
create index ind_CREATED_OBJECT_ID_testtb on testtb1(CREATED,OBJECT_ID);
SQL> insert into testtb1 select * from testtb;
345524 rows created.
Elapsed: 00:01:01.18<<插入入数据已经到60s以上

假如这些索引都是我们必须的,接下来该hint出场了
insert /*+Append parallel(8)*/ into testtb1 select * from testtb;
SQL> insert /*+Append parallel(8)*/ into testtb1 select * from testtb;
345524 rows created.
Elapsed: 00:00:21.43<<插入入数据21s

我们来看看执行过程
insert into testtb1 select * from testtb;
5a1ee58cea206189b4193a2774299f7f88d576f3
insert /*+Append parallel(8)*/ into testtb1 select * from testtb;
075c1a9363cc6c71caeb0e0b39743b8292167056
加hint提示后,select段使用的并行,但是load as select并没有使用并行,也就是说,parallel只对select段起作用了。
我们再来看看alter session enable parallel dml;
c3ca61d10fec6f570ae6f92447b6bfd645d0f979
此时加载数据也有并行,用户是使用pakage 和存储过程执行业务流,
create or replace procedure proc_1 AUTHID CURRENT_USER parallel_enable as
begin
insert /*+Append parallel(2)*/ into testtb1 select * from testtb;
commit;
DBMS_OUTPUT.put_line('Haved insert OK!');
end;
/

通过10046事件跟踪存储过程的执行
oradebug setmypid;
oradebug unlimit;
oradebug event 10046 trace name context forever, level 12;
exec proc_1;
oradebug event 10046 trace name context off;
oradebug tracefile_name;
经过测试,存储过程中的insert段是无法使用会话级并行的。这一点将在12c得到优化,hint中增加了enable_parallel_dml提示,同时这个参数包含了append的特性。
4af9ccafb333af1c9bab0b0e03db4a681bd07afb
2、真实的场景
9617ce562864bf33b500fc287f70adf98207841f
6a44546c7cf78d9c5dd8c1ddafb9ed627710f793

insert 语句已经使用了append 和parallel提示进行了优化,但执行1972s还是不能接受,查看插入的对象表,生产环境上该表的索引有8个,包含3个复合索引,测试环境虽然也有8个索引,但是插入速度比生产快,但两者环境不同,不能按照具体数值比较,测试环境索引是优化后的,去掉了多列的复合索引,开发兄弟把生产的索引和测试环境比照进行了重建,当晚执行时间下降到了359s,因此索引一定要按需创建、优化创建。
e60b361ec7c7cc2da9657eda770d5dcbf0fcdd40
这条sql执行273s,也是insert into ……select……的类型操作,select部分通过/*+ append parallel */提示从89s 缩减到8s。

六、总结
1.  建立必要的索引,废弃的索引尽快清理
2.  建立不同数据的生命周期,按周期清理,历史归并等
3.  合理使用并行 /*+ parallel(8)*/
5. Insert 表可通过/*+ APPEND */提示提高插入速度,但要持续关注空间使用。

相关文章
|
1天前
|
SQL Oracle 关系型数据库
Oracle数据库优化方法
【10月更文挑战第25天】Oracle数据库优化方法
13 7
|
1天前
|
Oracle 关系型数据库 数据库
oracle数据库技巧
【10月更文挑战第25天】oracle数据库技巧
9 6
|
1天前
|
存储 Oracle 关系型数据库
Oracle数据库优化策略
【10月更文挑战第25天】Oracle数据库优化策略
9 5
|
2天前
|
Java 数据库连接 数据库
优化之路:Java连接池技术助力数据库性能飞跃
在Java应用开发中,数据库操作常成为性能瓶颈。频繁的数据库连接建立和断开增加了系统开销,导致性能下降。本文通过问题解答形式,深入探讨Java连接池技术如何通过复用数据库连接,显著减少连接开销,提升系统性能。文章详细介绍了连接池的优势、选择标准、使用方法及优化策略,帮助开发者实现数据库性能的飞跃。
14 4
|
5天前
|
存储 缓存 监控
数据库优化:提升性能与效率的关键策略
【10月更文挑战第21】数据库优化:提升性能与效率的关键策略
|
5天前
|
存储 分布式计算 监控
数据库优化:提升性能与效率的全面策略
【10月更文挑战第21】数据库优化:提升性能与效率的全面策略
|
8天前
|
存储 Oracle 关系型数据库
数据库数据恢复—Oracle ASM磁盘组故障数据恢复案例
Oracle数据库数据恢复环境&故障: Oracle ASM磁盘组由4块磁盘组成。Oracle ASM磁盘组掉线 ,ASM实例不能mount。 Oracle数据库故障分析&恢复方案: 数据库数据恢复工程师对组成ASM磁盘组的磁盘进行分析。对ASM元数据进行分析发现ASM存储元数据损坏,导致磁盘组无法挂载。
|
10天前
|
监控 Oracle 关系型数据库
Oracle数据库性能优化
【10月更文挑战第16天】Oracle数据库性能优化是
13 1
|
9天前
|
存储 监控 关系型数据库
MySQL并发控制与管理:优化数据库性能的关键
【10月更文挑战第17天】MySQL并发控制与管理:优化数据库性能的关键
30 0
|
16天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
50 3
Mysql(4)—数据库索引

推荐镜像

更多