Re: Index strategy
From: <phil_herring_at_yahoo.com.au>
Date: Wed, 2 Jun 2010 15:31:06 -0700 (PDT)
Message-ID: <7556d6c2-096f-47ed-a6ac-9c37de7b7660_at_j36g2000prj.googlegroups.com>
I find that looking at tables and indexes on their own doesn't help much; really what you want to look at is the SQL running against the DB. One simple approach involves looking at the SGA stats for the DB after it's been up for a while (at least a few weeks), and identifying the SQL that has the highest total elapsed time. Pick off the top 5, work on those, put the changes in, clear the stats, and repeat. However, you have to be sensible; some SQL just takes a long time to run and may not need tuning. Batch jobs often fall into this category.
Date: Wed, 2 Jun 2010 15:31:06 -0700 (PDT)
Message-ID: <7556d6c2-096f-47ed-a6ac-9c37de7b7660_at_j36g2000prj.googlegroups.com>
I find that looking at tables and indexes on their own doesn't help much; really what you want to look at is the SQL running against the DB. One simple approach involves looking at the SGA stats for the DB after it's been up for a while (at least a few weeks), and identifying the SQL that has the highest total elapsed time. Pick off the top 5, work on those, put the changes in, clear the stats, and repeat. However, you have to be sensible; some SQL just takes a long time to run and may not need tuning. Batch jobs often fall into this category.
If you're lazy, TOAD has a built-in report that will help you do exactly this.
- Phil