System Performance Issue

[Youlette Etienne_2]

Hello Everyone,

First, the server specs:
n4000 development server
4 x 875-Mhz CPUs
4 Gb memory
4 x 75 gb cx600 LUNs RAID 5
3 x 21 gb LUNs RAID 1 for oracle log files
The server is a datwarehouse with 2 databases. Sine this is a dev server, only 1 or 2 developers are logged on. Once a month, jobs are executed to update the databases. A description of one of the jobs is as follows:
The job is creating/writing records (approximately 800,000) to the STAGE_MM table in ODWO as it also creates any necessary dimension table record as well, via the lookup common keys stored procedures.
On first implementation, this job completed in 2 hours. The following month, the job took more than 10 hours to complete 12,000 records. There were no ssytem performance issues, available memory was at 2 gb, system load was less than 1, and the LUN I/O for this table was at 5% utilization, nothing in I/O queue, or in wait state. Oracle's response was:
This event signifies that the user process is reading buffers into the SGA buffer cache and is waiting for a physical I/O call to return.
This call differs from a scattered read, because a sequential read is reading data into contiguous memory space. A sequential read is usually a single-block read.

Single block I/Os are usually the result of using indexes. Rarely, full table scan calls could get truncated to a single block call due to extent boundaries, or buffers already present in the buffer cache.
Complete tracing was no enabled. After killing the job and rebooting the server, the job completed in 2 hours as before.
Any ideas on what was occurring other than what oracle has indicated?

[Sanjay Kumar Suri]

You mentioned two databases on the server. What is the other database and what activity was happening during this period?

sks

[Stefan Farrelly · Accepted solution]

Doesnt sound like an OS issue to me. More likely something with Oracle - perhaps the SGA space had become badly fragmented since the last time your DB was restarted. Next time before you run this large job restart the DB beforehand, or as you have plenty of free RAM increase the size of the SGA to reduce possibly fragmentation.

[Youlette Etienne_2]

Thanks for your reply. One database processes the data and then moves the data to the second database where it is queried by users (of which there are none since this is still in development). The job was still in the processing data stage, thus the second db was simply waiting and not doing anything.

My thoughts were also on how Oracle was managing it's memory regions. I wll share this thought with the dba as well.

[Nicolas Dumeige]

Etienne,

(You can trace the procedure with the PL/SQL profiler :
http://www.orafaq.com/scripts/plsql/profiler.txt)

When you talk about event, you're right about the sequential read, that's an index access. Does the PL/SQL code access a table while modifying it ? Have you check the stats on the index ?

Next time, before rebooting the server, it would be best to shutdown the instance and restart it to see if that's a system or an Oracle issue. You could also bring the second instance down to eliminate another source of trouble.

Cheers

Nicolas

[Nicolas Dumeige]

While I was writting, you posted your answer.

You said "The job was still in the processing data stage, thus the second db was simply waiting and not doing anything."

So the database was doing nothing, therefore, how did you get a sequential read wait event from ?

[Mel_12]

Etienne,

If timed_statistics have been turned on and v$system_event, v$session_event, v$session_wait, v$waitstat have all been collected and you have narrowed your bottleneck to DB File Sequential Read in Oracle, this indicates many index reads and the the solution is to tune the PLSQL code especially the joins. As someone suggested, have the dba analyze the indexes and validate their structure. Check out the leaf nodes, height etc. Rebuild if necessary especially if the height is at 3 or more.

Regards,
Mel

[Youlette Etienne_2]

Everyone, thanks for your responses.

Right now, the same process is running and creating the same problem. One thing that I have noticed is that there are 29 TIME_WAIT states on the server. I have only noticed this 30 minutes ago. Any ideas?