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 */提示提高插入速度,但要持续关注空间使用。

相关文章
|
6天前
|
存储 NoSQL 分布式数据库
微服务架构下的数据库设计与优化策略####
本文深入探讨了在微服务架构下,如何进行高效的数据库设计与优化,以确保系统的可扩展性、低延迟与高并发处理能力。不同于传统单一数据库模式,微服务架构要求更细粒度的服务划分,这对数据库设计提出了新的挑战。本文将从数据库分片、复制、事务管理及性能调优等方面阐述最佳实践,旨在为开发者提供一套系统性的解决方案框架。 ####
|
7天前
|
存储 SQL 数据库
深入浅出后端开发之数据库优化实战
【10月更文挑战第35天】在软件开发的世界里,数据库性能直接关系到应用的响应速度和用户体验。本文将带你了解如何通过合理的索引设计、查询优化以及恰当的数据存储策略来提升数据库性能。我们将一起探索这些技巧背后的原理,并通过实际案例感受优化带来的显著效果。
25 4
|
9天前
|
SQL druid 数据库
如何进行数据库连接池的参数优化?
数据库连接池参数优化包括:1) 确定合适的初始连接数,考虑数据库规模和应用需求;2) 调整最大连接数,依据并发量和资源状况;3) 设置最小空闲连接数,平衡资源利用和响应速度;4) 优化连接超时时间,确保系统响应和资源利用合理;5) 配置连接有效性检测,定期检查连接状态;6) 调整空闲连接回收时间,适应访问模式并配合数据库超时设置。
|
17天前
|
SQL Oracle 关系型数据库
Oracle数据库优化方法
【10月更文挑战第25天】Oracle数据库优化方法
25 7
|
17天前
|
Oracle 关系型数据库 数据库
oracle数据库技巧
【10月更文挑战第25天】oracle数据库技巧
21 6
|
13天前
|
SQL 缓存 监控
数据库优化
【10月更文挑战第29天】数据库优化
27 1
|
17天前
|
存储 Oracle 关系型数据库
Oracle数据库优化策略
【10月更文挑战第25天】Oracle数据库优化策略
17 5
|
14天前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能?
【10月更文挑战第28天】
37 1
|
16天前
|
XML Java 数据库连接
如何使用HikariCP连接池来优化数据库连接管理
在Java应用中,高效管理数据库连接是提升性能的关键。本文介绍了如何使用HikariCP连接池来优化数据库连接管理。通过引入依赖、配置参数和获取连接,你可以显著提高系统的响应速度和吞吐量。 示例代码展示了从配置到使用的完整流程,帮助你轻松上手。
58 3
|
15天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
81 1

推荐镜像

更多