Re: RAC

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Sat, 17 Mar 2012 09:41:38 -0500
Message-ID: <CAA2DszwTYtqqEdNXnOKzit351RFVha_67RjMiW45Vej3Q4oNPg_at_mail.gmail.com>

You have gotten good information from others already, I am little late :)

1. Truss of DBWR process associated with RDBMS instance will show that files are opened to the devices and writes performed, i.e. ASM is not involved for direct I/O to the devices: * *

Write calls to file pointer 262: (truss output of a DBWR)

/1: kaio(AIOWRITE, 262 , 0x6DD3C000, 8192, 0xFC17E6380F4E4000) = 0

...

/1: kaio(AIOWRITE, 262 , 0x7DF3F000, 49152, 0xFC17D7080BD8A000) = 0

          File pointer 262 is a block device (pfiles output). Meaning I/O is performed directly to the devices.(This is iSCSI environment.)

 262: S_IFCHR mode:0755 dev:291,0 ino:15728902 uid:601 gid:503 rdev:30,129

      O_RDWR|O_NONBLOCK|O_DSYNC|O_LARGEFILE FD_CLOEXEC       /devices/iscsi/disk_at_0000iqm.demo.volumes-san0001,1:b,raw

2. DB stores the extent map in the shared pool: v$sgastat shows the extent pointer array in the RDBMS. This array is retrieved from ASM instance. Of course this area will be lot bigger in a highly active , large database. I have seen huge memory allocation for this extent pointer array in a VLDB database ( 10g). Following Output is from my laptop database at startup.

BTW, waits for extent pointer array retrieval was not well-instrumented in 10g, but in 11g, events starting with CSS% are *mostly* related to this retrieval operation.

 select * from gv$sgastat where name like '%ASM extent%’;

   INST_ID POOL         NAME                            BYTES

---------- ------------ -------------------------- ----------

         1 shared pool  ASM extent pointer array       171824

         2 shared pool  ASM extent pointer array       171824

For example, a recent performance problem with a client due to a bug in ASM lead to hung retrieval with CSS% wait events.

                                                                 Avg
                                            %Time  Total Wait    wait
Waits
Event                                 Waits -outs    Time (s)    (ms)

 /txn

---------------------------- -------------- ----- ----------- -------

---

..

CSS operation: query                  6,706   N/A       6,396    1046

0.1

3. Changes in extent maps (such as file extension, adding files etc) will lead to database querying the extent map. So, to create a new file or extend an existing file, database will request ASM to allocate space; and retrieve extent map changes. Having auto extent on with smaller auto extent size can increase this communication unnecessarily.

4. In RAC, *an* ASM instance will act as a master of an operation. For example, if two processes running in two instances request a datafile to be extended, just one ASM instance will be performing the operation protected by ASM level locks and corresponding GES locks (RAC). Along the same lines, rebalance operation of a disk group will be solely performed by one instance (locking is at disk group level). So, if you have multiple disk groups that needs rebalancing (and you need to rebalance quickly ), you may be better off triggering rebalance operation manually at disk group level from different instances. Oh, I digress from the discussion.

5. In essence, database instance makes a connection to ASM instance and act as a client. Database asmb process sleeps in *ASM background timer *wait event. If ASM crashes, then that asmb process will die taking down the database with it.

HTH :) Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com - Specialists in Performance, RAC and EBS
Blog: http://orainternals.wordpress.com
OakTable member http://www.oaktable.com and Oracle ACE Director

Co-author of the books: Expert Oracle
Practices<http://tinyurl.com/book-expert-oracle-practices/>, Pro Oracle SQL, Expert PL/SQL
Practices<http://tinyurl.com/book-expert-plsql-practices>

Join me for next RAC training in March
2012<http://www.orainternals.com/services/training/advanced-rac-training/>:

<http://tinyurl.com/book-expert-plsql-practices>

On Fri, Mar 16, 2012 at 12:27 PM, GG <grzegorzof_at_interia.pl> wrote:

> W dniu 2012-03-15 21:27, Paul Harrison pisze:
> > Does the RDBMS instance request the extents per query or does it store
> then
> > for later use(other queries) ?
> >
> They are stored (cached) in shared pool AFAIK.
> Regards
> GregG
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Sat Mar 17 2012 - 09:41:38 CDT