今天分析数据时,把命令敲错了,本来想写analyze ,却写成了alter,后来发现了,以前还没有对alter和analyze分析和修改数据的过程进行比较,开了session跟踪比较了一下:
SQL> alter index pk_emp compute statistics;
索引已更改。
C:\oracle\product\10.2.0\admin\devdb\udump>tkprof devdb_ora_4540.trc test.txt explain=scott/tiger
TKPROF: Release 10.2.0.1.0 - Production on 星期三 7月 23 14:46:00 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,
dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17
where
owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is
null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)
and(subname=:12 or subname is null and :12 is null)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 4 3 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 3 2
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE OBJ$ (cr=2 pr=0 pw=0 time=105 us)
1 INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=22 us)(object id 37)
看到,alter 操作只是引起了用户对象信息的更新.
SQL> analyze index pk_emp compute statistics;
索引已分析
C:\oracle\product\10.2.0\admin\devdb\udump>tkprof devdb_ora_5796.trc test2.txt explain=scott/tiger
TKPROF: Release 10.2.0.1.0 - Production on 星期三 7月 23 14:54:50 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
insert into sys.wri$_optstat_ind_history(obj#,rowcnt,leafcnt,distkey, lblkkey,
dblkkey,clufac,blevel,analyzetime,samplesize,guessq,cachedblk, cachehit,
logicalread, savtime,flags)
values
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 8 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 8 1
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
delete from ind_stats$
where
obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE IND_STATS$ (cr=1 pr=0 pw=0 time=36 us)
0 INDEX UNIQUE SCAN I_IND_STATS$_OBJ# (cr=1 pr=0 pw=0 time=21 us)(object id 711)
********************************************************************************
update ind$ set ts#=:2,file#=:3,block#=:4,intcols=:5,type#=:6,flags=:7,
property=:8,pctfree$=:9,initrans=:10,maxtrans=:11,blevel=:12,leafcnt=:13,
distkey=:14,lblkkey=:15,dblkkey=:16,clufac=:17,cols=:18,analyzetime=:19,
samplesize=:20,dataobj#=:21,degree=decode(:22,1,null,:22),instances=
decode(:23,1,null,:23),rowcnt=:24,pctthres$=:31*256+:25, indmethod#=:26,
trunccnt=:27,spare1=:28,spare4=:29,spare2=:30,spare6=:32where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 2 2 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 2 2 1
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 UPDATE IND$ (cr=2 pr=0 pw=0 time=226 us)
1 INDEX UNIQUE SCAN I_IND1 (cr=2 pr=0 pw=0 time=26 us)(object id 39)
********************************************************************************
delete from ind_online$
where
obj#= :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 3 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 3 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE IND_ONLINE$ (cr=3 pr=0 pw=0 time=83 us)
0 TABLE ACCESS FULL IND_ONLINE$ (cr=3 pr=0 pw=0 time=69 us)
而analyze 是实际把分析数据进行删除和插入,同时对对象信息进行更新.
总结:alter引起的是索引更改,还是object方面信息变更,而analyze是索引分析,对索引相关统计数据的更新.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/175005/viewspace-407117/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/175005/viewspace-407117/