Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> How to solve I/O problem, I/O is an OS or Stroage issue
ronenshi
post Jan 11 2005, 05:46 AM
Post #1


Newbie
*

Group: Members
Posts: 7
Joined: 15-December 04
Member No.: 1,144



Hi,

I have Oracle DB 9i r2 which need to support 1000 insert /sec (each insert is around 1k). and at the same time run queries that retrives a lot of data.
The DB is well tuned with indexes (no large table full scan).
currently the DB is supporting around 200 inserts/sec and the bottleneck of the application is the I/O.
my questions are:
1. Is this an OS or storage problem?
2. How can i tell what is the IO rate from 1000 inserts/sec?
3. How can i solve the problem?


THX
Ron
Go to the top of the page
 
+Quote Post
burleson
post Jan 11 2005, 07:07 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,506
Joined: 26-January 04
Member No.: 13



smile.gif

Hi,

To see the bottlenecks, run STATSPACK snapshots during the load period and look at the top-5 timed events.

How are you loading the data?

If plain "inserts", are you using append and a large blocksize?

If parallel, did you define multiple freelists?

http://www.dba-oracle.com/t_optimize_inser...performance.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
ronenshi
post Jan 11 2005, 08:05 AM
Post #3


Newbie
*

Group: Members
Posts: 7
Joined: 15-December 04
Member No.: 1,144



Hi,

To your questions:
I'm loading the data from 5 connections using OCI bulk inserts off 100 rows each.
We use the 8k blocksize without the append hint.
Yes multiple freelists are defined.

Sorry for not mention it before but one of the reason for this topic is
Because we want make a hardware resource planning for our application.


THX
Ron
Go to the top of the page
 
+Quote Post
burleson
post Jan 11 2005, 08:31 AM
Post #4


Advanced Member
***

Group: Members
Posts: 11,506
Joined: 26-January 04
Member No.: 13



smile.gif

Hi,

Oh! You want trhe append hin in your OCI code.

I did a benchmark using solid-state disk with OCI loads, and it went 200x faster with SSD. As I recall, I got over a half-millions rows per minute. it You might want this book on Oracle tuning with SSD:

http://www.rampant-books.com/book_2005_1_ssd.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
ronenshi
post Jan 11 2005, 08:34 AM
Post #5


Newbie
*

Group: Members
Posts: 7
Joined: 15-December 04
Member No.: 1,144



Hi,
thanks, but isnt the SSD solution is good for small DB?
Ron
Go to the top of the page
 
+Quote Post
saqibz
post Jan 11 2005, 12:42 PM
Post #6


Advanced Member
***

Group: Members
Posts: 64
Joined: 11-August 04
From: Atlanta, GA
Member No.: 415



First of all let me just say that the fact that your system is nicely indexed, does not mean that it is performing at the optimum level. As it is a known fact that blind use of indexes may at times be the actual source of performance problems. That is why you need to look at sql statements that do the most LIOs and tune them one by them in their severity order. You could also try to go FTS using Oracle Parallel Query to see if that helps performance.

Answers to questions:
1. In importance order, it may be un-tuned sql, inefficient design, oracle db, storage and then OS IMHO.

2. Can't say might want to have a look at benchmark factory from quest.
http://www.quest.com/benchmark_factory_for...es_benefits.asp

3. Write efficient code. Identify and isolate then problem, Ö.. and then fix problem. Use statspack and other monitoring tools to monitor. If you canít afford all that.... then throw hardware, but that would the last thing on my list personally.


--------------------
HTH,

Saqib Zulfiqar
Go to the top of the page
 
+Quote Post
metacharacter
post Jan 19 2005, 01:13 AM
Post #7


Advanced Member
***

Group: Members
Posts: 79
Joined: 26-November 04
From: Mumbai
Member No.: 1,074



If you r using unix use commands sar and iostat to look on i/o contentions


--------------------
If you're never scared or embarrassed or hurt, it means you never take any chances.
Go to the top of the page
 
+Quote Post
burleson
post Jan 19 2005, 06:21 AM
Post #8


Advanced Member
***

Group: Members
Posts: 11,506
Joined: 26-January 04
Member No.: 13



smile.gif

>> but isnt the SSD solution is good for small DB?

Actually, SSD is getting very cheap ($1k per gig), and lots of super-large database are using it selectively for removing I/O contention from disks.


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd September 2014 - 11:10 AM