oracle优化器怎么用 oracle语句优化工具

如何优化Oracle在where条件中用了自定义函

Oracle的自定义函数，提供了对逻辑的封装能力，便于我们对代码进行管理。然而当这个函数出现在where语句中，它却很可能给我们的SQL语句带来严重的效率问题。因为：

1、Oracle的优化器无法对函数进行优化，只能逐行执行（这就是为什么我们常发现，把函数里面的语句拷出来，就会执行得很快的原因）

2、函数并非标准SQL所包含的东西，因此Oracle在执行函数时，会频繁在SQL上下文和PL/SQL上下文之间切换。当数据量大时，就会增加CPU和内存的消耗，降低语句执行的效率。

因此，自定义函数，就像一把双刃剑一样，摆在了我们面前。对此，我们有以下几种对策：

1、不用函数

2、借用Oracle的缓存机制

3、函数索引

1）不用函数。不用函数确实能解决问题，但如果一段逻辑会被很多个SQL语句用到，这就会给我们的代码管理带来了很大的麻烦。因此这是万不得已的下下策。当然，没必要使用函数的地方，可以尽量避免。

2）借用Oracle的缓存机制。

Oracle对子查询，是会做缓存处理的。因此我们可以把函数写在一个子查询中，如把“my_func(id)”变成“(select my_func(id) from dual)”。这样做可以通过缓存，减少函数被调用的次数，从而提高效率。通常用函数对大数据量进行过滤时，此方法都可大量降低函数被调用的次数（可从Oracle的执行统计看出）

另一种方法，就是对函数强行开启结果缓存。方法是在函数的return类型之后，加上“result_cache”标记。

3）使用函数索引。首先该函数必须被标记为deterministic，即在函数的return类型之后，加上“deterministic”标记。它表示当函数的输入值是确定时，返回结果必定是唯一的。此后，在表的列上新建索引，索引列不是写列名，而是写“my_func(id)”这样即可。

通常地，我推荐先尝试“子查询缓存”这种方法。因为它不仅对函数、表本身没有作任何修改，而且效果也比较明显。我曾经试过对一条很复杂的SQL做了这种处理，结果执行效率提高了60倍之多！如果缓存效果不明显时，可以再尝试下函数索引。不过我发现同样的SQL，我只加函数索引的话，效果并不如“子查询缓存”那样立竿见影。

注意，以上提到的，都只是一些调优的手段而已，并非一定能解决问题。所以可以的话，我们还是要尽量避免把函数放在where语句中。

Oracle查询速度优化问题

1. 选用适合的ORACLE优化器

ORACLE的优化器共有3种:

a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性)

设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在SQL句级或是会话(session)级对其进行覆盖.

为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须经常运行analyze 命令,以增加数据库中的对象统计信息(object statistics)的准确性.

如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关. 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器.

在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器.

2. 访问Table的方式

ORACLE 采用两种访问表中记录的方式:

a. 全表扫描

全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描.

b. 通过ROWID访问表

你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息..ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.

3. 共享SQL语句

为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它

和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的

执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用.

可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询.

数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了.

当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句.

这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格,换行等).

4.选择最有效率的表名顺序(只在基于规则的优化器中有效)

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

5. WHERE子句中的连接顺序．

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

6. SELECT子句中避免使用 ‘ * ‘

当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*' 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.

7. 减少访问数据库的次数

当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量.

注意: 在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200.

8. 使用DECODE函数来减少处理时间

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.

9. 整合简单,无关联的数据库访问

如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)

10. 删除重复记录

最高效的删除重复记录方法 ( 因为使用了ROWID)

DELETE FROM EMP E

WHERE E.ROWID (SELECT MIN(X.ROWID)

FROM EMP X

WHERE X.EMP_NO = E.EMP_NO);

11. 用EXISTS替代IN

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.

12. 用NOT EXISTS替代NOT IN

在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS

如何设置使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有两种优化器，目前多数版本默认是基于成本的优化器，即CBO，这也是大多数查询语句所使用的，通过使用HINT提示（/* +rule */），可使用基于规则的优化器（RBO）。 select /* +rule */ from emp;

如何进行oracle数据库性能优化

你最好买一本专门讲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在修改了上述数据库的初始化参数以后，必须先关闭数据库，在重新启动数据库后才能使新的设置起作用。

oracle优化器怎么用的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于oracle语句优化工具、oracle优化器怎么用的信息别忘了在本站进行查找喔。