Home » RDBMS Server » Performance Tuning » Performance of database runnig on RAC 10g with two Solaris nodes (Oracle 10g RAC)
Performance of database runnig on RAC 10g with two Solaris nodes [message #406006] Mon, 01 June 2009 09:36 Go to next message
alexberi
Messages: 16
Registered: May 2009
Junior Member
Hello,

I have performance problems one a database run on RAC installed on two Solaris nodes (node1 and node2).
ASM is used with two disk groups (DG1, DG2):
- database files and redo logs are on DG1
- flash recovery area (FRA) files and archive log files are on DG2

SQL> alter system set db_recovery_file_dest='+DG2' scope=both;
SQL> alter system set log_archive_dest_10='location=use_db_recovery_file_dest';
SQL> alter database archivelog;
SQL> alter database enable block change tracking using file '+DG2';
SQL> alter database flashback on;

Need to increase performance. Which way do you recommend?

I am thinking to move some files (FRA or redo logs) on an NFS file system mounted from network on the two RAC nodes. But I don't know if this is possible.

Thanks alot.
Re: Performance of database runnig on RAC 10g with two Solaris nodes [message #406007 is a reply to message #406006] Mon, 01 June 2009 09:42 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
which have you tried & what were the results
Re: Performance of database runnig on RAC 10g with two Solaris nodes [message #406022 is a reply to message #406007] Mon, 01 June 2009 13:20 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


>>Need to increase performance.

Ok. How your saying your database performance not good; Prove it;

Thanks.

Babu
Re: Performance of database runnig on RAC 10g with two Solaris nodes [message #406159 is a reply to message #406006] Tue, 02 June 2009 10:33 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
alexberi wrote on Mon, 01 June 2009 16:36

I have performance problems one a database run on RAC installed on two Solaris nodes (node1 and node2).
ASM is used with two disk groups (DG1, DG2):
- database files and redo logs are on DG1
- flash recovery area (FRA) files and archive log files are on DG2



So what could you tell us about DG1 and DG2.

What kind of storage system are you using and how is it performing on your benchmarks?
What is included in those diskgroups? ( physical disks, or raw devices )
What RAID configuration are you using?

Quote:

I am thinking to move some files (FRA or redo logs) on an NFS file system mounted from network on the two RAC nodes. But I don't know if this is possible.



How do you think that moving your files ( and especially RedoLogs, generally the most frequently used ones ) on a storage system using a slow transfer method like NFS.

Quote:

Need to increase performance. Which way do you recommend?



It seems you don't even know a single notion about database performance tuning. By the way you described your environment and the way you think to improve its performances I can guess that you totally ignore how a database works and how a storage system works too... so call an expert and let him do the job!

Anyway you may find good tips here:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/part2.htm#i996730
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/technique.htm#i10797
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/iodesign.htm#i20394
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/storeman.htm#sthref1691


Bye Alessandro
Re: Performance of database runnig on RAC 10g with two Solaris nodes [message #406577 is a reply to message #406159] Thu, 04 June 2009 13:04 Go to previous messageGo to next message
alexberi
Messages: 16
Registered: May 2009
Junior Member
Hello Alessandro,

Thank you for your acid answer. I suppose I was not very clear in my request.
Hardware available:
- 2 Solaris servers with Oracle RAC 10g installed
- 1 NAS configured with RAID 5 (DG1 and DG2 disk groups of the ASM are stored on the NAS).
- archived log mode and flashback are enabled, DG2 is used
- DG1 is used to store database files and redo logs

Alessandro Rossi wrote on Tue, 02 June 2009 18:33
alexberi wrote on Mon, 01 June 2009 16:36

I have performance problems one a database run on RAC installed on two Solaris nodes (node1 and node2).
ASM is used with two disk groups (DG1, DG2):
- database files and redo logs are on DG1
- flash recovery area (FRA) files and archive log files are on DG2



So what could you tell us about DG1 and DG2.

What kind of storage system are you using and how is it performing on your benchmarks?
What is included in those diskgroups? ( physical disks, or raw devices )
What RAID configuration are you using?



During tests I saw that there is a high Waiting time on IO operation on both nodes of the RAC.
This is the reason I am thinking to store the redo logs and/or flashback area and/or archived redo logs on another hardware - the only one I have available is a NFS volume which is not really slow.

Hope now is more clear,

Thank you, Alex
Re: Performance of database runnig on RAC 10g with two Solaris nodes [message #406579 is a reply to message #406022] Thu, 04 June 2009 13:11 Go to previous messageGo to next message
alexberi
Messages: 16
Registered: May 2009
Junior Member
I am attaching AWR logs from RAC node1 during performance tests:

Re: Performance of database runnig on RAC 10g with two Solaris nodes [message #406580 is a reply to message #406579] Thu, 04 June 2009 13:13 Go to previous messageGo to next message
alexberi
Messages: 16
Registered: May 2009
Junior Member
Attaching also AWR logs for RAC node2:
Re: Performance of database runnig on RAC 10g with two Solaris nodes [message #406582 is a reply to message #406006] Thu, 04 June 2009 13:25 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>SELECT 'x' FROM DUAL
Parsed & executed almost 230,000 times.

What a waste!
Re: Performance of database runnig on RAC 10g with two Solaris nodes [message #406584 is a reply to message #406582] Thu, 04 June 2009 13:54 Go to previous messageGo to next message
alexberi
Messages: 16
Registered: May 2009
Junior Member
Huh, I did not see the 220,000 calls for:
SELECT 'x' FROM dual;

This is not from the application, must be from the JBOSS connected to the Oracle RAC. I found next page http://www.mastertheboss.com/en/jboss-server/202-porting-weblogic-to-jboss-part-1.html?tmpl=component&print=1&page=

In this page in the file "MyDatasource-jdbc.xml" is a section:
<jdbc-connection-pool-params>
<test-table-name>SQL SELECT 1 FROM DUAL</test-table-name>
</jdbc-connection-pool-params>

I need to check this with the JBOSS guys.

Thanks alot, ALex.
Re: Performance of database runnig on RAC 10g with two Solaris nodes [message #406585 is a reply to message #406006] Thu, 04 June 2009 14:13 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
I found it in the 1st AWR posted in
SQL ordered by Parse Calls

* Total Parse Calls: 2,154,390
* Captured SQL account for 81.3% of Total

It was top SQL in the list.

229,930 229,930 10.67 avc1jqzz04wpr java@jbosscluster2-s (TNS V1-V3) SELECT 'x' FROM DUAL
Re: Performance of database runnig on RAC 10g with two Solaris nodes [message #406587 is a reply to message #406006] Thu, 04 June 2009 14:42 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Tablespace	Reads	Av Reads/s	Av Rd(ms)	Av Blks/Rd	Writes	Av Writes/s	Buffer Waits	Av Buf Wt(ms)
BLUE_M064_CLOB 	171,791 	24 	1.14 	1.01 	30,928 	4 	1,541 	69.15
UNDOTBS1 	92,877 	13 	0.95 	1.00 	8,949 	1 	1,318 	0.09
BLUE_AUTO_INDX 	18,284 	3 	6.47 	2.02 	49,643 	7 	18,360 	24.54


In round numbers I see about 3 times more WRITES than READS to BLUE_AUTO_INDX tablespace.

If this TS really holds mostly indexes, I would suspect that index "rebuilds" were being done.
Typically in OLTP READs are 10 times more than WRITEs.

I am not claiming this is bad; just curious.

upon further review I see the following:
Segments by Row Lock Waits

    * % of Capture shows % of row lock waits for each top segment compared
    * with total row lock waits for all segments captured by the Snapshot 

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Row Lock Waits	% of Capture
HEAD 	BLUE_AUTO_INDX 	JMS_MESSAGES_DESTINATION_IB 	  	INDEX 	5,018 	78.71


My initial reaction is that this index is involved with message logging and that this index is a SEQUENCE.
If so, a slight performance gain might result from including the keyword REVERSE in the DDL for this index.

[Updated on: Thu, 04 June 2009 14:59]

Report message to a moderator

Re: Performance of database runnig on RAC 10g with two Solaris nodes [message #406751 is a reply to message #406577] Fri, 05 June 2009 10:45 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
My acid answer was just a way to stimulate this discussion where it was really hard to understand what was your problem. Don't give too much importance on it anyway.

Well, you're using the same NAS for DG1 and DG2, and probably they are sharing the same disks, so why didn't you use only one disk group to make for it?
There is no need to divide and recombine the same thing twice, it only adds more complexity to you storage configuration and I suggest you to merge the two partitions back together.


There is a really wide range of NAS on the market and no many of them are ideal for database systems and the ones that perform well on clusters are yet lesser, so could you say its model name. Without its specification I would need to ask you too many questions about it.

Currently what is composing the DG1 and DG2 diskgroups and how are you interfacing your NAS? (How do you access the NAS in simple words)

Keep in mind that the more complex is your interface to the NAS the more latency you will have and this will be a big problem on RACs. A RAC strongly depends on I/O subsystem performances and generally a NAS is not adviced for it.

If your database has an intense flow of I/O requests, the NAS may reach its elaboration limits with a consequent performance degradation.

For a RAC database it would be really better to have SAN system instead of a NAS one. A NAS system gives an abstraction level too high of the phisical storage. You had to use ASM because it wasn't possible to implement a Cluster File System on it and your database instances are involved in a very long travel to perform reads and writes.
A SAN system comes with a controller that implements only the basic I/O primitives that perform very well with low latency times and that leaves the task to organize the logical storage to the attached hosts.

Now considering your AWR reports I can see from your waits that there is a very high global cache contention and an intense (considering the time waited not the data volume) transactional activity.

After giving a look at Os stat it looks strange that during all this heavy load the system has been idle nearly all the time.

So you were probably right when supposing that your performance problems are regarding the I/O subsystem.

If my deductions are right the source of your problems is in the use of a NAS. I don't its model so I don't know if there could be be a better way to configure it.


Bye Alessandro
Re: Performance of database runnig on RAC 10g with two Solaris nodes [message #406766 is a reply to message #406587] Fri, 05 June 2009 12:59 Go to previous message
alexberi
Messages: 16
Registered: May 2009
Junior Member
1) You were right (again), all table indexes are stored in on BLUE_AUTO_INDX tablespace:
CREATE TABLESPACE BLUE_AUTO_INDX DATAFILE ''&5.BLUE_AUTO_INDX01.DBF'' SIZE 200M REUSE AUTOEXTEND ON NEXT 2M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

I dont know if this is the best way to do it.

2) In particular the index JMS_MESSAGES_DESTINATION_IB is a BITMAP index:
create bitmap index JMS_MESSAGES_DESTINATION_IB on JMS_MESSAGES (DESTINATION)
tablespace BLUE_AUTO_INDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);

I will put the REVERSE on index and see the improvement.

3) In AWR report there are 3 columns of type CLOB slowing most the performance for both read/write operations. I don't know how to improve this. Columns are: MESSAGECONTENTS.PLAINBODY , MESSAGECONTENTS.PLAINBODY and JMS_MESSAGES.MESSAGEBLOB:

CREATE TABLE MESSAGECONTENTS
(
ID VARCHAR2(16 BYTE) NOT NULL,
SYSTEMID VARCHAR2(8 BYTE) NOT NULL,
CREATIONTIMESTAMP DATE NOT NULL,
BODY CLOB,
PLAINBODY CLOB,
FORMATTYPE VARCHAR2(8 BYTE) NOT NULL,
TEMPLATEID VARCHAR2(18),
CUSTOMDATA CLOB
)
TABLESPACE BLUE_M008_NTAB
PCTUSED 60
PCTFREE 10
INITRANS 4
MAXTRANS 255
STORAGE (
INITIAL 8M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
LOB (BODY) STORE AS MESSAGECONTENTS_BODY
( TABLESPACE BLUE_M064_CLOB
ENABLE STORAGE IN ROW
CHUNK 4096
PCTVERSION 10
NOCACHE
STORAGE (
INITIAL 64M
NEXT 64M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
)
LOB (PLAINBODY) STORE AS MESSAGECONTENTS_PLAINBODY
( TABLESPACE BLUE_M064_CLOB
ENABLE STORAGE IN ROW
CHUNK 4096
PCTVERSION 10
NOCACHE
STORAGE (
INITIAL 64M
NEXT 64M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
)
LOB (CUSTOMDATA) STORE AS MESSAGECONTENTS_CUSTOMDATA
( TABLESPACE BLUE_M064_CLOB
ENABLE STORAGE IN ROW
CHUNK 4096
PCTVERSION 10
NOCACHE
STORAGE (
INITIAL 64M
NEXT 64M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL;


Tablespace creation:
CREATE TABLESPACE BLUE_M064_CLOB DATAFILE ''&5.BLUE_M064_CLOB01.DBF'' SIZE 2048M REUSE AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64M SEGMENT SPACE MANAGEMENT AUTO

Previous Topic: stale_stats and STATTYPE_LOCKED in DBA_TAB_STATISTICS
Next Topic: Performance degrades with big block size tablespace
Goto Forum:
  


Current Time: Fri Dec 09 06:00:36 CST 2016

Total time taken to generate the page: 0.32245 seconds