Skip to content

Instantly share code, notes, and snippets.

@akostadinov

akostadinov/ruby-oci8-lob-performance.rb

Created December 4, 2025 16:27
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save akostadinov/4e69b493e8413a0779628a8f0abfbe85 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save akostadinov/4e69b493e8413a0779628a8f0abfbe85 to your computer and use it in GitHub Desktop.
Download ZIP
Test the performance of fetching LOBs with ruby-oci8 gem
Raw
ruby-oci8-lob-performance.rb
#!/usr/bin/env ruby
#
# Ruby-level LOB fetch performance test
#
# Measures Ruby execution time for the same operations as bench_lob_fetch.c
# Tests 10,000 rows with 10KB LOBs using three different fetch modes:
# 1. LONG interface (:long_as_string) - default, fast
# 2. LOB locators without prefetch (:locator) - slow
# 3. LOB locators with prefetch (:locator + lob_prefetch_size) - slower
#
# Installation: it can run standalone with whatever version of ruby-oci8 gem you have installed,
# as long as it has implemented the LONG interface which hopefully will be 2.2.14+.
# Alternatively you can put it in the ruby-oci8 git tree but run `make build` before running.
#
# Running: ruby test_lob_performance.rb
# Load local development version of oci8
$LOAD_PATH.unshift File.expand_path('../lib', __FILE__)
$LOAD_PATH.unshift File.expand_path('../ext/oci8', __FILE__)
require 'oci8'
require_relative 'test/config'
class TestLobPerformance < Minitest::Test
ROWS = 10_000
LOB_SIZE = 10_000 # 10KB per LOB
def setup
@conn = get_oci8_connection
@saved_lob_fetch_mode = OCI8.lob_fetch_mode
drop_table('test_lob_performance')
@conn.exec(<<-SQL)
CREATE TABLE test_lob_performance (
id NUMBER,
clob_data CLOB,
blob_data BLOB
)
SQL
end
def teardown
return unless @conn
OCI8.lob_fetch_mode = @saved_lob_fetch_mode
drop_table('test_lob_performance')
@conn.logoff
end
def test_performance_comparison
puts "\n==================================================================="
puts "Ruby-level LOB Fetch Performance Test"
puts "==================================================================="
puts "Testing #{ROWS} rows with #{LOB_SIZE} byte LOBs"
puts "Compare with C-level results from bench_lob_fetch.c"
puts "==================================================================="
# Insert test data
print "\nInserting test data... "
insert_start = Process.clock_gettime(Process::CLOCK_MONOTONIC)
insert_test_data
insert_elapsed = Process.clock_gettime(Process::CLOCK_MONOTONIC) - insert_start
puts "done (#{format('%.3f', insert_elapsed)} seconds)"
# Test 1: LONG interface (:long_as_string)
puts "\n--- Test 1: LONG interface (:long_as_string) ---"
long_elapsed = test_long_interface
# Test 2: LOB locators without prefetch
puts "\n--- Test 2: LOB locators (no prefetch) ---"
locator_elapsed = test_locator_no_prefetch
# Test 3: LOB locators with prefetch
puts "\n--- Test 3: LOB locators with prefetch ---"
prefetch_elapsed = test_locator_with_prefetch
# Summary
puts "\n==================================================================="
puts "Performance Summary"
puts "==================================================================="
puts sprintf("%-30s %10s", "Mode", "Time (sec)")
puts "-------------------------------------------------------------------"
puts sprintf("%-30s %10.3f", "Data insertion", insert_elapsed)
puts sprintf("%-30s %10.3f", "LONG interface", long_elapsed)
puts sprintf("%-30s %10.3f", "LOB locator (no prefetch)", locator_elapsed)
puts sprintf("%-30s %10.3f", "LOB locator (with prefetch)", prefetch_elapsed)
puts "-------------------------------------------------------------------"
puts sprintf("%-30s %10.1fx", "Locator vs LONG (no prefetch)", locator_elapsed / long_elapsed)
puts sprintf("%-30s %10.1fx", "Locator vs LONG (with prefetch)", prefetch_elapsed / long_elapsed)
puts "==================================================================="
# All tests should successfully fetch all rows
assert true, "Performance tests completed"
end
private
def insert_test_data
cursor = @conn.parse("INSERT INTO test_lob_performance VALUES (:1, :2, :3)")
ROWS.times do |i|
# Generate 10KB of data with detectable pattern
text_data = "Row #{i} data: " + ('x' * (LOB_SIZE - "Row #{i} data: ".length))
cursor.exec(i, OCI8::CLOB.new(@conn, text_data), OCI8::BLOB.new(@conn, text_data))
end
cursor.close
@conn.commit
end
def test_long_interface
# Set mode to :long_as_string (default LONG interface)
OCI8.lob_fetch_mode = :long_as_string
start_time = Process.clock_gettime(Process::CLOCK_MONOTONIC)
cursor = @conn.exec("SELECT id, clob_data, blob_data FROM test_lob_performance ORDER BY id")
count = 0
total_clob_bytes = 0
total_blob_bytes = 0
while row = cursor.fetch
id, clob_data, blob_data = row
assert_instance_of String, clob_data, "CLOB should be fetched as String"
assert_instance_of String, blob_data, "BLOB should be fetched as String"
assert_equal LOB_SIZE, clob_data.size, "CLOB size mismatch for row #{id}"
assert_equal LOB_SIZE, blob_data.size, "BLOB size mismatch for row #{id}"
total_clob_bytes += clob_data.size
total_blob_bytes += blob_data.size
count += 1
end
cursor.close
elapsed = Process.clock_gettime(Process::CLOCK_MONOTONIC) - start_time
puts "Fetched #{count} rows"
puts "Total CLOB bytes: #{total_clob_bytes}"
puts "Total BLOB bytes: #{total_blob_bytes}"
puts "Elapsed time: #{format('%.3f', elapsed)} seconds"
assert_equal ROWS, count, "Should fetch all rows"
elapsed
end
def test_locator_no_prefetch
# Set mode to :locator (LOB locators without prefetch)
OCI8.lob_fetch_mode = :locator
@conn.lob_prefetch_size = 0 # Disable prefetch
start_time = Process.clock_gettime(Process::CLOCK_MONOTONIC)
cursor = @conn.exec("SELECT id, clob_data, blob_data FROM test_lob_performance ORDER BY id")
count = 0
total_clob_bytes = 0
total_blob_bytes = 0
while row = cursor.fetch
id, clob_loc, blob_loc = row
assert_instance_of OCI8::CLOB, clob_loc, "CLOB should be fetched as locator"
assert_instance_of OCI8::BLOB, blob_loc, "BLOB should be fetched as locator"
# Read LOB data - each read is a separate network call
clob_data = clob_loc.read
blob_data = blob_loc.read
assert_equal LOB_SIZE, clob_data.size, "CLOB size mismatch for row #{id}"
assert_equal LOB_SIZE, blob_data.size, "BLOB size mismatch for row #{id}"
total_clob_bytes += clob_data.size
total_blob_bytes += blob_data.size
count += 1
end
cursor.close
elapsed = Process.clock_gettime(Process::CLOCK_MONOTONIC) - start_time
puts "Fetched #{count} rows"
puts "Total CLOB bytes: #{total_clob_bytes}"
puts "Total BLOB bytes: #{total_blob_bytes}"
puts "Elapsed time: #{format('%.3f', elapsed)} seconds"
assert_equal ROWS, count, "Should fetch all rows"
elapsed
end
def test_locator_with_prefetch
# Set mode to :locator with prefetch enabled
OCI8.lob_fetch_mode = :locator
@conn.lob_prefetch_size = 50 * 1024 # 50KB prefetch
start_time = Process.clock_gettime(Process::CLOCK_MONOTONIC)
cursor = @conn.exec("SELECT id, clob_data, blob_data FROM test_lob_performance ORDER BY id")
count = 0
total_clob_bytes = 0
total_blob_bytes = 0
while row = cursor.fetch
id, clob_loc, blob_loc = row
assert_instance_of OCI8::CLOB, clob_loc, "CLOB should be fetched as locator"
assert_instance_of OCI8::BLOB, blob_loc, "BLOB should be fetched as locator"
# Read LOB data - should use prefetched data
clob_data = clob_loc.read
blob_data = blob_loc.read
assert_equal LOB_SIZE, clob_data.size, "CLOB size mismatch for row #{id}"
assert_equal LOB_SIZE, blob_data.size, "BLOB size mismatch for row #{id}"
total_clob_bytes += clob_data.size
total_blob_bytes += blob_data.size
count += 1
end
cursor.close
elapsed = Process.clock_gettime(Process::CLOCK_MONOTONIC) - start_time
puts "Fetched #{count} rows"
puts "Total CLOB bytes: #{total_clob_bytes}"
puts "Total BLOB bytes: #{total_blob_bytes}"
puts "Elapsed time: #{format('%.3f', elapsed)} seconds"
assert_equal ROWS, count, "Should fetch all rows"
# Reset prefetch size
@conn.lob_prefetch_size = 0
elapsed
end
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment