Re: Oracle server is very slow.

desmond <ddl76_at_singnet.com.sg> wrote:
>I have this problem when connecting to my Oracle server running on NT4
>workstation with 394mb of RAM
>and CPU running at PIII 500mhz. It happens not only to SQLPLUS but other
>types of database client like
>ODBC. Whenever we tried connecting to our Oracle server in the morning, its
>fine. But performance will
>degrade as time passes by. There' s only about 5-7 programmers using the
>Oracle server.
>
>Sometimes when we keyed in a wrong command at SQLPLUS the server will even
>hang.
>
>Is there any cure to this tricky problem? I have checked my Performance
>monitor for memory and processor
>status. Everything seems OK.

You don't give a version number for Oracle, please remember to always include the version number. It would make this answer much shorter.

My first guess would be memory related problems, try using Performance Monitor to measure memory use and page faults. To resolve this, modify the memory usage of various database structures, or buy more RAM.

Beyond that, to look for database contention: If you're on 8.1.6 or later, run a statspack report. On 8.1.7 or 9.0.1 see ${ORACLE_HOME}/rdbms/admin/spdoc.txt On 8.1.6 see ${ORACLE_HOME}/rdbms/admin/statspack.doc

(Apologies, these are UNIX paths, I don't know what the NT equivalent of ${ORACLE_HOME} is.)

If you're on 8.1.5, or Oracle 7 or 8, run a utlbstat/utlestat report.

-- 
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/