oraclesql参考手册
A. Oraclesql性能优化调整的几点基础事项
1. 选择最有效率的表名顺序 SQL的解析器按照从右到左的顺序处理FROM子句中的表名, FROM子句中包含多个表的情况下,你必须选择记录
1. 选择最有效率的表名顺序
SQL的解析器按照从右到左的顺序处理FROM子句中的表名, FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。
例如:
表 TAB1 16,384条记录 表 TAB2 1条记录
选择TAB2作为基础表 (最好的方法):
select count(*) from tab1,tab2 ----执行时间0.96秒
选择TAB1作为基础表 (不佳的方法):
select count(*) from tab2,tab1 ----执行时间26.09秒
2. 3个以上的表连接查询
如果有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
，
B. SQLServer和Oracle数据库分析(oraclesql性能分析)
分析原则：
1、具体问题具体分析（这是由于不同的应用系统，不同的测试目的，不同的性能关注点）
2、查找瓶颈时按以下顺序，由易到难。
服务器硬件瓶颈-〉网络瓶颈（对局域网，可以不考虑）-〉服务器操作系统瓶颈（参数配置）-〉中间件瓶颈（参数配置，数据库，web服务器等）-〉应用瓶颈（SQL语句、数据库设计、业务逻辑、算法等）注：以上过程并不是每个分析中都需要的，要根据测试目的和要求来确定分析的深度。对一些要求低的，我们分析到应用系统在将来大的负载压力（并发用户数、数据量）下，系统的硬件瓶颈在哪儿就够了。分段排除法很有效。
分析的信息来源：1、根据场景运行过程中的错误提示信息；
2、根据测试结果收集到的监控指标数据。
一、错误提示分析
分析实例：
1、Error:“10.10.10.30:8080〃:[10060]Connection
Error::Server“10.10.10.30〃
分析：
A、应用服务死掉（小用户时：程序上的问题。程序上处理数据库的问题）
B、应用服务没有死（应用服务参数设置问题）
例：在许多客户端连接Weblogic应用服务器被拒绝，而在服务器端没有错误显示，则有可能是Weblogic中的server元素的AeptBacklog属性值设得过低。如果连接时收到消息，说明应提高该值，每次增加25%
C、数据库的连接（1、在应用服务的性能参数可能太小了；2、数据库启动的最大连接数（跟硬件的内存有关）。）
分析：可能是以下原因造成
A、誉丛应用服务参庆掘樱数设置太大导致服务器的瓶颈；B、页面中图片太多；C、在程序处理表的时候检查字段太大多。
二．监控指标数据分析
1、最大并发用户数：
应用系统在当前环境（硬件环境、网络环境、软件环境（参数配置））下能承受的最大并发用户数。在方案运行中，如果出现了大于3个用户的业务操作失败，或出现了服务器shutdown的情况，则说明在当前环境下，系统承受不了当前并发用户的负载压力，那么最大并发用户数就是前一个没有出现这种现象的并发用户数。如果测得的最大并发用户数到达了性能要求，且各服务器资源情况良好，业务操作响应时间也达到了用户要求，那么可行。否则，再根据各服务器的资源情况和业务操作响应时间进一步分析原因所在。
2、业务操作响应时间：
分析方案运行情况应从平均事务响应时间图和事务性能摘要图开始。使用“事务性能摘要”图，可以确定在方案执行期间响应时间过长的事务。细分事务并分析每个页面组件的性能。如果服务器耗时过长，请使用相应的服务器图确定有问题的服务器度量并查明服务器性能下降的原因。如果网络耗时过长，请使用“网络监视器”图确定导致性能瓶颈的网络问题
3、服务器资源监控指标：内存：
1、UNIX资源监控中指标内存页交换速率（Pagingrate），如散衡果该值偶尔走高，表明当时有线程竞争内存。如果持续很高，则内存可能是瓶颈。也可能是内存访问命中率低。
2、Windows资源监控中，如果Process计数器和ProcessWorkingSet计数器的值在长时间内持续升高，同时Memory计数器的值持续降低，则很可能存在内存泄漏。
内存资源成为系统性能的瓶颈的征兆：很高的换页率()；进程进入不活动状态；交换区所有磁盘的活动次数可高；可高的全局系统CPU利用率；内存不够出错()。
处理器：
1、UNIX资源监控（Windows操作系统同理）中指标CPU占用率（），如果该值持续超过95%，表明瓶颈是CPU。可以考虑增加一个处理器或换一个更快的处理器。如果服务器专用于SQLServer，可接受的最大上限是80-85%合理使用的范围在60%至70%。
2、Windows资源监控中，如果System大于2，而处理器利用率（）一直很低，则存在着处理器阻塞。
CPU资源成为系统性能的瓶颈的征兆：很慢的响应时间()；CPU空闲时间为零()；过高的用户占用CPU时间()；过高的系统占用CPU时间()；长时间的有很长的运行进程队列()。
磁盘I/O：
1、UNIX资源监控（Windows操作系统同理）中指标磁盘交换率（Diskrate），如果该参数值一直很高，表明I/O有问题。可考虑更换更快的硬盘系统。
2、Windows资源监控中，如果DiskTime和Avg.DiskQueueLength的值很高，而PageReads/sec页面读取操作速率很低，则可能存在磁盘瓶径。
I/O资源成为系统性能的瓶颈的征兆：过高的磁盘利用率(highdiskutilization)；
太长的磁盘等待队列(largediskqueuelength)；
等待磁盘I/O的时间所占的百分率太高(/O)；
太高的物理I/O速率：largephysicalI/Orate(notsufficientinitself)；
过低的缓存命中率(lowbuffercachehitratio(notsufficientinitself))；
太长的运行进程队列，但CPU却空闲(largerunqueuewithidleCPU)。
4、数据库服务器：
SQLServer数据库：
1、SQLServer资源监控中指标缓存点击率（CacheHitRatio），该值越高越好。如果持续低于80%，应考虑增加内存。
2、如果FullScans/sec（全表扫描/秒）计数器显示的值比1或2高，则应分析你的查询以确定是否确实需要全表扫描，以及SQL查询是否可以被优化。
3、NumberofDeadlocks/sec(死锁的数量/秒)：死锁对应用程序的可伸缩性非常有害，并且会导致恶劣的用户体验。该计数器的值必须为0。
4、LockRequests/sec(锁请求/秒)，通过优化查询来减少读取次数，可以减少该计数器的值。
Oracle数据库：
1、如果自由内存接近于0而且库快存或数据字典快存的命中率小于0.90，那么需要增加SHARED_POOL_SIZE的大小。
快存（共享SQL区）和数据字典快存的命中率：select(sum(pins-reloads))/sum(pins)fromv$librarycache；
select(sum(gets-getmisses))/sum(gets)fromv$rowcache；
自由内存：select*fromv$sgastatwherename=‘freememory’。
2、如果数据的缓存命中率小于0.90，那么需要加大DB_BLOCK_BUFFERS参数的值（单位：块）。
缓冲区高速缓存命中率：selectname,valuefromv$sysstatwherenamein(‘dbblockgets’，‘consistentgets’‘physicalreads’)HitRatio=1-(physicalreads/(dbblockgetsconsistentgets))。
3、如果日志缓冲区申请的值较大，则应加大LOG_BUFFER参数的值。
日志缓冲区的申请情况：selectname，valuefromv$sysstatwherename=‘redologspacerequests’。
4、如果内存排序命中率小于0.95，则应加大SORT_AREA_SIZE以避免磁盘排序。
内存排序命中率：selectround((100*b.value)/decode((a.valueb.value)，0，1，(a.valueb.value)),2)fromv$sysstata，v$sysstatbwherea.name=’sorts(disk)’andb.name=’sorts(memory)’
注：上述SQLServer和Oracle数据库分析，只是一些简单、基本的分析，特别是Oracle数据库的分析和优化，是一门专门的技术，进一步的分析可查相关资料。
C. oracle 区别大小写吗
不区分大小写，关键字、用户、密码 、字段名 、表名、序列名 、触发器名等是不区分的，我们平时进入都是大小写随便输入的表名,字段名,关键字大小写忽略表名,字段名不能使用关键字，表名,字段名不可以使用数字开头,中间不能出现特殊符号表名,字段名长度不能超过30个字符表名,字段名定义需要有含义。具体字段值区分大小写。而scott是给初学者学习的用户，学习者可以用Scott登录系统，注意scott用户登录后，就可以使用Oracle提供的数据库和数据表，这些都是oracle提供。颤纤辩
(3)oraclesql参考手册扩展阅读
Oracle数据库中使用的都是sql语句，语法差异并不大。而sql语句对于大小写并不敏感，也就是说sql语句的语法可以任意使用。除了sql语句以外，我们还有自己的元素，比如表名、字段名，还有我们需要存入的数据，它们的大小写是需要DBM来管理的。
system、sys、scott，当中system和sys的差别在与是否能创建数据库，sys用户登录才干够竖袭创建数据库，而scott是给刚开始学习的人茄缺学习的用户。学习者能够用Scott登录系统，注意scott用户登录后，就能够使用Oracle提供的数据库和数据表，这些都是oracle提供的。
D. Oraclesql
前言
sql_trace 是我在工作中经常要用到的调优工具 相比较statspack 我更愿意用这个工具
因为数据逗搜库慢原因的 %以上是由于sql问题造成的 statspack没有sql的执行计划 显示没有它直观 方便 对想要针对性不强
介绍数据库调优需要经常会用到的工具 可以很精确地跟抓取相关session正在运行的sql 再通过tkprof分析出来sql的执行计划等相关信息 从而判断那些sql语句存在问题
统计如下信孝郑息（摘字官方文档）
Parse execute and fetch counts
CPU and elapsed times
Physical reads and logical reads
Number of rows processed
Misses on the library cache
Username under which each parse occurred
Each mit and rollback
使用
使用前需要注意的地方
初始化参数timed_statistics=true 允许sql trace 和其他的一些动态性能视图收集与时间（cpu elapsed）山慎历有关的参数 一定要打开 不然相关信息不会被收集 这是一个动态的参数 也可以在session级别设置
SQL>alter session set titimed_statistics=true
MAX_DUMP_FILE_SIZE跟踪文件的大小的限制 如果跟踪信息较多可以设置成unlimited 可以是KB MB单位 I开始默认为unlimited这是一个动态的参数 也可以在session级别设置
SQL>alter system set max_mp_file_size=
SQL>alter system set max_mp_file_size=unlimited
USER_DUMP_DEST指定跟踪文件的路径 默认路径实在$ORACLE_BASE/admin/ORA_SID/ump这是一个动态的参数 也可以在session级别设置
SQL>alter system set user_mp_dest=/oracle/trace
数据库级别
设置slq_trace参数为true会对整个实例进行跟踪 包括所有进程 用户进程和后台进程 会造成比较严重的性能问题 生产环境一定要慎用
SQL>alter system set sql_trace=true;
Session级别
当前会话
SQL>alter session set sql_trace=true;
SQL>alter session set sql_trace=false;
其他会话
通过oracle提供的系统包 DBMS_SYSTEM SET_SQL_TRACE_IN_SESSION来实现
SQL>execute dbms_system set_sql_trace_in_session(sid serial# true);
SQL>execute dbms_system set_sql_trace_in_session(sid serial# false);
注
sid serial#从v$session视图中获得
DBMS_SYSTEM包里还可以对其他用户的参数（如 timed_statistics max_mp_file）进行设置 在这不做介绍了 很少用到 想了解dbms_system里的程序包可以desc dbms_system看一下
得到trace文件后我们要用tkprof他进行格式化 通过sql语句快速定位到相应的trace文件
Tkprof
tkprof的目的是将sql trace生成的跟踪文件转换成用户可以理解的格式
格式
tkprof tracefile outputfile [optional | parameters ]
参数和选项（这里只介绍最常用的 也是最实用的）
explain=user/password执行explain命令将结果放在SQL trace的输出文件中
sys=[yes/no]确定系统是否列出由sys用户产生或重调的sql语句
sort=sort_option按照指定的方法对sql trace的输出文件进行降序排序
sort_option选项
prscnt按解析次数排序
prscpu按解析所花cpu时间排序
prsela按解析所经历的时间排序
prsdsk按解析时物理的读操作的次数排序
prsqry按解析时以一致模式读取数据块的次数排序
prscu按解析时以当前读取数据块的次数进行排序
execnt按执行次数排序
execpu按执行时花的cpu时间排序
exeela按执行所经历的时间排序
exedsk按执行时物理读操作的次数排序
exeqry按执行时以一致模式读取数据块的次数排序
execu按执行时以当前模式读取数据块的次数排序
exerow按执行时处理的记录的次数进行排序
exemis按执行时库缓冲区的错误排序
fchcnt按返回数据的次数进行排序
fchcpu按返回数据cpu所花时间排序
fchela按返回数据所经历的时间排序
fchdsk按返回数据时的物理读操作的次数排序
fchqry按返回数据时一致模式读取数据块的次数排序
fchcu按返回数据时当前模式读取数据块的次数排序
fchrow按返回数据时处理的数据数量排序
注
这些排序中我经常用到的是fchdsk fckchela fchqry 因为有问题的sql一般都是大的查询造成的 当然更新 插入 删除时也会存在全表扫描 这就需要:exedsk exeqry exeela等选项 根据具体情况具体分析
Cpu时间和Elapsed时间都是以秒为单位 而且两个值基本上一样 但我比较常用elapsed 他是反映的用户相应时间 从运行sql到用户得到结果的时间 会更实际些
tkprof输出文件各列的含义 （理解下面的含义对我们快速定位问题很有帮助）
parse:
将sql语句转换成执行计划 包括检查是否有正确的授权 需要到得表 列及其他引用到得对象是否存在 这些信息分别存在v$librarycache v$rowcache
execute
oracle实际执行的语句 如 insert update delete 这些会修改数据 对于select操作 这部只是确定选择的行数
fetch
返回查询获得的行数 只有执行select会被收集
Count
这个语句被parse execute fetch的次数的统计
Cpu
这个语句所有的parse execute fetch所用的cpu总的时间 以秒为单位 如果TIMED_STATISTICS 关闭的话 值为
Elapsed
这个语句所有的parse execute fetch所消耗的总的时间 以秒为单位 如果TIMED_STATISTICS 关闭的话 值为
Disk
这个语句所有的parse execute fetch从磁盘上的数据文件中读取的数据块的数量
Query
在一致性读的模式下 这个语句所有的parse execute fetch所获取的buffer数量（这部分是从内存读取的也就是逻辑读取的 相当于执行计划里的consistent gets）
Current
在current模式下 这个语句所有的parse execute fetch所获取的buffer数量 一般是current模式下发生的delect insert update的操作都会获取buffer
Rows
语句返回的行数 不包括子查询中返回的记录数目 对于select语句 返回在fetch这步 对于insert delete update操作 返回记录是在execute这步
分析
我一般的思路步骤是
先找磁盘多的sq l(sort= fchdsk ) 意味着全表扫描 找运行时间长的(sort= fchela) 意味着sql可能写的不好或磁盘 逻辑读较多 找出一致性读较多的(sort= fchqry) 当表不是很大的时候（可能全部缓存住了） 没有发生磁盘读 但不意味着不需要建立索引 或者sql需要优化 找出当前模式从缓冲区获得数据的数量(sort=exedsk exeela exeqry) 这些主要集中在dml语句里的操作 看是否有必要优化sql或建立索引之所以排序是为了在sql很多的时候快速定位sql 如果sql比较少的话就没必要排序了 但我们要有分析问题的思路
举例
我自己建立了一个表
create table t (id int);
begin
for v in loop
insert into t values(v );
end loop
mit;
end;
下面是sql_trace所抓到得sql
不正常状态
*******************************************************************************
select *
from t
where id=
call count cpu elapsed disk query current rows
Parse Execute Fetch
total
Misses in library cache ring parse:
Optimizer goal: CHOOSE
Parsing user id: (WH)
Rows Row Source Operation
TABLE ACCESS FULL T
Rows Execution Plan
SELECT STATEMENT GOAL: CHOOSE
TABLE ACCESS (FULL) OF T
首先这是一个select语句 它走了全部扫描
磁盘读（ ）和逻辑读（ ）都很多
运行了 次（Execute） 分析了 次（Parse） 一共用了将近 秒（elapsed）
我只是选择表的一行的数据的结果 就发生这么大的成本 很显然是全表扫描的结果造成的
正常状态
在做跟踪前我为这个表建立了一个索引
Create index t on t (id);
*******************************************************************************
select *
from t
where id=
call count cpu elapsed disk query current rows
Parse Execute Fetch
total
Misses in library cache ring parse:
Optimizer goal: CHOOSE
Parsing user id: (WH)
Rows Row Source Operation
INDEX RANGE SCAN T (object id )
Rows Execution Plan
SELECT STATEMENT GOAL: CHOOSE
INDEX (RANGE SCAN) OF T (NON UNIQUE)
*******************************************************************************
同样的语句
它走了索引 物理读 这个 其实是开始读索引时需要第一次读入的 以后运行就没有了
逻辑读 （平均这个sql一次 个逻辑读）
同样运行了 次（Execute）
分析了 次（Parse） 运行次数越多 分析次数越少越好一共只用了 秒（elapsed）
lishixin/Article/program/Oracle/201311/17866