View Issue Details

IDProjectCategoryView StatusLast Update
0004426SymmetricDSImprovementpublic2020-06-24 20:39
Reporterelong Assigned Toelong  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.11.0 
Target Version3.11.10Fixed in Version3.11.10 
Summary0004426: Purge service high CPU for query data range
DescriptionOracle users have reported high CPU on the database for a query run by the purge service to determine the eligible data range for purge.

select max(data_id) from sym_data_event where batch_id in (select max(batch_id) from sym_outgoing_batch
where batch_id > ? and create_time < ? group by channel_id)

This query contains a sub-query, so we might try to break it apart into two queries to lessen the load on the database.
Additional InformationThis user may have partitioned tables. Explain plan:

SELECT STATEMENT | | | | | | |ALL_ROWS | | 0| | 8090|8090| 1| 27|
SORT |AGGREGATE | | | | | | | | 1| 0| 1| | 1| 27|
HASH JOIN | | | | | | | | | 2| 1| 1|8090| 149| 4023|
NESTED LOOPS | | | | | | | | | 3| 2| 1|8090| 149| 4023|
NESTED LOOPS | | | | | | | | | 4| 3| 1|8090| 154| 4023|
STATISTICS COLLECTOR| | | | | | | | | 5| 4| 1| | | |
VIEW | | |SYS |VW_NSO_1 | 20|VIEW | | | 6| 5| 1|7992| 14| 182|
HASH |GROUP BY | | | | | | | | 7| 6| 1|7992| 14| 392|
TABLE ACCESS |BY INDEX ROWID BATCHED| |SRVC_SYMMETRIC|SYM_OUTGOING_BATCH | 2|TABLE |ANALYZED | | 8| 7| 1|7991| 19823|555044|
INDEX |RANGE SCAN | |SRVC_SYMMETRIC|SYM_OUTGOING_BATCH_PK| |INDEX (UNIQUE)|ANALYZED | 1| 9| 8| 1|1195| 20001| |
INDEX |RANGE SCAN | |SRVC_SYMMETRIC|SYM_IDX_DE_BATCHID | |INDEX |ANALYZED | 1|10| 4| 2| 3| 11| |
TABLE ACCESS |BY INDEX ROWID | |SRVC_SYMMETRIC|SYM_DATA_EVENT | 1|TABLE |ANALYZED | |11| 3| 2| 7| 11| 154|
INDEX |FAST FULL SCAN | |SRVC_SYMMETRIC|SYM_DATA_EVENT_PK | |INDEX (UNIQUE)|ANALYZED | |12| 2| 2| 3| 11| 154|
Tagspurge

Relationships

related to 0004453 closedelong Purge service high CPU for query data range 

Activities

There are no notes attached to this issue.

Related Changesets

SymmetricDS: 3.11 c388787d

2020-06-11 14:20:44

admin

Details Diff
0004426: Purge service high CPU for query data range Affected Issues
0004426
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/PurgeService.java Diff File
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/PurgeServiceSqlMap.java Diff File

Issue History

Date Modified Username Field Change
2020-06-11 18:17 elong New Issue
2020-06-11 18:17 elong Status new => assigned
2020-06-11 18:17 elong Assigned To => elong
2020-06-11 18:17 elong Tag Attached: purge
2020-06-11 18:28 elong Status assigned => resolved
2020-06-11 18:28 elong Resolution open => fixed
2020-06-11 18:28 elong Fixed in Version => 3.11.10
2020-06-11 19:00 admin Changeset attached => SymmetricDS 3.11 c388787d
2020-06-24 20:37 elong Issue cloned: 0004453
2020-06-24 20:37 elong Relationship added related to 0004453
2020-06-24 20:39 admin Status resolved => closed