①.、1、调整数据结构的设计.这一部分在开发信息系统之前完成,程序员需要考虑是否使用ORACLE数据库的分区功能,对于经常访问的数据库表是否需要建立索引等.
实际上,上述数据库优化措施之间是相互联系的.ORACLE数据库性能恶化表现基本上都是用户响应时间比较长,需要用户长时间的等待.但性能恶化的原因却是多种多样的,有时是多个因素共同造成了性能恶化的结果,这就需要数据库管理员有比较全面的计算机知识,能够敏感地察觉到影响数据库性能的主要原因所在.另外,良好的数据库管理工具对于优化数据库性能也是很重要的.
第一段： 磁盘方面调优
① 规范磁盘阵列
分离下面的东西,避免磁盘竞争
SYSTEM表空间
TEMPORARY表空间
UNDO表空间
联机重做日志（放在最快的磁盘上）
操作系统磁盘
ORACLE安装目录
经常被访问的数据文件
索引表空间
归档区域（应该总是与将要恢复的数据分离）
例：
通过下列语句查询确定IO问题
select name ,phyrds,phywrts,readtim,writetim
from v$filestat a,v$datafile b
where a.file#=b.file# order by readtim desc;
u 增大日志文件的大小,从而增加处理大型INSERT,DELETE,UPDATE操作的比例
查询日志文件状态
select a.member,b.* from v$logfile a,v$log b where a.GROUP#=b.GROUP#
查询日志切换时间
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';
修改三个初始参数：
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=CLOUDSEA_UNDO
UNDO_RETENTION=#of minutes
第二段： 初始化参数调优
① 重要初始化参数
l SGA_MAX_SIZE
l SGA_TARGET
l PGA_AGGREGATE_TARGET
l DB_CACHE_SIZE
l SHARED_POOL_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;
在调整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
正确地调整此参数可以同等可能地共享SQL语句,使得在内存中便能找到使用过的SQL语句.为了减少硬解析次数,优化对共享SQL区域的使用,需尽量使用存储过程、使用绑定变量
select ((1- sum(getmisses)/(sum(gets)+sum(getmisses)))*100) hitratio
from v$rowcache
where gets+getmisses 0;
SELECT SUM(PINS) "EXECUTIONS",SUM(RELOADS) "CACHE MISSES WHILE EXECUTING",1 - SUM(RELOADS)/SUM(PINS)
FROM V$LIBRARYCACHE;
第三段： SQL调优1. 使用提示
①1 改变执行路径
通过OPTIMIZER_MODE参数指定优化器使用方法,默认ALL_ROWS
ALL_ROWS 可得最佳吞吐量执行查询所有行
FIRST_ROWS(n) 可使优化器最快检索出第一行:
select /*+ FIRST_ROWS(1) */ store_id,... from tbl_store
允许开发人员改变访问的实际查询方式,经常使用INDEX提示
CLUSTER 强制使用集群
FULL
HASH
当不指定任何INDEX时,优化器会选择最佳的索引
SELECT /*+ INDEX */ STORE_ID FROM TBL_STORE
INDEX_DESC
INDEX_COMBINE 用来指定多个位图索引,而不是选择其中最好的索引
INDEX_JOIN 只需访问这些索引,节省了重新检索表的时间
INDEX_FFS 执行一次索引的快速全局扫描,只处理索引,不访问具体表
INDEX_SS
INDEX_SSX_ASC
INDEX_SS_DESC
NO_INDEX
NO_INDEX_FFS
NO_INDEX_SS
对于数据仓库非常有帮助
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
显示如何将连接表中的数据合并在一起,可用两提示直接影响连接顺序.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
NO_PARALLEL
NO_PARALLEL_INDEX
PARALLEL
PARALLEL_INDEX
PQ_DISTRIBUTE
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
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;
与V$SQLAREA类似
select * from
where rank_buffers 11
查看表上的索引
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'
如: select name from tbl where no=?
建立索引：create index test on tbl(name,no) tablespace cloudsea_index storage(....)
对于系统中很关键的查询,可以考虑建立此类连接索引
QUERY_REWRITE_ENALED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED (OR ENFORCED)
create index test on sum(test);
将常用的相对小的表缓存到内存中,但注意会影响到嵌套循环连接上的驱动表
alter table tablename cache;
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报表调整等待和闩锁
在$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数据库的分区功能,对于经常访问的数据库表是否需要建立索引等.
这一部分也是在开发信息系统之前完成,程序员在这一步需要考虑应用程序使用什么样的体系结构,是使用传统的Client/Server两层体系结构,还是使用Browser/Web/Database的三层体系结构.不同的应用程序体系结构要求的数据库资源是不同的.
应用程序的执行最终将归结为数据库中的SQL语句执行,所以呢SQL语句的执行效率最终决定了ORACLE数据库的性能.ORACLE公司推荐使用ORACLE语句优化器(OracleOptimizer)和行锁管理器(row-levelmanager)来调整优化SQL语句.
内存分配是在信息系统运行过程中优化配置的,数据库管理员可以根据数据库运行状况调整数据库系统全局区(SGA区)的数据缓冲区、日志缓冲区和共享池的大小;还可以调整程序全局区(PGA区)的大小.需要注意的是,SGA区不是越大越好,SGA区过大会占用操作系统使用的内存而引起虚拟内存的页面交换,这样反而会降低系统.
这一步是在信息系统开发之前完成的.数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上,做到硬盘之间I/O负载均衡.
例如：运行在UNIX操作系统上的ORACLE数据库,可以调整UNIX数据缓冲池的大小,每个进程所能使用的内存大小等参数.
第一段：ORACLE数据库性能优化工具
常用的数据库性能优化工具有：
ORACLE数据库在线数据字典,ORACLE在线数据字典能够反映出ORACLE动态运行情况,对于调整数据库性能是很有帮助的.
操作系统工具,例如UNIX操作系统的vmstat,iostat等命令可以查看到系统系统级内存和硬盘I/O的使用情况,这些工具对于管理员弄清出系统瓶颈出现在什么地方有时候很有用.
SQL语言跟踪工具(SQLTRACEFACILITY),SQL语言跟踪工具可以记录SQL语句的执行情况,管理员可以使用虚拟表来调整实例,使用SQL语句跟踪文件调整应用程序性能.SQL语言跟踪工具将结果输出成一个操作系统的文件,管理员可以使用TKPROF工具查看这些文件.
ORACLEEnterpriseManager(OEM),这是一个图形的用户管理界面,用户可以使用它方便地进行数据库管理而不必记住复杂的ORACLE数据库管理的命令.
EXPLAINPLAN——SQL语言优化命令,使用这个命令可以帮助程序员写出高效的SQL语言.
第二段：ORACLE数据库的系统性能评估
信息系统的类型不同,需要关注的数据库参数也是不同的.数据库管理员需要根据自己的信息系统的类型着重考虑不同的数据库参数.
①.、在线事务处理信息系统(OLTP),这种类型的信息系统一般需要有大量的Insert、Update操作,典型的系统包括民航机票发售系统、银行储蓄系统等.OLTP系统需要保证数据库的并发性、可靠性和最终用户的速度,这类系统使用的ORACLE数据库需要主要考虑下述参数：
数据库回滚段是否足够?
是否需要建立ORACLE数据库索引、聚集、散列?
系统全局区(SGA)大小是否足够?
SQL语句是否高效?
是否采用B*-索引或者bitmap索引?
是否采用并行SQL查询以提高查询效率?
是否采用PL/SQL函数编写存储过程?
有必要的话,需要建立并行数据库提高数据库的查询效率
第三段：SQL语句的调整原则
SQL语言是一种灵活的语言,相同的功能可以使用不同的语句来实现,但是语句的执行效率是很不相同的.程序员可以使用EXPLAINPLAN语句来比较各种实现方案,并选出最优的实现方案.总得来讲,程序员写SQL语句需要满足考虑如下规则：
①.、尽量使用索引.试比较下面两条SQL语句：
语句A：SELECTdname,deptnoFROMdeptWHEREdeptnoNOTIN
(SELECTdeptnoFROMemp);
语句B：SELECTdname,deptnoFROMdeptWHERENOTEXISTS
(SELECTdeptnoFROMempWHEREdept.deptno=emp.deptno);
这两条查询语句实现的结果是相同的,但是执行语句A的时候,ORACLE会对整个emp表进行扫描,没有使用建立在emp表上的deptno索引,执行语句B的时候,由于在子查询中使用了联合查询,ORACLE只是对emp表进行的部分数据扫描,并利用了deptno列的索引,所以语句B的效率要比语句A的效率高一些.
SELECTstuffFROMtabaa,tabbb,tabcc
WHEREa.acolbetween:alowand:ahigh
ANDb.bcolbetween:blowand:bhigh
ANDc.ccolbetween:clowand:chigh
ANDa.key1=b.key1
这个SQL例子中,程序员首先需要选择要查询的主表,因为主表要进行整个表数据的扫描,所以主表应该数据量最小,所以例子中表A的acol列的范围应该比表B和表C相应列的范围小.
第四段：CPU参数的调整
使用操作相同命令可以看到CPU的使用情况,一般UNIX操作系统的服务器,可以使用sar_u命令查看CPU的使用率,NT操作系统的服务器,可以使用NT的性能管理器来查看CPU的使用率.
数据库管理员还可以通过查看v$sesstat数据字典来获得当前连接ORACLE数据库各个会话占用的CPU时间,从而得知什么会话耗用服务器CPU比较多.
出现CPU资源不足的情况是很多的：SQL语句的重解析、低效率的SQL语句、锁冲突都会引起CPU资源不足.
①.、数据库管理员可以执行下述语句来查看SQL语句的解析情况：
SELECT*FROMV$SYSSTATWHERENAMEIN
('parsetimecpu','parsetimeelapsed','parsecount(hard)');
这里parsetimecpu是系统服务时间,parsetimeelapsed是响应时间,用户等待时间,waitetime=parsetimeelapsed_parsetimecpu
由此可以得到用户SQL语句平均解析等待时间=waitetime/parsecount.这个平均等待时间应该接近于0,如果平均解析等待时间过长,数据库管理员可以通过下述语句
SELECTSQL_TEXT,PARSE_CALLS,EXECUTIONSFROMV$SQLAREA
ORDERBYPARSE_CALLS;
来发现是什么SQL语句解析效率比较低.程序员可以优化这些语句,或者增加ORACLE参数SESSION_CACHED_CURSORS的值.
SELECTBUFFER_GETS,EXECUTIONS,SQL_TEXTFROMV$SQLAREA;
查看低效率的SQL语句,优化这些语句也有助于提高CPU的利用率.
第五段：内存参数的调整
内存参数的调整主要是指ORACLE数据库的系统全局区(SGA)的调整.SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区.
①.、共享池由两部分构成：共享SQL区和数据字典缓冲区,共享SQL区是存放用户SQL命令的区域,数据字典缓冲区存放数据库运行的动态信息.数据库管理员通过执行下述语句：
select(sum(pins-reloads))/sum(pins)"LibCache"fromv$librarycache;
select(sum(gets-getmisses-usage-fixed))/sum(gets)"RowCache"fromv$rowcache;
SELECTname,valueFROMv$sysstatWHEREnameIN('dbblockgets','consistentgets','physicalreads');
来查看数据库数据缓冲区的使用情况.查询出来的结果可以计算出来数据缓冲区的使用命中率=1-(physicalreads/(dbblockgets+consistentgets)).
selectname,valuefromv$sysstatwherenamein('redoentries','redologspacerequests');
查看日志缓冲区的使用情况.查询出的结果可以计算出日志缓冲区的申请失败率：
申请失败率=requests/entries,申请失败率应该接近于0,否则说明日志缓冲区开设太小,需要增加ORACLE数据库的日志缓冲区.
昆明北大青鸟java培训班转载自网络如有侵权请联系我们感谢您的关注谢谢支持