f



Oracle scalability issues

Hello,
I'm writing my masters thesis on "scalability of a web system". This
system uses a Oracle 9.0.3 RAC DB. I wonder if there exists any known
scalability problems with this database?.

I'm doing some calculations and wonder if anyone know how many
CPU instructions a SQL operations in Oracle needs (just a rough number, are
we talking 10^6 or 10^7), and how many clock cycles does it need to
complete?

Thanks in advance. 

-- 
Jorgen Ruud
jorgenru@stud.ntnu.no
Norwegian Universitity of Science and Technology
0
jorgenru
9/29/2004 1:44:32 PM
comp.databases.oracle.misc 8436 articles. 1 followers. Post Follow

9 Replies
2214 Views

Similar Articles

[PageSpeed] 59

**** Post for FREE via your newsreader at post.usenet.com ****

J�rgen Ruud wrote:


> Hello,
> I'm writing my masters thesis on "scalability of a web system". This
> system uses a Oracle 9.0.3 RAC DB. I wonder if there exists any known
> scalability problems with this database?.

> I'm doing some calculations and wonder if anyone know how many
> CPU instructions a SQL operations in Oracle needs (just a rough number, are
> we talking 10^6 or 10^7), and how many clock cycles does it need to
> complete?

> Thanks in advance. 

Jorgen, do you mean other than the basic scalability question concerning 
RAC to begin with?  Any application that runs on a RAC setup could 
probably be ran faster on a single SMP machine with processing power equal 
to the total of the RAC system.

Being that version 9.0 did not survive very long in the field before 9.2 
was released I would recommend you use 9.2.0.4 or higher if possible.  We 
skipped 9.0 upgrading from 8.1.7.4 OPS to 9.2.0 RAC rather than deal with 
the slew of published 9.0 bugs.

HTH -- Mark D Powell --





-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 *** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
                      http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
0
Mark
9/29/2004 3:11:19 PM
Mark D Powell wrote:
>Jorgen, do you mean other than the basic scalability question concerning 
>RAC to begin with?  

The sort of scalability i think about is:
- what will happen if the DB expiriences a doubling, trebling, ten fold
increase in number of operations per second. 
-  is it usually the memory, the cpu or the disk that is the
bottleneck, or is it something totaly else. Like memory leaks?

>was released I would recommend you use 9.2.0.4 or higher if possible.  We 
Sorry this was a typo, the system already runs on 9.2.0.X

-- 
J�rgen Ruud
jorgenru@stud.ntnu.no
0
jorgenru
9/29/2004 3:58:16 PM
On Wed, 29 Sep 2004 15:58:16 +0000 (UTC), jorgenru@jaguar.stud.ntnu.no
(J�rgen Ruud) wrote:

>Mark D Powell wrote:
>>Jorgen, do you mean other than the basic scalability question concerning 
>>RAC to begin with?  
>
>The sort of scalability i think about is:
>- what will happen if the DB expiriences a doubling, trebling, ten fold
>increase in number of operations per second. 

depends on how the application is written. If the application uses
prepared statements shouldn't be a problem, if it doesn't (default)
buy another server, and another server, and another server
>-  is it usually the memory, the cpu or the disk that is the
>bottleneck, or is it something totaly else. Like memory leaks?
>

Depends on the application. If the developers never tuned their sql
statements bottlenecks can possibly occur in all areas. Memory leaks
are scarce.
Not tuning sql statements, because not knowing how to do it, is the
default.
>>was released I would recommend you use 9.2.0.4 or higher if possible.  We 
>Sorry this was a typo, the system already runs on 9.2.0.X

You are barking against the wrong tree. More than 80 percent of all
performance problems is caused by the application and can't be
resolved by adding server capacity.


--
Sybrand Bakker, Senior Oracle DBA
0
Sybrand
9/29/2004 5:26:01 PM
"J�rgen Ruud" <jorgenru@jaguar.stud.ntnu.no> wrote in message
news:slrncllmso.90v.jorgenru@jaguar.stud.ntnu.no...
> Mark D Powell wrote:
> >Jorgen, do you mean other than the basic scalability question concerning
> >RAC to begin with?
>
> The sort of scalability i think about is:
> - what will happen if the DB expiriences a doubling, trebling, ten fold
> increase in number of operations per second.
> -  is it usually the memory, the cpu or the disk that is the
> bottleneck, or is it something totaly else. Like memory leaks?
>
> >was released I would recommend you use 9.2.0.4 or higher if possible.  We
> Sorry this was a typo, the system already runs on 9.2.0.X
>
> -- 
> J�rgen Ruud
> jorgenru@stud.ntnu.no

In addition to what Sybrand wrote, keep in mind that disk bottlenecks will
almost always have a bigger impact than memory bottlenecks because disk I/O
is thousands of times slower than memory I/O. So, a small disk I/O problem
can often (not always) cause a bigger delay than a big memory problem. See
the O'Reilly book, "Oracle Performance Tuning" for a start on the subject.
Scalability is a much bigger topic than can be covered in the time you have,
IMO. You may want to narrow down your topic.


0
Alan
9/29/2004 5:43:18 PM
J�rgen Ruud wrote:

> 
> I'm doing some calculations and wonder if anyone know how many
> CPU instructions a SQL operations in Oracle needs (just a rough number,
> are we talking 10^6 or 10^7), and how many clock cycles does it need to
> complete?
> 

Some things to think about ...

What is the meaning of 'SQL Operation' for your context.

Recognize as well that a single SQL statemnt can involve plain disk access,
composite disk access (index >> table, etc), multiple concurrent disk
accesses, as well as inherent sorting and calculations. 

What would you expect to be the difference in CPU cycle between a

        SELECT DUMMY FORM DUAL;

and possible a select from a view as seen in
$ORACLE_HOME/rdbms/admin/catalog.sql

Also, does you analysis include the setup and evaluation of specific
optimizers? (e. are you allowing only Rule optimization, or Cost, or mix,
etc.)

/Hans
0
Hans
9/30/2004 12:01:38 AM
"J�rgen Ruud" <jorgenru@jaguar.stud.ntnu.no> wrote in message
news:slrncllmso.90v.jorgenru@jaguar.stud.ntnu.no...
> Mark D Powell wrote:
> >Jorgen, do you mean other than the basic scalability question concerning
> >RAC to begin with?
>
> The sort of scalability i think about is:
> - what will happen if the DB expiriences a doubling, trebling, ten fold
> increase in number of operations per second.
> -  is it usually the memory, the cpu or the disk that is the
> bottleneck, or is it something totaly else. Like memory leaks?
>
> >was released I would recommend you use 9.2.0.4 or higher if possible.  We
> Sorry this was a typo, the system already runs on 9.2.0.X
>
> -- 
> J�rgen Ruud
> jorgenru@stud.ntnu.no
Usually the application sucks so that is the biggest problem by far.  If you
use bind variables, and reuse cursors and follow the application developer
recommendations it will scale rather well.  Also depends on what sql and is
it an oltp system or a data wharehouse?   Makes a big difference.
Jim


0
Jim
9/30/2004 12:20:17 AM
In article <C1I6d.2541$eq.2493@edtnps84>, Hans Forbrich wrote:

>> I'm doing some calculations and wonder if anyone know how many
>> CPU instructions a SQL operations in Oracle needs (just a rough number,
>> are we talking 10^6 or 10^7), and how many clock cycles does it need to
>> complete?

>What is the meaning of 'SQL Operation' for your context.
For this will e.g be a TCP-B operation, read one tuppel, write one
tuppel, update two tuppels. Some simple standard operation. 

What I'm after here is a simple: "stick our finger in the air, and give
me a feel" on the numbers i'm requesting. It is just so i get an
indication on the cpu usage a simple database operation use. 

-- 
J�rgen Ruud
jorgenru@stud.ntnu.no
0
jorgenru
9/30/2004 9:42:12 AM
"J�rgen Ruud" <jorgenru@tiger.stud.ntnu.no> wrote in message
news:slrnclnl7k.iul.jorgenru@tiger.stud.ntnu.no...
> In article <C1I6d.2541$eq.2493@edtnps84>, Hans Forbrich wrote:
>
> >> I'm doing some calculations and wonder if anyone know how many
> >> CPU instructions a SQL operations in Oracle needs (just a rough number,
> >> are we talking 10^6 or 10^7), and how many clock cycles does it need to
> >> complete?
>
> >What is the meaning of 'SQL Operation' for your context.
> For this will e.g be a TCP-B operation, read one tuppel, write one
> tuppel, update two tuppels. Some simple standard operation.
>
> What I'm after here is a simple: "stick our finger in the air, and give
> me a feel" on the numbers i'm requesting. It is just so i get an
> indication on the cpu usage a simple database operation use.
>
> -- 
> J�rgen Ruud
> jorgenru@stud.ntnu.no

We know what you are after, but we are trying to tell you that the answer is
not that simple. In a sense, you are asking the wrong question. Anyway, no
one knows how many CPU instructions are needed. First, it is irrelevant to
the SQL user, and second, it is most likely proprietary information for each
RDBMS. The RDBMS hides that layer of detail from the user. The engine takes
the SQL and ultimately turns it into machine instructions, and it is these
instructions that use the CPU cycles. The other items mentioned (proper
application coding, writing good SQL, designing a realtional schema
properly, tuning database memory and disk usage) are what we deal with. The
people who write compilers and chip instructions are the ones who deal with
CPU cycles.


0
Alan
9/30/2004 1:37:24 PM
J�rgen Ruud wrote:
> In article <C1I6d.2541$eq.2493@edtnps84>, Hans Forbrich wrote:
> 
> 
>>>I'm doing some calculations and wonder if anyone know how many
>>>CPU instructions a SQL operations in Oracle needs (just a rough number,
>>>are we talking 10^6 or 10^7), and how many clock cycles does it need to
>>>complete?
> 
> 
>>What is the meaning of 'SQL Operation' for your context.
> 
> For this will e.g be a TCP-B operation, read one tuppel, write one
> tuppel, update two tuppels. Some simple standard operation. 
> 
> What I'm after here is a simple: "stick our finger in the air, and give
> me a feel" on the numbers i'm requesting. It is just so i get an
> indication on the cpu usage a simple database operation use. 

While you are at it be sure you also use DBMS_ODCI to allow you to
compare various CPUs.

A demo can be found at:
http://www.psoug.org
click on Morgan's Library
click on DBMS_ODCI.

-- 
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

0
Daniel
10/1/2004 12:58:39 AM
Reply: