Re: RAC

From: Jared Still <jkstill_at_gmail.com>
Date: Thu, 15 Mar 2012 11:58:04 -0700
Message-ID: <CAORjz=O5cC9sCk9h0Ph25XfuLRWE=yj4Zu6Ct6sgsvweh5BLsQ_at_mail.gmail.com>

On Thu, Mar 15, 2012 at 11:03 AM, Paul Harrison <cure_at_austin.rr.com> wrote:
> Hi All,
>
> RAC.. In database instance... I query for some data and the database
> instance has to communicate with the asm instance in order to get the path
> to the block within the asm disk group. Does the asm instance return all
> the
> pointers to the blocks one time(one round trip) or round trip per block?

What in interesting question.

I have trace oracle reading file system datafiles many times, but had never done so for ASM.

The following was done on OEL 5.5 and Oracle 10.2.0.5

First I logged to the database, and looked up my OS pid - sorry, no screen shot for that.

The pid was 28702, so as root I then looked to see what files were open:

[root] ls -l /proc/28702/fd

total 0

dr-x------ 2 root   root    0 Mar 15 11:38 .
dr-xr-xr-x 5 oracle vboxsf  0 Mar 15 11:38 ..
lrwx------ 1 root   root   64 Mar 15 11:39 0 -> /dev/pts/3
lrwx------ 1 root   root   64 Mar 15 11:39 1 -> /dev/pts/3
lrwx------ 1 root   root   64 Mar 15 11:39 10 -> /dev/oracleasm/disks/VOL1
l-wx------ 1 root   root   64 Mar 15 11:39 11 -> pipe:[49869]
lrwx------ 1 root   root   64 Mar 15 11:39 2 -> /dev/pts/3
lr-x------ 1 root   root   64 Mar 15 11:39 3 -> /dev/null
lr-x------ 1 root   root   64 Mar 15 11:39 4 -> /dev/null
lr-x------ 1 root   root   64 Mar 15 11:39 5 -> /dev/null
lr-x------ 1 root   root   64 Mar 15 11:39 6 -> /dev/null
lr-x------ 1 root   root   64 Mar 15 11:39 7 -> /dev/zero
lr-x------ 1 root   root   64 Mar 15 11:39 8 -> pipe:[49868]
lr-x------ 1 root   root   64 Mar 15 11:39 9 ->

/u01/app/oracle/product/10.2.0/rdbms/mesg/oraus.msb

Note that the VOL1 of the asm disks is opened.

Start a trace (as root ) on the PID

[root] strace -p 28702 -f -o 28702.trc

Now in another SQLPlus session as sysdba:

SQL> alter system flush buffer_cache;

Now in the user SQLPlus session

SQL> select * from dba_objects.

This takes a few moments.

When the query finishes, I exit sqlplus, and the trace completes

We know that the file descriptor for the ASM volume was 10.

 > grep '(10' 28702.trc|wc

    304 2140 35637

All of the reads were 8k single block reads

> grep '(10' 28702.trc|head -5

28702 pread64(10,
"\20\242\0\0Y\0_at_\0\201C\6\0\0\0\1\0042\347\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 54206464) = 8192
28702 pread64(10,
"\6\242\0\0]\0_at_\0&\267*\0\0\0\1\6\16\324\0\0\1\0\0\0\n\0\0\0#\267*\0"..., 8192, 54239232) = 8192
28702 pread64(10,
"\20\242\0\0y\0_at_\0:\235.\0\0\0\1\4\\\33\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 54468608) = 8192
28702 pread64(10,
"\6\242\0\0\362\0_at_\0c[\5\0\0\0\1\4=\226\0\0\2\0\0\0'\0\0\0c[\5\0"..., 8192, 55459840) = 8192
28702 pread64(10,
"\6\242\0\0\363\0_at_\0\3523\2\0\0\0\1\4\3\6\0\0\2\0\0\0'\0\0\0\3523\2\0"..., 8192, 55468032) = 8192

So reading from ASM is similar to reading from OS based datafiles.

The oracle shadow process reads the ASM volume as it would an OS datafile.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Oracle Blog: http://jkstill.blogspot.com Home Page: http://jaredstill.com

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Mar 15 2012 - 13:58:04 CDT