PythonでOracleにSQLを発行しているが、1コアしか使っていない模様…お手軽にSQLをマルチコアで並列処理させて、処理速度を向上できる方法はないものかと調査してみた。

結果的に半日ぐらいかけて発見＆実装に成功したので共有したいと思う。

実現手法としては以下の通りです。
①Oracle 19C Enterprise EditionではSQLにヒントを入れると可能
　　→今回はStandard Editionを使っているので使用不可
②Oracle 19C Standard EditionではPL/SQLにて実現可能であることを発見
③Pythonから無名PL/SQLを発行して並列処理に成功

※尚、単語などに誤りがあればご指摘いただければ嬉しい所存です。

ミッション的なもの

使っている言語はPython3、DBはOracle 19C Standard Edition。

INSERT~SELECT文でデータを登録しているシンプルな処理。
SQLは「テーブルB」のある条件に合致するデータを「テーブルA」に登録するだけ。

カーソルでぐるぐるループする様な難しい処理は無し。
GROUP BYやORDER BYなども使っていない。

約1億件のレコードを処理しようとして5時間ぐらいかかっている。
が、システムの都合上1時間以下に抑えたいところである。
尚、インデックスなどは既に見直し済み。

…将来の重要案件に繋がるかもしれないミッションなのでちょっと胃が痛い。

まず以下のサイトを参考に…失敗

参考サイト：
https://oreno-it.info/archives/560
やり方は簡単、SQLに「parallel(4)」といったヒントをぶち込むだけ。

上記サイトを参考に以下のようなSQLにヒントを入れて作成
※実際に職場で作成したSQLとは違います。文法も適当です。

INSERT /*+ ENABLE_PARALLEL_DML PARALLEL(5) */ INTO SAMPLE_TABLE_A
SELECT /*+ PARALLEL(5) */ 
    ID,
    NAME,
    TIME
FROM
    SAMPLE_TABLE_B
WHERE
   ID >= 10000 AND ID < 20000;

実行した結果…

見事に並列処理できませんでした！失敗！残念！！
おかしい…他のサイトなどを参考にしても似たような記事が出てくるのに…ﾅｽﾞｪﾀﾞ!!(0w0)

諦めずにググってみたら以下の記事に辿り着いた。
https://qiita.com/sugimount/items/5978b43ff98beaae38f3

記事の冒頭に注目。

Oracle Database Enterprise Edition では、CPU を効率的に利用するためのパラレルクエリー機能があります。Standard Edition では、1個の SQL クエリーを 1個の CPU で処理をします。他の CPU はそのSQL クエリーに関わる仕事はしていません。

思いっきりStandard Editionではできないって書かれているやないかぁ…

もう無理か？無理なのか？ミッション失敗か？俺は飛ぶしかないのか？
少ない貯金でKLPの辺境に飛んでFIREするしかないのか？？

しかしそこに神(サイト)が現れた。
https://kenken0807.hatenablog.com/entry/2015/07/16/181340

PL/SQLを使えば実現可能

Pythonで簡単なSQL文(INSERT~SELECT)を発行するお手軽プログラムを…が目標なのにPL/SQL使用とか全然お手軽じゃないじゃん！！

というツッコミは少々お待ちください。

内容を見ているとSQLの前後に定型文的な呪文を書けば実現できるようだ。
尚、紹介したサイトはUPDATE文だが他のサイトもぐぐったところINSERTやSELECTでも可能みたいだ。

試しに作ってみたINSERT~SELECT文を無名PL/SQLブロックに埋め込んでSQL Developerにて動かしてみる。
注意するポイントはWHERE区の最後に以下の条件式をANDで追加すること
「rowid BETWEEN :start_id AND :end_id」
※職場で実際に動かしたSQLとは違うので、下記SQLをコピペしても正常動作しないかもしれません。

DECLARE
    sql_hensu VARCHAR2(100);    --SQLを入れる変数
BEGIN
    --呪文みたいなもの。タスクと言うのをCREATEする。
    DBMS_PARALLEL_EXECUTE.CREATE_TASK ('sample_mytask');
    --呪文みたいなもの「SAMPLE_USER」はユーザ名、「SAMPLE_TABLE_B」は並列化したいテーブル名（今回は取得元のテーブルを分割して並列処理したい所存）
    DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('sample_task', 'SAMPLE_USER', 'SAMPLE_TABLE_B', true, 100000);

    --SQL文を変数に突っ込む
    sql_hensu := 'INSERT INTO SAMPLE_TABLE_A SELECT ID, NAME, TIME FROM SAMPLE_TABLE_B' WHERE ID >= 10000 AND ID < 20000 AND rowid BETWEEN :start_id AND :end_id';

    --以下の命令で処理が開始！
    DBMS_PARALLEL_EXECUTE.RUN_TASK('sample_task', sql_hensu, DBMS_SQL.NATIVE,parallel_level => 5);
    --処理完了。タスクをDROPする。
    DBMS_PARALLEL_EXECUTE.DROP_TASK('sample_task');
END;

動いたあああああ！しかもマルチコアでキレイに動いてくれたよ！！

ちなみにコードにある「parallel_level => 5」の「5」を上げるとCPUをゴリゴリ使ってくれる。処理が早くなる。
あんまり上げすぎるとCPU100%近くになるので注意。

PythonからPL/SQLを実行

さて、問題はどのようにして先程のPL/SQLをPythonで実行させるかだ。

個人的にしかもシンプルなINSERT~SELECT文を、わざわざプロシージャをCREATEして登録してPythonから呼び出すのはめんどくさいのでやりたくない。

管理もめんどくさくなるし。

と言うわけで無名PL/SQLブロックとしてPythonから呼び出すことにした。

無名PL/SQLブロックの呼び出し方は以下のcx_Oracleのドキュメントサイト(英語)を翻訳して作成
https://cx-oracle.readthedocs.io/en/latest/user_guide/plsql_execution.html

以下の方法であっさり動きました

※カーソルまでの作成は省略
cursor.execute("""
※先程のPL/SQL(DECLARE ~(中略)~ END;)をコピペ
""")

タスクマネージャー見たら全部のCPUゴリゴリ使ってる！やったね！！
ちなみに性能は10分の1ぐらいまで跳ね上がったよ！

元々のミッションが5時間→1時間だったから大幅に性能向上だぜ！

注意事項的なもの

SQL構文が間違っていた場合などは一瞬で処理が終わります。
テーブルも登録・更新されていないので要確認です。

リターンコードなどの説明がOracle公式に書かれているものの、いまいち分かりづらいので、一瞬で処理が終わった場合は以下を疑えば大抵解決します。
・ユーザー名が間違っている
・対象テーブルが間違っている
・SQL構文に誤りがある

尚、Pythonの中にPL/SQL構文入れる(言語の中に別の言語をぶち込む)ってどうなの…
と思ったが、SQL以外の箇所はほぼ定型文的な呪文なので問題ないと判断した。

他の処理でもSQLを書き換えるだけでいけるはずだしね！たぶん。

ちなみにGROUP BYなどを使っていると上手く動かないといった文章を見かけたがそこは試してない。
もし今後試す機会があれば共有したいと思う。

終わりに

今回はOracleのPL/SQLを使って並列処理に成功しました。
しかし、Pythonの機能やロジックの見直しでもっと簡単に並列処理ができるかもしれません。

その辺りについても、今後は調べていきたいと思います。

※ちなみに公式サイトもめっちゃ参考にしましたが、Oracleさん公式の説明はイマイチこの記事では説明しづらいので、分かりやすくかみ砕いて例文まで書いてくれている個人サイトを引用させてもらいました。

以上、ここまで読んでいただきありがとうございました。

Pythonで作成したSQLをOracleに投げてマルチコアで並列処理させた。