{"id":46,"date":"2024-01-23T21:31:18","date_gmt":"2024-01-23T20:31:18","guid":{"rendered":"http:\/\/cbleile.de\/wordpress\/?p=46"},"modified":"2024-01-23T21:31:18","modified_gmt":"2024-01-23T20:31:18","slug":"what-is-the-fastest-method-to-connect-to-an-oracle-database","status":"publish","type":"post","link":"http:\/\/cbleile.de\/wordpress\/what-is-the-fastest-method-to-connect-to-an-oracle-database\/","title":{"rendered":"What is the fastest method to connect to an Oracle database?"},"content":{"rendered":"\n<p>There are still applications around, which do connect to the database, do some stuff and then disconnect again. I.e. for each and every operation a logon to the database is necessary. This produces unnecessary overhead and is expressed in a high percentage of DB time taken by &#8222;connection management call elapsed time&#8220; in the Oracle time model. In case the application cannot be changed and a lot of connections are happening per second, then the question is on what is the fastest method to connect to an Oracle database?<\/p>\n\n\n\n<p>I do want to compare 3 different methods on how connects to the DB are handled and see what is the fastest in terms of connect-time:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Dedicated Server<\/li>\n\n\n\n<li>Shared Server<\/li>\n\n\n\n<li>Database Resident Connection Pooling (DRCP)<\/li>\n<\/ol>\n\n\n\n<p>For the test I used the following configuration:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Container database cdb0, version 19.19. with pluggable database pdb0 running in a VM on Proxmox.<\/li>\n\n\n\n<li>Client with version 19.16. running on a VM on Proxmox (different physical server than the DB).<\/li>\n<\/ul>\n\n\n\n<p>REMARK: I do not use technologies, which may increase logon-time (like logon-triggers or logon-auditing) during the tests performed.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-preparation\">Preparation<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-setup-shared-server\">Setup shared server<\/h3>\n\n\n\n<p>For shared server I created a service pdb0_shared in my PDB pdb0:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>alter session set container=pdb0;\nexec dbms_service.create_service('pdb0_shared','pdb0_shared');\nexec dbms_service.start_service('pdb0_shared');<\/code><\/pre>\n\n\n\n<p>Then I setup 10 shared server processes and 4 dispatchers. This has to be done in the root container:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>connect \/ as sysdba\nALTER SYSTEM SET shared_servers= 10 SCOPE = BOTH ;\nALTER SYSTEM SET max_shared_servers = 100 SCOPE = BOTH;\nALTER SYSTEM SET dispatchers ='(INDEX=1)(PROTOCOL=TCP)(dispatchers=4)(SERVICE=DORNER_LIS)' SCOPE = BOTH;<\/code><\/pre>\n\n\n\n<p>REMARK: The INDEX=1 means that I do not change the existing dispatcher-setting (e.g. for XDB).<\/p>\n\n\n\n<p>Verify the setup:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ lsnrctl services listener1\n...\nService \"pdb0_shared\" has 1 instance(s).\n  Instance \"cdb0\", status READY, has 4 handler(s) for this service...\n    Handler(s):\n      \"D004\" established:0 refused:0 current:0 max:1022 state:ready\n         DISPATCHER &lt;machine: pm-DB-OEL8, pid: 57522&gt;\n         (ADDRESS=(PROTOCOL=tcp)(HOST=pm-DB-OEL8)(PORT=32407))\n      \"D003\" established:0 refused:0 current:0 max:1022 state:ready\n         DISPATCHER &lt;machine: pm-DB-OEL8, pid: 57520&gt;\n         (ADDRESS=(PROTOCOL=tcp)(HOST=pm-DB-OEL8)(PORT=17299))\n      \"D002\" established:0 refused:0 current:0 max:1022 state:ready\n         DISPATCHER &lt;machine: pm-DB-OEL8, pid: 57518&gt;\n         (ADDRESS=(PROTOCOL=tcp)(HOST=pm-DB-OEL8)(PORT=19185))\n      \"D001\" established:0 refused:0 current:0 max:1022 state:ready\n         DISPATCHER &lt;machine: pm-DB-OEL8, pid: 57516&gt;\n         (ADDRESS=(PROTOCOL=tcp)(HOST=pm-DB-OEL8)(PORT=15569))\nThe command completed successfully\n\n$ sqlplus cbleile@pm-DB-OEL8:1531\/pdb0_shared:shared\n\nSQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 18 19:29:34 2024\nVersion 19.16.0.0.0\n\nCopyright (c) 1982, 2022, Oracle.  All rights reserved.\n\nEnter password: \nLast Successful login time: Thu Jan 18 2024 19:17:55 +01:00\n\nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.19.0.0.0\n\nSQL&gt; <\/code><\/pre>\n\n\n\n<p>REMARK: Please consider the &#8222;:shared&#8220; in the Easy Connect string. Using a service, which has no shared service connect handler results in an error when connecting:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ sqlplus cbleile@pm-DB-OEL8:1531\/pdb0:shared\n\nSQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 18 19:30:40 2024\nVersion 19.16.0.0.0\n\nCopyright (c) 1982, 2022, Oracle.  All rights reserved.\n\nEnter password: \nERROR:\nORA-12520: TNS:listener could not find available handler for requested type of server<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-setup-pooled-connections-through-the-drcp\">Setup pooled connections through the DRCP<\/h3>\n\n\n\n<p>This is simple with a single command in the root container:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sys@CDB0&gt; EXEC DBMS_CONNECTION_POOL.start_pool;\n\nPL\/SQL procedure successfully completed.\n<\/code><\/pre>\n\n\n\n<p>For details see<br><a href=\"https:\/\/oracle-base.com\/articles\/11g\/database-resident-connection-pool-11gr1\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/oracle-base.com\/articles\/11g\/database-resident-connection-pool-11gr1<\/a><a href=\"https:\/\/oracle-base.com\/articles\/23c\/database-resident-connection-pool-enhancements-23c\" target=\"_blank\" rel=\"noreferrer noopener\"><br>https:\/\/oracle-base.com\/articles\/23c\/database-resident-connection-pool-enhancements-23c<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-testing\">Testing<\/h2>\n\n\n\n<p>I created 3 test scripts for dedicated, shared and pooled connections:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ cat loop_connect_speed_pdb0_dedicated.sh\n#!\/bin\/bash\n# Purpose: Connect to a DB using a dedicated server connect and immediately \n#          disconnet then. Do this n times (parameter 1).\n# History: 18.01.2024 CBL: Created\n#############################################################################\n\nif &#091; $# -ne 2 ]\nthen\n   echo \"Usage: $0 &lt;number of connections&gt; &lt;listener port&gt;\"\n   exit 1\nfi\n\nNUMBER_OF_CONNECTS=$1\nLISTENER_PORT=$2\n\nfor (( c=1; c&lt;=$NUMBER_OF_CONNECTS; c++ ))\ndo\n   echo exit | sqlplus -S cbleile\/&lt;password&gt;@pm-DB-OEL8:${LISTENER_PORT}\/pdb0:dedicated\ndone\n\n\n$ cat loop_connect_speed_pdb0_shared.sh\n#!\/bin\/bash\n# Purpose: Connect to a DB using a shared server connect and immediately \n#          disconnet then. Do this n times (parameter 1).\n# History: 18.01.2024 CBL: Created\n#############################################################################\n\nif &#091; $# -ne 2 ]\nthen\n   echo \"Usage: $0 &lt;number of connections&gt; &lt;listener port&gt;\"\n   exit 1\nfi\n\nNUMBER_OF_CONNECTS=$1\nLISTENER_PORT=$2\n\nfor (( c=1; c&lt;=$NUMBER_OF_CONNECTS; c++ ))\ndo\n   echo exit | sqlplus -S cbleile\/&lt;password&gt;@pm-DB-OEL8:${LISTENER_PORT}\/pdb0_shared:shared\ndone\n\n\n$ cat loop_connect_speed_pdb0_pooled.sh\n#!\/bin\/bash\n# Purpose: Connect to a DB using a pooled server connect and immediately \n#          disconnet then. Do this n times (parameter 1).\n# History: 18.01.2024 CBL: Created\n#############################################################################\n\nif &#091; $# -ne 2 ]\nthen\n   echo \"Usage: $0 &lt;number of connections&gt; &lt;listener port&gt;\"\n   exit 1\nfi\n\nNUMBER_OF_CONNECTS=$1\nLISTENER_PORT=$2\n\nfor (( c=1; c&lt;=$NUMBER_OF_CONNECTS; c++ ))\ndo\n   echo exit | sqlplus -S cbleile\/&lt;password&gt;@pm-DB-OEL8:${LISTENER_PORT}\/pdb0:pooled\ndone\n<\/code><\/pre>\n\n\n\n<p>The scripts do 400 connects to the DB (one after the other in the loop) through the listner listening on port 1531. The results for 400 connects to the DB are as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ time .\/loop_connect_speed_pdb0_dedicated.sh 400 1531\n\nreal\t0m24.489s\nuser\t0m7.418s\nsys\t0m3.132s<\/code><\/pre>\n\n\n\n<p>I.e. in average 61ms per connect\/disconnect using a dedicated server.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code> time .\/loop_connect_speed_pdb0_shared.sh 400 1531\n\nreal\t0m17.399s\nuser\t0m7.460s\nsys\t0m3.038s<\/code><\/pre>\n\n\n\n<p>I.e. in average 44ms per connect\/disconnect using a shared server.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ time .\/loop_connect_speed_pdb0_pooled.sh 400 1531\n\nreal\t0m18.235s\nuser\t0m7.413s\nsys\t0m3.099s<\/code><\/pre>\n\n\n\n<p>I.e. in average 46ms per connect\/disconnect using a pooled server.<\/p>\n\n\n\n<p>I repeated the test several times and results are consistent. I.e. <strong>shared server connections are the fastest followed closely by pooled connections and dedicated server connections are obviously the slowest<\/strong>.<\/p>\n\n\n\n<p>REMARK: Please consider that also the disconnect time is part of the measured time.<\/p>\n\n\n\n<p>In above tests I connected 400 times serially, i.e. one after the other. What happens when connects happen concurrently? How does it scale?<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-concurrent-connects\">Concurrent connects<\/h2>\n\n\n\n<p>I developed a second script, which starts e.g. 400 connections 2 or more times in parallel. I.e. with parallel 2 means I did 400 connections in two session, i.e. 800 connections in total using the following scripts:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>oracle@19c-dg2:\/home\/oracle\/ &#091;rdbms1916] cat run_loop_pdb0_dedicated.sh\n#!\/bin\/bash\n# Purpose: Connect to a DB using a dedicated server connect and immediately \n#          disconnet then. Do this n times (parameter 1) and do this n times in parallel (parameter 2).\n# History: 18.01.2024 CBL: Created\n#############################################################################\n\nif &#091; $# -ne 3 ]\nthen\n   echo \"Usage: $0 &lt;number of connections&gt; &lt;number of parallel starts&gt; &lt;number of listeners&gt;\"\n   exit 1\nfi\n\nNUMBER_OF_CONNECTS=$1\nNUMBER_OF_PARALLEL_STARTS=$2\nNUMBER_OF_LISTENERS=$3\n\nfor (( c=1; c&lt;=$NUMBER_OF_PARALLEL_STARTS; c++ ))\ndo\n   let \"port_nr = $(($c % $NUMBER_OF_LISTENERS)) + 1\"\n#   echo $port_nr\n   .\/loop_connect_speed_pdb0_dedicated.sh $NUMBER_OF_CONNECTS 153${port_nr} &amp;\ndone\n\n# jobs\n\nwait\n<\/code><\/pre>\n\n\n\n<p>The scripts for shared and pooled connects look accordingly.<\/p>\n\n\n\n<p>Here the results to connect 1 &#8211; 6 times in parallel 400 connections (all going over 1 listener) via dedicated server:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>oracle@19c-dg2:\/home\/oracle\/ &#091;rdbms1916] time .\/run_loop_pdb0_dedicated.sh 400 1 1\n\nreal\t0m24.265s\nuser\t0m7.485s\nsys\t0m3.099s\noracle@19c-dg2:\/home\/oracle\/ &#091;rdbms1916] time .\/run_loop_pdb0_dedicated.sh 400 2 1\n\nreal\t0m24.569s\nuser\t0m14.677s\nsys\t0m6.273s\noracle@19c-dg2:\/home\/oracle\/ &#091;rdbms1916] time .\/run_loop_pdb0_dedicated.sh 400 3 1\n\nreal\t0m26.511s\nuser\t0m21.708s\nsys\t0m9.807s\noracle@19c-dg2:\/home\/oracle\/ &#091;rdbms1916] time .\/run_loop_pdb0_dedicated.sh 400 4 1\n\nreal\t0m29.579s\nuser\t0m28.625s\nsys\t0m13.461s\noracle@19c-dg2:\/home\/oracle\/ &#091;rdbms1916] time .\/run_loop_pdb0_dedicated.sh 400 5 1\n\nreal\t0m33.383s\nuser\t0m35.570s\nsys\t0m16.990s\noracle@19c-dg2:\/home\/oracle\/ &#091;rdbms1916] time .\/run_loop_pdb0_dedicated.sh 400 6 1\n\nreal\t0m37.954s\nuser\t0m42.894s\nsys\t0m20.418s<\/code><\/pre>\n\n\n\n<p>So obviously something is slowing us down the higher concurrent logins get.<\/p>\n\n\n\n<p>Results for shared server connects:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>oracle@19c-dg2:\/home\/oracle\/ &#091;rdbms1916] time .\/run_loop_pdb0_shared.sh 400 1 1\n\nreal\t0m17.319s\nuser\t0m7.531s\nsys\t0m3.028s\noracle@19c-dg2:\/home\/oracle\/ &#091;rdbms1916] time .\/run_loop_pdb0_shared.sh 400 2 1\n\nreal\t0m17.301s\nuser\t0m14.338s\nsys\t0m6.468s\noracle@19c-dg2:\/home\/oracle\/ &#091;rdbms1916] time .\/run_loop_pdb0_shared.sh 400 3 1\n\nreal\t0m21.004s\nuser\t0m21.426s\nsys\t0m9.922s\noracle@19c-dg2:\/home\/oracle\/ &#091;rdbms1916] time .\/run_loop_pdb0_shared.sh 400 4 1\n\nreal\t0m25.325s\nuser\t0m28.712s\nsys\t0m13.222s\noracle@19c-dg2:\/home\/oracle\/ &#091;rdbms1916] time .\/run_loop_pdb0_shared.sh 400 5 1\n\nreal\t0m29.989s\nuser\t0m35.659s\nsys\t0m16.876s\noracle@19c-dg2:\/home\/oracle\/ &#091;rdbms1916] time .\/run_loop_pdb0_shared.sh 400 6 1\n\nreal\t0m35.180s\nuser\t0m42.738s\nsys\t0m20.573s<\/code><\/pre>\n\n\n\n<p>I.e. also a slow down and with higher concurrency the times get closer to dedicated connections. Finally pooled connections:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>oracle@19c-dg2:\/home\/oracle\/ &#091;rdbms1916] time .\/run_loop_pdb0_pooled.sh 400 1 1\n\nreal\t0m17.977s\nuser\t0m7.355s\nsys\t0m3.192s\noracle@19c-dg2:\/home\/oracle\/ &#091;rdbms1916] time .\/run_loop_pdb0_pooled.sh 400 2 1\n\nreal\t0m18.000s\nuser\t0m14.439s\nsys\t0m6.428s\noracle@19c-dg2:\/home\/oracle\/ &#091;rdbms1916] time .\/run_loop_pdb0_pooled.sh 400 3 1\n\nreal\t0m21.257s\nuser\t0m21.505s\nsys\t0m9.970s\noracle@19c-dg2:\/home\/oracle\/ &#091;rdbms1916] time .\/run_loop_pdb0_pooled.sh 400 4 1\n\nreal\t0m25.417s\nuser\t0m28.549s\nsys\t0m13.497s\noracle@19c-dg2:\/home\/oracle\/ &#091;rdbms1916] time .\/run_loop_pdb0_pooled.sh 400 5 1\n\nreal\t0m30.227s\nuser\t0m35.761s\nsys\t0m16.912s\noracle@19c-dg2:\/home\/oracle\/ &#091;rdbms1916] time .\/run_loop_pdb0_pooled.sh 400 6 1\n\nreal\t0m35.146s\nuser\t0m42.688s\nsys\t0m20.551s<\/code><\/pre>\n\n\n\n<p>Here a graph showing the result:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/01\/Screenshot-2024-01-23-at-21.13.33-1024x487.png\" alt=\"\" class=\"wp-image-30415\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-summary\">Summary<\/h2>\n\n\n\n<p>If it gets to connect time to an Oracle database then shared and pooled server connects are faster than dedicated server. However, changing from dedicated server connections to e.g. shared server connects is only a workaround. The base problem should be addressed: The application should be designed to use a connection pool and re-use connections without doing login\/logout for every action or, in case of classical client server, keep the connection open. <strong>Please consider that connecting and disconnecting to\/from the DB may take a considerable time in your application. <\/strong>The reason why connects do not scale that well when connecting concurrently will be addressed in a future blog.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>There are still applications around, which do connect to the database, do some stuff and then disconnect again. I.e. for each and every operation a logon to the database is necessary. This produces unnecessary overhead and is expressed in a high percentage of DB time taken by &#8222;connection management call elapsed time&#8220; in the Oracle [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[43,46,3,44,47,42,45],"class_list":["post-46","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-dedicated","tag-dedicated-server","tag-oracle","tag-pooled","tag-pooled-server","tag-shared","tag-shared-server"],"_links":{"self":[{"href":"http:\/\/cbleile.de\/wordpress\/wp-json\/wp\/v2\/posts\/46","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/cbleile.de\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/cbleile.de\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/cbleile.de\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/cbleile.de\/wordpress\/wp-json\/wp\/v2\/comments?post=46"}],"version-history":[{"count":1,"href":"http:\/\/cbleile.de\/wordpress\/wp-json\/wp\/v2\/posts\/46\/revisions"}],"predecessor-version":[{"id":47,"href":"http:\/\/cbleile.de\/wordpress\/wp-json\/wp\/v2\/posts\/46\/revisions\/47"}],"wp:attachment":[{"href":"http:\/\/cbleile.de\/wordpress\/wp-json\/wp\/v2\/media?parent=46"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/cbleile.de\/wordpress\/wp-json\/wp\/v2\/categories?post=46"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/cbleile.de\/wordpress\/wp-json\/wp\/v2\/tags?post=46"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}