{"id":14187,"date":"2020-05-24T23:28:23","date_gmt":"2020-05-24T21:28:23","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/how-to-use-dbms_scheduler-to-improve-performance\/"},"modified":"2020-05-24T23:28:23","modified_gmt":"2020-05-24T21:28:23","slug":"how-to-use-dbms_scheduler-to-improve-performance","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/how-to-use-dbms_scheduler-to-improve-performance\/","title":{"rendered":"How to use DBMS_SCHEDULER to improve performance ?"},"content":{"rendered":"

From an application point of view, the oracle scheduler DBMS_SCHEDULER allows to reach best performance by parallelizing your process.<\/p>\n

Let’s start with the following PL\/SQL code inserting in serial<\/strong> several rows from a metadata table to a target table. In my example, the metadata table does not contain “directly” the data but a set a of sql statement to be executed and for which the rows returned must be inserted into the target table My_Target_Table_Serial :<\/p>\n

Let’s verify the contents of the source table called My_Metadata_Table:<\/p>\n

SQL> SELECT priority,dwh_id, amq_name, sql_statement,scope from dwh_amq_v2;\nROWNUM  DWH_ID  AMQ_NAME SQL_STATEMENT          SCOPE\n1\t7\tAAA1\t SELECT SUM(P.age pt.p\tTYPE1\n2\t28\tBBB2  \t SELECT CASE WHEN pt.p\tTYPE1\n3\t37\tCCC3\t \"select cm.case_id fr\"\tTYPE2\n4\t48\tDDD4\t \"select cm.case_id fr\"\tTYPE2\n5\t73\tEEE5\t SELECT DISTINCT pt.p\tTYPE1\n6\t90\tFFF6 \t SELECT LAG(ORW pt.p\tTYPE1\n7\t114\tGGG7\t SELECT distinct pt.\tTYPE1\n8\t125\tHHH8\t SELECT DISTINCT pt.p\tTYPE1\n...\n148    115     ZZZ48    SELECT ROUND(TO_NUMBER TYPE2<\/pre>\n
Now let’s check the PL\/SQL program :<\/p>\n
DECLARE\n  l_errm VARCHAR2(200);\n  l_sql  VARCHAR2(32767) := NULL;\n  sql_statement_1  VARCHAR2(32767) := NULL;\n  sql_statement_2  VARCHAR2(32767) := NULL;\n  l_amq_name VARCHAR2(200);\n  l_date NUMBER;\nBEGIN\n  SELECT TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')) INTO l_date FROM dual;\n  FOR rec IN (SELECT dwh_id, amq_name, sql_statement,scope \n                FROM My_Metadata_Table,\n                     (SELECT dwh_pit_date FROM dwh_code_mv) pt\n               WHERE dwh_status = 1\n                 AND (pt.dwh_pit_date >= dwh_valid_from AND pt.dwh_pit_date < dwh_valid_to) \n               ORDER BY priority, dwh_id) LOOP\n    ...\n    sql_statement_1 := substr(rec.sql_statement, 1, 32000);\n    sql_statement_2 := substr(rec.sql_statement, 32001);\n    IF rec.SCOPE = 'TYPE1' THEN \n      -- TYPE1 LEVEL SELECT\n      l_sql := 'INSERT \/*+ APPEND *\/ INTO My_Target_Table_Serial (dwh_pit_date, AMQ_ID, AMQ_TEXT, CASE_ID, ENTERPRISE_ID)'||CHR(13)|| 'SELECT DISTINCT TO_DATE(code.dwh_pit_date, ''YYYYMMDDHH24MISS''),'||rec.dwh_id|| ',''' ||rec.amq_name ||''', case_id, 1'||CHR(13)\n      || ' FROM (SELECT dwh_pit_date FROM dwh_code) code, ('||sql_statement_1;\n      EXECUTE IMMEDIATE l_sql || sql_statement_2 || ')';\n      COMMIT;    \n    ELSE \n      -- TYPE2 LEVEL SELECT\n      l_sql :=  'INSERT \/*+ APPEND *\/ INTO My_Target_Table_Serial (dwh_pit_date, AMQ_ID, AMQ_TEXT, CASE_ID, ENTERPRISE_ID)\n      SELECT DISTINCT TO_DATE(code.dwh_pit_date, ''YYYYMMDDHH24MISS''), '||rec.dwh_id|| ',''' ||rec.amq_name || ''', cm.case_id, cm.enterprise_id'||CHR(13)\n      || '  FROM (SELECT dwh_pit_date FROM dwh_code) code, v_sc_case_master cm, v_sc_case_event ce, ('||sql_statement_1;\n              \n      EXECUTE IMMEDIATE l_sql || sql_statement_2 || ') pt'||CHR(13)\n      || ' WHERE cm.case_id = ce.case_id'||CHR(13) \n      || '   AND cm.deleted IS NULL AND cm.state_id <> 1'||CHR(13)\n      || '   AND ce.deleted IS NULL AND ce.pref_term = pt.pt_name';\n      COMMIT;         \n    END IF;\n    ...\n   END LOOP:\nEND;\nNumber of Rows Read : 148 (Means 148 Sql Statement to execute)\nSTART : 16:17:46\nEND : 16:57:42\nTotal :  40 mins\n<\/pre>\n
 <\/p>\n
As we can see, each Sql Statement is executed in serial, let’s check the audit table recording the loading time (Insert Time) and the “scheduling”\u00a0\u00a0 :<\/p>\n
CREATE_DATE\t\tNAME\tSTART_DATE\t\tEND_DATE            LOADING_TIME\n22.05.2020 16:46:34\tAAA1\t22.05.2020 16:46:34\t22.05.2020 16:57:42    11.08mins\n22.05.2020 16:42:05\tBBB2\t22.05.2020 16:42:05\t22.05.2020 16:46:34    04.29mins\n22.05.2020 16:41:15\tCCC3\t22.05.2020 16:41:15\t22.05.2020 16:42:05    50sec\n22.05.2020 16:40:42\tDDD4\t22.05.2020 16:40:42\t22.05.2020 16:41:15    32sec\n22.05.2020 16:40:20\tEEE5\t22.05.2020 16:40:20\t22.05.2020 16:40:42    22sec\n22.05.2020 16:37:23\tFFF6\t22.05.2020 16:37:23\t22.05.2020 16:40:20    02.57mins\n22.05.2020 16:37:12\tGGG7\t22.05.2020 16:37:12\t22.05.2020 16:37:23    11sec\n...\n22.05.2020 16:36:03\tZZZ148\t22.05.2020 16:17:35\t22.05.2020 16:17:46    11sec<\/pre>\n
To resume :<\/p>\n
    \n
  • The 148 rows (148 Sql Statement) coming from the source table are loaded in serial in 40mins.<\/li>\n
  • The majority of rows have taken less than 01 min to load (Ex. : Name = CCC3,DDD4,EEE5,GGG7 and ZZZ148)<\/li>\n
  • Few rows have taken more than a couple of minutes to load.<\/li>\n
  • The maximum loading time is 11.08mins for the Name “AA1”.<\/li>\n
  • Each row must wait the previous row complete his loading before to start his loading (compare END_DATE previous vs START_DATE current).<\/li>\n<\/ul>\n
    To optimize the process, let’s trying to load all the rows coming from the source table in parallel by using the oracle scheduler DBMS_SCHEDULER.<\/p>\n
    Instead to execute directly the Insert command in the loop, let’s create a job through DBMS_SCHEDULER:<\/p>\n
    FOR rec IN (SELECT priority,dwh_id, amq_name, sql_statement,scope \n                FROM My_Metadata_Table,\n                     (SELECT dwh_pit_date FROM dwh_code_mv) pt\n               WHERE dwh_status = 1\n                 AND (pt.dwh_pit_date >= dwh_valid_from AND pt.dwh_pit_date < dwh_valid_to) \n               ORDER BY priority, dwh_id) LOOP\n\n     l_amq_name := rec.amq_name;\n       IF rec.SCOPE = 'TYPE1' THEN \n        -- TYPE1 LEVEL SELECT\n         ...\n  \n            --Execute Job to insert the AMQ : Background process\n            DBMS_SCHEDULER.CREATE_JOB (\n            job_name             => 'AMQ_P'||rec.priority||'j'||i||'_'||l_date,\n            job_type             => 'PLSQL_BLOCK',\n            job_action           => 'BEGIN\n                                      LOAD_DATA(''CASE'','||''''||l_amq_name||''''||','||rec.priority||','||l_date||','||v_SESSION_ID||','||i||');\n                                     END;',\n            start_date    =>  sysdate,  \n            enabled       =>  TRUE,  \n            auto_drop     =>  TRUE,  \n            comments      =>  'job for amq '||l_amq_name);\n          END IF;\n        ELSE \n            ...\n            END IF;\n        END IF; \n      i := i +1;\n  END LOOP;\nNumber of Rows Read : 148 (Means 148 Sql Statement to execute)\nSTART : 08:14:03\nEND : 08:42:32\nTotal :  27.57 mins\n<\/pre>\n
    To resume :<\/p>\n
      \n
    • The 148 rows (148 Sql Statement) coming from the source table are loaded now in parallel<\/strong> in 27.57mins instead of 40mins in serial.<\/li>\n
    • The options of DBMS_SCHEDULER are\u00a0 :\n
        \n
      • As we are limited in number of character for the parameter “job_action”, we have to insert the data through a PL\/SQL procedure LOAD_DATA.<\/li>\n
      • The job is executed immediately (start_date=sysdate) and purged immediately after his execution (auto_drop=TRUE).<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
        Let’s check now how the jobs are scheduled. Since we do a loop of 148 times, I expect to have 148 jobs:<\/p>\n
        First, let’s check now if the rows (Remember, One Row = One Insert Into Target Table From Source Table) are loaded in parallel :<\/p>\n
        CREATE_DATE \t    NAME START_DATE \t        END_DATE \t\t\t\t       \n22.05.2020 16:46:34 AAA1 23.05.2020 08:14:04\t23.05.2020 08:21:19\n22.05.2020 16:42:05 BBB2 23.05.2020 08:14:04\t23.05.2020 08:20:43\n22.05.2020 16:41:15 CCC3 23.05.2020 08:14:04\t23.05.2020 08:21:59\n22.05.2020 16:40:42 DDD4 23.05.2020 08:14:03\t23.05.2020 08:15:29\n22.05.2020 16:40:20 EEE5 23.05.2020 08:14:03\t23.05.2020 08:15:05\n22.05.2020 16:37:23 FFF6 23.05.2020 08:14:03\t23.05.2020 08:14:47\n22.05.2020 16:37:12 GGG7 23.05.2020 08:14:03\t23.05.2020 08:15:59\n...                     \n22.05.2020 16:36:03 ZZZ148 22.05.2020 16:17:35 22.05.2020 16:17:46<\/pre>\n
        This is the case, all rows have the same start_date, meaning all rows start in parallel. Let’s verify into “all_scheduler_job_run_details” to check we have our 148 jobs in parallel :<\/p>\n
        SQL> select count(*) from all_scheduler_job_run_details where job_name like '%20200523081403';\n\n  COUNT(*)\n----------\n       148\nSQL> select log_date,job_name,status,req_start_date from all_scheduler_job_run_details where job_name like '%20200523081403';\nLOG_DATE\t\tJOB_NAME\t\t        STATUS\t\tREQ_START_DATE\n23-MAY-20 08.42.41\tAMQ_P3J147_20200523081403\tSUCCEEDED\t23-MAY-20 02.42.32\n23-MAY-20 08.42.32\tAMQ_P2J146_20200523081403\tSUCCEEDED\t23-MAY-20 02.23.13\n23-MAY-20 08.37.56\tAMQ_P2J145_20200523081403\tSUCCEEDED\t23-MAY-20 02.23.13\n23-MAY-20 08.37.33\tAMQ_P2J144_20200523081403\tSUCCEEDED\t23-MAY-20 02.23.13\n23-MAY-20 08.37.22\tAMQ_P2J143_20200523081403\tSUCCEEDED\t23-MAY-20 02.23.13\n23-MAY-20 08.37.03\tAMQ_P2J141_20200523081403\tSUCCEEDED\t23-MAY-20 02.23.13\n23-MAY-20 08.36.50\tAMQ_P2J142_20200523081403\tSUCCEEDED\t23-MAY-20 02.23.13\n23-MAY-20 08.33.57\tAMQ_P2J140_20200523081403\tSUCCEEDED\t23-MAY-20 02.23.13\n--Only the first 8 rows are displayed<\/pre>\n
        To resume :<\/p>\n
          \n
        • We have 148 jobs all started, most of the time in parallel (job with same REQ_START_DATE, oracle parallelizes jobs per block randomly).<\/li>\n
        • My PL\/SQL process now took 27.57 mins instead of 40mins.<\/li>\n<\/ul>\n
          But if we have a look in details, we have a lot of small jobs. Those are jobs where run_duration is less than 01 mins:<\/p>\n
          SQL> select run_duration from all_scheduler_job_run_details where job_name like '%20200523081403' order by run_duration;\n\nRUN_DURATION\n+00 00:00:04.000000\n+00 00:00:07.000000\n+00 00:00:09.000000\n+00 00:00:10.000000\n+00 00:00:13.000000\n+00 00:00:15.000000\n+00 00:00:20.000000\n+00 00:00:27.000000\n+00 00:00:33.000000\n+00 00:00:35.000000\n+00 00:00:36.000000\n+00 00:00:38.000000\n+00 00:00:43.000000\n+00 00:00:46.000000\n+00 00:00:51.000000\n+00 00:00:52.000000<\/pre>\n
          As we have a lot of small jobs (short-lived jobs), it will be more interesting to use lightweight jobs instead of regular jobs.<\/p>\n
          In contrary of regular jobs, lightweight jobs :<\/p>\n
            \n
          • Require less meta data, so they have quicker create and drop times.<\/li>\n
          • Suited for short-lived jobs (small jobs, jobs where run_duration is low).<\/li>\n<\/ul>\n
            Let’s rewrite our PL\/SQL process using lightweight jobs :<\/p>\n
            To use lightweight jobs, first create a program suitable for a lightweight job :<\/p>\n
            begin\ndbms_scheduler.create_program\n(\n    program_name=>'LIGHTWEIGHT_PROGRAM',\n    program_action=>'LOAD_AMQ',\n    program_type=>'STORED_PROCEDURE',\n    number_of_arguments=>6, \n    enabled=>FALSE);\nEND;<\/pre>\n
            Add the arguments (parameters) and enable the program :<\/p>\n
            BEGIN\ndbms_scheduler.DEFINE_PROGRAM_ARGUMENT(\nprogram_name=>'lightweight_program',\nargument_position=>1,\nargument_type=>'VARCHAR2',\nDEFAULT_VALUE=>NULL);\n\ndbms_scheduler.DEFINE_PROGRAM_ARGUMENT(\nprogram_name=>'lightweight_program',\nargument_position=>2,\nargument_type=>'VARCHAR2');\n\ndbms_scheduler.DEFINE_PROGRAM_ARGUMENT(\nprogram_name=>'lightweight_program',\nargument_position=>3,\nargument_type=>'NUMBER');\n\ndbms_scheduler.DEFINE_PROGRAM_ARGUMENT(\nprogram_name=>'lightweight_program',\nargument_position=>4,\nargument_type=>'NUMBER');\n\ndbms_scheduler.DEFINE_PROGRAM_ARGUMENT(\nprogram_name=>'lightweight_program',\nargument_position=>5,\nargument_type=>'VARCHAR');\n\ndbms_scheduler.DEFINE_PROGRAM_ARGUMENT(\nprogram_name=>'lightweight_program',\nargument_position=>6,\nargument_type=>'NUMBER');\n\ndbms_scheduler.enable('lightweight_program');  \nend;<\/pre>\n
            Into the PL\/SQL code, let’s create the lightweight job without forget to set the argument value before running the job:<\/p>\n
            DECLARE\n...\nBEGIN\n....\nLOOP\nDBMS_SCHEDULER.create_job (\njob_name        => 'AMQ_P'||rec.priority||'j'||i||'_'||l_date,\nprogram_name    => 'LIGHTWEIGHT_PROGRAM',\njob_style       => 'LIGHTWEIGHT',\nenabled         => FALSE);\n                  \n DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (\n   job_name                => 'AMQ_P'||rec.priority||'j'||i||'_'||l_date,\n   argument_position       => 1,\n   argument_value          => rec.scope);\n   \nDBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (\n   job_name                => 'AMQ_P'||rec.priority||'j'||i||'_'||l_date,\n   argument_position       => 2,\n   argument_value          => l_amq_name);\n   \nDBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (\n   job_name                => 'AMQ_P'||rec.priority||'j'||i||'_'||l_date,\n   argument_position       => 3,\n   argument_value          => rec.priority);\n\nDBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (\n   job_name                => 'AMQ_P'||rec.priority||'j'||i||'_'||l_date,\n   argument_position       => 4,\n   argument_value          => l_date);   \n\nDBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (\n   job_name                => 'AMQ_P'||rec.priority||'j'||i||'_'||l_date,\n   argument_position       => 5,\n   argument_value          => v_SESSION_ID);  \n\nDBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (\n   job_name                => 'AMQ_P'||rec.priority||'j'||i||'_'||l_date,\n   argument_position       => 6,\n   argument_value          => i); \n\ndbms_scheduler.run_job('AMQ_P'||rec.priority||'j'||i||'_'||l_date,TRUE);\n...\nEND LOOP;\nNumber of Rows Read : 148 (Means 148 Sql Statement to execute) \nSTART : 18:08:56\nEND : 18:27:40\nTotal : 18.84 mins<\/pre>\n
             <\/p>\n
            Let’s check we have always 148 jobs in parallel :<\/p>\n
            SQL> select count(*) from all_scheduler_job_run_details where job_name like '%20200524175036';\n\n  COUNT(*)\n----------\n       148\nSQL> select log_date,job_name,status,req_start_date from all_scheduler_job_run_details where job_name like '%20200524175036';\n\nLOG_DATE           JOB_NAME     STATUS\t        REQ_START_DATE\n24-MAY-20 05.50.51 AB1C\t\tSUCCEEDED\t24-MAY-20 05.50.36\n24-MAY-20 05.50.56 AB1D\t\tSUCCEEDED\t24-MAY-20 05.50.51\n24-MAY-20 05.51.14 AB1E\t\tSUCCEEDED\t24-MAY-20 05.50.56\n24-MAY-20 05.51.49 AB1I\t\tSUCCEEDED\t24-MAY-20 05.51.14\n24-MAY-20 05.52.14 AB1P\t\tSUCCEEDED\t24-MAY-20 05.51.49\n24-MAY-20 05.52.34 AB1L\t\tSUCCEEDED\t24-MAY-20 05.52.14\n24-MAY-20 05.52.55 AB1N\t\tSUCCEEDED\t24-MAY-20 05.52.34\n24-MAY-20 05.53.17 AB1M\t\tSUCCEEDED\t24-MAY-20 05.52.55\n24-MAY-20 05.53.29 AB1K\t\tSUCCEEDED\t24-MAY-20 05.53.17\n24-MAY-20 05.53.39 AB1O\t\tSUCCEEDED\t24-MAY-20 05.53.29\n24-MAY-20 05.53.57 AB1U\t\tSUCCEEDED\t24-MAY-20 05.53.39\n24-MAY-20 05.54.07 AB1V\t\tSUCCEEDED\t24-MAY-20 05.53.57<\/pre>\n
            To resume :<\/p>\n
              \n
            • We have 148 jobs all started, most of the time in parallel.<\/li>\n
            • My PL\/SQL process now took 18.54 mins (Lightweight Jobs) instead of 27.57mins (Regular Jobs).<\/li>\n
            • If we compare Regular Jobs VS Lightweight Jobs, the former seems to schedule the jobs randomly (start jobs with block of 4,5,6…8) while the last one schedule jobs by block of 3 or 4 (as we can see above).<\/li>\n<\/ul>\n
              Conclusion :<\/p>\n
                \n
              • DBMS_SCHEDULER (Regular Jobs or Lightweight Jobs) can improve significantly your PL\/SQL performance transforming transforming your serial process in parallel process.<\/li>\n
              • If you have small jobs (short lived-jobs), use lightweight jobs instead regular jobs.<\/li>\n
              • Don’t underestimate the development time (development, test, bug solving) to transform your serial process to parallel process. Create 1 job is different to create more than 100 or 1000 jobs through a PL\/SQL loop (concurrency problem, CPU used by create\/drop the jobs).<\/li>\n
              • As developer, you are responsible to manage your jobs (create,drop,purge) in order to not fill the oracle parameter job_queue_processes (used by a lot of critical oracle processes).<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
                From an application point of view, the oracle scheduler DBMS_SCHEDULER allows to reach best performance by parallelizing your process. Let’s start with the following PL\/SQL code inserting in serial several rows from a metadata table to a target table. In my example, the metadata table does not contain “directly” the data but a set a […]<\/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,368,59],"tags":[281,96,644],"type_dbi":[],"class_list":["post-14187","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-development-performance","category-oracle","tag-dbms_scheduler","tag-oracle","tag-performance-tuning"],"acf":[],"yoast_head":"\nHow to use DBMS_SCHEDULER to improve performance ? - 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\/how-to-use-dbms_scheduler-to-improve-performance\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to use DBMS_SCHEDULER to improve performance ?\" \/>\n<meta property=\"og:description\" content=\"From an application point of view, the oracle scheduler DBMS_SCHEDULER allows to reach best performance by parallelizing your process. Let’s start with the following PL\/SQL code inserting in serial several rows from a metadata table to a target table. In my example, the metadata table does not contain “directly” the data but a set a […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/how-to-use-dbms_scheduler-to-improve-performance\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-05-24T21:28:23+00:00\" \/>\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=\"10 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\/how-to-use-dbms_scheduler-to-improve-performance\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-use-dbms_scheduler-to-improve-performance\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"How to use DBMS_SCHEDULER to improve performance ?\",\"datePublished\":\"2020-05-24T21:28:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-use-dbms_scheduler-to-improve-performance\/\"},\"wordCount\":761,\"commentCount\":0,\"keywords\":[\"dbms_scheduler\",\"Oracle\",\"Performance Tuning\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Development & Performance\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/how-to-use-dbms_scheduler-to-improve-performance\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-use-dbms_scheduler-to-improve-performance\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/how-to-use-dbms_scheduler-to-improve-performance\/\",\"name\":\"How to use DBMS_SCHEDULER to improve performance ? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2020-05-24T21:28:23+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-use-dbms_scheduler-to-improve-performance\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/how-to-use-dbms_scheduler-to-improve-performance\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-use-dbms_scheduler-to-improve-performance\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to use DBMS_SCHEDULER to improve performance ?\"}]},{\"@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":"How to use DBMS_SCHEDULER to improve performance ? - 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\/how-to-use-dbms_scheduler-to-improve-performance\/","og_locale":"en_US","og_type":"article","og_title":"How to use DBMS_SCHEDULER to improve performance ?","og_description":"From an application point of view, the oracle scheduler DBMS_SCHEDULER allows to reach best performance by parallelizing your process. Let’s start with the following PL\/SQL code inserting in serial several rows from a metadata table to a target table. In my example, the metadata table does not contain “directly” the data but a set a […]","og_url":"https:\/\/www.dbi-services.com\/blog\/how-to-use-dbms_scheduler-to-improve-performance\/","og_site_name":"dbi Blog","article_published_time":"2020-05-24T21:28:23+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"10 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/how-to-use-dbms_scheduler-to-improve-performance\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-to-use-dbms_scheduler-to-improve-performance\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"How to use DBMS_SCHEDULER to improve performance ?","datePublished":"2020-05-24T21:28:23+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-to-use-dbms_scheduler-to-improve-performance\/"},"wordCount":761,"commentCount":0,"keywords":["dbms_scheduler","Oracle","Performance Tuning"],"articleSection":["Database Administration & Monitoring","Development & Performance","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/how-to-use-dbms_scheduler-to-improve-performance\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/how-to-use-dbms_scheduler-to-improve-performance\/","url":"https:\/\/www.dbi-services.com\/blog\/how-to-use-dbms_scheduler-to-improve-performance\/","name":"How to use DBMS_SCHEDULER to improve performance ? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-05-24T21:28:23+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-to-use-dbms_scheduler-to-improve-performance\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/how-to-use-dbms_scheduler-to-improve-performance\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/how-to-use-dbms_scheduler-to-improve-performance\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to use DBMS_SCHEDULER to improve performance ?"}]},{"@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\/14187","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=14187"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14187\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=14187"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=14187"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=14187"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=14187"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}