ORACLE性能优化笔记

1. 查询正在执行语句的执行计划(也就是实际语句执行计划)
a7Zoi2sXn-`I[0   select * from v$sql_plan where hash_value = (select sql_hash_value from v$session where sid = 1111);ITPUB个人空间"CP/T*RR2k*i
    其中id和parent_id表示了执行数的结构,数值最大的为最先执行比如

ID  PARENT_ID   

vs
SY$J{e`SR0-------------   ITPUB个人空间b&EB ~-i@c
0 
"Mi!MSmWd/A%F01 0ITPUB个人空间0Y/A;a2TU8K]
2 1ITPUB个人空间/Qj0tI(\
3 2ITPUB个人空间!S-_l8o `#k
4 3ITPUB个人空间U[ X
aP4m0u;Y+L
5 4
Uk3P$C1d yr"tu06 3   

 

 

则执行计划树为
$K"@6G VI{c0              0
0u\vA} e`
Q0              1ITPUB个人空间)A E;a5P\
              2ITPUB个人空间i&S)Kd/`ou|&m
              3ITPUB个人空间#a:yo]zEz
           6     4 
mXs9AYBE?5Ex0                  5

2.如何设置自动跟踪ITPUB个人空间 Iu0i8p.~B!a!QU a]!z6Q
　　用system登录
Xa-G&K`\9\G0　　执行$ORACLE_HOME/rdbms/admin/utlxplan.sql创建计划表
JHXa'^E!{*I0　　执行$ORACLE_HOME/sqlplus/admin/plustrce.sql创建plustrace角色ITPUB个人空间#]l5v(Pt f0`6W-i
　　如果想计划表让每个用户都能使用，则ITPUB个人空间/R,e6l^n5j
　　SQL>create public synonym plan_table for plan_table;ITPUB个人空间f AZ` [
　　SQL> grant all on plan_table to public;

　　如果想让自动跟踪的角色让每个用户都能使用，则
)e5M*ZKW)vs*b0　　SQL> grant plustrace to public;
/iX!|,E)qZ6W\0　　通过如下语句开启/停止跟踪
^#u^ib'X0　　SET AUTOTRACE ON |OFF | ON EXPLAIN | ON STATISTICS | TRACEONLY | TRACEONLY EXPLAIN

3.如何跟踪自己的会话或者是别人的会话ITPUB个人空间 e4dIfg
z
　　跟踪自己的会话很简单ITPUB个人空间B,| `}9FAd
　　Alter session set sql_trace true|falseITPUB个人空间i!Sod!D
{$vqw3B(h[
　　Or
3xK \I`1I0　　Exec dbms_session.set_sql_trace(TRUE);

　　如果跟踪别人的会话，需要调用一个包
,K;Y}@+}w0　　exec dbms_system.set_sql_trace_in_session(sid,serial#,true|false)

　　跟踪的信息在user_dump_dest 目录下可以找到或通过如下脚本获得文件名称（适用于Win环境，如果是unix需要做一定修改）
8RK YW.B9l:?0　　SELECT p1.value||'\'||p2.value||'_ora_'||p.spid||'.ora' filename
O.jF8E+XK
YN_4w|0　　FROM
`,u hK^!f{1X0　　v$process p,
|GheY zq0　　v$session s,ITPUB个人空间 bFWk4nE&m'oy
　　v$parameter p1,
7z
T/A Q5q{x~0　　v$parameter p2ITPUB个人空间%{rB3D Lv2K
　　WHERE p1.name = 'user_dump_dest'ITPUB个人空间X/x'p,F&JcA:iy
　　AND p2.name = 'db_name'
%i fBZR0　　AND p.addr = s.paddr
QhP'o\3pD0　　AND s.audsid = USERENV ('SESSIONID')
t0r6\|\N6KX#Du R0　　最后，可以通过Tkprof来解析跟踪文件，如ITPUB个人空间D7EH([ Ve3c
　　Tkprof 原文件 目标文件 sys=n

4.怎么设置整个数据库系统跟踪
MpP|-b7s0　　其实文档上的alter system set sql_trace=true是不成功的，但是可以通过设置事件来完成这个工作，作用相等
.H+e7B@&TZ7X0　　alter system set eventsITPUB个人空间K sB*ta${n*A
　　'10046 trace name context forever,level 1';

　　如果关闭跟踪，可以用如下语句ITPUB个人空间7yxU-uR&zL xV
　　alter system set events
p(CM9]7JD4F0　　'10046 trace name context off';

其中的level 1与上面的8都是跟踪级别
wH%l;L"E*J/LQM)D-H0　　level 1：跟踪SQL语句，等于sql_trace=true
k;G2Y!F0@E$e8l0　　level 4：包括变量的详细信息
Z&]7N7][0P|-f0　　level 8：包括等待事件
,_b
@4H:L"{MI0　　level 12：包括绑定变量与等待事件

5.怎么样根据OS进程快速获得DB进程信息与正在执行的语句ITPUB个人空间/eBix*\{b
　　有些时候，我们在OS上操作，象TOP之后我们得到的OS进程，怎么快速根据OS信息获得DB信息呢？ITPUB个人空间T0P
Urn
　　我们可以编写如下脚本：
(t0m w%@.Y0　　$more whoit.sh
S8VBL2i&{u8AN0　　#!/bin/shITPUB个人空间-o4}'blQ(Y7l)@
　　sqlplus /nolog 100,cascade=> TRUE);ITPUB个人空间"a4gS2_4K-By
　　dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);

这是对命令与工具包的一些总结
.I&@Zy uKA0　　<1>、对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句。ITPUB个人空间 M5K&K*gaLS&S
　　　a) 可以并行进行，对多个用户，多个TableITPUB个人空间'L7C%oo"aFi
　　　b) 可以得到整个分区表的数据和单个分区的数据。
L#O\'U/ur4S,O0　　　c) 可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区ITPUB个人空间 q
u"E}!?ML J
　　　d) 可以倒出统计信息
~8xE4g5K0　　　e) 可以用户自动收集统计信息
!F,R6b:JEr.g0　　<2>、DBMS_STATS的缺点
(kz6DS#j0kN!v9@0　　　a) 不能Validate Structure
y.fJ/n
V
y,V&j0　　　b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，这两个仍旧需要使用Analyze语句。ITPUB个人空间jL8R1@b^G.Bt7H p
　　　c) DBMS_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为TrueITPUB个人空间5s vb8f/W!U%f;e4Q
　　<3>、对于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息。

6.怎么样快速重整索引ITPUB个人空间C'r#mWo
　　通过rebuild语句，可以快速重整或移动索引到别的表空间
/v3xD8p@0　　rebuild有重建整个索引数的功能，可以在不删除原始索引的情况下改变索引的存储参数
RJALh4X0　　语法为
2A;k`v&E.e0　　alter index index_name rebuild tablespace ts_name
N$tL#Kc Ca,C4~0　　storage(......);

　　如果要快速重建整个用户下的索引，可以用如下脚本，当然，需要根据你自己的情况做相应修改
^j u)yC%KD"`E0　　SQL> set heading off
,x6Bn:ZXu _0　　SQL> set feedback off
V5e1c~X;}'e6eVt0　　SQL> spool d:\index.sql
aUc~5V[}0　　SQL> SELECT 'alter index ' || index_name || ' rebuild '
$`bG2IW0　　||'tablespace INDEXES storage(initial 256K next 256K pctincrease 0);'ITPUB个人空间e)O/|2["D&\
　　FROM all_indexesITPUB个人空间W[-c8B+B$F BTC
　　WHERE ( tablespace_name != 'INDEXES'ITPUB个人空间Qj%{+XKD9_,y
　　OR next_extent != ( 256 * 1024 )ITPUB个人空间1q{;nId-P/W"C
　　)
;F R\,L)ec7T0　　AND wner = USER
8U{t
]/@W/Oqqt0　　SQL>spool off

　　另外一个合并索引的语句是ITPUB个人空间u$cLk3{.U l/R"J0F
　　alter index index_name coalesce

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

7.如何使用Hint提示
(~|NoX0　　在select/delete/update后写/*+ hint */ITPUB个人空间 X0W2v2jU@&JN*?/L7l
　　如select /*+ index(TABLE_NAME INDEX_NAME) */ col1...

　　注意/*和+之间不能有空格，如用hint指定使用某个索引ITPUB个人空间$U!w*A%O9uU0j%r
　　select /*+ index(cbotab) */ col1 from cbotab;ITPUB个人空间-^q7\i5A2?X"L
　　select /*+ index(cbotab cbotab1) */ col1 from cbotab;
(g R5\Q [g0　　select /*+ index(a cbotab1) */ col1 from cbotab a;

　　其中ITPUB个人空间5J qb t2J `-K:x
　　TABLE_NAME是必须要写的，且如果在查询中使用了表的别名，在hint也要用表的别名来代替表名;ITPUB个人空间)E7w&Ho]d%e
　　INDEX_NAME可以不必写，Oracle会根据统计值选一个索引;ITPUB个人空间nLTL$x
　　如果索引名或表名写错了，那这个hint就会被忽略;

8.怎么样快速复制表或者是插入数据
9d}'MU'\0　　快速复制表可以指定Nologging选项ITPUB个人空间B%~RhJ&Nh
　　如：Create table t1 nologging
lv;ZR6F6LsH0　　as select * from t2;

　　快速插入数据可以指定append提示，但是需要注意noarchivelog模式下，默认用了append就是nologging模式的。 在archivelog下，需要把表设置程Nologging模式。ITPUB个人空间&~1|.Q-FA
　　如insert /*+ append */ into t1ITPUB个人空间:h}JQ#M?
　　select * from t2

　　注意：如果在9i环境中并设置了FORCE LOGGING，则以上操作是无效的，并不会加快，当然，可以通过如下语句设置为NO FORCE LOGGING。
p.eif3F*komC!q0　　Alter database no force logging;ITPUB个人空间7[,B/O.oO@
　　是否开启了FORCE LOGGING，可以用如下语句查看
H(R oOV5JrW0　　SQL> select force_logging from v$database;

9.怎么避免使用特定索引
qC)Ta)O4m&j0　　在很多时候，Oracle会错误的使用索引而导致效率的明显下降，我们可以使用一点点技巧而避免使用不该使用的索引，如：
u%{"Wo[M6Vx0　　表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个人空间as|3c y
　　where a=? and b=? and c=? group by b+0 --如果b是数字

　　通过这样简单的改变，往往可以是查询时间提交很多倍ITPUB个人空间:~;F qmOr6{:|8TYHq;B
　　当然，我们也可以使用no_index提示，相信很多人没有用过，也是一个不错的方法：
_2wq6H
i:a)X0　　select /*+ no_index(t,inx_b) */ * from test t
tSr b dj2na0　　where a=? and b=? and c=? group by b

　　举例：ITPUB个人空间p6d_5ZC1w(P0s
　　本来在CM_USER上有索引IDX_CM_USER4(ACC_ID)和IDX_CM_USER8(BILL_ID),可是执行如下语句的时候很慢。
QZ{!n7OS0　　select * from CM_USER where  acc_id =1200007175
lnHnUd"l'E0　　and user_status>0 and bill_id like '13%' order by acc_id,bill_id

　　用explain分析，发现执行计划是用IDX_CM_USER8.如下查询
b:`9YCj%mT0　　select * from user_indexes where table_name ='CM_USER'发现IDX_CM_USER8没有分析过。

　　用下面语句执行计划改变
-M)Pn:l7C{0　　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

　　或者分析索引ITPUB个人空间2JioJ:L/J8Nx7N
　　exec dbms_stats.gather_index_stats(ownname => 'QACS1',indname => 'IDX_CM_USER8',estimate_percent => 5 );ITPUB个人空间XI;Xt6ETb)T _
　　可以发现执行计划恢复正常。

ITPUB个人空间:Jz\bNCa
10.Oracle什么时候会使用跳跃式索引扫描
;I*E6OCF\H0　　这是9i的一个新特性跳跃式索引扫描(Index Skip Scan).
X_
C(h qm9^0　　例如表有索引index(a,b,c)，当查询条件为where b=?的时候，可能会使用到索引index(a,b,c)，如，执行计划中出现如下计划：ITPUB个人空间F+c9S0iF aS'v:e
　　INDEX (SKIP SCAN) OF 'TEST_IDX' (NON-UNIQUE)

　　Oracle的优化器(这里指的是CBO)能对查询应用Index Skip Scans至少要有几个条件:
!qmK)db0o3O0　　<1>优化器认为是合适的。
&U#JI-Fd$pr l#{0　　<2>索引中的前导列的唯一值的数量能满足一定的条件（如重复值很多）。
!`&Pn*k{m}*qd2v7w0　　<3>优化器要知道前导列的值分布(通过分析/统计表得到)。
cF]?p,\0　　<4>合适的SQL语句ITPUB个人空间s'_E|o9Leo
　　等。

ITPUB个人空间p5V.w5K oq\#\M1nq
11.怎么样创建使用虚拟索引
"}$CgVN:[C)m+@Z(V}0　　可以使用nosegment选项，如ITPUB个人空间B%Ynw-]z
　　create index virtual_index_name on table_name(col_name) nosegment;

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

　　就可以利用explain plan for select ……来看虚拟索引的效果，利用@$ORACLE_HOME/rdbms/admin/utlxpls查看执行计划，最后，根据需要，我们可以删除虚拟索引，如普通索引一样
KhL-U:XmX7@G0　　drop index virtual_index_name;

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

12.怎样监控无用的索引
3f4TDm5Wx)DP6L:V0　　Oracle 9i以上，可以监控索引的使用情况，如果一段时间内没有使用的索引，一般就是无用的索引ITPUB个人空间3\9J
`keq
　　语法为：
k'?{'@}Q/F0　　开始监控：alter index index_name monitoring usage;ITPUB个人空间$C[E/R7n4`6R
　　检查使用状态：select * from v$object_usage;ITPUB个人空间b`\`9ju
　　停止监控：alter index index_name nomonitoring usage;

　　当然，如果想监控整个用户下的索引，可以采用如下的脚本：ITPUB个人空间/Q["e,Dz
　　set heading offITPUB个人空间,j2`)S8hC(Ao
　　set echo off
~W'?%tG)_S%C0　　set feedback off
'O/l5p4d7s ~2wq y0　　set pages 10000ITPUB个人空间*Bw~+} um"M~ueIr
　　spool start_index_monitor.sqlITPUB个人空间h5Dd2P)m$Ii
　　SELECT 'alter index '||owner||'.'||index_name||' monitoring usage;'
gB`Q/T;H0　　FROM dba_indexes
GNYDL!\T:O*DZ0　　WHERE wner = USER;ITPUB个人空间B2@!]
g1Ixu+B*y
　　spool off
z:ID^.?0　　set heading on
4U@"q4e)RB!FcN-X0　　set echo on
;krR*J$Z!~ n^`:E0　　set feedback onITPUB个人空间5D[R uH
　　------------------------------------------------ITPUB个人空间w9lg1o2l v
　　set heading off
C6gJ`'N0　　set echo offITPUB个人空间Li9N*?1~^9tH0\"F
　　set feedback off
&[0S"k["Bao0　　set pages 10000
@K)J-sz:Fk4F'N0　　spool stop_index_monitor.sqlITPUB个人空间|R@z%g"}bn2gi
　　SELECT 'alter index '||owner||'.'||index_name||' nomonitoring usage;'ITPUB个人空间TM
i`;Q+NH
　　FROM dba_indexes
] v$IZ*q]z0　　WHERE wner = USER;ITPUB个人空间'{_^3q;|3u
　　spool off
+e U9i@9B]Vwy0　　set heading onITPUB个人空间;eE$c5ml
　　set echo onITPUB个人空间O6J.k{'C P!W
　　set feedback on

13.怎么样能固定我的执行计划ITPUB个人空间(\9T0m#@$Ov
　　可以使用OUTLINE来固定SQL语句的执行计划，用如下语句可以创建一个OUTLINEITPUB个人空间ra+Q,EZT"A
　　Create oe replace outline OutLn_Name on
/G/W!N&xJ]0　　Select Col1,Col2 from Table
(M"c_tQ/q.Ex kHj0　　where .......

　　如果要删除Outline，可以采用ITPUB个人空间}"`t-o&EH
　　Drop Outline OutLn_Name;

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

　　update outln.ol$hints(ol_name,'TEST1','TEST2','TEST2','TEST1)
X;Y!M+~!p9K'{c
L0　　where ol_name in ('TEST1','TEST2');

　　这样，你就把Test1 OUTLINE与Test2 OUTLINE互换了，如果想利用已经存在的OUTLINE，需要设置以下参数ITPUB个人空间^,P*}5_0A QA
　　Alter system/session set Query_rewrite_enabled = true
j?T$N
?/smmy0　　Alter system/session set use_stored_outlines = true

14.v$sysstat中的class分别代表什么
3D*KHc O)c.C0　　统计类别
Z{lWKFp.{w \0　　1 代表事例活动
8i3mzA5AUVp0　　2 代表Redo buffer活动ITPUB个人空间}v0h0{v~'L
　　4 代表锁
5o/{:g~ Xr\E0　　8 代表数据缓冲活动
Mx L7mD0　　16 代表OS活动ITPUB个人空间'C.K%@"I/f:y
　　32 代表并行活动
f-vC i\1M? Y1o0　　64 代表表访问ITPUB个人空间l)`V%JQ+K)t
　　128 代表调试信息

15.怎么杀掉特定的数据库会话ITPUB个人空间r Ga$G$hR sa.e
　　Alter system kill session 'sid,serial#';ITPUB个人空间)x]w9m:QI(Gl
　　或者
3ewj?X0　　alter system disconnect session 'sid,serial#' immediate;

　　在win上，还可以采用oracle提供的orakill杀掉一个线程（其实就是一个Oracle进程）
G:i Lb
C%T Y0　　在Linux/Unix上，可以直接利用kill杀掉数据库进程对应的OS进程

16.怎么快速查找锁与锁等待ITPUB个人空间'xdk+v7yh3C"c
　　数据库的锁是比较耗费资源的，特别是发生锁等待的时候，我们必须找到发生等待的锁，有可能的话，杀掉该进程。ITPUB个人空间,wiB3N0I'V @%X@
　　这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。
f0J1_)Bu5zI0　　可以通过alter system kill session ‘sid,serial#’来杀掉会话

　　SELECT /*+ rule */ s.username,ITPUB个人空间8PB E6lS9j)k
　　decode(l.type,'TM','TABLE LOCK','TX','ROW LOCK',NULL) LOCK_LEVEL,ITPUB个人空间'e{3@3Q7a)F8D$|
　　o.owner,o.object_name,o.object_type,
;r.s$c xK7ph3_0　　s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuserITPUB个人空间uvy*|0P
iZ/h-z~[
　　FROM v$session s,v$lock l,dba_objects oITPUB个人空间o H[%IYf%BN'O U
　　WHERE l.sid = s.sid
%H$[$WfP3jjp0　　AND l.id1 = o.object_id(+)ITPUB个人空间`@#v;n
kG/y%B:J
　　AND s.username is NOT NULL

　　如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待，以下的语句可以查询到谁锁了表，而谁在等待。ITPUB个人空间4e@pC#P\e4ng
　　SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,ITPUB个人空间u
Qv(?1ub
　　o.owner,o.object_name,o.object_type,s.sid,s.serial#ITPUB个人空间N5ky#D5FS
　　FROM v$locked_object l,dba_objects o,v$session sITPUB个人空间$H.Fgj8U9^
　　WHERE l.object_id=o.object_idITPUB个人空间8zK
H)_5l$N2_,~~5y a
　　AND l.session_id=s.sidITPUB个人空间
X/AtxI/NDx,k#SI
　　ORDER BY o.object_id,xidusn DESC

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

　　[Q] 如何有效的删除一个大表(extent数很多的表)
F%}??cMs0　　[A] 一个有很多(100k)extent的表，如果只是简单地用drop table的话，会很大量消耗CPU（Oracle要对fet$、uet$数据字典进行操作），可能会用上几天的时间，较好的方法是分多次删除extent，以减轻这种消耗：ITPUB个人空间[McbK1o2y BJ7YR
　　1. truncate table big-table reuse storage;ITPUB个人空间4P/JR{Ov/I
　　2. alter table big-table deallocate unused keep 2000m ( 原来大小的n-1/n);ITPUB个人空间dM/E G+?`bb.d
　　3. alter table big-table deallocate unused keep 1500m ;ITPUB个人空间n S+v?:h _\1i
　　....
-i$o'aXyrY0　　4. drop table big-table;

17.如何收缩临时数据文件的大小
+H6J&Sn%VJR0　　9i以下版本采用ITPUB个人空间9G&m'ldN-k4Hql6u)v6u
　　ALTER DATABASE DATAFILE 'file name' RESIZE 100M类似的语句
L*L7aio0　　9i以上版本采用ITPUB个人空间6d6j%s(dj eu_ p
　　ALTER DATABASE TEMPFILE 'file name' RESIZE 100M
qEOkG]c-^
Kz c0　　注意，临时数据文件在使用时，一般不能收缩，除非关闭数据库或断开所有会话，停止对临时数据文件的使用。

18.怎么清理临时段
M,QJu{,J'\0　　可以使用如下办法ITPUB个人空间S9@+ql)H"I
　　<1>、 使用如下语句查看一下认谁在用临时段ITPUB个人空间n2T,_,N,V
　　SELECT username,sid,serial#,sql_address,machine,program,
D/a5X cM4q EwL0　　tablespace,segtype, contents
`LepV&Vn$n-H0　　FROM v$session se,v$sort_usage su
k S(qWYK3D3Q)h0　　WHERE se.saddr=su.session_addr

 　　<2>、 那些正在使用临时段的进程ITPUB个人空间/w O"A/F/OP^N*N
　　SQL>Alter system kill session 'sid,serial#';

　　<3>、把TEMP表空间回缩一下ITPUB个人空间;KlNv'L UCF%b K
　　SQL>Alter tablespace TEMP coalesce;

ITPUB个人空间hX*v
G XdR q0M
　　还可以使用诊断事件ITPUB个人空间:xK o.^Z~:Zv.G$~
　　<1>、 确定TEMP表空间的ts#ITPUB个人空间V{jB}zZ kX
　　SQL> select ts#, name FROM v$tablespace;ITPUB个人空间-I9YP+z[R!X
　　TS# NAME
:N,^W"L"{ A%D3W0　　-----------------------
d9j2rP~9kG0　　0 SYSYEM
S]*d+S"S2SY+b0　　1 RBSITPUB个人空间H }g.q?NaOr#u
　　2 USERS
,kA8r:}Z8w*xY0　　3* TEMPITPUB个人空间Zst];Gb
　　...

　　<2>、 执行清理操作ITPUB个人空间KX_hOmq)?
　　alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1'ITPUB个人空间 S9?5x`mN
|;V
　　说明：
!ah!T/mn vk0　　temp表空间的TS# 为 3*, So TS#+ 1= 4，如果想清除所有表空间的临时段，则，TS# = 2147483647

19.怎么样dump数据库内部结构，如上面显示的控制文件的结构ITPUB个人空间4P)j7I/V;y:k|
　　常见的有
!}4J.C^7Qr K#Ma0　　1、分析数据文件块，转储数据文件n的块m
-]z @?
}$mjD$_0　　alter system dump datafile n block m

　　2、分析日志文件
/fX*E#tT_n&k.[0　　alter system dump logfile logfilename;

　　3、分析控制文件的内容ITPUB个人空间}7VT&hcA
　　alter session set events 'immediate trace name CONTROLF level 10'

　　4、分析所有数据文件头
*^BdF$^%j5K!U&l0　　alter session set events 'immediate trace name FILE_HDRS level 10'

　　5、分析日志文件头
mG8u1h#R,h(m%S Vg0　　alter session set events 'immediate trace name REDOHDR level 10'

　　6、分析系统状态，最好每10分钟一次，做三次对比ITPUB个人空间qsU/pV"D_3p
　　alter session set events 'immediate trace name SYSTEMSTATE level 10'

　　7、分析进程状态
:W/c,K!KT:_0F`0　　alter session set events 'immediate trace name PROCESSSTATE level 10'

　　8、分析Library Cache的详细情况
B)joj s*R[ R`Ur5V0　　alter session set events 'immediate trace name library_cache level 10'

ITPUB个人空间c{$CQ/Rh/K
20.如何获得所有的事件代码
h#[E#K.NP p0　　事件代码范围一般从10000 to 10999，以下列出了这个范围的事件代码与信息
)\ A)AoiIj0　　SET SERVEROUTPUT ONITPUB个人空间3]ORgSPl+V
　　DECLARE
xM+BS&E3Q
x0　　err_msg VARCHAR2(120);ITPUB个人空间n&RBJ D$T*U
　　BEGIN
H1z0g&cH0　　dbms_output.enable (1000000);ITPUB个人空间]Yb X0^|um
　　FOR err_num IN 10000..10999
Z7n u\)w&m&b0　　LOOP
0a2E0Y6{:b0tq&\0　　err_msg := SQLERRM (-err_num);
*N0X7_']%ija%i.d#O0　　IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN
"`Si'?d |0　　dbms_output.put_line (err_msg);ITPUB个人空间5I8d[ OX r
　　END IF;ITPUB个人空间:w`{{7w7Vs$nf
　　END LOOP;
(p'j DO+bc/F'C/s0　　END;ITPUB个人空间l'oF2rlJS6m
　　/

　　在Unix系统上，事件信息放在一个文本文件里
{M%jh)Yt(r0　　$ORACLE_HOME/rdbms/mesg/oraus.msg
q3[g ]"t0　　可以用如下脚本查看事件信息ITPUB个人空间SUq8w+GA:~
　　event=10000
7R:P}!z YB0　　while [ $event -ne 10999 ]ITPUB个人空间lq%`` i F.u;qx
　　doITPUB个人空间+H/H.K{-to C-b(e
　　event=`expr $event + 1`ITPUB个人空间wv
iR*j%i]%b9i[V
　　oerr ora $event
,^^jQ0Tn{M` Kn.F0　　done

　　对于已经确保的/正在跟踪的事件，可以用如下脚本获得ITPUB个人空间s
F,RJ$E*V"a%D$kV;s
　　SET SERVEROUTPUT ONITPUB个人空间6sKWL\}jw
　　DECLARE
b B&b$j4A t
MR0　　l_level NUMBER;ITPUB个人空间U/rG[/k;X
　　BEGINITPUB个人空间(b.D3Vimgg
　　FOR l_event IN 10000..10999ITPUB个人空间?0~_C+pQZ6c
　　LOOP
G~x5K2BLl0　　dbms_system.read_ev (l_event,l_level);
Q1I0{!?:Z.Pf_x0　　IF l_level > 0 THENITPUB个人空间8s8p;S*F*A
　　dbms_output.put_line ('Event '||TO_CHAR (l_event)||
w S%DXh0　　' is set at level '||TO_CHAR (l_level));ITPUB个人空间I6h"omCJ],J
　　END IF;ITPUB个人空间6JQ7Xlw
　　END LOOP;ITPUB个人空间8NdI6r;I(d$l
　　END;
|mXEZ N+Ne8Q0　　/

21.什么是STATSPACK，我怎么使用它？
T#L'{A'p0b-Z0　　Statspack是Oracle 8i以上提供的一个非常好的性能监控与诊断工具，基本上全部包含了BSTAT/ESTAT的功能，更多的信息，可以参考附带文档$ORACLE_HOME/rdbms/admin/spdoc.txt。

　　安装Statspack:ITPUB个人空间pA3f4f:A
　　cd $ORACLE_HOME/rdbms/admin
haBP(X
A/l0　　sqlplus "/ as sysdba" @spdrop.sql -- 卸载，第一次可以不需要
1^f1Q1ZZJ{0　　sqlplus "/ as sysdba" @spcreate.sql -- 需要根据提示输入表空间名

　　使用Statspack:ITPUB个人空间m7W!eR/@N}L|
　　sqlplus perfstat/perfstatITPUB个人空间UI@,f9n I GT.tY
　　exec statspack.snap; -- 进行信息收集统计，每次运行都将产生一个快照号ITPUB个人空间Oq:@ ~g ]v
　　-- 获得快照号，必须要有两个以上的快照，才能生成报表
R-~z*r+@%rK0　　select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;ITPUB个人空间K;@^X:`.jE.C
　　@spreport.sql -- 输入需要查看的开始快照号与结束快照号

　　其他相关脚本s:
8u1Xa#_'K0　　spauto.sql - 利用dbms_job提交一个作业，自动的进行STATPACK的信息收集统计
~1w9J"dF1dB/b0　　sppurge.sql - 清除一段范围内的统计信息，需要提供开始快照与结束快照号ITPUB个人空间?8?o bP].s#Y
　　sptrunc.sql - 清除(truncate)所有统计信息

,{i[S*bT!l022. SQL语句的优化方法
![!rE+p/C;` Z7f0　　<1> /*+ALL_ROWS*/ITPUB个人空间6h$I8b/a\0n5F0w
　　表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.ITPUB个人空间nM0e!A0Fm*r u"V+B
　　例如:ITPUB个人空间 iNt n X:`)W
　　SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';

　　<2>. /*+FIRST_ROWS*/
%Q
z6V@)N-Q0　　表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.
O X'Q}
m0　　例如:
"E/v@m8OS5fE0　　SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE    EMP_NO='CCBZZP';

　　<3>. /*+CHOOSE*/ITPUB个人空间J |!z0{-sE
　　表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;ITPUB个人空间8u6`hEg1iL@
　　表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;ITPUB个人空间-C-q I%|&Stxb
　　例如:ITPUB个人空间] ]:g0T3h)@x
　　SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';

　　<4>. /*+RULE*/
"B~ k'tS,Z VS-cJ0　　表明对语句块选择基于规则的优化方法.ITPUB个人空间 u-p8F[3R2J5A
　　例如:
w._?}T j_;a0　　SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';  

　　<5>. /*+FULL(TABLE)*/
{rk5C)f Zwu"k0　　表明对表选择全局扫描的方法.
!StZOK1f0\P{0　　例如:ITPUB个人空间1_J/[5Q P CM M+A*v
　　SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='CCBZZP';

　　<6>. /*+ROWID(TABLE)*/ITPUB个人空间*D?_$cv6gj
　　提示明确表明对指定表根据ROWID进行访问.
@s#O2zxT&qK0　　例如:
3a!NDpN;\0　　SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'
3W;Z&q*T;^QWm#@0   AND EMP_NO='CCBZZP';

　　<7>. /*+CLUSTER(TABLE)*/ITPUB个人空间Do$A hUm_/`H
　　提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.
;P8^| z%iNTjl"g0　　例如:ITPUB个人空间+X_ X+f$tp
　　SELECT  /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMSITPUB个人空间%v"Gq%{.~cQ
　　WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

　　<8>. /*+INDEX(TABLE INDEX_NAME)*/
q A#Jm6Le#zU-s@`0　　表明对表选择索引的扫描方法.

D*Lsbh3u.qr(F+@0　　例如:
/}7fa.@XwUp0　　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个人空间1[G0G7P$FLFS
　　表明对表选择索引升序的扫描方法.
~ ?s'd&^?`!I0　　例如:ITPUB个人空间/m4WX^2tRfT#b
　　SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */  FROM BSEMPMS WHERE DPT_NO='CCBZZP';

　　<10>. /*+INDEX_COMBINE*/ITPUB个人空间8A1{,pMQG
　　为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的ITPUB个人空间q&LPMZ
U"v5vQ9t
　　布尔组合方式.ITPUB个人空间 ee(x EEie4{4D
　　例如:
,e:|q-R3bC(n`0　　SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
6Vp b5o~+FWRL0　　WHERE SAL<5000000 AND HIREDATE<SYSDATE;

　　<11>. /*+INDEX_JOIN(TABLE INDEX_NAME)*/ITPUB个人空间E$|0Yy8C
　　提示明确命令优化器使用索引作为访问路径.ITPUB个人空间r3QkYE'b)w!r1\
　　例如:ITPUB个人空间9vX{,Ug:w(ZC
　　SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATEITPUB个人空间V*Lj/snq2g&NYr5p
　　FROM BSEMPMS WHERE SAL<60000;

　　<12>. /*+INDEX_DESC(TABLE INDEX_NAME)*/ITPUB个人空间?xqHKK
　　表明对表选择索引降序的扫描方法.
8]"o{x)cD7R0　　例如:
'f|h2ww/RdQ0　　SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */  FROM BSEMPMS WHERE    DPT_NO='CCBZZP';

　　<13>. /*+INDEX_FFS(TABLE INDEX_NAME)*/
H!LF/a'W8L0　　对指定的表执行快速全索引扫描,而不是全表扫描的办法.
di/rB2Y)\E+U _ ^v0　　例如:ITPUB个人空间w UnG+Vm~6SA
　　SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';

<14>. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/
5L#G5w/C?%W-cA+j0　　提示明确进行执行规划的选择,将几个单列索引的扫描合起来.ITPUB个人空间{BsM9g;x\zG
　　例如:ITPUB个人空间Qc zg"wl.L|7M.|3W
　　SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='CCBZZP' AND DPT_NO='TDC306';

　　<15>. /*+USE_CONCAT*/
\{ U5_Ce-_d0　　对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询.
9l!Gf&`*K;T,E'_0j.Y6x0　　例如:
gf,g?z0　　SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

　　<16>. /*+NO_EXPAND*/
^;P^zm2ufs0　　对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展.ITPUB个人空间rM&wu KEj
　　例如:ITPUB个人空间"}OE&I/C|*}
　　SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE  DPT_NO='TDC506' AND SEX='M';

　　<17>. /*+NOWRITE*/
Yf3P]Yz;G/J0　　禁止对查询块的查询重写操作.

　　<18>. /*+REWRITE*/
5N7?5`$XL+iys|0　　可以将视图作为参数.

　　<19>. /*+MERGE(TABLE)*/
6tprc-Hd&Z0　　能够对视图的各个查询进行相应的合并.ITPUB个人空间@4r+av%q'Cs[
　　例如:
4Hc?k}0　　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)*/
[,r*aAr:U?$aA0　　对于有可合并的视图不再合并.ITPUB个人空间:v4l8t'C_'euNX!^
　　例如:
:QBw*P*C'pF0　　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;
8m&K_/q7T`?&k2D3_0  
qfX&TThJ"IJ7[0　　<21>. /*+ORDERED*/ITPUB个人空间 |!Q*L ^E7t\x]*s KS7L
　　根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接.
h!JH(BSO+H*x(IQ0　　例如:ITPUB个人空间@(Iu4H*QRh
　　SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 CITPUB个人空间*tOuM4z-]
　　WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

　　<22>. /*+USE_NL(TABLE)*/
XG%Whq:b0　　将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.

t%~2[a,aDz&`(l0　　例如:
?V)Q |Q0　　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)*/ITPUB个人空间!WX9Y O}7?-{e*i(@Y9j
　　将指定的表与其他行源通过合并排序连接方式连接起来.
i-X'x6HmF;Tq0　　例如:
jN:tY?DbG5q6}1_S0　　SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHEREITPUB个人空间4~Q&PN8lhO;fl
BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

　　<24>. /*+USE_HASH(TABLE)*/
4SRNJ!k0　　将指定的表与其他行源通过哈希连接方式连接起来.ITPUB个人空间Sa)Lf;hl
　　例如:
Y*Qd v8@s4@Y7Q0　　SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE
%xX]+lk9pu/ci)D0　　BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

　　<25>. /*+DRIVING_SITE(TABLE)*/ITPUB个人空间^Vm$u
oDi l
　　强制与ORACLE所选择的位置不同的表进行查询执行.ITPUB个人空间*A9LoU _X-pAw7n
　　例如:ITPUB个人空间w|.A
d2}^
　　SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;

　　<26>. /*+LEADING(TABLE)*/
-_U6{7PW2Llk0　　将指定的表作为连接次序中的首表.
f St0Q3H9erT0  
)\ m(yCj`J0　　<27>. /*+CACHE(TABLE)*/
#h1E-`J$id0　　当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端
ZL+q+I']'N d0　　例如:
'[&y"aBBn$eq|0　　SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM  BSEMPMS;

　　<28>. /*+NOCACHE(TABLE)*/ITPUB个人空间 L-KvrB%pK;l"|
　　当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端
?Xk#jh`t0　　例如:ITPUB个人空间?a*ki4f7B,ld
　　SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM  BSEMPMS;

　　<29>. /*+APPEND*/
k\3jh;A0y j0　　直接插入到表的最后,可以提高速度.
3b aOr5p0　　insert /*+append*/ into test1  select * from test4 ;ITPUB个人空间8Y$Hww_!z
  
"w5d2W KDJ|m1|0　　insert /*+append */ into emp nologging

　　<30>. /*+NOAPPEND*/ITPUB个人空间 g)| D+^E_$lU;xu
　　通过在插入语句生存期内停止并行模式来启动常规插入.

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

　　<31>.parallel direct-load insert
#Wp#p Oo!M-]2o0　　sql> alter session enable parallel dml;
|*Dg'Cz*YRU~k.G0　　sql> insert /*+parallel(emp,2) */ into emp nologgingITPUB个人空间pooFbA0hZ$}b
　　sql> select * from emp_old;