Oracle Database Discussions

Hello,

RHEL 4
Oracle 10.2.0.4
Attached to a DAS (partition is 91% full) RAID 5

Over the past few weeks my production database performance has majorly degraded. I have not made any application, OS, or database changes (I was on vacation!). I have started troubleshooting, but need some more tips as to what else I can check.

My users run a query against the database, and for a table with only 40,000 rows, it will take about 2 minutes before the results return. For a table with 12 million records, it takes about 10 minutes or more for the query to complete. If I run a script that counts/displays a total record count for each table in the database as well as a total count of all records in the database (~15,000,000 records total), the script either takes about 45 minutes to complete or sometimes it just never completes. The Linux partition on my DAS is currently 91% full. I do not have Flashback or auditing enabled.

These are some things I tried/observed:

I shut down all applications/servers/connections to the database and then restarted the database. After starting the database, I monitored the DAS interface, and the CPU utilization spiked to 100% and never goes down, even with no users/application trying to connect to the database. The alert.log file contains these errors:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code arguments: [ttcdrv-recursivecall]
ORA-03135: connection lost contact
ORA-06512: at "CTXSYS.SYNCRN", line 1

The database still starts, but the performance is bad. From the error above and after checking performance in EM, I see there are a lot of sync index jobs running by each of the schemas and the db sequential file read is high. There is a job to resync the indexes every 5 minutes. I am going to try disabling these jobs tihs afternoon to see what happens with the CPU utilization. If it helps, I will try adjusting the job from running every 5 minutes to something like every 30 minutes. Is there a way to defrag the CONTEXT indexes? REBUILD?

I'm not sure if I am running down the right path or not. Does anyone have any other suggestions as to what I can check? My SGA_TARGET is currently set to 880M and the SGA_MAX_SIZE is 2032M. Would it also help for me to increase the SGA_TARGET to the SGA_MAX_SIZE; thus increasing the amount of space allocated to the buffer cache? I have ASMM enabled and currently this is what is allocated:
Shared Pool = 18.2%
Buffer Cache = 61.8%
Large Pool = 16.4%
Java Pool = 1.8%
Other = 1.8%

I also ran ADDM and these were the results of my Performance Analysis:
34.7% The throughput of the I/O subsystem was significantly lower than expected (when I clicked on this it said to either implement ASM or stripe using SAME methodology...we are already using RAID5)
31% SQL statements consuming significant database time were found (I cannot make application code changes, and my database consists entirely of INSERT statements...there are never any deletes or updates. I see that the updates that are being made were by the index resyncing job to the various DR$ tables)
18% Individual database segments responsible for significant user I/O wait were found
15.9% Individual SQL statements responsible for significant user I/O wait were found
8.4% PL/SQL execution consumed significant database time

I also recently ran a SHRINK on all possible tablespace as recommended in EM, but that did not seem to help either.

Please let me know if I can provide any other pertinent information to solve the poor I/O problem. I am leaning toward thinking it has to do with the index sync job stepping on itself...the job cannot complete in 5 minutes before it tries to kick off again...but I could be completely wrong! What else can I check to figure out why I have 100% CPU utilization, with no users/applications connected? Thank you!

Mimi

Edited by: Mimi Miami on Jul 25, 2009 10:22 AM