偶然的實驗,查詢兩條記錄的全表很久,10046真好用...
作業系統版本:
資料庫版本:
查詢現象:
實驗過程:
一開始還以為Ctrl+c中斷後,記錄應該是超過兩條的,沒想到中斷會回滾。
做了下10046,以下擷取一部分。
看來原因是高水位線被提高了。
如何驗證高水位線被抬高了呢?可以查詢dba_extents檢視,但是該方法不清楚高水位線在哪裡。
以下是查詢高水位線過程。
擷取部分proc_ora_3974.trc檔案內容。
其中,Highwater即是高水位線。
由此得到高水位線的地址是4號檔案的148864號塊。一般這裡可以猜測表最新的區最後塊的地址為4,148863。
驗證以下。
140672+8192-1=148863剛好是這樣。
-
[oracle@oracle trace]$ uname -a
-
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
-
[oracle@oracle trace]$ lsb_release -a
-
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
-
Distributor ID: RedHatEnterpriseServer
-
Description: Red Hat Enterprise Linux Server release 6.5 (Santiago)
-
Release: 6.5
- Codename: Santiago
資料庫版本:
-
SYS@proc> select * from v$version where rownum=1;
-
-
BANNER
-
--------------------------------------------------------------------------------
- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
查詢現象:
-
ZKM@proc> select count(*) from t;
-
-
COUNT(*)
-
----------
-
2
-
- Elapsed: 00:00:20.66
實驗過程:
-
ZKM@proc> create table t(id int);
-
-
Table created.
-
-
ZKM@proc> insert into t values(1);
-
-
1 row created.
-
-
ZKM@proc> c/1/2
-
1* insert into t values(2)
-
ZKM@proc> /
-
-
1 row created.
-
-
ZKM@proc> commit;
-
-
Commit complete.
-
-
ZKM@proc> select * from t;
-
-
ID
-
----------
-
1
-
2
-
-
ZKM@proc> select bytes from dba_segments where segment_name='T' and owner='ZKM';
-
-
BYTES
-
----------
- 65536
-
-
ZKM@proc> set timing on
-
ZKM@proc> begin
-
for i in 1..100000 loop
-
insert into t select * from t;
-
end loop;
- commit;
-
end;
-
/
-
^C
-
*
-
ERROR at line 1:
-
ORA-01013: user requested cancel of current operation
-
ORA-06512: at line 3
-
-
- Elapsed: 00:18:15.08
- ZKM@proc> select count(*) from t;
-
- COUNT(*)
- ----------
- 2
-
- Elapsed: 00:00:20.66
做了下10046,以下擷取一部分。
- ...
-
WAIT #140653717427600: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=88856 tim=1509951777507463
-
WAIT #140653717427600: nam='direct path read' ela= 191 file number=4 first dba=561 block cnt=15 obj#=88856 tim=1509951777507731
-
WAIT #140653717427600: nam='direct path read' ela= 37 file number=4 first dba=577 block cnt=15 obj#=88856 tim=1509951777507869
-
WAIT #140653717427600: nam='direct path read' ela= 38 file number=4 first dba=593 block cnt=15 obj#=88856 tim=1509951777507979
-
WAIT #140653717427600: nam='direct path read' ela= 37 file number=4 first dba=609 block cnt=15 obj#=88856 tim=1509951777508083
-
WAIT #140653717427600: nam='direct path read' ela= 43 file number=4 first dba=625 block cnt=15 obj#=88856 tim=1509951777508300
-
WAIT #140653717427600: nam='direct path read' ela= 39 file number=4 first dba=641 block cnt=15 obj#=88856 tim=1509951777508435
-
WAIT #140653717427600: nam='direct path read' ela= 126 file number=4 first dba=770 block cnt=14 obj#=88856 tim=1509951777508630
-
WAIT #140653717427600: nam='direct path read' ela= 183 file number=4 first dba=784 block cnt=16 obj#=88856 tim=1509951777508886
-
WAIT #140653717427600: nam='direct path read' ela= 287 file number=4 first dba=800 block cnt=16 obj#=88856 tim=1509951777509248
-
WAIT #140653717427600: nam='direct path read' ela= 153 file number=4 first dba=816 block cnt=16 obj#=88856 tim=1509951777509512
-
WAIT #140653717427600: nam='direct path read' ela= 145 file number=4 first dba=832 block cnt=16 obj#=88856 tim=1509951777509738
-
WAIT #140653717427600: nam='direct path read' ela= 121 file number=4 first dba=848 block cnt=16 obj#=88856 tim=1509951777509935
-
WAIT #140653717427600: nam='direct path read' ela= 41 file number=4 first dba=864 block cnt=16 obj#=88856 tim=1509951777510055
-
WAIT #140653717427600: nam='direct path read' ela= 263 file number=4 first dba=880 block cnt=16 obj#=88856 tim=1509951777510389
-
WAIT #140653717427600: nam='direct path read' ela= 38 file number=4 first dba=898 block cnt=14 obj#=88856 tim=1509951777510526
-
WAIT #140653717427600: nam='direct path read' ela= 38 file number=4 first dba=912 block cnt=16 obj#=88856 tim=1509951777510637
-
WAIT #140653717427600: nam='direct path read' ela= 38 file number=4 first dba=928 block cnt=16 obj#=88856 tim=1509951777510740
-
WAIT #140653717427600: nam='direct path read' ela= 38 file number=4 first dba=944 block cnt=16 obj#=88856 tim=1509951777510849
-
WAIT #140653717427600: nam='direct path read' ela= 39 file number=4 first dba=960 block cnt=16 obj#=88856 tim=1509951777510959
-
WAIT #140653717427600: nam='direct path read' ela= 40 file number=4 first dba=976 block cnt=16 obj#=88856 tim=1509951777511070
-
WAIT #140653717427600: nam='direct path read' ela= 44 file number=4 first dba=992 block cnt=16 obj#=88856 tim=1509951777511235
-
WAIT #140653717427600: nam='direct path read' ela= 38 file number=4 first dba=1008 block cnt=16 obj#=88856 tim=1509951777511354
-
WAIT #140653717427600: nam='direct path read' ela= 33 file number=4 first dba=1026 block cnt=14 obj#=88856 tim=1509951777511460
-
WAIT #140653717427600: nam='direct path read' ela= 37 file number=4 first dba=1040 block cnt=16 obj#=88856 tim=1509951777511567
-
WAIT #140653717427600: nam='direct path read' ela= 37 file number=4 first dba=1056 block cnt=16 obj#=88856 tim=1509951777511668
- WAIT #140653717427600: nam='direct path read' ela= 35 file number=4 first dba=1072 block cnt=16 obj#=88856 tim=1509951777511775
- ....
-
ZKM@proc> select bytes,bytes/1024/1024 M from dba_segments where segment_name='T' and owner='ZKM';
-
-
BYTES M
-
---------- ----------
- 1214251008 1158
如何驗證高水位線被抬高了呢?可以查詢dba_extents檢視,但是該方法不清楚高水位線在哪裡。
以下是查詢高水位線過程。
-
SYS@proc> select HEADER_FILE,HEADER_BLOCK from dba_segments where owner='ZKM' and segment_name='T'; --L3塊地址
-
-
HEADER_FILE HEADER_BLOCK
-
----------- ------------
-
4 530
-
-
Elapsed: 00:00:00.12
-
SYS@proc> alter system dump datafile 4 block 530;
-
-
System altered.
-
-
Elapsed: 00:00:00.22
-
-
SYS@proc> select value from v$diag_info where name like '%De%';
-
-
VALUE
-
----------------------------------------------------------------------
-
/u01/app/oracle/diag/rdbms/proc_stb/proc/trace/proc_ora_3974.trc
-
- Elapsed: 00:00:00.22
-
Extent Control Header
-
-----------------------------------------------------------------
-
Extent Header:: spare1: 0 spare2: 0 #extents: 202 #blocks: 148224
-
last map 0x00000000 #maps: 0 offset: 2716
-
Highwater:: 0x01024580 ext#: 201 blk#: 8192 ext size: 8192
- #blocks in seg. hdr
-
SYS@proc> select to_number('01024580','xxxxxxxx') from dual;
-
-
TO_NUMBER('01024580','XXXXXXXX')
-
--------------------------------
-
16926080
-
-
Elapsed: 00:00:00.02
-
SYS@proc> select dbms_utility.data_block_address_file(16926080) from dual;
-
-
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16926080)
-
----------------------------------------------
-
4
-
-
Elapsed: 00:00:00.27
-
SYS@proc> select dbms_utility.data_block_address_block(16926080) from dual;
-
-
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16926080)
-
-----------------------------------------------
-
148864
-
- Elapsed: 00:00:00.00
驗證以下。
-
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;
-
-
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
-
---------- ---------- ---------- ----------
- 201 4 140672 8192
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30174570/viewspace-2146898/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 查詢鎖表記錄
- 兩種閃回查詢的使用實驗
- MySQL 查詢所有表中的記錄數MySql
- [MySQL] - 聯表查詢,查詢一個不在另一個表的記錄MySql
- 使用JdbcTemp查詢少了一條記錄JDBC
- MongoDB隨機查詢返回一條或N條記錄的方法MongoDB隨機
- 真實記錄我的學習linux的經驗Linux
- SQL 單表多條記錄分組查詢分頁程式碼SQL
- 查詢全表掃描的sqlSQL
- oracle實驗記錄 (全表掃描COST計算方法)Oracle
- sqlserver查詢一個庫所有表的記錄數SQLServer
- 查詢 SQL SERVER 所有表記錄數SQLServer
- PHP查詢資料庫中滿足條件的記錄條數(二種實現方法)PHP資料庫
- 查詢條件和條數,先查詢兩條免費的,後面為vip
- 查詢多張表記錄數的儲存過程儲存過程
- 查詢刪除表中重複記錄
- oracle實驗記錄(分割槽全表掃描(全區掃描) FTS 時候的成本計算)Oracle
- sqlserver查詢最接近的記錄SQLServer
- Mysql-基本練習(09-刪除單表記錄、查詢指定列資料、列的別名、簡單單表條件查詢、簡單分組查詢)MySql
- 雙主鍵,,查詢不在另一個表中的記錄
- 使用awk查詢不滿足sqlloader載入條件的記錄SQL
- 對查詢資料庫中第M到N條記錄的思考資料庫
- SSH:hiberate實現資料的查詢(單查詢和全查詢)
- ORACLE全表掃描查詢Oracle
- Oracle查詢資料庫中所有表和分割槽表的記錄數Oracle資料庫
- 查詢某條記錄存在哪個資料塊中
- 報表查詢條件的 N 種使用方式
- 排除表和query查詢條件的expdp、impdp
- Linq兩個from查詢條件
- 59 條搞笑但真實的程式設計語錄程式設計
- 查詢匯入記錄的進度
- Oracle查詢庫中記錄數大於2千萬的所有表Oracle
- oracle 10046與select table查詢表系列(一)Oracle
- mongodb索引--1億條記錄的查詢從55.7秒到毫秒級別MongoDB索引
- [Mysql 查詢語句]——查詢指定記錄MySql
- efcore 跨表查詢,實現一個介面內查詢兩個不同資料庫裡各自的表資料資料庫
- 查詢全表掃描語句
- 處理表重複記錄(查詢和刪除)