Re: Database slow - drop and recreate indexes
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.
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