| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Please help with performance problem
In article <3474BCCE.1630EF46_at_webspan.net>, Gena <gshepin_at_webspan.net> wrote:
>Hi.
>
>I am trying to tune an Oracle 7.3.3 running on an NT 4 server.
>This machine has 256Megs of Memory and dual 200MHZ processors.
>This machine is completely dedicated to Oracle and never has anything
>else running.
>
>I have been trying to tune it few a few days now.
>The selects seem to work fine, but updates, deletes and especially
>inserts seem to take much longer than I would think they should on such
>a machine.
>
>To insert 2500 records on a 4 field table (which has only about 2000
>records) takes 45 seconds to a minute.
>To update or delete the same # of records (the delete criteria is on an
>indexed field) takes 30-40 seconds.
>Basically, every operation that does any database changes seems to kill
>performance.
You may not be testing accurately. Remember, oracle is designed for multiuser performance, and single user tests may give these types of results.
When you select, the performance can be better because the SGA is loaded with a larger granularity than the record size. So, you scan through the whole table, each disk fetch can load a whole bunch of records into the buffers, to then be read from memory. Also, SQL statements are kept in memory, and thus can affect performance if the same statement is executed multiple times.
When you do the writes and updates, a lot more has to happen from the point of view of I/O. In fact, dirty buffers are written out at the database writers leisure, so several users pounding updates into the system may show the same performance you've seen - each (more likely, you will see degradation, but it will be less than you appear to expect).
Mass deletes can also have highwatermark effects.
Of course, NT may not be real bright about prioritizing its processes. You may have to convince it to give more time to Oracle.
Have you run the various scripts that tell you what to tune? (If anyone has the url of Tales From the Scrypt, please send it to me, I lost it and support hasn't been any help)
SET ECHO off
REM @(#)tfsdbsta.sql - shows some dbstatistics
set pages 999
set head off feedback off termout off
col name format a10
column dbname new_value xdb noprint
column today new_value tdy noprint
select substr(sysdate,1,9) today from dual;
select value dbname from v$parameter where name = 'db_name';
spool tfsdbsta.lst
select 'Hit and miss rate for '||name||' - '||sysdate
from v$database, dual;
prompt
set head on feedback on
REM NAME: TFSDBSTA.SQL
REM USAGE:"@path/tfsdbsta"
REM ------------------------------------------------------------------------REM REQUIREMENTS:
REM ------------------------------------------------------------------------REM PURPOSE:
REM ------------------------------------------------------------------------ REM EXAMPLE: REM REM PINS RELOADS miss rate COMMENTS REM ---------- ---------- ---------- ------------------------ REM38327 172 .448769797 okay REM REM GETS GETMISSES miss rate COMMENTS REM ---------- ---------- ---------- ------------------------ REM 92511 4378 4.7324102 okay REM REM PHYSICAL LOGICAL hit rate COMMENTS REM ---------- ---------- ---------- ------------------------ REM 350260 2198046 84.0649377 okay REM REM memory disk rows COMMENTS REM ---------- ----------- ---------- ----------------------- REM 2342 7 119892 okayREM
REM ------------- --------------- ---------- ------------------------- REM 4 .040816327 .020833333 okayREM
REM ---------------- --------------- ---------- ---------------------- REM 98 1 .510416667 okayREM
REM ----------------------- --------------- ---------- --------------- REM 12 .12244898 .0625 okayREM
REM ------------------------------------------------------------------------REM DISCLAIMER:
REM ------------------------------------------------------------------------REM Main text of script follows:
set echo off
set verify off
set feedback off
def p=sum(pins)
def r=sum(reloads)
def m=&r/&p*100
select &p pins,&r reloads, &m "miss rate",
decode(sign(&m-1),1,'increase shared_pool_size','okay') comments
from v$librarycache
def g=sum(gets)
def m=sum(getmisses)
def mi=&m/&g*100
select &g gets,&m getmisses, &mi "miss rate",
decode(sign(&mi-10),1,'increase shared_pool_size','okay') comments
from v$rowcache
/
prompt
prompt
def p=sum(decode(statistic#,39,value,0))
def l=sum(decode(statistic#,37,value,38,value,0))
def h=(1-(&p/&l))*100
select &p physical, &l logical, &h "hit rate",
decode(sign(&h-70),-1,'increase db_block_buffer',
decode(sign(&h-95),1,'decrease db_block_buffer','okay')) comments
from v$sysstat
col disk format 99,999,999
def m=sum(decode(statistic#,121,value,0))
def d=sum(decode(statistic#,122,value,0))
def r=sum(decode(statistic#,123,value,0))
select &m "memory",
&d "disk",
&r "rows",
decode(sign(&d-&m),1,'increase sort_area_size','okay') comments
from v$sysstat
def t=sum(decode(statistic#,4,value,0))
def l=sum(decode(statistic#,0,value,0))
def e=sum(decode(statistic#,23,value,0))
select &e "enqueue waits",
&e/&t "per transaction",
&e/&l "per logon" ,
decode(sign((&e/&t)-1),1,'increase enqueue_resources ','okay') comments
from v$sysstat
def t=sum(decode(statistic#,4,value,0))
def l=sum(decode(statistic#,0,value,0))
def e=sum(decode(statistic#,58,value,0))
select &e "DBWR checkpoints",
&e/&t "per transaction",
&e/&l "per logon" ,
decode(sign((&e/&t)-1),1,'increase log_checkpoint_interval ','okay')
comments
def t=sum(decode(statistic#,4,value,0))
def l=sum(decode(statistic#,0,value,0))
def e=sum(decode(statistic#,83,value,0))
select &e "redo log space requests",
&e/&t "per transaction",
&e/&l "per logon" ,
decode(sign((&e/&t)-1),1,'increase log_buffer ','okay') comments
from v$sysstat
exit
>
>
>I am trying anything I can think of to improve this performance but
>nothing seems to be working.
>
>The Data, indexes, and rollback tablespaces are all on separate drives.
>
>I have rebuilt the database with the following initorcl parameters:
>
>I changes all initorcl parameters to those of a Large Database, I also
>set
>cpu_count=2
>db_block_size=8192 (the maximum allowed on NT)
>shared_pool_size=12000000
>pre_page_sga = True (to ensure the entire SGA will be in memory)
>
>Then I rebuilt the database.
>
>I saw little if any improvement in performance (from the default install
>
>configuration) after the rebuild.
>
>Can somebody please tell me if I'm missing any important parameters to
>improve
>performance, or has any other suggestions, or best of all if someone has
>a similar
>hardware setup, can I please see you initorcl file.
>
>any help would be GREATLY appreciated..........
>
-- These opinions are my own and not necessarily those of Information Quest jgarry@eiq.com http://www.informationquest.com http://ourworld.compuserve.com/homepages/joel_garry "See your DBA?" I AM the @#%*& DBA!Received on Fri Nov 21 1997 - 00:00:00 CST
 |
 |