Hello!
We have BaanIV, NT and Oracle 8.1.7.3 on our system, and sometimes baan is working very slow so we have to reboot server then is OK. When I stop baan services and oracle services all 4 procesors are stil on 80% becouse oracle.exe is stil on.
Can someone can tell me what could be wrong or what to look for opimise the system to avoid rebooting server every day

Thank's in advance for all the answers

Lp Mare

Try loading Oracle's STATPACK and collect several samples over a day or so before generating the spreport. This will show you the top wait events and should start giving you a place to start looking. You wil find several good articles on using STATPACK in the archives of Oracle magazine and on Metalink.

Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.

Please check the tablespaces.Data file may be full.In that case add new data files.This is also a main reason of sytem slow.
Rgds,

C N Chhabi

If the tablespaces become full, you get an Oracle error and won't be able to do anything on the system until it gets fixed. It won't make the system slow, but it will make it stop.

Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.

Hi Lp Mare,

NOTE > This is *NOT* a one day affair, it is important to make sure that Ur collecting data on a regular basis to ensure a base line of performance. Else it will be difficult.

Ok...do U have any of the following with U?

NOTE > Basically these are supplied with the Windows NT Resource Kit (Workstation or Server). Check with Ur Network Geek. This is the first step in diagnosing any operating system related problems (if any).

>>QuickSlice

Start > Programs > Resource Kit 4.0 > Diagnostics > Quick Slice

*This GUI-based tool is very useful for obtaining a quick overview of which processes are using most CPU time. It has a continuously updated, easy-to-interpret display that distinguishes between time spent in user mode and kernel mode.

Clicking on an individual process pops up a window showing more details for that process. Unlike Performance Monitor, QuickSlice has a low system overhead.

>>Process Viewer

Start > Programs > Resource Kit 4.0 > Diagnostics > Process Viewer,

*This GUI-based tool gives a snapshot of process details, including process priority, thread priority, CPU usage and memory usage. There are buttons to provide additional details of memory usage, kill the process if required, and refresh the display.

Tips
++++

There are several general systems setting on an NT server which U can ensure that NT is configured as well as possible for database server performance. To make Windows NT virtual memory perform as well as possible, it is best to have a large page file. The file pagefile.sys is used for managing virtual memory via the operating system.

>You may need to configure the page file to be as big as largest datafile to use OCOPY, as OCOPY.EXE uses memory to copy the datafile.

To change the page file right click on My Computer and select Properties and go to the Performance Tab. Also, using Control Panel | Network Server | Configuration select optimize for Network Applications instead of File sharing, and remove unused protocols. Also set the Performance Boost Foreground Applications to NONE.A couple of Oracle settings via initSID.ORA are pre_page_sga to load the entire SGA into memory (we'll see why in a minute) and timed_statistics to get accurate timings in various Oracle performance reporting utilities.

One of the most unique performance monitoring tools for Windows NT is the Performance Monitor or Perfmon for short. Using Perfmon, you can interactively monitor charts containing counters of various performance metrics such as CPU use and DISK activity. --- Which I am sure U haven't done it.

Oracle8 has integrated its own objects into the Perfmon tool - creating a program entry called Oracle8 Performance Monitor. In order gather statistics from Oracle, the performance manager needs to connect to Oracle. This is done via the registry entries mentioned previously in this paper - and creates a security exposure that you can avoid by creating a PERFMON user that simply has CREATE SESSION and SELECT ANY TABLE privileges.

If U have connection problems with Perfmon (i.e. no Oracle Objects are shown) check the file %ORACLE_HOME%\dbs\PERF80.LOG file for error messages.

In order to use Perfmon, it is useful to measure the statistics over a period of time to get an overview of the situation. U can configure Perfmon to save its statistics to a LOG file for later review, and export to an analysis tools such as Excel. This approach is recommended for developing a base line.

Other Aspects
+++++++++++

Memory:

Tuning memory on NT is the single most important area to look at first. Since NT is a 32 bit operating system, it can address 4GB of memory, 2GB of which are reserved for the operating system. Thus there is a maximum of 2GB available to applications (including Oracle). Oracle's memory consumption is primarily a function of the Shared Global Area or SGA. To manage memory use, U need to ensure the SGA must fit into physical memory, and U have a large enough page file. If Ur system is consuming more memory that is physically available, U will observe a phenomenon know as paging. U can isolate paging activity by placing the page file on a separate volume. Using Perfmon, you can watch Memory: page faults/sec, pages input/sec and pages read/sec. If Ur seeing more than 5 page faults per second over time, U have a paging problem. Another easy indication of paging can be found on the Performance Tab of the task manager, which shows Physical and Virtual memory use at a point in time.

To address paging problems U must either reduce consumption (shrink SGA, remove unnecessary services/protocols etc.) and/or add more memory. Do not allow you system to continuously page fault!

CPU:

CPU bottlenecks are easily observed in Perfmon (or Task Manager). If the CPU is consistently over 90% busy during normal processing, U have a CPU problem. U can use Perfmon to see where CPU is spent; it is best that the CPU is spent in USER rather than SYSTEM time. Excess SYSTEM time may indicate unnecessary overhead in the I/O or Oracle subsystems. To address CPU bottlenecks application or SQL tuning may be required. U can also upgrade the speed or number of CPUs to take advantage of multiprocessing. In addition, Oracle8 provides for many operations to be executed in parallel.

I/O:

The overall I/O tuning objective is to spend as little time doing I/O as possible. This is accomplished by providing the system with enough memory and making sure that the I/O that occurs is as fast as possible. To ensure fast I/O, U must avoid having critical files on busy devices. Place REDO logs on separate fast devices, separate DATA and INDEX datafiles and avoid RAID 5 for Oracle files.

In order to monitor disk activity, it is recommended that U use the INIT.ORA parameter timed_statistics and the Perfmon tool. In order to view disk activity in Perfmon, U must enable statistic calculations by executing the command DISKPERF -YE. Use DISKPERF -N to disable statistic gathering.

Within Oracle, the view V$Filestat will provide disk statistics such as number of read/writes and associated timings at the datafile level. This can be used to identify unbalanced I/O across the database.

Use Perform to determine if disk activity is too high for your hardware. Assuming the system is not paging: U must look at the PhysicalDisk Disk Queue Length; Disk Transfer/Sec and %Disk Time to see if Ur attempting to exceed your disk's maximum transfer rate.

Hope this helps.

-Suhas