ORACLE性能优化笔记

ID  PARENT_ID   
9q Yo"V6X4T0-------------   
{}*p$@4{ R.~00 ITPUB个人空间2QN/I$YbKv6zS
1 0
Q2Lb)Q^(k}t3r4qI02 1
'D"j9j"_;ZF\03 2ITPUB个人空间/qx/Z4k@q
U#fN_#E
4 3

c7E8s!z%@#VSA4BF05 4
bb3g7C5c;r.b/M06 3   

则执行计划树为ITPUB个人空间 AC,n0RZ
              0ITPUB个人空间)ah1J?)_S9?'l
              1
/Qd#M1z)Ro|0              2
V6U!d6H9c&P;[aB&i0              3
A/Zm+Q8W;JRr0           6     4 
7X-{hkQ0                  5

2.如何设置自动跟踪
!Yf}6b7z/K)K&Vvx0　　用system登录ITPUB个人空间9O#f dR]$j2\
　　执行$ORACLE_HOME/rdbms/admin/utlxplan.sql创建计划表
0vb'H/^ `*P0　　执行$ORACLE_HOME/sqlplus/admin/plustrce.sql创建plustrace角色
,x1l!R n@)~0　　如果想计划表让每个用户都能使用，则
`c6k1GR+F;H{0　　SQL>create public synonym plan_table for plan_table;ITPUB个人空间+gE5~E)z z(a-bgav
　　SQL> grant all on plan_table to public;

　　如果想让自动跟踪的角色让每个用户都能使用，则ITPUB个人空间D)gi!B
Nj
　　SQL> grant plustrace to public;ITPUB个人空间 g.BOWK?7E-~
　　通过如下语句开启/停止跟踪
%x7N8?[/kJ%| R0　　SET AUTOTRACE ON |OFF | ON EXPLAIN | ON STATISTICS | TRACEONLY | TRACEONLY EXPLAIN

3.如何跟踪自己的会话或者是别人的会话ITPUB个人空间V7KSE k+F
　　跟踪自己的会话很简单
j{\1~1a/g-}%z;CkB0　　Alter session set sql_trace true|false
Av:DQ#`u E#n%Pl0　　OrITPUB个人空间5{X5f%BJ8n,N;~
　　Exec dbms_session.set_sql_trace(TRUE);

　　如果跟踪别人的会话，需要调用一个包ITPUB个人空间)]Z%wE K5n
　　exec dbms_system.set_sql_trace_in_session(sid,serial#,true|false)

　　跟踪的信息在user_dump_dest 目录下可以找到或通过如下脚本获得文件名称（适用于Win环境，如果是unix需要做一定修改）ITPUB个人空间i4oZ h]$`ZEC#?!}
　　SELECT p1.value||'\'||p2.value||'_ora_'||p.spid||'.ora' filename
Lpw7W$u0　　FROM

kP#C}P`0　　v$process p,
+\r-WC:{(pu&Cm/X0　　v$session s,ITPUB个人空间)P,\v{-K,q+W8u
　　v$parameter p1,ITPUB个人空间-p,fqL~+r8v
　　v$parameter p2ITPUB个人空间8EXAp0{au/B5k
　　WHERE p1.name = 'user_dump_dest'ITPUB个人空间KL*qi7g$\l
C
　　AND p2.name = 'db_name'ITPUB个人空间:|#zLf Xm#U A(I
　　AND p.addr = s.paddr
_c0AJ,t-Q0　　AND s.audsid = USERENV ('SESSIONID')ITPUB个人空间~E8x p Lnm
　　最后，可以通过Tkprof来解析跟踪文件，如
]P1G7lj@j @ [(q3Zs+@0　　Tkprof 原文件 目标文件 sys=n

4.怎么设置整个数据库系统跟踪
gbL.TO-X0　　其实文档上的alter system set sql_trace=true是不成功的，但是可以通过设置事件来完成这个工作，作用相等ITPUB个人空间!BN/IN
r O"W
　　alter system set events
uo|a;eM6{y0　　'10046 trace name context forever,level 1';

　　如果关闭跟踪，可以用如下语句
0M%O-Rc dlZ0　　alter system set eventsITPUB个人空间/KN*?aC|NEbDPz*k_
　　'10046 trace name context off';

其中的level 1与上面的8都是跟踪级别ITPUB个人空间
kQBs+IY"W"j@r
　　level 1：跟踪SQL语句，等于sql_trace=true
P2y~ B$k0　　level 4：包括变量的详细信息ITPUB个人空间t9@2X@Vv/N4uC L!zs
　　level 8：包括等待事件
z2g2lda/o0　　level 12：包括绑定变量与等待事件

5.怎么样根据OS进程快速获得DB进程信息与正在执行的语句ITPUB个人空间|x9A j]Fts_Q
　　有些时候，我们在OS上操作，象TOP之后我们得到的OS进程，怎么快速根据OS信息获得DB信息呢？
v/y!u]t0　　我们可以编写如下脚本：ITPUB个人空间R"X7K1Rr"Y
　　$more whoit.shITPUB个人空间)l%S~)mu)Q1t
　　#!/bin/sh
[ v#G(U8E\ jU0　　sqlplus /nolog 100,cascade=> TRUE);
'yu.l-S\Wf0　　dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);

这是对命令与工具包的一些总结ITPUB个人空间"Y$k%WT.ZY?+K
　　<1>、对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句。ITPUB个人空间/IWH| ]uP
　　　a) 可以并行进行，对多个用户，多个Table
I-z9C$JG`6E0　　　b) 可以得到整个分区表的数据和单个分区的数据。
K2P6ru:s;a.q ie0　　　c) 可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区
&{7yASo/U)pH7]$p0　　　d) 可以倒出统计信息ITPUB个人空间BS&k{x(vC%~b
　　　e) 可以用户自动收集统计信息
.t/S)i@;ksWP0　　<2>、DBMS_STATS的缺点
6z"G8pW-h\WtK)d0　　　a) 不能Validate Structure
3l8[ C i0oy0　　　b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，这两个仍旧需要使用Analyze语句。ITPUB个人空间5}dB RZ5N?&C4{
　　　c) DBMS_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True
9}#B]~!h4g1O#j\0　　<3>、对于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息。

6.怎么样快速重整索引
7ztA)xgV0　　通过rebuild语句，可以快速重整或移动索引到别的表空间ITPUB个人空间-Oeh%O&A
　　rebuild有重建整个索引数的功能，可以在不删除原始索引的情况下改变索引的存储参数
:Jlbe gg0　　语法为
M4\j1Y@QM0　　alter index index_name rebuild tablespace ts_nameITPUB个人空间(d/lZ%lN}#SY
　　storage(......);

　　如果要快速重建整个用户下的索引，可以用如下脚本，当然，需要根据你自己的情况做相应修改
uQ @z%G3|X0　　SQL> set heading off
9Cz}Y,qb%\0　　SQL> set feedback offITPUB个人空间mE x m @!Z
　　SQL> spool d:\index.sql
0]a i^
_+u;~l-R0　　SQL> SELECT 'alter index ' || index_name || ' rebuild '
!n&uB`7f0　　||'tablespace INDEXES storage(initial 256K next 256K pctincrease 0);'ITPUB个人空间Zn)a5wXen:W%o4`
　　FROM all_indexes
YTv%n(vC0　　WHERE ( tablespace_name != 'INDEXES'
d%Lfnv7E s0　　OR next_extent != ( 256 * 1024 )
\Gt3^G!M@0　　)
7VB[9Z-S0　　AND wner = USER
6~fJ!Gw+IYa8{0　　SQL>spool off

　　另外一个合并索引的语句是
Niy^]He ?,Nk0　　alter index index_name coalesce

　　这个语句仅仅是合并索引中同一级的leaf block，消耗不大，对于有些索引中存在大量空间浪费的情况下，有一些作用。

7.如何使用Hint提示
;[Rc}|'b)g0　　在select/delete/update后写/*+ hint */ITPUB个人空间i$^fi+tS
q*Wh
　　如select /*+ index(TABLE_NAME INDEX_NAME) */ col1...

　　注意/*和+之间不能有空格，如用hint指定使用某个索引ITPUB个人空间2UCr!{LM%uzr
　　select /*+ index(cbotab) */ col1 from cbotab;ITPUB个人空间cS a|/b5G
　　select /*+ index(cbotab cbotab1) */ col1 from cbotab;ITPUB个人空间dwsMQ2Ts
　　select /*+ index(a cbotab1) */ col1 from cbotab a;

　　其中ITPUB个人空间]'l+UG _Tx
　　TABLE_NAME是必须要写的，且如果在查询中使用了表的别名，在hint也要用表的别名来代替表名;
6S:`Vab4L0　　INDEX_NAME可以不必写，Oracle会根据统计值选一个索引;ITPUB个人空间HnEC9u
　　如果索引名或表名写错了，那这个hint就会被忽略;

8.怎么样快速复制表或者是插入数据
6DKz7c gl&G:c0　　快速复制表可以指定Nologging选项ITPUB个人空间6h3v$OT-~6JK%q
　　如：Create table t1 nologgingITPUB个人空间J:Q;W8^3@,`
\W
　　as select * from t2;

　　快速插入数据可以指定append提示，但是需要注意noarchivelog模式下，默认用了append就是nologging模式的。 在archivelog下，需要把表设置程Nologging模式。
f.}AX*u3~i}{-|0　　如insert /*+ append */ into t1ITPUB个人空间J9tq"amV ]
　　select * from t2

　　注意：如果在9i环境中并设置了FORCE LOGGING，则以上操作是无效的，并不会加快，当然，可以通过如下语句设置为NO FORCE LOGGING。
|'I$HSr h0^._%fp0　　Alter database no force logging;ITPUB个人空间LN4G(d.y:aGXRxJ"A
　　是否开启了FORCE LOGGING，可以用如下语句查看
Ji7}1f.sH6D9C V0　　SQL> select force_logging from v$database;

9.怎么避免使用特定索引ITPUB个人空间@2m?V}
　　在很多时候，Oracle会错误的使用索引而导致效率的明显下降，我们可以使用一点点技巧而避免使用不该使用的索引，如：ITPUB个人空间1l#Rih;i"fR
　　表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是字符ITPUB个人空间Qvuq{8R6eFv
　　where a=? and b=? and c=? group by b+0 --如果b是数字

　　通过这样简单的改变，往往可以是查询时间提交很多倍ITPUB个人空间#Qb]#KCa5@P
　　当然，我们也可以使用no_index提示，相信很多人没有用过，也是一个不错的方法：
K `0U*gpU.yc0　　select /*+ no_index(t,inx_b) */ * from test tITPUB个人空间2O } o"t&Z*R
　　where a=? and b=? and c=? group by b

　　举例：
B&|_8fBcO0　　本来在CM_USER上有索引IDX_CM_USER4(ACC_ID)和IDX_CM_USER8(BILL_ID),可是执行如下语句的时候很慢。ITPUB个人空间k|j[ @;f?8^
　　select * from CM_USER where  acc_id =1200007175
"Zq}7ow,?0　　and user_status>0 and bill_id like '13%' order by acc_id,bill_id

　　用explain分析，发现执行计划是用IDX_CM_USER8.如下查询
peo6N2`&uY'q0　　select * from user_indexes where table_name ='CM_USER'发现IDX_CM_USER8没有分析过。

　　用下面语句执行计划改变ITPUB个人空间a+_;[)M'zBF
　　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

　　或者分析索引
(Q-C0Wrg0　　exec dbms_stats.gather_index_stats(ownname => 'QACS1',indname => 'IDX_CM_USER8',estimate_percent => 5 );ITPUB个人空间/Y$W:oz|
　　可以发现执行计划恢复正常。

ITPUB个人空间QX.Q0G-hdnP6at+G
10.Oracle什么时候会使用跳跃式索引扫描
:UX|$s
y)H[ D5P4Q0　　这是9i的一个新特性跳跃式索引扫描(Index Skip Scan).
l y'X8EQ M{:i0P0　　例如表有索引index(a,b,c)，当查询条件为where b=?的时候，可能会使用到索引index(a,b,c)，如，执行计划中出现如下计划：
$ud"\J/C|#a3h*\|
]8h0　　INDEX (SKIP SCAN) OF 'TEST_IDX' (NON-UNIQUE)

　　Oracle的优化器(这里指的是CBO)能对查询应用Index Skip Scans至少要有几个条件:ITPUB个人空间y}U7l8gM
　　<1>优化器认为是合适的。ITPUB个人空间/O#q2~s0k:k
　　<2>索引中的前导列的唯一值的数量能满足一定的条件（如重复值很多）。ITPUB个人空间1TRYt0dB
　　<3>优化器要知道前导列的值分布(通过分析/统计表得到)。
J8vN(v"\'Sn]0　　<4>合适的SQL语句
A~Vk"PQ3G0　　等。

*I5]uYm%|011.怎么样创建使用虚拟索引ITPUB个人空间6X,[
a#W Ee8Bt
　　可以使用nosegment选项，如
[b:AL+A%]ag0　　create index virtual_index_name on table_name(col_name) nosegment;

　　如果在哪个session需要测试虚拟索引，可以利用隐含参数来处理ITPUB个人空间lYU/\%y#oA
　　alter session set "_use_nosegment_indexes" = true;

　　就可以利用explain plan for select ……来看虚拟索引的效果，利用@$ORACLE_HOME/rdbms/admin/utlxpls查看执行计划，最后，根据需要，我们可以删除虚拟索引，如普通索引一样ITPUB个人空间7x^v&L8g
　　drop index virtual_index_name;

　　注意：虚拟索引并不是物理存在的，所以虚拟索引并不等同于物理索引，不要用自动跟踪去测试虚拟索引，因为那是实际执行的效果，是用不到虚拟索引的。

12.怎样监控无用的索引
LM4TV;aG6P4zbd%@u0　　Oracle 9i以上，可以监控索引的使用情况，如果一段时间内没有使用的索引，一般就是无用的索引ITPUB个人空间3S8@w.]?V@6apQ
　　语法为：ITPUB个人空间%w?3f Ex
　　开始监控：alter index index_name monitoring usage;
&P*j iL2i@X0　　检查使用状态：select * from v$object_usage;
1n+b*|6q&m.\0　　停止监控：alter index index_name nomonitoring usage;

　　当然，如果想监控整个用户下的索引，可以采用如下的脚本：
KA$O@^Qh0　　set heading off
a'h,P?*T'g7gN _z*e0　　set echo offITPUB个人空间+En6TH8d6O-{S^;l
　　set feedback off
2c!x.aOR8_y0　　set pages 10000
9XMx0?%}hk|B0　　spool start_index_monitor.sqlITPUB个人空间5\%a%a.`3^/_1t
　　SELECT 'alter index '||owner||'.'||index_name||' monitoring usage;'ITPUB个人空间9Fh%?!c
Yb/bf
　　FROM dba_indexes
_ G k;C]U0　　WHERE wner = USER;ITPUB个人空间1xY,pP0GE9H
　　spool offITPUB个人空间
r g,_6z,BO+o
　　set heading on
;[/GC-A:Q`3H3x!B^0　　set echo onITPUB个人空间| C d;y.X
　　set feedback onITPUB个人空间 xWs w4HY
　　------------------------------------------------ITPUB个人空间JF_IK
　　set heading off
;cr]/Xi ohD+V8]m0　　set echo offITPUB个人空间%m3ryV,bR7]
　　set feedback offITPUB个人空间.W4P/BW&g |(y |y1f
　　set pages 10000ITPUB个人空间p#R.s!z&pzYb Kv7@
　　spool stop_index_monitor.sqlITPUB个人空间
Y\'w%OWhE0V
　　SELECT 'alter index '||owner||'.'||index_name||' nomonitoring usage;'ITPUB个人空间 q"M%s;m)]+g_h'B
　　FROM dba_indexes
l(A,K!P `+D*S
t0　　WHERE wner = USER;ITPUB个人空间GV/p4~C.W3hG#N
　　spool off
1HE\P3J3E$z0　　set heading onITPUB个人空间I#@+U2A7ej
　　set echo onITPUB个人空间Y*`"c["m@)?w
　　set feedback on

13.怎么样能固定我的执行计划
a&T(R-q/~0　　可以使用OUTLINE来固定SQL语句的执行计划，用如下语句可以创建一个OUTLINE
S4ry V[[5WN+~0　　Create oe replace outline OutLn_Name onITPUB个人空间&Kr?5O*}N-Lau(k
　　Select Col1,Col2 from Table
)h\ RQ4NFV7]'q0　　where .......

　　如果要删除Outline，可以采用ITPUB个人空间'V,A]+g[x!A*XN
　　Drop Outline OutLn_Name;

　　对于已经创建了的OutLine，存放在OUTLN用户的OL$HINTS表下面，对于有些语句，你可以使用update outln.ol$hints来更新outline，如

　　update outln.ol$hints(ol_name,'TEST1','TEST2','TEST2','TEST1)
6j*ry lF?7W0　　where ol_name in ('TEST1','TEST2');

　　这样，你就把Test1 OUTLINE与Test2 OUTLINE互换了，如果想利用已经存在的OUTLINE，需要设置以下参数ITPUB个人空间)dU8T|CL!}
　　Alter system/session set Query_rewrite_enabled = true
\/lr@4X
]0　　Alter system/session set use_stored_outlines = true

14.v$sysstat中的class分别代表什么ITPUB个人空间 uB+?^`!|p-SR
　　统计类别ITPUB个人空间'[*bV&eX3~+IW
　　1 代表事例活动
6z.Yy&h"n/W1^}0　　2 代表Redo buffer活动ITPUB个人空间2guZ,]!JfAfC
　　4 代表锁ITPUB个人空间.@/D,C;U*}6}
　　8 代表数据缓冲活动ITPUB个人空间n4u7W^ R
　　16 代表OS活动ITPUB个人空间w*F$m5~Iu0o.G+\Qr3}#q
　　32 代表并行活动
X,g/vv+t)LDG3IR"?*|0　　64 代表表访问
$LSc6YT*j0　　128 代表调试信息

15.怎么杀掉特定的数据库会话ITPUB个人空间7yw1r3Q!?$k
　　Alter system kill session 'sid,serial#';ITPUB个人空间 mi OwHZT:C8d_
　　或者
)n$n M6e\*Gc
WkK0　　alter system disconnect session 'sid,serial#' immediate;

　　在win上，还可以采用oracle提供的orakill杀掉一个线程（其实就是一个Oracle进程）ITPUB个人空间~_4K5vBV*f"g!}:y `
　　在Linux/Unix上，可以直接利用kill杀掉数据库进程对应的OS进程

16.怎么快速查找锁与锁等待ITPUB个人空间
]!rE w&b!@D
　　数据库的锁是比较耗费资源的，特别是发生锁等待的时候，我们必须找到发生等待的锁，有可能的话，杀掉该进程。ITPUB个人空间(C*z(kAj1g&e%n
　　这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。ITPUB个人空间S'_@+V4@ aF r
　　可以通过alter system kill session ‘sid,serial#’来杀掉会话

　　SELECT /*+ rule */ s.username,
d0}kZ0Pz0　　decode(l.type,'TM','TABLE LOCK','TX','ROW LOCK',NULL) LOCK_LEVEL,ITPUB个人空间 jMQ5VM
JYp
　　o.owner,o.object_name,o.object_type,ITPUB个人空间1GMV7@Yb){
　　s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuserITPUB个人空间)|k#b2`u4`
　　FROM v$session s,v$lock l,dba_objects o
Q8{
b,P} N0X/QP0　　WHERE l.sid = s.sid
$x\p4dV9P0Y`1a%f0　　AND l.id1 = o.object_id(+)
&r4K3o \D;S
cm0　　AND s.username is NOT NULL

　　如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待，以下的语句可以查询到谁锁了表，而谁在等待。ITPUB个人空间:F"v]"X$T2qwc$j:I,D
　　SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
%s rA2xJ1H0　　o.owner,o.object_name,o.object_type,s.sid,s.serial#ITPUB个人空间~9Jn:M9M G2gMx}4E+{
　　FROM v$locked_object l,dba_objects o,v$session sITPUB个人空间xNt9yj
　　WHERE l.object_id=o.object_idITPUB个人空间R1o2M/guSMr0f
　　AND l.session_id=s.sidITPUB个人空间$o~:e lK#u4G@h,S
　　ORDER BY o.object_id,xidusn DESC

　　以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN

　　[Q] 如何有效的删除一个大表(extent数很多的表)ITPUB个人空间*N%Lb%Ys d6vN,Pj
　　[A] 一个有很多(100k)extent的表，如果只是简单地用drop table的话，会很大量消耗CPU（Oracle要对fet$、uet$数据字典进行操作），可能会用上几天的时间，较好的方法是分多次删除extent，以减轻这种消耗：
$P1VQ,H9[x;T2h0　　1. truncate table big-table reuse storage;
IHgN7M0　　2. alter table big-table deallocate unused keep 2000m ( 原来大小的n-1/n);
4{4s6m#R B7{x0　　3. alter table big-table deallocate unused keep 1500m ;ITPUB个人空间6X6czk)Ya
　　....
:`U;^5k}u0　　4. drop table big-table;

17.如何收缩临时数据文件的大小
8V-A
j[A3eW)~0　　9i以下版本采用ITPUB个人空间q,oI2f8ZP4V
　　ALTER DATABASE DATAFILE 'file name' RESIZE 100M类似的语句ITPUB个人空间G+t5[f$J7i3[)ET%c
　　9i以上版本采用ITPUB个人空间4n{$s+b+p4UvR
　　ALTER DATABASE TEMPFILE 'file name' RESIZE 100M
3b7pY{A N1k1m0　　注意，临时数据文件在使用时，一般不能收缩，除非关闭数据库或断开所有会话，停止对临时数据文件的使用。

18.怎么清理临时段ITPUB个人空间 m&B7A D$}A,o`
　　可以使用如下办法
tHccdjK;o{0　　<1>、 使用如下语句查看一下认谁在用临时段
M PZ-KfA3?0　　SELECT username,sid,serial#,sql_address,machine,program,ITPUB个人空间
`9af M||-n_-F
　　tablespace,segtype, contents
G8v0DG YG&g-x0　　FROM v$session se,v$sort_usage suITPUB个人空间.v#f#le0HG7]Xn
　　WHERE se.saddr=su.session_addr

 　　<2>、 那些正在使用临时段的进程ITPUB个人空间"|)N0z5O,@/L8Z7IP
　　SQL>Alter system kill session 'sid,serial#';

　　<3>、把TEMP表空间回缩一下
p7g;g.J$Y!L?8Q0　　SQL>Alter tablespace TEMP coalesce;

ITPUB个人空间2cl r7bspHA@8I
　　还可以使用诊断事件ITPUB个人空间A+Gf!o/qc*_EGm
　　<1>、 确定TEMP表空间的ts#ITPUB个人空间+er0Z%`
@0X/W'PI!T
　　SQL> select ts#, name FROM v$tablespace;
6?*dD.L v-d_'GP0　　TS# NAMEITPUB个人空间[1Xq5zX
　　-----------------------ITPUB个人空间&b$n9B;s
LyN
　　0 SYSYEM
9ZZ/M[Ny{d0　　1 RBSITPUB个人空间|i dZ;c
　　2 USERS
GwE0Z Q)eK ]0　　3* TEMP
!KVGnR@0　　...

　　<2>、 执行清理操作
U@c/m;}e0　　alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1'ITPUB个人空间 f8jpVD._mJ.U1IF
　　说明：ITPUB个人空间a4Z^Pg!Lh1bN`n
　　temp表空间的TS# 为 3*, So TS#+ 1= 4，如果想清除所有表空间的临时段，则，TS# = 2147483647

19.怎么样dump数据库内部结构，如上面显示的控制文件的结构
$m^L,n3B0　　常见的有ITPUB个人空间Z A6mQ-s/K
　　1、分析数据文件块，转储数据文件n的块m
].WS o N;^,vD
M0　　alter system dump datafile n block m

　　2、分析日志文件ITPUB个人空间(N7?+WO?.pb
　　alter system dump logfile logfilename;

　　3、分析控制文件的内容ITPUB个人空间Cp!M*L3Nq[lS
　　alter session set events 'immediate trace name CONTROLF level 10'

　　4、分析所有数据文件头ITPUB个人空间:GJR#M4p
　　alter session set events 'immediate trace name FILE_HDRS level 10'

　　5、分析日志文件头ITPUB个人空间h2k wEwOt;Q}
　　alter session set events 'immediate trace name REDOHDR level 10'

　　6、分析系统状态，最好每10分钟一次，做三次对比
C1d8FEh$q1nY0　　alter session set events 'immediate trace name SYSTEMSTATE level 10'

　　7、分析进程状态
^!J W,F5w OT0　　alter session set events 'immediate trace name PROCESSSTATE level 10'

　　8、分析Library Cache的详细情况
EM7z^
f0　　alter session set events 'immediate trace name library_cache level 10'

} X.^v5EMu~/J020.如何获得所有的事件代码ITPUB个人空间Yn_t"Q
Wa
　　事件代码范围一般从10000 to 10999，以下列出了这个范围的事件代码与信息ITPUB个人空间!X*z;jM$Kl(? T.E5n2U
　　SET SERVEROUTPUT ONITPUB个人空间'LC'BmwK
　　DECLARE
9I1^#w2x/Oaze0　　err_msg VARCHAR2(120);
b_*P!n5I-~7Lep0　　BEGIN
Wo {AeX0　　dbms_output.enable (1000000);ITPUB个人空间A&K(ZV@1?
　　FOR err_num IN 10000..10999ITPUB个人空间;^%~1x8~\B
　　LOOPITPUB个人空间%eQ&M\(@
　　err_msg := SQLERRM (-err_num);ITPUB个人空间"rVkY*aGc.sOo
　　IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN
"kV$fq}!VtTrSa0　　dbms_output.put_line (err_msg);
:kC"K"l+|xM{ptJ0　　END IF;
([tD;Cjj0　　END LOOP;ITPUB个人空间S0V!_7A,\(wN9r
　　END;ITPUB个人空间;Wv9x??*PK*r"o
　　/

　　在Unix系统上，事件信息放在一个文本文件里
*STVpo6t#cu H0　　$ORACLE_HOME/rdbms/mesg/oraus.msgITPUB个人空间rD%q"T%oM
　　可以用如下脚本查看事件信息
)N E/W$C4K/Jzo$_0　　event=10000ITPUB个人空间b6{b?-HGR
　　while [ $event -ne 10999 ]
#E9D9J,OP0　　do
L?$y)L
mC/Kr0　　event=`expr $event + 1`
&Q7Jf'z+Vn0　　oerr ora $eventITPUB个人空间
A [%}%{NH'|'a
　　done

　　对于已经确保的/正在跟踪的事件，可以用如下脚本获得
dW@~.f
~c5^1gu0　　SET SERVEROUTPUT ON
%X{"m@#Y"T0　　DECLAREITPUB个人空间Zr"@
\#K@*S
}I
　　l_level NUMBER;ITPUB个人空间S:YJf AL LA
n
　　BEGINITPUB个人空间cy,H6^d[8TO2l(I3I/f
　　FOR l_event IN 10000..10999
7t
F|:{k0　　LOOPITPUB个人空间)DJH,i$m3{.jA#kP
　　dbms_system.read_ev (l_event,l_level);
2u0I?/f&TW
?0　　IF l_level > 0 THEN
nL"[#ZA/C,M0　　dbms_output.put_line ('Event '||TO_CHAR (l_event)||ITPUB个人空间d*Hr ^(Bvi*?`
　　' is set at level '||TO_CHAR (l_level));
t?0zrM0　　END IF;ITPUB个人空间gMCBt0f5G3Iw
　　END LOOP;
t] @0I.dP0　　END;ITPUB个人空间(U1~eC`%O9Zz
　　/

21.什么是STATSPACK，我怎么使用它？ITPUB个人空间(`-]m1^Q6ZQ
　　Statspack是Oracle 8i以上提供的一个非常好的性能监控与诊断工具，基本上全部包含了BSTAT/ESTAT的功能，更多的信息，可以参考附带文档$ORACLE_HOME/rdbms/admin/spdoc.txt。

　　安装Statspack:ITPUB个人空间$co&tB}l},@iF k
　　cd $ORACLE_HOME/rdbms/admin
p `/xt,]O3oV0　　sqlplus "/ as sysdba" @spdrop.sql -- 卸载，第一次可以不需要ITPUB个人空间3k;W8]5iIPx
　　sqlplus "/ as sysdba" @spcreate.sql -- 需要根据提示输入表空间名

　　使用Statspack:
"l
i4tSf$Dd Z3E7T[0　　sqlplus perfstat/perfstatITPUB个人空间7i
n.{!yt;\
　　exec statspack.snap; -- 进行信息收集统计，每次运行都将产生一个快照号ITPUB个人空间#x Qr+Wm7qJ
　　-- 获得快照号，必须要有两个以上的快照，才能生成报表ITPUB个人空间K]_&kcb0h{
　　select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;ITPUB个人空间}(J7w7N1Jh[V
　　@spreport.sql -- 输入需要查看的开始快照号与结束快照号

　　其他相关脚本s:
DiC%tN ^7e\:rY0　　spauto.sql - 利用dbms_job提交一个作业，自动的进行STATPACK的信息收集统计
(UZ jc{(^#R0　　sppurge.sql - 清除一段范围内的统计信息，需要提供开始快照与结束快照号
5yp3n S8BD$qQ0　　sptrunc.sql - 清除(truncate)所有统计信息

v u Xj0Uu'nI022. SQL语句的优化方法
az%p"h.Hq;p_0　　<1> /*+ALL_ROWS*/
0V?/dnd7Uk0　　表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.ITPUB个人空间,~n dt;lo
　　例如:
D].v0?,es{hh8z CQ#^0　　SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';

　　<2>. /*+FIRST_ROWS*/ITPUB个人空间#m't K U
q6P
　　表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.ITPUB个人空间L6Aoi7H
　　例如:
6T.Bw*t P0　　SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE    EMP_NO='CCBZZP';

　　<3>. /*+CHOOSE*/ITPUB个人空间 A'T U @AB'd+IjMR;u
　　表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;ITPUB个人空间w&rb8PaW2`Wp
　　表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;ITPUB个人空间-C1BO},T0x
　　例如:
b
S?8UcO0{-N;SZ0　　SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';

　　<4>. /*+RULE*/ITPUB个人空间 w B*ED*N5H0ap
　　表明对语句块选择基于规则的优化方法.
0\'K]Vc
S:T0　　例如:ITPUB个人空间(y$[&@9Z-e
　　SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';  

　　<5>. /*+FULL(TABLE)*/ITPUB个人空间5x!QK~V4u,x|3J
　　表明对表选择全局扫描的方法.ITPUB个人空间|3{5Q,~7vn@y2Z}(mv
　　例如:ITPUB个人空间NPor JD
　　SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='CCBZZP';

　　<6>. /*+ROWID(TABLE)*/
w_m2T']UY}`!J0　　提示明确表明对指定表根据ROWID进行访问.
E#u*I+T(gy,s:{({0　　例如:
t,ww7ccG)GkZ0　　SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'
y_xbn#WBsF0   AND EMP_NO='CCBZZP';

　　<7>. /*+CLUSTER(TABLE)*/
i` Gs|0^~6}KDQ!t0　　提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.
thi| ZvK @.m0　　例如:ITPUB个人空间g;}#F2} Z[9GI j
　　SELECT  /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
A p$f)mL7W,o+jhaZQ5b0　　WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

　　<8>. /*+INDEX(TABLE INDEX_NAME)*/
%X(i
kuoY0　　表明对表选择索引的扫描方法.
+DI h%s P,B+@n1r?rT0　　例如:
tL8Xp4F;k&vW0　　SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE    BSEMPMS */  FROM BSEMPMS WHERE SEX='M';

　　<9>. /*+INDEX_ASC(TABLE INDEX_NAME)*/ITPUB个人空间!H4gg*od2K!QL5i+c
　　表明对表选择索引升序的扫描方法.ITPUB个人空间mE-Fg
L:y%e_b)k H9v9x
　　例如:ITPUB个人空间z Pyio5jlA!|d
　　SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */  FROM BSEMPMS WHERE DPT_NO='CCBZZP';

　　<10>. /*+INDEX_COMBINE*/
rxh,fz5E*O"j~0　　为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的
?:hD:N0g ]a{&y2a8N*LQ7`0　　布尔组合方式.ITPUB个人空间I)c(QU5q%~.N
　　例如:
3Z0r:r-_}0　　SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
E?pi'\sq0　　WHERE SAL<5000000 AND HIREDATE<SYSDATE;

　　<11>. /*+INDEX_JOIN(TABLE INDEX_NAME)*/ITPUB个人空间b#N;|#f3~9\C~k/s5t)e)l
　　提示明确命令优化器使用索引作为访问路径.
0OjEsD'uxM0　　例如:
o2K"sM![9__Fi-U0　　SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE
@2]4g CQ3g)J'zr0　　FROM BSEMPMS WHERE SAL<60000;

　　<12>. /*+INDEX_DESC(TABLE INDEX_NAME)*/
vF]$XR}/y2{[0　　表明对表选择索引降序的扫描方法.
uM M.p;vu6U)y0　　例如:
~ e2g/n(F4D9E3\0　　SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */  FROM BSEMPMS WHERE    DPT_NO='CCBZZP';

　　<13>. /*+INDEX_FFS(TABLE INDEX_NAME)*/
Pe N3X~JF{E3cv0　　对指定的表执行快速全索引扫描,而不是全表扫描的办法.ITPUB个人空间#e9?7?:o|D1m/u&d#d
　　例如:
e8g?5ga j-K0　　SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';

<14>. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/ITPUB个人空间}.]UfD8P!Zs
　　提示明确进行执行规划的选择,将几个单列索引的扫描合起来.
F!Fhn/w#_3|7o#Z;n0　　例如:ITPUB个人空间`
tt8fXx8G
　　SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='CCBZZP' AND DPT_NO='TDC306';

　　<15>. /*+USE_CONCAT*/
l'l3_9CDrwN bC0　　对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询.ITPUB个人空间0U"O8K0ru^C0\7z0R
　　例如:
@Z.Fa N-G5pe0　　SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

　　<16>. /*+NO_EXPAND*/ITPUB个人空间'SD{jIM
　　对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展.ITPUB个人空间+x
^ l.~+W4N
　　例如:
5M w-j#^*m$x7p!\mK0　　SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE  DPT_NO='TDC506' AND SEX='M';

　　<17>. /*+NOWRITE*/
9j+}a#C A1d'L0l0　　禁止对查询块的查询重写操作.

　　<18>. /*+REWRITE*/
o+A5~;s F0　　可以将视图作为参数.

　　<19>. /*+MERGE(TABLE)*/
K6L5_5xb0　　能够对视图的各个查询进行相应的合并.ITPUB个人空间m"f/v E-W\7X,j
　　例如:
"A&P5K^ _{kCi0z0　　SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO　AND A.SAL>V.AVG_SAL;

　　<20>. /*+NO_MERGE(TABLE)*/ITPUB个人空间XX%` _4n+?}
　　对于有可合并的视图不再合并.ITPUB个人空间%Rf6Z2R$~`6tn
　　例如:ITPUB个人空间L
{)LYx`$U
　　SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO　AND A.SAL>V.AVG_SAL;
XE#kh#}0  
;AaL+M sn0　　<21>. /*+ORDERED*/ITPUB个人空间6FA^4TZU Xw C
　　根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接.ITPUB个人空间l%y#?#N8e\
　　例如:
N+LdJc6t0　　SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C
6DSLU_.~.Lb"K}0　　WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

　　<22>. /*+USE_NL(TABLE)*/ITPUB个人空间7O9ZG8X}F ]W
　　将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.
+Sg,jh)jY0　　例如:
1CN+VEN]B?{$I0　　SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

　　<23>. /*+USE_MERGE(TABLE)*/
YDd-Xbp.{t0　　将指定的表与其他行源通过合并排序连接方式连接起来.ITPUB个人空间S4{t"gd
y g%E
　　例如:ITPUB个人空间}!SdH0EJ
　　SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE
MR5w-w#V/pB0BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

　　<24>. /*+USE_HASH(TABLE)*/ITPUB个人空间?6D V?|Q*KK
　　将指定的表与其他行源通过哈希连接方式连接起来.

b ?FP+BM9F{#K0E0　　例如:ITPUB个人空间&o/L.]+C$L,|
　　SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE
PS2QsEnc1q/a0　　BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

　　<25>. /*+DRIVING_SITE(TABLE)*/
[y(hP1lnp0　　强制与ORACLE所选择的位置不同的表进行查询执行.ITPUB个人空间4h+ob/Cd
n,k
　　例如:ITPUB个人空间%e` Nkq] hK2R Z
　　SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;

　　<26>. /*+LEADING(TABLE)*/ITPUB个人空间:m*bN0P$?A-n9K/[D
　　将指定的表作为连接次序中的首表.
|jg7ygJ{0  ITPUB个人空间T8ra3a`KE
　　<27>. /*+CACHE(TABLE)*/
b,?:rx6g @!w0　　当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端
%BM4gUN|3b2bw0　　例如:
'fb_UQx,Av%I)Kj8f0　　SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM  BSEMPMS;

　　<28>. /*+NOCACHE(TABLE)*/ITPUB个人空间0m3f
gkp|[2f+w
　　当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端ITPUB个人空间+d`9R%H["c V
　　例如:ITPUB个人空间\#GB9h.^e&x rz
　　SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM  BSEMPMS;

　　<29>. /*+APPEND*/
^s2s4Y/z_%U:P0　　直接插入到表的最后,可以提高速度.ITPUB个人空间h1XqS.gM
　　insert /*+append*/ into test1  select * from test4 ;ITPUB个人空间$?T,C!o;YB n
  ITPUB个人空间h^,\d4l+x#j(T{t
　　insert /*+append */ into emp nologging

　　<30>. /*+NOAPPEND*/ITPUB个人空间;[2ky(x"JZ zC]
　　通过在插入语句生存期内停止并行模式来启动常规插入.

　　insert /*+noappend*/ into test1  select * from test4 ;

　　<31>.parallel direct-load insert
6uA(GUwnf(}h@:m0　　sql> alter session enable parallel dml;
V8x.n-p7C Ie0　　sql> insert /*+parallel(emp,2) */ into emp nologging
5}K&f
ZZ,f.F+_@0　　sql> select * from emp_old;