alter index compute statistics與analyze index的比較

  今天分析資料時,把命令敲錯了,本來想寫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/，如需轉載，請註明出處，否則將追究法律責任。