View Issue Details

IDProjectCategoryView StatusLast Update
0004453SymmetricDSImprovementpublic2020-07-08 08:27
ReporterelongAssigned Toelong 
Prioritynormal 
Status closedResolutionfixed 
Product Version3.12.0 
Target Version3.12.1Fixed in Version3.12.1 
Summary0004453: 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|
Tagsperformance

Relationships

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

Activities

There are no notes attached to this issue.

Issue History

Date Modified Username Field Change
2020-06-24 16:37 elong New Issue
2020-06-24 16:37 elong Status new => assigned
2020-06-24 16:37 elong Assigned To => elong
2020-06-24 16:37 elong Issue generated from: 0004426
2020-06-24 16:37 elong Relationship added related to 0004426
2020-06-24 16:37 elong Status assigned => resolved
2020-06-24 16:37 elong Resolution open => fixed
2020-06-24 16:37 elong Fixed in Version => 3.12.1
2020-06-24 16:47 admin Status resolved => closed
2020-07-08 08:27 elong Tag Attached: performance