r-dbi
Performance issue to connect to an Oracle database using Oracle's OEM driver
Issue Description and Expected Result
When using package:odbc with Oracle's OEM driver, the connection is excessively long to establish. Once established, the Connection Pane seems to load objects forever and the session is excessively slow in the meantime:
When dbConnect() is wrapped in e.g. invisible() or system.time(), the connection establishes much quicker and the session is not slow. However, the Connection Pane is not populated by the content of the database.
This was previously discussed on rstudio/rstudio#12699.
Database
dbGetInfo(con)
# $dbname
# [1] ""
#
# $dbms.name
# [1] "Oracle"
#
# $db.version
# [1] "19.00.0000"
#
# $username
# [1] ""
#
# $host
# [1] ""
#
# $port
# [1] ""
#
# $sourcename
# [1] "OracleODBC-12g"
#
# $servername
# [1] "IPIAMPR2.WORLD"
#
# $drivername
# [1] "SQORA32.DLL"
#
# $odbc.version
# [1] "03.52"
#
# $driver.version
# [1] "12.01.0020"
#
# $odbcdriver.version
# [1] "03.52"
#
# $supports.transactions
# [1] TRUE
#
# $getdata.extensions.any_column
# [1] TRUE
#
# $getdata.extensions.any_order
# [1] TRUE
#
# attr(,"class")
# [1] "Oracle" "driver_info" "list"
Reproducible Example
This is slow:
library(DBI)
start_time <- Sys.time()
con <- dbConnect(drv = odbc::odbc(), dsn = "<DSN using Oracle's OEM driver>")
Sys.time() - start_time
# Time difference of 33.26858 mins
A workaround:
library(DBI)
# or invisible() instead of system.time()
system.time(
con <- dbConnect(drv = odbc::odbc(), dsn = "<DSN using Oracle's OEM driver>")
)
# utilisateur système écoulé
# 0.073 0.033 20.351
Session Info
sessionInfo()
# R version 4.1.2 (2021-11-01)
# Platform: x86_64-pc-linux-gnu (64-bit)
# Running under: Red Hat Enterprise Linux Server 7.8 (Maipo)
#
# Matrix products: default
# BLAS/LAPACK: /usr/lib64/libopenblasp-r0.3.3.so
#
# locale:
# [1] LC_CTYPE=fr_FR.UTF-8 LC_NUMERIC=C LC_TIME=fr_FR.UTF-8 LC_COLLATE=fr_FR.UTF-8
# [5] LC_MONETARY=fr_FR.UTF-8 LC_MESSAGES=fr_FR.UTF-8 LC_PAPER=fr_FR.UTF-8 LC_NAME=C
# [9] LC_ADDRESS=C LC_TELEPHONE=C LC_MEASUREMENT=fr_FR.UTF-8 LC_IDENTIFICATION=C
#
# attached base packages:
# [1] stats graphics grDevices datasets utils methods base
#
# other attached packages:
# [1] DBI_1.0.0
#
# loaded via a namespace (and not attached):
# [1] bit_1.1-14 odbc_1.3.5 compiler_4.1.2 ellipsis_0.3.2 hms_1.1.1 tools_4.1.2 Rcpp_1.0.7
# [8] bit64_0.9-7 vctrs_0.3.8 blob_1.2.2 lifecycle_1.0.1 pkgconfig_2.0.2 rlang_0.4.12
Hi there:
I am not sure if this is the culprit, but that driver seems a bit dated. Do you have the ability to upgrade to something more modern? This is what I am using:
$drivername
[1] "SQORA32.DLL"
$odbc.version
[1] "03.52"
$driver.version
[1] "21.08.0000"
$odbcdriver.version
[1] "03.52"
@detule Sorry for the late reply. I just asked my IT department if they can update the driver. I will report if they do. Thanks!
@simonpcouch @zedapolo
Hi, Our IT department did not update the driver yet so I asked again today. I will report as soon as we can test. Thanks!
My IT department tested with version 12, 19.17 and 21, but the issue is still there.
V12:
start_time <- Sys.time()
con <- dbConnect(drv = odbc::odbc(), dsn = "<v12>")
Sys.time() - start_time
# Time difference of 5.8 mins
V19.17:
start_time <- Sys.time()
con <- dbConnect(drv = odbc::odbc(), dsn = "<v19.17>")
Sys.time() - start_time
# Time difference of 6.216667 mins
V21:
start_time <- Sys.time()
con <- dbConnect(drv = odbc::odbc(), dsn = "<v21>")
Sys.time() - start_time
# Time difference of 6.25 mins
They used the following config in the 3 cases:
[$DSN]
AggregateSQLType = FLOAT
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CacheBufferSize = 20
CloseCursor = F
DisableDPM = F
DisableMTS = T
DisableRULEHint = T
Driver = $DRIVER_NAME
DSN = $DSN
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
LobPrefetchSize = 8192
Lobs = T
Longs = T
MaxLargeData = 0
MaxTokenSize = 8192
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName =
SQLGetData extensions = F
SQLTranslateErrors = F
StatementCache = F
Translation DLL =
Translation Option = 0
UseOCIDescribeAny = F
And also (from #158):
options(connectionObserver = NULL)
start_time <- Sys.time()
con <- dbConnect(drv = odbc::odbc(), dsn = "<v12>")
Sys.time() - start_time
# Time difference of 20.36195 secs
Thanks / I think this is a manifestation of an issue I am tracking here with some ideas on how we might make this better. I think i might have some time in a couple of weeks to tackle.
Thanks for tracking this! I should have some time to tackle this in the next couple of weeks I think. I'll ping you then to see if we can test out couple of prototypes.
Our IT department finally updated the installation but the issue is still here. Can the issue be reopened? Thanks!
library(DBI)
start_time <- Sys.time()
con1 <- dbConnect(odbc::odbc(), dsn = "OracleODBC-19g")
Sys.time() - start_time
# Time difference of 26.12799 mins
options(connectionObserver = NULL)
start_time <- Sys.time()
con2 <- dbConnect(odbc::odbc(), dsn = "OracleODBC-19g")
Sys.time() - start_time
# Time difference of 1.631401 secs
dbGetInfo(con2)
# $dbname
# [1] ""
#
# $dbms.name
# [1] "Oracle"
#
# $db.version
# [1] "19.00.0000"
#
# $username
# [1] ""
#
# $host
# [1] ""
#
# $port
# [1] ""
#
# $sourcename
# [1] "OracleODBC-19g"
#
# $servername
# [1] "zzz"
#
# $drivername
# [1] "SQORA32.DLL"
#
# $odbc.version
# [1] "03.52"
#
# $driver.version
# [1] "19.23.0000"
#
# $odbcdriver.version
# [1] "03.52"
#
# $supports.transactions
# [1] TRUE
#
# $supports.catalogs
# [1] FALSE
#
# $supports.schema
# [1] TRUE
#
# $getdata.extensions.any_column
# [1] TRUE
#
# $getdata.extensions.any_order
# [1] TRUE
#
sessionInfo()
# R version 4.4.3 (2025-02-28)
# Platform: x86_64-pc-linux-gnu
# Running under: Red Hat Enterprise Linux 8.10 (Ootpa)
#
# Matrix products: default
# BLAS/LAPACK: /usr/lib64/libopenblasp-r0.3.15.so; LAPACK version 3.9.0
#
# locale:
# [1] LC_CTYPE=fr_FR.UTF-8 LC_NUMERIC=C LC_TIME=fr_FR.UTF-8 LC_COLLATE=fr_FR.UTF-8 LC_MONETARY=fr_FR.UTF-8
# [6] LC_MESSAGES=fr_FR.UTF-8 LC_PAPER=fr_FR.UTF-8 LC_NAME=C LC_ADDRESS=C LC_TELEPHONE=C
# [11] LC_MEASUREMENT=fr_FR.UTF-8 LC_IDENTIFICATION=C
#
# time zone: GMT
# tzcode source: system (glibc)
#
# attached base packages:
# [1] stats graphics grDevices datasets utils methods base
#
# other attached packages:
# [1] DBI_1.2.3
#
# loaded via a namespace (and not attached):
# [1] bit_4.0.5 odbc_1.6.0 compiler_4.4.3 cli_3.6.4 hms_1.1.3 tools_4.4.3 rstudioapi_0.16.0
# [8] Rcpp_1.0.14 bit64_4.0.5 vctrs_0.6.5 blob_1.2.4 lifecycle_1.0.4 pkgconfig_2.0.3 rlang_1.1.5
dbDisconnect(con1)
dbDisconnect(con2)