Poor database performace due to Segment Fragmentation

Hello all,

We have a database where we consistently see high cpu/ io wait times. e.g.

Event      Waits      Time(s)      Avg Wait(ms)      % Total Call Time      Wait Class
CPU time             12,596             61.6       
db file sequential read      475,458      4,322      9      21.1      User I/O
db file scattered read      243,545      2,618      11      12.8      User I/O
log file sync      354,624      1,064      3      5.2      Commit
log file parallel write      432,639      1,039      2      5.1      System I/O

Also we see a bunch of sql statements with slow performance, but I don't see anything in the explain plans (missing indexes/high cost etc..)

The one thing that I do see in our database is the tables are fragmented. EG. we have a table that is using 16 gigs of space but the reclaimable space is 8+GB.

Would this be causing I/O and memory issues?

Please let me know if more info is required to diagnose this problem.

Who is Participating?
mrjoltcolaConnect With a Mentor Commented:
Waits are normal. Historically, I've had UNIX admins studying iostat/vmstat/sar reports and all of the sudden they become concerned with "high waits". High waits are normal for a database server. Specifically, if you have multiple CPUs and a lot of IO, you will see high waits. For some reason we see this a lot on Solaris. This is normal and you should only be concerned if you start seeing actual degradation of response time and/or the IO due to swapping becomes a problem. I wouldn't increase SGA unless you see a real need.

It is good that you created missing indexes and are being proactice about this. I'd limit the work to a shrink of the large table. It is an online operation and wont impact the uptime (though it may run a long time). Otherwise, I normally don't rebuild indexes on tables with high DML, because the index will just gravitate its way back to the same state. Indexes are designed to work like this. Otherwise, I'd focus on the top running queries for my proactive tuning.
I doubt it. 8GB vs 16GB is not on any order that should make more than a linear difference if compacted. More likely the issue can be improved by increasing the SGA / buffer cache. Do you have available RAM? That would be my first tweak.
WaqasulhaqAuthor Commented:
We do have extra memory, and ADDM also keeps advising us to increase the memory. I was just wondering if an I/O issue could be a bottleneck. Also, the size that ADDM asks me to set fluctuates. 2 weeks ago it was telling me 25GB for SGA target, now it is up to 35GB for SGA target. Currently our sga_target is set to 17GB.

I am also seeing statments in addm like the one below::
Rationale      SQL statement with SQL_ID "gccj7ygh4pvxn" was executed 92338 times and had an average elapsed time of 0.0049 seconds.
Rationale      Average time spent in User I/O wait events per execution was 0.0045 seconds.

Since shrinking the segments is easier, I was wondering if I should shrink the segments before proceeding to the memory, I/O issues.
Reorganizing a table can certainly improve things, I would start with a table "shrink", but consider a shrink is going to result in linear order space reduction and therefore linear order io improvement. We usually only find significant performance improvement by quadratic / exponential order tuning (such as missing indexes, etc.)

Are you actually having database performance problems, or are you just concerned with waits? Wait time is normal, and a busy database will have a lot of io / waits, etc. The question is whether the db is performing to expectations?

WaqasulhaqAuthor Commented:
The following were my concerns:

1. Should I change the SGA size? ADDM keeps advising me to, but i do not see why I need to add space. We only have about 700 active sessions at a single time with on a 3 node RAC cluster (10G). the SGA_TARGET has been  set to 17GB on all 3.

2.  Would shrinking the objects actually improve the general database health as well as performance.

3. I am trying to be proactive with figuring out the problems in the database. If the waits are happening for a valid reason, it is fine, but it the waits are because of an I/O or memory issue, then I would like to fix it.

We used have a lot of missing indexes, but I was able to create the indexes and I don't see long waits because of full table scans. We do have a lot of horribly written code which I have no control over.
I will take your advice and shrink indexes and tables tonight.

I have attached a short snapshot of my awreport as well.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.