Home » Infrastructure » Linux » SAN i/o in RAC (oracle database RAC 11.2.0..3)
SAN i/o in RAC [message #573987] Fri, 04 January 2013 13:42 Go to next message
saikat891
Messages: 11
Registered: April 2012
Location: MUMBAI
Junior Member

Hi,

We have 11.2.0.3 RAC on Oracle Virtual Box,OEL 5.8. Storage is ASM on EMC SAN.

Even when the database is not connected to application and thus open but idle and none is working on it, and all other virtual machines are powered off, we are still getting a continuous i/o in SAN.Since I am new to RAC setup I need to know the few things...


1.Is it normal behaviour?
2.If yes, what is the reason of this i/o? Is it due to continuous ping on voting disk?..write in OCR? automatic backup of OCR?

Please find the attached screenshot.If anyone kindly explain the reason, I will be grateful.

Regards,
Saikat
Re: SAN i/o in RAC [message #573989 is a reply to message #573987] Fri, 04 January 2013 14:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Why are you focused on I/O activity; as opposed to CPU or Network activity?
Why does system continuously burn CPU cycles? What is causing all this activity?
Re: SAN i/o in RAC [message #573990 is a reply to message #573987] Fri, 04 January 2013 14:34 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Can you confirm your environment? Do you mean that your VirtualBox VMs are running from VDI files on file systems mounted from the EMC SAN?
Re: SAN i/o in RAC [message #573994 is a reply to message #573990] Fri, 04 January 2013 21:09 Go to previous messageGo to next message
saikat891
Messages: 11
Registered: April 2012
Location: MUMBAI
Junior Member

Quote:
Can you confirm your environment? Do you mean that your VirtualBox VMs are running from VDI files on file systems mounted from the EMC SAN?



Yes.exactly.
Re: SAN i/o in RAC [message #573995 is a reply to message #573989] Fri, 04 January 2013 21:18 Go to previous messageGo to next message
saikat891
Messages: 11
Registered: April 2012
Location: MUMBAI
Junior Member

Quote:
Why are you focused on I/O activity; as opposed to CPU or Network activity?
Why does system continuously burn CPU cycles? What is causing all this activity?


yes..I am also unware why ntw and cpu too showing this behavior. If anyone from their experience/test setup kindly let me know their output and explain the reason I will be grateful.


Regards,
Saikat

Re: SAN i/o in RAC [message #574009 is a reply to message #573994] Sat, 05 January 2013 01:32 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I use VirtualBox all the time, I'm running a RAC right now on my PC: two VMs for the RAC nodes, and third VM as the DHCP/DNS/storage server. There is always continuous i/o and CPU usage. But I would never use VB for anything other than test/demo/development environments, certainly not for any application that required EMC storage. So sorry, I can't advise further.
Re: SAN i/o in RAC [message #574046 is a reply to message #574009] Sun, 06 January 2013 02:27 Go to previous messageGo to next message
saikat891
Messages: 11
Registered: April 2012
Location: MUMBAI
Junior Member

Hi John,

Thanks a lot for your reply.

Actually this is not a production server. This is only for testing, learning , practice and building competency on oracle products. We work on various products apart from oracle.That's why we used virtual box.

Since our budget could afford EMC storage we used it to simulate near real-time environment.


Regards,
Saikat
Re: SAN i/o in RAC [message #579662 is a reply to message #574046] Wed, 13 March 2013 18:08 Go to previous message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
You can look at the historic physical I/O saved within the hourly snapshots (if statistics_level=typical) with the following sql:
ENDOCP1P > @physical

DATE                 PHYSICAL_READS_LAST_HOUR OBJECT_NAME
-------------------- ------------------------ --------------------
2013-11-03 Monday                        6054 D_1F000D5D80000910
2013-11-03 Monday                       26179 D_1F000D5D80000005
2013-11-03 Monday                       40332 D_1F000D5D80000901
2013-11-03 Monday                       46808 DM_SYSOBJECT_S_COMP2
2013-11-03 Monday                       64930 DM_RELATION_S
2013-11-03 Monday                       78228 NFL_CONTENT_R_COMP1
2013-11-03 Monday                       79647 DM_SYSOBJECT_S_COMP1
2013-11-03 Monday                      135421 DMR_CONTENT_S
2013-11-03 Monday                      165087 NFL_CONTENT_R
2013-11-03 Monday                      243480 DM_SYSOBJECT_R_COMP1
2013-11-03 Monday                      548052 D_1F000D5D8000010A
2013-11-03 Monday                     1475076 DM_SYSOBJECT_R_COMP2

ENDOCP1P > list
  1  select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DATE",
  2  sum(b.PHYSICAL_READS_DELTA) PHYSICAL_READS_LAST_HOUR,
  3  a.object_name
  4  from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
  5  where b.SNAP_ID =(select max(SNAP_ID) from sys.wRM$_SNAPSHOT)
  6  and a.object_id=b.OBJ#
  7  and b.PHYSICAL_READS_DELTA>0
  8  and c.instance_number=(select instance_number from v$instance)
  9  and c.snap_id=b.snap_id
 10  group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day'),a.object_name
 11* order by 2

If you are not saving hourly shapshots, you can see what is being written by taking a backup of gv$segment_statistics and comparing it with gv$segment_statistics.
ECSCDAP1P > @io605

INSTANCE  IO_PER_MINUTE STATISTIC_NAME          OBJECT_NAME
--------- ------------- ----------------------- -------------------------------------
CSCDAP1              12 physical reads          SYS.IDL_UB2$.
CSCDAP1              12 physical reads          SYS.IDL_CHAR$.
CSCDAP1              12 physical read requests  SYS.IDL_UB2$.
CSCDAP1              12 physical read requests  SYS.IDL_CHAR$.
CSCDAP1              84 physical reads          SYS.I_OBJAUTH2.
CSCDAP1              84 physical read requests  SYS.I_OBJAUTH2.
CSCDAP3             240 physical read requests  CDA_SHARED.SYS_LOB0000077712C00008$$.
CSCDAP3             252 physical reads direct   CDA_SHARED.SYS_LOB0000077712C00008$$.
CSCDAP3             252 physical reads          CDA_SHARED.SYS_LOB0000077712C00008$$.
CSCDAP3             276 physical reads          CDA_SHARED.SYS_LOB0000077712C00004$$.
CSCDAP3             276 physical read requests  CDA_SHARED.SYS_LOB0000077712C00004$$.
CSCDAP3             276 physical reads direct   CDA_SHARED.SYS_LOB0000077712C00004$$.
CSCDAP4             288 physical read requests  CDA_SHARED.SYS_LOB0000077712C00008$$.
CSCDAP1             288 physical reads direct   CDA_SHARED.SYS_LOB0000077712C00004$$.
CSCDAP1             288 physical reads          CDA_SHARED.SYS_LOB0000077712C00004$$.
CSCDAP1             288 physical read requests  CDA_SHARED.SYS_LOB0000077712C00004$$.
CSCDAP1             300 physical reads direct   CDA_SHARED.SYS_LOB0000077712C00008$$.
CSCDAP1             300 physical reads          CDA_SHARED.SYS_LOB0000077712C00008$$.
CSCDAP1             300 physical read requests  CDA_SHARED.SYS_LOB0000077712C00008$$.
CSCDAP4             312 physical reads direct   CDA_SHARED.SYS_LOB0000077712C00008$$.
CSCDAP4             312 physical reads          CDA_SHARED.SYS_LOB0000077712C00008$$.
CSCDAP4             360 physical reads direct   CDA_SHARED.SYS_LOB0000077712C00004$$.
CSCDAP4             360 physical reads          CDA_SHARED.SYS_LOB0000077712C00004$$.
CSCDAP4             360 physical read requests  CDA_SHARED.SYS_LOB0000077712C00004$$.
CSCDAP2             384 physical read requests  CDA_SHARED.SYS_LOB0000077712C00008$$.
CSCDAP2             396 physical reads          CDA_SHARED.SYS_LOB0000077712C00008$$.
CSCDAP2             396 physical reads direct   CDA_SHARED.SYS_LOB0000077712C00008$$.
CSCDAP2             480 physical read requests  CDA_SHARED.SYS_LOB0000077712C00004$$.
CSCDAP2             492 physical reads          CDA_SHARED.SYS_LOB0000077712C00004$$.
CSCDAP2             492 physical reads direct   CDA_SHARED.SYS_LOB0000077712C00004$$.
          -------------
sum                8184

io605.sql follows:
set termout off
set lines 160
break on report
col instance for a9
compute sum of io_per_minute break on report
drop table gv$segment_statistics1;
drop table gv$segment_statistics2;
column statistic_name format a23
create table gv$segment_statistics1 as select * from gv$segment_statistics 
where upper(statistic_name) like '%PHYSICAL%' and owner not like 'E%';
execute dbms_lock.sleep(5);
create table gv$segment_statistics2 as select * from gv$segment_statistics 
where upper(statistic_name) like '%PHYSICAL%' and owner not like 'E%';
set wrap off
set termout on
select i.instance_name instance,(b.value-a.value)*12 IO_PER_MINUTE,
a.statistic_name,
a.owner||'.'||a.object_name||'.'||a.subobject_name object_name
from gv$segment_statistics2 b,gv$segment_statistics1 a,gv$instance i
where a.statistic_name=b.statistic_name 
and a.inst_id=i.inst_id and b.inst_id=i.inst_id
and a.inst_id=b.inst_id and a.object_type=b.object_type
and a.owner||'.'||a.object_name||'.'||a.subobject_name=
b.owner||'.'||b.object_name||'.'||b.subobject_name
and upper(a.statistic_name) like '%PHYSICAL%'
and a.object_name not like 'GV$SEGMENT_STATISTICS%'
and b.value-a.value > 0
order by b.value-a.value;
set termout off
drop table gv$segment_statistics1 purge;
drop table gv$segment_statistics2 purge;
set termout on
Previous Topic: how to install developer suite 10g on linux version?
Next Topic: spliting a xml file based on no.of records in linux
Goto Forum:
  


Current Time: Thu Mar 28 09:05:41 CDT 2024