View Issue Details

IDProjectCategoryView StatusLast Update
0003793SymmetricDSImprovementpublic2019-05-01 14:26
Reportermmichalek Assigned Tommichalek  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.8.0 
Target Version3.9.19Fixed in Version3.9.19 
Summary0003793: Optimize routing query for Oracle when majority of sym_data is unrouted
DescriptionThis routing query select ~31M out of 42 M rows. It timed out after 1 hour.

select d.data_id, d.table_name, d.event_type, '' as row_data, '' as pk_data, '' as old_data, d.create_time, d.trigger_hist_id, d.channel_id, d.transaction_id, d.source_node_id, d.external_data, d.node_list from sym_data d where d.channel_id='default' and data_id >= 804584792 order by d.create_time asc, d.data_id asc

Adding the full table scan hint, it comes back in 85 seconds:
 select /*+ full (d) */ d.data_id, d.table_name, d.event_type, '' as row_data, '' as pk_data, '' as old_data, d.create_time, d.trigger_hist_id, d.channel_id, d.transaction_id, d.source_node_id, d.external_data, d.node_list from sym_data d where d.channel_id='default' and data_id >= 804584792 order by d.create_time asc, d.data_id asc

This is off by default. Can be enabled for Oracle with:
oracle.use.select.data.using.start.data.id.hint=true
TagsNo tags attached.

Relationships

related to 0003931 closedmmichalek Routing fails when data data gaps exceed routing.max.gaps.to.qualify.in.sql $(selectDataUsingStartDataIdHint) 

Activities

There are no notes attached to this issue.

Related Changesets

SymmetricDS: 3.9 1d2d96b0

2019-04-04 14:37:03

mmichalek

Details Diff
0003793: Optimize routing query for Oracle when majority of sym_data is unrouted Affected Issues
0003793
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/oracle/OracleSymmetricDialect.java Diff File
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/common/ParameterConstants.java Diff File
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/RouterServiceSqlMap.java Diff File

SymmetricDS: 3.10 9cff3b69

2019-04-04 14:37:03

mmichalek

Details Diff
0003793: Optimize routing query for Oracle when majority of sym_data is unrouted Affected Issues
0003793
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/oracle/OracleSymmetricDialect.java Diff File
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/common/ParameterConstants.java Diff File
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/RouterServiceSqlMap.java Diff File

Issue History

Date Modified Username Field Change
2018-11-13 21:33 mmichalek New Issue
2018-11-13 21:33 mmichalek Status new => assigned
2018-11-13 21:33 mmichalek Assigned To => mmichalek
2018-11-28 18:23 mmichalek Target Version 3.8.41 =>
2019-04-04 18:36 mmichalek Description Updated View Revisions
2019-04-04 18:40 mmichalek Status assigned => resolved
2019-04-04 18:40 mmichalek Resolution open => fixed
2019-04-04 18:40 mmichalek Fixed in Version => 3.9.19
2019-04-04 18:40 mmichalek Target Version => 3.9.19
2019-04-04 19:00 mmichalek Changeset attached => SymmetricDS 3.9 1d2d96b0
2019-04-04 19:00 mmichalek Changeset attached => SymmetricDS 3.10 9cff3b69
2019-04-23 15:14 elong Status resolved => closed
2019-05-01 14:26 mmichalek Relationship added related to 0003931