A. oracle表如何优化
原则一：注意WHERE子句中的连接顺序：
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
尤其是“主键ID=？”这样的条件。
原则二： SELECT子句中避免使用 ‘ * ‘：
ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间 。
简单地讲，语句执行的时间越短越好（尤其对于系统的终端用户来说）。而对于查询语句，由于全表扫描读取的数据多，尤其是对于大型表不仅查询速度慢，而且对磁盘IO造成大的压力，通常都要避免，而避免的方式通常是使用索引Index。
使用索引的优势与代价。
优势：
1）索引是表的一个概念部 分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率.
2） 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.。那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率.
代价： 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会 使查询反应时间变慢.。而且表越大，影响越严重。
使用索引需要注意的地方：
1、避免在索引列上使用NOT ，
我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.
2、避免在索引列上使用计算．
WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描． 举例:
低效:SELECT … FROM DEPT WHERE SAL * 12 > 25000;
高效:SELECT … FROM DEPT WHERE SAL > 25000/12;
3、避免在索引列上使用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;
4、注意通配符%的影响
使用通配符的情况下Oracle可能会停用该索引。如 :
SELECT…FROM DEPARTMENT WHERE DEPT_CODE like ‘%123456%'（无效）。
SELECT…FROM DEPARTMENT WHERE DEPT_CODE = ‘123456'（有效）
5、避免改变索引列的类型.:
当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.
假 设 EMPNO是一个数值类型的索引列. SELECT … FROM EMP WHERE EMPNO = ‘123' 实际上,经过ORACLE类型转换, 语句转化为: SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123') 幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变. 现在,假设EMP_TYPE是一个字符类型的索引列. SELECT … FROM EMP WHERE EMP_TYPE = 123 这个语句被ORACLE转换为: SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)=123 因为内部发生的类型转换, 这个索引将不会被用到! 为了避免ORACLE对你的sql进行隐式的类型转换, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, ORACLE会优先转换数值类型到字符类型
6、索引的一些“脾气”
a. 如果检索数据量超过30%的表中记录数.使用索引将没有显着的效率提高.
b. 在特定情况下, 使用索引也许会比全表扫描慢, 但这是同一个数量级上的区别. 而通常情况下,使用索引比全表扫描要块几倍乃至几千倍!
除了使用索引，我们还有其他能减少资源消耗的方法：
1、用EXISTS替换DISTINCT：
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.
例子:
(低效): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E
WHERE D.DEPT_NO = E.DEPT_NO
And E.sex =man
(高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D
WHERE EXISTS
( SELECT ‘X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO
And E.sex =man
);
2、用(UNION)UNION ALL替换OR (适用于索引列)
通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描.
注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引.
如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.
高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION ALL
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”
3、用UNION-ALL 替换UNION ( 如果有可能的话)：
当 SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序. 如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高. 需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录. 因此各位还是要从业务需求分析使用UNION ALL的可行性. UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存. 对于这块内存的优化也是相当重要的.
4、Order By语句加在索引列，最好是主键PK上。
SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE（低效）
SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_CODE （高效）
5、避免使用耗费资源的操作:
带 有DISTINCT,UNION,MINUS,INTERSECT的SQL语句会启动SQL引擎 执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序. 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写. 如果你的数据库的SORT_AREA_SIZE调配得好, 使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强
6、使用Where替代Having（如果可以的话）
优化GROUP BY:
提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多.
低效:
SELECT JOB , AVG(SAL)
FROM EMP GROUP JOB HAVING JOB = ‘PRESIDENT'AND AVG(SAL)>XXX
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER' GROUP JOB Having AND AVG(SAL)>XXX
7、通常来说，如果语句能够避免子查询的 使用，就尽量不用子查询。因为子查询的开销是相当昂贵的。具体的例子在后面的案例“一条SQL的优化过程”中。
B. 关于优化Oracle存储过程的问题
你好：
您的程序我也看了。
先这么入手吧：
您的程序是包含在一个循环语句中，可否把循环语句的cursor单独拉出来，查一下执行计划：
selectdistinctv.procinstid_,v.value_,r.purc_report_id,r.purc_pro_id,r.purc_pro_name,t.team_name,r.purc_type,r.requi_form,r.build_name
fromcg_purchase_reportr,cg_bid_teamt,jbpm4_hist_varv
wherer.bid_team=t.bid_team_id
andr.purc_report_id=v.value_
andv.varname_='pkId'
andv.procinstid_like'requirementsReport%'
andr.purc_report_state=3;
一般来说对程序效率造成最大的问题的就是cursor，当然也包括了对INDEX列的DML。那我们先一步步来。您先查一下，给个截图。或者简单描述下您查到的执行计划的结果。
-------------------------------------------------------------------------------------------------------------------------
另外， 指出一下（或者说探讨一下）楼上wwtburning所提出的时间上用to_number, to_date的问题。 这个问题本身确实可能会造成效率下降， 但是不是由于使用了转换， 您的代码中全部都是 select __________ from al, 没有用在 where句中， 就不可能通过index而造成影响。所以这个方向不需要考虑了。
------------------------------------------------------------------------------------------------------------------------
提2个问题：
您的cursor查询结果是2000， 但是您的from 后面跟的几个表的数据集是多大？如果可能， 可否给您的几个表的索引一个截图。目的是如果是driving cursor的效率不高， 那么需要提升这个cursor的效率， 需要看下他的索引是怎么写的， 顺便也可以了解一下有多少个索引。
您的这个过程中有少许几个update语句，通过您的程序的命名法看出很多数据是为了商业报表而准备的。 这个您要更新的语句是staging table吗，或者是数据仓库吗？ 索引是怎样的？ 问这个问题是因为， 为报表而准备的数据在设计过程中为的是用报表的人可以读的快，也就是说select尽量快， 因此， 如果我们第一步检查cursor走不通， 就可能要考虑是否是您这个目标表的效率问题。
C. Oracle 大数量 存储 优化 DML
利用rowid分块实现非分区表的并行update与delete
大表中海量历史数据的更新与删除一直是令DBA非常头痛的事情，在表已经分区的前提下我们还可以利用并行或者truncate parition等手段来为UPDATE或者DELETE提速， 但是如果对象是普通的非分区对表(non-partitioned heap table)的话，似乎就没有太好的加速方法了， nologging或parallel 对非分区表都没有效果。
之前我也有介绍过一个利用rowid将非分区表分割成指定数量个区间块的方法，见<Script:partition table into rowid extent chunks>；利用该脚本可以获取到这些分割后的区间块的起始rowid和结尾rowid，之后利用between start_rowid and end_rowid的条件构造多条DML语句， 因为这些DML语句所更新的数据都是在互不相关的区间内的，所以可以在多个终端内并行地运行这些DML语句，而不会造成锁的争用或者Oracle并行执行协调(Parallel Execution coordinator ) 所带来的一些开销。
http://www.askmaclean.com/archives/%E5%88%A9%E7%94%A8rowid%E5%88%86%E5%9D%97%E5%AE%9E%E7%8E%B0%E9%9D%9E%E5%88%86%E5%8C%BA%E8%A1%A8%E7%9A%84%E5%B9%B6%E8%A1%8Cupdate%E4%B8%8Edelete.html
D. oracle数据库存储过程执行慢时如何优化
1 首先看看，先分析慢的原因，一部分是因为循环次数多，一部分是因为查询数据量大慢。
2 可以从优化查询入手，比如某次查询的sql里面的数据，条件字段没有建索引，导致了全表扫描，
是不是 只需要几个字段，但是你写了 select * 等等，总之要优化数据的速度。
2 可以从循环逻辑看起，有些循环可能是不必要的，能不能通过条件查询来替代循环，总之要从逻辑上优化代码
E. Oracle数据库存储优化问题
创建存储过程的语句如下：
create[or
replace]
procere＜过程名＞
＜参数1>，“方式l]＜数据类型1>，
＜参数2>，[
方式2]＜数据类型2>，
……)
is|as
（is或as完全等价）
begin
pl/sql过程体
end＜过程名＞
例如，下面是一个删除表的存储过程：
--参数：tablename
要删除的表名
create
or
replace
procere
del_table(tablename
varchar2)
as
--定义变量
sql_del_table
long;
begin
--构造sql语句
sql_del_table
:=
'drop
table
'
||
tablename;
--执行
execute
immediate
sql_del_table;
end
del_table;
F. oracle数据库优化有哪些方法
你最好买一本专门讲ORACLE性能优化的书，好好看看
1、调整数据库服务器的性能
Oracle数据库服务器是整个系统的核心，它的性能高低直接影响整个系统的性能，为了调整Oracle数据库服务器的性能，主要从以下几个方面考虑：
1.1、调整操作系统以适合Oracle数据库服务器运行
Oracle数据库服务器很大程度上依赖于运行服务器的操作系统，如果操作系统不能提供最好性能，那么无论如何调整，Oracle数据库服务器也无法发挥其应有的性能。
1.1.1、为Oracle数据库服务器规划系统资源
据已有计算机可用资源, 规划分配给Oracle服务器资源原则是：尽可能使Oracle服务器使用资源最大化,特别在Client/Server中尽量让服务器上所有资源都来运行Oracle服务。
1.1.2、调整计算机系统中的内存配置
多数操作系统都用虚存来模拟计算机上更大的内存，它实际上是硬盘上的一定的磁盘空间。当实际的内存空间不能满足应用软件的要求时，操作系统就将用这部分的磁盘空间对内存中的信息进行页面替换，这将引起大量的磁盘I/O操作，使整个服务器的性能下降。为了避免过多地使用虚存，应加大计算机的内存。
1.1.3、为Oracle数据库服务器设置操作系统进程优先级
不要在操作系统中调整Oracle进程的优先级，因为在Oracle数据库系统中，所有的后台和前台数据库服务器进程执行的是同等重要的工作，需要同等的优先级。所以在安装时，让所有的数据库服务器进程都使用缺省的优先级运行。
1.2、调整内存分配
Oracle数据库服务器保留3个基本的内存高速缓存，分别对应3种不同类型的数据：库高速缓存，字典高速缓存和缓冲区高速缓存。库高速缓存和字典高速缓存一起构成共享池，共享池再加上缓冲区高速缓存便构成了系统全程区(SGA)。SGA是对数据库数据进行快速访问的一个系统全程区，若SGA本身需要频繁地进行释放、分配，则不能达到快速访问数据的目的，因此应把SGA放在主存中，不要放在虚拟内存中。内存的调整主要是指调整组成SGA的内存结构的大小来提高系统性能，由于Oracle数据库服务器的内存结构需求与应用密切相关，所以内存结构的调整应在磁盘I/O调整之前进行。
1.2.1、库缓冲区的调整
库缓冲区中包含私用和共享SQL和PL/SQL区，通过比较库缓冲区的命中率决定它的大小。要调整库缓冲区，必须首先了解该库缓冲区的活动情况，库缓冲区的活动统计信息保留在动态性能表v$librarycache数据字典中，可通过查询该表来了解其活动情况，以决定如何调整。
Select sum(pins),sum(reloads) from v$librarycache;
Pins列给出SQL语句，PL/SQL块及被访问对象定义的总次数；Reloads列给出SQL 和PL/SQL块的隐式分析或对象定义重装载时在库程序缓冲区中发生的错误。如果sum(pins)/sum(reloads) ≈0，则库缓冲区的命中率合适；若sum(pins)/sum(reloads)>1, 则需调整初始化参数 shared_pool_size来重新调整分配给共享池的内存量。
1.2.2、数据字典缓冲区的调整
数据字典缓冲区包含了有关数据库的结构、用户、实体信息。数据字典的命中率，对系统性能影响极大。数据字典缓冲区的使用情况记录在动态性能表v$librarycache中，可通过查询该表来了解其活动情况，以决定如何调整。
Select sum(gets),sum(getmisses) from v$rowcache;
Gets列是对相应项请求次数的统计；Getmisses 列是引起缓冲区出错的数据的请求次数。对于频繁访问的数据字典缓冲区，sum(getmisses)/sum(gets)<10%～15%。若大于此百分数，则应考虑增加数据字典缓冲区的容量，即需调整初始化参数shared_pool_size来重新调整分配给共享池的内存量。
1.2.3、缓冲区高速缓存的调整
用户进程所存取的所有数据都是经过缓冲区高速缓存来存取，所以该部分的命中率，对性能至关重要。缓冲区高速缓存的使用情况记录在动态性能表v$sysstat中，可通过查询该表来了解其活动情况，以决定如何调整。
Select name,value from v$sysstat where name in ('dbblock gets','consistent gets','physical reads');
dbblock gets和consistent gets的值是请求数据缓冲区中读的总次数。physical reads的值是请求数据时引起从盘中读文件的次数。从缓冲区高速缓存中读的可能性的高低称为缓冲区的命中率，计算公式：
Hit Ratio=1-(physical reds/(dbblock gets+consistent gets))
如果Hit Ratio<60%～70%，则应增大db_block_buffers的参数值。db_block_buffers可以调整分配给缓冲区高速缓存的内存量，即db_block_buffers可设置分配缓冲区高速缓存的数据块的个数。缓冲区高速缓存的总字节数=db_block_buffers的值*db_block_size的值。db_block_size 的值表示数据块大小的字节数，可查询 v$parameter 表：
select name,value from v$parameter where name='db_block_size';
在修改了上述数据库的初始化参数以后，必须先关闭数据库，在重新启动数据库后才能使新的设置起作用。