Performance issue to connect to an Oracle database using Oracle's OEM driver

[ThomasSoeiro]

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   

[detule]

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"

[ThomasSoeiro]

@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]

Any updates here, @ThomasSoeiro?

[ThomasSoeiro]

@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!

[ThomasSoeiro]

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

[ThomasSoeiro]

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

[detule]

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.

[ThomasSoeiro]

@detule

Is there anything we can do to help on #714?
Thanks!

[detule]

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.