More than 1 year has passed since last update.

日本オラクル株式会社

Autonomous Database：DBMS_SCHEDULERで実行するジョブ内のSQLをパラレル実行させてみた

Last updated at 2023-07-09Posted at 2023-07-09

はじめに

Autonomous Databaseにおいて、DBMS_SCHEDULERで実行するジョブ内のSQLの動作を確認して、パラレル実行させてみました。

※こちらの内容は2023/7月現在の検証内容です。実環境で実装する際は、充分な検証を実施し、自己責任でお願いします。

1.事前準備

MEDIUMサービスを使用してAutonomous Databaseに接続します。
こちらのAutonomous DatabaseのOCPU数は2に設定されています。

[opc@tools ~]$ sqlplus admin/xxxxxxxxxxx@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=p36dah33ehxmnrf_demoatp1_medium.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))

SQL*Plus: Release 19.0.0.0.0 - Production on 土 7月 8 16:43:33 2023
Version 19.16.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

最終正常ログイン時間: 土 7月  08 2023 11:36:33 +09:00


Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.1.0
に接続されました。
SQL>

v$sessionビューでセッションのリソース・コンシューマ・グループを確認します。

SQL> set linesize 120
SQL> col username format a10
SQL> col service_name format a55
SQL> col resource_consumer_group for a25
SQL> SELECT username, service_name, resource_consumer_group
  2  FROM v$session;

USERNAME   SERVICE_NAME 					                       RESOURCE_CONSUMER_GROUP
---------- ------------------------------------------------------- -------------------------
ADMIN	   P36DAH33EHXMNRF_DEMOATP1_medium.adb.oraclecloud.com	   MEDIUM

SQL>

MEDIUMサービスで接続していますので、リソース・コンシューマ・グループとしてMEDIUMが割り当てられています。

DBMS_SCHEDULERで実行されるジョブのセッションのリソース・コンシューマ・グループの情報を保存する表test_tableを作成します。

SQL> CREATE TABLE test_table (
  2      log_date                 DATE,
  3      username                 VARCHAR2(256),
  4      service_name             VARCHAR2(256),
  5      resource_consumer_group  VARCHAR2(256)
  6  );

表が作成されました。

SQL>

DBMS_SCHEDULERで実行するジョブ内でSQLを実行する際の統計情報を保存するための表test_plan_tableを作成します。

SQL> CREATE TABLE test_plan_table (
  2      plan_output  VARCHAR2(4096)
  3  );

表が作成されました。

SQL>

2. DBMS_SCHEDULERでジョブとして実行するPL/SQLプロシージャの作成

DBMS_SCHEDULERでジョブとして実行するPL/SQLプロシージャtest_procを作成します。
処理の内容は、
・v$sessionビューから取得したセッション情報をtest_tableにINSERT
・SELECT文の実行計画を取得してtest_plan_tableにINSERT
となります。

SQL> CREATE OR REPLACE PROCEDURE test_proc
  2  IS
  3  BEGIN
  4      EXECUTE IMMEDIATE 'INSERT INTO admin.test_table SELECT sysdate ,username, service_name, resource_consumer_group FROM v$session';
  5      EXECUTE IMMEDIATE 'EXPLAIN PLAN FOR SELECT * FROM ssb.lineorder';
  6      EXECUTE IMMEDIATE 'INSERT INTO admin.test_plan_table SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => ''ALL''))';
  7      COMMIT;
  8  END;
  9  /

プロシージャが作成されました。

SQL>

3. DBMS_SCHEDULERジョブの作成（ジョブ・クラス指定なし）

プロシージャtest_procを定期的に実行するDBMS_SCHEDULERジョブtestjob1を作成します。

SQL> BEGIN
  2  	DBMS_SCHEDULER.CREATE_JOB (
  3   	    job_name           =>  'TESTJOB1',
  4   	    job_type           =>  'STORED_PROCEDURE',
  5   	    job_action         =>  'ADMIN.TEST_PROC', 
  6   	    start_date         =>  TO_TIMESTAMP_TZ('2023-07-08 17:05:00 +9:00','YYYY-MM-DD HH24:MI:SS TZH:TZM'),
  7   	    repeat_interval    =>  'FREQ=DAILY;INTERVAL=1',
  8   	    auto_drop          =>  FALSE,
  9   	    enabled            =>  TRUE
 10   	);
 11  END;
 12  /

PL/SQLプロシージャが正常に完了しました。

SQL>

SQL*Plusを終了します。

SQL> exit
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.1.0との接続が切断されました。
[opc@tools ~]$

作成したジョブtestjob1の実行時刻が過ぎたら、再度SQL*PlusからAutonomous Databaseに接続します。

[opc@tools ~]$ sqlplus admin/xxxxxxxxxxx@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=p36dah33ehxmnrf_demoatp1_medium.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))

SQL*Plus: Release 19.0.0.0.0 - Production on 土 7月 8 17:05:26 2023
Version 19.16.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

最終正常ログイン時間: 土 7月  08 2023 16:43:36 +09:00


Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.1.0
に接続されました。
SQL>

表test_tableの内容を確認します。

SQL> col username for a10
SQL> col service_name for a55
SQL> col resource_consumer_group for a25
SQL> SELECT * FROM test_table;

LOG_DATE	        USERNAME   SERVICE_NAME					                           RESOURCE_CONSUMER_GROUP
------------------- ---------- ------------------------------------------------------- -------------------------
2023/07/08 08:05:00 ADMIN      SYS$USERS					                           OTHER_GROUPS

SQL>

ジョブtestjob1を実行した際のリソース・コンシューマ・グループがOTHER_GROUPSになっていることがわかりました。

表test_plan_tableの内容からSELECT文の実行計画を確認してみます。

SQL> set pagesize 100
SQL> SELECT * FROM test_plan_table;

PLAN_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4017770458

---------------------------------------------------------------------------------------
| Id  | Operation		          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	      |	          |  5999M|   564G|  5825K	(2)| 00:03:48 |
|   1 |  TABLE ACCESS STORAGE FULL| LINEORDER |  5999M|   564G|  5825K	(2)| 00:03:48 |
---------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / LINEORDER@SEL$1

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (rowset=256) "LINEORDER"."LO_ORDERKEY"[NUMBER,22],
       "LINEORDER"."LO_LINENUMBER"[NUMBER,22], "LINEORDER"."LO_CUSTKEY"[NUMBER,22],
       "LINEORDER"."LO_PARTKEY"[NUMBER,22], "LINEORDER"."LO_SUPPKEY"[NUMBER,22],
       "LINEORDER"."LO_ORDERDATE"[DATE,7], "LINEORDER"."LO_ORDERPRIORITY"[CHARACTER,15
       ], "LINEORDER"."LO_SHIPPRIORITY"[CHARACTER,1],
       "LINEORDER"."LO_QUANTITY"[NUMBER,22], "LINEORDER"."LO_EXTENDEDPRICE"[NUMBER,22]
       , "LINEORDER"."LO_ORDTOTALPRICE"[NUMBER,22],
       "LINEORDER"."LO_DISCOUNT"[NUMBER,22], "LINEORDER"."LO_REVENUE"[NUMBER,22],
       "LINEORDER"."LO_SUPPLYCOST"[NUMBER,22], "LINEORDER"."LO_TAX"[NUMBER,22],
       "LINEORDER"."LO_COMMITDATE"[NUMBER,22], "LINEORDER"."LO_SHIPMODE"[CHARACTER,10]

27行が選択されました。

SQL>

SQL文の実行計画がシリアル実行になっていることが確認できました。

user_scheduler_jobsビューで、ジョブtestjob1のジョブ・クラスを確認します。

SQL> col job_name for a20
SQL> col job_class for a20
SQL> SELECT job_name, job_class
  2  FROM user_scheduler_jobs
  3  WHERE job_name = 'TESTJOB1';

JOB_NAME	         JOB_CLASS
-------------------- --------------------
TESTJOB1	         DEFAULT_JOB_CLASS

SQL>

作成時にジョブ・クラスを指定しなかったので、ジョブtestjob1のジョブ・クラスはDEFAULT_JOB_CLASSが設定されています。

dba_scheduler_job_classesビューで、各ジョブ・クラスに割り当てられているリソース・コンシューマ・グループを確認してみます。

SQL> col job_class_name for a30
SQL> col resource_consumer_group for a30
SQL> SELECT job_class_name, resource_consumer_group
  2  FROM dba_scheduler_job_classes;

JOB_CLASS_NAME		           RESOURCE_CONSUMER_GROUP
------------------------------ ------------------------------
DEFAULT_JOB_CLASS
SCHED$_LOG_ON_ERRORS_CLASS
DEFAULT_IN_MEMORY_JOB_CLASS
DBMS_JOB$
ORA$AT_JCURG_OS 	           ORA$AUTOTASK
ORA$AT_JCNRM_OS 	           ORA$AUTOTASK
ORA$AT_JCMED_OS 	           ORA$AUTOTASK
ORA$AT_JCURG_SA 	           ORA$AUTOTASK
ORA$AT_JCNRM_SA 	           ORA$AUTOTASK
ORA$AT_JCMED_SA 	           ORA$AUTOTASK
ORA$AT_JCURG_SQ 	           ORA$AUTOTASK
ORA$AT_JCNRM_SQ 	           ORA$AUTOTASK
ORA$AT_JCMED_SQ 	           ORA$AUTOTASK
AQ$_PROPAGATION_JOB_CLASS
ORA$AUTOTASK_JOB_CLASS	       ORA$AUTOTASK
TPURGENT		               TPURGENT
TP			                   TP
HIGH			               HIGH
MEDIUM			               MEDIUM
LOW			                   LOW
ADP$STATS_JOB_CLASS	           LOW
ADP$DATALOAD_JOB_CLASS	       HIGH

22行が選択されました。

SQL>

ジョブ・クラスDEFAULT_JOB_CLASSにはリソース・コンシューマ・グループが割り当てられていないことがわかりました。
リソース・コンシューマ・グループが割り当てられていない場合は、OTHER_GROUPSが割り当てられます。

dba_rsrc_plan_directivesビューでリソース・コンシューマ・グループの設定を確認してみます。

SQL> col plan for a10
SQL> col name for a15
SQL> SELECT plan, group_or_subplan name, mgmt_p1, parallel_server_limit, parallel_degree_limit_p1
  2  FROM dba_rsrc_plan_directives
  3  WHERE plan = 'DWCS_PLAN' OR plan = 'OLTP_PLAN'
  4  ORDER BY 1,2,3 DESC;

PLAN	   NAME 	       MGMT_P1    PARALLEL_SERVER_LIMIT PARALLEL_DEGREE_LIMIT_P1
---------- --------------- ---------- --------------------- ------------------------
OLTP_PLAN  HIGH 		            4			         50			               2
OLTP_PLAN  LOW			            1						                       1
OLTP_PLAN  MEDIUM		            2			         84			               2
OLTP_PLAN  OTHER_GROUPS 	        1						                       1
OLTP_PLAN  TP			            8						                       1
OLTP_PLAN  TPURGENT		           12

6行が選択されました。

SQL>

リソース・コンシューマ・グループOTHER_GROUPSでは、PARALLEL_DEGREE_LIMIT_P1（最大並列度）が1に設定されているため、SQLのパラレル実行ができません。
これが、OCPU数が2以上でもDBMS_SCHEDULERで実行するジョブ内のSQLがシリアル実行になった理由です。

4. DBMS_SCHEDULERジョブの作成（ジョブ・クラス指定あり）

では、同じプロシージャtest_procを実行するDBMS_SCHEDULERジョブtestjob2を、ジョブ・クラス（job_cllass）にHIGHを指定して作成してみます。
※DBMS_SCHEDULER.SET_ATTRIBUTEプロシージャを使用して、既存のDBMS_SCHEDULERジョブのジョブ・クラスを変更することも可能です。

その前に、表test_tableと表test_plan_tableの内容を切り捨てます。

SQL> TRUNCATE TABLE test_table;
表が切り捨てられました。

SQL> TRUNCATE TABLE test_plan_table;

表が切り捨てられました。

SQL>

DBMS_SCHEDULERジョブtestjob2を作成します。

SQL> BEGIN
  2  	DBMS_SCHEDULER.CREATE_JOB (
  3   	    job_name           =>  'TESTJOB2',
  4   	    job_type           =>  'STORED_PROCEDURE',
  5   	    job_action         =>  'ADMIN.TEST_PROC', 
  6   	    start_date         =>  TO_TIMESTAMP_TZ('2023-07-08 17:15:00 +9:00','YYYY-MM-DD HH24:MI:SS TZH:TZM'),
  7   	    repeat_interval    =>  'FREQ=DAILY;INTERVAL=1',
  8   	    auto_drop          =>  FALSE,
  9   	    enabled            =>  TRUE,
 10         job_class          =>  'HIGH'
 11   	);
 12  END;
 13  /

PL/SQLプロシージャが正常に完了しました。

SQL>

SQL*Plusを終了します。

SQL> exit
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.1.0との接続が切断されました。
[opc@tools ~]$

作成したジョブtestjob2の実行時刻が過ぎたら、再度SQL*PlusからAutonomous Databaseに接続します。

[opc@tools ~]$ sqlplus admin/Demo#1Demo#1@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=p36dah33ehxmnrf_demoatp1_medium.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))

SQL*Plus: Release 19.0.0.0.0 - Production on 土 7月 8 17:15:26 2023
Version 19.16.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

最終正常ログイン時間: 土 7月  08 2023 17:05:26 +09:00


Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.1.0
に接続されました。
SQL>

表test_plan_tableの内容からSELECT文の実行計画を確認してみます。

SQL> set linesize 150
SQL> set pagesize 100
SQL> SELECT * FROM test_plan_table;

PLAN_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2924420454

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation		             | Name	     | Rows  | Bytes | Cost (%CPU)| Time	 |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	         |		     |  5999M|   564G|  3233K  (2)| 00:02:07 |	      |	     |	          |
|   1 |  PX COORDINATOR 	         |		     |	     |	     |	          | 	     |	      |	     |	          |
|   2 |   PX SEND QC (RANDOM)	     | :TQ10000  |  5999M|   564G|  3233K  (2)| 00:02:07 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR	     |		     |  5999M|   564G|  3233K  (2)| 00:02:07 |  Q1,00 | PCWC |	          |
|   4 |     TABLE ACCESS STORAGE FULL| LINEORDER |  5999M|   564G|  3233K  (2)| 00:02:07 |  Q1,00 | PCWP |	          |
-----------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   4 - SEL$1 / LINEORDER@SEL$1

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "LINEORDER"."LO_ORDERKEY"[NUMBER,22], "LINEORDER"."LO_LINENUMBER"[NUMBER,22],
       "LINEORDER"."LO_CUSTKEY"[NUMBER,22], "LINEORDER"."LO_PARTKEY"[NUMBER,22], "LINEORDER"."LO_SUPPKEY"[NUMBER,22],
       "LINEORDER"."LO_ORDERDATE"[DATE,7], "LINEORDER"."LO_ORDERPRIORITY"[CHARACTER,15],
       "LINEORDER"."LO_SHIPPRIORITY"[CHARACTER,1], "LINEORDER"."LO_QUANTITY"[NUMBER,22],
       "LINEORDER"."LO_EXTENDEDPRICE"[NUMBER,22], "LINEORDER"."LO_ORDTOTALPRICE"[NUMBER,22],
       "LINEORDER"."LO_DISCOUNT"[NUMBER,22], "LINEORDER"."LO_REVENUE"[NUMBER,22],
       "LINEORDER"."LO_SUPPLYCOST"[NUMBER,22], "LINEORDER"."LO_TAX"[NUMBER,22],
       "LINEORDER"."LO_COMMITDATE"[NUMBER,22], "LINEORDER"."LO_SHIPMODE"[CHARACTER,10]
   2 - (#keys=0) "LINEORDER"."LO_ORDERKEY"[NUMBER,22], "LINEORDER"."LO_LINENUMBER"[NUMBER,22],
       "LINEORDER"."LO_CUSTKEY"[NUMBER,22], "LINEORDER"."LO_PARTKEY"[NUMBER,22], "LINEORDER"."LO_SUPPKEY"[NUMBER,22],
       "LINEORDER"."LO_ORDERDATE"[DATE,7], "LINEORDER"."LO_ORDERPRIORITY"[CHARACTER,15],
       "LINEORDER"."LO_SHIPPRIORITY"[CHARACTER,1], "LINEORDER"."LO_QUANTITY"[NUMBER,22],
       "LINEORDER"."LO_EXTENDEDPRICE"[NUMBER,22], "LINEORDER"."LO_ORDTOTALPRICE"[NUMBER,22],
       "LINEORDER"."LO_DISCOUNT"[NUMBER,22], "LINEORDER"."LO_REVENUE"[NUMBER,22],
       "LINEORDER"."LO_SUPPLYCOST"[NUMBER,22], "LINEORDER"."LO_TAX"[NUMBER,22],
       "LINEORDER"."LO_COMMITDATE"[NUMBER,22], "LINEORDER"."LO_SHIPMODE"[CHARACTER,10]
   3 - (rowset=256) "LINEORDER"."LO_ORDERKEY"[NUMBER,22], "LINEORDER"."LO_LINENUMBER"[NUMBER,22],
       "LINEORDER"."LO_CUSTKEY"[NUMBER,22], "LINEORDER"."LO_PARTKEY"[NUMBER,22], "LINEORDER"."LO_SUPPKEY"[NUMBER,22],
       "LINEORDER"."LO_ORDERDATE"[DATE,7], "LINEORDER"."LO_ORDERPRIORITY"[CHARACTER,15],
       "LINEORDER"."LO_SHIPPRIORITY"[CHARACTER,1], "LINEORDER"."LO_QUANTITY"[NUMBER,22],
       "LINEORDER"."LO_EXTENDEDPRICE"[NUMBER,22], "LINEORDER"."LO_ORDTOTALPRICE"[NUMBER,22],
       "LINEORDER"."LO_DISCOUNT"[NUMBER,22], "LINEORDER"."LO_REVENUE"[NUMBER,22],
       "LINEORDER"."LO_SUPPLYCOST"[NUMBER,22], "LINEORDER"."LO_TAX"[NUMBER,22],
       "LINEORDER"."LO_COMMITDATE"[NUMBER,22], "LINEORDER"."LO_SHIPMODE"[CHARACTER,10]
   4 - (rowset=256) "LINEORDER"."LO_ORDERKEY"[NUMBER,22], "LINEORDER"."LO_LINENUMBER"[NUMBER,22],
       "LINEORDER"."LO_CUSTKEY"[NUMBER,22], "LINEORDER"."LO_PARTKEY"[NUMBER,22], "LINEORDER"."LO_SUPPKEY"[NUMBER,22],
       "LINEORDER"."LO_ORDERDATE"[DATE,7], "LINEORDER"."LO_ORDERPRIORITY"[CHARACTER,15],
       "LINEORDER"."LO_SHIPPRIORITY"[CHARACTER,1], "LINEORDER"."LO_QUANTITY"[NUMBER,22],
       "LINEORDER"."LO_EXTENDEDPRICE"[NUMBER,22], "LINEORDER"."LO_ORDTOTALPRICE"[NUMBER,22],
       "LINEORDER"."LO_DISCOUNT"[NUMBER,22], "LINEORDER"."LO_REVENUE"[NUMBER,22],
       "LINEORDER"."LO_SUPPLYCOST"[NUMBER,22], "LINEORDER"."LO_TAX"[NUMBER,22],
       "LINEORDER"."LO_COMMITDATE"[NUMBER,22], "LINEORDER"."LO_SHIPMODE"[CHARACTER,10]

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 4 because of degree limit

57行が選択されました。

SQL>

SQL文の実行計画がパラレル実行になっていることが確認できました。

まとめ

OCPUの設定値が2以上のAutonomous Databaseにおいて、DBMS_SCHEDULERで実行するジョブ内のSQLをパラレル実行させたい場合は、ジョブのジョブ・クラス（job_class）を適切に設定する必要があります。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up