IDS Forum

Re: Database slow - drop and recreate indexes

Posted By: Art Kagel
Date: Sunday, 3 July 2016, at 12:06 p.m.

In Response To: Re: Database slow - drop and recreate indexes (LARRY SORENSEN)

For extents, query sysmaster.sysextents counting the number of extents per
table, order by the count, and look for tables with many extents. Also
tables with more than a few extents but you know that multiple extents must
be accessed in order to fulfill a typical query.

You can defragment using the API functions in 11.70+.

If you want to check for wasted space, email me directly for a script. This
can often be solved by moving the table to a different page size and
enabling MAX_FILL_DATA_PAGES for variable length rows. I have another
script to determine the best page size.

Art

On Jul 3, 2016 11:56 AM, "LARRY SORENSEN" <LSORENSEN25@msn.com> wrote:

> I appreciate both of your comments Art and Fernando. They both make sense.
> So,
> if the new indexes are not the cure, what are some steps for implementing
> some
> of your suggestions?
>
> 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.
>
>

--94eb2c05665c698e390536bd68b2

Messages In This Thread

• Database slow - drop and recreate indexes
  LARRY SORENSEN -- Saturday, 2 July 2016, at 11:41 a.m.
  • Re: Database slow - drop and recreate indexes
    Art Kagel -- Saturday, 2 July 2016, at 10:38 p.m.
    • Re: Database slow - drop and recreate indexes
      LARRY SORENSEN -- Sunday, 3 July 2016, at 11:56 a.m.
      • Re: Database slow - drop and recreate indexes
        Art Kagel -- Sunday, 3 July 2016, at 12:06 p.m.
    • Re: Database slow - drop and recreate indexes
      LARRY SORENSEN -- Sunday, 3 July 2016, at 12:00 p.m.
      • Re: Database slow - drop and recreate indexes
        Art Kagel -- Sunday, 3 July 2016, at 12:09 p.m.
      • Re: Database slow - drop and recreate indexes
        BENJAMIN THOMPSON -- Monday, 4 July 2016, at 7:29 a.m.
  • Re: Database slow - drop and recreate indexes
    Fernando Nunes -- Sunday, 3 July 2016, at 7:16 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.