注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

常在心

淡泊明志,人生自在

 
 
 

日志

 
 

Oracle数据库优化的方案和实践(建议稿)-转  

2011-06-10 15:42:01|  分类: oracle优化 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
Oracle数据库优化的方案和实践(建议稿)
bigboar占用itpub的blog,bigboar ID第一次发帖,呱唧一下
http://bigboar.itpub.net/post/8411/53195
05年10月写的,具有很强的业务针对性,并不一定适合所有的业务环境。
一、前言 2
二、ORACLE数据库优化概述 2
1、内存等参数配置的优化 3
2、减少物理读写的优化 4
3、批量重复操作的SQL语句及大表操作的优化 4
二、ORACLE数据库优化方案 5
1、内存等Oracle系统参数配置 5
2、使用索引 6
3、表分区 8
4、Procedure优化 9
5、其他改造 10
6、维护作业计划 10
三、ORACLE数据库优化前后比较 10
1、批量重复的SQL语句执行性能 10
2、一些单次、不常用的操作的语句执行性能 11
四、参考 11
1、常用的优化工具 11
2、参考文献 12
一、前言
随着实际项目的启动,实际项目中使用的 Oracle数据库经过一段时间的运行,在线保存的数据量和业务处理的数据量在逐渐增大,最初的Oracle设置,与现在实际需要的运行性能有一定差距,需要进行一些优化调整。
本文将结合本人实际维护经验,相应地提出实际项目数据处理的一些优化方法,以供参考。
适用于Oracle 9i。
二、Oracle数据库优化概述
Oracle数据库的优化,针对不同的应用,会有侧重点不同的优化方法,根据我们实际项目的应用特点,我们主要关心的是每次事务执行完成的时间长短。
从Oracle数据库本身的特点,我们可以把优化工作划分为初始优化设置,微优化。
在初始优化设置时,我们只能根据硬件情况,估计业务运行的情况,综合经验,给出一种经验设置,大体上来说,这种经验设置离满足优化需求的目标不是很远。
在完成了初始优化设置后,经过一段时间的业务运行,已可开始收集实际运行环境的性能数据,此时,就可以对各种Oracle性能指标、各种关心的事务操作进行性能评估,然后进行微优化了。
Oracle优化,不是一个一蹴而就的工作,也不是一个一劳永逸的工作,需要定期维护,定期观察,在发现性能瓶颈时及时进行调整。Oracle总是存在性能瓶颈的,不使用、不操作的数据库总是最快的,在解决当前瓶颈后,总是会有另一个瓶颈出现,所以在优化前,我们需要确定一个优化目标,我们的目标是满足我们的应用性能要求就可以了。
Oracle优化,涉及的范围太广泛,包含的有主机性能,内存使用性能,网络传输性能,SQL语句执行性能等等,从我们面向网管来说,满足事务执行速度性能主要表现在:
1)批量重复的SQL语句执行性能(主要是通过Procedure计算完成数据合并和数据汇总的性能和批量数据采集入库的性能);
2)一些单次、不常用的操作的语句执行性能(主要是GUI的非规律操作)。
根据这两个特点,我们可把优化方法归纳到3个重要方向:
1)内存等参数配置的优化。内存优化,是性能受益最快的地方。
2)减少物理读写的优化。内存逻辑I/O操作的时间,远远小于物理I/O的操作时间。
3)批量重复操作的SQL语句及大表操作的优化。减少SQL执行次数,减少大表操作次数。
下面主要针对得益最大的这三个方向的优化进行阐述。
1、内存等参数配置的优化
对于大多数应用来说,最直接、最快速得到优化收益的,肯定属于内存的优化。给每个Oracle内存块分配合理的大小,可以有效的使用数据库。通过观察各种数据库活动在内存里的命中率,执行情况,我们能很快的掌握数据库的主要瓶颈。我们从下面的一条SQL语句的执行步骤就可知道。
一个SQL语句,从发布到执行,会按顺序经历如下几个步骤:
1)Oracle把该SQL的字符转换成它们的ASCII等效数字码。
2)该ASCII数字码被传送给一个散列算法,生成一个散列值。
3)用户server process查看该散列值是否在shared pool内存块中存在。
若存在:
4)使用shared pool中缓存的版本来执行。
若不存在:
4)检查该语句的语义正确性。
5)执行对象解析(这期间对照数据字典,检查被引用的对象的名称和结构的正确性)。
6)检查数据字典,收集该操作所引用的所有对象的相关统计数据。
7)准备执行计划,从可用的执行计划中选择一个执行计划。(包括对stored outline和materialized view的相关使用的决定)
8)检查数据字典,确定所引用对象的安全性。
9)生成一个编译代码(P-CODE)。
10)执行。
这里,通过内存的合理分配,参数的合理设置,我们主要解决:
1)减少执行到第五步的可能,节约SQL语句解析的时间。第五步以后的执行过程,是一个很消耗资源的操作过程。
2)通过内存配置,尽可能让SQL语句所做的操作和操作的数据都在内存里完成。大家都知道,从内存读取数据的速度,要远远快于从物理硬盘上读数据,一次内存排序要比硬盘排序快很多倍。
3)根据数据库内存活动,减少每个内存块活动的响应时间,充分利用每个内存块,减少内存latch争用发生的次数。
2、减少物理读写的优化
无论如何配置Oracle数据库,我们的网管系统,每小时周期性的都会有新数据被处理,就会发生物理读写,这是避免不了的。
减少物理读写的优化,一般所用的方法有:
1) 增加内存data buffer的大小,尽可能让数据库操作的数据都能在内存里找到,不需要进行物理读写操作。
2) 通过使用索引,避免不必要的全表扫描。
3) 大表物理分区,Oracle具有很好的分区识别功能,减少数据扫描范围。
上述3个方法,是从整体上改善数据库物理I/O性能最明显的3个方法。能非常快速的减少数据库在物理I/O,最直接的反应是数据库事务执行时间能能以数量级为单位减少。
其他的一些减少物理读写的优化方法,比如使用materialized view,Cluster等方法;还有一些分散I/O的方法,比如 Oracle日志文件不与数据文件放在一个物理硬盘,数据热点文件物理I/O分开等等方法,就目前我们的网管系统而言,能得到的效果不是很明显,在网管系统中,为了不增加数据库维护的复杂性,不推荐使用。
3、批量重复操作的SQL语句及大表操作的优化
批量重复执行的SQL语句,一般出现在每个周期时间内的数据批量入库的insert语句,和数据合并、汇总的周期性select、delete、insert操作。
我们需要注意以下几点:
1) 减少不必要的SQL语句执行和SQL语句的执行次数。
每条SQL语句执行,都会消费系统资源,都有执行时间。减少不必要的SQL语句执行和减少SQL语句的执行次数,自然能减少业务执行时间。需要根据业务流程,重新设计数据处理的代码。此方法主要适用于procedure执行的数据合并、汇总。
2) 这些SQL语句,由于每个SQL语句都要执行很多次,应该尽量让该SQL的散列值在shared pool内存块中存在。也就是使用动态SQL,避免SQL硬解析。
可通过Oracle参数的设置,和动态SQL语句的应用,通过绑定变量的方式,减少SQL语句的解析次数。
3) 减少大表的操作,确保在一次事务中,同类操作只对大表执行一次。主要在数据合并和数据汇总的pprocedure和数据采集时出现。
- procedure:在我们的应用中,cell数据和traffic数据,以及link,linkset,trunkgroup等数据,都属于大数据量业务,对他们的每次操作,执行的时间都不短(即使做了表分区,减少数据扫描范围,操作的响应时间还是大于一个同类大小的普通表)。在多次执行的一个procedure中,通过使用临时表的方式,把多次大表操作,改造成一次大表操作,能极大的缩短业务执行时间。
- 数据采集:如果是普通的每条insert语句完成一条数据入库,为了保证数据的唯一性,每次入库的时候,都会判断该条数据是否存在数据库,造成对表不必要的多次操作。对于批量数据入库,我们推荐使用sqlldr的方法进行数据拷贝,能极大的提高数据入库时间。
二、Oracle数据库优化方案
1、内存等Oracle系统参数配置
Oracle 的parameter参数,分动态参数和静态参数,静态参数需要重新启动数据库才能生效,动态参数不需要重新启动数据库即可生效。
Oracle 9i可以使用spfile的特性,使用alter system set 参数名=参数值 scope=both[spfile];的方法进行修改。也可以直接修改pfile。
以下给出了网管Oracle 数据库重点关注的parameter的初始优化设置。
? 最大可使用的内存SGA总和
静态参数sga_max_size=物理内存的大小减1.5G
? Shared pool
动态参数shared_pool_size= 600 ~ 800 M
静态参数shared_pool_reserved_size= 300 M
动态参数open_cursors= 400 ~ 600
静态参数cursor_space_for_time= TRUE
静态参数session_cached_cursors= 60 ~ 100
动态参数cursor_sharing= SIMILAR
? Data buffer
动态参数db_cache_advice= READY
动态参数db_cache_size
动态参数Db_keep_cache_size
动态参数db_recycle_cache_size
(sga_max_size大小,除了分配给所有非data buffer的size,都分配给data buffer)
? Sga other memory
动态参数large_pool_size= 50 M
静态参数java_pool_size= 100 M
动态参数log_buffer= 3 M
? Other memory
动态参数sort_area_size= 3 M
静态参数sort_area_retained_size= 0.5 M
静态参数pga_aggregate_target= 800 M
动态参数workarea_size_policy= AUTO
? 磁盘I/O配置
静态参数sql_trace= FALSE
动态参数timed_statistics= true
动态参数db_file_multiblock_read_count= 16
静态参数dbwr_io_slaves= 0
静态参数db_writer_processes= 3
静态参数undo_management= AUTO
动态参数undo_retention= 7200
2、使用索引
我们初步定义,表数据超过1000行的表,我们都要求使用索引。(不区分事务操作的数据在表数据中所占的比例)
索引所包含的字段不超过4个。
检查SQL语句是否使用了索引,我们使用execute plan来看,获得explain的方法,我们通过SQL*PLUS工具,使用如下命令进行查看:
set autotrace on
set autotrace traceonly explain
set timing on
或通过SQL*PLUS trace,然后查看user_dump_dest下的跟踪文件,使用tkprof工具格式化后阅览。
alter session set events '10046 trace name context forever,level 12';
alter session set events '10046 trace name context off';
SELECT p.spid,s.username FROM v$session s,v$process p WHERE s.audsid=USERENV('sessionid') AND s.paddr = p.addr;
使用方法示例:
DBserver% sqlplus perf/perf
SQL*Plus: Release 9.2.0.6.0 - Production on Mon Oct 17 14:32:29 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> set timing on
SQL> set autotrace on
SQL> select count(*) from perf_sdcch_nn where start_time = (select max(start_time) from perf_sdcch_nn);
COUNT(*)
----------
638
Elapsed: 00:00:00.80
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'IDX02_PERF_SDCCH_NN' (NON-UNIQUE)
(Cost=2 Card=1495 Bytes=11960)
3 2 SORT (AGGREGATE)
4 3 INDEX (FULL SCAN (MIN/MAX)) OF 'IDX02_PERF_SDCCH_NN'
(NON-UNIQUE) (Cost=1 Card=3852090 Bytes=30816720)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
492 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
从上面的示例我们可以看到,该SQL语句执行花了0.8秒,select语句和inline view,都使用了名为'IDX02_PERF_SDCCH_NN 的索引。物理读为0,redo log size为0,没有生成REDO日志。
3、表分区
在网管数据库里,比较突出的大表有小区表和告警表。
? 性能表,使用范围分区。
以时间点start_time为范围分区字段。
? 告警表,使用range-hash的混合分区和范围分区。
范围分区以时间点starttime为分区字段,混合分区增加ALARMNUMBER为字段的hash子分区。
同时,创建本地分区索引。
范围分区示范:
drop table part_mid_cell_traf;
create table part_mid_cell_traf;
(
NE_ID CHAR(16) NOT NULL,
....................
TCHSEIZE FLOAT(126)
)
partition by range(start_time)
(
partition mid_cell_traf_051225 values less than (to_date('2005-12-25 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace perf_partition42,
partition mid_cell_traf_051230 values less than (to_date('2005-12-30 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace perf_partition43,
partition mid_cell_traf_error values less then (maxvalue) tablespace perf_partition44
)
enable row movement;
CREATE INDEX local_mid_cell_traf ON part_mid_cell_traf (ne_id,cell_id,start_time,stop_time)
LOCAL
(
partition l_ind1_mid_cell_traf_051225 tablespace perf_partition42,
partition l_ind1_mid_cell_traf_051230 tablespace perf_partition43,
partition l_ind1_mid_cell_traf_error tablespace perf_partition44
);
Range-hash混合分区示范:
create table part_ALARMTEXTDATA;
(
ALARMNUMBER VARCHAR2(16) NOT NULL,
......
SERIAL NUMBER(38) NOT NULL
)
partition by range (STARTTIME)
subpartition by hash(ALARMNUMBER)
subpartition template
(
subpartition atd01 tablespace alarm_partition01,
subpartition atd02 tablespace alarm_partition02,
subpartition atd03 tablespace alarm_partition03,
)
(
partition ALARMTEXTDATA_051225 values less than (to_date('2005-12-25 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition ALARMTEXTDATA_051230 values less than (to_date('2005-12-30 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition ALARMTEXTDATA_error values less then (maxvalue)
)
enable row movement;
CREATE INDEX local_ALARMTEXTDATA ON ALARMTEXTDATA (STARTTIME,ALARMNUMBER,SERIAL)
LOCAL
(
partition l_ind1_atd_050910 tablespace alarm_partition02,
partition l_ind1_atd_050915 tablespace alarm_partition03,
partition l_ind1_atd_050920 tablespace alarm_partition04,
);
4、Procedure优化
1)取消地市一级的Procedure,只保留其上层调用Procedure,并保持参数输入方法,调用方法不变。
2)确保大表数据查询操作只有1次,确保大表数据删除只有一次。
3)确保单条SQL语句执行已优化。
4)减少SQL执行次数。
5、其他改造
修改表存储参数,提前预先分配extents。
修改表空间存储参数(采集表空间所用块设置为大块,比如32k一个块;修改ptcfree,pctused,pctincrease等)。
避免使用唯一索引和非空约束。
创建合理的索引。
各模块SQL语句优化,比如使用提示固定索引等。
确认每一条历史数据删除语句已优化和删除方法。
临时表的使用。
6、维护作业计划
表分析(包含确定具体的表的分析方法,分区表分析方法,索引分析方法)。
空间回收维护(包括确定HWM,回收多余分配给表的块,合并数据块碎片等)。
索引维护(包括定期重建索引,索引使用情况监视等)。
历史数据删除检查(检查保存的数据是否符合要求,检查历史数据删除方法是否正确-比如批量删除提交的方法等)。
全库性能分析和问题报告及优化(比如使用statspack进行性能趋势分析,检查有问题的SQL或事务,确定当前系统等待的top 5事件等等)。
表数据keep,default及reclye(比如把一些常用的配置表固定在内存里等)。
数据库参数核查(防止数据库参数被修改,定期对系统配置参数进行比较)。
日志文件分析(定期检查Oracle生成的日志文件,定期备份、删除)。
硬盘空间维护(定期对Oracle 对象使用的空间情况进行监视)。
三、Oracle数据库优化前后比较
1、批量重复的SQL语句执行性能
根据网元数量,各地的执行的完成时间有所区别。
? 用于数据合并和汇总的Procedure的计算性能
通过statspack的周期性采集数据,我们可以使用以下语句,计算我们想统计的Procedure的执行情况:
SELECT TO_CHAR(sn.snap_time,'yyyy-mm-dd hh24:mi:ss') AS snap_time,s.disk_reads,
s.buffer_gets,s.elapsed_time/1000000 AS elapsedtime
FROM
(SELECT hash_value,sql_text,address,last_snap_id
FROM STATS$SQLTEXT WHERE piece = 0 AND sql_text LIKE '%&sqltext_key%') t,
(SELECT address,hash_value,snap_id,sql_text,disk_reads,executions,
buffer_gets,rows_processed,elapsed_time
FROM STATS$SQL_SUMMARY) s,STATS$SNAPSHOT sn
WHERE s.hash_value = t.hash_value
AND s.address = t.address
AND s.snap_id = t.last_snap_id
AND sn.snap_id = s.snap_id;
比如,我们以perfstat用户执行该SQL,输入“to_comp”,可以观察到数据库里保存的有的to_comp存储过程的执行时间,我们发现,其执行时间,从优化前的几千秒,最后稳定在优化后的几十秒。
注:to_comp是整体调用执行一次所有网元的数据合并和汇总的procedure。
? 用于小区分析数据的Procedure的计算性能
使用上面的方法,我们一样可以知道,小区分析的procedure执行,从优化前的约几千秒,最后稳定在优化后的几十秒。
? 批量数据采集入库性能
使用bcp,能从以前约15分钟,减少到约4分钟。
2、一些单次、不常用的操作的语句执行性能
GUI上的性能数据查询,告警数据查询,响应时间都极快,几乎不再出现长时间等待响应的情况。
四、参考
1、常用的优化工具
statspack
sql*plus
TOAD
2、参考文献
Oracle online document Oracle company release
  评论这张
 
阅读(186)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017