Adel_HPOO_1020 Super Contributor.
Super Contributor.
359 views

oo schecduler issue

Hi everyone,

This post is to search for a solution to my OO 10.20 which stops executing some schedules for some time (eg: 30 min, no schedule is executed ) and than it comes back to a normal behaviour after this period.

When this behaviour appears for the last time, i took a look to the monitoring page, and there are many values in red (see attachment).

Thanks in advance.

0 Likes
17 Replies
Outstanding Contributor.. JarodMB Outstanding Contributor..
Outstanding Contributor..

Re: oo schecduler issue

Toward the bottom of the page expand and share a screen shot of the "System Information",  "Jobs" and "Statistics Job - 1 day" sections

0 Likes
Adel_HPOO_1020 Super Contributor.
Super Contributor.

Re: oo schecduler issue

Here are the requested screenshots.

0 Likes
Outstanding Contributor.. JarodMB Outstanding Contributor..
Outstanding Contributor..

Re: oo schecduler issue

Can you connect to the OO DB, execute the following query, and share the results

select T.TRIGGER_NAME, T.TRIGGER_STATE, ST.REPEAT_INTERVAL
from OO_TRIGGERS T, OO_SIMPLE_TRIGGERS ST
where T.JOB_NAME <> 'jobDetail' and T.TRIGGER_NAME = ST.TRIGGER_NAME
0 Likes
Outstanding Contributor.. JarodMB Outstanding Contributor..
Outstanding Contributor..

Re: oo schecduler issue

If you could also share 

# of Centrals, RAS in the environment

DB Size on disk, proc, mem
DB Provider (SQL Server 2012?)

List of indexes and their fragmentation level
Size of tables (number of records in each table)

0 Likes
Adel_HPOO_1020 Super Contributor.
Super Contributor.

Re: oo schecduler issue

Here are some information:

Query result:

auditDataPurgerTrigger                                           WAITING 432000000
deploymentKeepAliveJobTrigger                       WAITING 300000
l10nDataCleanTrigger                                              WAITING 86400000
oldDeploymentProcessesCleanTrigger            WAITING 86400000
onRolling:OO_EXECUTION_EVENTS_Trigger WAITING 43200000
onRolling:OO_EXECUTION_STATES_Trigger  WAITING 270000
queueCleanerTrigger                                                WAITING 60000
queueRecoveryTrigger                                             WAITING 120000
recoveryVersionTrigger                                           WAITING 30000
splitJoinTrigger                                                            ACQUIRED 1000

 

We have 1 Central and 2 RAS

DB Size 435 GB  SQL SERVER 2012

We execute a purge every 5 days.

Reagrds

 

0 Likes
Outstanding Contributor.. JarodMB Outstanding Contributor..
Outstanding Contributor..

Re: oo schecduler issue

Did you make any changes to the engine jar file? 

Expand monitoring/ threads - see any excessive blocking

0 Likes
Adel_HPOO_1020 Super Contributor.
Super Contributor.

Re: oo schecduler issue

No, i did not make any change to the engine jar file.

Also, i have notice that deployments of little contents take a lot lot of time:

2019-05-20 14:12:08,973 [taskExecutor-2] (DeploymentServiceImpl.java:266) INFO - Started async deployment of Deployment Unit
2019-05-20 14:18:45,701 [taskExecutor-2] (DeployedEntityDependenciesMappingServiceImpl.java:77) INFO - Start updating flow dependencies
2019-05-20 14:18:58,225 [taskExecutor-2] (DeployedEntityDependenciesMappingServiceImpl.java:144) INFO - Finish updating flow dependencies
2019-05-20 14:22:49,347 [taskExecutor-2] (DeploymentServiceImpl.java:553) INFO - Delete CP files from Temp Directory
2019-05-20 14:51:46,536 [taskExecutor-2] (DeploymentServiceImpl.java:276) INFO - Ended async deployment of Deployment Unit

 

 

0 Likes
Outstanding Contributor.. JarodMB Outstanding Contributor..
Outstanding Contributor..

Re: oo schecduler issue

When you have an opportunity can you provide the table row counts (all tables) and the index fragmentation report. 

0 Likes
Adel_HPOO_1020 Super Contributor.
Super Contributor.

Re: oo schecduler issue

Please find in attachment  the table row counts  (sheete 1 of Excel document) and the index fragmentation report (sheet 2) of our OO DB.

Thanks 

0 Likes
Outstanding Contributor.. JarodMB Outstanding Contributor..
Outstanding Contributor..

Re: oo schecduler issue

Thanks - can you also grab a screenshot of the oo/monitoring page Data Caches section

0 Likes
Adel_HPOO_1020 Super Contributor.
Super Contributor.

Re: oo schecduler issue

Please find in attachment the screenshot of the oo/monitoring page Data Caches section.

0 Likes
Outstanding Contributor.. JarodMB Outstanding Contributor..
Outstanding Contributor..

Re: oo schecduler issue

I'd recommend executing the following in order

TRUNCATE TABLE OO_EXECUTED_FLOW_GRAPH
GO



--Create the following indexes 
ALTER TABLE [dbo].[OO_EXECUTION_QUEUES] ADD  CONSTRAINT [OO_EXECUTION_QUEUES_CONSTRAINT] UNIQUE NONCLUSTERED 
(
	[EXEC_STATE_ID] ASC,
	[MSG_SEQ_ID] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [OO_Q_EXEC_STATE_IDX] ON [dbo].[OO_EXECUTION_QUEUES]
(
	[STATUS] ASC
)
INCLUDE ( 	[EXEC_STATE_ID],
	[ASSIGNED_WORKER],
	[MSG_SEQ_ID]) WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO


ALTER TABLE [dbo].[OO_EXECUTION_QUEUES] ADD  CONSTRAINT [PK_OO_EXECUTION_QUEUES] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_qrtz_ft_inst_job_req_rcvry] ON [dbo].[OO_FIRED_TRIGGERS]
(
	[SCHED_NAME] ASC,
	[INSTANCE_NAME] ASC,
	[REQUESTS_RECOVERY] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_qrtz_ft_j_g] ON [dbo].[OO_FIRED_TRIGGERS]
(
	[SCHED_NAME] ASC,
	[JOB_NAME] ASC,
	[JOB_GROUP] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [idx_qrtz_ft_jg] ON [dbo].[OO_FIRED_TRIGGERS]
(
	[SCHED_NAME] ASC,
	[JOB_GROUP] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [idx_qrtz_ft_t_g] ON [dbo].[OO_FIRED_TRIGGERS]
(
	[SCHED_NAME] ASC,
	[TRIGGER_NAME] ASC,
	[TRIGGER_GROUP] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [idx_qrtz_ft_tg] ON [dbo].[OO_FIRED_TRIGGERS]
(
	[SCHED_NAME] ASC,
	[TRIGGER_GROUP] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [idx_qrtz_ft_trig_inst_name] ON [dbo].[OO_FIRED_TRIGGERS]
(
	[SCHED_NAME] ASC,
	[INSTANCE_NAME] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO


CREATE UNIQUE NONCLUSTERED INDEX [UQ_RFID_OO_GROUP_ALIASES] ON [dbo].[OO_GROUP_ALIASES]
(
	[REFERENCED_ID] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_qrtz_j_grp] ON [dbo].[OO_JOB_DETAILS]
(
	[SCHED_NAME] ASC,
	[JOB_GROUP] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [idx_qrtz_j_req_recovery] ON [dbo].[OO_JOB_DETAILS]
(
	[SCHED_NAME] ASC,
	[REQUESTS_RECOVERY] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO

--This index isnt provided by OO - should be removed 
ALTER TABLE [dbo].[OO_STEP_LOG_BINDINGS] DROP CONSTRAINT [FK_ID]
GO

CREATE NONCLUSTERED INDEX [idx_qrtz_t_c] ON [dbo].[OO_TRIGGERS]
(
	[SCHED_NAME] ASC,
	[CALENDAR_NAME] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_qrtz_t_g] ON [dbo].[OO_TRIGGERS]
(
	[SCHED_NAME] ASC,
	[TRIGGER_GROUP] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_qrtz_t_j] ON [dbo].[OO_TRIGGERS]
(
	[SCHED_NAME] ASC,
	[JOB_NAME] ASC,
	[JOB_GROUP] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_qrtz_t_jg] ON [dbo].[OO_TRIGGERS]
(
	[SCHED_NAME] ASC,
	[JOB_GROUP] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_qrtz_t_n_g_state] ON [dbo].[OO_TRIGGERS]
(
	[SCHED_NAME] ASC,
	[TRIGGER_GROUP] ASC,
	[TRIGGER_STATE] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_qrtz_t_n_state] ON [dbo].[OO_TRIGGERS]
(
	[SCHED_NAME] ASC,
	[TRIGGER_NAME] ASC,
	[TRIGGER_GROUP] ASC,
	[TRIGGER_STATE] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_qrtz_t_next_fire_time] ON [dbo].[OO_TRIGGERS]
(
	[SCHED_NAME] ASC,
	[NEXT_FIRE_TIME] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_qrtz_t_nft_misfire] ON [dbo].[OO_TRIGGERS]
(
	[SCHED_NAME] ASC,
	[MISFIRE_INSTR] ASC,
	[NEXT_FIRE_TIME] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_qrtz_t_nft_st] ON [dbo].[OO_TRIGGERS]
(
	[SCHED_NAME] ASC,
	[TRIGGER_STATE] ASC,
	[NEXT_FIRE_TIME] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_qrtz_t_nft_st_misfire] ON [dbo].[OO_TRIGGERS]
(
	[SCHED_NAME] ASC,
	[MISFIRE_INSTR] ASC,
	[NEXT_FIRE_TIME] ASC,
	[TRIGGER_STATE] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_qrtz_t_nft_st_misfire_grp] ON [dbo].[OO_TRIGGERS]
(
	[SCHED_NAME] ASC,
	[MISFIRE_INSTR] ASC,
	[NEXT_FIRE_TIME] ASC,
	[TRIGGER_GROUP] ASC,
	[TRIGGER_STATE] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_qrtz_t_state] ON [dbo].[OO_TRIGGERS]
(
	[SCHED_NAME] ASC,
	[TRIGGER_STATE] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]
GO


-- remove old flow graphs for no longer executing flows, keep the delete count low or your system performance will degrade
set nocount on
declare @r int
set @r = 1
while @r > 0
begin
       begin transaction
       delete top (10) from OO_FLOW_GRAPH where ID NOT IN (select FLOW_GRAPH_ID from OO_LATEST_FLOW_GRAPH)
       set @r = @@rowcount
       commit transaction
end

 

On each Central node edit the following ("central\tomcat\webapps\oo\WEB-INF\classes\[ehcache.xml, ehcache-for-methods.xml]") - restart Central

<defaultCache
            maxEntriesLocalHeap="10000"
            eternal="false"
            timeToIdleSeconds="300"
            timeToLiveSeconds="600"
            overflowToDisk="false"
            diskPersistent="false"
            memoryStoreEvictionPolicy="LRU"
            statistics="true"
            />

Once complete I'd suggest running a reindex

 

 

exec sp_MSforeachtable "ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR=80,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=OFF,SORT_IN_TEMPDB=ON);"

 

 

0 Likes
Adel_HPOO_1020 Super Contributor.
Super Contributor.

Re: oo schecduler issue

Many Thanks Jarod for this great Job.

But, as you know, our environment is a production one, so, when applying this instructions can i be 100% sure that there is no risk to damage the database for example or to produce a major incident on the plateform.?

Regards

0 Likes
Outstanding Contributor.. JarodMB Outstanding Contributor..
Outstanding Contributor..

Re: oo schecduler issue

With the current performance issues, I don't know that you would see any further degradation with the additional indexes. You're missing a couple of constraints - there may be duplicate data that will need to be dealt with but hopefully not. Personally, I would run each statement individually. 

[OO_STEP_LOG_BINDINGS] DROP CONSTRAINT [FK_ID]

This constraint isn't part of the OO deploy - it looks like it may have been added afterward. I'd suggest removal unless you have a known reason for it. 

The step to remove old flow graphs could cause some slowness - keep the delete count low and it shouldn't be problematic. You could also stop jobs (bring down central) and just truncate the table. 

Updating the cache value will require a restart of each central - do this after the other stuff has completed.

Reindexing on a busy system is likely to cause blocked transactions - I would suggest a couple hour maintenance window just to be safe - after your weekly purge.  

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.