Re: Database slow - drop and recreate indexes

Also, what are some things that I can monitor to help determine where the slowness is coming from? Larry ________________________________ From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Art Kagel <art.kagel@gmail.com> Sent: Saturday, July 2, 2016 8:38 PM To: ids@iiug.org Subject: Re: Database slow - drop and recreate indexes [37353] It could be inefficient indexes. It could also be table fragmentation, fragmented tablespace tablespace pages, wasted space on data pages due to deletes or variable length columns, or any of several other things. Worth a shot rebuilding indexes on some of the most affected tables to see if it helps. Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com<http://www.askdbmgt.com>; ASK Database Management - Home<http://www.askdbmgt.com/>; www.askdbmgt.com This is the site for Art S. Kagel's consultancy. The soaring majesty and beauty in the image above hides the complex ecology and detail of its existence. Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sat, Jul 2, 2016 at 11:41 AM, 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. > > --001a1144b7d08195340536b21bab ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

First, compare query plans on prod versus you test system. If they are different look for causes of that difference. Different distributions levels or freshness for example. More versus fewer levels in indexes, etc. Art On Jul 3, 2016 12:01 PM, "LARRY SORENSEN" <LSORENSEN25@msn.com> wrote: > Also, what are some things that I can monitor to help determine where the > slowness is coming from? > > Larry > > ________________________________ > From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Art Kagel > <art.kagel@gmail.com> > Sent: Saturday, July 2, 2016 8:38 PM > To: ids@iiug.org > Subject: Re: Database slow - drop and recreate indexes [37353] > > It could be inefficient indexes. It could also be table fragmentation, > fragmented tablespace tablespace pages, wasted space on data pages due to > deletes or variable length columns, or any of several other things. Worth a > shot rebuilding indexes on some of the most affected tables to see if it > helps. > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com<http://www.askdbmgt.com>; > ASK Database Management - Home<http://www.askdbmgt.com/>; > www.askdbmgt.com > This is the site for Art S. Kagel's consultancy. The soaring majesty and > beauty in the image above hides the complex ecology and detail of its > existence. > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Sat, Jul 2, 2016 at 11:41 AM, 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. > > > > > > --001a1144b7d08195340536b21bab > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --94eb2c18a9c253eb060536bd70df

From what I'm reading you've gone down the route of rebuilding indices and other actions without really knowing what the problem is. Of course there could be something I don't know here. I would hold off any other actions until you know what's wrong. Do you have a large ready queue ('onstat -g rea')? What state are your user threads in ('onstat -u'): is there any waiting on buffers, locks or mutexes? Maybe switch on SQLTrace, even briefly, and use 'onstat -g his' to see what queries are taking a long time. SQLTrace docs for version 11.50: https://www.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/com.ibm.admin.doc/ids _admin_1129.htm I'd also recommend this during a period of slowness: onstat -zWait for several minutes: onstat -g ppf | sort -nk 10 | tail If you have installed partn (IIUG software area) you can use it to translate the part numbers to database objects easily otherwise you'll have to look them up in systables, sysindices etc. manually. onstat -g ppf | sort -nk 10 | tail | partn -x -k 1 This will then show you where your top "buffered reads" are which is a good way of identifying where a problem lies. If you can match that to some SQL you're on your way to solving the problem. I usually find that the checks above are enough to diagnose where a problem might lie and it is then more obvious where to check further (specific distribution out of date, poor query plan, poor SQL, missing index, lock contention, failed disk etc.). Ben.