Oracle

Setup and Best Practices

Connection Information

In addition to a hostname or IP and port, you will also need to know the site identifier (SID) of the Oracle server.

Resource Allocation Considerations

For RDBMS sources like Oracle, Dremio's query execution is largely single threaded. This means that for each Oracle directed query, only one Dremio node will experience a computational load. So unlike most other data sources, larger Dremio clusters won't lead to faster individual query execution times. However, if you expect a large number of concurrent queries (as in the case of many simultaneous Dremio users) these will be distributed evenly across the nodes.

NOTE: Ensure that your Dremio cluster has access to the appropriate port for your Oracle source. By default this is port 1521.

Dremio and Oracle

Query Pushdowns

For some operations Dremio can tell the data source to execute that part of the query natively, often dramatically improving performance. These operations are called 'pushdowns.'

Since they share a common language (SQL), Dremio supports most operations as pushdowns in Oracle. These include:

NOTE: Since Oracle has no boolean type, project operations that contain SQL expressions which evaluate to true or false (e.g. SELECT username, friends > 0), and filter operations that include boolean literals in a filter (e.g. WHERE currentAccount = true) cannot be executed as pushdowns.

Initial Connection

Depending on the number of tables in your Oracle source, the final step of adding it to Dremio can take anywhere from a few seconds to a few minutes as the source's metadata is processed. However, this is a one-time cost and further queries to the source will not incur additional metadata reads.

Dremio Configuration

Here are all available source specific options:

Name

Description

Host

Oracle host name.

Port

Oracle port number. Defaults to 1521.

Service Name

Service Name of your database.

Authentication Type

No authentication or Master Authentication.

Username

Oracle user name.

Password

Oracle password.

Record Fetch Size

Record fetch size, use 0 to have Dremio automatically decide.

Oracle TLS Configuration

To use TLS to connect to an Oracle source follow the instructions below.

Select the option Connect using SSL/TLS when initially configuring it

Optionally add the Server Certificate Distinguished Name If you want to ensure the server you’re connecting to be the exact one matching a particular certificate string then add the Server Certificate Distinguished Name under the SSL/TLS option in Advanced Options

Add the Certificate Authority certificate to Dremio's trust store To add the CA certificate that is used to sign the Oracle certificate into Dremio's trust store follow the instructions below.