IDS Forum

Enable or Apply Auto Update Statistics

Posted By: PRAVIN BANKAR
Date: Friday, 2 October 2015, at 7:11 a.m.

Hi All,

Here is email communication between Ben and myself. I hope this will help someone who really want to play with AUS feature.

------------------------------------------------------------------------------------------------------------------------------------------
Hi Ben,

Can you please help me to enable the AUS at my test box?
I configured the parameters on 09/07/2015, still all the records/commands state in aus_command.aus_cmd_state shows as “P”=> Pending

Please correct me, if I am wrong, I am assuming AUS will be start its work once I enables scheduler task, AUS evaluation & refresh tasks and set the AUS parameters as per requirement.
If this is correct, then I am still facing the aus_cmd_state in aus_command as P (Pending). Please suggest.

Here are AUS related details on my test box:
AUS parameters:
AUS_STAT_MODE => 1
STATCHANGE => 0
USTLOW_SAMPLE => 0
AUS_AGE => 7
AUS_PDQ => 10
AUS_CHANGE => 0
AUS_AUTO_RULES => 1
AUS_SMALL_TABLES => 100

AUS evaluation & refresh tasks status
SELECT tk_id, tk_name, tk_sequence, tk_next_execution tk_enable
FROM ph_task
WHERE tk_name like 'Auto Update Statistics%';

tk_id tk_name tk_sequence tk_next_execution tk_enable
54 Auto Update Statistics Evaluation 11 NULL t
55 Auto Update Statistics Refresh 4 NULL t
57 Auto Update Statistics Refresh 2 2 9/7/2015 16:48 t
59 Auto Update Statistics Refresh 3 2 9/7/2015 16:50 t
61 Auto Update Statistics Refresh 4 2 9/8/2015 9:37 t

dbScheduler and dbWorker thread statistics
onstat –g dbc => Print dbScheduler and dbWorker thread statistics
We can get result if executed scheduler task (EXECUTE FUNCTION task("scheduler start");)
Else get “Database Scheduler is Disabled.” Message for onstat –g dbc command.

$onstat -g dbc

IBM Informix Dynamic Server Version 11.70.FC8W1 -- On-Line -- Up 1 days 22:59:02 -- 163228788 Kbytes
Worker Thread(1) 1b3ac6c4c0
=====================================
Task: 1b3af61028
Task Name: post_alarm_message (20-471497)
Task Type: TASK
Task Execution: ph_dbs_alert
WORKER PROFILE

Total Jobs Executed 1434

Sensors Executed 67

Tasks Executed 1367

Purge Requests 1434

Rows Purged 0

Errors 0

Worker Thread(2) 1b3ac6c540
=====================================
Task: 1b3af614c0
Task Name: post_alarm_message (20-471498)
Task Type: TASK
Task Execution: ph_dbs_alert
WORKER PROFILE

Total Jobs Executed 1437

Sensors Executed 60

Tasks Executed 1377

Purge Requests 1437

Rows Purged 0

Errors 0

Scheduler Thread 1b39fb6e78
=====================================
Next Task 51
Next Task Waittime 2458
General Queue size 7

full 0

empty 7
Private Queue size 12

full 0

empty 12
PRIVATE QUEUE TASKS

Total Jobs Executed 22

Sensors Executed 2

Tasks Executed 20

Purge Requests 22

Rows Purged 0

Errors 0
$

Please let me know, if you need any other details. Thank you.
------------------------------
Thanks & Regards,
Pravin Bankar

------------------------------------------------------------------------------------------------------------------------------------------
Hi Pravin,

If your commands are all pending it is because the refresh job(s) have not run or were not allowed enough time to do anything.

Two things stand out here.

1. Your database scheduler should always be running. If it is not, it could be because you have a stop file ($INFORMXDIR/etc/sysadmin/stop). If you do, remove it. Another way to check it's running is 'onstat -g ath | grep dbScheduler'.
2. Your refresh task with tk_id=55 appears not be working (also the evaluation task). There is a bug where just setting tk_enable='t' without changing the times or days of the week does not fire the trigger that updates the next execution times. Try changing the days of the week or times of the jobs: this should set the tk_next_execution date to something in the future. The other dates are all in the past, no idea why. Again changing the days of the week or the time should solve this.

Ben Thompson
Principal DBA
------------------------------------------------------------------------------------------------------------------------------------------
Thanks a lot Ben for valuable information.

Just want to clarify for 1st point:
There is no stop file in $INFORMXDIR/etc/sysadmin/stop this directory; whereas dbScheduler showing as sleeping state for 'onstat -g ath | grep dbScheduler'.
Will this be start running by below commands:
EXECUTE FUNCTION task("scheduler shutdown");
EXECUTE FUNCTION task("scheduler start");

Thanks again for your guidance & valuable time.

------------------------------
Thanks & Regards,
Pravin Bankar

------------------------------------------------------------------------------------------------------------------------------------------
If your commands are all pending it is because the refresh job(s) have not run or were not allowed enough time to do anything.

Two things stand out here.

1. Your database scheduler should always be running. If it is not, it could be because you have a stop file ($INFORMXDIR/etc/sysadmin/stop). If you do, remove it. Another way to check it's running is 'onstat -g ath | grep dbScheduler'.
2. Your refresh task with tk_id=55 appears not be working (also the evaluation task). There is a bug where just setting tk_enable='t' without changing the times or days of the week does not fire the trigger that updates the next execution times. Try changing the days of the week or times of the jobs: this should set the tk_next_execution date to something in the future. The other dates are all in the past, no idea why. Again changing the days of the week or the time should solve this.

Ben Thompson
Principal DBA
-----------------------------------------------------------------------------------------------------------------------------------------
It's ok for it to be sleeping. You can try stopping and starting it. I am pretty sure that it will still be sleeping most of the time after that.

Ben Thompson
Principal DBA
-----------------------------------------------------------------------------------------------------------------------------------------
Sure Ben.
Thanks a lot!

Have a great time a head!!!
------------------------------
Thanks & Regards,
Pravin Bankar
-----------------------------------------------------------------------------------------------------------------------------------------
Hello Ben,

I have tried for stopping & starting scheduler task; but nothing happened.

Also I tried for second point for updating the future time in tk_next_execution column by keeping 5 to 10 minutes difference for each Evaluation & Refresh task.
Once again I stopped & started scheduler task after updating time & before they start to execute.
They executed as per their time mentioned in tk_next_execution column, but once they completed the value became NULL for all the tasks.

tk_id tk_name tk_sequence tk_next_execution tk_enable
54 Auto Update Statistics Evaluation 11 NULL t
55 Auto Update Statistics Refresh 4 NULL t
57 Auto Update Statistics Refresh 2 2 NULL t
59 Auto Update Statistics Refresh 3 2 NULL t
61 Auto Update Statistics Refresh 4 2 NULL t

Above mentioned each task scheduled for each day (Monday - Sunday) that is "t", but not sure why time became NULL after execution.
Also all the commands from aus_command table remain as P (Pending).

I think I am missing something, but not sure what is that. I am not able to figure out why the AUS commands not getting executed.
Do I have to refer mon_table_profile or mon_table_names or any other tables from sysadmin for any configuration so that I able to enable the AUS & commands from aus_command table will get executed.

Please suggest. Thank you Ben!
------------------------------
Thanks & Regards,
Pravin Bankar
-----------------------------------------------------------------------------------------------------------------------------------------
Hi,

Have you tried running the refresh tasks manually using the exectask function? What happens?

I'd need to see all the columns in the ph_task table. There is nothing obviously wrong from the information in your email.

Ben Thompson
Principal DBA
-----------------------------------------------------------------------------------------------------------------------------------------
Thank you Ben,

I have tried the Evaluation task followed by Refresh task using exectask() Function at my local machine which is on windows with IDS 11.70.FC8TL.
All the commands from aus_command table got executed successfully with aus_cmd_state as "C".
Also aus_cmd_comp table got updated accordingly. Evaluation & Refresh tasks in ph_task table got updated with future date & time also tk_sequence got updated by one as it got executed once by Refresh task using exectask() Function.

I have to test these steps (Evaluation and Refresh task using exectask() Function) at my test box.
I hope, this will work :)

I will keep you posted for same.

Thanks a lot once again.
------------------------------
Thanks & Regards,
Pravin Bankar
-----------------------------------------------------------------------------------------------------------------------------------------
Hello Ben,
Thank you for valuable guidance on AUS.

In initial testing of AUS using exectask() function for evaluation & refresh tasks at my test box, evaluation task did its work correctly, but for refresh task after execution of task, tk_next_execution set to NULL.
But when I referred my laptop testing, I came across new thing that is at my test box the value for ph_task columns (that is tk_frequency, tk_start_time & tk_stop_time) was NULL.
As soon as I set the recommended timings for evaluation & refresh tasks (that is between 1:00 - 5:00), & executed the evaluation and refresh tasks using exectask() function, it ran perfectly also the value for tk_next_column set to next day.

I am trying to collect the points for monitoring of AUS for few days on test box so that I can play with changing the parameters & see the performance & query execution.
And based on the results at test box, I will implement on prod box.
-----------------------------
Thanks & Regards,
Pravin Bankar
-----------------------------------------------------------------------------------------------------------------------------------------

IDS Forum is maintained by Administrator with WebBBS 5.12.