Update statistics issue
A user on IDS 11.50 (HP-UX) asked how to speed up UPDATE STATISTICS on a 23-million-row table that took ~15 minutes. Respondents said that isn't unreasonable for that size, and Art Kagel explained that plain UPDATE STATISTICS (LOW) doesn't build distributions anyway; he recommended reading John Miller's tuning paper, setting PDQPRIORITY, PSORT_NPROCS and ample DS_TOTAL_MEMORY so sorts stay in memory, and using his dostats utility to generate optimal LOW/MEDIUM/HIGH commands. He also showed how to check the sysadmin:ph_task AUS evaluator/refresh tasks; the poster found both enabled, meaning stats were also being updated automatically, duplicating the cron job. The thread ends there with no confirmed outcome.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All, I am having one table and contains data 23326189 and taking about 15 mins to complete update statistics Is there any opportunities to minimize this time? thank you very much --001a11c35338ab010704fccc4622
Which update statistics ? Low Medium High ? Marcus Haarmann ----- Ursprüngliche Mail ----- Von: "medkba" <medkba@gmail.com> An: ids@iiug.org Gesendet: Freitag, 27. Juni 2014 09:30:00 Betreff: Update statistics issue [33289] Hi All, I am having one table and contains data 23326189 and taking about 15 mins to complete update statistics Is there any opportunities to minimize this time? thank you very much --001a11c35338ab010704fccc4622 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Marcus Hararmann, It is using default. I think, it is Low Thank you very much On Fri, Jun 27, 2014 at 4:07 PM, Marcus Haarmann <marcus.haarmann@midoco.de> wrote: > Which update statistics ? Low Medium High ? > > Marcus Haarmann > > ----- Ursprüngliche Mail ----- > > Von: "medkba" <medkba@gmail.com> > An: ids@iiug.org > Gesendet: Freitag, 27. Juni 2014 09:30:00 > Betreff: Update statistics issue [33289] > > Hi All, > > I am having one table and contains data 23326189 and taking about 15 mins > to complete update statistics > > Is there any opportunities to minimize this time? > > thank you very much > > --001a11c35338ab010704fccc4622 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c265403eb0dd04fccce433
You don't mention O/S, IDS version, table layout (width, special columns etc), but for a 23 million row table (of say 300 bytes width) I would not consider 15 minutes to be an excessive time, especially on a older IDS or O/S and possibly aging hardware :-) Keith On 27 June 2014 08:30, medkba <medkba@gmail.com> wrote: > Hi All, > > I am having one table and contains data 23326189 and taking about 15 mins > to complete update statistics > > Is there any opportunities to minimize this time? > > thank you very much > > --001a11c35338ab010704fccc4622 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b670b377b5eb104fccd0e65
Hi, Sorry to give you details OS: HP-UX server1 B.11.31 U ia64 2807235852 unlimited-user license Informix: 11.50 FC9 Table does not have special columns. Char and date time only Please let me know if any more information On Fri, Jun 27, 2014 at 4:25 PM, Keith Simmons <smiley73@gmail.com> wrote: > You don't mention O/S, IDS version, table layout (width, special columns > etc), > but for a 23 million row table (of say 300 bytes width) I would not > consider 15 > minutes to be an excessive time, especially on a older IDS or O/S and > possibly > aging hardware :-) > > Keith > > On 27 June 2014 08:30, medkba <medkba@gmail.com> wrote: > > > Hi All, > > > > I am having one table and contains data 23326189 and taking about 15 mins > > to complete update statistics > > > > Is there any opportunities to minimize this time? > > > > thank you very much > > > > --001a11c35338ab010704fccc4622 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --047d7b670b377b5eb104fccd0e65 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c36806fa1a2c04fccd6de6
How are you running update statistics for that table? What environment variables have you set? Are you using PDQPRIORITY? What engine version and edition are you using? How many cores are on the machine and how many CPU VPs are configured? Have you read John Miller's paper in optimizing update statistics runs? Art Art S. Kagel, Principal Consultant ASK Database Management 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 Fri, Jun 27, 2014 at 3:30 AM, medkba <medkba@gmail.com> wrote: > Hi All, > > I am having one table and contains data 23326189 and taking about 15 mins > to complete update statistics > > Is there any opportunities to minimize this time? > > thank you very much > > --001a11c35338ab010704fccc4622 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c3eaaa3f037b04fccfcec4
Running from cron jobs daily from shell script Not using PDQPRIORITY 23 CPUs 8 cpu vps is used increased from 6 to 8 no improvement Please let me if require more information Thank you very much On 27 Jun 2014 19:43, "Art Kagel" <art.kagel@gmail.com> wrote: > How are you running update statistics for that table? What environment > variables have you set? Are you using PDQPRIORITY? What engine version > and edition are you using? How many cores are on the machine and how many > CPU VPs are configured? > > Have you read John Miller's paper in optimizing update statistics runs? > > Art > > Art S. Kagel, Principal Consultant > ASK Database Management > > 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 Fri, Jun 27, 2014 at 3:30 AM, medkba <medkba@gmail.com> wrote: > > > Hi All, > > > > I am having one table and contains data 23326189 and taking about 15 mins > > to complete update statistics > > > > Is there any opportunities to minimize this time? > > > > thank you very much > > > > --001a11c35338ab010704fccc4622 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11c3eaaa3f037b04fccfcec4 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0158b87c65336e04fcd0b829
"from a shell script" wasn't what I was looking for. Are you running plan
UPDATE STATISTICS? UPDATE STATISTICS LOW? UPDATE STATISTICS MEDIUM?
UPDATE STATISTICS HIGH? Combinations of multiple commands? Are yourunning these in dbaccess? If so are you running against just this one
table in each command or against the entire database? Are you using my
dostats utility to generate an optimal set of commands?
OK, first thing to do then is to read John's paper. Link here:
http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/miller
/0203miller.html#section3
Quick-and-dirty:
export PDQPRIORITY=100 # Or as high as you dare without disrupting
production resource requirements.
export PSORT_NPROCS=16 # I know the max documented is 10, trust me.
Make sure that you have lots of memory allocated to DS_TOTAL_MEMORY to
allow for all sorting to be accomplished in memory. If you want to know how
much memory you will need and you haven't zero'd out your server stats
since the last time you tried running the update statistics (ie onstat -z)
then the sysmaster:sysprofile table has a row containing the size of the
largest sort that's been executed in KB:
select value from sysmaster:sysprofile where name = 'maxsortspace';
I think that I remember that you are using 11.50 so you can't take
advantage or LOW sampling.
BTW, in another reply you said you are only runing vanilla UPDATE
STATISTICS with no modifiers. This command does not update the data
distributions for the table, it only updates a few columns in systables,
syscolumns, sysfragments, and sysindices/sysindexes like nrows and npused
in systables, colmin and colmax in syscolumns, nlevels, nleaves, uniq,
clust, and nrows in sysindices. You need to run a combination of LOW,
MEDIUM, and HIGH on individual sets of columns to get really useful data
distributions in minimum time. If you use my dostats utility, as do
hundreds of Informix sites, it will take care of that for you.
BTW, have you disabled Auto Update Statistics (AUS)? Because if not then
AUS is running every night updating stats for you (almost as well as
dostats would) so you should not have to be running update statistics
manually (or from cron) anyway.
Art
Art S. Kagel, Principal Consultant
ASK Database Management
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 Fri, Jun 27, 2014 at 8:48 AM, medkba <medkba@gmail.com> wrote:
> Running from cron jobs daily from shell script
> Not using PDQPRIORITY
> 23 CPUs
> 8 cpu vps is used increased from 6 to 8 no improvement
>
> Please let me if require more information
>
> Thank you very much
> On 27 Jun 2014 19:43, "Art Kagel" <art.kagel@gmail.com> wrote:
>
> > How are you running update statistics for that table? What environment
> > variables have you set? Are you using PDQPRIORITY? What engine version
> > and edition are you using? How many cores are on the machine and how many
> > CPU VPs are configured?
> >
> > Have you read John Miller's paper in optimizing update statistics runs?
> >
> > Art
> >
> > Art S. Kagel, Principal Consultant
> > ASK Database Management
> >
> > 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 Fri, Jun 27, 2014 at 3:30 AM, medkba <medkba@gmail.com> wrote:
> >
> > > Hi All,
> > >
> > > I am having one table and contains data 23326189 and taking about 15
> mins
> > > to complete update statistics
> > >
> > > Is there any opportunities to minimize this time?
> > >
> > > thank you very much
> > >
> > > --001a11c35338ab010704fccc4622
> > >
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --001a11c3eaaa3f037b04fccfcec4
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --089e0158b87c65336e04fcd0b829
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e01182b54c5879104fcd123c5
Yes, i have running update statistics one of the database i.e. most used
the application
Run UPDATE STATISTICS LOW
Ok, i will read this article
Dosstat runs weekly once
This is new hp blade server with 32gb memory. It means that hardware very
powerful but statistics job running slow then the question aries why slow
and hard to convenience management to change different way of running.
How do i check aus running
Thank you
On 27 Jun 2014 21:18, "Art Kagel" <art.kagel@gmail.com> wrote:
> "from a shell script" wasn't what I was looking for. Are you running plan
> UPDATE STATISTICS? UPDATE STATISTICS LOW? UPDATE STATISTICS MEDIUM?
> UPDATE STATISTICS HIGH? Combinations of multiple commands? Are you> running these in dbaccess? If so are you running against just this one
> table in each command or against the entire database? Are you using my
> dostats utility to generate an optimal set of commands?
>
> OK, first thing to do then is to read John's paper. Link here:
>
>
>
>
http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/miller
/0203miller.html#section3
>
> Quick-and-dirty:
>
> export PDQPRIORITY=100 # Or as high as you dare without disrupting
> production resource requirements.
> export PSORT_NPROCS=16 # I know the max documented is 10, trust me.
> Make sure that you have lots of memory allocated to DS_TOTAL_MEMORY to
> allow for all sorting to be accomplished in memory. If you want to know how
> much memory you will need and you haven't zero'd out your server stats
> since the last time you tried running the update statistics (ie onstat -z)
> then the sysmaster:sysprofile table has a row containing the size of the
> largest sort that's been executed in KB:
>
> select value from sysmaster:sysprofile where name = 'maxsortspace';>
> I think that I remember that you are using 11.50 so you can't take
> advantage or LOW sampling.
>
> BTW, in another reply you said you are only runing vanilla UPDATE
> STATISTICS with no modifiers. This command does not update the data
> distributions for the table, it only updates a few columns in systables,
> syscolumns, sysfragments, and sysindices/sysindexes like nrows and npused
> in systables, colmin and colmax in syscolumns, nlevels, nleaves, uniq,
> clust, and nrows in sysindices. You need to run a combination of LOW,
> MEDIUM, and HIGH on individual sets of columns to get really useful data
> distributions in minimum time. If you use my dostats utility, as do
> hundreds of Informix sites, it will take care of that for you.
>
> BTW, have you disabled Auto Update Statistics (AUS)? Because if not then
> AUS is running every night updating stats for you (almost as well as
> dostats would) so you should not have to be running update statistics
> manually (or from cron) anyway.
>
> Art
>
> Art S. Kagel, Principal Consultant
> ASK Database Management
>
> 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 Fri, Jun 27, 2014 at 8:48 AM, medkba <medkba@gmail.com> wrote:
>
> > Running from cron jobs daily from shell script
> > Not using PDQPRIORITY
> > 23 CPUs
> > 8 cpu vps is used increased from 6 to 8 no improvement
> >
> > Please let me if require more information
> >
> > Thank you very much
> > On 27 Jun 2014 19:43, "Art Kagel" <art.kagel@gmail.com> wrote:
> >
> > > How are you running update statistics for that table? What environment
> > > variables have you set? Are you using PDQPRIORITY? What engine version
> > > and edition are you using? How many cores are on the machine and how
> many
> > > CPU VPs are configured?
> > >
> > > Have you read John Miller's paper in optimizing update statistics runs?
> > >
> > > Art
> > >
> > > Art S. Kagel, Principal Consultant
> > > ASK Database Management
> > >
> > > 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 Fri, Jun 27, 2014 at 3:30 AM, medkba <medkba@gmail.com> wrote:
> > >
> > > > Hi All,
> > > >
> > > > I am having one table and contains data 23326189 and taking about 15
> > mins
> > > > to complete update statistics
> > > >
> > > > Is there any opportunities to minimize this time?
> > > >
> > > > thank you very much
> > > >
> > > > --001a11c35338ab010704fccc4622
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --001a11c3eaaa3f037b04fccfcec4
> > >
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --089e0158b87c65336e04fcd0b829
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --089e01182b54c5879104fcd123c5
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a1133d1e23364d104fcd1ce86
There are two AUS tasks, the AUS evaluator that determines what tables need
their stats updated and the AUS refresh task that executes the commands
calculated by the evaluator. You can find the task manager records
governing their execution in the sysadmin database in the table ph_task.
Run:
select tk_id, tk_name, tk_enable, tk_next_execution
from sysadmin:ph_task
where tk_execute matches 'aus*';
If tk_enable is set to 't' then the AUS tasks are running. Obviously if
the evaluator is executing but the refresh is not, then stats are not being
updated by AUS and if the refresh is running without the evaluator running
before it then the list of work its doing is probably out-of-date and may
even be empty. The tk_next_execution column will tell you when each is
scheduled to run next. Note that in 11.50 the evaluator was rather
inefficient and could take longer to run than the default difference in the
scheduled run times of the two tasks (1 hour) so that when the refresh
launched the evaluator may not have finished running resulting in the
refresh only processing some of the tables that needed to updated. This
was fixed in 11.70 and again in 12.10 by recoding the evaluator to run
faster and by changing the default scheduling of the tasks so they run
further apart.
Art
Art S. Kagel, Principal Consultant
ASK Database Management
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 Fri, Jun 27, 2014 at 10:05 AM, medkba <medkba@gmail.com> wrote:
> Yes, i have running update statistics one of the database i.e. most used
> the application
> Run UPDATE STATISTICS LOW
> Ok, i will read this article
> Dosstat runs weekly once
> This is new hp blade server with 32gb memory. It means that hardware very
> powerful but statistics job running slow then the question aries why slow
> and hard to convenience management to change different way of running.
> How do i check aus running
> Thank you
> On 27 Jun 2014 21:18, "Art Kagel" <art.kagel@gmail.com> wrote:
>
> > "from a shell script" wasn't what I was looking for. Are you running plan
> > UPDATE STATISTICS? UPDATE STATISTICS LOW? UPDATE STATISTICS MEDIUM?
> > UPDATE STATISTICS HIGH? Combinations of multiple commands? Are you> > running these in dbaccess? If so are you running against just this one
> > table in each command or against the entire database? Are you using my
> > dostats utility to generate an optimal set of commands?
> >
> > OK, first thing to do then is to read John's paper. Link here:
> >
> >
> >
> >
>
>
http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/miller
/0203miller.html#section3
> >
> > Quick-and-dirty:
> >
> > export PDQPRIORITY=100 # Or as high as you dare without disrupting
> > production resource requirements.
> > export PSORT_NPROCS=16 # I know the max documented is 10, trust me.
> > Make sure that you have lots of memory allocated to DS_TOTAL_MEMORY to
> > allow for all sorting to be accomplished in memory. If you want to know
> how
> > much memory you will need and you haven't zero'd out your server stats
> > since the last time you tried running the update statistics (ie onstat
> -z)
> > then the sysmaster:sysprofile table has a row containing the size of the
> > largest sort that's been executed in KB:
> >
> > select value from sysmaster:sysprofile where name = 'maxsortspace';> >
> > I think that I remember that you are using 11.50 so you can't take
> > advantage or LOW sampling.
> >
> > BTW, in another reply you said you are only runing vanilla UPDATE
> > STATISTICS with no modifiers. This command does not update the data
> > distributions for the table, it only updates a few columns in systables,
> > syscolumns, sysfragments, and sysindices/sysindexes like nrows and npused
> > in systables, colmin and colmax in syscolumns, nlevels, nleaves, uniq,
> > clust, and nrows in sysindices. You need to run a combination of LOW,
> > MEDIUM, and HIGH on individual sets of columns to get really useful data
> > distributions in minimum time. If you use my dostats utility, as do
> > hundreds of Informix sites, it will take care of that for you.
> >
> > BTW, have you disabled Auto Update Statistics (AUS)? Because if not then
> > AUS is running every night updating stats for you (almost as well as
> > dostats would) so you should not have to be running update statistics
> > manually (or from cron) anyway.
> >
> > Art
> >
> > Art S. Kagel, Principal Consultant
> > ASK Database Management
> >
> > 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 Fri, Jun 27, 2014 at 8:48 AM, medkba <medkba@gmail.com> wrote:
> >
> > > Running from cron jobs daily from shell script
> > > Not using PDQPRIORITY
> > > 23 CPUs
> > > 8 cpu vps is used increased from 6 to 8 no improvement
> > >
> > > Please let me if require more information
> > >
> > > Thank you very much
> > > On 27 Jun 2014 19:43, "Art Kagel" <art.kagel@gmail.com> wrote:
> > >
> > > > How are you running update statistics for that table? What
> environment
> > > > variables have you set? Are you using PDQPRIORITY? What engine
> version
> > > > and edition are you using? How many cores are on the machine and how
> > many
> > > > CPU VPs are configured?
> > > >
> > > > Have you read John Miller's paper in optimizing update statistics
> runs?
> > > >
> > > > Art
> > > >
> > > > Art S. Kagel, Principal Consultant
> > > > ASK Database Management
> > > >
> > > > 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 Fri, Jun 27, 2014 at 3:30 AM, medkba <medkba@gmail.com> wrote:
> > > >
> > > > > Hi All,
> > > > >
> > > > > I am having one table and contains data 23326189 and taking about
> 15
> > > mins
> > > > > to complete update statistics
> > > > >
> > > > > Is there any opportunities to minimize this time?
> > > > >
> > > > > thank you very much
> > > > >
> > > > > --001a11c35338ab010704fccc4622
> > > > >
> > >
Thank you very much Art
The below output executing the query
tk_id 18
tk_name Auto Update Statistics Evaluation
tk_enable t
tk_next_execution 2014-06-28 01:00:00
tk_id 19
tk_name Auto Update Statistics Refresh
tk_enable t
tk_next_execution 2014-06-28 01:11:00
It means that statistics running automatically. Am i right? I need to
disable both
On Fri, Jun 27, 2014 at 11:39 PM, Art Kagel <art.kagel@gmail.com> wrote:
> There are two AUS tasks, the AUS evaluator that determines what tables need
> their stats updated and the AUS refresh task that executes the commands
> calculated by the evaluator. You can find the task manager records
> governing their execution in the sysadmin database in the table ph_task.
> Run:
>
> select tk_id, tk_name, tk_enable, tk_next_execution
> from sysadmin:ph_task
> where tk_execute matches 'aus*';>
> If tk_enable is set to 't' then the AUS tasks are running. Obviously if
> the evaluator is executing but the refresh is not, then stats are not being
> updated by AUS and if the refresh is running without the evaluator running
> before it then the list of work its doing is probably out-of-date and may
> even be empty. The tk_next_execution column will tell you when each is
> scheduled to run next. Note that in 11.50 the evaluator was rather
> inefficient and could take longer to run than the default difference in the
> scheduled run times of the two tasks (1 hour) so that when the refresh
> launched the evaluator may not have finished running resulting in the
> refresh only processing some of the tables that needed to updated. This
> was fixed in 11.70 and again in 12.10 by recoding the evaluator to run
> faster and by changing the default scheduling of the tasks so they run
> further apart.
>
> Art
>
> Art S. Kagel, Principal Consultant
> ASK Database Management
>
> 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 Fri, Jun 27, 2014 at 10:05 AM, medkba <medkba@gmail.com> wrote:
>
> > Yes, i have running update statistics one of the database i.e. most used
> > the application
> > Run UPDATE STATISTICS LOW
> > Ok, i will read this article
> > Dosstat runs weekly once
> > This is new hp blade server with 32gb memory. It means that hardware very
> > powerful but statistics job running slow then the question aries why slow
> > and hard to convenience management to change different way of running.
> > How do i check aus running
> > Thank you
> > On 27 Jun 2014 21:18, "Art Kagel" <art.kagel@gmail.com> wrote:
> >
> > > "from a shell script" wasn't what I was looking for. Are you running
> plan
> > > UPDATE STATISTICS? UPDATE STATISTICS LOW? UPDATE STATISTICS MEDIUM?
> > > UPDATE STATISTICS HIGH? Combinations of multiple commands? Are you> > > running these in dbaccess? If so are you running against just this one
> > > table in each command or against the entire database? Are you using my
> > > dostats utility to generate an optimal set of commands?
> > >
> > > OK, first thing to do then is to read John's paper. Link here:
> > >
> > >
> > >
> > >
> >
> >
>
>
http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/miller
/0203miller.html#section3
> > >
> > > Quick-and-dirty:
> > >
> > > export PDQPRIORITY=100 # Or as high as you dare without disrupting
> > > production resource requirements.
> > > export PSORT_NPROCS=16 # I know the max documented is 10, trust me.
> > > Make sure that you have lots of memory allocated to DS_TOTAL_MEMORY to
> > > allow for all sorting to be accomplished in memory. If you want to know
> > how
> > > much memory you will need and you haven't zero'd out your server stats
> > > since the last time you tried running the update statistics (ie onstat
> > -z)
> > > then the sysmaster:sysprofile table has a row containing the size of
> the
> > > largest sort that's been executed in KB:
> > >
> > > select value from sysmaster:sysprofile where name = 'maxsortspace';> > >
> > > I think that I remember that you are using 11.50 so you can't take
> > > advantage or LOW sampling.
> > >
> > > BTW, in another reply you said you are only runing vanilla UPDATE
> > > STATISTICS with no modifiers. This command does not update the data
> > > distributions for the table, it only updates a few columns in
> systables,
> > > syscolumns, sysfragments, and sysindices/sysindexes like nrows and
> npused
> > > in systables, colmin and colmax in syscolumns, nlevels, nleaves, uniq,
> > > clust, and nrows in sysindices. You need to run a combination of LOW,
> > > MEDIUM, and HIGH on individual sets of columns to get really useful
> data
> > > distributions in minimum time. If you use my dostats utility, as do
> > > hundreds of Informix sites, it will take care of that for you.
> > >
> > > BTW, have you disabled Auto Update Statistics (AUS)? Because if not
> then
> > > AUS is running every night updating stats for you (almost as well as
> > > dostats would) so you should not have to be running update statistics
> > > manually (or from cron) anyway.
> > >
> > > Art
> > >
> > > Art S. Kagel, Principal Consultant
> > > ASK Database Management
> > >
> > > 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 Fri, Jun 27, 2014 at 8:48 AM, medkba <medkba@gmail.com> wrote:
> > >
> > > > Running from cron jobs daily from shell script
> > > > Not using PDQPRIORITY
> > > > 23 CPUs
> > > > 8 cpu vps is used increased from 6 to 8 no improvement
> > > >
> > > > Please let me if require more information
> > > >
> > > > Thank you very much
> > > > On 27 Jun 2014 19:43, "Art Kagel" <art.kagel@gmail.com> wrote:
> > > >
> > > > > How are you running update statistics for that table? What
> > environment
> > > > > variables have you set? Are you using PDQPRIORITY? What engine
> > version
> > > > > and edition are you using? How many cores are on the machine and
> how
> > > many
> > > > > CPU VPs are configured?
> > > > >
> > > > > Have you read John Miller's paper in optimizing update statistics
> > runs?
> > > > >
> > > > > Art
> > > > >
> > > > > Art S. Kagel, Principal Consultant
> > > > > ASK Database Management
> > > > >
> > > > > Blog: http://informix-myview.blogspot.com/
> > > > >
> > > > > Disclaimer: Please keep in mind that my own opinions are my own
> > > opinions@@NL@
If you would like you large tables running update statistics = low to
run very fast then
you need to read the link below. I= n general this makes each index
which update
data statistic low is run o= n take about 1 minute (unless there is
great data skew).
http=
://www.ibmnosql.com/2011/05/craving-a-bit-of-update-statistics-low-per
forma= nce/
John F. Miller III
STSM, Lead Architect
miller3@= us.ibm.com
503-747-1366
IBM Informix Dynamic Server (IDS)
<= br>-----ids-bounces@iiug.org wrote: -----
>To: ids@iiug.org
>From: "medkba"
>Sent by: ids-bounce= s@iiug.org
>Date: 06/27/2014 07:06AM
>Subject: Re: Update stati= stics issue [33297]
>
>Yes, i have running update statistics o= ne of the database i.e. most
>used the application Run UPDATE STATI= STICS LOW Ok, i will read
>this article Dosstat runs weekly once T= his is new hp blade server
>with 32gb memory. It means that hardware = very powerful but
>statistics job running slow then the question ari= es why slow and
>hard to convenience management to change different = way of running.
>How do i check aus running Thank you On 27 Jun 201= 4 21:18, "Art
>Kagel" <art.kagel@gmail.com> wrote: > "from= a shell script" wasn't
>what I was looking for. Are you running plan= > UPDATE STATISTICS?
>UPDATE STATISTICS LOW? UPDATE STATISTICS M= EDIUM? > UPDATE
STATISTICS
>HIGH? Combinations of multiple comman= ds? Are you > running these in
>dbaccess? If so are you running a= gainst just this one > table in
>each command or against the enti= re database? Are you using my >
>dostats utility to generate an o= ptimal set of commands? > > OK,
>first thing to do then is to= read John's paper. Link here: > > >
>>
>http://=
www.ibm.com/developerworks/data/zones/informix/library/techart
>icle/= miller/0203miller.html#section3 > > Quick-and-dirty: > >
>export PDQPRIORITY=3D100 # Or as high as you dare without disrupting
= >
>production resource requirements. > export PSORT=5FNPROCS= =3D16 # I
know
>the max documented is 10, trust me. > Make sure t= hat you have lots
>of memory allocated to DS=5FTOTAL=5FMEMORY to >= ; allow for all
sorting to
>be accomplished in memory. If you want to= know how > much memory you
>will need and you haven't zero'd out= your server stats > since the
>last time you tried running the u= pdate statistics (ie onstat -z) >
>then the sysmaster:sysprofile = table has a row containing the size
of
>the > largest sort that's= been executed in KB: > > select value
>from sysmaster:syspro= file where name =3D 'maxsortspace'; > > I
think
>that I remem= ber that you are using 11.50 so you can't take >
>advantage or LO= W sampling. > > BTW, in another reply you said you
>are only = runing vanilla UPDATE > STATISTICS with no modifiers. This
>comma= nd does not update the data > distributions for the table, it
>on= ly updates a few columns in systables, > syscolumns,
sysfragments,
&= gt;and sysindices/sysindexes like nrows and npused > in systables,
&= gt;colmin and colmax in syscolumns, nlevels, nleaves, uniq, >
clust,
>and nrows in sysindices. You need to run a combination of LOW,
>>MEDIUM, and HIGH on individual sets of columns to get really
useful>data > distributions in minimum time. If you use my dostats
utili= ty,
>as do > hundreds of Informix sites, it will take care of tha= t for
>you. > > BTW, have you disabled Auto Update Statistics= (AUS)?
>Because if not then > AUS is running every night updatin= g stats for
>you (almost as well as > dostats would) so you shoul= d not have to
be
>running update statistics > manually (or from c= ron) anyway. > >
>Art > > Art S. Kagel, Principal Con= sultant > ASK Database
>Management > > Blog: http://infor= mix-myview.blogspot.com/ > >
>Disclaimer: Please keep in mind= that my own opinions are my own
>opinions > and do not reflect o= n the IIUG, nor any other
>organization with which I am > associa= ted either explicitly,
>implicitly, or by inference. Neither do >= those opinions reflect
>those of other individuals affiliated with a= ny > entity with which
I
>am affiliated nor those of the entities= themselves. > > On Fri, Jun
>27, 2014 at 8:48 AM, medkba <= ;medkba@gmail.com> wrote: > > >
Running
>from cron jobs= daily from shell script > > Not using PDQPRIORITY >
>>= 23 CPUs > > 8 cpu vps is used increased from 6 to 8 no
improvement<= br>> > > > > Please let me if require more information
>= ; > > > Thank
>you very much > > On 27 Jun 2014 19:4= 3, "Art Kagel"
><art.kagel@gmail.com> wrote: > > > &= gt; > How are you running
update
>statistics for that table? What = environment > > > variables have
you
>set? Are you using PD= QPRIORITY? What engine version > > > and
>edition are you u= sing? How many cores are on the machine and how >
>many > >= ; > CPU VPs are configured? > > > > > > Have you rea= d
John
>Miller's paper in optimizing update statistics runs? > &g= t; > > > >
>Art > > > > > > Art S. K= agel, Principal Consultant > > > ASK
>Database Management = > > > > > > Blog:
>http://informix-myview.blogspot= .com/ > > > > > > Disclaimer: Please
>keep in min= d 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 thos= e of other
>individuals affiliated with any > > > entity wi= th which I am
>affiliated nor those of the entities themselves. >= > > > > > On
>Fri, Jun 27, 2014 at 3:30 AM, medkba &= lt;medkba@gmail.com> wrote: >
> >
> > > > > = Hi All, > > > > > > > > I am having one table and=
contains
>data 23326189 and taking about 15 > > mins > &g= t; > > to complete
>update statistics > > > > >= ; > > > Is there any opportunities to
>minimize this time? = > > > > > > > > thank you very much > > >= ; >
>
>> > > --001a11c35338ab010704fccc4622 > >= ; > > > > > > > > > > > >
>>= ; > > > > > > > > > > > > >
>*********************************************************************
<= br>>********** > > > > Forum Note: Use "Reply" to post a re= sponse
in the
>discussion forum. > > > > > > >= > > > > > > >
>--001a11c3eaaa3f037b04fccfcec4= > > > > > > > > > > > > > >= > >
>> >
>**************************************=
*******************************
>********** > > > Forum Not= e: Use "Reply" to post a response in the
>discussion forum. > >= ; > > > > > > > >
>--089e0158b87c65336e04f= cd0b829 > > > > > > > > > >
>****=@@
I have verified my environment. If I have comment these values, any impact
current system
cat onconPROD82 | grep RA_PAGES
#RA_PAGES - The number of pages, as a positive integer, to
RA_PAGES 32
cat onconPROD82 | grep RA_THRESHOLD
#RA_THRESHOLD - The number of pages, as a postive integer, left
RA_THRESHOLD 8
cat onconPROD82 | grep AUTO_READAHEAD
Thank you
On Fri, Jun 27, 2014 at 11:54 PM, John Miller iii <miller3@us.ibm.com>
wrote:
> If you would like you large tables running update statistics = low to
>
> run very fast then
>
> you need to read the link below. I= n general this makes each index
>
> which update
>
> data statistic low is run o= n take about 1 minute (unless there is
>
> great data skew).
>
> http=
>
> ://www.ibmnosql.com/2011/05/craving-a-bit-of-update-statistics-low-per
>
> forma= nce/
>
> John F. Miller III
>
> STSM, Lead Architect
>
> miller3@= us.ibm.com
>
> 503-747-1366
>
> IBM Informix Dynamic Server (IDS)
>
> <= br>-----ids-bounces@iiug.org wrote: -----
>
> >To: ids@iiug.org
>
> >From: "medkba"
>
> >Sent by: ids-bounce= s@iiug.org
>
> >Date: 06/27/2014 07:06AM
>
> >Subject: Re: Update stati= stics issue [33297]
>
> >
>
> >Yes, i have running update statistics o= ne of the database i.e. most
>
> >used the application Run UPDATE STATI= STICS LOW Ok, i will read
>
> >this article Dosstat runs weekly once T= his is new hp blade server
>
> >with 32gb memory. It means that hardware = very powerful but
>
> >statistics job running slow then the question ari= es why slow and
>
> >hard to convenience management to change different = way of running.
>
> >How do i check aus running Thank you On 27 Jun 201= 4 21:18, "Art
>
> >Kagel" <art.kagel@gmail.com> wrote: > "from= a shell script" wasn't
>
> >what I was looking for. Are you running plan= > UPDATE STATISTICS?
>
> >UPDATE STATISTICS LOW? UPDATE STATISTICS M= EDIUM? > UPDATE>
> STATISTICS
>
> >HIGH? Combinations of multiple comman= ds? Are you > running these in
>
> >dbaccess? If so are you running a= gainst just this one > table in
>
> >each command or against the enti= re database? Are you using my >
>
> >dostats utility to generate an o= ptimal set of commands? > > OK,
>
> >first thing to do then is to= read John's paper. Link here: > > >
>
> >>
>
> >http://=
>
> www.ibm.com/developerworks/data/zones/informix/library/techart
>
> >icle/= miller/0203miller.html#section3 > > Quick-and-dirty: > >
>
> >export PDQPRIORITY=3D100 # Or as high as you dare without disrupting
>
> = >
>
> >production resource requirements. > export PSORT=5FNPROCS= =3D16 # I
>
> know
>
> >the max documented is 10, trust me. > Make sure t= hat you have lots
>
> >of memory allocated to DS=5FTOTAL=5FMEMORY to >= ; allow for all
>
> sorting to
>
> >be accomplished in memory. If you want to= know how > much memory you
>
> >will need and you haven't zero'd out= your server stats > since the
>
> >last time you tried running the u= pdate statistics (ie onstat -z) >
>
> >then the sysmaster:sysprofile = table has a row containing the size
>
> of
>
> >the > largest sort that's= been executed in KB: > > select value
>
> >from sysmaster:syspro= file where name =3D 'maxsortspace'; > > I
>
> think
>
> >that I remem= ber that you are using 11.50 so you can't take >
>
> >advantage or LO= W sampling. > > BTW, in another reply you said you
>
> >are only = runing vanilla UPDATE > STATISTICS with no modifiers. This
>
> >comma= nd does not update the data > distributions for the table, it
>
> >on= ly updates a few columns in systables, > syscolumns,
>
> sysfragments,
>
> &= gt;and sysindices/sysindexes like nrows and npused > in systables,
>
> &= gt;colmin and colmax in syscolumns, nlevels, nleaves, uniq, >
>
> clust,
>
> >and nrows in sysindices. You need to run a combination of LOW,
>
> >>MEDIUM, and HIGH on individual sets of columns to get really
>
> useful>data > distributions in minimum time. If you use my dostats
>
> utili= ty,
>
> >as do > hundreds of Informix sites, it will take care of tha= t for
>
> >you. > > BTW, have you disabled Auto Update Statistics= (AUS)?
>
> >Because if not then > AUS is running every night updatin= g stats for
>
> >you (almost as well as > dostats would) so you shoul= d not have to
>
> be
>
> >running update statistics > manually (or from c= ron) anyway. > >
>
> >Art > > Art S. Kagel, Principal Con= sultant > ASK Database
>
> >Management > > Blog: http://infor= mix-myview.blogspot.com/ > >
>
> >Disclaimer: Please keep in mind= that my own opinions are my own
>
> >opinions > and do not reflect o= n the IIUG, nor any other
>
> >organization with which I am > associa= ted either explicitly,
>
> >implicitly, or by inference. Neither do >= those opinions reflect
>
> >those of other individuals affiliated with a= ny > entity with which
>
> I
>
> >am affiliated nor those of the entities= themselves. > > On Fri, Jun
>
> >27, 2014 at 8:48 AM, medkba <= ;medkba@gmail.com> wrote: > > >
>
> Running
>
> >from cron jobs= daily from shell script > > Not using PDQPRIORITY >
>
> >>= 23 CPUs > > 8 cpu vps is used increased from 6 to 8 no
>
> improvement<= br>> > > > > Please let me if require more information
>
> >= ; > > > Thank
>
> >you very much > > On 27 Jun 2014 19:4= 3, "Art Kagel"
>
> ><art.kagel@gmail.com> wrote: > > > &= gt; > How are you running
>
> update
>
> >statistics for that table? What = environment > > > variables have
>
> you
>
> >set? Are you using PD= QPRIORITY? What engine version > > > and
>
> >edition are you u= sing? How many cores are on the machine and how >
>
> >many > >= ; > CPU VPs are configured? > > > > > > Have you rea= d
>
> John
>
> >Miller's paper in optimizing update statistics runs? > &g= t; > > > >
>
> >Art > > > > > > Art S. K= agel, Principal Consultant > > > ASK
>
> >Database Management = > > > > > > Blog:
>
> >http://informix-myview.blogspot= .com/ > > > > > > Disclaimer: Please
>
> >keep in min= d 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 thos= e of other
>
> >individuals affiliated with any > > > entity wi= th which I am
>
> >affiliated nor those of the entities themselves. >= > > > > > On
>
> >Fri, Jun 27, 2014 at 3:30 AM, medkba &= lt;medkba@gmail.com> wrote: >
>
> > >
>
> > > > > > = Hi All, > > > > > > > > I am having one table and=
>
> contains
>
> >data 23326189 and taking about 15 > > mins > &g= t; > > to complete
>
> >update statistics > > > > >= ; > > > Is there any opportunities to>@
Yes, either disable them or change them to run further apart and rely on
what they do and disable your own scripts. Right now it looks like the
refresh is running 11 minutes after the evaluator starts running and that
can't be right unless your database has very few tables. If you decide to
keep AUS running and abandon your own scripts then you should look in the
ph_run table to see how long the evaluator actually runs for (columns
duration) and change the scheduling for the AUS refresh task:
select * from ph_run where run_task_id = 18;
Art
Art S. Kagel, Principal Consultant
ASK Database Management
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 Fri, Jun 27, 2014 at 11:51 AM, medkba <medkba@gmail.com> wrote:
> Thank you very much Art
>
> The below output executing the query
>
> tk_id 18
> tk_name Auto Update Statistics Evaluation
> tk_enable t
> tk_next_execution 2014-06-28 01:00:00
>
> tk_id 19
> tk_name Auto Update Statistics Refresh
> tk_enable t
> tk_next_execution 2014-06-28 01:11:00
>
> It means that statistics running automatically. Am i right? I need to
> disable both
>
> On Fri, Jun 27, 2014 at 11:39 PM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > There are two AUS tasks, the AUS evaluator that determines what tables
> need
> > their stats updated and the AUS refresh task that executes the commands
> > calculated by the evaluator. You can find the task manager records
> > governing their execution in the sysadmin database in the table ph_task.
> > Run:
> >
> > select tk_id, tk_name, tk_enable, tk_next_execution
> > from sysadmin:ph_task
> > where tk_execute matches 'aus*';> >
> > If tk_enable is set to 't' then the AUS tasks are running. Obviously if
> > the evaluator is executing but the refresh is not, then stats are not
> being
> > updated by AUS and if the refresh is running without the evaluator
> running
> > before it then the list of work its doing is probably out-of-date and may
> > even be empty. The tk_next_execution column will tell you when each is
> > scheduled to run next. Note that in 11.50 the evaluator was rather
> > inefficient and could take longer to run than the default difference in
> the
> > scheduled run times of the two tasks (1 hour) so that when the refresh
> > launched the evaluator may not have finished running resulting in the
> > refresh only processing some of the tables that needed to updated. This
> > was fixed in 11.70 and again in 12.10 by recoding the evaluator to run
> > faster and by changing the default scheduling of the tasks so they run
> > further apart.
> >
> > Art
> >
> > Art S. Kagel, Principal Consultant
> > ASK Database Management
> >
> > 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 Fri, Jun 27, 2014 at 10:05 AM, medkba <medkba@gmail.com> wrote:
> >
> > > Yes, i have running update statistics one of the database i.e. most
> used
> > > the application
> > > Run UPDATE STATISTICS LOW
> > > Ok, i will read this article
> > > Dosstat runs weekly once
> > > This is new hp blade server with 32gb memory. It means that hardware
> very
> > > powerful but statistics job running slow then the question aries why
> slow
> > > and hard to convenience management to change different way of running.
> > > How do i check aus running
> > > Thank you
> > > On 27 Jun 2014 21:18, "Art Kagel" <art.kagel@gmail.com> wrote:
> > >
> > > > "from a shell script" wasn't what I was looking for. Are you running
> > plan
> > > > UPDATE STATISTICS? UPDATE STATISTICS LOW? UPDATE STATISTICS MEDIUM?
> > > > UPDATE STATISTICS HIGH? Combinations of multiple commands? Are you> > > > running these in dbaccess? If so are you running against just this
> one
> > > > table in each command or against the entire database? Are you using
> my
> > > > dostats utility to generate an optimal set of commands?
> > > >
> > > > OK, first thing to do then is to read John's paper. Link here:
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/miller
/0203miller.html#section3
> > > >
> > > > Quick-and-dirty:
> > > >
> > > > export PDQPRIORITY=100 # Or as high as you dare without disrupting
> > > > production resource requirements.
> > > > export PSORT_NPROCS=16 # I know the max documented is 10, trust me.
> > > > Make sure that you have lots of memory allocated to DS_TOTAL_MEMORY
> to
> > > > allow for all sorting to be accomplished in memory. If you want to
> know
> > > how
> > > > much memory you will need and you haven't zero'd out your server
> stats
> > > > since the last time you tried running the update statistics (ie
> onstat
> > > -z)> > > > then the sysmaster:sysprofile table has a row containing the size of
> > the
> > > > largest sort that's been executed in KB:
> > > >
> > > > select value from sysmaster:sysprofile where name = 'maxsortspace';> > > >
> > > > I think that I remember that you are using 11.50 so you can't take
> > > > advantage or LOW sampling.
> > > >
> > > > BTW, in another reply you said you are only runing vanilla UPDATE
> > > > STATISTICS with no modifiers. This command does not update the data
> > > > distributions for the table, it only updates a few columns in
> > systables,
> > > > syscolumns, sysfragments, and sysindices/sysindexes like nrows and
> > npused
> > > > in systables, colmin and colmax in syscolumns, nlevels, nleaves,
> uniq,
> > > > clust, and nrows in sysindices. You need to run a combination of LOW,
> > > > MEDIUM, and HIGH on individual sets of columns to get really useful
> > data
> > > > distributions in minimum time. If you use my dostats utility, as do
> > > > hundreds of Informix sites, it will take care of that for you.
> > > >
> > > > BTW, have you disabled Auto Update Statistics (AUS)? Because if not
> > then
> > > > AUS is running every night updating stats for you (almost as well as
> > > > dostats would) so you should not have to be running update statistics
> > > > manually (or from cron) anyway.
> > > >
> > > > Art
> > > >
> > > > Art S. Kagel, Principal Consultant
> > > > ASK Database Management
> > > >
> > > > Blog: http://informix-myview.blogspot.com/
> > > >
> > > > Disclaimer: Please keep in mind that my own opinions are my own
> > opinions
> > > > and do
tk_id 18
tk_name Auto Update Statistics Evaluation
tk_enable f
tk_next_execution 2014-06-28 00:30:28
tk_id 19
tk_name Auto Update Statistics Refresh
tk_enable f
tk_next_execution 2014-06-28 00:29:31
I have disable both jobs and will continue to run manually statistics but
please let me know how to do the
On Sat, Jun 28, 2014 at 12:20 AM, Art Kagel <art.kagel@gmail.com> wrote:
> Yes, either disable them or change them to run further apart and rely on
> what they do and disable your own scripts. Right now it looks like the
> refresh is running 11 minutes after the evaluator starts running and that
> can't be right unless your database has very few tables. If you decide to
> keep AUS running and abandon your own scripts then you should look in the
> ph_run table to see how long the evaluator actually runs for (columns
> duration) and change the scheduling for the AUS refresh task:
>
> select * from ph_run where run_task_id = 18;>
> Art
>
> Art S. Kagel, Principal Consultant
> ASK Database Management
>
> 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 Fri, Jun 27, 2014 at 11:51 AM, medkba <medkba@gmail.com> wrote:
>
> > Thank you very much Art
> >
> > The below output executing the query
> >
> > tk_id 18
> > tk_name Auto Update Statistics Evaluation
> > tk_enable t
> > tk_next_execution 2014-06-28 01:00:00
> >
> > tk_id 19
> > tk_name Auto Update Statistics Refresh
> > tk_enable t
> > tk_next_execution 2014-06-28 01:11:00
> >
> > It means that statistics running automatically. Am i right? I need to
> > disable both
> >
> > On Fri, Jun 27, 2014 at 11:39 PM, Art Kagel <art.kagel@gmail.com> wrote:
> >
> > > There are two AUS tasks, the AUS evaluator that determines what tables
> > need
> > > their stats updated and the AUS refresh task that executes the commands
> > > calculated by the evaluator. You can find the task manager records
> > > governing their execution in the sysadmin database in the table
> ph_task.
> > > Run:
> > >
> > > select tk_id, tk_name, tk_enable, tk_next_execution
> > > from sysadmin:ph_task
> > > where tk_execute matches 'aus*';> > >
> > > If tk_enable is set to 't' then the AUS tasks are running. Obviously if
> > > the evaluator is executing but the refresh is not, then stats are not
> > being
> > > updated by AUS and if the refresh is running without the evaluator
> > running
> > > before it then the list of work its doing is probably out-of-date and
> may
> > > even be empty. The tk_next_execution column will tell you when each is
> > > scheduled to run next. Note that in 11.50 the evaluator was rather
> > > inefficient and could take longer to run than the default difference in
> > the
> > > scheduled run times of the two tasks (1 hour) so that when the refresh
> > > launched the evaluator may not have finished running resulting in the
> > > refresh only processing some of the tables that needed to updated. This
> > > was fixed in 11.70 and again in 12.10 by recoding the evaluator to run
> > > faster and by changing the default scheduling of the tasks so they run
> > > further apart.
> > >
> > > Art
> > >
> > > Art S. Kagel, Principal Consultant
> > > ASK Database Management
> > >
> > > 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 Fri, Jun 27, 2014 at 10:05 AM, medkba <medkba@gmail.com> wrote:
> > >
> > > > Yes, i have running update statistics one of the database i.e. most
> > used
> > > > the application
> > > > Run UPDATE STATISTICS LOW
> > > > Ok, i will read this article
> > > > Dosstat runs weekly once
> > > > This is new hp blade server with 32gb memory. It means that hardware
> > very
> > > > powerful but statistics job running slow then the question aries why
> > slow
> > > > and hard to convenience management to change different way of
> running.
> > > > How do i check aus running
> > > > Thank you
> > > > On 27 Jun 2014 21:18, "Art Kagel" <art.kagel@gmail.com> wrote:
> > > >
> > > > > "from a shell script" wasn't what I was looking for. Are you
> running
> > > plan
> > > > > UPDATE STATISTICS? UPDATE STATISTICS LOW? UPDATE STATISTICS MEDIUM?
> > > > > UPDATE STATISTICS HIGH? Combinations of multiple commands? Are you> > > > > running these in dbaccess? If so are you running against just this
> > one
> > > > > table in each command or against the entire database? Are you using
> > my
> > > > > dostats utility to generate an optimal set of commands?
> > > > >
> > > > > OK, first thing to do then is to read John's paper. Link here:
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/miller
/0203miller.html#section3
> > > > >
> > > > > Quick-and-dirty:
> > > > >
> > > > > export PDQPRIORITY=100 # Or as high as you dare without disrupting
> > > > > production resource requirements.
> > > > > export PSORT_NPROCS=16 # I know the max documented is 10, trust me.
> > > > > Make sure that you have lots of memory allocated to DS_TOTAL_MEMORY
> > to
> > > > > allow for all sorting to be accomplished in memory. If you want to
> > know
> > > > how
> > > > > much memory you will need and you haven't zero'd out your server
> > stats
> > > > > since the last time you tried running the update statistics (ie
> > onstat
> > > > -z)> > > > > then the sysmaster:sysprofile table has a row containing the size
> of
> > > the
> > > > > largest sort that's been executed in KB:
> > > > >
> > > > > select value from sysmaster:sysprofile where name = 'maxsortspace';> > > > >
> > > > > I think that I remember that you are using 11.50 so you can't take
> > > > > advantage or LOW sampling.
> > > > >
> > > > > BTW, in another reply you said you are only runing vanilla UPDATE
> > > > > STATISTICS with no modifiers. This command does not update the data
> > > > > distributions for the table, it only updates a few columns in
> > > systables,
> > > > > syscolumns, sysfragments, and sysindices/sysindexes like nrows and
> > > npused
> > > > > in systables, colmin and colmax in syscolumns, nlevels, nleaves,
> > uniq,
> > > > > clust, and nrows in sysindices. You need to run a combination of
> LOW,
> > > > > MEDIUM, and HIGH on individual sets of columns to
Art:
The newer (all the C versions) of aus_refesh wait for update to 5 minutes
for the aus_evaluator to
complete before starting any real processing. The accomplish this by
checking for the
existence of the aus_command table. If this table exists then the
aus_refresh will
sleep for 15 seconds and check again. It will loop for 20 times before
proceeding
I see now that a maximum of 5 minutes of wait time is to short and will
increase
this wait time.
John F. Miller III
STSM, Lead Architect
miller3@us.ibm.com
503-747-1366
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 06/27/2014 09:20:31 AM:
> From: "Art Kagel" <art.kagel@gmail.com>
> To: ids@iiug.org,
> Date: 06/27/2014 09:22 AM
> Subject: Re: Update statistics issue [33302]
> Sent by: ids-bounces@iiug.org
>
> Yes, either disable them or change them to run further apart and rely on
> what they do and disable your own scripts. Right now it looks like the
> refresh is running 11 minutes after the evaluator starts running and that
> can't be right unless your database has very few tables. If you decide to
> keep AUS running and abandon your own scripts then you should look in the
> ph_run table to see how long the evaluator actually runs for (columns
> duration) and change the scheduling for the AUS refresh task:
>
> select * from ph_run where run_task_id = 18;>
> Art
>
> Art S. Kagel, Principal Consultant
> ASK Database Management
>
> 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 Fri, Jun 27, 2014 at 11:51 AM, medkba <medkba@gmail.com> wrote:
>
> > Thank you very much Art
> >
> > The below output executing the query
> >
> > tk_id 18
> > tk_name Auto Update Statistics Evaluation
> > tk_enable t
> > tk_next_execution 2014-06-28 01:00:00
> >
> > tk_id 19
> > tk_name Auto Update Statistics Refresh
> > tk_enable t
> > tk_next_execution 2014-06-28 01:11:00
> >
> > It means that statistics running automatically. Am i right? I need to
> > disable both
> >
> > On Fri, Jun 27, 2014 at 11:39 PM, Art Kagel <art.kagel@gmail.com>
wrote:
> >
> > > There are two AUS tasks, the AUS evaluator that determines what
tables
> > need
> > > their stats updated and the AUS refresh task that executes the
commands
> > > calculated by the evaluator. You can find the task manager records
> > > governing their execution in the sysadmin database in the table
ph_task.
> > > Run:
> > >
> > > select tk_id, tk_name, tk_enable, tk_next_execution
> > > from sysadmin:ph_task
> > > where tk_execute matches 'aus*';> > >
> > > If tk_enable is set to 't' then the AUS tasks are running. Obviously
if
> > > the evaluator is executing but the refresh is not, then stats are not
> > being
> > > updated by AUS and if the refresh is running without the evaluator
> > running
> > > before it then the list of work its doing is probably out-of-date and
may
> > > even be empty. The tk_next_execution column will tell you when each
is
> > > scheduled to run next. Note that in 11.50 the evaluator was rather
> > > inefficient and could take longer to run than the default difference
in
> > the
> > > scheduled run times of the two tasks (1 hour) so that when the
refresh
> > > launched the evaluator may not have finished running resulting in the
> > > refresh only processing some of the tables that needed to updated.
This
> > > was fixed in 11.70 and again in 12.10 by recoding the evaluator to
run
> > > faster and by changing the default scheduling of the tasks so they
run
> > > further apart.
> > >
> > > Art
> > >
> > > Art S. Kagel, Principal Consultant
> > > ASK Database Management
> > >
> > > 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 Fri, Jun 27, 2014 at 10:05 AM, medkba <medkba@gmail.com> wrote:
> > >
> > > > Yes, i have running update statistics one of the database i.e. most
> > used
> > > > the application
> > > > Run UPDATE STATISTICS LOW
> > > > Ok, i will read this article
> > > > Dosstat runs weekly once
> > > > This is new hp blade server with 32gb memory. It means that
hardware
> > very
> > > > powerful but statistics job running slow then the question aries
why
> > slow
> > > > and hard to convenience management to change different way of
running.
> > > > How do i check aus running
> > > > Thank you
> > > > On 27 Jun 2014 21:18, "Art Kagel" <art.kagel@gmail.com> wrote:
> > > >
> > > > > "from a shell script" wasn't what I was looking for. Are you
running
> > > plan
> > > > > UPDATE STATISTICS? UPDATE STATISTICS LOW? UPDATE STATISTICSMEDIUM?
> > > > > UPDATE STATISTICS HIGH? Combinations of multiple commands? Areyou
> > > > > running these in dbaccess? If so are you running against just
this
> > one
> > > > > table in each command or against the entire database? Are you
using
> > my
> > > > > dostats utility to generate an optimal set of commands?
> > > > >
> > > > > OK, first thing to do then is to read John's paper. Link here:
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
> http://www.ibm.com/developerworks/data/zones/informix/library/
> techarticle/miller/0203miller.html#section3
> > > > >
> > > > > Quick-and-dirty:
> > > > >
> > > > > export PDQPRIORITY=100 # Or as high as you dare without
disrupting
> > > > > production resource requirements.
> > > > > export PSORT_NPROCS=16 # I know the max documented is 10, trust
me.
> > > > > Make sure that you have lots of memory allocated to
DS_TOTAL_MEMORY> > to
> > > > > allow for all sorting to be accomplished in memory. If you want
to
> > know
> > > > how
> > > > > much memory you will need and you haven't zero'd out your server
> > stats
> > > > > since the last time you tried running the update statistics (ie
> > onstat
> > > > -z)> > > > > then the sysmaster:sysprofile table has a row containing the size
of
> > > the
> > > > > largest sort that's been executed in KB:
> > > > >
> > > > > select value from sysmaster:sysprofile where name ='maxsortspace';
> > > > >
> > > > > I think that I remember that you are using 11.50 so you can't
take
> > > > > advantage or LOW sampling.
how to do the PDQPRIORITY before run the statics
On Sat, Jun 28, 2014 at 12:36 AM, medkba <medkba@gmail.com> wrote:
> tk_id 18
> tk_name Auto Update Statistics Evaluation
> tk_enable f
> tk_next_execution 2014-06-28 00:30:28
>
> tk_id 19
> tk_name Auto Update Statistics Refresh
> tk_enable f
> tk_next_execution 2014-06-28 00:29:31
>
> I have disable both jobs and will continue to run manually statistics but
> please let me know how to do the
>
> On Sat, Jun 28, 2014 at 12:20 AM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > Yes, either disable them or change them to run further apart and rely on
> > what they do and disable your own scripts. Right now it looks like the
> > refresh is running 11 minutes after the evaluator starts running and that
> > can't be right unless your database has very few tables. If you decide to
> > keep AUS running and abandon your own scripts then you should look in the
> > ph_run table to see how long the evaluator actually runs for (columns
> > duration) and change the scheduling for the AUS refresh task:
> >
> > select * from ph_run where run_task_id = 18;> >
> > Art
> >
> > Art S. Kagel, Principal Consultant
> > ASK Database Management
> >
> > 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 Fri, Jun 27, 2014 at 11:51 AM, medkba <medkba@gmail.com> wrote:
> >
> > > Thank you very much Art
> > >
> > > The below output executing the query
> > >
> > > tk_id 18
> > > tk_name Auto Update Statistics Evaluation
> > > tk_enable t
> > > tk_next_execution 2014-06-28 01:00:00
> > >
> > > tk_id 19
> > > tk_name Auto Update Statistics Refresh
> > > tk_enable t
> > > tk_next_execution 2014-06-28 01:11:00
> > >
> > > It means that statistics running automatically. Am i right? I need to
> > > disable both
> > >
> > > On Fri, Jun 27, 2014 at 11:39 PM, Art Kagel <art.kagel@gmail.com>
> wrote:
> > >
> > > > There are two AUS tasks, the AUS evaluator that determines what
> tables
> > > need
> > > > their stats updated and the AUS refresh task that executes the
> commands
> > > > calculated by the evaluator. You can find the task manager records
> > > > governing their execution in the sysadmin database in the table
> > ph_task.
> > > > Run:
> > > >
> > > > select tk_id, tk_name, tk_enable, tk_next_execution
> > > > from sysadmin:ph_task
> > > > where tk_execute matches 'aus*';> > > >
> > > > If tk_enable is set to 't' then the AUS tasks are running. Obviously
> if
> > > > the evaluator is executing but the refresh is not, then stats are not
> > > being
> > > > updated by AUS and if the refresh is running without the evaluator
> > > running
> > > > before it then the list of work its doing is probably out-of-date and
> > may
> > > > even be empty. The tk_next_execution column will tell you when each
> is
> > > > scheduled to run next. Note that in 11.50 the evaluator was rather
> > > > inefficient and could take longer to run than the default difference
> in
> > > the
> > > > scheduled run times of the two tasks (1 hour) so that when the
> refresh
> > > > launched the evaluator may not have finished running resulting in the
> > > > refresh only processing some of the tables that needed to updated.
> This
> > > > was fixed in 11.70 and again in 12.10 by recoding the evaluator to
> run
> > > > faster and by changing the default scheduling of the tasks so they
> run
> > > > further apart.
> > > >
> > > > Art
> > > >
> > > > Art S. Kagel, Principal Consultant
> > > > ASK Database Management
> > > >
> > > > 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 Fri, Jun 27, 2014 at 10:05 AM, medkba <medkba@gmail.com> wrote:
> > > >
> > > > > Yes, i have running update statistics one of the database i.e. most
> > > used
> > > > > the application
> > > > > Run UPDATE STATISTICS LOW
> > > > > Ok, i will read this article
> > > > > Dosstat runs weekly once
> > > > > This is new hp blade server with 32gb memory. It means that
> hardware
> > > very
> > > > > powerful but statistics job running slow then the question aries
> why
> > > slow
> > > > > and hard to convenience management to change different way of
> > running.
> > > > > How do i check aus running
> > > > > Thank you
> > > > > On 27 Jun 2014 21:18, "Art Kagel" <art.kagel@gmail.com> wrote:
> > > > >
> > > > > > "from a shell script" wasn't what I was looking for. Are you
> > running
> > > > plan
> > > > > > UPDATE STATISTICS? UPDATE STATISTICS LOW? UPDATE STATISTICS> MEDIUM?
> > > > > > UPDATE STATISTICS HIGH? Combinations of multiple commands? Are> you
> > > > > > running these in dbaccess? If so are you running against just
> this
> > > one
> > > > > > table in each command or against the entire database? Are you
> using
> > > my
> > > > > > dostats utility to generate an optimal set of commands?
> > > > > >
> > > > > > OK, first thing to do then is to read John's paper. Link here:
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/miller
/0203miller.html#section3
> > > > > >
> > > > > > Quick-and-dirty:
> > > > > >
> > > > > > export PDQPRIORITY=100 # Or as high as you dare without
> disrupting
> > > > > > production resource requirements.
> > > > > > export PSORT_NPROCS=16 # I know the max documented is 10, trust
> me.
> > > > > > Make sure that you have lots of memory allocated to
> DS_TOTAL_MEMORY> > > to
> > > > > > allow for all sorting to be accomplished in memory. If you want
> to
> > > know
> > > > > how
> > > > > > much memory you will need and you haven't zero'd out your server
> > > stats
> > > > > > since the last time you tried running the update statistics (ie
> > > onstat
> > > > > -z)> > > > > > then the sysmaster:sysprofile table has a row containing the size
> > of
> > > > the
> > > > > > largest sort that's been executed in KB:
> > > > > >
> > > > > > select value from sysmaster:sysprofile where name => 'maxsortspace';
> > > > > >
> > > > > > I think that I remember that you are using 11.50 so you can't
> take
> >
Art and John,
Which is best way either to do manual or automatic. My Informix engine
version is 11.50 FC9.
Currently, this job is running manual using shell script on the HP-UX
PA-RISC and recently we have migrated to HP integrity server so that
expectation of this job running faster but it is slower compare to HP-UX
Thank you very much
On Sat, Jun 28, 2014 at 12:40 AM, John Miller iii <miller3@us.ibm.com>
wrote:
> Art:
>
> The newer (all the C versions) of aus_refesh wait for update to 5 minutes
> for the aus_evaluator to
> complete before starting any real processing. The accomplish this by
> checking for the
> existence of the aus_command table. If this table exists then the
> aus_refresh will
> sleep for 15 seconds and check again. It will loop for 20 times before
> proceeding
> I see now that a maximum of 5 minutes of wait time is to short and will
> increase
> this wait time.
>
> John F. Miller III
> STSM, Lead Architect
> miller3@us.ibm.com
> 503-747-1366
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 06/27/2014 09:20:31 AM:
>
> > From: "Art Kagel" <art.kagel@gmail.com>
> > To: ids@iiug.org,
> > Date: 06/27/2014 09:22 AM
> > Subject: Re: Update statistics issue [33302]
> > Sent by: ids-bounces@iiug.org
> >
> > Yes, either disable them or change them to run further apart and rely on
> > what they do and disable your own scripts. Right now it looks like the
> > refresh is running 11 minutes after the evaluator starts running and that
>
> > can't be right unless your database has very few tables. If you decide to
>
> > keep AUS running and abandon your own scripts then you should look in the
>
> > ph_run table to see how long the evaluator actually runs for (columns
> > duration) and change the scheduling for the AUS refresh task:
> >
> > select * from ph_run where run_task_id = 18;> >
> > Art
> >
> > Art S. Kagel, Principal Consultant
> > ASK Database Management
> >
> > 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 Fri, Jun 27, 2014 at 11:51 AM, medkba <medkba@gmail.com> wrote:
> >
> > > Thank you very much Art
> > >
> > > The below output executing the query
> > >
> > > tk_id 18
> > > tk_name Auto Update Statistics Evaluation
> > > tk_enable t
> > > tk_next_execution 2014-06-28 01:00:00
> > >
> > > tk_id 19
> > > tk_name Auto Update Statistics Refresh
> > > tk_enable t
> > > tk_next_execution 2014-06-28 01:11:00
> > >
> > > It means that statistics running automatically. Am i right? I need to
> > > disable both
> > >
> > > On Fri, Jun 27, 2014 at 11:39 PM, Art Kagel <art.kagel@gmail.com>
> wrote:
> > >
> > > > There are two AUS tasks, the AUS evaluator that determines what
> tables
> > > need
> > > > their stats updated and the AUS refresh task that executes the
> commands
> > > > calculated by the evaluator. You can find the task manager records
> > > > governing their execution in the sysadmin database in the table
> ph_task.
> > > > Run:
> > > >
> > > > select tk_id, tk_name, tk_enable, tk_next_execution
> > > > from sysadmin:ph_task
> > > > where tk_execute matches 'aus*';> > > >
> > > > If tk_enable is set to 't' then the AUS tasks are running. Obviously
> if
> > > > the evaluator is executing but the refresh is not, then stats are not
>
> > > being
> > > > updated by AUS and if the refresh is running without the evaluator
> > > running
> > > > before it then the list of work its doing is probably out-of-date and
> may
> > > > even be empty. The tk_next_execution column will tell you when each
> is
> > > > scheduled to run next. Note that in 11.50 the evaluator was rather
> > > > inefficient and could take longer to run than the default difference
> in
> > > the
> > > > scheduled run times of the two tasks (1 hour) so that when the
> refresh
> > > > launched the evaluator may not have finished running resulting in the
>
> > > > refresh only processing some of the tables that needed to updated.
> This
> > > > was fixed in 11.70 and again in 12.10 by recoding the evaluator to
> run
> > > > faster and by changing the default scheduling of the tasks so they
> run
> > > > further apart.
> > > >
> > > > Art
> > > >
> > > > Art S. Kagel, Principal Consultant
> > > > ASK Database Management
> > > >
> > > > 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 Fri, Jun 27, 2014 at 10:05 AM, medkba <medkba@gmail.com> wrote:
> > > >
> > > > > Yes, i have running update statistics one of the database i.e. most
>
> > > used
> > > > > the application
> > > > > Run UPDATE STATISTICS LOW
> > > > > Ok, i will read this article
> > > > > Dosstat runs weekly once
> > > > > This is new hp blade server with 32gb memory. It means that
> hardware
> > > very
> > > > > powerful but statistics job running slow then the question aries
> why
> > > slow
> > > > > and hard to convenience management to change different way of
> running.
> > > > > How do i check aus running
> > > > > Thank you
> > > > > On 27 Jun 2014 21:18, "Art Kagel" <art.kagel@gmail.com> wrote:
> > > > >
> > > > > > "from a shell script" wasn't what I was looking for. Are you
> running
> > > > plan
> > > > > > UPDATE STATISTICS? UPDATE STATISTICS LOW? UPDATE STATISTICS> MEDIUM?
> > > > > > UPDATE STATISTICS HIGH? Combinations of multiple commands? Are> you
> > > > > > running these in dbaccess? If so are you running against just
> this
> > > one
> > > > > > table in each command or against the entire database? Are you
> using
> > > my
> > > > > > dostats utility to generate an optimal set of commands?
> > > > > >
> > > > > > OK, first thing to do then is to read John's paper. Link here:
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> > http://www.ibm.com/developerworks/data/zones/informix/library/
> > techarticle/miller/0203miller.html#section3
> > > > > >
> > > > > > Quick-and-dirty:
> > > > > >
> > > > > > export PDQPRIORITY=100 # Or as high as you dare without
> disrupting
> > > > > > production resource requirements.
> > > > > > export PSORT_NPROCS=16 # I know the ma
You can either set it in the environment with:
export PDQPRIORITY=100
or if you are executing the UPDATE STATISTICS in dbaccess, then you can
include this SQL line at the top of the SQL script:
SET PDQPRIORITY 100;
When you run dostats you would add the flag:
-Q 100
Art
Art S. Kagel, Principal Consultant
ASK Database Management
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 Fri, Jun 27, 2014 at 12:42 PM, medkba <medkba@gmail.com> wrote:
> how to do the PDQPRIORITY before run the statics
>
> On Sat, Jun 28, 2014 at 12:36 AM, medkba <medkba@gmail.com> wrote:
>
> > tk_id 18
> > tk_name Auto Update Statistics Evaluation
> > tk_enable f
> > tk_next_execution 2014-06-28 00:30:28
> >
> > tk_id 19
> > tk_name Auto Update Statistics Refresh
> > tk_enable f
> > tk_next_execution 2014-06-28 00:29:31
> >
> > I have disable both jobs and will continue to run manually statistics but
> > please let me know how to do the
> >
> > On Sat, Jun 28, 2014 at 12:20 AM, Art Kagel <art.kagel@gmail.com> wrote:
> >
> > > Yes, either disable them or change them to run further apart and rely
> on
> > > what they do and disable your own scripts. Right now it looks like the
> > > refresh is running 11 minutes after the evaluator starts running and
> that
> > > can't be right unless your database has very few tables. If you decide
> to
> > > keep AUS running and abandon your own scripts then you should look in
> the
> > > ph_run table to see how long the evaluator actually runs for (columns
> > > duration) and change the scheduling for the AUS refresh task:
> > >
> > > select * from ph_run where run_task_id = 18;> > >
> > > Art
> > >
> > > Art S. Kagel, Principal Consultant
> > > ASK Database Management
> > >
> > > 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 Fri, Jun 27, 2014 at 11:51 AM, medkba <medkba@gmail.com> wrote:
> > >
> > > > Thank you very much Art
> > > >
> > > > The below output executing the query
> > > >
> > > > tk_id 18
> > > > tk_name Auto Update Statistics Evaluation
> > > > tk_enable t
> > > > tk_next_execution 2014-06-28 01:00:00
> > > >
> > > > tk_id 19
> > > > tk_name Auto Update Statistics Refresh
> > > > tk_enable t
> > > > tk_next_execution 2014-06-28 01:11:00
> > > >
> > > > It means that statistics running automatically. Am i right? I need to
> > > > disable both
> > > >
> > > > On Fri, Jun 27, 2014 at 11:39 PM, Art Kagel <art.kagel@gmail.com>
> > wrote:
> > > >
> > > > > There are two AUS tasks, the AUS evaluator that determines what
> > tables
> > > > need
> > > > > their stats updated and the AUS refresh task that executes the
> > commands
> > > > > calculated by the evaluator. You can find the task manager records
> > > > > governing their execution in the sysadmin database in the table
> > > ph_task.
> > > > > Run:
> > > > >
> > > > > select tk_id, tk_name, tk_enable, tk_next_execution
> > > > > from sysadmin:ph_task
> > > > > where tk_execute matches 'aus*';> > > > >
> > > > > If tk_enable is set to 't' then the AUS tasks are running.
> Obviously
> > if
> > > > > the evaluator is executing but the refresh is not, then stats are
> not
> > > > being
> > > > > updated by AUS and if the refresh is running without the evaluator
> > > > running
> > > > > before it then the list of work its doing is probably out-of-date
> and
> > > may
> > > > > even be empty. The tk_next_execution column will tell you when each
> > is
> > > > > scheduled to run next. Note that in 11.50 the evaluator was rather
> > > > > inefficient and could take longer to run than the default
> difference
> > in
> > > > the
> > > > > scheduled run times of the two tasks (1 hour) so that when the
> > refresh
> > > > > launched the evaluator may not have finished running resulting in
> the
> > > > > refresh only processing some of the tables that needed to updated.
> > This
> > > > > was fixed in 11.70 and again in 12.10 by recoding the evaluator to
> > run
> > > > > faster and by changing the default scheduling of the tasks so they
> > run
> > > > > further apart.
> > > > >
> > > > > Art
> > > > >
> > > > > Art S. Kagel, Principal Consultant
> > > > > ASK Database Management
> > > > >
> > > > > 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 Fri, Jun 27, 2014 at 10:05 AM, medkba <medkba@gmail.com> wrote:
> > > > >
> > > > > > Yes, i have running update statistics one of the database i.e.
> most
> > > > used
> > > > > > the application
> > > > > > Run UPDATE STATISTICS LOW
> > > > > > Ok, i will read this article
> > > > > > Dosstat runs weekly once
> > > > > > This is new hp blade server with 32gb memory. It means that
> > hardware
> > > > very
> > > > > > powerful but statistics job running slow then the question aries
> > why
> > > > slow
> > > > > > and hard to convenience management to change different way of
> > > running.
> > > > > > How do i check aus running
> > > > > > Thank you
> > > > > > On 27 Jun 2014 21:18, "Art Kagel" <art.kagel@gmail.com> wrote:
> > > > > >
> > > > > > > "from a shell script" wasn't what I was looking for. Are you
> > > running
> > > > > plan
> > > > > > > UPDATE STATISTICS? UPDATE STATISTICS LOW? UPDATE STATISTICS> > MEDIUM?
> > > > > > > UPDATE STATISTICS HIGH? Combinations of multiple commands? Are> > you
> > > > > > > running these in dbaccess? If so are you running against just
> > this
> > > > one
> > > > > > > table in each command or against the entire database? Are you
> > using
> > > > my
> > > > > > > dostats utility to generate an optimal set of commands?
> > > > > > >
> > > > > > > OK, first thing to do then is to read John's paper. Link here:
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > >
Ooo, now you're getting into a bone of contention between very good
friends. John wrote the AUS functions and has good reason to think they
are doing the best job possible. I wrote dostats and have good reason to
think that it does the best job possible. Honestly in most cases both will
work excellently. There are some specific databases on which one or the
other will either be faster or will produce slightly better quality data
distributions. One nice feature of AUS is that it will duplicate your
existing levels of distributions if you turn on that flag in the
ph_threshold table so you can preset the levels of distributions you want
using dostats and then let AUS maintain them over time. You can do the
same with my tools by using the myschema --distributions=filename option to
generate an SQL script you can run to reproduce/refresh the current level
of distributions.
Art
Art S. Kagel, Principal Consultant
ASK Database Management
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 Fri, Jun 27, 2014 at 12:46 PM, medkba <medkba@gmail.com> wrote:
> Art and John,
>
> Which is best way either to do manual or automatic. My Informix engine
> version is 11.50 FC9.
>
> Currently, this job is running manual using shell script on the HP-UX
> PA-RISC and recently we have migrated to HP integrity server so that
> expectation of this job running faster but it is slower compare to HP-UX
>
> Thank you very much
>
> On Sat, Jun 28, 2014 at 12:40 AM, John Miller iii <miller3@us.ibm.com>
> wrote:
>
> > Art:
> >
> > The newer (all the C versions) of aus_refesh wait for update to 5 minutes
> > for the aus_evaluator to
> > complete before starting any real processing. The accomplish this by
> > checking for the
> > existence of the aus_command table. If this table exists then the
> > aus_refresh will
> > sleep for 15 seconds and check again. It will loop for 20 times before
> > proceeding
> > I see now that a maximum of 5 minutes of wait time is to short and will
> > increase
> > this wait time.
> >
> > John F. Miller III
> > STSM, Lead Architect
> > miller3@us.ibm.com
> > 503-747-1366
> > IBM Informix Dynamic Server (IDS)
> >
> > ids-bounces@iiug.org wrote on 06/27/2014 09:20:31 AM:
> >
> > > From: "Art Kagel" <art.kagel@gmail.com>
> > > To: ids@iiug.org,
> > > Date: 06/27/2014 09:22 AM
> > > Subject: Re: Update statistics issue [33302]
> > > Sent by: ids-bounces@iiug.org
> > >
> > > Yes, either disable them or change them to run further apart and rely
> on
> > > what they do and disable your own scripts. Right now it looks like the
> > > refresh is running 11 minutes after the evaluator starts running and
> that
> >
> > > can't be right unless your database has very few tables. If you decide
> to
> >
> > > keep AUS running and abandon your own scripts then you should look in
> the
> >
> > > ph_run table to see how long the evaluator actually runs for (columns
> > > duration) and change the scheduling for the AUS refresh task:
> > >
> > > select * from ph_run where run_task_id = 18;> > >
> > > Art
> > >
> > > Art S. Kagel, Principal Consultant
> > > ASK Database Management
> > >
> > > 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 Fri, Jun 27, 2014 at 11:51 AM, medkba <medkba@gmail.com> wrote:
> > >
> > > > Thank you very much Art
> > > >
> > > > The below output executing the query
> > > >
> > > > tk_id 18
> > > > tk_name Auto Update Statistics Evaluation
> > > > tk_enable t
> > > > tk_next_execution 2014-06-28 01:00:00
> > > >
> > > > tk_id 19
> > > > tk_name Auto Update Statistics Refresh
> > > > tk_enable t
> > > > tk_next_execution 2014-06-28 01:11:00
> > > >
> > > > It means that statistics running automatically. Am i right? I need to
> > > > disable both
> > > >
> > > > On Fri, Jun 27, 2014 at 11:39 PM, Art Kagel <art.kagel@gmail.com>
> > wrote:
> > > >
> > > > > There are two AUS tasks, the AUS evaluator that determines what
> > tables
> > > > need
> > > > > their stats updated and the AUS refresh task that executes the
> > commands
> > > > > calculated by the evaluator. You can find the task manager records
> > > > > governing their execution in the sysadmin database in the table
> > ph_task.
> > > > > Run:
> > > > >
> > > > > select tk_id, tk_name, tk_enable, tk_next_execution
> > > > > from sysadmin:ph_task
> > > > > where tk_execute matches 'aus*';> > > > >
> > > > > If tk_enable is set to 't' then the AUS tasks are running.
> Obviously
> > if
> > > > > the evaluator is executing but the refresh is not, then stats are
> not
> >
> > > > being
> > > > > updated by AUS and if the refresh is running without the evaluator
> > > > running
> > > > > before it then the list of work its doing is probably out-of-date
> and
> > may
> > > > > even be empty. The tk_next_execution column will tell you when each
> > is
> > > > > scheduled to run next. Note that in 11.50 the evaluator was rather
> > > > > inefficient and could take longer to run than the default
> difference
> > in
> > > > the
> > > > > scheduled run times of the two tasks (1 hour) so that when the
> > refresh
> > > > > launched the evaluator may not have finished running resulting in
> the
> >
> > > > > refresh only processing some of the tables that needed to updated.
> > This
> > > > > was fixed in 11.70 and again in 12.10 by recoding the evaluator to
> > run
> > > > > faster and by changing the default scheduling of the tasks so they
> > run
> > > > > further apart.
> > > > >
> > > > > Art
> > > > >
> > > > > Art S. Kagel, Principal Consultant
> > > > > ASK Database Management
> > > > >
> > > > > 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 Fri, Jun 27, 2014 at 10:05 AM, medkba <medkba@gmail.com> wrote:
> > > > >@@NL@
Art,
Ok, I has enabled AUS
Thank you very much
On Sat, Jun 28, 2014 at 1:18 AM, Art Kagel <art.kagel@gmail.com> wrote:
> Ooo, now you're getting into a bone of contention between very good
> friends. John wrote the AUS functions and has good reason to think they
> are doing the best job possible. I wrote dostats and have good reason to
> think that it does the best job possible. Honestly in most cases both will
> work excellently. There are some specific databases on which one or the
> other will either be faster or will produce slightly better quality data
> distributions. One nice feature of AUS is that it will duplicate your
> existing levels of distributions if you turn on that flag in the
> ph_threshold table so you can preset the levels of distributions you want
> using dostats and then let AUS maintain them over time. You can do the
> same with my tools by using the myschema --distributions=filename option to
> generate an SQL script you can run to reproduce/refresh the current level
> of distributions.
>
> Art
>
> Art S. Kagel, Principal Consultant
> ASK Database Management
>
> 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 Fri, Jun 27, 2014 at 12:46 PM, medkba <medkba@gmail.com> wrote:
>
> > Art and John,
> >
> > Which is best way either to do manual or automatic. My Informix engine
> > version is 11.50 FC9.
> >
> > Currently, this job is running manual using shell script on the HP-UX
> > PA-RISC and recently we have migrated to HP integrity server so that
> > expectation of this job running faster but it is slower compare to HP-UX
> >
> > Thank you very much
> >
> > On Sat, Jun 28, 2014 at 12:40 AM, John Miller iii <miller3@us.ibm.com>
> > wrote:
> >
> > > Art:
> > >
> > > The newer (all the C versions) of aus_refesh wait for update to 5
> minutes
> > > for the aus_evaluator to
> > > complete before starting any real processing. The accomplish this by
> > > checking for the
> > > existence of the aus_command table. If this table exists then the
> > > aus_refresh will
> > > sleep for 15 seconds and check again. It will loop for 20 times before
> > > proceeding
> > > I see now that a maximum of 5 minutes of wait time is to short and will
> > > increase
> > > this wait time.
> > >
> > > John F. Miller III
> > > STSM, Lead Architect
> > > miller3@us.ibm.com
> > > 503-747-1366
> > > IBM Informix Dynamic Server (IDS)
> > >
> > > ids-bounces@iiug.org wrote on 06/27/2014 09:20:31 AM:
> > >
> > > > From: "Art Kagel" <art.kagel@gmail.com>
> > > > To: ids@iiug.org,
> > > > Date: 06/27/2014 09:22 AM
> > > > Subject: Re: Update statistics issue [33302]
> > > > Sent by: ids-bounces@iiug.org
> > > >
> > > > Yes, either disable them or change them to run further apart and rely
> > on
> > > > what they do and disable your own scripts. Right now it looks like
> the
> > > > refresh is running 11 minutes after the evaluator starts running and
> > that
> > >
> > > > can't be right unless your database has very few tables. If you
> decide
> > to
> > >
> > > > keep AUS running and abandon your own scripts then you should look in
> > the
> > >
> > > > ph_run table to see how long the evaluator actually runs for (columns
> > > > duration) and change the scheduling for the AUS refresh task:
> > > >
> > > > select * from ph_run where run_task_id = 18;> > > >
> > > > Art
> > > >
> > > > Art S. Kagel, Principal Consultant
> > > > ASK Database Management
> > > >
> > > > 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 Fri, Jun 27, 2014 at 11:51 AM, medkba <medkba@gmail.com> wrote:
> > > >
> > > > > Thank you very much Art
> > > > >
> > > > > The below output executing the query
> > > > >
> > > > > tk_id 18
> > > > > tk_name Auto Update Statistics Evaluation
> > > > > tk_enable t
> > > > > tk_next_execution 2014-06-28 01:00:00
> > > > >
> > > > > tk_id 19
> > > > > tk_name Auto Update Statistics Refresh
> > > > > tk_enable t
> > > > > tk_next_execution 2014-06-28 01:11:00
> > > > >
> > > > > It means that statistics running automatically. Am i right? I need
> to
> > > > > disable both
> > > > >
> > > > > On Fri, Jun 27, 2014 at 11:39 PM, Art Kagel <art.kagel@gmail.com>
> > > wrote:
> > > > >
> > > > > > There are two AUS tasks, the AUS evaluator that determines what
> > > tables
> > > > > need
> > > > > > their stats updated and the AUS refresh task that executes the
> > > commands
> > > > > > calculated by the evaluator. You can find the task manager
> records
> > > > > > governing their execution in the sysadmin database in the table
> > > ph_task.
> > > > > > Run:
> > > > > >
> > > > > > select tk_id, tk_name, tk_enable, tk_next_execution
> > > > > > from sysadmin:ph_task
> > > > > > where tk_execute matches 'aus*';> > > > > >
> > > > > > If tk_enable is set to 't' then the AUS tasks are running.
> > Obviously
> > > if
> > > > > > the evaluator is executing but the refresh is not, then stats are
> > not
> > >
> > > > > being
> > > > > > updated by AUS and if the refresh is running without the
> evaluator
> > > > > running
> > > > > > before it then the list of work its doing is probably out-of-date
> > and
> > > may
> > > > > > even be empty. The tk_next_execution column will tell you when
> each
> > > is
> > > > > > scheduled to run next. Note that in 11.50 the evaluator was
> rather
> > > > > > inefficient and could take longer to run than the default
> > difference
> > > in
> > > > > the
> > > > > > scheduled run times of the two tasks (1 hour) so that when the
> > > refresh
> > > > > > launched the evaluator may not have finished running resulting in
> > the
> > >
> > > > > > refresh only processing some of the tables that needed to
> updated.
> > > This
> > > > > > was fixed in 11.70 and again in 12.10 by recoding the evaluator
> to
> > > run
> > > > > > faster and by changing the default scheduling of the tasks so
> they
> > > run
> > > > > > further apart.
> > > > > >
> > > > > > Art
> > > > > >
> > > > > > Art S. Kagel, Principal Consultant
> > > > > > ASK Database Management
> > > > > >
> > > > > > Blog: http://informix-myview.blogspot.com/
> > > > > >
> > > > > > Disclaime
Art,
I have given SET PDQPRIORITY 100; UPDATE STATISTICS FOR
TABLE chksum; and job running but not improving i.e. speed
is there any place to check whether PDQPRIORITY takes place?
Thank you very much
On Sat, Jun 28, 2014 at 1:30 AM, medkba <medkba@gmail.com> wrote:
> Art,
>
> Ok, I has enabled AUS
>
> Thank you very much
>
>
>
>
> On Sat, Jun 28, 2014 at 1:18 AM, Art Kagel <art.kagel@gmail.com> wrote:
>
>> Ooo, now you're getting into a bone of contention between very good
>> friends. John wrote the AUS functions and has good reason to think they
>> are doing the best job possible. I wrote dostats and have good reason to
>> think that it does the best job possible. Honestly in most cases both will
>> work excellently. There are some specific databases on which one or the
>> other will either be faster or will produce slightly better quality data
>> distributions. One nice feature of AUS is that it will duplicate your
>> existing levels of distributions if you turn on that flag in the
>> ph_threshold table so you can preset the levels of distributions you want
>> using dostats and then let AUS maintain them over time. You can do the
>> same with my tools by using the myschema --distributions=filename option
>> to
>> generate an SQL script you can run to reproduce/refresh the current level
>> of distributions.
>>
>> Art
>>
>> Art S. Kagel, Principal Consultant
>> ASK Database Management
>>
>> 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 Fri, Jun 27, 2014 at 12:46 PM, medkba <medkba@gmail.com> wrote:
>>
>> > Art and John,
>> >
>> > Which is best way either to do manual or automatic. My Informix engine
>> > version is 11.50 FC9.
>> >
>> > Currently, this job is running manual using shell script on the HP-UX
>> > PA-RISC and recently we have migrated to HP integrity server so that
>> > expectation of this job running faster but it is slower compare to HP-UX
>> >
>> > Thank you very much
>> >
>> > On Sat, Jun 28, 2014 at 12:40 AM, John Miller iii <miller3@us.ibm.com>
>> > wrote:
>> >
>> > > Art:
>> > >
>> > > The newer (all the C versions) of aus_refesh wait for update to 5
>> minutes
>> > > for the aus_evaluator to
>> > > complete before starting any real processing. The accomplish this by
>> > > checking for the
>> > > existence of the aus_command table. If this table exists then the
>> > > aus_refresh will
>> > > sleep for 15 seconds and check again. It will loop for 20 times before
>> > > proceeding
>> > > I see now that a maximum of 5 minutes of wait time is to short and
>> will
>> > > increase
>> > > this wait time.
>> > >
>> > > John F. Miller III
>> > > STSM, Lead Architect
>> > > miller3@us.ibm.com
>> > > 503-747-1366
>> > > IBM Informix Dynamic Server (IDS)
>> > >
>> > > ids-bounces@iiug.org wrote on 06/27/2014 09:20:31 AM:
>> > >
>> > > > From: "Art Kagel" <art.kagel@gmail.com>
>> > > > To: ids@iiug.org,
>> > > > Date: 06/27/2014 09:22 AM
>> > > > Subject: Re: Update statistics issue [33302]
>> > > > Sent by: ids-bounces@iiug.org
>> > > >
>> > > > Yes, either disable them or change them to run further apart and
>> rely
>> > on
>> > > > what they do and disable your own scripts. Right now it looks like
>> the
>> > > > refresh is running 11 minutes after the evaluator starts running and
>> > that
>> > >
>> > > > can't be right unless your database has very few tables. If you
>> decide
>> > to
>> > >
>> > > > keep AUS running and abandon your own scripts then you should look
>> in
>> > the
>> > >
>> > > > ph_run table to see how long the evaluator actually runs for
>> (columns
>> > > > duration) and change the scheduling for the AUS refresh task:
>> > > >
>> > > > select * from ph_run where run_task_id = 18;>> > > >
>> > > > Art
>> > > >
>> > > > Art S. Kagel, Principal Consultant
>> > > > ASK Database Management
>> > > >
>> > > > 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 Fri, Jun 27, 2014 at 11:51 AM, medkba <medkba@gmail.com> wrote:
>> > > >
>> > > > > Thank you very much Art
>> > > > >
>> > > > > The below output executing the query
>> > > > >
>> > > > > tk_id 18
>> > > > > tk_name Auto Update Statistics Evaluation
>> > > > > tk_enable t
>> > > > > tk_next_execution 2014-06-28 01:00:00
>> > > > >
>> > > > > tk_id 19
>> > > > > tk_name Auto Update Statistics Refresh
>> > > > > tk_enable t
>> > > > > tk_next_execution 2014-06-28 01:11:00
>> > > > >
>> > > > > It means that statistics running automatically. Am i right? I
>> need to
>> > > > > disable both
>> > > > >
>> > > > > On Fri, Jun 27, 2014 at 11:39 PM, Art Kagel <art.kagel@gmail.com>
>> > > wrote:
>> > > > >
>> > > > > > There are two AUS tasks, the AUS evaluator that determines what
>> > > tables
>> > > > > need
>> > > > > > their stats updated and the AUS refresh task that executes the
>> > > commands
>> > > > > > calculated by the evaluator. You can find the task manager
>> records
>> > > > > > governing their execution in the sysadmin database in the table
>> > > ph_task.
>> > > > > > Run:
>> > > > > >
>> > > > > > select tk_id, tk_name, tk_enable, tk_next_execution
>> > > > > > from sysadmin:ph_task
>> > > > > > where tk_execute matches 'aus*';>> > > > > >
>> > > > > > If tk_enable is set to 't' then the AUS tasks are running.
>> > Obviously
>> > > if
>> > > > > > the evaluator is executing but the refresh is not, then stats
>> are
>> > not
>> > >
>> > > > > being
>> > > > > > updated by AUS and if the refresh is running without the
>> evaluator
>> > > > > running
>> > > > > > before it then the list of work its doing is probably
>> out-of-date
>> > and
>> > > may
>> > > > > > even be empty. The tk_next_execution column will tell you when
>> each
>> > > is
>> > > > > > scheduled to run next. Note that in 11.50 the evaluator was
>> rather
>> > > > > > inefficient and could take longer to run than the default
>> > difference
>> > > in
>> > > > > the
>> > > > > > scheduled run times of the two tasks (1 hour) so that when the
>> > > refresh
>> > > > > > launched the evaluator may not have finished running resulting
>> in
>> > the
>> > >
>
In your ONCONFIG the parameter MAX_PDQPRIORITY acts as a multiplicative
governor for PDQPRIORITY. So if MAX_PDQPRIORITY is set to 50 (ie 50%) then
setting PDQPRIORITY in a session to 100 only gets you 50% of the server's
resources, setting PDQPRIORITY 50 gets you 25% etc. If MAX_PDQPRIORITY is
set to zero, the PDQPRIORITY settings will always effectively be zero no
matter what you set.
Another thing you can try is to set PSORT_NPROCS to twice the number of CPU
VPs to allow for parallel sorting. This is needed whether PDQPRIORITY is
set or not.
Art
Art S. Kagel, Principal Consultant
ASK Database Management
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 Fri, Jun 27, 2014 at 2:06 PM, medkba <medkba@gmail.com> wrote:
> Art,
>
> I have given SET PDQPRIORITY 100; UPDATE STATISTICS FOR
> TABLE chksum; and job running but not improving i.e. speed
>
> is there any place to check whether PDQPRIORITY takes place?
>
> Thank you very much
>
> On Sat, Jun 28, 2014 at 1:30 AM, medkba <medkba@gmail.com> wrote:
>
> > Art,
> >
> > Ok, I has enabled AUS
> >
> > Thank you very much
> >
> >
> >
> >
> > On Sat, Jun 28, 2014 at 1:18 AM, Art Kagel <art.kagel@gmail.com> wrote:
> >
> >> Ooo, now you're getting into a bone of contention between very good
> >> friends. John wrote the AUS functions and has good reason to think they
> >> are doing the best job possible. I wrote dostats and have good reason to
> >> think that it does the best job possible. Honestly in most cases both
> will
> >> work excellently. There are some specific databases on which one or the
> >> other will either be faster or will produce slightly better quality data
> >> distributions. One nice feature of AUS is that it will duplicate your
> >> existing levels of distributions if you turn on that flag in the
> >> ph_threshold table so you can preset the levels of distributions you
> want
> >> using dostats and then let AUS maintain them over time. You can do the
> >> same with my tools by using the myschema --distributions=filename option
> >> to
> >> generate an SQL script you can run to reproduce/refresh the current
> level
> >> of distributions.
> >>
> >> Art
> >>
> >> Art S. Kagel, Principal Consultant
> >> ASK Database Management
> >>
> >> 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 Fri, Jun 27, 2014 at 12:46 PM, medkba <medkba@gmail.com> wrote:
> >>
> >> > Art and John,
> >> >
> >> > Which is best way either to do manual or automatic. My Informix engine
> >> > version is 11.50 FC9.
> >> >
> >> > Currently, this job is running manual using shell script on the HP-UX
> >> > PA-RISC and recently we have migrated to HP integrity server so that
> >> > expectation of this job running faster but it is slower compare to
> HP-UX
> >> >
> >> > Thank you very much
> >> >
> >> > On Sat, Jun 28, 2014 at 12:40 AM, John Miller iii <miller3@us.ibm.com
> >
> >> > wrote:
> >> >
> >> > > Art:
> >> > >
> >> > > The newer (all the C versions) of aus_refesh wait for update to 5
> >> minutes
> >> > > for the aus_evaluator to
> >> > > complete before starting any real processing. The accomplish this by
> >> > > checking for the
> >> > > existence of the aus_command table. If this table exists then the
> >> > > aus_refresh will
> >> > > sleep for 15 seconds and check again. It will loop for 20 times
> before
> >> > > proceeding
> >> > > I see now that a maximum of 5 minutes of wait time is to short and
> >> will
> >> > > increase
> >> > > this wait time.
> >> > >
> >> > > John F. Miller III
> >> > > STSM, Lead Architect
> >> > > miller3@us.ibm.com
> >> > > 503-747-1366
> >> > > IBM Informix Dynamic Server (IDS)
> >> > >
> >> > > ids-bounces@iiug.org wrote on 06/27/2014 09:20:31 AM:
> >> > >
> >> > > > From: "Art Kagel" <art.kagel@gmail.com>
> >> > > > To: ids@iiug.org,
> >> > > > Date: 06/27/2014 09:22 AM
> >> > > > Subject: Re: Update statistics issue [33302]
> >> > > > Sent by: ids-bounces@iiug.org
> >> > > >
> >> > > > Yes, either disable them or change them to run further apart and
> >> rely
> >> > on
> >> > > > what they do and disable your own scripts. Right now it looks like
> >> the
> >> > > > refresh is running 11 minutes after the evaluator starts running
> and
> >> > that
> >> > >
> >> > > > can't be right unless your database has very few tables. If you
> >> decide
> >> > to
> >> > >
> >> > > > keep AUS running and abandon your own scripts then you should look
> >> in
> >> > the
> >> > >
> >> > > > ph_run table to see how long the evaluator actually runs for
> >> (columns
> >> > > > duration) and change the scheduling for the AUS refresh task:
> >> > > >
> >> > > > select * from ph_run where run_task_id = 18;> >> > > >
> >> > > > Art
> >> > > >
> >> > > > Art S. Kagel, Principal Consultant
> >> > > > ASK Database Management
> >> > > >
> >> > > > 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 Fri, Jun 27, 2014 at 11:51 AM, medkba <medkba@gmail.com>
> wrote:
> >> > > >
> >> > > > > Thank you very much Art
> >> > > > >
> >> > > > > The below output executing the query
> >> > > > >
> >> > > > > tk_id 18
> >> > > > > tk_name Auto Update Statistics Evaluation
> >> > > > > tk_enable t
> >> > > > > tk_next_execution 2014-06-28 01:00:00
> >> > > > >
> >> > > > > tk_id 19
> >> > > > > tk_name Auto Update Statistics Refresh
> >> > > > > tk_enable t
> >> > > > > tk_next_execution 2014-06-28 01:11:00
> >> > > > >
> >> > > > > It means that statistics running automatically. Am i right? I
> >> need to
> >> > > > > disable both
> >> > > > >
> >> > > > > On Fri, Jun 27, 2014 at 11:39 PM, Art Kagel <
> art.kagel@gmail.com>
> >> > > wrote:
> >> > > > >
> >> > > > > > There are two AUS tasks, the AUS evaluator that determines
Just to be clear, it sounds like you are only running the low part of
update statistics whichonly does one thing. Scan the index leaf level While update stats low
will use PDQ only
up to 10 and only on fragmented indexes as it will start a parallel scan on
each index fragment.
There is no sorting done with low, only medium and high. Low in 11.50 is
strictly how many
I/O operations and how fast. You should look at your KAIO or AIO
sub-system and ensure
this is highly tuned. If you are using Informix AIO then ensure that
onstat -g iov has the invertedpyramid. The AIO VP at the bottom is hardly used when update statistics
low is running.
If not then add AIO VPs, if that does not fix the issue then the disks are
too busy.
If you are using KAIO the up the number of KAIO requests, if that does not
work the disks are too busy.
John F. Miller III
STSM, Lead Architect
miller3@us.ibm.com
503-747-1366
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 06/27/2014 11:06:10 AM:
> From: "medkba" <medkba@gmail.com>
> To: ids@iiug.org,
> Date: 06/27/2014 11:07 AM
> Subject: Re: Update statistics issue [33310]
> Sent by: ids-bounces@iiug.org
>
> Art,
>
> I have given SET PDQPRIORITY 100; UPDATE STATISTICS FOR
> TABLE chksum; and job running but not improving i.e. speed
>
> is there any place to check whether PDQPRIORITY takes place?
>
> Thank you very much
>
> On Sat, Jun 28, 2014 at 1:30 AM, medkba <medkba@gmail.com> wrote:
>
> > Art,
> >
> > Ok, I has enabled AUS
> >
> > Thank you very much
> >
> >
> >
> >
> > On Sat, Jun 28, 2014 at 1:18 AM, Art Kagel <art.kagel@gmail.com> wrote:
> >
> >> Ooo, now you're getting into a bone of contention between very good
> >> friends. John wrote the AUS functions and has good reason to think
they
> >> are doing the best job possible. I wrote dostats and have good reason
to
> >> think that it does the best job possible. Honestly in most cases both
will
> >> work excellently. There are some specific databases on which one or
the
> >> other will either be faster or will produce slightly better quality
data
> >> distributions. One nice feature of AUS is that it will duplicate your
> >> existing levels of distributions if you turn on that flag in the
> >> ph_threshold table so you can preset the levels of distributions you
want
> >> using dostats and then let AUS maintain them over time. You can do the
> >> same with my tools by using the myschema --distributions=filename
option
> >> to
> >> generate an SQL script you can run to reproduce/refresh the current
level
> >> of distributions.
> >>
> >> Art
> >>
> >> Art S. Kagel, Principal Consultant
> >> ASK Database Management
> >>
> >> 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 Fri, Jun 27, 2014 at 12:46 PM, medkba <medkba@gmail.com> wrote:
> >>
> >> > Art and John,
> >> >
> >> > Which is best way either to do manual or automatic. My Informix
engine
> >> > version is 11.50 FC9.
> >> >
> >> > Currently, this job is running manual using shell script on the
HP-UX
> >> > PA-RISC and recently we have migrated to HP integrity server so that
> >> > expectation of this job running faster but it is slower compareto
HP-UX
> >> >
> >> > Thank you very much
> >> >
> >> > On Sat, Jun 28, 2014 at 12:40 AM, John Miller iii
<miller3@us.ibm.com>
> >> > wrote:
> >> >
> >> > > Art:
> >> > >
> >> > > The newer (all the C versions) of aus_refesh wait for update to 5
> >> minutes
> >> > > for the aus_evaluator to
> >> > > complete before starting any real processing. The accomplish this
by
> >> > > checking for the
> >> > > existence of the aus_command table. If this table exists then the
> >> > > aus_refresh will
> >> > > sleep for 15 seconds and check again. It will loop for 20 times
before
> >> > > proceeding
> >> > > I see now that a maximum of 5 minutes of wait time is to short and
> >> will
> >> > > increase
> >> > > this wait time.
> >> > >
> >> > > John F. Miller III
> >> > > STSM, Lead Architect
> >> > > miller3@us.ibm.com
> >> > > 503-747-1366
> >> > > IBM Informix Dynamic Server (IDS)
> >> > >
> >> > > ids-bounces@iiug.org wrote on 06/27/2014 09:20:31 AM:
> >> > >
> >> > > > From: "Art Kagel" <art.kagel@gmail.com>
> >> > > > To: ids@iiug.org,
> >> > > > Date: 06/27/2014 09:22 AM
> >> > > > Subject: Re: Update statistics issue [33302]
> >> > > > Sent by: ids-bounces@iiug.org
> >> > > >
> >> > > > Yes, either disable them or change them to run further apart and
> >> rely
> >> > on
> >> > > > what they do and disable your own scripts. Right now it looks
like
> >> the
> >> > > > refresh is running 11 minutes after the evaluator starts running
and
> >> > that
> >> > >
> >> > > > can't be right unless your database has very few tables. If you
> >> decide
> >> > to
> >> > >
> >> > > > keep AUS running and abandon your own scripts then you should
look
> >> in
> >> > the
> >> > >
> >> > > > ph_run table to see how long the evaluator actually runs for
> >> (columns
> >> > > > duration) and change the scheduling for the AUS refresh task:
> >> > > >
> >> > > > select * from ph_run where run_task_id = 18;> >> > > >
> >> > > > Art
> >> > > >
> >> > > > Art S. Kagel, Principal Consultant
> >> > > > ASK Database Management
> >> > > >
> >> > > > 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 Fri, Jun 27, 2014 at 11:51 AM, medkba <medkba@gmail.com>
wrote:
> >> > > >
> >> > > > > Thank you very much Art
> >> > > > >
> >> > > > > The below output executing the query
> >> > > > >
> >> > > > > tk_id 18
> >> > > > > tk_name Auto Update Statistics Evaluation
> >> > > > > tk_enable t
> >> > > > > tk_next_execution 2014-06-28 01:00:00
> >> > > > >
> >> > > > > tk_id 19
> >> > > > > tk_name Auto Update Statistics Refresh
> >> > > > > tk_enable t
> >> > > > > tk_next_execution 2014-06-28 01:11:00
> >> > > > >
> >> > > > > It means that statistics running automatically. Am i right? I
> >> need to
> >> > > > > disable both
> >> > > > >
Does AUS, if set up, run "UPDATE STATISTICS" both medium and high depenting
upon what is needed?
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: Update statistics issue [33296]
> Date: Fri, 27 Jun 2014 09:18:13 -0400
>
> "from a shell script" wasn't what I was looking for. Are you running plan
> UPDATE STATISTICS? UPDATE STATISTICS LOW? UPDATE STATISTICS MEDIUM?
> UPDATE STATISTICS HIGH? Combinations of multiple commands? Are you> running these in dbaccess? If so are you running against just this one
> table in each command or against the entire database? Are you using my
> dostats utility to generate an optimal set of commands?
>
> OK, first thing to do then is to read John's paper. Link here:
>
>
>
http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/miller
/0203miller.html#section3
>
> Quick-and-dirty:
>
> export PDQPRIORITY=100 # Or as high as you dare without disrupting
> production resource requirements.
> export PSORT_NPROCS=16 # I know the max documented is 10, trust me.
> Make sure that you have lots of memory allocated to DS_TOTAL_MEMORY to
> allow for all sorting to be accomplished in memory. If you want to know how
> much memory you will need and you haven't zero'd out your server stats
> since the last time you tried running the update statistics (ie onstat -z)
> then the sysmaster:sysprofile table has a row containing the size of the
> largest sort that's been executed in KB:
>
> select value from sysmaster:sysprofile where name = 'maxsortspace';>
> I think that I remember that you are using 11.50 so you can't take
> advantage or LOW sampling.
>
> BTW, in another reply you said you are only runing vanilla UPDATE
> STATISTICS with no modifiers. This command does not update the data
> distributions for the table, it only updates a few columns in systables,
> syscolumns, sysfragments, and sysindices/sysindexes like nrows and npused
> in systables, colmin and colmax in syscolumns, nlevels, nleaves, uniq,
> clust, and nrows in sysindices. You need to run a combination of LOW,
> MEDIUM, and HIGH on individual sets of columns to get really useful data
> distributions in minimum time. If you use my dostats utility, as do
> hundreds of Informix sites, it will take care of that for you.
>
> BTW, have you disabled Auto Update Statistics (AUS)? Because if not then
> AUS is running every night updating stats for you (almost as well as
> dostats would) so you should not have to be running update statistics
> manually (or from cron) anyway.
>
> Art
>
> Art S. Kagel, Principal Consultant
> ASK Database Management
>
> 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 Fri, Jun 27, 2014 at 8:48 AM, medkba <medkba@gmail.com> wrote:
>
> > Running from cron jobs daily from shell script
> > Not using PDQPRIORITY
> > 23 CPUs
> > 8 cpu vps is used increased from 6 to 8 no improvement
> >
> > Please let me if require more information
> >
> > Thank you very much
> > On 27 Jun 2014 19:43, "Art Kagel" <art.kagel@gmail.com> wrote:
> >
> > > How are you running update statistics for that table? What environment
> > > variables have you set? Are you using PDQPRIORITY? What engine version
> > > and edition are you using? How many cores are on the machine and how many
> > > CPU VPs are configured?
> > >
> > > Have you read John Miller's paper in optimizing update statistics runs?
> > >
> > > Art
> > >
> > > Art S. Kagel, Principal Consultant
> > > ASK Database Management
> > >
> > > 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 Fri, Jun 27, 2014 at 3:30 AM, medkba <medkba@gmail.com> wrote:
> > >
> > > > Hi All,
> > > >
> > > > I am having one table and contains data 23326189 and taking about 15
> > mins
> > > > to complete update statistics
> > > >
> > > > Is there any opportunities to minimize this time?
> > > >
> > > > thank you very much
> > > >
> > > > --001a11c35338ab010704fccc4622
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --001a11c3eaaa3f037b04fccfcec4
> > >
> > >
> > >
> > >
> >
> >
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --089e0158b87c65336e04fcd0b829
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --089e01182b54c5879104fcd123c5
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Normally AUS does:
- LOW on the whole table
- High on leading index columns as described in the Performance Guide
- Medium on the remaining index key columns
I does not produce any distributions for non-indexed columns using its
default settings. There is a flag you can set that will instruct AUS to
replicate whatever level of distributions you already have on your
columns. So, if you want distributions on non-indexed columns (useful if
you join or filter on minor columns that are not indexed) you can manually
(or using dostats) produce those distributions and tell AUS to maintain
them. But, as I said, normally it does not produce them. The last thing
that dostats does that AUS does not is to perform a LOW on each complete
index key. Some of the developers working on the optimizer have told me
that this can help, others disagree. When I wrote dostats I adhered to the
former group's recommendation. When John Miller wrote AUS he followed the
latter group's advice.
Art
Art S. Kagel, Principal Consultant
ASK Database Management
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 Mon, Jun 30, 2014 at 1:59 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
> Does AUS, if set up, run "UPDATE STATISTICS" both medium and high depenting
> upon what is needed?
>
> > To: ids@iiug.org
> > From: art.kagel@gmail.com
> > Subject: Re: Update statistics issue [33296]
> > Date: Fri, 27 Jun 2014 09:18:13 -0400
> >
> > "from a shell script" wasn't what I was looking for. Are you running plan
> > UPDATE STATISTICS? UPDATE STATISTICS LOW? UPDATE STATISTICS MEDIUM?
> > UPDATE STATISTICS HIGH? Combinations of multiple commands? Are you> > running these in dbaccess? If so are you running against just this one
> > table in each command or against the entire database? Are you using my
> > dostats utility to generate an optimal set of commands?
> >
> > OK, first thing to do then is to read John's paper. Link here:
> >
> >
> >
>
>
http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/miller
/0203miller.html#section3
> >
> > Quick-and-dirty:
> >
> > export PDQPRIORITY=100 # Or as high as you dare without disrupting
> > production resource requirements.
> > export PSORT_NPROCS=16 # I know the max documented is 10, trust me.
> > Make sure that you have lots of memory allocated to DS_TOTAL_MEMORY to
> > allow for all sorting to be accomplished in memory. If you want to know
> how
> > much memory you will need and you haven't zero'd out your server stats
> > since the last time you tried running the update statistics (ie onstat
> -z)
> > then the sysmaster:sysprofile table has a row containing the size of the
> > largest sort that's been executed in KB:
> >
> > select value from sysmaster:sysprofile where name = 'maxsortspace';> >
> > I think that I remember that you are using 11.50 so you can't take
> > advantage or LOW sampling.
> >
> > BTW, in another reply you said you are only runing vanilla UPDATE
> > STATISTICS with no modifiers. This command does not update the data
> > distributions for the table, it only updates a few columns in systables,
> > syscolumns, sysfragments, and sysindices/sysindexes like nrows and npused
> > in systables, colmin and colmax in syscolumns, nlevels, nleaves, uniq,
> > clust, and nrows in sysindices. You need to run a combination of LOW,
> > MEDIUM, and HIGH on individual sets of columns to get really useful data
> > distributions in minimum time. If you use my dostats utility, as do
> > hundreds of Informix sites, it will take care of that for you.
> >
> > BTW, have you disabled Auto Update Statistics (AUS)? Because if not then
> > AUS is running every night updating stats for you (almost as well as
> > dostats would) so you should not have to be running update statistics
> > manually (or from cron) anyway.
> >
> > Art
> >
> > Art S. Kagel, Principal Consultant
> > ASK Database Management
> >
> > 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 Fri, Jun 27, 2014 at 8:48 AM, medkba <medkba@gmail.com> wrote:
> >
> > > Running from cron jobs daily from shell script
> > > Not using PDQPRIORITY
> > > 23 CPUs
> > > 8 cpu vps is used increased from 6 to 8 no improvement
> > >
> > > Please let me if require more information
> > >
> > > Thank you very much
> > > On 27 Jun 2014 19:43, "Art Kagel" <art.kagel@gmail.com> wrote:
> > >
> > > > How are you running update statistics for that table? What
> environment
> > > > variables have you set? Are you using PDQPRIORITY? What engine
> version
> > > > and edition are you using? How many cores are on the machine and how
> many
> > > > CPU VPs are configured?
> > > >
> > > > Have you read John Miller's paper in optimizing update statistics
> runs?
> > > >
> > > > Art
> > > >
> > > > Art S. Kagel, Principal Consultant
> > > > ASK Database Management
> > > >
> > > > 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 Fri, Jun 27, 2014 at 3:30 AM, medkba <medkba@gmail.com> wrote:
> > > >
> > > > > Hi All,
> > > > >
> > > > > I am having one table and contains data 23326189 and taking about
> 15
> > > mins
> > > > > to complete update statistics
> > > > >
> > > > > Is there any opportunities to minimize this time?
> > > > >
> > > > > thank you very much
> > > > >
> > > > > --001a11c35338ab010704fccc4622
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
>
>
*******************************************************************************
> > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > >
> > > > >
> > > >
> > > > --001a11c3eaaa3f037b04fccfcec4
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
>
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to
Thank you. That was helpful.
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: Update statistics issue [33330]
> Date: Mon, 30 Jun 2014 18:10:54 -0400
>
> Normally AUS does:
>
> - LOW on the whole table
>
> - High on leading index columns as described in the Performance Guide
>
> - Medium on the remaining index key columns
>
> I does not produce any distributions for non-indexed columns using its
> default settings. There is a flag you can set that will instruct AUS to
> replicate whatever level of distributions you already have on your
> columns. So, if you want distributions on non-indexed columns (useful if
> you join or filter on minor columns that are not indexed) you can manually
> (or using dostats) produce those distributions and tell AUS to maintain
> them. But, as I said, normally it does not produce them. The last thing
> that dostats does that AUS does not is to perform a LOW on each complete
> index key. Some of the developers working on the optimizer have told me
> that this can help, others disagree. When I wrote dostats I adhered to the
> former group's recommendation. When John Miller wrote AUS he followed the
> latter group's advice.
>
> Art
>
> Art S. Kagel, Principal Consultant
> ASK Database Management
>
> 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 Mon, Jun 30, 2014 at 1:59 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
>
> > Does AUS, if set up, run "UPDATE STATISTICS" both medium and high depenting
> > upon what is needed?
> >
> > > To: ids@iiug.org
> > > From: art.kagel@gmail.com
> > > Subject: Re: Update statistics issue [33296]
> > > Date: Fri, 27 Jun 2014 09:18:13 -0400
> > >
> > > "from a shell script" wasn't what I was looking for. Are you running plan
> > > UPDATE STATISTICS? UPDATE STATISTICS LOW? UPDATE STATISTICS MEDIUM?
> > > UPDATE STATISTICS HIGH? Combinations of multiple commands? Are you> > > running these in dbaccess? If so are you running against just this one
> > > table in each command or against the entire database? Are you using my
> > > dostats utility to generate an optimal set of commands?
> > >
> > > OK, first thing to do then is to read John's paper. Link here:
> > >
> > >
> > >
> >
> >
>
http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/miller
/0203miller.html#section3
> > >
> > > Quick-and-dirty:
> > >
> > > export PDQPRIORITY=100 # Or as high as you dare without disrupting
> > > production resource requirements.
> > > export PSORT_NPROCS=16 # I know the max documented is 10, trust me.
> > > Make sure that you have lots of memory allocated to DS_TOTAL_MEMORY to
> > > allow for all sorting to be accomplished in memory. If you want to know
> > how
> > > much memory you will need and you haven't zero'd out your server stats
> > > since the last time you tried running the update statistics (ie onstat
> > -z)
> > > then the sysmaster:sysprofile table has a row containing the size of the
> > > largest sort that's been executed in KB:
> > >
> > > select value from sysmaster:sysprofile where name = 'maxsortspace';> > >
> > > I think that I remember that you are using 11.50 so you can't take
> > > advantage or LOW sampling.
> > >
> > > BTW, in another reply you said you are only runing vanilla UPDATE
> > > STATISTICS with no modifiers. This command does not update the data
> > > distributions for the table, it only updates a few columns in systables,
> > > syscolumns, sysfragments, and sysindices/sysindexes like nrows and npused
> > > in systables, colmin and colmax in syscolumns, nlevels, nleaves, uniq,
> > > clust, and nrows in sysindices. You need to run a combination of LOW,
> > > MEDIUM, and HIGH on individual sets of columns to get really useful data
> > > distributions in minimum time. If you use my dostats utility, as do
> > > hundreds of Informix sites, it will take care of that for you.
> > >
> > > BTW, have you disabled Auto Update Statistics (AUS)? Because if not then
> > > AUS is running every night updating stats for you (almost as well as
> > > dostats would) so you should not have to be running update statistics
> > > manually (or from cron) anyway.
> > >
> > > Art
> > >
> > > Art S. Kagel, Principal Consultant
> > > ASK Database Management
> > >
> > > 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 Fri, Jun 27, 2014 at 8:48 AM, medkba <medkba@gmail.com> wrote:
> > >
> > > > Running from cron jobs daily from shell script
> > > > Not using PDQPRIORITY
> > > > 23 CPUs
> > > > 8 cpu vps is used increased from 6 to 8 no improvement
> > > >
> > > > Please let me if require more information
> > > >
> > > > Thank you very much
> > > > On 27 Jun 2014 19:43, "Art Kagel" <art.kagel@gmail.com> wrote:
> > > >
> > > > > How are you running update statistics for that table? What
> > environment
> > > > > variables have you set? Are you using PDQPRIORITY? What engine
> > version
> > > > > and edition are you using? How many cores are on the machine and how
> > many
> > > > > CPU VPs are configured?
> > > > >
> > > > > Have you read John Miller's paper in optimizing update statistics
> > runs?
> > > > >
> > > > > Art
> > > > >
> > > > > Art S. Kagel, Principal Consultant
> > > > > ASK Database Management
> > > > >
> > > > > 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 Fri, Jun 27, 2014 at 3:30 AM, medkba <medkba@gmail.com> wrote:
> > > > >
> > > > > > Hi All,
> > > > > >
> > > > > > I am having one table and contains data 23326189 and taking about
> > 15
> > > > mins
> > > > > > to complete update statistics
> > > > > >
> > > > > > Is there any opportunities to minimize this time?
> > > > > >
> > > > > > thank you very much
> > > > > >
> > > > > > --001a11c35338ab010704fccc4622
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
Related threads
- IDS not writing to online.log
- Help!!! syntax error
- installclientsdk bug?
- RamDisk tempdbs boot script for Linux