ORACLE学习笔记--性能优化(1)

2/9/2008来源:Oracle教程人气:4685

1. 查询正在执行语句的执行计划(也就是实际语句执行计划)

select * from v$sql_plan where hash_value = (select sql_hash_value from v$session where sid = 1111);

其中id和parent_id表示了执行数的结构,数值最大的为最先执行 比如 ID  PARENT_ID   

-------------   

0

1 0

2 1

3 2

4 3

5 4

6 3   则执行计划树为

0

1

2

3

6     4 

5

2.如何设置自动跟踪

用system登录

执行$Oracle_HOME/rdbms/admin/utlXPlan.sql创建计划表

执行$ORACLE_HOME/sqlplus/admin/plustrce.sql创建plustrace角色

假如想计划表让每个用户都能使用,则

SQL>create public synonym plan_table for plan_table;

SQL> grant all on plan_table to public; 假如想让自动跟踪的角色让每个用户都能使用,则

SQL> grant plustrace to public;

通过如下语句开启/停止跟踪

SET AUTOTRACE ON OFF ON EXPLAIN ON STATISTICS TRACEONLY TRACEONLY EXPLAIN 3.如何跟踪自己的会话或者是别人的会话

跟踪自己的会话很简单

Alter session set sql_trace truefalse

Or

Exec dbms_session.set_sql_trace(TRUE); 假如跟踪别人的会话,需要调用一个包

exec dbms_system.set_sql_trace_in_session(sid,serial#,truefalse) 跟踪的信息在user_dump_dest 目录下可以找到或通过如下脚本获得文件名称(适用于Win环境,假如是unix需要做一定修改)

SELECT p1.value'\'p2.value'_ora_'p.spid'.ora' filename

FROM

v$PRocess p,

v$session s,

v$parameter p1,

v$parameter p2

WHERE p1.name = 'user_dump_dest'

AND p2.name = 'db_name'

AND p.addr = s.paddr

AND s.audsid = USERENV ('SESSIONID')

最后,可以通过Tkprof来解析跟踪文件,如

Tkprof 原文件 目标文件 sys=n 4.怎么设置整个数据库系统跟踪

其实文档上的alter system set sql_trace=true是不成功的,但是可以通过设置事件来完成这个工作,作用相等

alter system set events

'10046 trace name context forever,level 1'; 假如关闭跟踪,可以用如下语句

alter system set events

'10046 trace name context off'; 其中的level 1与上面的8都是跟踪级别

level 1:跟踪SQL语句,等于sql_trace=true

level 4:包括变量的具体信息

level 8:包括等待事件

level 12:包括绑定变量与等待事件 5.怎么样根据OS进程快速获得DB进程信息与正在执行的语句

有些时候,我们在OS上操作,象TOP之后我们得到的OS进程,怎么快速根据OS信息获得DB信息呢?

我们可以编写如下脚本:

$more whoit.sh

#!/bin/sh

sqlplus /nolog 100,cascade=> TRUE);

dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);
这是对命令与工具包的一些总结

<1>、对于分区表,建议使用DBMS_STATS,而不是使用Analyze语句。

 a) 可以并行进行,对多个用户,多个Table

 b) 可以得到整个分区表的数据和单个分区的数据。

 c) 可以在不同级别上Compute Statistics:单个分区,子分区,全表,所有分区

 d) 可以倒出统计信息

 e) 可以用户自动收集统计信息

<2>、DBMS_STATS的缺点

 a) 不能Validate StrUCture

 b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息,这两个仍然需要使用Analyze语句。

 c) DBMS_STATS 默认不对索引进行Analyze,因为默认Cascade是False,需要手工指定为True

<3>、对于oracle 9里面的External Table,Analyze不能使用,只能使用DBMS_STATS来收集信息。 6.怎么样快速重整索引

通过rebuild语句,可以快速重整或移动索引到别的表空间

rebuild有重建整个索引数的功能,可以在不删除原始索引的情况下改变索引的存储参数

语法为

alter index index_name rebuild tablespace ts_name

storage(......); 假如要快速重建整个用户下的索引,可以用如下脚本,当然,需要根据你自己的情况做相应修改

SQL> set heading off

SQL> set feedback off

SQL> spool d:\index.sql

SQL> SELECT 'alter index ' index_name ' rebuild '

'tablespace INDEXES storage(initial 256K next 256K pctincrease 0);'

FROM all_indexes

WHERE ( tablespace_name != 'INDEXES'

OR next_extent != ( 256 * 1024 )

)

AND owner = USER

SQL>spool off 另外一个合并索引的语句是

alter index index_name coalesce 这个语句仅仅是合并索引中同一级的leaf block,消耗不大,对于有些索引中存在大量空间浪费的情况下,有一些作用。 7.如何使用Hint提示

在select/delete/update后写/*+ hint */

如 select /*+ index(TABLE_NAME INDEX_NAME) */ col1... 注重/*和+之间不能有空格,如用hint指定使用某个索引

select /*+ index(cbotab) */ col1 from cbotab;

select /*+ index(cbotab cbotab1) */ col1 from cbotab;

select /*+ index(a cbotab1) */ col1 from cbotab a; 其中

TABLE_NAME是必须要写的,且假如在查询中使用了表的别名,在hint也要用表的别名来代替表名;

INDEX_NAME可以不必写,Oracle会根据统计值选一个索引;

假如索引名或表名写错了,那这个hint就会被忽略; 8.怎么样快速复制表或者是插入数据

快速复制表可以指定Nologging选项

如:Create table t1 nologging

as select * from t2; 快速插入数据可以指定append提示,但是需要注重noarchivelog模式下,默认用了append就是nologging模式的。 在archivelog下,需要把表设置程Nologging模式。

如insert /*+ append */ into t1

select * from t2 注重:假如在9i环境中并设置了FORCE LOGGING,则以上操作是无效的,并不会加快,当然,可以通过如下语句设置为NO FORCE LOGGING。

Alter database no force logging;

是否开启了FORCE LOGGING,可以用如下语句查看

SQL> select force_logging from v$database; 9.怎么避免使用特定索引

在很多时候,Oracle会错误的使用索引而导致效率的明显下降,我们可以使用一点点技巧而避免使用不该使用的索引,如:

表test,有字段a,b,c,d,在a,b,c上建立联合索引inx_a(a,b,c),在b上单独建立了一个索引Inx_b(b)。 在正常情况下,where a=? and b=? and c=?会用到索引inx_a,where b=?会用到索引inx_b,但是,where a=? and b=? and c=? group by b会用到哪个索引呢?在分析数据不正确(很长时间没有分析)或根本没有分析数据的情况下,oracle往往会使用索引inx_b。通过执行计划的分析,这个索引的使用,将大大耗费查询时间。
当然,我们可以通过如下的技巧避免使用inx_b,而使用inx_a。 where a=? and b=? and c=? group by b'' --假如b是字符

where a=? and b=? and c=? group by b+0 --假如b是数字 通过这样简单的改变,往往可以是查询时间提交很多倍

当然,我们也可以使用no_index提示,相信很多人没有用过,也是一个不错的方法:

select /*+ no_index(t,inx_b) */ * from test t

where a=? and b=? and c=? group by b 举例:

本来在CM_USER上有索引IDX_CM_USER4(ACC_ID)和IDX_CM_USER8(BILL_ID),可是执行如下语句的时候很慢。

select * from CM_USER where  acc_id =1200007175

and user_status>0 and bill_id like '13%' order by acc_id,bill_id 用explain分析,发现执行计划是用IDX_CM_USER8.如下查询

select * from user_indexes where table_name ='CM_USER' 发现IDX_CM_USER8没有分析过。 用下面语句执行计划改变

select /*+INDEX(CM_USER IDX_CM_USER4)*/* from CM_USER where  acc_id =1200007175 and user_status>0 and bill_id like '13%' order by acc_id,bill_id 或者分析索引

exec dbms_stats.gather_index_stats(ownname => 'QACS1',indname => 'IDX_CM_USER8',estimate_percent => 5 );

可以发现执行计划恢复正常。


10.Oracle什么时候会使用跳跃式索引扫描

这是9i的一个新特性跳跃式索引扫描(Index Skip Scan).

例如表有索引index(a,b,c),当查询条件为where b=?的时候,可能会使用到索引index(a,b,c),如,执行计划中出现如下计划:

INDEX (SKIP SCAN) OF 'TEST_IDX' (NON-UNIQUE) Oracle的优化器(这里指的是CBO)能对查询应用Index Skip Scans至少要有几个条件:

<1> 优化器认为是合适的。

<2> 索引中的前导列的唯一值的数量能满足一定的条件(如重复值很多