ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 偶然的实验，查询两条记录的全表很久，10046真好用...

偶然的实验，查询两条记录的全表很久，10046真好用...

原创 Oracle 作者：PiscesCanon 时间：2017-11-06 17:37:16 0 删除 编辑
操作系统版本：
  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博客 ” ，链接：https://blog.itpub.net/30174570/viewspace-2146898/ ，如需转载，请注明出处，否则将追究法律责任。

1
0
上一篇： Oracle不正规操作导致drop表空间drop用户报错ora38301(记录，未解决)
下一篇： 【Oracle】查询条件中出现to_number报错，用Hint之后改变运行结果
请登录后发表评论 登录
全部评论
PiscesCanon

注册时间：2015-03-23

  • 博文量
    29
  • 访问量
    57487

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593