{"id":11016,"date":"2018-03-12T11:26:24","date_gmt":"2018-03-12T10:26:24","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/"},"modified":"2018-03-12T11:26:24","modified_gmt":"2018-03-12T10:26:24","slug":"connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/","title":{"rendered":"Connect-times to the DB suddenly become very slow using sqlcl"},"content":{"rendered":"

I recently wrote a couple of sql-scripts which had to run on all of my customer’s DBs. The sql-scripts had to be started from a Linux-client, which does not have any Oracle client software installed. So I thought of using sqlcl (see http:\/\/www.oracle.com\/technetwork\/developer-tools\/sqlcl\/downloads\/index.html<\/a>), because there is no need to “install” something then. All I needed was an installed JRE on the Linux-machine. Fortunately that was available. So I downloaded the newest version of sqlcl and unzipped it. Initially I had an issue with the timezone when connecting:<\/p>\n


\nORA-00604: error occurred at recursive SQL level 1
\nORA-01882: timezone region not found
\n<\/code><\/p>\n

I could workaround this by adding “AddVMOption -Doracle.jdbc.timezoneAsRegion=false” in <sqlcl-home>\/bin\/sql:<\/p>\n


\n#
\n# set up the main arguments for java.
\n#
\nfunction setupArgs {
\n...
\n AddVMOption -Doracle.jdbc.timezoneAsRegion=false
\n....
\n<\/code><\/p>\n

When looping over the databases the connect-times suddenly became very slow. I had to wait for up to 2 minutes to setup a connection to the database. After doing strace on the sqlcl-connects I saw the reason for the slow connects:<\/p>\n

The Oracle JDBC driver requires a random number for encrypting the connect string. By default this random number is generated from \/dev\/random. If the number of entropy-entries (cat \/proc\/sys\/kernel\/random\/entropy_avail) becomes too low, the access to \/dev\/random is blocked until enough entropy-entries are available again. The number of entropy-entries is generated through some random noise on the machine (drivers for keyboard, mouse, network, etc. generate the entries). If there is no driver-activity then the entropy-pool may become empty.<\/p>\n

The workaround is to
\n– either artificially generate some driver load (examples on how to do it are in referenced links at the end of the blog)
\n– or use \/dev\/urandom instead of \/dev\/random<\/p>\n

The first workaround was not feasible in my case. So I had to use the more unsecure workaround of using \/dev\/urandom. I.e. I updated <sqlcl-home>\/bin\/sql again and added “AddVMOption -Djava.security.egd=file:\/\/\/dev\/urandom”:<\/p>\n


\n#
\n# set up the main arguments for java.
\n#
\nfunction setupArgs {
\n...
\n AddVMOption -Djava.security.egd=file:\/\/\/dev\/urandom
\n....
\n<\/code><\/p>\n

Afterwards my sqlcl-connects were fast again.<\/p>\n

See the following links for more info:<\/p>\n

http:\/\/www.usn-it.de\/index.php\/2009\/02\/20\/oracle-11g-jdbc-driver-hangs-blocked-by-devrandom-entropy-pool-empty<\/a>
\nhttps:\/\/stackoverflow.com\/questions\/2327220\/oracle-jdbc-intermittent-connection-issue<\/a>
\nhttps:\/\/oraganism.wordpress.com\/2015\/09\/20\/slow-jdbc-connections-strace-and-random-numbers<\/a>
\nhttp:\/\/www.oracle.com\/technetwork\/database\/database-appliance\/documentation\/oda-eval-comparing-performance-1895230.pdf<\/a> –> see page 7: “Specify non-blocking random number generators”<\/p>\n

Remark: I updated the Oracle Community entry https:\/\/community.oracle.com\/thread\/4073170<\/a> with that information as well.<\/p>\n","protected":false},"excerpt":{"rendered":"

I recently wrote a couple of sql-scripts which had to run on all of my customer’s DBs. The sql-scripts had to be started from a Linux-client, which does not have any Oracle client software installed. So I thought of using sqlcl (see http:\/\/www.oracle.com\/technetwork\/developer-tools\/sqlcl\/downloads\/index.html), because there is no need to “install” something then. All I needed […]<\/p>\n","protected":false},"author":35,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198,368,59],"tags":[214,1319,96,673,609],"type_dbi":[],"class_list":["post-11016","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","category-development-performance","category-oracle","tag-java","tag-jdbc","tag-oracle","tag-sqlcl","tag-sqldeveloper"],"acf":[],"yoast_head":"\nConnect-times to the DB suddenly become very slow using sqlcl - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Connect-times to the DB suddenly become very slow using sqlcl\" \/>\n<meta property=\"og:description\" content=\"I recently wrote a couple of sql-scripts which had to run on all of my customer’s DBs. The sql-scripts had to be started from a Linux-client, which does not have any Oracle client software installed. So I thought of using sqlcl (see http:\/\/www.oracle.com\/technetwork\/developer-tools\/sqlcl\/downloads\/index.html), because there is no need to “install” something then. All I needed […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-03-12T10:26:24+00:00\" \/>\n<meta name=\"author\" content=\"Clemens Bleile\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@ifgtxD2SrQ8r!YuXj\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Clemens Bleile\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/\"},\"author\":{\"name\":\"Clemens Bleile\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da\"},\"headline\":\"Connect-times to the DB suddenly become very slow using sqlcl\",\"datePublished\":\"2018-03-12T10:26:24+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/\"},\"wordCount\":392,\"commentCount\":0,\"keywords\":[\"Java\",\"jdbc\",\"Oracle\",\"SQLcl\",\"SQLDeveloper\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Database management\",\"Development & Performance\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/\",\"name\":\"Connect-times to the DB suddenly become very slow using sqlcl - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2018-03-12T10:26:24+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Connect-times to the DB suddenly become very slow using sqlcl\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da\",\"name\":\"Clemens Bleile\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"caption\":\"Clemens Bleile\"},\"description\":\"Clemens Bleile has more than 30 years of IT experience, thirteen in Oracle Support and fifteen in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.\",\"sameAs\":[\"https:\/\/www.dbi-services.com\",\"https:\/\/x.com\/ifgtxD2SrQ8r!YuXj\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/clemens-bleile\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Connect-times to the DB suddenly become very slow using sqlcl - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/","og_locale":"en_US","og_type":"article","og_title":"Connect-times to the DB suddenly become very slow using sqlcl","og_description":"I recently wrote a couple of sql-scripts which had to run on all of my customer’s DBs. The sql-scripts had to be started from a Linux-client, which does not have any Oracle client software installed. So I thought of using sqlcl (see http:\/\/www.oracle.com\/technetwork\/developer-tools\/sqlcl\/downloads\/index.html), because there is no need to “install” something then. All I needed […]","og_url":"https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/","og_site_name":"dbi Blog","article_published_time":"2018-03-12T10:26:24+00:00","author":"Clemens Bleile","twitter_card":"summary_large_image","twitter_creator":"@ifgtxD2SrQ8r!YuXj","twitter_misc":{"Written by":"Clemens Bleile","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/"},"author":{"name":"Clemens Bleile","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"headline":"Connect-times to the DB suddenly become very slow using sqlcl","datePublished":"2018-03-12T10:26:24+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/"},"wordCount":392,"commentCount":0,"keywords":["Java","jdbc","Oracle","SQLcl","SQLDeveloper"],"articleSection":["Database Administration & Monitoring","Database management","Development & Performance","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/","url":"https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/","name":"Connect-times to the DB suddenly become very slow using sqlcl - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-03-12T10:26:24+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Connect-times to the DB suddenly become very slow using sqlcl"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da","name":"Clemens Bleile","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","caption":"Clemens Bleile"},"description":"Clemens Bleile has more than 30 years of IT experience, thirteen in Oracle Support and fifteen in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.","sameAs":["https:\/\/www.dbi-services.com","https:\/\/x.com\/ifgtxD2SrQ8r!YuXj"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/clemens-bleile\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11016","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/35"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=11016"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11016\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11016"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11016"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11016"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11016"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}