There is some kind of wrong understanding, regarding what Oracle ASM really is. A lot of people thinks, that this is some kind of file system, provided by Oracle and that Oracle Database reads data "from ASM", while the truth is, that the database reads data directly from the physical raw device with assistance of the ASM instance.
This short example will demonstrate what I mean:
I have table "EMPLOYEES", located on "DATA2″ diskgroup which uses the "/dev/sdc2″ device:
SQL> ; 1 select t.table_name, d.file_name, dg.name, dk.path 2 from dba_tables t, dba_data_files d, v$asm_diskgroup dg, v$asm_disk dk 3 where t.tablespace_name=d.tablespace_name 4 and regexp_substr(d.file_name,'\w+',1,1)=dg.name 5 and dg.GROUP_NUMBER=dk.GROUP_NUMBER 6 and t.owner='HR' 7* and t.table_name='EMPLOYEES' SQL> / TABLE_NAME FILE_NAME NAME PATH ------------------------------ --------------------------------------------- -------- ---------- EMPLOYEES +DATA2/orcl/datafile/tbs_sdc.257.872537323 DATA2 /dev/sdc2
In the other session I logged in as a HR user and performed a SELECT statement on the EMPLOYEES table.
Now let’s locate the SPID of the server process, correlated with the HR session.
SQL> ; 1 select spid 2 from v$process p, v$session s 3 where p.addr=s.paddr 4* and s.username='HR' SQL> / SPID ------------------------ 6675
Now as "root" I’ll check the file descriptors, that were created by the server process.
[root@dupa ~]# cd /proc/6675/fd [root@dupa fd]# ls -al total 0 dr-x------. 2 root root 0 Feb 24 19:42 . dr-xr-xr-x. 8 oracle dba 0 Feb 24 19:42 .. lr-x------. 1 root root 64 Feb 24 19:43 0 -> /dev/null l-wx------. 1 root root 64 Feb 24 19:43 1 -> /dev/null l-wx------. 1 root root 64 Feb 24 19:43 12 -> pipe:[31069] l-wx------. 1 root root 64 Feb 24 19:43 2 -> /dev/null lrwx------. 1 root root 64 Feb 24 19:43 256 -> /dev/sdc2 lr-x------. 1 root root 64 Feb 24 19:43 3 -> /dev/null lr-x------. 1 root root 64 Feb 24 19:43 4 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb lr-x------. 1 root root 64 Feb 24 19:43 5 -> /proc/6675/fd lr-x------. 1 root root 64 Feb 24 19:43 6 -> /dev/zero lrwx------. 1 root root 64 Feb 24 19:43 7 -> /dev/oracleasm/iid/0000000000000003 lr-x------. 1 root root 64 Feb 24 19:43 9 -> pipe:[31068]
As we can see, the file descriptor "256″ is pointing directly to the "/dev/sdc2″ device.
And this is what we can see in the strace output for the physical reads of database blocks:
[root@dupa fd]# strace -p 6675 -o /tmp/employees_read.trc Process 6675 attached - interrupt to quit [root@dupa fd]# cat /tmp/employees_read.trc | grep "pread(256" pread(256, "#\242\0\0\202\0@\2\371\301\20\0\0\0\1\4\250\361\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 190857216) = 8192 pread(256, "\6\242\0\0\203\0@\2\371\301\20\0\0\0\1\4\2227\0\0\1\0\0\0002Z\1\0\371\301\20\0"..., 16384, 190865408) = 16384
The above example shows that function called "pread" has read 3 database blocks (8192+16384 => 3X 8k blocks). We can see those blocks in the buffer cache:
SQL> select b.file#, b.block#
2 from v$bh b, dba_objects o
3 where b.objd=o.data_object_id
4 and o.object_name='EMPLOYEES'
5 and b.status!='free';
FILE# BLOCK#
---------- ----------
9 130
9 132
9 131
We can use dd to extract those blocks from physical device, using the offset values, provided by the above trace.
As we can see in the second occurrence of the "pread" function, we can use the 8k block size, skip first 23299 blocks (190865408/8192=23299) and copy two blocks.
[root@dupa fd]# dd if=/dev/sdc2 of=/tmp/employees.blocks bs=8192 skip=23299 count=2 2+0 records in 2+0 records out 16384 bytes (16 kB) copied, 0.00639561 s, 2.6 MB/s
The contents of those blocks has data from my table:
[root@dupa fd]# xxd /tmp/employees.blocks | grep -v 0000 0001000: 6f74 6b65 7908 455a 4c4f 544b 4559 1230 otkey.EZLOTKEY.0 0001010: 3131 2e34 342e 3133 3434 2e34 3239 3031 11.44.1344.42901 0001020: 3807 786c 011d 0101 0106 5341 5f4d 414e 8.xl......SA_MAN 0001030: 03c3 0206 02c0 1502 c202 02c1 512c 000b ............Q,.. 0001040: 03c2 0231 0647 6572 616c 6409 4361 6d62 ...1.Gerald.Camb 0001050: 7261 756c 7408 4743 414d 4252 4155 1230 rault.GCAMBRAU.0 0001060: 3131 2e34 342e 3133 3434 2e36 3139 3236 11.44.1344.61926 0001070: 3807 786b 0a0f 0101 0106 5341 5f4d 414e 8.xk......SA_MAN 0001080: 03c3 020b 02c0 1f02 c202 02c1 512c 000b ............Q,.. 0001090: 03c2 0230 0741 6c62 6572 746f 0945 7272 ...0.Alberto.Err 00010a0: 617a 7572 697a 0841 4552 5241 5a55 5212 azuriz.AERRAZUR. 00010b0: 3031 312e 3434 2e31 3334 342e 3432 3932 011.44.1344.4292 00010c0: 3738 0778 6903 0a01 0101 0653 415f 4d41 78.xi......SA_MA 00010d0: 4e03 c302 1502 c01f 02c2 0202 c151 2c00 N............Q,. 00010e0: 0b03 c202 2f05 4b61 7265 6e08 5061 7274 ..../.Karen.Part 00010f0: 6e65 7273 084b 5041 5254 4e45 5212 3031 ners.KPARTNER.01 0001100: 312e 3434 2e31 3334 342e 3436 3732 3638 1.44.1344.467268 0001110: 0778 6901 0501 0101 0653 415f 4d41 4e03 .xi......SA_MAN. 0001120: c302 2402 c01f 02c2 0202 c151 2c00 0b03 ..$........Q,... 0001130: c202 2e04 4a6f 686e 0752 7573 7365 6c6c ....John.Russell 0001140: 074a 5255 5353 454c 1230 3131 2e34 342e .JRUSSEL.011.44. 0001150: 3133 3434 2e34 3239 3236 3807 7868 0a01 1344.429268.xh.. 0001160: 0101 0106 5341 5f4d 414e 03c3 0229 02c0 ....SA_MAN...).. 0001170: 2902 c202 02c1 512c 000b 03c2 022d 0550 ).....Q,.....-.P 0001180: 6574 6572 0656 6172 6761 7307 5056 4152 eter.Vargas.PVAR 0001190: 4741 530c 3635 302e 3132 312e 3230 3034 GAS.650.121.2004 00011a0: 0778 6a07 0901 0101 0853 545f 434c 4552 .xj......ST_CLER 00011b0: 4b02 c21a ff03 c202 1902 c133 2c00 0b03 K..........3,... 00011c0: c202 2c07 5261 6e64 616c 6c05 4d61 746f ..,.Randall.Mato 00011d0: 7306 524d 4154 4f53 0c36 3530 2e31 3231 s.RMATOS.650.121 00011e0: 2e32 3837 3407 786a 030f 0101 0108 5354 .2874.xj......ST 00011f0: 5f43 4c45 524b 02c2 1bff 03c2 0219 02c1 _CLERK.......... 0001200: 332c 000b 03c2 022b 0643 7572 7469 7306 3,.....+.Curtis. 0001210: 4461 7669 6573 0743 4441 5649 4553 0c36 Davies.CDAVIES.6 0001220: 3530 2e31 3231 2e32 3939 3407 7869 011d 50.121.2994.xi.. 0001230: 0101 0108 5354 5f43 4c45 524b 02c2 20ff ....ST_CLERK.. . (......output removed for clarity......)