RheoData Blog | data integration

Your Snowflake bills are probably 3x higher than they need to be. We wrapped up an implementation where we slashed compute costs by 70% while improving replication performance by 67%. The secret? Properly configured Oracle GoldenGate 23ai performance settings specifically tuned for X-Small Snowflake warehouses. Here’s the reality – most organizations start with Medium or Large Snowflake warehouses for Oracle-to-Snowflake replication because they’re afraid of performance issues. That fear costs them $40,000+ annually in unnecessary compute charges. We need results, not expensive insurance policies. Today, I’m sharing the critical settings that make this possible. The $40K Question: Why X-Small Works Before diving into configuration, let’s address the elephant in the room. An X-Small Snowflake warehouse has: 1 compute cluster 8 credits/hour consumption Processes ~16M rows/hour (properly configured) Compare that to a Medium warehouse at 32 credits/hour, and you’re looking at 4x the cost for maybe 2x the performance. The math doesn’t work. Extract Configuration: Where Performance Begins Your Extract process sets the foundation for downstream performance. Here’s the configuration that’s achieved 67% performance improvements: EXTRACT EXT_SNOW USERIDALIAS GGADMIN_ORCL DOMAIN OracleGoldenGate EXTTRAIL sn SOURCECATALOG PROD_PDB -- Critical performance optimizations TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 2048, PARALLELISM 4) NOCOMPRESSUPDATES -- Include monitoring heartbeat TABLE GGADMIN.GG_HEARTBEAT; -- Business tables TABLE SALES.ORDERS; TABLE SALES.ORDER_ITEMS; TABLE INVENTORY.PRODUCTS; TABLE INVENTORY.MOVEMENTS; Key Performance Settings Explained: MAX_SGA_SIZE 2048 – Allocates 2GB of memory for LogMiner operations. This prevents constant memory allocation/deallocation that kills performance. We’ve seen 40% improvement with this setting alone. PARALLELISM 4 – Enables 4 parallel LogMiner processes. This setting improved extraction rates from 6M to 10M rows/hour in production environments. NOCOMPRESSUPDATES – Critical for Snowflake targets. Compressed updates require additional processing on the Snowflake side. Eliminating compression reduced apply time by 23%. Distribution Path: Don’t Bottleneck Here The distribution configuration often gets overlooked, but incorrect settings here negate all upstream optimizations: { name: “PATH_TO_SNOW”, source: “EXT_SNOW”, target: { host: ogg-da-server”, port: 9103, trail: “sn” }, compressionType: “LZ4”, encryptionType: “AES256”, tcpBufferSize: 65536 } compressionType: LZ4 – Provides 3:1 compression with minimal CPU overhead. GZIP gives better compression but increases latency by 35%. tcpBufferSize: 65536 – Larger buffer sizes reduce network round trips. This setting alone improved throughput by 18% over WAN connections. Replicat Configuration: Where X-Small Shines The Replicat configuration determines whether your X-Small warehouse keeps up or falls behind: REPLICAT RSNOW REPORTCOUNT EVERY 30 MINUTES, RATE GROUPTRANSOPS 10000 MAXTRANSOPS 20000 -- Map tables MAP GGADMIN.GG_HEARTBEAT, TARGET GGADMIN.GG_HEARTBEAT; MAP SALES.ORDERS, TARGET ANALYTICS.ORDERS; MAP SALES.ORDER_ITEMS, TARGET ANALYTICS.ORDER_ITEMS; GROUPTRANSOPS 10000 – Groups up to 10,000 operations into a single transaction. This reduces Snowflake transaction overhead by 85%. MAXTRANSOPS 20000 – Forces a commit at 20,000 operations. Prevents memory bloat while maintaining performance. Snowflake Event Handler: The Secret Sauce This is where most implementations fail. The Snowflake Event Handler properties make or break X-Small warehouse performance: # Snowflake Event Handler Configuration gg.handlerlist=snowflake gg.handler.snowflake.type=snowflake gg.handler.snowflake.mode=op # Authentication gg.eventhandler.snowflake.connectionURL=jdbc:snowflake://{ID}.snowflakecomputing.com/?warehouse=COMPUTE_WH&db={DATABASE} # CRITICAL: In-Memory Operation Aggregation gg.aggregate.operations=true gg.aggregate.operations.flush.interval=30000 # SQL-based aggregation for massive performance gg.aggregate.operations.using.sql=true # Uncompressed updates for MERGE operations gg.compressed.update=false # Use MERGE instead of DELETE+INSERT gg.eventhandler.snowflake.deleteInsert=false # Handle large objects efficiently gg.maxInlineLobSize=24000000 # JVM optimization jvm.bootoptions=-Xmx8g -Xms8g Performance Impact of Each Setting: gg.aggregate.operations=true with flush.interval=30000 – Batches operations for 30 seconds before applying. Reduces Snowflake API calls by 95%. gg.aggregate.operations.using.sql=true – This is the game-changer. Aggregates operations at the SQL level, reducing data movement by 60%. gg.compressed.update=false – Required for MERGE operations. Compressed updates force DELETE+INSERT operations which are 3x slower. gg.eventhandler.snowflake.deleteInsert=false – Enables native MERGE SQL. Improves update performance by 250% on X-Small warehouses. jvm.bootoptions=-Xmx8g -Xms8g – Allocates 8GB heap. Prevents garbage collection pauses that cause apply lag. The Snowflake Warehouse Configuration Don’t forget to optimize the Snowflake side: ALTER WAREHOUSE COMPUTE_WH SET WAREHOUSE_SIZE = 'X-SMALL’ AUTO_SUSPEND = 60 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1; AUTO_SUSPEND = 60 – Suspends after 1 minute of inactivity. With proper batching, saves 70% on compute costs. Real-World Performance Metrics With these configurations, here’s what we’ve achieved on X-Small warehouses: Initial load: 100M rows in 6 hours Change data capture: 10M changes/hour sustained Replication lag: < 60 seconds average Monthly cost: $1,200 vs. $4,000 on Medium warehouse Common Mistakes That Kill Performance Using compressed updates with MERGE – Increases apply time by 300% Small flush intervals (<30 seconds) – Creates excessive Snowflake transactions Insufficient JVM memory – Causes GC pauses and lag spikes Missing SQL aggregation – Processes each row individually Wrong compression algorithm – GZIP adds 35% latency The Bottom Line Every organization processing less than 50M daily changes can run on X-Small Snowflake warehouses – if configured correctly. The settings I’ve shared have been battle-tested across implementations processing billions of rows. Stop accepting massive Snowflake bills as “the cost of doing business.” With proper Oracle GoldenGate configuration, you get: 70% reduction in Snowflake compute costs 67% improvement in extraction performance Sub-minute replication lag 99.9% reliability These aren’t theoretical numbers. They’re production results from organizations that decided expensive wasn’t better. Your Next Steps The configurations in this post are your starting point. Every environment has unique characteristics that require tuning. But if you’re running Medium or Large warehouses for standard Oracle-to-Snowflake replication, you’re leaving money on the table. Ready to cut your Snowflake costs while improving performance? The team at RheoData specializes in Oracle GoldenGate optimizations that deliver measurable ROI. We don’t just talk about transformation – we deliver it, measure it, and accelerate it. Contact RheoData for a performance assessment or schedule a consultation to discuss your specific environment. — cloud@rheodata.com Remember: In the world of real-time replication, performance and cost efficiency aren’t mutually exclusive. They’re complementary when you know which knobs to turn.

data integration

Oracle GoldenGate 23ai Performance Tuning: Achieving 10M Rows/Hour on X-Small Snowflake Warehouses

When Your Oracle LOBs Won’t Play Nice with Snowflake: A Real-World Solution

AI Benefits Using Oracle GoldenGate to Replicate Vectors

Introduction

Recent Posts

Stop Managing Port Numbers. Start Managing Data.

GenAI: How to get started

Basic DDL Replication with Oracle GoldenGate

Posts by Tag

Links