Accelerate Oracle Data Warehouse Queries with Gluent and Snowflake
Faster, Smarter storage
One method to increase the performance of data warehouse is to attach faster storage. Data warehouse queries rely heavily on scanning large volumes of data so high throughput low latency storage is an effective way to improve performance without having to tune or change any SQL code. An even better way of improving warehouse query performance is to use Computational Storage which offloads host processing and reduces data movement. This is precisely how Exadata Smart Scan works, instead of requesting entire data blocks from storage, SQL queries are pushed down to the storage which is capable of applying filters (WHERE clauses) and returning only required (SELECT) columns from matching rows.
Snowflake as Computational Storage
Gluent Data Platform presents Snowflake as Computational Storage to the Oracle query engine, similar to Exadata Smart Scan and Exadata Storage Servers. The key differences are you do not require an Exadata Storage Server and Gluent can push even more SQL logic down to Snowflake, including
- Projection Pushdown (SELECT, JOIN and PREDICATE columns)
- Predicate Pushdown (WHERE)
- Join Filter Pulldown (one table in Snowflake)
- Advanced Aggregation Pushdown
- Join Pushdown (both tables in Snowflake)
- Data type formatting (Oracle data types returned)
- Parallel query
This reduces the Oracle query engine compute requirements even more than Exadata Smart Scan.
Oracle Dependencies
The Gluent solution relies on two key Oracle capabilities, External Tables and Query Rewrite. All Snowflake tables or views that are made queryable to Oracle by Gluent are exposed to Oracle as External Tables. When running queries Gluent modifies the query on the fly and pushes as much workload as possible to the data stored in Snowflake. It does this by pushing predicates, join filters and projection pruning to Snowflake and where applicable, also uses the Oracle Query Rewrite mechanism to push down aggregations and joins as well. The Oracle Query Engine would normally perform a full table scan against an External table but Gluent replaces that with a filtered result set.
Network Latency
Clearly returning only matching rows and columns (a result set) from the storage back to the compute reduces network requirements and elapsed time. In addition, Gluent compresses the result set which reduces the network traffic further.
Exadata Storage Servers which support Smart Scan also use a high throughput 40Gb/sec network. Such high throughput capacity is mostly relevant to the offload use case where Gluent synchronises copies of data from Oracle to Snowflake, the transparent query option is less dependent due to query pushdown and reduced sized result sets being transmitted. Gluent customers who frequently synchronise high volumes of data from Oracle to Snowflake use high bandwidth dedicated network connections between their data centre which hosts their Oracle database warehouses and their Snowflake deployment region. When running Oracle Data Warehouse on cloud it can be collocated on the same Snowflake deployment region.
It’s not just Fast it’s Instantaneous
In essence, Gluent can leverage Snowflake to implement an alternative Smart Scan and Storage Server solution for Oracle. Gluent implements hybrid OLAP queries where recent volatile data is queried from Oracle and older, higher volume and less volatile data is queried via Snowflake.
The benefits are almost instantaneous as no changes are required to Oracle SQL or PL/SQL. Gluent also takes care of synchronising data from Oracle to Snowflake where you can then choose to truncate the data that has been synchronised from Oracle. When querying using the Gluent maintained hybrid schema all query re-writes are performed automatically.
In addition, any data visible in Snowflake, including Marketplace and Data Exchange subscriptions are directly queryable by Oracle and any Oracle data offloaded to Snowflake is accessible to any Snowflake client tools.
You can of course use Gluent Data Platform to help you migrate to Snowflake more efficiently and with less risk than most migration methods but many Oracle customers have no plans to (or cannot) migrate from Oracle but are interested in higher Oracle data warehouse performance at lower cost.
Is this really a “no code” solution?
As close as you will get. The only command you need to run in Oracle to leverage Snowflake once Gluent is installed is:
SQL> alter session set current_schema = <hybrid_schema>;All your Oracle OLAP queries then run unchanged.
The hybrid schema is built and maintained by Gluent to facilitate the query rewrite and offload.
What are the primary Oracle customer use cases?
- Improve the performance of non-Exadata Oracle Data Warehouses
- Avoid the expansion cost of Exadata, offloading work to Snowflake
- Archive older analytics data but keep it highly accessible
- Release Oracle capacity for consolidating OLTP workloads
- Reverse migration, migrate workloads first and code later
- Gain access to Snowflake Data Sharing for publishing and subscribing
- Migrate wholly or partially to Cloud (AWS, Azure or GCP)
Our workload is special, will it work for me?
Gluent’s customers range from those with a few terabytes of data to multi petabyte databases. Existing customer compute workloads range from dozens to thousands of average active sessions (AAS).
You can run a utility from Gluent called Gluent Advisor. This will calculate how much workload can be pushed down to Snowflake based on empirical evidence captured from Oracle AWR. The code is published as SQL for your DBAs to review before running.
Gluent Advisor is very accurate, typically around 90% of warehouse data and queries can be offloaded. Give it a try you having nothing to lose.
