UPDATE STATISTICS

Dear friends, As our application be running slow since DB migration is done. Informix version: 12.10 FC8 Application developed in Java and server running under JBOSS. OS: windows 2012 R2. I have done DB migration from old server to new server which is of same hardware and software using dbexport and dbimport utilities. and I have rebuild the indexes of entire database using "oncheck -ci" and i have run the update statistics in LOW mode. After the migration our application is working very slow. I have raised PMR. They have suggested to do UPDATE STATISTICS in HIGH mode for particular huge tables and on columns having indexes. So I have studied some where like running UPDATE STATISTICS in HIGH mode is a risky. Please suggest on this if i run UPDATE STATISTICS in HIGH mode do we have any impact or any data loss. Command i want to run in production server is : UPDATE STATISTICS HIGH FOR TABLE <table_name> (Column_1, column2,....) and also please suggest when to run this statistics. when users connected to application or shall i down the application, i want impact analysis on this.

According to the book, you want to update stats high. In real life I do not recommend that against a huge table. I would instead go medium for large tables. Also set PSORT_NPROCS=10 from the environment, theoretically that helps. There should not be any real impact to users, things might get a little slow, but there is no risk of data loss that I have ever seen. Did you partition the tables in any way shape or form? cheers j. > On Jan 23, 2018, at 12:47 PM, MUKESH TANUKU <mukeshbt1328@gmail.com> wrote: > > Dear friends, > As our application be running slow since DB migration is done. > > Informix version: 12.10 FC8 > Application developed in Java and server running under JBOSS. > OS: windows 2012 R2. > > I have done DB migration from old server to new server which is of same > hardware and software using dbexport and dbimport utilities. > and I have rebuild the indexes of entire database using "oncheck -ci" and i > have run the update statistics in LOW mode. > > After the migration our application is working very slow. I have raised PMR. > > They have suggested to do UPDATE STATISTICS in HIGH mode for particular huge > tables and on columns having indexes. > > So I have studied some where like running UPDATE STATISTICS in HIGH mode is a > risky. > > Please suggest on this if i run UPDATE STATISTICS in HIGH mode do we have any > impact or any data loss. > > Command i want to run in production server is : > UPDATE STATISTICS HIGH FOR TABLE <table_name> (Column_1, column2,....)> > and also please suggest when to run this statistics. when users connected to > application or shall i down the application, i want impact analysis on this. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

We didn't do any partitions for tables. But we have more than 40 lakhs records in table which we thought to run statisitcs, So as preferred can i go with medium UPDATE STATISTICS MEDIUM FOR TABLE <table_name> (Column_1, column2,....)

And also i did not found any PSORT_NPROCS environment set my onconfig parameters are MAX_PDQPRIORITY 100 DS_MAX_QUERIES 4 DS_TOTAL_MEMORY 26214400 DS_MAX_SCANS 5 DS_NONPDQ_QUERY_MEM 2048 suggest me only if required to set PSORT_NPROCS=10 and also how to set?

Update statistics medium for table <table> (<col>, <col>) where columns arecolumns from your indexes. There is more to it than that, but this is sufficient. from the shell, before you start your dbaccess (or whatever) session: export PSORT_NPROCS=10 Actually any number between 2 and 10. This is to reflect the number of CPUs, or cores you are using. Obviously if you are running with Windows that would need to be set PSORT_NPROCS=10 and you would need your head examined. If the table is slow, you should consider partitioning it according to how it is used by your application. Thats a lengthy and separate discussion. Here I am giving out advice with no idea what hardware youre on, how big your database is, OS or anything - Your Mileage May Vary drastically. cheers j. > On Jan 23, 2018, at 1:09 PM, MUKESH TANUKU <mukeshbt1328@gmail.com> wrote: > > We didn't do any partitions for tables. > > But we have more than 40 lakhs records in table which we thought to run > statisitcs, > > So as preferred can i go with medium > > UPDATE STATISTICS MEDIUM FOR TABLE <table_name> (Column_1, column2,....)> > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Thanks for your valuable info. I will go ahead with this info.

> According to the book, you want to update stats high. In real life I do not > recommend that against a huge table. I would instead go medium for large > tables. Also set PSORT_NPROCS=10 from the environment, theoretically that > helps. I hate to be controversial but I have seen both these bits of advice given on the forum before now and I think they need to come with health warnings. Using any form of parallelism for UPDATE STATISTICS HIGH or MEDIUM makes it take longer while at the same time doing more I/O and consuming more CPU VPs. The only effective way to get through a large amount of data quickly is to do multiple tables in parallel with PDQ off. I have spent quite a lot of time testing this to come to this conclusion. Going for UPDATE STATISTICS MEDIUM for large tables will certainly complete the stats run a lot faster and it might work ok for you but you risk having insufficient resolution in your distributions for the optimizer to choose the right query plan. More generally I would say that if you deviate from the recommendations of HIGH mode on leading index columns, medium mode on non-leading index columns, you should do your own testing to make sure your apps and SQL still perform well. If you employ the strategy of switching off PDQ, running a few stats streams in parallel you might not need to consider moving to medium level distributions for large tables. Ben.

> They have suggested to do UPDATE STATISTICS in HIGH mode for particular huge > tables and on columns having indexes. This is good advice. > So I have studied some where like running UPDATE STATISTICS in HIGH mode is > risky. No it isn't risky, where have you seen this? > Please suggest on this if i run UPDATE STATISTICS in HIGH mode do we have any > impact or any data loss. Data loss - no chance of this happening. Impact - some I/O, some CPU resources used, will increment table version numbers and cause recompilation of stored procedures. The engine will handle this all internally though so you shouldn't need to worry about it. I would strongly suggest setting AUTO_REPREPARE to 1 in your onconfig to make sure it all works smoothly. > Command i want to run in production server is : > UPDATE STATISTICS HIGH FOR TABLE <table_name> (Column_1, column2,....) Why go down this route? Auto Update Statistics (AUS) is built into the database engine and will manage this for you. If you want to use AUS you can use Art Kagel's 'dostats' instead, part of utils2_ak package in the IIUG Software Repository. You probably do not need or want a home brew solution. > and also please suggest when to run this statistics. when users connected to > application or shall i down the application, i want impact analysis on this. See above for the impact. You may want to run it at a quiet time as it will need some CPU and disk resources but otherwise any time is fine. There is definitely no need to stop applications. Hope this helps. Ben.

+1 j. > On Jan 25, 2018, at 8:10 AM, BENJAMIN THOMPSON <benjamin.thompson@skybettingandgaming.com> wrote: > >> According to the book, you want to update stats high. In real life I do not >> recommend that against a huge table. I would instead go medium for large >> tables. Also set PSORT_NPROCS=10 from the environment, theoretically that >> helps. > > I hate to be controversial but I have seen both these bits of advice given on > the forum before now and I think they need to come with health warnings. > > Using any form of parallelism for UPDATE STATISTICS HIGH or MEDIUM makes it > take longer while at the same time doing more I/O and consuming more CPU VPs. > The only effective way to get through a large amount of data quickly is to do > multiple tables in parallel with PDQ off. I have spent quite a lot of time > testing this to come to this conclusion. > > Going for UPDATE STATISTICS MEDIUM for large tables will certainly complete > the stats run a lot faster and it might work ok for you but you risk having > insufficient resolution in your distributions for the optimizer to choose the > right query plan. More generally I would say that if you deviate from the > recommendations of HIGH mode on leading index columns, medium mode on > non-leading index columns, you should do your own testing to make sure your > apps and SQL still perform well. > > If you employ the strategy of switching off PDQ, running a few stats streams > in parallel you might not need to consider moving to medium level > distributions for large tables. > > Ben. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

My point about not doing it for large tables, I have seen customers perform an update stats high every night on a particularly large table. At times the job takes longer than 24 hours to complete. Depending on the environment, there may be no value to extended statistics. The optimizer is likely to choose the same path to resolve a query, but YMMV, each situation should be investigated on its own merits. I appreciate the deeper dive youve provided here. cheers j. > On Jan 25, 2018, at 8:26 AM, BENJAMIN THOMPSON <benjamin.thompson@skybettingandgaming.com> wrote: > >> They have suggested to do UPDATE STATISTICS in HIGH mode for particular huge >> tables and on columns having indexes. > > This is good advice. > >> So I have studied some where like running UPDATE STATISTICS in HIGH mode is >> risky. > > No it isn't risky, where have you seen this? > >> Please suggest on this if i run UPDATE STATISTICS in HIGH mode do we have > any >> impact or any data loss. > > Data loss - no chance of this happening. > Impact - some I/O, some CPU resources used, will increment table version > numbers and cause recompilation of stored procedures. The engine will handle > this all internally though so you shouldn't need to worry about it. > > I would strongly suggest setting AUTO_REPREPARE to 1 in your onconfig to make > sure it all works smoothly. > >> Command i want to run in production server is : >> UPDATE STATISTICS HIGH FOR TABLE <table_name> (Column_1, column2,....)> > Why go down this route? Auto Update Statistics (AUS) is built into the > database engine and will manage this for you. If you want to use AUS you can > use Art Kagel's 'dostats' instead, part of utils2_ak package in the IIUG > Software Repository. You probably do not need or want a home brew solution. > >> and also please suggest when to run this statistics. when users connected to >> application or shall i down the application, i want impact analysis on this. > > See above for the impact. You may want to run it at a quiet time as it will > need some CPU and disk resources but otherwise any time is fine. There is > definitely no need to stop applications. > > Hope this helps. > Ben. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

> My point about not doing it for large tables, I have seen customers > perform an update stats high every night on a particularly large table. > At times the job takes longer than 24 hours to complete. Yes this can be a problem although I would add that it shouldn't be necessary in most cases to update stats daily on every table. Ben.

I did point that out to them. ;-) j. > On Jan 25, 2018, at 9:56 AM, BENJAMIN THOMPSON <benjamin.thompson@skybettingandgaming.com> wrote: > >> My point about not doing it for large tables, I have seen customers >> perform an update stats high every night on a particularly large table. >> At times the job takes longer than 24 hours to complete. > > Yes this can be a problem although I would add that it shouldn't be necessary > in most cases to update stats daily on every table. > > Ben. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Thanks for your response in deep level. It helps me in good understanding. Thanks a lot.