偶然的實驗，查詢兩條記錄的全表很久，10046真好用...

PiscesCanon發表於2017-11-06
原文網址 : http://blog.itpub.net/30174570/viewspace-2146898/
作業系統版本：
  1. [oracle@oracle trace]$ uname -a
  2. Linux oracle.example.com 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
  3. [oracle@oracle trace]$ lsb_release -a
  4. LSB Version:     :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
  5. Distributor ID: RedHatEnterpriseServer
  6. Description:    Red Hat Enterprise Linux Server release 6.5 (Santiago)
  7. Release:        6.5
  8. Codename:       Santiago

資料庫版本：
  1. SYS@proc> select * from v$version where rownum=1;

  3. BANNER
  4. --------------------------------------------------------------------------------
  5. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

查詢現象：
  1. ZKM@proc> select count(*) from t;

  3.   COUNT(*)
  4. ----------
  5.          2

  7. Elapsed: 00:00:20.66

實驗過程：

  1. ZKM@proc> create table t(id int);

  3. Table created.

  5. ZKM@proc> insert into t values(1);

  7. 1 row created.

  9. ZKM@proc> c/1/2
  10.   1* insert into t values(2)
  11. ZKM@proc> /

  13. 1 row created.

  15. ZKM@proc> commit;

  17. Commit complete.

  19. ZKM@proc> select * from t;

  21.         ID
  22. ----------
  23.          1
  24.          2

  26. ZKM@proc> select bytes from dba_segments where segment_name='T' and owner='ZKM';

  28.      BYTES
  29. ----------
  30.      65536

  32. ZKM@proc> set timing on
  33. ZKM@proc> begin
  34.             for i in 1..100000 loop
  35.                 insert into t select * from t;
  36.             end loop;
  37.             commit;
  38.             end;
  39.             /
  40. ^C
  41. *
  42. ERROR at line 1:
  43. ORA-01013: user requested cancel of current operation
  44. ORA-06512: at line 3


  47. Elapsed: 00:18:15.08
  48. ZKM@proc> select count(*) from t;

  50.   COUNT(*)
  51. ----------
  52.          2

  54. Elapsed: 00:00:20.66
一開始還以為Ctrl+c中斷後，記錄應該是超過兩條的，沒想到中斷會回滾。
做了下10046，以下擷取一部分。

  1. ...
  2. WAIT #140653717427600: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=88856 tim=1509951777507463
  3. WAIT #140653717427600: nam='direct path read' ela= 191 file number=4 first dba=561 block cnt=15 obj#=88856 tim=1509951777507731
  4. WAIT #140653717427600: nam='direct path read' ela= 37 file number=4 first dba=577 block cnt=15 obj#=88856 tim=1509951777507869
  5. WAIT #140653717427600: nam='direct path read' ela= 38 file number=4 first dba=593 block cnt=15 obj#=88856 tim=1509951777507979
  6. WAIT #140653717427600: nam='direct path read' ela= 37 file number=4 first dba=609 block cnt=15 obj#=88856 tim=1509951777508083
  7. WAIT #140653717427600: nam='direct path read' ela= 43 file number=4 first dba=625 block cnt=15 obj#=88856 tim=1509951777508300
  8. WAIT #140653717427600: nam='direct path read' ela= 39 file number=4 first dba=641 block cnt=15 obj#=88856 tim=1509951777508435
  9. WAIT #140653717427600: nam='direct path read' ela= 126 file number=4 first dba=770 block cnt=14 obj#=88856 tim=1509951777508630
  10. WAIT #140653717427600: nam='direct path read' ela= 183 file number=4 first dba=784 block cnt=16 obj#=88856 tim=1509951777508886
  11. WAIT #140653717427600: nam='direct path read' ela= 287 file number=4 first dba=800 block cnt=16 obj#=88856 tim=1509951777509248
  12. WAIT #140653717427600: nam='direct path read' ela= 153 file number=4 first dba=816 block cnt=16 obj#=88856 tim=1509951777509512
  13. WAIT #140653717427600: nam='direct path read' ela= 145 file number=4 first dba=832 block cnt=16 obj#=88856 tim=1509951777509738
  14. WAIT #140653717427600: nam='direct path read' ela= 121 file number=4 first dba=848 block cnt=16 obj#=88856 tim=1509951777509935
  15. WAIT #140653717427600: nam='direct path read' ela= 41 file number=4 first dba=864 block cnt=16 obj#=88856 tim=1509951777510055
  16. WAIT #140653717427600: nam='direct path read' ela= 263 file number=4 first dba=880 block cnt=16 obj#=88856 tim=1509951777510389
  17. WAIT #140653717427600: nam='direct path read' ela= 38 file number=4 first dba=898 block cnt=14 obj#=88856 tim=1509951777510526
  18. WAIT #140653717427600: nam='direct path read' ela= 38 file number=4 first dba=912 block cnt=16 obj#=88856 tim=1509951777510637
  19. WAIT #140653717427600: nam='direct path read' ela= 38 file number=4 first dba=928 block cnt=16 obj#=88856 tim=1509951777510740
  20. WAIT #140653717427600: nam='direct path read' ela= 38 file number=4 first dba=944 block cnt=16 obj#=88856 tim=1509951777510849
  21. WAIT #140653717427600: nam='direct path read' ela= 39 file number=4 first dba=960 block cnt=16 obj#=88856 tim=1509951777510959
  22. WAIT #140653717427600: nam='direct path read' ela= 40 file number=4 first dba=976 block cnt=16 obj#=88856 tim=1509951777511070
  23. WAIT #140653717427600: nam='direct path read' ela= 44 file number=4 first dba=992 block cnt=16 obj#=88856 tim=1509951777511235
  24. WAIT #140653717427600: nam='direct path read' ela= 38 file number=4 first dba=1008 block cnt=16 obj#=88856 tim=1509951777511354
  25. WAIT #140653717427600: nam='direct path read' ela= 33 file number=4 first dba=1026 block cnt=14 obj#=88856 tim=1509951777511460
  26. WAIT #140653717427600: nam='direct path read' ela= 37 file number=4 first dba=1040 block cnt=16 obj#=88856 tim=1509951777511567
  27. WAIT #140653717427600: nam='direct path read' ela= 37 file number=4 first dba=1056 block cnt=16 obj#=88856 tim=1509951777511668
  28. WAIT #140653717427600: nam='direct path read' ela= 35 file number=4 first dba=1072 block cnt=16 obj#=88856 tim=1509951777511775
  29. ....
  1. ZKM@proc> select bytes,bytes/1024/1024 M from dba_segments where segment_name='T' and owner='ZKM';

  3.      BYTES          M
  4. ---------- ----------
  5. 1214251008       1158
看來原因是高水位線被提高了。
如何驗證高水位線被抬高了呢？可以查詢dba_extents檢視，但是該方法不清楚高水位線在哪裡。
以下是查詢高水位線過程。

  1. SYS@proc> select HEADER_FILE,HEADER_BLOCK from dba_segments where owner='ZKM' and segment_name='T'; --L3塊地址

  3. HEADER_FILE HEADER_BLOCK
  4. ----------- ------------
  5.           4          530

  7. Elapsed: 00:00:00.12
  8. SYS@proc> alter system dump datafile 4 block 530;

  10. System altered.

  12. Elapsed: 00:00:00.22

  14. SYS@proc> select value from v$diag_info where name like '%De%';

  16. VALUE
  17. ----------------------------------------------------------------------
  18. /u01/app/oracle/diag/rdbms/proc_stb/proc/trace/proc_ora_3974.trc

  20. Elapsed: 00:00:00.22
擷取部分proc_ora_3974.trc檔案內容。
  1. Extent Control Header
  2.   -----------------------------------------------------------------
  3.   Extent Header:: spare1: 0 spare2: 0 #extents: 202 #blocks: 148224
  4.                   last map 0x00000000 #maps: 0 offset: 2716
  5.       Highwater:: 0x01024580 ext#: 201 blk#: 8192 ext size: 8192
  6.   #blocks in seg. hdr
其中，Highwater即是高水位線。
  1. SYS@proc> select to_number('01024580','xxxxxxxx') from dual;

  3. TO_NUMBER('01024580','XXXXXXXX')
  4. --------------------------------
  5.                         16926080

  7. Elapsed: 00:00:00.02
  8. SYS@proc> select dbms_utility.data_block_address_file(16926080) from dual;

  10. DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16926080)
  11. ----------------------------------------------
  12.                                              4

  14. Elapsed: 00:00:00.27
  15. SYS@proc> select dbms_utility.data_block_address_block(16926080) from dual;

  17. DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16926080)
  18. -----------------------------------------------
  19.                                          148864

  21. Elapsed: 00:00:00.00
由此得到高水位線的地址是4號檔案的148864號塊。一般這裡可以猜測表最新的區最後塊的地址為4，148863。
驗證以下。
  1. SYS@proc> select * from (select EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where owner='ZKM' and SEGMENT_NAME='T' order by 1 desc) where rownum=1;

  3.  EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
  4. ---------- ---------- ---------- ----------
  5.        201          4     140672       8192
140672+8192-1=148863剛好是這樣。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30174570/viewspace-2146898/，如需轉載，請註明出處，否則將追究法律責任。

相關文章