怎么优化oracle数据库

1、1、调整数据结构的设计。这一部分在开发信息系统之前完成，程序员需要考虑是否使用ORACLE数据库的分区功能，对于经常访问的数据库表是否需要建立索引等。

成都创新互联公司专注于企业成都全网营销、网站重做改版、岑巩网站定制设计、自适应品牌网站建设、H5场景定制、商城网站建设、集团公司官网建设、成都外贸网站建设公司、高端网站制作、响应式网页设计等建站业务，价格优惠性价比高，为岑巩等各大城市提供网站开发制作服务。

2、2、调整应用程序结构设计。这一部分也是在开发信息系统之前完成，程序员在这一步需要考虑应用程序使用什么样的体系结构，是使用传统的Client/Server两层体系结构，还是使用Browser/Web/Database的三层体系结构。不同的应用程序体系结构要求的数据库资源是不同的。

3、3、调整数据库SQL语句。应用程序的执行最终将归结为数据库中的SQL语句执行，因此SQL语句的执行效率最终决定了ORACLE数据库的性能。ORACLE公司推荐使用ORACLE语句优化器（Oracle Optimizer）和行锁管理器（row-level manager）来调整优化SQL语句。

4、4、调整服务器内存分配。内存分配是在信息系统运行过程中优化配置的，数据库管理员可以根据数据库运行状况调整数据库系统全局区（SGA区）的数据缓冲区、日志缓冲区和共享池的大小；还可以调整程序全局区（PGA区）的大小。需要注意的是，SGA区不是越大越好，SGA区过大会占用操作系统使用的内存而引起虚拟内存的页面交换，这样反而会降低系统。

5、5、调整硬盘I/O，这一步是在信息系统开发之前完成的。数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间I/O负载均衡。

6、6、调整操作系统参数，例如：运行在UNIX操作系统上的ORACLE数据库，可以调整UNIX数据缓冲池的大小，每个进程所能使用的内存大小等参数。

实际上，上述数据库优化措施之间是相互联系的。ORACLE数据库性能恶化表现基本上都是用户响应时间比较长，需要用户长时间的等待。但性能恶化的原因却是多种多样的，有时是多个因素共同造成了性能恶化的结果，这就需要数据库管理员有比较全面的计算机知识，能够敏感地察觉到影响数据库性能的主要原因所在。另外，良好的数据库管理工具对于优化数据库性能也是很重要的。

ORACLE数据库性能优化工具

常用的数据库性能优化工具有：

1、1、ORACLE数据库在线数据字典，ORACLE在线数据字典能够反映出ORACLE动态运行情况，对于调整数据库性能是很有帮助的。

2、2、操作系统工具，例如UNIX操作系统的vmstat，iostat等命令可以查看到系统系统级内存和硬盘I/O的使用情况，这些工具对于管理员弄清出系统瓶颈出现在什么地方有时候很有用。

3、3、SQL语言跟踪工具（SQL TRACE FACILITY），SQL语言跟踪工具可以记录SQL语句的执行情况，管理员可以使用虚拟表来调整实例，使用SQL语句跟踪文件调整应用程序性能。SQL语言跟踪工具将结果输出成一个操作系统的文件，管理员可以使用TKPROF工具查看这些文件。

4、4、ORACLE Enterprise Manager（OEM），这是一个图形的用户管理界面，用户可以使用它方便地进行数据库管理而不必记住复杂的ORACLE数据库管理的命令。

5、5、EXPLAIN PLAN——SQL语言优化命令，使用这个命令可以帮助程序员写出高效的SQL语言。

ORACLE数据库的系统性能评估

信息系统的类型不同，需要关注的数据库参数也是不同的。数据库管理员需要根据自己的信息系统的类型着重考虑不同的数据库参数。

1、1、在线事务处理信息系统（OLTP），这种类型的信息系统一般需要有大量的Insert、Update操作，典型的系统包括民航机票发售系统、银行储蓄系统等。OLTP系统需要保证数据库的并发性、可靠性和最终用户的速度，这类系统使用的ORACLE数据库需要主要考虑下述参数：

l l 数据库回滚段是否足够？

l l 是否需要建立ORACLE数据库索引、聚集、散列？

l l 系统全局区（SGA）大小是否足够？

l l SQL语句是否高效？

2、2、数据仓库系统（Data Warehousing），这种信息系统的主要任务是从ORACLE的海量数据中进行查询，得到数据之间的某些规律。数据库管理员需要为这种类型的ORACLE数据库着重考虑下述参数：

l l 是否采用B*-索引或者bitmap索引？

l l 是否采用并行SQL查询以提高查询效率？

l l 是否采用PL/SQL函数编写存储过程？

l l 有必要的话，需要建立并行数据库提高数据库的查询效率

SQL语句的调整原则

SQL语言是一种灵活的语言，相同的功能可以使用不同的语句来实现，但是语句的执行效率是很不相同的。程序员可以使用EXPLAIN PLAN语句来比较各种实现方案，并选出最优的实现方案。总得来讲，程序员写SQL语句需要满足考虑如下规则：

1、1、尽量使用索引。试比较下面两条SQL语句：

语句A：SELECT dname, deptno FROM dept WHERE deptno NOT IN

(SELECT deptno FROM emp);

语句B：SELECT dname, deptno FROM dept WHERE NOT EXISTS

(SELECT deptno FROM emp WHERE dept.deptno = emp.deptno);

这两条查询语句实现的结果是相同的，但是执行语句A的时候，ORACLE会对整个emp表进行扫描，没有使用建立在emp表上的deptno索引，执行语句B的时候，由于在子查询中使用了联合查询，ORACLE只是对emp表进行的部分数据扫描，并利用了deptno列的索引，所以语句B的效率要比语句A的效率高一些。

2、2、选择联合查询的联合次序。考虑下面的例子：

SELECT stuff FROM taba a, tabb b, tabc c

WHERE a.acol between :alow and :ahigh

AND b.bcol between :blow and :bhigh

AND c.ccol between :clow and :chigh

AND a.key1 = b.key1

AMD a.key2 = c.key2;

这个SQL例子中，程序员首先需要选择要查询的主表，因为主表要进行整个表数据的扫描，所以主表应该数据量最小，所以例子中表A的acol列的范围应该比表B和表C相应列的范围小。

3、3、在子查询中慎重使用IN或者NOT IN语句，使用where (NOT) exists的效果要好的多。

4、4、慎重使用视图的联合查询，尤其是比较复杂的视图之间的联合查询。一般对视图的查询最好都分解为对数据表的直接查询效果要好一些。

5、5、可以在参数文件中设置SHARED_POOL_RESERVED_SIZE参数，这个参数在SGA共享池中保留一个连续的内存空间，连续的内存空间有益于存放大的SQL程序包。

6、6、ORACLE公司提供的DBMS_SHARED_POOL程序可以帮助程序员将某些经常使用的存储过程“钉”在SQL区中而不被换出内存，程序员对于经常使用并且占用内存很多的存储过程“钉”到内存中有利于提高最终用户的响应时间。

CPU参数的调整

CPU是服务器的一项重要资源，服务器良好的工作状态是在工作高峰时CPU的使用率在90％以上。如果空闲时间CPU使用率就在90％以上，说明服务器缺乏CPU资源，如果工作高峰时CPU使用率仍然很低，说明服务器CPU资源还比较富余。

使用操作相同命令可以看到CPU的使用情况，一般UNIX操作系统的服务器，可以使用sar –u命令查看CPU的使用率，NT操作系统的服务器，可以使用NT的性能管理器来查看CPU的使用率。

数据库管理员可以通过查看v$sysstat数据字典中“CPU used by this session”统计项得知ORACLE数据库使用的CPU时间，查看“OS User level CPU time”统计项得知操作系统用户态下的CPU时间，查看“OS System call CPU time”统计项得知操作系统系统态下的CPU时间，操作系统总的CPU时间就是用户态和系统态时间之和，如果ORACLE数据库使用的CPU时间占操作系统总的CPU时间90％以上，说明服务器CPU基本上被ORACLE数据库使用着，这是合理，反之，说明服务器CPU被其它程序占用过多，ORACLE数据库无法得到更多的CPU时间。

数据库管理员还可以通过查看v$sesstat数据字典来获得当前连接ORACLE数据库各个会话占用的CPU时间，从而得知什么会话耗用服务器CPU比较多。

出现CPU资源不足的情况是很多的：SQL语句的重解析、低效率的SQL语句、锁冲突都会引起CPU资源不足。

1、数据库管理员可以执行下述语句来查看SQL语句的解析情况：

SELECT * FROM V$SYSSTAT

WHERE NAME IN

('parse time cpu', 'parse time elapsed', 'parse count (hard)');

这里parse time cpu是系统服务时间，parse time elapsed是响应时间，用户等待时间

waite time = parse time elapsed – parse time cpu

由此可以得到用户SQL语句平均解析等待时间＝waite time / parse count。这个平均等待时间应该接近于0，如果平均解析等待时间过长，数据库管理员可以通过下述语句

SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA

ORDER BY PARSE_CALLS;

来发现是什么SQL语句解析效率比较低。程序员可以优化这些语句，或者增加ORACLE参数SESSION_CACHED_CURSORS的值。

2、数据库管理员还可以通过下述语句：

SELECT BUFFER_GETS, EXECUTIONS, SQL_TEXT FROM V$SQLAREA;

查看低效率的SQL语句，优化这些语句也有助于提高CPU的利用率。

3、3、数据库管理员可以通过v$system_event数据字典中的“latch free”统计项查看ORACLE数据库的冲突情况，如果没有冲突的话，latch free查询出来没有结果。如果冲突太大的话，数据库管理员可以降低spin_count参数值，来消除高的CPU使用率。

内存参数的调整

内存参数的调整主要是指ORACLE数据库的系统全局区（SGA）的调整。SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区。

1、 1、 共享池由两部分构成：共享SQL区和数据字典缓冲区，共享SQL区是存放用户SQL命令的区域，数据字典缓冲区存放数据库运行的动态信息。数据库管理员通过执行下述语句：

select (sum(pins - reloads)) / sum(pins) "Lib Cache" from v$librarycache;

来查看共享SQL区的使用率。这个使用率应该在90％以上，否则需要增加共享池的大小。数据库管理员还可以执行下述语句：

select (sum(gets - getmisses - usage - fixed)) / sum(gets) "Row Cache" from v$rowcache;

查看数据字典缓冲区的使用率，这个使用率也应该在90％以上，否则需要增加共享池的大小。

2、 2、 数据缓冲区。数据库管理员可以通过下述语句：

SELECT name, value FROM v$sysstat WHERE name IN ('db block gets', 'consistent gets','physical reads');

来查看数据库数据缓冲区的使用情况。查询出来的结果可以计算出来数据缓冲区的使用命中率＝1 - ( physical reads / (db block gets + consistent gets) )。

这个命中率应该在90％以上，否则需要增加数据缓冲区的大小。

3、 3、 日志缓冲区。数据库管理员可以通过执行下述语句：

select name,value from v$sysstat where name in ('redo entries','redo log space requests');查看日志缓冲区的使用情况。查询出的结果可以计算出日志缓冲区的申请失败率：

申请失败率＝requests/entries，申请失败率应该接近于0，否则说明日志缓冲区开设太小，需要增加ORACLE数据库的日志缓冲区。

如何设置使oracle10g性能最优 性能调优 步骤

一、 磁盘方面调优

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;

oracle性能优化如何做

性能优化大体分为两部分，一个是内存优化，一个是语句优化。语句优化核心就是避免全表查询，内存优化核心是最大化发挥数据库性能提高运行效率。想简单了解性能优化够你学个一两个月了，要想深入了解到精通没有三五年下不来。

如何提高oracle数据库的性能

在公路建设中，通过建立多条车道可以提高道路的流量。其实这个道理在Oracle数据库中也行得通。即可以将关键数据文件存储在多块硬盘上，以提高Oracle数据库的性能。可惜的是，不少数据库管理员没有意识到这一点。在这篇文章中笔者就以Oracle11G为例，说明如何通过在硬盘之间分布关键数据文件来提高性能。 一、在硬盘之间分布关键数据文件的基本原则。

在传统的文件系统上(即不是在裸机上)部署Oracle数据库，可以通过将关键的数据文件分布到多个可用的文件系统上或者不同的硬盘上来提高数据库的性能。具体的来说，需要遵循如下几个原则。

一是对于表来说，往往包含两个部分，即基本表与索引表。只要为基本表中的字段创建了索引，其对应的就有一张索引表。当用户访问表中的数据时，应用系统需要同时访问到索引表与数据表。此时我们可以将这两张表比喻成两辆车。如果现在只有一个车道(即将他们同时存放在一个硬盘或者文件系统中)，那么两辆车必须前后行使。而如果现在有两个车道(即将基本表与其相对应的索引表存放在不同的硬盘或者文件系统中)，那么这两辆车就可以并排行使。显然，后者的效率更高。为此笔者建议，可将经常需要访问的表和与之对应的索引表分开来存放。

二是可以将日志文件也分开来存放。不光光是数据表与索引表存在着这种状况。其实在日志文件管理中也是如此。只要条件允许，那么最好能够将联机重做日志和归档日志与其它数据文件存放在不同的硬盘或者文件系统上。因为当用户往数据库中写入数据时，需要同时往数据文件与重做日志文件中写入数据。此时如果将它们分开来存放，那么就相当于有了多条车道，分别往不同的文件中写入数据。这无疑就可以提高数据写入的效率，从而提高数据库的性能。

二、哪些文件最好能够分开存放?

在讲到硬盘之间分布关键数据文件的基本原则的时候，笔者举了几个需要分开存放的几个案例。但是在实际工作中，并不仅仅局限于上面提到的这些文件。笔者认为，如果条件允许的话，那么可以考虑将如下文件放置在不同的硬盘上。

一是表空间，如临时表空间、系统表空间、UNDO表空间等等。这三个表空间可能系统会同时进行访问。为此需要将其分开来存放。二是数据文件和索引文件。上面提到过，需要将经常访问的数据文件与其对应的索引文件存放在不同的硬盘上。因为这两类文件在访问数据时也可能会同时访问到。三是操作系统盘与数据库文件单独存放。显然Oracle系统肯定是与操作系统同时运行的。为了避免他们之间的I/Q冲突，就需要将Oracle部署在操作系统盘以外的磁盘上。四是联机重做日志文件。这个文件比较复杂，不但要将其与其他文件分开来存放。而且还需要注意的是，最好能够将其存放在性能最佳的硬盘上。

最后需要说明的一点是，增加磁盘也会增加成本。这不光光是购买磁盘所需要的花费，还包括管理的成本。所以这之间也会涉及到成本与性能之间的一个均衡问题。如果企业的数据不是很多，或者主要是涉及到查询操作，那么这么设计的话，就可能不怎么合理。因为投入要大于回报。

三、如何确定是否需要将文件分开来存放?

在实际工作中，企业的数据是一个从少到多的过程。也就是说，刚开始使用数据库的时候，可能数据量比较少，此时出于成本的考虑，没有将相关文件存放在不同的磁盘上。但是随着工作的深入，用户会发现数据库的性能在逐渐的降低。此时管理员就需要考虑，能够采取这种多建车道的措施，来提高数据库性能。当然在采取这个措施之前，管理员需要先进性评估。此时评估所需要用到的一个指标就是磁盘的I/O争用。

磁盘争用通常发生在有多个进程试图同时访问一个物理磁盘的情况下。如现在用户需要访问某个数据表中的数据，此时系统需要访问索引文件与数据表文件。如果将它们放置在同一磁盘上，那么在访问时就会发生I/O冲突。所以评估I/O冲突的严重程度，可以帮我们来确定是否需要将关键文件存放在不同的磁盘上。

将I/O平均的分布到多个可用的磁盘上，这可以有效的减少磁盘之间的争用情况，提高数据存储与读取的性能。从而提高Oracle等应用程序的效率。在实际工作中，数据库控制文件中有两个参数可以用来帮助我们评估这个指标。这两个参数是文件平均读取时间和文件平均写入时间。不过在使用这两个参数的时候，其只评估所有与数据库相关联的文件。管理员如果有需要的话，也可以通过下面的查询语句来查询数据文件是否存在I/O问题。查询的语法与结果如下图所示：

从如上的查询结果中可以看出某个数据文件是否繁忙，数据文件之间是否存在着/I/O冲突文件。这里需要注意的是，这个结果是一个动态的结果。在不同的时刻、用户进行不同的操作时往往会得出不同的结论。为此笔者建议，在使用这个数据的时候，最好能够多跟踪几次。然后分析多次运行的结果。只有如此，才能够得到比较合乎情理的判断。 通常情况下，管理员根据上面的结果可以得出三种结论。

第一种结论是上面这些数据文件都不是很忙。即文件的平均读取时间与写入时间都比较短，表示这两个文件都是比较空闲的。此时正常情况下，数据库的性能应该是不错的。也就是说，如果此时数据库的性能不理想的话，那么就不是磁盘的I/O所造成的。管理员应该从其他角度来改善数据库的性能。

第二种结论是每个数据库文件都非常的繁忙。此时有可能是读取时间或者写入时间比较长，或者说两个时间都比较长。当多个数据文件同时比较繁忙并且他们处于同一磁盘的话，那么管理员就需要考虑购买新的磁盘，然后将上面提到的这些关键文件重新整理，让他们部署在不同的磁盘上。

第三种结论是某几个特定的数据文件比较繁忙，而其他数据文件还可以。此时管理员如果成本受到限制，那么也不需要重新购买硬盘。在磁盘上的物理写入和读取次数上如果出现比较大的差异，就表明某个磁盘负载过大，即有很严重的I/O冲突。此时最好能够将这个磁盘中的文件进行调整，如将某些文件移动到另外的一块I/O相对不怎么严重的磁盘上。不过在采取这个操作的时候，需要注意一点。对于联机重做日志文件来说，即使其所在的磁盘I/O冲突比较低，或者访问这个文件的时间比较短，但是也不建议将其他数据文件转移到其所在的磁盘上来。因为通常情况下，为了保障数据库的性能，我们都建议将联机重做日志文件单独存放，并且还需要讲起放置在性能比较高的硬盘上。

总之，将关键的Oracle数据库文件分开放置。如此的话可以有效避免磁盘争用成为Oracle数据库系统的性能瓶颈。

如何提高oracle模糊查询的性能？

1、使用两边加‘%’号的查询，Oracle是不通过索引的，所以查询效率很低。

例如：select count(*) from lui_user_base t where t.user_name like '%cs%';

2、like '...%'和 like'%...'虽然走了索引，但是效率依然很低。

3、有人说使用如下sql，他的效率提高了10倍，但是数据量小的时候

select count(*) from lui_user_base where rowid in (select rowid from lui_user_base t where t.user_name like '%cs%')

我拿100w跳数据做了测试，效果一般，依然很慢，原因：

select rowid from lui_user_base t where t.user_name like '%cs%'   这条sql执行很快，那是相当的快，但是放到select count(*) from lui_user_base where rowid in（）里后，效率就会变的很慢了。

4、select count(*) from lui_user_base t where instr(t.user_name,'cs') 0

这种查询效果很好，速度很快，推荐使用这种。因为我对oracle内部机制不是很懂，只是对结果做了一个说明。

5、有人说了用全文索引，我看了，步骤挺麻烦，但是是个不错的方法，留着备用：

对cmng_custominfo 表中的address字段做全文检索：

1，在oracle9201中需要创建一个分词的东西：

BEGIN

ctx_ddl.create_preference ('SMS_ADDRESS_LEXER', 'CHINESE_LEXER');

--ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer'); 不用

end;

2，创建全文检索：

CREATE INDEX INX_CUSTOMINFO_ADDR_DOCS ON cmng_custominfo(address) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER SMS_ADDRESS_LEXER');

3，查询时候，使用：

select * from cmng_custominfo where contains (address, '金色新城')1;

4，需要定期进行同步和优化：

同步：根据新增记录的文本内容更新全文搜索的索引。

begin

ctx_ddl.sync_index('INX_CUSTOMINFO_ADDR_DOCS');

end;

优化：根据被删除记录清除全文搜索索引中的垃圾

begin

ctx_ddl.optimize_index('INX_CUSTOMINFO_ADDR_DOCS', 'FAST');

end;

5，采用job做步骤4中的工作：

1)该功能需要利用oracle的JOB功能来完成

因为oracle9I默认不启用JOB功能，所以首先需要增加ORACLE数据库实例的JOB配置参数:

job_queue_processes=5

重新启动oracle数据库服务和listener服务。

2)同步 和 优化

--同步 sync:

variable jobno number;

BEGIN

DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.sync_index(''INX_CUSTOMINFO_ADDR_DOCS'');',

SYSDATE, 'SYSDATE + (1/24/4)');

commit;

END;

--优化

variable jobno number;

begin

DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.optimize_index(''INX_CUSTOMINFO_ADDR_DOCS'',''FULL'');', SYSDATE, 'SYSDATE + 1');

commit;

END;

其中， 第一个job的SYSDATE + (1/24/4)是指每隔15分钟同步一次，第二个job的SYSDATE + 1是每隔1天做一次全优化。具体的时间间隔，可以根据应用的需要而定。

6，索引重建

重建索引会删除原来的索引，重新生成索引，需要较长的时间。

重建索引语法如下：

ALTER INDEX INX_CUSTOMINFO_ADDR_DOCS REBUILD;

据网上一些用家的体会，oracle重建索引的速度也是比较快的，有一用家这样描述：

Oracle 的全文检索建立和维护索引要比ms sql server都要快得多，笔者的65万记录的一个表建立索引只需要20分钟，同步一次只需要1分钟。

因此，也可以考虑用job的办法定期重建索引。

文章标题：oracle怎么提高性能,如何提高oracle数据库的性能
网站URL：http://pwwzsj.com/article/phiieg.html