{"id":17088,"date":"2022-01-24T22:11:13","date_gmt":"2022-01-24T21:11:13","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-partition-and-performance-of-massive-concurrent-inserts\/"},"modified":"2022-05-31T16:32:32","modified_gmt":"2022-05-31T14:32:32","slug":"oracle-partition-and-performance-of-massive-concurrent-inserts","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-partition-and-performance-of-massive-concurrent-inserts\/","title":{"rendered":"Oracle Partition and Performance of massive\/concurrent Inserts"},"content":{"rendered":"<p>For a customer, I had to check if partitioning improves performance of massive and concurrent inserts.<\/p>\n<p>The goal is to execute several Inserts in parallel via dbms_parallel_execute package (my previous blog &#8220;<a href=\"https:\/\/www.dbi-services.com\/blog\/parallelize-your-oracle-insert-with-dbms_parallel_execute\/\">parallelize your Oracle Insert with DBMS_PARALLEL_EXECUTE<\/a>&#8221; explains how to use dbms_parallel_execute).<\/p>\n<p>The idea is to insert more than 20 millions of rows in 2 tables :<\/p>\n<ul>\n<li>One table not partitioned &#8211;&gt; DBI_FK_NOPART<\/li>\n<li>One table partitioned in HASH &#8211;&gt; DBI_FK_PART<\/li>\n<\/ul>\n<p>Both table have the same columns, same indexes but of different type :<\/p>\n<ul>\n<li>All Indexes on the table partitioned are global:\n<ul>\n<li><em>CREATE INDEX &#8230;GLOBAL PARTITION BY HASH (&#8230;.)&#8230;.<\/em><\/li>\n<\/ul>\n<\/li>\n<li>All indexes on the table not partitioned are normal\n<ul>\n<li>CREATE INDEX &#8230;ON&#8230;<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">--Table DBI_FK_PART --&gt; PARTITIONED\nSQL&gt; select TABLE_NAME,PARTITION_NAME from dba_tab_partitions where table_name = 'DBI_FK_PART';\n\nTABLE_NAME          PARTITION_NAME\n------------------- --------------------------------------------------------------------------------------------------------------------------------\nDBI_FK_PART         SYS_P9797\nDBI_FK_PART         SYS_P9798\nDBI_FK_PART         SYS_P9799\nDBI_FK_PART         SYS_P9800\nDBI_FK_PART         SYS_P9801\nDBI_FK_PART         SYS_P9802\nDBI_FK_PART         SYS_P9803\nDBI_FK_PART         SYS_P9804\nDBI_FK_PART         SYS_P9805\nDBI_FK_PART         SYS_P9806\nDBI_FK_PART         SYS_P9807\n\nTABLE_NAME          PARTITION_NAME\n------------------- --------------------------------------------------------------------------------------------------------------------------------\nDBI_FK_PART         SYS_P9808\nDBI_FK_PART         SYS_P9809\nDBI_FK_PART         SYS_P9810\nDBI_FK_PART         SYS_P9811\nDBI_FK_PART         SYS_P9812\nDBI_FK_PART         SYS_P9813\nDBI_FK_PART         SYS_P9814\nDBI_FK_PART         SYS_P9815\nDBI_FK_PART         SYS_P9816\nDBI_FK_PART         SYS_P9817\nDBI_FK_PART         SYS_P9818\n\nTABLE_NAME          PARTITION_NAME\n------------------- --------------------------------------------------------------------------------------------------------------------------------\nDBI_FK_PART         SYS_P9819\nDBI_FK_PART         SYS_P9820\nDBI_FK_PART         SYS_P9821\nDBI_FK_PART         SYS_P9822\nDBI_FK_PART         SYS_P9823\nDBI_FK_PART         SYS_P9824\nDBI_FK_PART         SYS_P9825\nDBI_FK_PART         SYS_P9826\nDBI_FK_PART         SYS_P9827\nDBI_FK_PART         SYS_P9828\n\n32 rows selected.\n\n\n--TABLE DBI_FK_NOPART --&gt; NOT PARTITIONED\n\nSQL&gt; select TABLE_NAME,PARTITION_NAME from dba_tab_partitions where table_name = 'DBI_FK_NOPART';\n\nno rows selected\n\nSQL&gt;<\/pre>\n<p>Each table has more than 1.2 billion of rows:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select count(*) from xxxx.dbi_fk_nopart;\n\n  COUNT(*)\n----------\n1241226011\n\n1 row selected.\n\nSQL&gt; select count(*) from xxxx.dbi_fk_part;\n\n  COUNT(*)\n----------\n1196189234\n\n1 row selected.\n\n<\/pre>\n<p>Let&#8217;s check the maximum primary key for the both tables :<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select max(pkey) from xxxx.dbi_fk_part;\n\n MAX(PKEY)\n----------\n9950649803\n\n1 row selected.\n\nSQL&gt; select max(pkey) from xxxx.dbi_fk_nopart;\n\n MAX(PKEY)\n----------\n9960649804\n\n1 row selected.\n\nSQL&gt;<\/pre>\n<p>Let&#8217;s create 2 procedures :<\/p>\n<ul>\n<li>&#8220;test_insert_nopart&#8221; which do the Insert into the table not partitioned &#8220;DBI_FK_NOPART&#8221;<\/li>\n<li>&#8220;test_insert_part&#8221; which do the Insert into the table partitioned &#8220;DBI_FK_PART&#8221;<\/li>\n<\/ul>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">create or replace NONEDITIONABLE procedure test_insert_nopart is\n\nv_sql_stmt varchar2(32767);\nv_pkey number;\nl_chunk_id NUMBER;\nl_start_id NUMBER;\n  l_end_id   NUMBER;\n  l_any_rows BOOLEAN;\n  l_try      NUMBER;\n  l_status   NUMBER;\nbegin\n    DBMS_OUTPUT.PUT_LINE('start : '||to_char(sysdate,'hh24:mi:ss'));\n\n   begin\n     DBMS_PARALLEL_EXECUTE.DROP_TASK(task_name =&gt; 'TASK_NAME');\n   exception when others then null;\n   end;\n\n   DBMS_PARALLEL_EXECUTE.CREATE_TASK(task_name =&gt; 'TASK_NAME');\n   --We create 3 chunks\n   DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL(task_name =&gt; 'TASK_NAME',sql_stmt =&gt;'SELECT NUM_COL,NUM_COL+10 FROM TEST_TAB WHERE ROWNUM &lt; 10001', by_rowid =&gt; false);   \n\n   SELECT max(pkey) into v_pkey FROM XXXX.DBI_FK_NOPART;\n   --I will Insert 1000 rows for each chunks, each chunks will work with different session_id\n   v_sql_stmt := 'declare\n       s varchar2(16000); vstart_id number := :start_id; vend_id number:= :end_id;\n       table_name varchar2(30);\n       v_pkey number;\n       begin\n         EXECUTE IMMEDIATE ''SELECT max(pkey) FROM XXXX.DBI_FK_NOPART'' INTO v_pkey;\n         for rec in 1..1000 loop\n         s:=''INSERT \/*TEST_INSERT_DBI_FK_NOPART*\/ INTO XXXX.DBI_FK_NOPART ( \n        pkey,\n        boid,\n        metabo,\n        lastupdate,\n        processid,\n        rowcomment,\n        created,\n        createduser,\n        replaced,\n        replaceduser,\n        archivetag,\n        mdbid,\n        itsforecast,\n        betrag,\n        itsopdetherkunft,\n        itsopdethkerstprm,\n        itsfckomppreisseq,\n        clsfckomppreisseq,\n        issummandendpreis,\n        partitiontag,\n        partitiondomain,\n        fcvprodkomppkey,\n        fckvprdankomppkey,\n        session_id\n        ) VALUES (\n        1 +'||v_pkey||' ,\n         ''''8189b7c7-0c36-485b-8993-054dddd62708'''' ,\n        -695,\n        sysdate,\n         ''''B.3142'''' ,\n        NULL,\n        SYSDATE,\n         ''''XXXX_DEV_DBITEST'''' ,\n        SYSDATE,\n        NULL,\n        NULL,\n        NULL,\n        ''''8a9f1321-b3ec-46d5-b6c7-af1c7fb5167G'''' ,\n        0,\n         ''''ae03b3fc-b31c-433b-be0f-c8b0bdaa82fK'''' ,\n        NULL,\n         ''''5849f308-215b-486b-95bd-cbd7afe8440H'''',  \n        -251,\n        0,\n        201905,\n         ''''E'''',  \n        :start_id,\n        :end_id,\n        SYS_CONTEXT(''''USERENV'''',''''SESSIONID''''))'';\n         execute immediate s using vstart_id, vend_id;\n         commit;\n         end loop;\n     end;';\ndbms_output.put_Line (v_sql_stmt);\n\n   DBMS_PARALLEL_EXECUTE.RUN_TASK (task_name =&gt; 'TASK_NAME',\n     sql_stmt =&gt;v_sql_stmt,\n     language_flag =&gt; DBMS_SQL.NATIVE, parallel_level =&gt; 4 );\n\n    DBMS_OUTPUT.PUT_LINE('end : '||to_char(sysdate,'hh24:mi:ss'));\n\nend;\n\n\ncreate or replace NONEDITIONABLE procedure test_insert_part is\n\nv_sql_stmt varchar2(32767);\nv_pkey number;\nl_chunk_id NUMBER;\nl_start_id NUMBER;\n  l_end_id   NUMBER;\n  l_any_rows BOOLEAN;\n  l_try      NUMBER;\n  l_status   NUMBER;\nbegin\n    DBMS_OUTPUT.PUT_LINE('start : '||to_char(sysdate,'hh24:mi:ss'));\n\n   begin\n     DBMS_PARALLEL_EXECUTE.DROP_TASK(task_name =&gt; 'TASK_NAME');\n   exception when others then null;\n   end;\n\n   DBMS_PARALLEL_EXECUTE.CREATE_TASK(task_name =&gt; 'TASK_NAME');\n   --We create 3 chunks\n   DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL(task_name =&gt; 'TASK_NAME',sql_stmt =&gt;'SELECT NUM_COL,NUM_COL+10 FROM TEST_TAB WHERE ROWNUM &lt; 10001', by_rowid =&gt; false);   \n\n   SELECT max(pkey) into v_pkey FROM XXXX.DBI_FK_PART;\n   --I will Insert 1000 rows for each chunks, each chunks will work with different session_id\n   v_sql_stmt := 'declare\n       s varchar2(16000); vstart_id number := :start_id; vend_id number:= :end_id;\n       table_name varchar2(30);\n       v_pkey number;\n       begin\n         EXECUTE IMMEDIATE ''SELECT max(pkey) FROM xxxx.DBI_FK_PART'' INTO v_pkey;\n         for rec in 1..1000 loop\n         s:=''INSERT \/*TEST_INSERT_DBI_FK_PART*\/ INTO xxxx.DBI_FK_PART ( \n        pkey,\n        boid,\n        metabo,\n        lastupdate,\n        processid,\n        rowcomment,\n        created,\n        createduser,\n        replaced,\n        replaceduser,\n        archivetag,\n        mdbid,\n        itsforecast,\n        betrag,\n        itsopdetherkunft,\n        itsopdethkerstprm,\n        itsfckomppreisseq,\n        clsfckomppreisseq,\n        issummandendpreis,\n        partitiontag,\n        partitiondomain,\n        fcvprodkomppkey,\n        fckvprdankomppkey,\n        session_id\n        ) VALUES (\n        1 +'||v_pkey||' ,\n         ''''8189b7c7-0c36-485b-8993-054dddd62708'''' ,\n        -695,\n        sysdate,\n         ''''B.3142'''' ,\n        NULL,\n        SYSDATE,\n         ''''xxxx_DBITEST'''' ,\n        SYSDATE,\n        NULL,\n        NULL,\n        NULL,\n        ''''8a9f1321-b3ec-46d5-b6c7-af1c7fb5167G'''' ,\n        0,\n         ''''ae03b3fc-b31c-433b-be0f-c8b0bdaa82fK'''' ,\n        NULL,\n         ''''5849f308-215b-486b-95bd-cbd7afe8440H'''',  \n        -251,\n        0,\n        201905,\n         ''''E'''',  \n        :start_id,\n        :end_id,\n        SYS_CONTEXT(''''USERENV'''',''''SESSIONID''''))'';\n         execute immediate s using vstart_id, vend_id;\n         commit;\n         end loop;\n     end;';\ndbms_output.put_Line (v_sql_stmt);\n\n   DBMS_PARALLEL_EXECUTE.RUN_TASK (task_name =&gt; 'TASK_NAME',\n     sql_stmt =&gt;v_sql_stmt,\n     language_flag =&gt; DBMS_SQL.NATIVE, parallel_level =&gt; 4 );\n\n    DBMS_OUTPUT.PUT_LINE('end : '||to_char(sysdate,'hh24:mi:ss'));\n\nend;<\/pre>\n<p>&nbsp;<\/p>\n<p>Now let&#8217;s inserting about 20 millions of rows in each tables via the procedures we created above:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; set timing on\nSQL&gt; set autotrace on\nSQL&gt; begin\n  2  test_insert_nopart;\n  3  end;\n  4  \/\n\nPL\/SQL procedure successfully completed.\n\nElapsed: 00:06:30.34\nSQL&gt; begin\n  2  test_insert_part;\n  3  end;\n  4\n  5  \/\n\nPL\/SQL procedure successfully completed.\n\nElapsed: 00:00:22.92\n\n\nSQL&gt; select max(pkey) from xxxx.dbi_fk_nopart;\n\n MAX(PKEY)\n----------\n9980650809\n\nSQL&gt; select 9980650809 - 9960649804 from dual;\n\n9980650809-9960649804\n---------------------\n             20001005\n             \nSQL&gt; select max(pkey) from xxxx.dbi_fk_part;\n\n MAX(PKEY)\n----------\n9980811483\n\nSQL&gt; select 9980811483 - 9950649803 from dual;\n\n9980811483-9950649803\n---------------------\n             30161680\n\n\n<\/pre>\n<p><strong>FIRST CONCLUSION:<\/strong><\/p>\n<ul>\n<li>About 20 millions of rows has been inserted into the table not partitioned &#8220;DBI_FK_NOPART&#8221;\u00a0 in 06.30.34 mins<\/li>\n<li>About 30 millions of rows has been inserted into the table partitioned &#8220;DBI_FK_PART&#8221;\u00a0 in 22 sec<\/li>\n<\/ul>\n<p>Do a massive concurrent INSERT on a huge table is always faster on table partitioned compare a table non partitioned.<\/p>\n<p>&nbsp;<\/p>\n<p>Now, let&#8217;s check OEM graphics to understand why the Insert is 17 times faster into DBI_FK_PART than DBI_FK_NOPART<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/01\/OraclePartitionning.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-53975 size-full\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/01\/OraclePartitionning.png\" alt=\"\" width=\"1464\" height=\"345\" \/><\/a><\/p>\n<p>Between 03:40 PM and 03:46 PM, we can see the peak related to the Insert on DBI_FK_NOPART<\/p>\n<p>At 03:49 PM, we can see a very small peak related to the Insert related to DBI_FK_PART<\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/01\/OraclePartitionning1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-53976 size-full\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/01\/OraclePartitionning1.png\" alt=\"\" width=\"904\" height=\"249\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>If we focus only on the INSERT command (1st line and 4th line), the one into DBI_FK_PART (table partitioned) waits less on CPU (green) and CONCURRENCY (purple) compare to INSERT in DBI_FK_NOPART (Table partitioned) where the I\/O is the event the most important.<\/p>\n<p>Let&#8217;s see more in details on which event the database is waiting for both INSERT:<\/p>\n<p>For INSERT into DBI_FK_NOPART:<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/01\/OraclePartitionning2.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-53979 size-full\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/01\/OraclePartitionning2.png\" alt=\"\" width=\"837\" height=\"415\" \/><\/a><\/p>\n<p>And if we click into Concurrency Event :<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/01\/OraclePartitionning4.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-53983 size-full\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/01\/OraclePartitionning4.png\" alt=\"\" width=\"836\" height=\"674\" \/><\/a><\/p>\n<p>For INSERT into DBI_FK_PART:<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/01\/OraclePartitionning3-1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-53981 size-full\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/01\/OraclePartitionning3-1.png\" alt=\"\" width=\"883\" height=\"279\" \/><\/a><\/p>\n<p>If we click on Concurrency Event :<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/01\/OraclePartitionning5.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-53984 size-full\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/01\/OraclePartitionning5.png\" alt=\"\" width=\"825\" height=\"336\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong>SECOND CONCLUSION<\/strong><\/p>\n<p>The event &#8220;db file sequential read&#8221; seems indicate that the difference of response time between the both tables seems due to the type of index we created on each table (Global Partitioned Index on partitioned table VS Normal Index on nonpartitioned table).<\/p>\n<p>As it&#8217;s possible to create Global Partitioned Index on nonpartitioned table, another &#8220;<em>interesting<\/em>&#8221; test (not done on this blog) should be to replace normal indexes by global indexes on non partitioned tables and check if response time is faster.<\/p>\n<p>To conclude, if we have Partitioning license, in term of performance, we should always partition huge tables accessed several times in read (SELECT) or in write (INSERT).<\/p>\n","protected":false},"excerpt":{"rendered":"<p>For a customer, I had to check if partitioning improves performance of massive and concurrent inserts. The goal is to execute several Inserts in parallel via dbms_parallel_execute package (my previous blog &#8220;parallelize your Oracle Insert with DBMS_PARALLEL_EXECUTE&#8221; explains how to use dbms_parallel_execute). The idea is to insert more than 20 millions of rows in 2 [&hellip;]<\/p>\n","protected":false},"author":27,"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":[1322,96,644,24,98],"type_dbi":[],"class_list":["post-17088","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","category-development-performance","category-oracle","tag-hash-partitioning","tag-oracle","tag-performance-tuning","tag-pl-sql","tag-sql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.5) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Oracle Partition and Performance of massive\/concurrent Inserts - 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\/oracle-partition-and-performance-of-massive-concurrent-inserts\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle Partition and Performance of massive\/concurrent Inserts\" \/>\n<meta property=\"og:description\" content=\"For a customer, I had to check if partitioning improves performance of massive and concurrent inserts. The goal is to execute several Inserts in parallel via dbms_parallel_execute package (my previous blog &#8220;parallelize your Oracle Insert with DBMS_PARALLEL_EXECUTE&#8221; explains how to use dbms_parallel_execute). The idea is to insert more than 20 millions of rows in 2 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-partition-and-performance-of-massive-concurrent-inserts\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-01-24T21:11:13+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-05-31T14:32:32+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/01\/OraclePartitionning.png\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 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\\\/oracle-partition-and-performance-of-massive-concurrent-inserts\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-partition-and-performance-of-massive-concurrent-inserts\\\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle Partition and Performance of massive\\\/concurrent Inserts\",\"datePublished\":\"2022-01-24T21:11:13+00:00\",\"dateModified\":\"2022-05-31T14:32:32+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-partition-and-performance-of-massive-concurrent-inserts\\\/\"},\"wordCount\":511,\"commentCount\":1,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-partition-and-performance-of-massive-concurrent-inserts\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/01\\\/OraclePartitionning.png\",\"keywords\":[\"Hash Partitioning\",\"Oracle\",\"Performance Tuning\",\"PL\\\/SQL\",\"SQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\",\"Development &amp; Performance\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-partition-and-performance-of-massive-concurrent-inserts\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-partition-and-performance-of-massive-concurrent-inserts\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-partition-and-performance-of-massive-concurrent-inserts\\\/\",\"name\":\"Oracle Partition and Performance of massive\\\/concurrent Inserts - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-partition-and-performance-of-massive-concurrent-inserts\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-partition-and-performance-of-massive-concurrent-inserts\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/01\\\/OraclePartitionning.png\",\"datePublished\":\"2022-01-24T21:11:13+00:00\",\"dateModified\":\"2022-05-31T14:32:32+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-partition-and-performance-of-massive-concurrent-inserts\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-partition-and-performance-of-massive-concurrent-inserts\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-partition-and-performance-of-massive-concurrent-inserts\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/01\\\/OraclePartitionning.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/01\\\/OraclePartitionning.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-partition-and-performance-of-massive-concurrent-inserts\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle Partition and Performance of massive\\\/concurrent Inserts\"}]},{\"@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\\\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/oracle-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle Partition and Performance of massive\/concurrent Inserts - 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\/oracle-partition-and-performance-of-massive-concurrent-inserts\/","og_locale":"en_US","og_type":"article","og_title":"Oracle Partition and Performance of massive\/concurrent Inserts","og_description":"For a customer, I had to check if partitioning improves performance of massive and concurrent inserts. The goal is to execute several Inserts in parallel via dbms_parallel_execute package (my previous blog &#8220;parallelize your Oracle Insert with DBMS_PARALLEL_EXECUTE&#8221; explains how to use dbms_parallel_execute). The idea is to insert more than 20 millions of rows in 2 [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-partition-and-performance-of-massive-concurrent-inserts\/","og_site_name":"dbi Blog","article_published_time":"2022-01-24T21:11:13+00:00","article_modified_time":"2022-05-31T14:32:32+00:00","og_image":[{"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/01\/OraclePartitionning.png","type":"","width":"","height":""}],"author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-partition-and-performance-of-massive-concurrent-inserts\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-partition-and-performance-of-massive-concurrent-inserts\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle Partition and Performance of massive\/concurrent Inserts","datePublished":"2022-01-24T21:11:13+00:00","dateModified":"2022-05-31T14:32:32+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-partition-and-performance-of-massive-concurrent-inserts\/"},"wordCount":511,"commentCount":1,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-partition-and-performance-of-massive-concurrent-inserts\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/01\/OraclePartitionning.png","keywords":["Hash Partitioning","Oracle","Performance Tuning","PL\/SQL","SQL"],"articleSection":["Database Administration &amp; Monitoring","Database management","Development &amp; Performance","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-partition-and-performance-of-massive-concurrent-inserts\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-partition-and-performance-of-massive-concurrent-inserts\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-partition-and-performance-of-massive-concurrent-inserts\/","name":"Oracle Partition and Performance of massive\/concurrent Inserts - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-partition-and-performance-of-massive-concurrent-inserts\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-partition-and-performance-of-massive-concurrent-inserts\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/01\/OraclePartitionning.png","datePublished":"2022-01-24T21:11:13+00:00","dateModified":"2022-05-31T14:32:32+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-partition-and-performance-of-massive-concurrent-inserts\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-partition-and-performance-of-massive-concurrent-inserts\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-partition-and-performance-of-massive-concurrent-inserts\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/01\/OraclePartitionning.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/01\/OraclePartitionning.png"},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-partition-and-performance-of-massive-concurrent-inserts\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle Partition and Performance of massive\/concurrent Inserts"}]},{"@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\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17088","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\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=17088"}],"version-history":[{"count":1,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17088\/revisions"}],"predecessor-version":[{"id":17089,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17088\/revisions\/17089"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=17088"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=17088"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=17088"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=17088"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}