Oracle - Optimizing read speeds using Oracle ASM storage

Sonja_Bauernfeind — Mon, 08 Nov 2021 15:38:37 GMT

Preface:

Qlik Log Reader is Qlik's proprietary high speed redo log parser for parsing the Oracle redo logs, it also provides greatly improved performance and reduces the load on the Oracle server when compared with other methods such as Oracle LogMiner.

When the Oracle redo logs are stored in ASM, Replicate enables several techniques to improve the read performance. This includes the Copy redo logs to a temporary folder, which allows Replicate to Copy the redo logs and read it from a faster storage.

The problem originates in the 32KB packet size limitation when reading directly from ASM. Other methods like reading using the 'binary file' allows readings MBs of data, and this is why the Copy to folder does help improve performance.

Replicate now supports a new method for reading from ASM. This is accomplished by reading ASM via parallel threads. Starting from Replicate v6.2 this is the default read method for ASM. This allows Replicate to read the redo log in several threads thus improving the performance.

The Parallel Method:

Starting from Replicate v6.2 the default ASM read mode is 4 parallel threads, the parameter that configures this is called parallelASMReadThreads, the maximum value for this parameter is 8 threads. Finding the right value for a specific environment may require few testing cycles.

Using the parallel method may add some overhead as follows:

Some CPU overhead to the Replicate and Oracle machines.
More network traffic as we will be reading in 4 threads.

That being said, this overhead is understandable and may not be noticeable. Also note that the Copy method also introduces overhead:

Storage for the temporary location.
More permissions and managing the deletion of the copied logs.
Copying the redo logs and reading from the temporary location.

Further Optimizations with Parallel:

The parallel method does not solve the 32KB ASM read limitation, however, it does help by reading in several threads. We have added another mechanizm that can be enabled by setting the asmUsePLSQLArray internal parameter.

When enabled, Replicate will buffer 50 reads at the ASM server (where the PL/SQL is processed) and send back this larger buffer. This will cause less round trips but it will still read in 32KB buffers.
The user can enable asmUsePLSQLArray even when reading using a single thread, however, the benefit will be seen when working with parallel ASM threads. The benefit will vary from system to another, we have observed up to 10-20% improvement in throughput in some cases.

Limitation:

The 50 buffers setting is fixed. This feature can only be used on platforms where the redo log block size = 512 bytes: Linux, Windows, this excludes HPUX.

Advanced Tweak:

The parallel mode can be further optimized using the readAheadBlocks parameter.

Currently, each thread will read:

(readAheadBlocks / parallelASMReadThreads)

The default value of the readAheadBlocks is 100,000 (in blocks), and the maximum value is 2,000,000.

Important Note

Our testing and experience so far has shown that a value of 100,000 (50MBs) is optimal for most platforms.

Summary

Support for the parallel ASM read is available starting from these versions:

Qlik Replicate 6.1 or higher.

article Oracle - Optimizing read speeds using Oracle ASM storage in Official Support Articles

Oracle - Optimizing read speeds using Oracle ASM storage