[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Poor database performace due to Segment Fragmentation

Posted on 2011-04-25
Medium Priority
Last Modified: 2012-05-11
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.

Question by:Waqasulhaq
  • 3
  • 2
LVL 40

Expert Comment

ID: 35460633
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.

Author Comment

ID: 35460752
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.
LVL 40

Expert Comment

ID: 35461771
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?


Author Comment

ID: 35462186
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.


LVL 40

Accepted Solution

mrjoltcola earned 2000 total points
ID: 35467152
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.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question