Tuesday, May 5, 2015

"MAKEPLAN" & "UPDATESTATS" increase runtime

Why the runtime for jobs MAKEPLAN & UPDATESTATS from FINAL job stream it is increasing steadily day by day?

The main reason is that there are "left overs" in the plan or preproduction plan

1. Clean the old jobs / job streams from the plan. My recommendation is to set-up an automatically clean-up job that is canceling the +N weeks older job streams (in my experience I've discovered that we can safely cancel the +2 weeks old job streams).
conman "ss @#@"  # check the SchedTime 
2. Clean the "junk data" data from preproduction plan.
Do i have "junk data" in pre-production plan? In order to check run:
planman showinfo
And check the below line time stamp against you oldest job stream from the plan, if it is older a manual DB "junk data" clean-up must be performed.
Start time of first not complete preproduction plan job stream instance: 04/20/2015 00:04 TZ America/Los_Angeles
Connect to the TWS database check and delete the following:
a) All records from JSI_JOB_STREAM_INSTANCES older than the oldest job stream from the plan (usually the number of records should be low).


    JSI_ID JSI_SYM_ID       JSI_FIRST_RUN_NUMBER JSI_LAST_RUN_NUMBER JSI_SCHEDULED_DATE JSI_START_TIME                  JSI_STATUS JST_ID                           RCY_ID                           WKC_ID                           VAT_ID                           PLAN_ID                       
---------- ---------------- -------------------- ------------------- ------------------ ------------------------------- ---------- -------------------------------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
  10742074 0AAAAAAAAAAKH2J2                  863                 863 06-APR-15          05-APR-15 PM C          5A9D352A24D53AE78074D760D7CCB44B                                  DF397BE179E632FAA7892A3BBF01B179                                                                  
  10507507 0AAAAAAAAAAKAVHT                  848                 849 19-APR-15          20-APR-15 AM C          530A93D37FCA394E8144EA089708ECA6 33FCBC96CB62363AA4A1BC87D74EF655 38C431C7F7563087B4DF692EF152AB5A                                                                  
  10435375 0AAAAAAAAAAJ6OZP                  848                 848 19-APR-15          19-APR-15 PM C          6506E033D41F37219ABA3E6D4D2E46E0 01C08911A19E377CA1D75AE5A4FB81C1 DF397BE179E632FAA7892A3BBF01B179                                                                  
  10496296 0AAAAAAAAAAKAKJI                  848                 848 19-APR-15          20-APR-15 AM C          B7390188014E32A6BCADBE87A68B93F9 4E0BBC5B7DD432ACBA7F15AE621EA5C6 9B18F62CEE2532919612989A031EF052                                                                  
  10496303 0AAAAAAAAAAKAKJP                  848                 848 19-APR-15          20-APR-15 AM C          B5F7FDF861D133F0B75E6940CF97A39F B5C442B6CD653F4790ADCF25AE3F54C0 9B18F62CEE2532919612989A031EF052                                                                  
  10496310 0AAAAAAAAAAKAKJW                  848                 848 19-APR-15          20-APR-15 AM C          7A68C1570A9D3364845CB3F6D6A7DC0F 75C767CDAD0E33D4ACCD11076A7854D4 9B18F62CEE2532919612989A031EF052                                                                  
  10496317 0AAAAAAAAAAKAKJ5                  848                 849 19-APR-15          20-APR-15 AM C          190E13C8D2F633F88E53A7AC78FA2ACD 816CED621ADD3B02B4C71D30B21E6D5B 9B18F62CEE2532919612989A031EF052                                                                  

b) All invalid entries from preproduction plan:
select count(*) from JSI_JOB_STREAM_INSTANCES where PLAN_ID is not null;


delete from JSI_JOB_STREAM_INSTANCES where PLAN_ID is not null;

On the next run "MAKEPLAN" & "UPDATESTATS" will decrease, in some cases the new runtime could be ~60-80% less.

No comments:

Post a Comment