【数据库建设及优化的一般思路】：
1.安装过程
Oracle官方对于ORACLE的通用最佳实践提供的非常详细，针对不同平台、针对不同版本、针对不同用途等都会有相应一套实施的最佳实践。
例如：
1）RAC 和Oracle Clusterware 最佳实践和初学者指南（平台无关部分）
Document 810394.1
RAC and Oracle Clusterware Best Practices and Starter Kit (Platform Independent)
2）特定平台的详细最佳实践
Document 811306.1
RAC and Oracle Clusterware Best Practices and Starter Kit (Linux)
3）操作系统配置注意事项
4）虚拟化注意事项
5）存储注意事项
6）网络注意事项
7）特定硬件注意事项
2.测试及系统上线之前
这个过程当中，根据特定的应用场合及测试结果以及我们对数据库理解的不同可能会产生一些以行业背景为区分的行业经验及行业实践。
本次活动也在借此机会进行总结和整理形成一些初步的经验点的积累，具体问题及解答聚焦在如下几个方面：
1）关于重做日志的配置优化应该做哪些点？应该如何做？
首先、接触过数据库的人相信对这个概念都不陌生。数据库在做SQL更新的时候，首先要将事务执行过程记入重做日志当中，然后才会把日志刷入磁盘，将数据更新持久化。一条数据提交之后成功的标准时日志落到磁盘，而不是真正的数据落盘。因此日志的配置（大小、数量）直接决定着数据库读写的性能，如果日志大小非常大，那么会造成归档切换时间非常长，一旦这时候发生了不可恢复的DB灾难，那么通过备份恢复的数据流失量或者说RPO就会较大。日志大小非常小的话，势必会造成日志频繁切换，AWR里面有大量的日志切换事件，这样对数据库的性能会有较大影响。因此根据性能测试的AWR报告中日志切换的等待事件、和切换频度来决定其数据量和大小是否需要调整。一般的OLTP建议（10组、500M）。接着，我们还需要考虑与其相关的参数设置。
比如说“_use_adaptive_log_file_sync”，它直接决定了日志落盘的方式，对于日志缓冲区的数据落盘的方式，11g增加一种新的方式就是polling的方式，传统方式是post/wait方式。oracle底层自动判断何
时用何种方法来完成lgwr进程的写任务。对于post/wait方式来讲，客户端做了commit之后，需要等待事件完成。oracle一旦完成会通知用户进程，用户进程立刻感知。但是这一通知post，会耗费大量CPU 资源。polling是oracle前台进程启动检查任务，自动检查后台lgwr写入情况，耗费CPU资源比较少，但是用户进程并不一定能立刻感知。所以两种方法各有千秋。但是关键是后台实现两种方法切换的时候要耗费系统性能，尤其在繁忙的时候频繁切换的话反而会导致数据库性能下降。awr出现大量‘Log file sync’。Bug 13707904。
比如说“archive_lag_target”，它决定了我们是否开启日志强制切换功能，为了减少故障时数据损失，可以设置ARCHIVE_LAG_TARGET参数，强制进行日志切换。这个参数的缺省值是0，即为不启用该参数。建议设置值为1800。
2）关于ORACLE的内存管理应该关注那些点？应该如何配置？
首先、ORACLE通用的两种内存管理方式AMM&ASMM，从Oracle 11g开始，ORACLE默认使用AMM(自动内存管理)，即让数据库完全管理SGA、PGA的大小，而对于管理员只需要设置一个总的大小（memory_target），数据库会动态的调整SGA、PGA的大小以及其中包含的各个组件大小，如Database buffer cache、Shared pool等。这个特性设计的初衷是好的，它希望避免不正确的SGA和PGA设置导致的内存使用不平衡的性能问题。但是在实际应用过程中，这个特性是不是一定非常出色呢？AMM中在数据库启动是会有一个固定比例来分配SGA/PGA 大小：sga_target =memory_target *60%
pga_aggregate_target=memory_target *40%。
但是在并发较高，数据库非常繁忙的场合下，自动内存调整的速度很可能赶不上大量会话对内存的请求的速度。另外当PGA随着会话不断增加而需求量猛增的情况下，它会首先抢占SGA，导致数据库性能故障。
在高并发的数据库场景中并不建议使用AMM。采用10g更为成熟的自动共享内存管理(ASMM)和自动PGA管理。手动调整内存参数，具体可以参照以下：
//关闭内存自动管理
memory_target=0
memory_max_target=0
//设置SGA为固定值，可以根据性能测试中的AWR报告中的建议
sga_max_size=XG
sga_target=XG
//设置PGA等参数
pga_aggregate_target=XG
large_pool_size=256M
另外很重要的一个参数，“_shared_pool_reserved_pct”，如果这个参数设置小了，很可能导致ORA04031，TROUBLESHOOTING ORA-4031 - Simple Guide and Basic Approach to Solve the issue (文档ID 1416817.1)
3）关于Linux系统下的大页配置？
在Linux 环境中实施HugePage 能够极大地提高内核性能。对于内存较大的系统，效果尤其明显。一般而言，系统中的RAM 越大，系统启用Hugepage 后获得的好处也越大。这是因为内核为映射和维护
内存页表所要做的工作量会随着系统内存的增大而增加。启用Hugepage 能够显著地降低内核要管理的页面数，而且能提高系统的效率。经验表明，如果未启用Hugepage，内核挤占关键的Oracle Clusterware 或Real Application Clusters 守护进程的情况会很常见，而这会导致实例或节点驱逐出现。具体配置方法可以参照：HugePages on Linux: What It Is... and What It Is Not... (文档ID 361323.1) 4）关于SQL解析相关的参数优化？
首先、在Oracle中每条SQL语在执行之前都需要经过解析，这里面又分为软解析和硬解析。在Oracle
中存在两种类型的SQL语句，一类为DDL语句(数据定义语言)，他们是从来不会共享使用的，也就是每次执行都需要进行硬解析。还有一类就是DML语句(数据操纵语言)，他们会根据情况选择要么进行硬解析，要么进行软解析。
一般我们希望我们的AWR报告中硬解析偏少，而软解析偏多。因为硬解析的代价会非常高。为了减少带绑定变量的sql的解析时间，oracle 9i引入的绑定变量窥测的功能。也就是在同一个SQL的变量被赋于不同值时采用同一个游标，这样虽然节省了sql的解析时间。大家有没有通过功能的打开或者关闭实际观察过AWR中的软硬解析数目的实际状况呢？其实对于绑定变量窥测这个特性以及后来的自适应游标等特性，都是oracle为了找到最优执行计划而启用的一些新特性，但是在实际应用过程中，对于不同量级不
同特性的业务场景也曾经因此出现了很多bug。
understanding and Diagnosing ORA-00600 [12333] / ORA-3137 [12333] Errors (ID 389713.1)
根据自己的业务系统特点，做大量的性能测试和业务测试，根据参数的关闭打开对比awr报告当中显示出的软硬解析比率以及执行计划数据决定是否打开或者关系相应功能特性。如下参数：
"_optim_peek_user_binds"
"_optimizer_adaptive_cursor_sharing"
"_optimizer_extended_cursor_sharing"
"_optimizer_extended_cursor_sharing_rel"
"_optimizer_use_feedback"
接着，与之相关的几个参数：open_cursors、session_cached_cursors 这两个参数决定着应用会话可以控制打开以及缓存的游标数量，如果数量不足，就会引起SQL解析的性能问题。这两个参数要根据
v$resource_limit视图中的值的情况进行调整，避免资源设置不合理导致的性能问题。
还有，与执行解析执行计划相关的几个参数，_b_tree_bitmap_plans、有时将B-Tree索引进行BITMAP 转换来进行SQL执行，往往会生成极其恶劣的执行计划，导致CPU100%。
Select Fails With ORA-600 [20022] (文档ID 1202646.1)
建议可以关掉。
5）如何避免数据库集群节点之间的激烈竞争？
数据库节点之间的竞争有很多，包括锁（各种粒度锁）的竞争以及数据的传输等。完全避免竞争那就失去了RAC的意义了，RAC本身就是希望能在两个节点并行执行任务。
如果特别极致的并行一定引起严重的性能问题，如果完全禁止，既无法做到又失去了集群本来的意义。所以我们只能在一定程度上去平衡：
首先、关于DRM，oracle的DRM特性从理论上来看，它是为了避免节点间的数据量传输，避免节点间的锁等待事件频繁发生。DRM的极致是做到请求节点和Master节点统一化。但是实践中，这个特性引起了很多的BUG、反而导致了节点间的竞争出现了性能故障。Bug 6018125 - Instance crash during dynamic remastering or instance reconfiguration (Doc ID 6018125.8)。所以建议关闭。
接着、关于参数“parallel_force_local”，ORACLE RAC为了实现多节点并行处理是花费了很大代价的，假设一个集群当中有三个节点，对于某一个数据块儿读写，有一个Master、有一个请求者、有一个拥有者，请求者向Master请求数据块儿的最新版本，Master把请求转发给拥有者，拥有者按照请求信息把数据块儿传送给申请者，然后加锁进行读写。这一过程是需要有大量的数据传输和竞争存在的，一旦这个事情成为多数，那么势必造成节点间的通讯负载过大，造成大量的锁等待时间，严重影响数据库整体性能。尤其是在做跨数据中心高可用的场合下。因此我们只要做到业务级别的并发处理，而不要追求一个SQL 级别的绝对并发。物极必反的道理就在于此。因此把参数打开，使得进程级别并发实现本地化处理，不要跨节点处理。在官方文档ID 1536272.1当中，必须优化的参数就包括这个。
6）关于数据库的自动任务？
Oracle 11g 数据库有三个预定义自动维护任务：
Automatic Optimizer Statistics Collection（自动优化器统计信息收集）：
收集数据库中所有无统计信息或仅有过时统计信息的Schema 对象的Optimizer（优化器）统计信息。QL query optimizer（SQL 查询优化器）使用此任务收集的统计信息提高SQL 执行的性能。Automatic Segment Advisor（自动段指导）：
识别有可用回收空间的段，并提出如何消除这些段中的碎片的建议。您也可以手动运行Segment Advisor 获取更多最新建议，或获取Automatic Segment Advisor 没有检查到的那些有可能做空间回收的段的建议。
Automatic SQL Tuning Advisor（自动SQL 优化指导）：检查高负载SQL 语句的性能，并提出如何优化这些语句的建议。您可以配置此指导，自动应用建议的SQL profile。
关于统计信息收集，数据库是有其自己的默认启动时间，11g是在22:00-2:00之间，假设这个时间跟我们的跑批时间有冲突的话，我们可以修改器具体执行时间。但是这个任务必须保留。
关于其他的两个优化指导，其实要看我们实际工作中用到的几率是否很高，是否有价值留着给我们提供一些优化的理论指导。一般感觉用不好的话意义不大，还不如不用。
7）关于安全方面的几个配置优化？
首先，是数据库要不要保留审计？如何保留。假设不打开，那么将来出来安全问题，我们无法寻找线索；假设打开，那么很可能因为使得审计日志占用大量的存储空间，甚至影响数据库IO性能。一般情况下还是需要对一些基本登录行为的审计，但是我们可以把日志位置修改制定到操作系统层面减少数据库层因此的性能压力，而且应该定期转储，减少碎文件太多而把文件系统i节点用光的极端情况。可以通过对参数"AUDIT_TRAIL"以及adump参数的调整来实现此项优化。
接着，alert日志和trace文件的控制参数。
“MAX_DUMP_FILE_SIZE”，它决定了这些文件的大小限制，默认情况下是unlimited，如果生成了很大的文件，就会达到OS对文件上限的要求，导致写入失败。
最后，所有这些重定给OS或者本来就依靠OS的日志文件也好、审计文件也好。一定得注意其对OS的i节点资源使用情况的一个把握，不要出现df -h正常但是df -i 不正常的情况。这个往往是非常容易忽视的一点。无论是从监控上还是从OS对用户资源参数的限定上都要有一个明确的把握。
8）关于ADG的关注点？
ADG本身作为容灾的一个手段，那么其本身会有很多点需要我们监控。比如说主备库的状态、日志的切换状况、数据之间有没有GAP等等。但是我想说的是我们非常容易忽略的地方。
首先，关于备库的RMAN参数设置，
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
这个参数设置是保护没有被应用的日志不被删除，在11g的高版本实际上已经不需要再设置了，但是低版本的就需要注意了。具体可以参照文档ID 1577382.1
9）其他在管理数据库时应该注意的点？
例如：
表空间的数据文件是否采用了自动扩展的方式？
表空间的数据文件是否都用了ASM的方式？
ASM的冗余方式是否一致？
应用用户的默认密码策略是不是已经取消了180天的限制等等。
数据库的监控指标是否覆盖了（集群、服务、监听、ASM、表空间、性能等所有应该涵盖的方面）？
OS层面的监控是否已经启用？尤其是私网之间的通讯、CPU、内存的监控等？是Nmon还是osw，他们的日志是定期循环还是持续不断增长等等？
数据库巡检的体系是否完善？日巡检月度巡检的内容是否经过精心设计？是否已经实现了自动化等等？强烈建议日巡检工作实现脚本自动化，任务定时执行，日志统一整合到共享文件系统上，有条件的可以进行整合入库，按照自己的巡检机制和体系实现按需调入调出。
大型ORACLE数据库优化设计方案 本文主要从大型数据库ORACLE环境四个不同级别的调整分析入手，分析ORACLE的系统结构和工作机理，从九个不同方面较全面地总结了ORACLE数据库的优化调整方案。 对于ORACLE数据库的数据存取，主要有四个不同的调整级别，第一级调整是操作系统级 包括硬件平台,第二级调整是ORACLE RDBMS级的调整,第三级是数据库设计级的调整,最后一个调整级是SQL级。通常依此四级调整级别对数据库进行调整、优化，数据库的整体性能会得到很大的改善。下面从九个不 同方面介绍ORACLE数据库优化设计方案。 一.数据库优化自由结构OFA(Optimal flexible Architecture) 数据库的逻辑配置对数据库性能有很大的影响,为此,ORACLE公司对表空间设计提出了一种优化结构OFA。使用这种结构进行设计会大大简化物理设计中的数据管理。优化自由结构OFA,简单地讲就是在数据库中可以高效自由地分布逻辑数据对象,因此首先要对数据库中的逻辑对象根据他们的使用方式和物理结构对数据库的影响来进行分类,这种分类包括将系统数据和用户数据分开、一般数据和索引数据分开、低活动表和高活动表分开等等。数据库逻辑设计的结果应当符合下面的准则：(1)把以同样方式使用的段类型存储在一起； (2)按照标准使用来设计系统；(3)存在用于例外的分离区域；(4)最小化表空间冲突；(5)将数 据字典分离。 二、充分利用系统全局区域SGA(SYSTEM GLOBAL AREA) SGA是oracle数据库的心脏。用户的进程对这个内存区发送事务，并且以这里作为高速缓存读取命中的数据，以实现加速的目的。正确的SGA大小对数据库的性能至关重要。SGA 包括以下几个部分： 1、数据块缓冲区(data block buffer cache)是SGA中的一块高速缓存，占整个数据库大小 的1%-2%，用来存储从数据库重读取的数据块(表、索引、簇等)，因此采用least recently used (LRU,最近最少使用)的方法进行空间管理。 2、字典缓冲区。该缓冲区内的信息包括用户账号数据、数据文件名、段名、盘区位置、表 说明和权限，它也采用LRU方式管理。 3、重做日志缓冲区。该缓冲区保存为数据库恢复过程中用于前滚操作。 4、SQL共享池。保存执行计划和运行数据库的SQL语句的语法分析树。也采用LRU算法 管理。如果设置过小，语句将被连续不断地再装入到库缓存，影响系统性能。 另外，SGA还包括大池、JAVA池、多缓冲池。但是主要是由上面4种缓冲区构成。对这
o r a c l e数据库优化报告公司内部编号：（GOOD-TMMT-MMUT-UUPTY-UUYY-DTTI-
oracle数据库 优化报告 目录 1、概述 随着应用软件用户负载的增加和愈来愈复杂的应用环境，操作系统的各项性能参数、数据库的使用效率、用户的响应速度、系统的安全运行等性能问题逐渐成为系统必须考虑的指标之一。性能测试以及优化通常通过自动化的测试工具模拟多种正常、峰值以及异常负载条件来对系统的各项性能指标进行测试，用来检测系统是否达到用户提出的性能指标，及时发现系统中存在的瓶颈，最后起到优化系统的目的。
随着需求不断增加，特别是复杂逻辑的需求，一旦出现高并发量时，也将可能导致数据库主机无法承载，因此数据库优化亟待解决。 2、数据库优化部分 从2018年1月份开始跟踪及分析，发现托管区数据库在环境、设计及SQL 三方面，都存在不少问题。在SQL类优化中，本地化代码编写和设计不良，是比较明显的问题。下面将分成环境、设计、SQL优化三类进行持续分析，并给出相关建议、整改方案、整改进度。 、环境优化 被关闭 zonghe托管区数据库统计信息未自动收集，如果未打开收集，会对系统性能造成较大的影响。 需要开启统计信息 开启方法如下： --执行 BEGIN (client_name => 'auto optimizer statscollection', operation => NULL, window_name =>NULL); END;
部分索引失效 需要将索引进行删除。删除命令参考如下： drop index index_name; 、设计优化 设计类问题概述 设计类问题优化建议 1、对于表的创建开发人员需要与业务人员确认后再定义 2、经常与其他表进行连接的表，在连接字段上应该建立索引 3、索引应该建在选择性高的字段上。例如：表示性别的数据列，由于只有男女两种值，就属于选择性低
大型ORACLE数据库优化设计方案 摘要主要从大型数据库ORACLE环境四个不同级别的调整分析入手，分析ORACLE的系统结构和工作机理，从九个不同方面较全面地总结了ORACLE数据库的优化调整方案。 关键词ORACLE数据库环境调整优化设计方案 对于ORACLE数据库的数据存取，主要有四个不同的调整级别，第一级调整是操作系统级包括硬件平台,第二级调整是ORACLERDBMS级的调整,第三级是数据库设计级的调整,最后一个调整级是SQL级。通常依此四级调整级别对数据库进行调整、优化，数据库的整体性能会得到很大的改善。下面从九个不同
方面介绍ORACLE数据库优化设计方案。 一.数据库优化自由结构OFA(OptimalflexibleArchitecture) 数据库的逻辑配置对数据库性能有很大的影响,为此,ORACLE公司对表空间设计提出了一种优化结构OFA。使用这种结构进行设计会大大简化物理设计中的数据管理。优化自由结构OFA,简单地讲就是在数据库中可以高效自由地分布逻辑数据对象,因此首先要对数据库中的逻辑对象根据他们的使用方式和物理结构对数据库的影响来进行分类,这种分类包括将系统数据和用户数据分开、一般数据和索引数据分开、低活动表和高活动表分开等等。 二、充分利用系统全局区域SGA （SYSTEMGLOBALAREA) SGA是oracle数据库的心脏。用户的进程对这个内存区发送事务，并且以这里作为高速缓存读取命中的数据，以实现加速的目的。正确的SGA大小对数据库
的性能至关重要。SGA包括以下几个部分： 2、字典缓冲区。该缓冲区内的信息包括用户账号数据、数据文件名、段名、盘区位置、表说明和权限，它也采用LRU 方式管理。 3、重做日志缓冲区。该缓冲区保存为数据库恢复过程中用于前滚操作。 4、SQL共享池。保存执行计划和运行数据库的SQL语句的语法分析树。也采用LRU算法管理。如果设置过小，语句将被连续不断地再装入到库缓存，影响系统性能。 另外，SGA还包括大池、JA V A池、多缓冲池。但是主要是由上面4种缓冲区构成。对这些内存缓冲区的合理设置，可以大大加快数据查询速度，一个足够大的内存区可以把绝大多数数据存储在内存中，只有那些不怎么频繁使用的数据，才从磁盘读取，这样就可以大大提高内存区的命中率。三、规范与反规范设计数据库
Oracle SQL的优化 标签：oraclesql优化date数据库subquery 2009-10-14 21:18 18149人阅读评论(21) 收藏举报分类： Oracle Basic Knowledge（208） SQL的优化应该从5个方面进行调整： 1.去掉不必要的大型表的全表扫描 2.缓存小型表的全表扫描 3.检验优化索引的使用 4.检验优化的连接技术 5.尽可能减少执行计划的Cost SQL语句： 是对数据库(数据)进行操作的惟一途径； 消耗了70%~90%的数据库资源；独立于程序设计逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低； 可以有不同的写法；易学，难精通。 SQL优化： 固定的SQL书写习惯，相同的查询尽量保持相同，存储过程的效率较高。 应该编写与其格式一致的语句，包括字母的大小写、标点符号、换行的位置等都要一致 ORACLE优化器： 在任何可能的时候都会对表达式进行评估，并且把特定的语法结构转换成等价的结构，这么做的原因是 要么结果表达式能够比源表达式具有更快的速度 要么源表达式只是结果表达式的一个等价语义结构 不同的SQL结构有时具有同样的操作（例如： = ANY (subquery) and IN (subquery)），ORACLE会把他们映射到一个单一的语义结构。 1 常量优化： 常量的计算是在语句被优化时一次性完成，而不是在每次执行时。下面是检索月薪大于2000的的表达式：
sal > 24000/12 sal > 2000 sal*12 > 24000 如果SQL语句包括第一种情况，优化器会简单地把它转变成第二种。 优化器不会简化跨越比较符的表达式，例如第三条语句，鉴于此，应尽量写用常量跟字段比较检索的表达式，而不要将字段置于表达式当中。否则没有办法优化，比如如果sal上有索引，第一和第二就可以使用，第三就难以使用。 2 操作符优化： 优化器把使用LIKE操作符和一个没有通配符的表达式组成的检索表达式转换为一个“=”操作符表达式。 例如：优化器会把表达式ename LIKE 'SMITH'转换为ename = 'SMITH' 优化器只能转换涉及到可变长数据类型的表达式，前一个例子中，如果ENAME 字段的类型是CHAR(10)，那么优化器将不做任何转换。 一般来讲LIKE比较难以优化。 其中： ~~IN 操作符优化： 优化器把使用IN比较符的检索表达式替换为等价的使用“=”和“OR”操作符的检索表达式。 例如，优化器会把表达式ename IN ('SMITH','KING','JONES')替换为 ename = 'SMITH' OR ename = 'KING' OR ename = 'JONES‘ oracle 会将in 后面的东西生成一存中的临时表。然后进行查询。 如何编写高效的SQL: 当然要考虑sql常量的优化和操作符的优化啦，另外，还需要： 1 合理的索引设计： 例：表record有620000行，试看在不同的索引下，下面几个SQL的运行情况：语句A SELECT count(*) FROM record WHERE date >'19991201' and date <'19991214‘and amount >2000 语句B
oracle数据库 优化报告
目录 1、概述 (3) 2、数据库优化部分 (3) 2.1、环境优化 (3) 2.1.1 统计信息收集被关闭 (3) 2.1.2 部分索引失效 (4) 2.2、设计优化 (4) 2.2.1 设计类问题概述 (4) 2.2.2 设计类问题优化建议 (5) 2.3、SQL优化 (5) 2.3.1 SQL_ID= 7gf3typgc469a (5) 2.3.2 SQL_ID= bdcfdz26x5hm9 (6) 3、数据库优化总结 (7)
1、概述 随着应用软件用户负载的增加和愈来愈复杂的应用环境，操作系统的各项性能参数、数据库的使用效率、用户的响应速度、系统的安全运行等性能问题逐渐成为系统必须考虑的指标之一。性能测试以及优化通常通过自动化的测试工具模拟多种正常、峰值以及异常负载条件来对系统的各项性能指标进行测试，用来检测系统是否达到用户提出的性能指标，及时发现系统中存在的瓶颈，最后起到优化系统的目的。 随着需求不断增加，特别是复杂逻辑的需求，一旦出现高并发量时，也将可能导致数据库主机无法承载，因此数据库优化亟待解决。 2、数据库优化部分 从2018年1月份开始跟踪及分析，发现托管区数据库在环境、设计及SQL三方面，都存在不少问题。在SQL类优化中，本地化代码编写和设计不良，是比较明显的问题。下面将分成环境、设计、SQL优化三类进行持续分析，并给出相关建议、整改方案、整改进度。 2.1、环境优化 2.1.1 被关闭 zonghe托管区数据库统计信息未自动收集，如果未打开收集，会对系统性能造成较大的影响。
需要开启统计信息 开启方法如下： --执行 BEGIN dbms_auto_task_admin.enable(client_name => 'auto optimizer statscollection', operation => NULL, window_name =>NULL); END; 2.1.2 部分索引失效 需要将索引进行删除。删除命令参考如下： drop index index_name; 2.2、设计优化 2.2.1 设计类问题概述 序号 类型 问题描述 1 表 ZJ_KZH_DATE 、ZJ_CRM_S_ORDER_GATHER 等本 地表，设计了大量的V1，V2，需要开发人员核对需 求 2 索引 索引定义较混乱，常与其他表进行连接的表，在连接
Oracle SQL性能优化方法探讨 Oracle性能优化方法(SQL篇) (1) 1综述 (2) 2表分区的应用 (2) 3访问Table的方式 (3) 4共享SQL语句 (3) 5选择最有效率的表名顺序 (5) 6WHERE子句中的连接顺序． (6) 7SELECT子句中幸免使用’*’ (6) 8减少访问数据库的次数 (6) 9使用DECODE函数来减少处理时刻 (7) 10整合简单,无关联的数据库访问 (8) 11删除重复记录 (8) 12用TRUNCATE替代DELETE (9) 13尽量多使用COMMIT (9) 14计算记录条数 (9) 15用Where子句替换HAVING子句 (9) 16减少对表的查询 (10) 17通过内部函数提高SQL效率 (11)
18使用表的不名(Alias) (12) 19用EXISTS替代IN (12) 20用NOT EXISTS替代NOT IN (13) 21识不低效执行的SQL语句 (13) 22使用TKPROF 工具来查询SQL性能状态 (14) 23用EXPLAIN PLAN 分析SQL语句 (14) 24实时批量的处理 (16)
1综述 ORACLE数据库的性能调整是个重要，却又有难度的话题，如何有效地进行调整，需要通过反反复复的过程。在数据库建立时，就能依照顾用的需要合理设计分配表空间以及存储参数、内存使用初始化参数，对以后的数据库性能有专门大的益处，建立好后，又需要在应用中不断进行应用程序的优化和调整，这需要在大量的实践工作中不断地积存经验，从而更好地进行数据库的调优。 数据库性能调优的方法 ●调整内存 ●调整I/O ●调整资源的争用问题 ●调整操作系统参数 ●调整数据库的设计 ●调整应用程序 本文针对应用程序的调整，来讲明对数据库性能如何进行优化。 2表分区的应用 关于海量数据的表，能够考虑建立分区以提高操作效率。建
1系统问题 XX公司BI系统上线运行以来，客户反映系统目前存在着下面的几个问题，涉及到数据库和ETL. 问题一：表空间增长太快，每个月需增加3—5G空间。 问题二：ETL JOB会经常导致数据库产生表空间不足错误。 2系统优化分析 2.1分析思路 要解决表空间的问题，我们必须搞清楚下面几个问题： 思路一：真正每个月数据仓库增量是多少空间 目的：得出一个正确的月表空间增长量。 思路二：目前的数据仓库表空间是是如何分布的。 目的：找出那些对象是最占空间，分析其合理性。 2.2分析过程 要得到真实的数据分布必须对表进行分析，首先需要对数据仓库的oracle数据库进行表分析，。执行下面脚本可以对数据库进行表分析。
脚本一 analyze table SA_IMS_PRODUCT_GROUP compute statistics; analyze table SA_CONSUMP_ACT_DEL compute statistics; analyze table SA_FINANCE_ACT compute statistics; analyze table SA_CONSUMP_TGT_DEL compute statistics; analyze table SA_FACT_IS compute statistics; analyze table SA_CPA compute statistics; analyze table SA_REF_TERR_ALIGNMENT_DEL compute statistics; analyze table SA_IMS_MTHLC_BK compute statistics; analyze table SA_IMS_CHPA compute statistics; analyze table SA_FINANCE_PNL compute statistics; analyze table SA_CUST_TARG_SEG compute statistics; analyze table SA_CONSUMP_ACT compute statistics; analyze table SA_FINANCE_BS compute statistics; analyze table SA_FINANCE_BGT_QTY compute statistics; analyze table SA_CONSUMP_ACT0423 compute statistics; analyze table SA_CALLS compute statistics; analyze table SA_COMPANY_DAILY_SALES_ALL compute statistics; analyze table SA_IMS_MTHLC compute statistics; analyze table SA_IMS_MTHUS compute statistics; analyze table SA_CONSUMP_TGT compute statistics; analyze table TEST_TABLE compute statistics; analyze table SA_DOCTOR_CYCLE_EXTRACT compute statistics; analyze table SA_EXCHANGE_ACT compute statistics;
ORACLE优化常用技巧 多表查询优化 注：这里提供的是编程时SQL语句的执行性能的优化，而不是后台数据库的优化。 执行路径:ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用:我们发现,单表数据的统计比多表统计的速度完全是两个概念.单表统计可能只要0.02秒,但是2张表联合统计就可能要几十表了.这是因为ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询..数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了. 当你向ORACLE提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句. 这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须 完全相同(包括空格,换行等). 共享的语句必须满足两个条件: A.字符级的比较: 当前被执行的语句和共享池中的语句必须完全相同. 例如: SELECT * FROM EMP; 和下列每一个都不同 SELECT * from EMP; Select * From Emp; SELECT * FROM EMP; B.两个SQL语句中必须使用相同的名字的绑定变量(bind variables) 例如：第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不同的 a. select pin , name from people where pin = :blk1.pin; select pin , name from people where pin = :blk1.pin; b. select pin , name from people where pin = :blk1.ot_ind; select pin , name from people where pin = :blk1.ov_ind; 重点关注1：选择最有效率的表名顺序 ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并. 例如: 表TAB1 16,384 条记录 表TAB2 1 条记录
1.1 使用绑定变量(已测试) 动态sql使用绑定变量 begin for i in 1..100000 loop execute immediate'insert into t values(:x)'using i;--绑定变量 end loop; end ; 所用时间：3.562 s begin for i in 1..100000 loop execute immediate'insert into t values('||i||')';--未使用绑定变量 end loop; end ; 所用时间：55.781s 1.2 WHERE子句中的连接顺序(已测试) 所用时间：0.015s SELECT * FROM EMP E WHERE SAL > 1000 AND JOB = 'MANAGER' AND 1 < (SELECT COUNT(*) FROM EMP WHERE MGR = E.EMPNO); 所用时间：0.001s SELECT * FROM EMP E WHERE 1 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 1000 AND JOB = 'MANAGER';
1.3 SELECT子句中避免使用‘ * ’ (已测试) 当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用‘*’ 是一个方便的方法。不幸的是，这是一个非常低效的方法。实际上，ORACLE在解析的过程中，会将‘*’ 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。 1.4 减少访问数据库的次数(已测试) 当执行每条SQL语句时， ORACLE在内部执行了许多工作：解析SQL语句，估算索引的利用率，绑定变量，读数据块等等。由此可见，减少访问数据库的次数，就能实际上减少ORACLE的工作量。 例如，以下有三种方法可以检索出雇员号等于0342或0291的职员。 方法1 (最低效) 分两个查询 SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 342; SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 291; 方法2 (次低效) 用游标 DECLARE CURSOR C1 (E_NO NUMBER) IS SELECT EMP_NAME,SALARY,GRADE FROM EMP WHERE EMP_NO = E_NO; BEGIN OPEN C1(342); FETCH C1 INTO …,..,.. ; OPEN C1(291); FETCH C1 INTO …,..,.. ; CLOSE C1; END; 方法3 (高效) 作为两个表查最高效 SELECT A.EMP_NAME , A.SALARY , A.GRADE, B.EMP_NAME , B.SALARY , B.GRADE FROM EMP A,EMP B WHERE A.EMP_NO = 342