| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: DB gets really slow
Reuben
Well, as you get into web development it is more important to learn to pinpoint the performance problem. First, figure out how to check the performance on your Linux box. Try to find out whether the Linux system gets progressively more heavily loaded as your DB gets slower, or becomes less loaded. As Jonathan Lewis on this list put it,
If you have a performance problem,
either your database is working too hard,
or it's not being allowed to work.
Next, check inside Oracle to see what your Oracle database is waiting on. Here is a script that was previously posted on this list. This may unearth the problem or you may have to dig a little deeper, as Ryan has pointed out.
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
REM From: Mohammed Shakir [mailto:mshakir08816_at_yahoo.com]
REM Sent: Thursday, October 10, 2002 5:14 PM
REM To: Multiple recipients of list ORACLE-L
REM Subject: RE: Table Scans
REM Try the following script. I am not sure where I found it on the web.
REM However, this script I use to find the bottlenecks in the system.
REM Run it while your application is running.
REM Look for wait event 'db_file_scattered_read'.
REM Check the related SQL.
REM You can remove other wait events if you do not need them.
set echo off feedback off timing off pause off
set pages 100 lines 500 trimspool on trimout on space 1 recsep each
col sid format 990
col program format a15 word_wrap
col event format a8 word_wrap
col ospid format 9999990 heading "Srvr|PID"
col name format a15 word_wrap heading "OBJECT NAME"
col sql_text format a30 word_wrap
select /*+ rule */
w.sid,
w.event,
s.program,
p.spid ospid,
e.owner || '.' || e.segment_name || ' (' || e.segment_type || ')'
name,
a.sql_text
from sys.v_$sqlarea a,
sys.dba_extents e,
sys.v_$process p,
sys.v_$session s,
'latch free',
'log buffer space',
'free buffer waits',
'buffer busy waits',
'db file scattered read',
'db file sequential read',
'library cache pin',
'log file switch completion',
'enqueue',
'log file parallel write',
'db file parallel write',
'log file sync',
'file open',
'direct path write',
'library cache lock')
and s.sid = w.sid and p.addr = s.paddr and e.file_id = to_number(w.p1)
-----Original Message-----
Sent: Thursday, August 21, 2003 3:20 PM
To: Multiple recipients of list ORACLE-L
Hello,
Sorry if this is a really newbie kinda question. I am a web developer and at
this point we have a contract to develop something using Oracle. We use PHP as the front end to connect to oracle.
Now, eventually all the codes will be moves to the company that outsource it
to us, and they have cluster oracle and their DBA. But for development purposes, for some reason, we don't have access to their DB server.
So I downloaded the demo version and install it to our own development
server
(Redhat Linux 7.3, 1GB RAM, 1.4GHz Athlon XP). I just do install as
explained
here:http://www.puschitz.com/InstallingOracle9i.html, started the database,
and started doing development (creating/dropping table,
insert/update/delete/select, etc). I don't have very much data, since it's
only dummy data for testing purposes.
So it's really a vanilla oracle-install. No customization, optimization
whatsoever.
At some point, after being up for a while, the whole thing starts to
slowdown.
I am assuming it's Oracle, since I can't really think anything else who slow
it down. The same codes and tables and data using postgreSQL doesn't do this.
Restarting oracle and the webserver helps, and things back to normal.
My question is, is there anything critical that I miss here? Do I have to do
something, like cleaning up etc., to keep the DB running well? In the early starts of development, we did a lot of dropping and re-creating tables, indexes, constraint, and updating/deleting data. Does this have any effect? Anything I can do to find the source of this problem?
We don't have an Oracle DBA, and I am not very familiar with oracle from DBA
point of view (only as developer), and I didn't really want to spend a lot
of
time customizing/learning/optimizing the development database because we
needed to start development ASAP, but it's probably time to start learning.
Thanks for any help.
Reuben D.Budiardja
-- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN ------------------------------------------------- /"\ ASCII Ribbon Campaign against HTML \ / email and proprietary format X attachments. / \ ------------------------------------------------- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reuben D. Budiardja INET: techlist_at_voyager.phys.utk.edu Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: DWILLIAMS_at_LIFETOUCH.COM Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Aug 21 2003 - 16:39:31 CDT
 |
 |