1.ORACLE的优化器共有3种
A、RULE (基于规则) b、COST (基于成本) c、CHOOSE (选择性)
设置缺省的优化器，可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明，如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROWS 。你当然也在SQL句级或是会话(session)级对其进行覆盖。
为了使用基于成本的优化器(CBO，Cost-Based Optimizer) ，你必须经常运行analyze 命令，以增加数据库中的对象统计信息(object statistics)的准确性。
如果数据库的优化器模式设置为选择性(CHOOSE)，那么实际的优化器模式将和是否运行过analyze 命令有关。如果table已经被analyze过，优化器模式将自动成为CBO ，反之，数据库将采用RULE 形式的优化器。
在缺省情况下，ORACLE采用CHOOSE优化器，为了避免那些不必要的全表扫描(full table scan) ，你必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。
2.访问Table的方式
ORACLE 采用两种访问表中记录的方式：
A、全表扫描
全表扫描就是顺序地访问表中每条记录。ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。
B、通过ROWID访问表
你可以采用基于ROWID的访问方式情况，提高访问表的效率，ROWID包含了表中记录的物理位置信息。ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能上的提高。
3.共享SQL语句
为了不重复解析相同的SQL语句，在第一次解析之后，ORACLE将SQL语句存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。因此，当你执行一个SQL语句(有时被称为一个游标)时，如果它和之前的执行过的语句完全相同，ORACLE就能很快获得已经被解析的语句以及最好的执行路径。ORACLE的这个功能大大地提高了SQL 的执行性能并节省了内存的使用。
可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering)，这个功能并不适用于多表连接查询。
数据库管理员必须在init.ora中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。
当你向ORACLE提交一个SQL语句，ORACLE会首先在这块内存中查找相同的语句。这里需要注明的是，ORACLE对两者采取的是一种严格匹配，要达成共享，SQL语句必须完全相同(包括空格，换行等)。
数据库管理员必须在init.ora中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。
共享的语句必须满足三个条件：
A、字符级的比较：当前被执行的语句和共享池中的语句必须完全相同。
B、两个语句所指的对象必须完全相同：
C、两个SQL语句中必须使用相同的名字的绑定变量(bind variables)。
4.选择最有效率的表名顺序(只在基于规则的优化器中有效)
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表driving table)将被最先处理。在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行派序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。
如果有3个以上的表连接查询，那就需要选择交叉表(intersection table)作为基础表，交叉表是指
那个被其他表所引用的表。
5.WHERE子句中的连接顺序
ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE 条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
6.SELECT子句中避免使用' * '
当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用'*' 是一个方便的方法。不幸的是，这是一个非常低效的方法。实际上，ORACLE在解析的过程中，会将'*' 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。
7.减少访问数据库的次数
当执行每条SQL语句时，ORACLE在内部执行了许多工作：解析SQL语句，估算索引的利用率，绑定变量，读数据块等等。由此可见，减少访问数据库的次数，就能实际上减少ORACLE的工作量。
8.使用DECODE函数来减少处理时间
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。
9.整合简单，无关联的数据库访问
如果你有几个简单的数据库查询语句，你可以把它们整合到一个查询中(即使它们之间没有关系)
10.删除重复记录
11.用TRUNCATE替代DELETE
当删除表中的记录时，在通常情况下，回滚段(rollback segments ) 用来存放可以被恢复的信息。如果你没有COMMIT事务，ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)。
而当运用TRUNCATE时，回滚段不再存放任何可被恢复的信息。当命令运行后，数据不能被恢复。因此很少的资源被调用，执行时间也会很短。
12.尽量多使用COMMIT
只要有可能，在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少
COMMIT所释放的资源：
A、回滚段上用于恢复数据的信息。
B、被程序语句获得的锁。
C、redo log buffer 中的空间。
D、ORACLE为管理上述3种资源中的内部花费。
13.计算记录条数
和一般的观点相反，count(*) 比count(1)稍快，当然如果可以通过索引检索，对索引列的计数仍旧是最快的。例如COUNT(EMPNO)
14.用Where子句替换HAVING子句
避免使用HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。
15.减少对表的查询
在含有子查询的SQL语句中，要特别注意减少对表的查询。
16.通过内部函数提高SQL效率
17.使用表的别名(Alias)
当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
18.用EXISTS替代IN
在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用
EXISTS(或NOT EXISTS)通常将提高查询的效率。
19.用NOT EXISTS替代NOT IN
在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN ，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。
20.用表连接替换EXISTS
通常来说，采用表连接的方式比EXISTS更有效率
21.用EXISTS替换DISTINCT
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时，避免在SELECT子句中使用DISTINCT。一般可以考虑用EXIST替换
22.用索引提高效率：
索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.。那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的.：
ALTER INDEX
23.sql语句用大写的
因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行
24.在java代码中尽量少用连接符“＋”连接字符串！
25.用>=替代>
高效: SELECT * FROM EMP WHERE DEPTNO >=4
低效: SELECT * FROM EMP WHERE DEPTNO >3
两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.
26.用UNION替换OR (适用于索引列)
通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引. 高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE” 低效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE” 如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.
27.避免在索引列上使用IS NULL和IS NOT NULL
避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录.如果至少有一个列不为空，则记录存在于索引中．举例: 如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入). 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000 条具有相同键值的记录,当然它们都是空! 因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使
ORACLE停用该索引.
低效: (索引失效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL; 高效: (索引有效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;
28.用UNION-ALL 替换UNION ( 如果有可能的话)：
当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序. 如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高. 需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录. 因此各位还是要从业务需求分析使用UNION ALL的可行性. UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存. 对于这块内存的优化也是相当重要的. 下面的SQL可以用来查询排序的消耗量
29.需要当心的WHERE子句:
某些SELECT 语句中的WHERE子句不使用索引. 这里有一些例子. 在下面的例子里, (1)…!=' 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中. (2) …||'是字符连接函数. 就象其他函数那样, 停用了索引. (3) …+'是数学函数. 就象其他数学函数那样, 停用了索引. (4)相同的索引列不能互相比较,这将会启用全表扫描.
30.带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序. 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写. 如果你的数据库的SORT_AREA_SIZE调配得好, 使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强
31.优化GROUP BY:
提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多。
ORACLE优化总结和注意事项 本文档中对优化方法进行详述，并对在优化过程中发现的一些问题进行总结。列出ORACLE的一些注意事项 注意事项： 1.安装的过程中，请务必进行正确安装。 2.当安装过程中出现错误的时候，最好清除原有遗留信息，进行重装，否则在数据库运行 的过程中可能会出现各种诡异的问题。 3.当数据库安装的过程中如果有警告信息，请记录下来，存档，方便排查数据库问题 4.安装的过程中请选择OLTP的数据模板Transaction Processing 5.安装过程中文件的创建
Controlfile、Datafiles、Redo Log Groups如果条件允许，最好分别放于不同的磁盘上。其中Controlfile和Redo Log Groups要尽量保证放在不同的磁盘上 6.其中Redo Log Groups重做日志组最好建5组以上，每个文件大小在1G以上，最大不超 过3G，避免出现进行check_point的时候造成buffer wait 导致数据库宕机 7.检查/etc/hosts文件 配置最后一行信息，将当前的主机名和ip配对起来，避免应用服务连接数据库导致的性能损耗 8.安装完成后，请启动数据库确保数据库基本安装成功 步骤： sqlplus /nolog connect /as sysdba startup//启动数据库实例 exit//退出sqlplus lsnrctl start//启动监听
emctl start dbconsole 上述步骤如果执行完，没有报错，则说明数据库基本安装正确，并可正常运行。如果执行上述操作的时候出现了问题，则说明数据库安装的过程中出现了某些问题，即使数据库实例当前可以启动连接，但是在以后稳定服务的过程中也是有可能会出现一些数据库问题的。 配置OCI连接 因为当前应用服务采用OCI连接的方式，因此在运行应用服务之前要配置OCI的连接条件 1、需求软件： 如果应用服务是跟ORACLE数据库安装在一台机器上，则不需要额外软件，直接进入第2步即可 如果应用服务是跟ORACLE数据库分开部署，则需要在部署应用服务的机器上安装一个客户端（精简客户端即可大小几M）需要从官方网站下载三个文件instantclient-basic-linux-x86-64-10.2.0.3-20070103.zip instantclient-sqlplus-linux-x86-64-10.2.0.3-20070103.zip instantclient-jdbc-linux-x86-64-10.2.0.3-20070103.zip 解压到同一个目录中，同时在该目录下新建一个文件tnsnames.ora文件，文件中添加以下内容 # Generated by Oracle configuration tools. HMS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.15.61)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = hms) ) )
OracleSQL性能优化方法 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子句替换HA VING子句 (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表分区的应用 关于海量数据的表，能够考虑建立分区以提高操作效率。建立分区一样以关键字为分区的标志，也能够以其他字段作为分区的标志，但效率不如关键字高。建立分区的语句在建表时能够进行讲明： create table TABLENAME(
ORACLE 数据库性能优化 参考书目： 《ORACLE 9i Database Performance Tuning Guide and Reference》《ORACLE 9i Database Reference》 《ORACLE 9i SQL Reference》 《ORACLE 9i Database Administrator’s Guide》
一、数据库实例创建过程参数确定 在创建数据库实例过程中，需要确定以下几个参数： 1. 数据块大小（DB_BLOCK_SIZE） 该参数指明了ORACLE所处理的数据存贮于数据文档以及SGA内存中的数据块大小。 该参数的可选择的范围为:4k,8k,16k,32k,64k。对于OLTP系统而言，取值可以为4K或8K，对于DSS系统而言，则可以取较大的数据，如32K或64K 建议统一取8K（即8192） 说明 DB_BLOCK_SIZE的大小将影响创建表时的EXTENT的大小。例如指定db_block_size=16K,某表空间的EXTENT MANAGEMENT 为local autoallocate，则其系统将extent的大小最小指定为1M.所以将可能导致空间的浪费。 2. 字符集(Character set) 该参数确定数据库以何种字符集来存贮CHAR以及V ARCHAR、V ARCHAR2等字符类型的值。对于ORACLE数据字典中的字符（如表及字段的COMMENT 内容）具有同样的作用。因此需要考虑如字符集的使用。对于国际项目，因为数据库中的comment内容（包括表及字符、存贮过程中的中文字符等内容）可能性需要以中文存贮，而用户业务数据使用的字符可能性是使用本地的语言，基于此，该参数需要选择支持UNICODE的字符编码的字符集。目前ORACLE9i支持以下二种UNICODE字符集： ?UTF8 ?AL32UTF8 建议统一取AL32UTF8
（O管理）ORACLESL性能优化(内部培训资料)
ORACLESQL性能优化系列(一) 1.选用适合的ORACLE优化器 ORACLE的优化器共有3种: a.RULE(基于规则) b.COST(基于成本) c.CHOOSE(选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS.你当然也在SQL句级或是会话(session)级对其进行覆盖. 为了使用基于成本的优化器(CBO,Cost-BasedOptimizer),你必须经常运行analyze命令,以增加数据库中的对象统计信息(objectstatistics)的准确性. 如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关.如果table已经被analyze过,优化器模式将自动成为CBO,反之,数据库将采用RULE形式的优化器. 在缺省情况下,ORACLE采用CHOOSE优化器,为了避免那些不必要的全表扫描(fulltablescan),你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器.
2.访问Table的方式 ORACLE采用两种访问表中记录的方式: a.全表扫描 全表扫描就是顺序地访问表中每条记录.ORACLE采用一次读入多个数据块(databaseblock)的方式优化全表扫描. b.通过ROWID访问表 你可以采用基于ROWID的访问方式情况,提高访问表的效率,,ROWID包含了表中记录的物理位置信息..ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系.通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高. 3.共享SQL语句 为了不重复解析相同的SQL语句,在第一次解析之后,ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(systemglobalarea)的共享池(sharedbufferpool)中的内存可以被所有的数据库用户共享.因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同,ORACLE就能很快获得已经被解析的语句以及最好的执行路
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.ORACLE的优化器共有3种 A、RULE (基于规则) b、COST (基于成本) c、CHOOSE (选择性) 设置缺省的优化器，可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明，如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROWS 。你当然也在SQL句级或是会话(session)级对其进行覆盖。 为了使用基于成本的优化器(CBO，Cost-Based Optimizer) ，你必须经常运行analyze 命令，以增加数据库中的对象统计信息(object statistics)的准确性。 如果数据库的优化器模式设置为选择性(CHOOSE)，那么实际的优化器模式将和是否运行过analyze 命令有关。如果table已经被analyze过，优化器模式将自动成为CBO ，反之，数据库将采用RULE 形式的优化器。 在缺省情况下，ORACLE采用CHOOSE优化器，为了避免那些不必要的全表扫描(full table scan) ，你必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。 2.访问Table的方式 ORACLE 采用两种访问表中记录的方式： A、全表扫描 全表扫描就是顺序地访问表中每条记录。ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。 B、通过ROWID访问表 你可以采用基于ROWID的访问方式情况，提高访问表的效率，ROWID包含了表中记录的物理位置信息。ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能上的提高。 3.共享SQL语句 为了不重复解析相同的SQL语句，在第一次解析之后，ORACLE将SQL语句存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。因此，当你执行一个SQL语句(有时被称为一个游标)时，如果它和之前的执行过的语句完全相同，ORACLE就能很快获得已经被解析的语句以及最好的执行路径。ORACLE的这个功能大大地提高了SQL 的执行性能并节省了内存的使用。 可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering)，这个功能并不适用于多表连接查询。 数据库管理员必须在init.ora中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。 当你向ORACLE提交一个SQL语句，ORACLE会首先在这块内存中查找相同的语句。这里需要注明的是，ORACLE对两者采取的是一种严格匹配，要达成共享，SQL语句必须完全相同(包括空格，换行等)。 数据库管理员必须在init.ora中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。 共享的语句必须满足三个条件： A、字符级的比较：当前被执行的语句和共享池中的语句必须完全相同。 B、两个语句所指的对象必须完全相同： C、两个SQL语句中必须使用相同的名字的绑定变量(bind variables)。 4.选择最有效率的表名顺序(只在基于规则的优化器中有效) ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表driving table)将被最先处理。在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行派序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。 如果有3个以上的表连接查询，那就需要选择交叉表(intersection table)作为基础表，交叉表是指
ORACLE的优化器共有3种 A、RULE (基于规则) b、COST (基于成本) c、CHOOSE (选择性) 设置缺省的优化器，可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明，如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROWS 。你当然也在SQL句级或是会话(session)级对其进行覆盖。 为了使用基于成本的优化器(CBO， Cost-Based Optimizer) ，你必须经常运行analyze 命令，以增加数据库中的对象统计信息(object statistics)的准确性。 如果数据库的优化器模式设置为选择性(CHOOSE)，那么实际的优化器模式将和是否运行过analyze命令有关。如果table已经被analyze过，优化器模式将自动成为CBO ，反之，数据库将采用RULE形式的优化器。 在缺省情况下，ORACLE采用CHOOSE优化器，为了避免那些不必要的全表扫描(full table scan) ，你必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。 2.访问Table的方式 ORACLE 采用两种访问表中记录的方式： A、全表扫描 全表扫描就是顺序地访问表中每条记录。ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。 B、通过ROWID访问表 你可以采用基于ROWID的访问方式情况，提高访问表的效率， ROWID 包含了表中记录的物理位置信息。ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能上的提高。 3.共享SQL语句 为了不重复解析相同的SQL语句，在第一次解析之后，ORACLE将SQL语句存放在存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的存可以被所有的数据库用户共享。因此，当你执行一个SQL语句(有时被称为一个游标)时，如果它和之前的执行过的语句完全相同， ORACLE就能很快获得已经被解析的语句以及最好的执行路径。ORACLE的这个功能大提高了SQL的执行性能并节省了存的使用。 可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering)，这个功能并不适用于多表连接查询。
y物理模型CheckList （Oracle，性能） 1. 系统级优化 数据库参数配置 合理分配SGA及其内部参数（经验值如下）： SGA=phy*（60%-80%） Share pool=SAG*45% DB Cache=SGA*45% Log Buffer: 1~3M 注：Oracle9i在Windows下有bug，是由Windows下的SGA最大 值有2G的限制造成的 注意调整process和open cursor参数，这两个参数直接影响 数据库的session量 分离表和索引：将表和索引建立在不同的表空间，决不要将 不属于Oracle内部系统的对象存放到SYSTEM表空间。同 时，确保数据表空间和索引表空间置于不同的硬盘，减少I/O 竞争； 如果是企业版数据库，大表可以考虑采取分区存储措施，提 高系统的性能； 优化Export和Import工作：使用较大的BUFFER(比如10MB , 10,240,000)可以提高EXPORT和IMPORT的速度 定期分析查询计划，提高数据库的性能；
2. 索引相关 要对经常查询的字段建立索引，但是由于索引管理的开销， 在增删改操作频繁的情况下避免建立不必要的索引； 对于只读或者接近只读的场合，如数据仓库，对于势值比较 小的列可以考虑使用bitmap索引； 如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. 3. SQL相关 Oracle的From子句表的顺序：记录越多的表放在越前面 （左）； Oracle的where子句表达式的顺序：过滤掉最大数目记录的条 件放到where子句的末尾； Select子句中避免使用‘*’，增加了查询表的列的开销； 在执行结果等效的情况下，使用Truncate代替Delete； 为了在查询过程中要尽量使用索引，对于like语句避免使用 右匹配或者中间匹配的模糊查询； 将过滤条件尽可能放到Where子句中，而不是放到Having子 句中； 在SQL语句中，要减少对表的查询，特别是在含有子查询的 SQL子句中； 使用表的别名可以减少解析的时间并避免引起歧义； 使用exists替代in； 用NOT EXISTS替代NOT IN； 通常情况下，采用表连接的方式比exists更有效率； 当提交一个包含一对多表信息(比如部门表和雇员表)的查询
个人理解，数据库性能最关键的因素在于IO，因为操作内存是快速的，但是读写磁盘是速度很慢的，优化数据库最关键的问题在于减少磁盘的IO，就个人理解应该分为物理的和逻辑的优化，物理的是指oracle产品本身的一些优化，逻辑优化是指应用程序级别的优化物理优化： 一、优化内存
V$ROWCACHE视图结构
3.管理员可以通过下述语句来查看数据缓冲区的使用情况 select name,value from v$sysstat where name in ('db block gets', 'consistent gets ', 'physical reads'); 数据缓冲区使用命中率（physical reads除以db block gets加consistent gets之和)一定要小于10%，否则需要增加数据缓冲区大小 4.管理员可以通过执行下述语句，查看日志缓冲区的使用情况 select name,value from v$sysstat where name in ('redo entries','redo log space requests') 根据查询出的结果可以计算出日志缓冲区的申请失败率：requests除以entries 申请失败率应该解决与0，否则说明日志缓冲区开设太小，需要增加Oracle数据库的日志缓冲区 二、物理I/0的优化 1.在磁盘上建立数据文件前首先运行磁盘碎片整理程序 为了安全地整理磁盘碎片，需关闭打开数据文件的实例，并且停止服务。如果有足够的连续磁盘空间建立数据文件，那么就容易避免数据文件产生碎片。 2.不要使用磁盘压缩（Oracle文件不支持磁盘压缩） 3.不要使用磁盘加密
理解Oracle的日志机制 ? Oracle的日志是用来记录用户对数据库的改变，这样，当出现服务器硬件故障或者用户错误而丢失数据时，可以通过重做这些日志来恢复已提交的事务，Oracle日志机制包含以下组件： ?日志缓存SGA的一部分，用于缓存服务器进程产生的日志，包括DML和DDL； ? LGWR进程这个后台进程负责将日志缓存的数据写到联机日志文件，每个实例只有一个； ?数据库检查点检查点用于同步数据文件和日志文件，一个检查点事件的完成，代表在这个事件开始之前发生的所有对数据文件的改变都已实际记录到了数据文件，数据库在这个时间点是一致的，在实例恢复的时候，只有在最后一个检查点之后的日志才需要重做； ?联机日志文件用于存放从日志缓存中写出的日志数据，每个数据库最少需要两个日志文件，当前日志文件填满以后，发生日志切换，然后才可以继续写下一个日志文件； ?日志归档LGWR写满所有组的联机日志文件以后，会回头再写第一个组的日志文件，在非归档模式下，被重用的日志文件中的日志会被丢弃，在归档模式下，日志文件被重用前会被ARC0进程复制到归档日志文件； ? 一些可选的日志机制，如归档和Standby，因为附加的I/O会降低系统的性能，同时提供了可靠的灾难恢复能力，不建议因这些性能的下降而关闭生产系统的归档功能。 调整日志缓存 ? 日志缓存的管理机制可以类似理解成一个漏斗，日志数据不断地从漏斗上方加入，然后偶尔打开漏斗下方的开关将加入的数据清空，这个开关就是LGWR进程，为了日志缓存有空间容纳不断加进来的日志数据，LGWR在下面列出的任何一个条件下都会执行写出日志缓存的操作： ?应用程序发出Commit命令时； ?三秒间隔已到时； ?日志缓存三分之一满时； ?日志缓存达到1M时； ?数据库检查点发生时； ? 测量日志缓存的性能通过服务器进程放置日志条到日志缓存时发生等待的次数和时间来测量； Select Name, Value From V$sysstat Where Name In ('redo entries', 'redo buffer allocation retries','redo log space requests'); redo entries 服务器进程放进日志缓存的日志条的总数量； redo buffer allocation retries 服务器放置日志条时必须等待然后再重试的次数； redo log space requests LGWR进程写出日志缓存时等待日志切换的次数； 这个查询用于计算日志缓存重试率，这个比率应该小于百分之一； Select Retries.Value / Entries.Value "Redo log Buffer Retry Ratio" From V$sysstat Entries, V$sysstat Retries Where https://www.doczj.com/doc/205204487.html, = 'redo entries' And https://www.doczj.com/doc/205204487.html, = 'redo buffer allocation retries'; 这个查询用来显示哪些会话的LGWR正在进行写等待；
ORACLE SQL性能优化 我要讲的题目是Oracle SQL性能优化，只是Oracle性能优化中的一项。Oracle的性能优化包含很多方面，比如调整物理存取，调整逻辑存取，调整内存使用，减少网络流量等。这里选择SQL性能优化是因为这部分内容我们测试人员最容易接触到，另外开发人员写SQL脚本时有时很随意，不知不觉就会造成程序性能上的下降。 1．选择最有效率的表名顺序(只在基于规则的优化器中有效) ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基 础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描 第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出 的记录与第一个表中合适记录进行合并. 例如: 表 TAB1 16,384 条记录 表 TAB2 1 条记录 选择TAB2作为基础表 (最好的方法) select count(*) from tab1,tab2 执行时间0.96秒 选择TAB2作为基础表 (不佳的方法)
select count(*) from tab2,tab1 执行时间26.09秒 如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表. 例如: EMP表描述了LOCATION表和CATEGORY表的交集. SELECT * FROM LOCATION L , CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN 将比下列SQL更有效率 SELECT * FROM EMP E , LOCATION L , CATEGORY C WHERE E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN AND E.EMP_NO BETWEEN 1000 AND 2000 2．WHERE子句中的连接顺序． ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
--数据库巡检或性能优化方法各异，但首要的是要发现数据库性能瓶颈，系统自带的statspack,或awr太耗时， --以下是本人常用的方法，共享之 --1、查询数据库等待事件top10,关注前前几个等待事件，关注前三个等待事件是否有因果或关联关系 --oracle 9i select t2.event,round(100*t2.time_waited/(t1.w1+t3.cpu),2) event_wait_percent from ( SELECT SUM(time_waited) w1 FROM v$system_event WHERE event NOT IN ('smon timer','pmon timer','rdbms ipc message','Null event','parallel query dequeue','pipe get', 'client message','SQL*Net message to client','SQL*Net message from client','SQL*Net more data from client', 'dispatcher timer','virtual circuit status','lock manager wait for remote message','PX Idle Wait', 'PX Deq: Execution Msg','PX Deq: Table Q Normal','wakeup time manager','slave wait','i/o slave wait', 'jobq slave wait','null event','gcs remote message','gcs for action','ges remote message','queue messages') ) t1, (select * from ( select t.event,t.total_waits,t.total_timeouts,t.time_waited,t.average_wait,rownum num from (select event,total_waits,total_timeouts,time_waited,average_wait from v$system_event where event not in ('smon timer','pmon timer','rdbms ipc message','Null event','parallel query dequeue','pipe get', 'client message','SQL*Net message to client','SQL*Net message from client','SQL*Net more data from client', 'dispatcher timer','virtual circuit status','lock manager wait for remote message','PX Idle Wait', 'PX Deq: Execution Msg','PX Deq: Table Q Normal','wakeup time manager','slave wait','i/o slave wait', 'jobq slave wait','null event','gcs remote message','gcs for action','ges remote message','queue messages') order by time_waited desc ) t) where num<11) t2, (SELECT VALUE CPU FROM v$sysstat WHERE NAME LIKE 'CPU used by this session' ) t3 --oracle10g select t2.event,round(100*t2.time_waited/(t1.w1+t3.cpu),2) event_wait_percent from ( SELECT SUM(time_waited) w1 FROM v$system_event WHERE event NOT IN ('smon timer','pmon timer','rdbms ipc message','Null event','parallel query dequeue','pipe get','client message','SQL*Net message to client','SQL*Net message from client','SQL*Net more data from client','dispatcher timer','virtual circuit status','lock manager wait for remote message','PX Idle Wait','PX Deq: Execution Msg','PX Deq: Table Q Normal','wakeup time manager','slave wait', 'i/o slave wait','jobq slave wait','null event','gcs remote message','gcs for action','ges remote
个人理解，数据库性能最关键的因素在于IO，因为操作存是快速的，但是读写磁盘是速度很慢的，优化数据库最关键的问题在于减少磁盘的IO，就个人理解应该分为物理的和逻辑的优化，物理的是指oracle产品本身的一些优化，逻辑优化是指应用程序级别的优化 物理优化： 一、优化存
3.管理员可以通过下述语句来查看数据缓冲区的使用情况 select name,value from v$sysstat where name in('db block gets','consistent gets','physica l reads'); 数据缓冲区使用命中率（physical reads除以db block gets加consistent gets之和)一定要小于10%，否则需要增加数据缓冲区大小 4.管理员可以通过执行下述语句，查看日志缓冲区的使用情况 select name,value from v$sysstat where name in ('redo entries','redo log space requests') 根据查询出的结果可以计算出日志缓冲区的申请失败率：requests除以entries 申请失败率应该解决与0，否则说明日志缓冲区开设太小，需要增加Oracle数据库的日志缓冲区 二、物理I/0的优化 1.在磁盘上建立数据文件前首先运行磁盘碎片整理程序 为了安全地整理磁盘碎片，需关闭打开数据文件的实例，并且停止服务。如果有足够的连续磁盘空间建立数据文件，那么就容易避免数据文件产生碎片。 2.不要使用磁盘压缩（Oracle文件不支持磁盘压缩） 3.不要使用磁盘加密 加密像磁盘压缩一样加了一个处理层，降低磁盘读写速度。如果担心自己的数据可能泄露，可以使用dbms_obfuscation包和label security选择性地加密数据的敏感部分 4.使用RAID raid使用应注意： 选择硬件raid超过软件raid；日志文件不要放在raid5卷上，因为raid5读性能高而写性能差；把日志文件和归档日志放在与控制文件和数据文件分离的磁盘控制系统上 5.分离页面交换文件到多个磁盘物理卷 跨越至少两个磁盘建立两个页面文件。可以建立四个页面文件并在性能上受益，确保所有页面文件的大小之和至少是物理存的两倍。
Oracle性能优化学习心得 一，优化总的原则 1，查看系统的使用情况 2，查看SGA分配情况，结合系统具体情况进行分析。 3，表的设计分析 4，SQL语句分析 实施要则 1，查看系统的使用情况，CPU占用，内存，I/O读取等 Oracle10G提供的Oracle Enterprise Manager图形化工具中的ADDM 和SQL Tuning Advisor等可以方便的查看系统状况 2，OPS上负载均衡，不同查询用不同Instance 3，提供脚本查看SGA使用情况 4，分析SQL执行情况（trace及其他工具） 实施细节 1，外部调整：我们应该记住Oracle并不是单独运行的。因此我们将查看一下通过调整Oracle服务器以得到高的性能。 2，Row re-sequencing以减少磁盘I/O：我们应该懂得Oracle调优最重要的目标是减少I/O。 3，Oracle SQL调整。Oracle SQL调整是Oracle调整中最重要的领域之一，只要通过一些简单的SQL调优规则就可以大幅度地提升SQL语句的性能，这是一点都不奇怪的。 4，调整Oracle排序：排序对于Oracle性能也是有很大影响的。 5，调整Oracle的竞争：表和索引的参数设置对于UPDATE和INSERT的性能有很大的影响。 二，调优分类： 对Oracle数据库进行性能调整时，应当按照一定的顺序进行，因为系统在前面步骤中进行的调整可以避免后面的一些不必要调整或者代价很大的调整。一般来说可以从两个阶段入手： 1、设计阶段：对其逻辑结构和物理结构进行优化设计，使之在满足需求条件的情况下,系统性能达到最佳，系统开销达到最小； 2、数据库运行阶段：采取操作系统级、数据库级的一些优化措施来使系统性能最佳； ㈠设计阶段： A，数据库设计优化 较多修改较少查询的数据和较多查询较少修改的数据分别对待。 a，结构优化 1，根据应用程序进行数据库设计。 即应用程序采用的是传统的C/S两层体系结构，还是B/W/D三层体系结构。不同的应用程序体系结构要求的数据库资源是不同的。 2，遵循3大范式规范化数据结构，减少不必要的冗余。 3，反规范设计，增加必要冗余，提高查询速度。 4，针对变化较少的数据，合理创建临时表和视图，需注意对临时表和视图的及时同步更新
（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)： ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表. （2） WHERE子句中的连接顺序．： ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE 子句的末尾. （3） SELECT子句中避免使用 * ： ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间 （4）减少访问数据库的次数： ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等； （5）在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200 （6）使用DECODE函数来减少处理时间： 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表. （7）整合简单,无关联的数据库访问： 如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系) （8）删除重复记录： 最高效的删除重复记录方法 ( 因为使用了ROWID)例子： DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_NO = E.EMP_NO); （9）用TRUNCATE替代DELETE： 当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. 译者按: TRUNCATE只在删除全表适 用,TRUNCATE是DDL不是DML) （10）尽量多使用COMMIT： 只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求
1.选用适合的ORACLE优化器 ORACLE的优化器共有3种: 1.RULE(基于规则) 2.COST(基于成本) 3.CHOOSE(选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如 RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在SQL 句级或是会话(session)级对其进行覆盖. 为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须经常运行analyze 命令,以增加数据库中的对象统计信息(object statistics)的准确性. 如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关. 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器. 在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器. 2.访问Table的方式 ORACLE 采用两种访问表中记录的方式: 1.全表扫描
全表扫描就是顺序地访问表中每条记录. ORACLE 采用一次读入多个数据块(database block)的方式优化全表扫描. 2.通过ROWID访问表 你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息..ORACLE 采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高. 3.共享SQL语句 为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用. 可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询.