Poor database performace due to Segment Fragmentation

Posted on 2011-04-25
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
    LVL 40

    Expert Comment

    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

    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

    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

    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

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now