189 8069 5689
你最好买一本专门讲ORACLE性能优化的书，好好看看\x0d\x0a1、调整数据库服务器的性能\x0d\x0aOracle数据库服务器是整个系统的核心，它的性能高低直接影响整个系统的性能，为了调整Oracle数据库服务器的性能，主要从以下几个方面考虑： \x0d\x0a1.1、调整操作系统以适合Oracle数据库服务器运行\x0d\x0aOracle数据库服务器很大程度上依赖于运行服务器的操作系统，如果操作系统不能提供最好性能，那么无论如何调整，Oracle数据库服务器也无法发挥其应有的性能。 \x0d\x0a1.1.1、为Oracle数据库服务器规划系统资源 \x0d\x0a据已有计算机可用资源, 规划分配给Oracle服务器资源原则是：尽可能使Oracle服务器使用资源最大化,特别在Client/Server中尽量让服务器上所有资源都来运行Oracle服务。 \x0d\x0a1.1.2、调整计算机系统中的内存配置 \x0d\x0a多数操作系统都用虚存来模拟计算机上更大的内存，它实际上是硬盘上的一定的磁盘空间。当实际的内存空间不能满足应用软件的要求时，操作系统就将用这部分的磁盘空间对内存中的信息进行页面替换，这将引起大量的磁盘I/O操作，使整个服务器的性能下降。为了避免过多地使用虚存，应加大计算机的内存。 \x0d\x0a1.1.3、为Oracle数据库服务器设置操作系统进程优先级 \x0d\x0a不要在操作系统中调整Oracle进程的优先级，因为在Oracle数据库系统中，所有的后台和前台数据库服务器进程执行的是同等重要的工作，需要同等的优先级。所以在安装时，让所有的数据库服务器进程都使用缺省的优先级运行。 \x0d\x0a1.2、调整内存分配\x0d\x0aOracle数据库服务器保留3个基本的内存高速缓存，分别对应3种不同类型的数据：库高速缓存，字典高速缓存和缓冲区高速缓存。库高速缓存和字典高速缓存一起构成共享池，共享池再加上缓冲区高速缓存便构成了系统全程区(SGA)。SGA是对数据库数据进行快速访问的一个系统全程区，若SGA本身需要频繁地进行释放、分配，则不能达到快速访问数据的目的，因此应把SGA放在主存中，不要放在虚拟内存中。内存的调整主要是指调整组成SGA的内存结构的大小来提高系统性能，由于Oracle数据库服务器的内存结构需求与应用密切相关，所以内存结构的调整应在磁盘I/O调整之前进行。 \x0d\x0a1.2.1、库缓冲区的调整 \x0d\x0a库缓冲区中包含私用和共享SQL和PL/SQL区，通过比较库缓冲区的命中率决定它的大小。要调整库缓冲区，必须首先了解该库缓冲区的活动情况，库缓冲区的活动统计信息保留在动态性能表v$librarycache数据字典中，可通过查询该表来了解其活动情况，以决定如何调整。 \x0d\x0a \x0d\x0aSelect sum(pins),sum(reloads) from v$librarycache; \x0d\x0a \x0d\x0aPins列给出SQL语句，PL/SQL块及被访问对象定义的总次数；Reloads列给出SQL 和PL/SQL块的隐式分析或对象定义重装载时在库程序缓冲区中发生的错误。如果sum(pins)/sum(reloads) ≈0，则库缓冲区的命中率合适；若sum(pins)/sum(reloads)1, 则需调整初始化参数 shared_pool_size来重新调整分配给共享池的内存量。 \x0d\x0a1.2.2、数据字典缓冲区的调整 \x0d\x0a数据字典缓冲区包含了有关数据库的结构、用户、实体信息。数据字典的命中率，对系统性能影响极大。数据字典缓冲区的使用情况记录在动态性能表v$librarycache中，可通过查询该表来了解其活动情况，以决定如何调整。 \x0d\x0a \x0d\x0aSelect sum(gets),sum(getmisses) from v$rowcache; \x0d\x0a \x0d\x0aGets列是对相应项请求次数的统计；Getmisses 列是引起缓冲区出错的数据的请求次数。对于频繁访问的数据字典缓冲区，sum(getmisses)/sum(gets)10%～15%。若大于此百分数，则应考虑增加数据字典缓冲区的容量，即需调整初始化参数shared_pool_size来重新调整分配给共享池的内存量。 \x0d\x0a1.2.3、缓冲区高速缓存的调整 \x0d\x0a用户进程所存取的所有数据都是经过缓冲区高速缓存来存取，所以该部分的命中率，对性能至关重要。缓冲区高速缓存的使用情况记录在动态性能表v$sysstat中，可通过查询该表来了解其活动情况，以决定如何调整。 \x0d\x0a \x0d\x0aSelect name,value from v$sysstat where name in ('dbblock gets','consistent gets','physical reads'); \x0d\x0a \x0d\x0adbblock gets和consistent gets的值是请求数据缓冲区中读的总次数。physical reads的值是请求数据时引起从盘中读文件的次数。从缓冲区高速缓存中读的可能性的高低称为缓冲区的命中率，计算公式： \x0d\x0a \x0d\x0aHit Ratio=1-(physical reds/(dbblock gets+consistent gets)) \x0d\x0a \x0d\x0a如果Hit Ratio60%～70%，则应增大db_block_buffers的参数值。db_block_buffers可以调整分配给缓冲区高速缓存的内存量，即db_block_buffers可设置分配缓冲区高速缓存的数据块的个数。缓冲区高速缓存的总字节数=db_block_buffers的值*db_block_size的值。db_block_size 的值表示数据块大小的字节数，可查询 v$parameter 表： \x0d\x0a \x0d\x0aselect name,value from v$parameter where name='db_block_size'; \x0d\x0a \x0d\x0a在修改了上述数据库的初始化参数以后，必须先关闭数据库，在重新启动数据库后才能使新的设置起作用。
成都创新互联公司致力于互联网网站建设与网站营销，提供成都网站建设、网站建设、网站开发、seo优化、网站排名、互联网营销、微信小程序定制开发、公众号商城、等建站开发，成都创新互联公司网站建设策划专家，为不同类型的客户提供良好的互联网应用定制解决方案，帮助客户在新的全球化互联网环境中保持优势。
Oracle优化器在任何可能的时候都会对表达式进行评估 并且把特定的语法结构转换成等价的结构 这么做的原因是
·要么结果表达式能够比源表达式具有更快的速度
·要么源表达式只是结果表达式的一个等价语义结构
不同的SQL结构有时具有同样的操作（例如 = ANY (subquery) and IN (subquery)） Oracle会把他们映射到一个单一的语义结构
下面将讨论优化器如何评估优化如下的情况和表达式
常量 LIKE 操作符 IN 操作符 ANY和SOME 操作符 ALL 操作符 BEEEN 操作符 NOT 操作符
传递（Transitivity） 确定性（DETERMINISTIC）函数
常量
常量的计算是在语句被优化时一次性完成 而不是在每次执行时 下面是检索月薪大于 的的表达式 · sal / · sal · sal*
如果SQL语句包括第一种情况 优化器会简单地把它转变成第二种
注意 优化器不会简化跨越比较符的表达式 例如第三条语句 鉴于此 应用程序开发者应该尽量写用常量跟字段比较检索的表达式 而不要将字段置于表达式当中
LIKE 操作符
优化器把使用LIKE操作符和一个没有通配符的表达式组成的检索表达式转换为一个“=”操作符表达式
例如 优化器会把表达式ename LIKE SMITH 转换为ename = SMITH 优化器只能转换涉及到可变长数据类型的表达式 前一个例子中 如果ENAME字段的类型是CHAR( ) 那么优化器将不做任何转换
IN 操作符
优化器把使用IN比较符的检索表达式替换为等价的使用“=”和“OR”操作符的检索表达式 例如 优化器会把表达式ename IN ( SMITH KING JONES )替换为: ename = SMITH OR ename = KING OR ename = JONES
ANY和SOME 操作符
优化器将跟随（following）值列表的ANY和SOME检索条件用等价的同等操作符和“OR”组成的表达式替换
例如 优化器将如下所示的第一条语句用第二条语句替换 · sal ANY (:first_sal :second_sal) · sal :first_sal OR sal :second_sal
优化器将跟随子查询的ANY和SOME检索条件转换成由“EXISTS”和一个相应的子查询组成的检索表达式
例如 优化器将如下所示的第一条语句用第二条语句替换 · x ANY (SELECT sal FROM emp WHERE job = ANALYST ) · EXISTS (SELECT sal FROM emp WHERE job = ANALYST AND x sal)
ALL 操作符
优化器将跟随值列表的ALL操作符用等价的“=”和“AND”组成的表达式替换
例如 sal ALL (:first_sal :second_sal)表达式会被替换为 sal :first_sal AND sal :second_sal
对于跟随子查询的ALL表达式 优化器用ANY和另外一个合适的比较符组成的表达式替换 例如 优化器会把表达式 x ALL (SELECT sal FROM emp WHERE deptno = ) 替换为 NOT (x = ANY (SELECT sal FROM emp WHERE deptno = ))
接下来优化器会把第二个表达式适用ANY表达式的转换规则转换为下面的表达式 NOT EXISTS (SELECT sal FROM emp WHERE deptno = AND x = sal)
BEEEN 操作符
优化器总是用“=”和“=”比较符来等价的代替BEEEN操作符 例如 优化器会把表达式sal BEEEN AND 用sal = AND sal = 来代替
NOT 操作符
优化器总是试图简化检索条件以消除“NOT”逻辑操作符的影响 这将涉及到“NOT”操作符的消除以及代以相应的比较运算符
例如 优化器将下面的第一条语句用第二条语句代替 · NOT deptno = (SELECT deptno FROM emp WHERE ename = TAYLOR ) · deptno (SELECT deptno FROM emp WHERE ename = TAYLOR )
通常情况下一个含有NOT操作符的语句有很多不同的写法 优化器的转换原则是使“NOT”操作符后边的子句尽可能的简单 即使可能会使结果表达式包含了更多的“NOT”操作符 例如 优化器将如下所示的第一条语句用第二条语句代替 · NOT (sal OR m IS NULL) · NOT sal AND m IS NOT NULL sal = AND m IS NOT NULL
传递（Transitivity）
如果“WHERE”子句的两个检索条件涉及了一个共同的字段 优化器有时会根据传递原理推断出第三个检索条件 随后可以根据这个推断出的条件对语句 进行优化 推断出的条件可能会激活一个原来的检索条件没有激活的潜在的接口路径（access path） 注意 传递仅仅被用在基于代价（cost based）的优化中
假设有一个这样的包含两个检索条件的“WHERE”子句 WHERE 字段 常量 AND字段 = 字段 在这个例子里 优化器会推断出新的检索条件 字段 常量 在这里 是比较运算符= != ^= = 或 =之中的任何一个 常量是指任何一个涉及了操作符 SQL函数 文字 绑定变量（bind variables）或者关联变量（correlation variables）的常量表达式
例如 考虑这样一个包含两个各自使用了字段EMP DEPTNO的检索条件的WHERE子句的查询 SELECT * FROM emp dept WHERE emp deptno = AND emp deptno = dept deptno;
使用传递优化 优化器会推断出如下条件 dept deptno = 如果有索引存在于EMP DEPTNO字段上 这个条件会使调用这个索引的接口路径有效 注意 优化器只能对字段关联常量的表达式进行推断 而不是 字段关联字段的表达式 例如 包含这样条件的WHERE子句 字段 字段 AND 字段 = 字段 这种情况不能推断出表达式 字段 p_oper 字段
确定性（DETERMINISTIC）函数
在某些情况下 优化器能够使用先前的函数返回结果而不是重新执行用户定义的函数 这仅仅对那些以限制的方式来执行的函数来说是有效的 这些函数必须 对任何的输入都有同样的返回值 函数的结果必须不能因为包（PACKAGE）变量 数据库或会话（SESSION）的参数（例如NLS参数）不同而变化 如果函数在将来重新定义 返回值必须对任何参数来说仍然与以前的返回值相同 函数的创建者可以在以CREATE FUNCTION CREATE PACKAGE或者CREATE TYPE声明函数时根据以上的要求使用DETERMINISTIC关键字向数据库申明该函数为确定性函数 数据库不会对确定性函数的合法性进行校验 即使 一个函数明显的使用了包变量或操作了数据库 仍然可以被定义为确定性函数 这就是说如何安全合法的使用和定义确定性函数是程序员的责任
lishixinzhi/Article/program/Oracle/201311/19040
一、 磁盘方面调优
1. 规范磁盘阵列
RAID 10比RAID5更适用于OLTP系统，RAID10先镜像磁盘，再对其进行分段，由于对数据的小规模访问会比较频繁，所以对OLTP适用。而RAID5,优势在于能够充分利用磁盘空间，并且减少阵列的总成本。但是由于阵列发出一个写入请求时，必须改变磁盘上已修改的块，需要从磁盘上读取“奇偶校验”块，并且使用已修改的块计算新的奇偶校验块，然后把数据写入磁盘，且会限制吞吐量。对性能有所影响，RAID5适用于OLAP系统。
2. 数据文件分布
分离下面的东西,避免磁盘竞争
Ø SYSTEM表空间
Ø TEMPORARY表空间
Ø UNDO表空间
Ø 联机重做日志（放在最快的磁盘上）
Ø 操作系统磁盘
Ø ORACLE安装目录
Ø 经常被访问的数据文件
Ø 索引表空间
Ø 归档区域（应该总是与将要恢复的数据分离）
例：
² /: System
² /u01: Oracle Software
² /u02: Temporary tablespace, Control file1
² /u03: Undo Segments, Control file2
² /u04: Redo logs, Archive logs, Control file4
² /u05: System, SYSAUX tablespaces
² /u06: Data1 ,control file3
² /u07: Index tablespace
² /u08: Data2
通过下列语句查询确定IO问题
select name ,phyrds,phywrts,readtim,writetim
from v$filestat a,v$datafile b
where a.file#=b.file# order by readtim desc;
3. 增大日志文件
u 增大日志文件的大小，从而增加处理大型INSERT,DELETE,UPDATE操作的比例
查询日志文件状态
select a.member,b.* from v$logfile a,v$log b where a.GROUP#=b.GROUP#
查询日志切换时间
select b.RECID,to_char(b.FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') start_time,a.RECID,to_char(a.FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') end_time,round(((a.FIRST_TIME-b.FIRST_TIME)*25)*60,2) minutes
from v$log_history a ,v$log_history b
where a.RECID=b.RECID+1
order by a.FIRST_TIME desc
增大日志文件大小，以及对每组增加日志文件（一个主文件、一个多路利用文件）
u 增大LOG_CHECKPOINT_INTERVAL参数，现已不提倡使用它
如果低于每半小时切换一次日志，就增大联机重做日志大小。如果处理大型批处理任务时频繁进行切换，就增大联机重做日志数目。
alter database add logfile member ‘/log.ora’ to group 1;
alter database drop logfile member ‘/log.ora’;
4. UNDO表空间
修改三个初始参数：
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=CLOUDSEA_UNDO
UNDO_RETENTION=#of minutes
5. 不要在系统表空间中执行排序
二、 初始化参数调优
32位的寻址最大支持应该是2的32次方，就是4G大小。但实际中32位系统（XP，windows2003等MS32位系统, ubuntu等linux32 位系统）要能利用4G内存，都是采用内存重映射技术。需要主板及系统的支持。如果关闭主板BIOS的重映射功能，系统将不能利用4G内存，可能只达3.5G.而在windows下看到的一般为3.25G。所以SGA设置为内存的40%，但不能超过3.25G
1. 重要初始化参数
l SGA_MAX_SIZE
l SGA_TARGET
l PGA_AGGREGATE_TARGET
l DB_CACHE_SIZE
l SHARED_POOL_SIZE
2. 调整DB_CACHE_SIZE来提高性能
它设定了用来存储和处理内存中数据的SGA区域大小，从内存中取数据比磁盘快10000倍以上
根据以下查询出数据缓存命中率
select sum(decode(name,'physical reads',value,0)) phys,
sum(decode(name,'db block gets',value,0)) gets,
sum(decode(name,'consistent gets',value,0)) con_gets,
(1- (sum(decode(name,'physical reads',value,0))/(sum(decode(name,'db block gets',value,0))+sum(decode(name,'consistent gets',value,0)) ) ))*100 Hitratio
from v$sysstat;
一个事务处理程序应该保证得到95%以上的命中率，命中率从90%提高到98%可能会提高500%的性能，ORACLE正在通过CPU或服务时间与等待时间来分析系统性能，不太重视命中率，不过现在的库缓存和字典缓存仍将命中率作为基本的调整方法。
在调整DB_CACHE_SIZE时使用V$DB_CACHE_ADVICE
select size_for_estimate, estd_physical_read_factor, estd_physical_reads
from v$db_cache_advice
where name = 'DEFAULT';
如果查询的命中率过低，说明缺少索引或者索引受到限制，通过V$SQLAREA视图查询执行缓慢的SQL
3. 设定DB_BLOCK_SIZE来反映数据读取量大小
OLTP一般8K
OLAP一般16K或者32K
4. 调整SHARED_POOL_SIZE以优化性能
正确地调整此参数可以同等可能地共享SQL语句，使得在内存中便能找到使用过的SQL语句。为了减少硬解析次数，优化对共享SQL区域的使用，需尽量使用存储过程、使用绑定变量
保证数据字典缓存命中率在95%以上
select ((1- sum(getmisses)/(sum(gets)+sum(getmisses)))*100) hitratio
from v$rowcache
where gets+getmisses 0;
如果命中率小于 99％，就可以考虑增加shared pool 以提高library cache 的命中率
SELECT SUM(PINS) "EXECUTIONS",SUM(RELOADS) "CACHE MISSES WHILE EXECUTING",1 - SUM(RELOADS)/SUM(PINS)
FROM V$LIBRARYCACHE;
通常规则是把它定为DB_CACHE_SIZE大小的50%－150%，在使用了大量存储过程或程序包，但只有有限内存的系统里，最后分配为150%。在没有使用存储过程但大量分配内存给DB_CACHE_SIZE的系统里，这个参数应该为10%－20%
5. 调整PGA_AGGREGATE_TARGET以优化对内存的应用
u OLTP ：totalmemory*80%*20%
u DSS: totalmemory*80%*50%
6. 25个重要初始化参数
² DB_CACHE_SIZE：分配给数据缓存的初始化内存
² SGA_TARGET：使用了自动内存管理，则设置此参数。设置为0可禁用它
² PGA_AGGREGATE_TARGET：所有用户PGA软内存最大值
² SHARED_POOL_SIZE：分配给数据字典、SQL和PL／SQL的内存
² SGA_MAX_SIZE：SGA可动态增长的最大内存
² OPTIMIZER_MODE：
² CURSOR_SHARING：把字面SQL转换成带绑定变更的SQL，可减少硬解析开销
² OPTIMIZER_INDEX_COST_ADJ：索引扫描成本和全表扫描成本进行调整，设定在1－10间会强制频繁地使用索引，保证索引可用性
² QUERY_REWRITE_ENABLED：用于启用具体化视图和基于函数的索引功能
² DB_FILE_MULTIBLOCK_READ_COUNT：对于全表扫描，为了更有效执行IO，此参数可在一次IO中读取多个块
² LOG_BUFFER：为内存中没有提交的事务分配缓冲区（非动态参数）
² DB_KEEP_CACHE_SIZE：分配给KEEP池或者额外数据缓存的内存
² DB_RECYCLE_CACHE_SIZE：
² DBWR_IO_SLAVES：如果没有异步IO，参数等同于DB_WRITER_PROCESSES模拟异步IO而分配的从SGA到磁盘的写入器数。如果有异步IO，则使用DB_WRITER_PROCESSES设置多个写程序，在DBWR期间更快地写出脏块
² LARGE_POOL_SIZE：分配给大型PLSQL或其他一些很少使用的ORACLE选项LARGET池的总块数
² STATISTICS_LEVEL：启用顾问信息，并可选择提供更多OS统计信息来改进优化器决策。默认：TYPICAL
² JAVA_POOL_SIZE：为JVM使用的JAVA存储过程所分配的内存
² JAVA_MAX_SESSIONSPACE_SIZE：跟踪JAVA类的用户会话状态所用内存上限
² MAX_SHARED_SERVERS：当使用共享服务器时的共享服务器上限
² WORKAREA_SIZE_POLICY：启用PGA大小自动管理
² FAST_START_MTTR_TARGET：完成一次崩溃恢复的大概时间／S
² LOG_CHECKPOINT_INTERVAL：检查点频率
² OPEN_CURSORS：指定了保存用户语句的专用区域大小，如此设置过高会导致ORA-4031
² DB_BLOCK_SIZE：数据库默认块大小
² OPTIMIZER_DYNAMIC_SAMPLING：控制动态抽样查询读取的块数量，对正在使用全局临时表的系统非常有用
三、 SQL调优1. 使用提示
1.1 改变执行路径
通过OPTIMIZER_MODE参数指定优化器使用方法，默认ALL_ROWS
Ø ALL_ROWS 可得最佳吞吐量执行查询所有行
Ø FIRST_ROWS(n) 可使优化器最快检索出第一行:
select /*+ FIRST_ROWS(1) */ store_id,… from tbl_store
1.2 使用访问方法提示
允许开发人员改变访问的实际查询方式，经常使用INDEX提示
Ø CLUSTER 强制使用集群
Ø FULL
Ø HASH
Ø INDEX 语法：/*+ INDEX (TABLE INDEX1,INDEX2….) */ COLUMN 1,….
当不指定任何INDEX时，优化器会选择最佳的索引
SELECT /*+ INDEX */ STORE_ID FROM TBL_STORE
Ø INDEX_ASC 8I开始默认是升序，所以与INDEX同效
Ø INDEX_DESC
Ø INDEX_COMBINE 用来指定多个位图索引，而不是选择其中最好的索引
Ø INDEX_JOIN 只需访问这些索引，节省了重新检索表的时间
Ø INDEX_FFS 执行一次索引的快速全局扫描，只处理索引，不访问具体表
Ø INDEX_SS
Ø INDEX_SSX_ASC
Ø INDEX_SS_DESC
Ø NO_INDEX
Ø NO_INDEX_FFS
Ø NO_INDEX_SS
1.3 使用查询转换提示
对于数据仓库非常有帮助
Ø FACT
Ø MERGE
Ø NO_EXPAND 语法：/*+ NO_EXPAND */ column1,…
保证OR组合起的IN列表不会陷入困境，/*+ FIRST_ROWS NO_EXPAND */
Ø NO_FACT
Ø NO_MERGE
Ø NO_QUERY_TRANSFORMATION
Ø NO_REWRITE
Ø NO_STAR_TRANSFORMATION
Ø NO_UNSET
Ø REWRITE
Ø STAR_TRANSFORMATION
Ø UNSET
Ø USE_CONCAT
1.4 使用连接操作提示
显示如何将连接表中的数据合并在一起，可用两提示直接影响连接顺序。LEADING指定连接顺序首先使用的表，ORDERED告诉优化器基于FROM子句中的表顺序连接这些表，并使用第一个表作为驱动表（最行访问的表）
ORDERED语法：/*+ ORDERED */ column 1,….
访问表顺序根据FROM后的表顺序来
LEADING语法：/*+ LEADING(TABLE1) */ column 1,….
类似于ORDER，指定驱动表
Ø NO_USE_HASH
Ø NO_USE_MERGE
Ø NO_USE_NL
Ø USE_HASH前提足够的HASH_AREA_SIZE或PGA_AGGREGATE_TARGET
通常可以为较大的结果集提供最佳的响应时间
Ø USE_MERGE
Ø USE_NL 通常可以以最快速度返回一个行
Ø USE_NL_WITH_INDEX
1.5 使用并行执行
Ø NO_PARALLEL
Ø NO_PARALLEL_INDEX
Ø PARALLEL
Ø PARALLEL_INDEX
Ø PQ_DISTRIBUTE
1.6 其他提示
Ø APPEND 不会检查当前所用块中是否有剩余空间，而直接插入到表中，会直接将数据添加到新的块中。
Ø CACHE 会将全表扫描全部缓存到内存中，这样可直接在内存中找到数据，不用在磁盘上查询
Ø CURSOR_SHARING_EXACT
Ø DRIVING_SITE
Ø DYNAMIC_SAMPLING
Ø MODEL_MIN_ANALYSIS
Ø NOAPPEND
Ø NOCACHE
Ø NO_PUSH_PRED
Ø NO_PUSH_SUBQ
Ø NO_PX_JOIN_FILTER
Ø PUSH_PRED
Ø PUSH_SUBQ 强制先执行子查询，当子查询很快返回少量行时，这些行可以用于限制外部查询返回行数，可极大地提高性能
例：select /*+PUSH_SUBQ */ emp.empno,emp.ename
From emp,orders
where emp.deptno=(select deptno from dept where loc=’1’)
Ø PX_JOIN_FILTER
Ø QB_NAME
2. 调整查询
2.1 在V$SQLAREA中选出最占用资源的查询
HASH_VALUE：SQL语句的Hash值。
ADDRESS：SQL语句在SGA中的地址。
PARSING_USER_ID：为语句解析第一条CURSOR的用户
VERSION_COUNT：语句cursor的数量
KEPT_VERSIONS：
SHARABLE_MEMORY：cursor使用的共享内存总数
PERSISTENT_MEMORY：cursor使用的常驻内存总数
RUNTIME_MEMORY：cursor使用的运行时内存总数。
SQL_TEXT：SQL语句的文本（最大只能保存该语句的前1000个字符）。
MODULE,ACTION：用了DBMS_APPLICATION_INFO时session解析第一条cursor时信息
SORTS: 语句的排序数
CPU_TIME: 语句被解析和执行的CPU时间
ELAPSED_TIME: 语句被解析和执行的共用时间
PARSE_CALLS: 语句的解析调用(软、硬)次数
EXECUTIONS: 语句的执行次数
INVALIDATIONS: 语句的cursor失效次数
LOADS: 语句载入(载出)数量
ROWS_PROCESSED: 语句返回的列总数
select b.username,a.DISK_READS,a.EXECUTIONS,a.DISK_READS/decode(a.EXECUTIONS,0,1,a.EXECUTIONS) rds_exec_ratio,a.SQL_TEXT
from v$sqlarea a ,dba_users b
where a.PARSING_USER_ID=b.user_id and a.DISK_READS100 order by a.DISK_READS desc;
2.2 在V$SQL中选出最占用资源的查询
与V$SQLAREA类似
select * from
(select sql_text,rank() over (order by buffer_gets desc) as rank_buffers,to_char(100*ratio_to_report(buffer_gets) over (),'999.99') pct_bufgets from v$sql)
where rank_buffers 11
2.3 确定何时使用索引
² 当查询条件只需要返回很少的行（受限列）时，则需要建立索引，不同的版本中这个返回要求不同
V5:20% V7:7% V8i,V9i:4% V10g: 5%
查看表上的索引
select a.table_name,a.index_name,a.column_name,a.column_position,a.table_owner
from dba_ind_columns a
where a.table_owner='CLOUDSEA'
² 修正差的索引，可使用提示来限制很差的索引，如INDEX，FULL提示
² 在SELECT 和WHERE中的列使用索引
如: select name from tbl where no=?
建立索引：create index test on tbl(name,no) tablespace cloudsea_index storage(….)
对于系统中很关键的查询，可以考虑建立此类连接索引
² 在一个表中有多个索引时可能出现麻烦，使用提示INDEX指定使用索引
² 使用索引合并，使用提示INDEX_JOIN
² 基于函数索引，由于使用了函数造成查询很慢.必须基于成本的优化模式,参数：
QUERY_REWRITE_ENALED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED (OR ENFORCED)
create index test on sum(test);
2.4 在内存中缓存表
将常用的相对小的表缓存到内存中，但注意会影响到嵌套循环连接上的驱动表
alter table tablename cache;
2.5 使用EXISTS 与嵌套子查询 代替IN
SELECT …FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=’A’);
(方法一: 高效)
SELECT ….FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+) = ‘A’
(方法二: 最高效)
SELECT ….FROM EMP E WHERE NOT EXISTS (SELECT ‘X’ FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’);
四、 使用STATSPACK和AWR报表调整等待和闩锁
1. 10GR2里的脚本
在$ORACLE_HOME/RDBMS/ADMIN下
Spcreate.sql 通过调用spcusr.sql spctab.sql 和spcpkg.sql创建STATSPACK环境，使用SYSDBA运行它
Spdrop.sql 调用sptab.sql和spdusr.sql删除整个STATSPACK环境，使用SYSDBA运行它
Spreport.sql 这是生成报表的主要脚本，由PERFSTAT用户运行
Sprepins.sql 为指定的数据库和实例生成实例报表
Sprepsql.sql 为指定的SQL散列值生成SQL报表
Sprsqins.sql 为指定的数据库和实例生成SQL报表
Spauto.sql 使用DBMS_JOB自动进行统计数据收集（照相）
Sprepcon.sql 配置SQLPLUS变量来设置像阈值这样的内容的配置文件
Spurge.sql 删除给定数据库实例一定范围内的快照ID，不删除基线快照
Sptrunc.sql 截短STATSPACK表里所有性能数据
五、 执行快速系统检查1. 缓冲区命中率
查询缓冲区命中率
select (1 - (sum(decode(name, 'physical reads',value,0)) /
(sum(decode(name, 'db block gets',value,0)) +
sum(decode(name, 'consistent gets',value,0))))) * 100 "Hit Ratio"
from v$sysstat;
TROUBLESHOOTING STEPS
For any ONE-TIME occurrence we can safely ignore the error. This error does not cause to any potential data corruption or any data loss. Check Note 35928.1 for known issues and two basic usages of OERI(12333):
Usage with 3 additional numeric values
Usage with 4 additional values ... new since 8.0.X
In case the errors are seen consistently and no known issues matches to your issue then do the following
Common Causes Solutions:
1. Check the client software version. An incompatible client software is a common cause of this error. Upgrade the client software to current version (i.e. match the server version).
The following note explains the supported combinations of clients for a particular RDBMS Server release.
Note 207303.1 Client / Server / Interoperability Support Between Different Oracle Versions
Similar issues are reported when the client installation is faulty.
2. An incompatible NLS Client setting can also trigger such internal errors. Check the NLS settings as explained in the following note.
Note 77442.1 ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) Environment Variables explained
3. There are a number of bugs associated with the use of bind peeking. Turn off bind peeking as follows:
alter system set "_optim_peek_user_binds"=false;
4. Please review your listener.log and SQLNET log for network errors at the time of the 1233 errors. Correct these errors.
Try to reproduce the issue from a different client machine, preferably from server. This will rule out the possibility of bad network. If the execution fails from a particular client then it could be due to the bad network from that client to server. The error ORA-600[12333] could occur when a request is being received from a network packet and the request code in the packet is not recognized.
5. Ask your sysadmin to correct any network delays. A slow network can lead to this error.
6. If the error still continues , then do the following to understand from where the error is generated.
We need to understand which session/program/application that triggers this issue. This shall be found from the trace files under the session state object '(session)'. Sometimes the ORA-00600 [12333] trace files will not capture the session information. In such cases we can check with the end users about any issues around the time-frame of this internal error.
There is a HIGH chance for application run-time exception to lead to this ORA-00600 [12333]. Due to the unhandled exceptions there is a chance for incomplete information passed to the server hence the internal errors are raised. In such cases, handling those exceptions (or) addressing those errors on the application code will resolve the issue.
In few cases, the problem application will work for a while before it terminates with the error. This could be due to the TIMEOUT settings at the Application Server (or) Forms Server.,etc. To resolve this either tune the failing application to complete within the TIMEOUT value (or) reset the TIMEOUT to an optimal value.
There could be more possibilities here; in simple words the approach should be from application side, which would help us to understand the other issues that triggered this network interruption (ORA-00600 [12333]).
Check the 'Current SQL Statement' section of the incident trace files. Does each trace file point to the same SQL statement. If so, there may be a problem with the code.
7. If you notice JDBC Client as the failing program under Session state object, please check the following
That you use latest compatible version of JDBC Drivers in your application side.
Note 430839.1 to verify the version of JDBC Drivers and for Installation steps.
Monitor and try to handle correctly the timeouts from the application
Check that idle or died programs/processes are not just killed at the client side but using a good cleaning procedure.
Increase queue sizes / buffers and similar from the jdbc side.
Further diagnostics regarding JDBC Application driver can be found:
Note 1361107.1 Suggestions For Troubleshooting ORA-3137 [12333] Errors Encountered When Using Oracle JDBC Driver
8. In-case if all the above suggestions does not help to identify the cause, and the ORA-00600 [12333] persists we may need to enable the networking tracings from the problem client and need to review the network traces to understand where the problem occurs. See Note 219968.1 Title: SQL*Net, Net8, Oracle Net Services - Tracing and Logging at a Glance. Check the network logs for errors and fix them with the network administrator.
E.g.:
12333错要 具体诊断的
如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!
诗檀软件专业数据库修复团队
在User Schema 上 用人工指定方式指定 Tmp 的tablespace 换句话说要人工定义一个tmp 的tablespace 给user schema ——为colm v这个user单独开一个临时表空间 个人认为调整之后效果不会有很明显的变化临时表空间的作用是当sql语句中出现order by group by（也就是需要排序的时候） 如果排序的数据比较少 就会在内存中排序 排序的数据量很大时 oracle会把排序的任务放在临时表空间中完成 内存中排序（逻辑排序）比在磁盘上排序（物理排序）理论上快 倍！所以临时表空间很大并不是一件好事情 说明sql中存在大量排序的动作 sql语句需要优化！ 所以深圳那里需要看一下临时表空间的大小！
当然johanna的意见也会有一定的作用 临时表空间独立出来之后 就不会和sys system等一系列oracle 内部使用临时表空间的操作产生资源竞争！
请检查SQL 使用到的where 条件是否均有定index 除此以外 检查使用到的where 条件最好以index 之顺序来写——这部分比较复杂 也是性能最关键的地方 几乎所有的oracle专家都认为 %以上的性能调整都和sql语句优化有关 需要check所有的sql语句where后面的条件是否有用到index的必要 工程量比较浩大！需要一个个小心谨慎的check！
把index 做一次 dbms_stats dbms stats 是oracle内部的api 可以对index table进行分析 收集统计信息 这样oracle优化器就会有一个最佳的选择 使性能达到最佳方法如下
SQLselect tt table_name tt num_rows tt blocks tt empty_blocks tt avg_row_len from dba_tables tt where tt owner= COLMTEST ; SQL select ttt index_name ttt num_rows ttt distinct_keys ttt avg_leaf_blocks_per_key ttt clustering_factor from dba_indexes ttt where ttt owner= COLMTEST ;
执行上述两条命令之后会发现除了table_name和index_name 其余列的统计信息都是不完全的
SQLexecute dbms_stats gather_schema_stats（ownname = COLMTEST cascade=true）
执行完dbms_stats 再调用上述两句语句 会发现所有的列基本上都已经被填充！
PS 执行统计比较慢 相当于所有table index都扫描一遍的时间 COLMTEST 改一下另外 对单个表执行统计分析的语句如下
EXECUTE dbms_stats gather_table_stats (ownname= citic tabname= col_cust_id estimate_percent= cascade=true)
做过以上处理之后 再看情形 再依情况放参数
——参数暂时不用调整！
建议做一个STATSPACK通过Statspack我们可以很容易的确定Oracle数据库的瓶颈所在 记录数据库性能状态 迅速了解数据库运行状况
方法如下
安装Statspack安装Statspack拥有SYSDBA（connect / as sysdba）权限的用户登陆 需要在本地安装或者通过telnet登陆到服务器 ——客户端登录不可以
必要条件 先创建名称为perfstat的表空间 至少 M
在那台oracle数据库上用colmv 登录SQL*PLUS 然后输入SQL connect sys/sys@（你们那里的sid） as sysdba SQL alter system set timed_statistics = true System altered——使用statspack收集统计信息时建议将该值设置为 TRUE 否则收集的统计信息大约只能起到 %的作用
SQL @C \oracle\ora \rdbms\admin\spcreate sql 输入 perfstat_password 的值 perfstat输入default_tablespace的值 perfstat输入temporary_tablespace 的值 temp
NOTE SPCPKG plete Please check spcpkg lis for any errors ——需要出现上述语句才算成功 否则请查看 lis文件并执行 进行重建SQL @C \oracle\ora \rdbms\admin\spdrop sql SQL @C \oracle\ora \rdbms\admin\spcreate sql
查看文件夹会产生三个文件C \oracle\ora \bin spcpkg lis spctab lis spcusr lis
——从下面开始都可以在客户端SQL_PLUS进行操作 手动执行STATSPACK收集统计信息SQL show user USER为 PERFSTAT SQL execute statspack snap ——快照
然后需要经过 个小时（跑批需要包含在里面） 再执行SQL execute statspack snap ——快照
最后生成STATSPACK调整报告
SQL @C:\oracle\ora \rdbms\admin\spreport sql;
Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance COLM colm Instances in this Statspack schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host COLM colm STEVENHUANG Using for database Id Using for instance number Completed Snapshots Snap Snap Instance DB Name Id Snap Started Level Comment colm COLM 月 : 月 : Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 输入 begin_snap 的值: 输入 end_snap 的值: End Snapshot Id specified: Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is sp_ _ To use this name press return to continue otherwise enter an alternative 输入 report_name 的值: report txt End of Report
查看产生的report 文档C \oracle\ora \bin\report txt
lishixinzhi/Article/program/Oracle/201311/17298
任何事情都有它的源头 要解决问题 也得从源头开始 影响ORACLE性能的源头非常多 主要包括如下方面 数据库的硬件配置 CPU 内存 网络条件 CPU 在任何机器中CPU的数据处理能力往往是衡量计算机性能的一个标志 并且ORACLE是一个提供并行能力的数据库系统 在CPU方面的要求就更高了 如果运行队列数目超过了CPU处理的数目 性能就会下降 我们要解决的问题就是要适当增加CPU的数量了 当然我们还可以将需要许多资源的进程KILL掉 内存 衡量机器性能的另外一个指标就是内存的多少了 在ORACLE中内存和我们在建数据库中的交换区进行数据的交换 读数据时 磁盘I/O必须等待物理I/O操作完成 在出现ORACLE的内存瓶颈时 我们第一个要考虑的是增加内存 由于I/O的响应时间是影响ORACLE性能的主要参数 我将在这方面进行详细的讲解 网络条件 NET*SQL负责数据在网络上的来往 大量的SQL会令网络速度变慢 比如 M的网卡和 的网卡就对NET*SQL有非常明显的影响 还有交换机 集线器等等网络设备的性能对网络的影响很明显 建议在任何网络中不要试图用 个集线器来将网段互联 OS参数的设置 下表给出了OS的参数设置及说明 DBA可以根据实际需要对这些参数进行设置 用户SQL质量 以上讲的都是硬件方面的东西 在条件有限的条件下 我们可以调整应用程序的SQL质量 不要进行全表扫描（Full Table Scan） 全表扫描导致大量的I/O 尽量建好和使用好索引 建索引也是有讲究的 在建索引时 也不是索引越多越好 当一个表的索引达到 个以上时 ORACLE的性能可能还是改善不了 因为OLTP系统每表超过 个索引即会降低性能 而且在一个sql 中 Oracle 从不能使用超过 个索引 当我们用到GROUP BY和ORDER BY时 ORACLE就会自动对数据进行排序 而ORACLE在INIT ORA中决定了sort_area_size区的大小 当排序不能在我们给定的排序区完成时 ORACLE就会在磁盘中进行排序 也就是我们讲的临时表空间中排序 过多的磁盘排序将会令 free buffer waits 的值变高 而这个区间并不只是用于排序的 对于开发人员我提出如下忠告: ) select update delete 语句中的子查询应当有规律地查找少于 %的表行 如果一个语句查找的行数超过总行数的 % 它将不能通过使用索引获得性能上的提高 ） 索引可能产生碎片 因为记录从表中删除时 相应也从表的索引中删除 表释放的空间可以再用 而索引释放的空间却不能再用 频繁进行删除操作的被索引的表 应当阶段性地重建索引 以避免在索引中造成空间碎片 影响性能 在许可的条件下 也可以阶段性地truncate表 truncate命令删除表中所有记录 也删除索引碎片 ） 在使用索引时一定要按索引对应字段的顺序进行引用 ） 用（+）比用NOT IN更有效率 降低ORACLE的竞争 先讲几个ORACLE的几个参数 这几个参数关系到ORACLE的竞争 ） freelists 和 freelist 组 他们负责ORACLE的处理表和索引的空间管理 ） pctfree 及 pctused 该参数决定了freelists 和 freelist 组的行为 pctfree 和pctused 参数的唯一目的就是为了控制块如何在 freelists 中进出设置好pctfree 及 pctused对块在freelists的移走和读取很重要 其他参数的设置 ） 包括SGA区（系统全局区） 系统全局区（SGA）是一个分配给Oracle 的包含一个 Oracle 实例的数据库的控制信息内存段 主要包括数据库高速缓存(the database buffer cache) 重演日志缓存（the redo log buffer） 共享池（the shared pool） 数据字典缓存（the data dictionary cache）以及其它各方面的信息 ） db_block_buffers（数据高速缓冲区）访问过的数据都放在这一片内存区域 该参数越大 Oracle在内存中找到相同数据的可能性就越大 也即加快了查询速度 ） share_pool_size （SQL共享缓冲池） 该参数是库高速缓存和数据字典的高速缓存 ） Log_buffer （重演日志缓冲区） ） sort_area_size（排序区） ） processes （同时连接的进程数） ） db_block_size （数据库块大小） Oracle默认块为 KB 太小了 因为如果我们有一个 KB的数据 则 KB块的数据库要读 次盘 才能读完 而 KB块的数据库只要 次就读完了 大大减少了I/O操作 数据库安装完成后 就不能再改变db_block_size的值了 只能重新建立数据库并且建库时 要选择手工安装数据库 ） open_links （同时打开的链接数） ） dml_locks ） open_cursors （打开光标数） ） dbwr_io_slaves （后台写进程数） lishixinzhi/Article/program/Oracle/201311/17955