Database slow - drop and recreate indexes

Solaris 10 IDS 11.50.FC7 Our database has been running slow on processes that used to be much quicker. We dropped distrib. We updated statistics low and high. We ran oncheck -cI with no errors. Then.. We unloaded and loaded the data into a test instance with less resources on similar hardware, and the processes ran quickly as they used to. That lead us to believe that we still had problems with the indexes, so we have decided to drop and recreate all of the indexes. How does that sound to all? Are there any other maintenance things that need to be done with recreating indexes? Larry ________________________________ From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Andreas Legner <andreas.legner@de.ibm.com> Sent: Wednesday, June 29, 2016 5:12 AM To: ids@iiug.org Subject: Re: Cannot add a new chunk to the DBspace [37328] Correct, and this root chunk only has 8 free pages left which, even if=20 contiguous, might be too little - depends a bit on on your chunk path=20 lengths which, in v7, still used to impact the number of chunks fitting on = a single reserved page. Go and find some table in root dbspace, with an extent in root chunk (#1), = that you can drop and later recreate after you've added the chunk.=20 Alternatively, if you have your logical logs in rootdbs, find and drop one = residing in root chunk. HTH, Andreas From: "Keith Simmons" <smiley73@gmail.com> To: ids@iiug.org Date: 29.06.2016 12:09 Subject: Re: Cannot add a new chunk to the DBspace [37327] Sent by: ids-bounces@iiug.org Pushpa=20 >From recollection of a similar error (many years ago) the free space=20 required must be in the original root chunk and not in any additional=20 chunk.=20 Keith=20 On 29 June 2016 at 10:57, PUSHPA KUMARA <pushpa@cybersoft.lk> wrote:=20 > Hi Andreas,=20 >=20 > this instance has total 78 data chunks and 55 chunks for this dbspace=20 > and root chunk has free space as bellows=20 > 70000028006e2a8 1 1 50 65475 8 PO- /dblinks/rootpr=20 > 70000028006e3c0 1 1 50 65475 0 MO- /dblinks/rootmr=20 > 700000280cd45a0 75 1 25 131072 107509 PO- /dblinks/rootpr2=20 > 700000280cd4a00 75 1 25 131072 0 MO- /dblinks/rootmr2=20 >=20 > thanks=20 > Pushpa=20 >=20 >=20 >=20 >=20 ***************************************************************************= ****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20 >=20 >=20 --001a11469dec21c0e6053667f219=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

It's not absolutely clear if after you drop/create the indexes the speed was ok? If yes, it sounds a bit strange, but there are a few cases where it can make sense. In particular some type of indexes may become relatively inneficient IF you do large range scans. Don't get me wrong, but what you did is not ideal. You may have solved a problem, but you didn't identify the cause. That usually translate into hitting the problem again sometime in the future. While I know and understand this is not always possible, and that in most cases people can't wait enough to deeply investigate a problem, it's always best to study the process that has the error and identify where it's taking too long or what's dragging it down. Also not that by creating the indexes again, the database has run the statistics. It could be (although I doubt it) that you STATS policy is incorrect. But honestly, recreating the indexes is not usually necessary to gain performance. Again, for some types of indexes and index usage it can happen. Ideally an index should be "physically" ordered... and index is always ordered by the key. But many times, the ROWIDs for a key, or a sequence of keys are very spread acrosss the disk. When you recreate them, they tend to be ordererd (within each key and sequence of keys). This is very beneficial if you have lot's of entries for the same keys or if you do large range scans. Version 11.70 introduced a feature that minimizes the importance of this factor. it's called "SKIP SCAN" but many times it's hard to get it into the query plan. But this basically means that when we do a key-first scan (we first get the ROWIDs from the index, and then we get the data from the rows), fetching the row data is much more efficient because we first order the rowids. And that means we get the data rows in a "physical ordered manner". And by doing so we take huge advantage of the several cache layers (disk, controllers etc.) Not however that all this is rather irrelevant for very select index lookups (keys with very few ROWIDs associated with them) Regards. On Sat, Jul 2, 2016 at 4:41 PM, LARRY SORENSEN <LSORENSEN25@msn.com> wrote: > Solaris 10 > > IDS 11.50.FC7 > > Our database has been running slow on processes that used to be much > quicker. > > We dropped distrib. > > We updated statistics low and high. > > We ran oncheck -cI with no errors. > > Then.. > > We unloaded and loaded the data into a test instance with less resources on > similar hardware, and the processes ran quickly as they used to. > That lead us to believe that we still had problems with the indexes, so we > have decided to drop and recreate all of the indexes. > > How does that sound to all? > Are there any other maintenance things that need to be done with recreating > indexes? > > Larry > ________________________________ > From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Andreas > Legner > <andreas.legner@de.ibm.com> > Sent: Wednesday, June 29, 2016 5:12 AM > To: ids@iiug.org > Subject: Re: Cannot add a new chunk to the DBspace [37328] > > Correct, and this root chunk only has 8 free pages left which, even if=20 > contiguous, might be too little - depends a bit on on your chunk path=20 > lengths which, in v7, still used to impact the number of chunks fitting on > = > > a single reserved page. > > Go and find some table in root dbspace, with an extent in root chunk (#1), > = > > that you can drop and later recreate after you've added the chunk.=20 > Alternatively, if you have your logical logs in rootdbs, find and drop one > = > > residing in root chunk. > > HTH, > Andreas > > From: "Keith Simmons" <smiley73@gmail.com> > To: ids@iiug.org > Date: 29.06.2016 12:09 > Subject: Re: Cannot add a new chunk to the DBspace [37327] > Sent by: ids-bounces@iiug.org > > Pushpa=20 > > >From recollection of a similar error (many years ago) the free space=20 > required must be in the original root chunk and not in any additional=20 > chunk.=20 > > Keith=20 > > On 29 June 2016 at 10:57, PUSHPA KUMARA <pushpa@cybersoft.lk> wrote:=20 > > > Hi Andreas,=20 > >=20 > > this instance has total 78 data chunks and 55 chunks for this dbspace=20 > > and root chunk has free space as bellows=20 > > 70000028006e2a8 1 1 50 65475 8 PO- /dblinks/rootpr=20 > > 70000028006e3c0 1 1 50 65475 0 MO- /dblinks/rootmr=20 > > 700000280cd45a0 75 1 25 131072 107509 PO- /dblinks/rootpr2=20 > > 700000280cd4a00 75 1 25 131072 0 MO- /dblinks/rootmr2=20 > >=20 > > thanks=20 > > Pushpa=20 > >=20 > >=20 > >=20 > >=20 > > ***************************************************************************= > ****=20 > > > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > >=20 > >=20 > > --001a11469dec21c0e6053667f219=20 > > > ***************************************************************************= > ****=20 > > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a11405dccdaeba60536b95acc