前天使用sysinternals的procmon.exe工具幫助別人定位一個軟體問題。就想是否linux下使用strace定位一些oracle的效能問題，自己今天做了一個嘗試。

1. 確定會話spid=9130,執行如下:

12:04:30 SQL> select /*+ full(a) */ count(*) from THREEMEASURERECCONTACCESS a;
COUNT(*)
----------
14781771

--這個表很大，接近1G。

2.在另外一個終端開啟：

# strace -o test.txt -p 9130

# ls -l /proc/9130/fd
total 11
lrwx------ 1 oracle oinstall 64 Dec 27 12:09 0 -> /dev/null
lrwx------ 1 oracle oinstall 64 Dec 27 12:09 1 -> /dev/null
lrwx------ 1 oracle oinstall 64 Dec 27 12:09 14 -> socket:[5675157]
lrwx------ 1 oracle oinstall 64 Dec 27 12:09 2 -> /dev/null
lr-x------ 1 oracle oinstall 64 Dec 27 12:09 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 Dec 27 12:09 4 -> /dev/null
lr-x------ 1 oracle oinstall 64 Dec 27 12:09 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 Dec 27 12:09 6 -> /dev/null
lr-x------ 1 oracle oinstall 64 Dec 27 12:09 7 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Dec 27 12:09 8 -> /u01/app/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 Dec 27 12:09 9 -> /u02/app/oracle/oradata/ticare/icare_emr.dbf

# grep -i lseek test.txt
lseek(9, 30785536, SEEK_SET)            = 30785536
lseek(9, 32710656, SEEK_SET)            = 32710656
lseek(9, 41099264, SEEK_SET)            = 41099264
lseek(9, 49487872, SEEK_SET)            = 49487872
lseek(9, 57876480, SEEK_SET)            = 57876480
lseek(9, 66265088, SEEK_SET)            = 66265088
lseek(9, 74653696, SEEK_SET)            = 74653696
lseek(9, 82944000, SEEK_SET)            = 82944000
lseek(9, 91332608, SEEK_SET)            = 91332608
lseek(9, 99721216, SEEK_SET)            = 99721216
...............
lseek(9, 1027710976, SEEK_SET)          = 1027710976
lseek(9, 1036099584, SEEK_SET)          = 1036099584
lseek(9, 1044258816, SEEK_SET)          = 1044258816
lseek(9, 1044488192, SEEK_SET)          = 1044488192
lseek(9, 1052876800, SEEK_SET)          = 1052876800
lseek(9, 1061265408, SEEK_SET)          = 1061265408

lseek(9, 30785536, SEEK_SET)            = 30785536
readv(9, [{"\6\242\0\0\256\16\300\1W\251\316\0\0\0\1\4S\322\0\0\1\0"..., 8192}, {"\6\242\0\0\257\16\300\1W\251\316\0\0\0\1\4.h\0\0\1\0\0"..., 8192}, {"\6\242\0\0\260\16\300\1W\2..."
gettimeofday({1324958959, 727510}, NULL) = 0

從以上資訊可以確定fd=9,表示的資料檔案是/u02/app/oracle/oradata/ticare/icare_emr.dbf。lseek我的理解就是就是尋道定位的意思，readv應該表示讀取，無法讀懂裡面的資訊，
不過從lseek還是可以定位使用那個物件的。

3.查詢檢視：（哈哈，還是要使用oracle的檢視）

SELECT *
FROM dba_extents
WHERE tablespace_name = 'ICARE_EMR' AND 30785536 / 8192 BETWEEN block_id AND block_id + blocks - 1;

OWNER      SEGMENT_NAME                   PARTITION_ SEGMENT_TYPE       TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
---------- ------------------------------ ---------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
ICARE      THREEMEASURERECCONTACCESS                 TABLE              ICARE_EMR                               0          7       2953    8388608       1024            7

SELECT *
FROM dba_extents
WHERE tablespace_name = 'ICARE_EMR' AND 1052876800 / 8192 BETWEEN block_id AND block_id + blocks - 1;

OWNER      SEGMENT_NAME                   PARTITION_ SEGMENT_TYPE       TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
---------- ------------------------------ ---------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
ICARE      THREEMEASURERECCONTACCESS                 TABLE              ICARE_EMR                             121          7     128521    8388608       1024            7

可以確定基本一致。

[20111227]strace 定位問題一例.txt

相關文章