View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0004453 | SymmetricDS | Improvement | public | 2020-06-24 20:37 | 2020-07-08 12:27 |
| Reporter | elong | Assigned To | elong | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.12.0 | ||||
| Target Version | 3.12.1 | Fixed in Version | 3.12.1 | ||
| Summary | 0004453: Purge service high CPU for query data range | ||||
| Description | Oracle 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 Information | This 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| | ||||
| Tags | performance | ||||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2020-06-24 20:37 | elong | New Issue | |
| 2020-06-24 20:37 | elong | Status | new => assigned |
| 2020-06-24 20:37 | elong | Assigned To | => elong |
| 2020-06-24 20:37 | elong | Issue generated from: 0004426 | |
| 2020-06-24 20:37 | elong | Relationship added | related to 0004426 |
| 2020-06-24 20:37 | elong | Status | assigned => resolved |
| 2020-06-24 20:37 | elong | Resolution | open => fixed |
| 2020-06-24 20:37 | elong | Fixed in Version | => 3.12.1 |
| 2020-06-24 20:47 | admin | Status | resolved => closed |
| 2020-07-08 12:27 | elong | Tag Attached: performance |