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_dump_file_size=

SQL>alter system set max_dump_file_size=unlimited

USER_DUMP_DEST指定跟踪文件的路径 默认路径实在$ORACLE_BASE/admin/ORA_SID/udump这是一个动态的参数 也可以在session级别设置

SQL>alter system set user_dump_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_dump_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 during 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 during 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）

lishixinzhi/Article/program/Oracle/201311/17866

成都尚观怎么样，我想去培训（客观评价）去培训过的进！谢谢

尚观千万不要去 本人被坑产了 当时机构请了一个垃圾老师 根本不管学生 花了一万多 真鸡儿坑 发了一本书天天对着书敲代码 IT不是人人都合适的 不要跟风

北京尚观的linux课程有谁学过老师教的怎么样

没有学过他们的课，由于长期使用red hat系的 *** 作系统，然后报名参加了尚观的RHCE考试，回他们会答给个光盘（光盘是课程录像，可以看看，但是得有点点基础，感觉还行），一次性过了。如果要做网管和运维等的工作，这只是个敲门砖，掌握情况就看你自己感兴趣不，以我的经验来看，大多单位不看这个证书，当然同等能力下优先考虑有证的，主要看经验和处理问题的能力。

百度网盘下载的尚观RHCE课程培训112集完整版.zip有密码 有人知道是啥吗

oracle

刚报了尚观科技的软件测试的课程谁了解他们其中和LINUX相关的内容的来回答一下

ULA-121

测试相关Linux系统调优

性能优化的原则与概述，测试基准及Unix性能测试工具

Linux系统版性能监视

内核参数与运行状态调权优

存储调优

文件系统调优

存储硬件表述

限制对系统资源的使用保证关键业务

网络性能的监视与调优

Web性能的监视与调优

网络文件传输的调优

Mail系统的优化

数据库调优

Mysql数据库的性能调优

编译优化

阶段目标：在Linux平台下的大型软件系统由各种各样的负责应用构成，对于此类软件系统进行性能测试，需要理解影响整个应用的各种因素。本课程中除了详细介绍有关Linux系统本身的调节与优化，还详细讲述了各种应用子系统的性能优化，如J2EE系统，Web系统，数据库系统等。

内容摘自尚观软件测试网站

我想在北京学LINUX 主机部分的课程，请问尚观和荣新哪个更好些

我是在另一家培训机构退了费，然后去尚观的，结果是：没有最烂，只有更烂！还不如之前的呢！

尚观的 Linux 培训课程效果怎么样

每个linux培训机构其实教的基础知识都是大同小异的，关键在于自己的努力学习，再好的老师，如果你自己不努力学习，最后还是不行，可以关注《Linux就该这么学》参加刘老师的培训。

上海尚观嵌入式培训中心的课程体系结构

课程大抄纲

◆ ULA 尚观袭Linux集群架构师部分课程大纲

ULA 尚观Linux集群架构师部分课程大纲·

ULA-125. Linux 存储以及Linux集群技术 与 Linux 高级路由·

ULA-121 Linux高级性能调优 ·

ULA-128 Linux系统高级安全方案·

ULA-P-122 实践系列课程 ULA-P Shell编程课程 ULA-P 网络服务案例课程 ULA-P J2EE中间件部分·

ULE金牌三证课程 （ULA的基础课程部分）

◆ UOA-尚观Oracle集群架构师 连读课程

·Linux下Oracle数据库初步SQL语句基础

·Oracle9i/10g 数据库构建与管理

·Oracle9i/10g 备份与恢复

·Oracle 数据库调优

·Oracle Dataguard构建

·Oracle RAC集群构建课程

·Oracle Dataguard 构建

·Oracle RAC集群构建课程

·Linux下Oracle数据库初步

·Linux平台全套Oracle 9i + 10g OCP课程

尚观培训好不好托就别来了。

广州尚观是北京尚观科技有限公司的广州分公司，尚观的师资是全国分享的回，尚观由于需要输答送大量相关人员，进行多年的人才培养模式的积累，独创出十多项国内独家的职业训练专题和实际项目，面向初入it行业的新手，提供了可以真正超越入职水平的职业训练，而且每年使数千名学员成功达到5万以上年薪，这些学员大多数输送至尚观的合作伙伴中，如：网络/搜狐/华为/阿里巴巴/盛大/intel等一流it公司，这些详细就业信息已经发布于网站，我们是业内唯一能做到每月至少更新一次就业信息，诚信对企业，诚信对学员的it服务及培训公司。

linux是尚观的主打课程，挺牛的！你要是想学习linux，就去他们网站上看看吧，上面有他们的联系方式，也可以去他们那边看看，眼见为实嘛！祝你学习成功！

我准备去尚观学他们UTA的软件测试课程，但是听说有测试，想了解一下都有什么测试内容

阶段目标：软件测试课程是进阶课程，通过边学边练的方式，重点学习和应用软件测试中的常用方法、技术，以及主流测试工具；在本阶段的学习过程中，您将结合前面学习的内容，在老师的带领下，完成一个实际的大型CRM软件项目的测试。 需求分析和跟踪管理，UML分析和建模技术。 评审技术，同行评审（包括需求评审、设计评审、代码走读等）。 测试用例设计方法（等价类划分、边界值、正交表等） CTE XL的使用、自己动手编写一个正交表用例生成工具。 单元测试及xUnit的使用、模拟对象技术（NMock） 单元级别性能测试（NTime）。 集成测试 ，持续集成框架的搭建， 接口测试方法，TTCN。 系统测试方法和技术（WEB测试技术、安全性测试技术、 兼容性测试技术、内存泄漏测试与BoundsChecker等） 功能自动化测试及QTP的使用， 自己动手编写一个猴子测试工具。 性能自动化测试及LoadRunner的使用、 自己动手编写性能测试框架。 代码分析和检测技术， 自动化代码分析工具的使用（StyleCop、FxCop等）。 GUI设计与用户体验测试。 数据库测试（SQL注入、数据库兼容性测试、TSQLUnit的使用），自己动手编写一个数据库比较工具、Oracle SQL跟踪工具的制作。 大型CRM项目软件测试实战（综合演练）。

请问尚观培训的怎么样

呵呵~当然不包copy括ocp认证和rhce认证的费用了，14700是长期的培训的课，总共要学习4个月，而且还签署保证5万年薪的就业协议。ocp和rhce认证是原厂授权的培训考试中心，是原厂课程。费用另外收取，但针对长期班学员只收取考试费用，不收培训费。

一、 磁盘方面调优

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_READS>100 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 DEPTWHERE 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

---