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