１年ぶりのご無沙汰でございます
かえるのクーの助手の「井戸中 聖」（いとなか セイ）でございます。わからないことを調べて実験しながら記録するのが、このブログのスタイルでしたが、AIでやる前にほとんど分かってしまうので書くことがありませんでした。壁打ちを１０～２０回もすれば、できるか、できなかぐらいは大体みえてきます。久しぶりに「やってみないと分からない」ことがでてきたので、書こうと思います。ここしばらくのことをまとめて書くので、長い＆ほぼ考察なしの私的メモになると思います。ご容赦ください。
ブログのベースひな型もどっかいったので、書き方も試行錯誤かな。
お題：大きなデータを比較したい
花蓮さんにチケット発行して調べてもらってもよかったのですが、今回は自分でがんばってみます。
「Oracleのでかいデータ(およそ10億～100億件くらい）を別のデータウェアハウス（DWH)に連携してその連携したあと、データの完全性が保たれているかを調べたい。」のです。DWHはもっとでかい！そしてたまに連携に失敗するので、完全性が損なわれていないか確認したい！！
通常の比較方法では遅いのは目に見えているので、試行錯誤で調べます。（今回はもう試行錯誤しちゃいました）
おおきいデータの比較の仕方（方針）
・とにかく連携後、完全であるか、そうでないかをチェックしたい。
・プライマリーキーと更新日付スタンプでHashをとり、それをチェックサムする。
・追加、削除はチェックサムでわかるし、更新したら必ず更新タイムスタンプ項目がかわるので、行データはハッシュにして、列方向はそのハッシュのチェックサムのみで比較する。
・とにかくデータが大きいので速度命とする。ハッシュのみよりかなり衝突可能性は上がるが、実用的許容範囲でとにかく高速に処理したい。（目標は１億件で1分以内のチェックサム生成）
試行錯誤の結果（まとめ）
本ブログはこの試行錯誤のバタバタ感が醍醐味なのですが、今回は結果のみ記載し、それ以外は環境などの私的記録となります。あしからず。
経過のあらまし
・OracleのDBMS_CRYPTOをSQLからPLSQL経由で使うとかなり遅い。PLSQLにハッシュ⇒チェックサム用値変換ファンクションを作ってみたが、億単位では使い物にならないくらい遅かった。
・OracleのSQLだけ高速に実行できるものを模索。
・汎用性はなくてもよく、今回の比較だけで使えるものでよい。ただし他のDBやDWHでも（当たり前だが）順序や多重度とは関係なしに計算できることができて、もちろん同じチェックサム値になる必要がある。
だいたいの方針
・PK（プライマリキー）は文字列化、更新タイムスタンプ項目はUTC文字列（ミリ秒まで保持）に変換し、結合する。この文字列をハッシュの入力文字列とする。
・ハッシュはOracleSQLで扱える SHA256にする。(MD5でもいいけどMD5非推奨の流れにのって少し重いがSHA256を使うことにする）
・ハッシュは一部だけを使用して32Bit以下程度の整数になるようにする
・上のように行単位で得られた整数値を単純に加算することでチェックサムとする。（チェックサムなので、データ順序は関係しない。並列高速化が期待できる）
・チェックサム値は折り返さず(畳まず)、単に足すだけとする。チェックサム値の総合計はINT128を超えないようにする。計算では32Bitの乱数を１兆個足しても高々72Bit程度なので、Oracleの限界38桁 (127Bit程度)は余裕でいける。
・チェックサムなので、衝突可能性はかなり上がるが、同一性チェック用途には十分と判断した。
結果
１テーブル1億件のデータ（ただしテーブル項目数は少ない）の２項目結合チェックサムが30秒以内でできたので、期待した1分以内をクリアできた。
10億件のデータでの実測、項目数が多い場合の性能については引き続き評価したい。
Oracle側はめどがついた。さて、100億とか1000億オーダのDWHでの性能はどうなのだろう。（自分でしらべられるかもわからない）
ところで
他の研究・実験は袋小路にはいって瞑想・迷走中です。
記録
延々記述中。。。いまどき全部AIが教えてくれますが、現時点のログとして残します。
Oracle 21cのWSL + Dockerでの導入
Docker Desktopをつかわない方法のインストール手順の羅列
WSLの設定をしてUbuntuをいれます。
起動したUbuntuを最新化してごにょごにょおまじないをします。
$ sudo apt install ca-certificates curl gnupg lsb-release -y
$ sudo mkdir -p /etc/apt/keyrings
$ curl -fsSL https://download.docker.com/linux/ubuntu/gpg | \
sudo gpg --dearmor -o /etc/apt/keyrings/docker.gpg
"deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/docker.gpg] \
https://download.docker.com/linux/ubuntu \
$(lsb_release -cs) stable" | \
sudo tee /etc/apt/sources.list.d/docker.list > /dev/null
$ sudo apt install docker-ce docker-ce-cli containerd.io docker-compose-plugin -y
$ sudo systemctl enable docker
$ sudo systemctl start docker
$ sudo docker run hello-world
$ sudo docker images
$ sudo docker ps -a
$ sudo docker rmi <IMAGE ID> # hello-world イメージ削除
Dockerをいれます。（Docker Desktopは商用有償なのでいれません）
Dockerが動くことを確認しました。hello-worldは消しておきます。
Oracle 21c の設定
https://container-registry.oracle.com/ でOCRへログインしてブラウザアカウント情報のAuthTokenでSecret Keyを取得します。
Password: xxxx
~~~~~~~
Login Succeeded
ブラウザで OCRのDatabaseのenterpriseをaccepstします。
$ vi docker-compose.yml
----------------------------------------
services:
oracle21xe:
image: container-registry.oracle.com/database/express:21.3.0-xe
container_name: oracle21xe
ports:
- "1521:1521"
- "5500:5500"
environment:
ORACLE_PWD: "Passw0rd!"
volumes:
- oradata_xe21:/opt/oracle/oradata
shm_size: "1g"
restart: unless-stopped
volumes:
oradata_xe21:
----------------------------------------
$ sudo docker compose up -d
$ sudo docker logs -f oracle21xe
$ sudo docker ps
$ sudo docker exec -it oracle21xe bash
DockerでOracle21c XE を指定して起動します。あとでXEでなくて有償版にするかも。
やっぱりパラレルクエリの実験もしたいのでEnterprise版にしてみます。
----------------------------------------
services:
oraDB21:
image: container-registry.oracle.com/database/enterprise:21.3.0.0
container_name: oraDB21
ports:
- "1521:1521" # SQL*Net
- "5500:5500" # EM Express (https)
environment:
ORACLE_SID: "ORCLCDB" # 省略可（既定 ORCLCDB）
ORACLE_PDB: "ORCLPDB1" # 省略可（既定 ORCLPDB1）
ORACLE_PWD: "Passw0rd!" # 管理者(SYS/SYSTEM/PDBADMIN)の初期PW
ORACLE_CHARACTERSET: "AL32UTF8" # 省略可（既定 AL32UTF8）
# 新規作成時にアーカイブログを有効化したい場合のみ true
# 既存DBではこのフラグは効きません（新規作成時のみ）
ENABLE_ARCHIVELOG: "false"
# 必要なら初期SGA/PGAを固定
# INIT_SGA_SIZE: "2048"
# INIT_PGA_SIZE: "1024"
volumes:
# ★ 永続化：データファイルをホストに保持（oracleユーザ: uid 54321 で書込み可能であること）
- oradata21ee:/opt/oracle/oradata
# 任意：初回セットアップや起動時に走らせるスクリプトを置く（*.sh, *.sql）
# - ./startup-scripts:/opt/oracle/scripts/startup
# - ./setup-scripts:/opt/oracle/scripts/setup
shm_size: "64g" # /dev/shm を十分に確保（推奨）
restart: unless-stopped
volumes:
oradata21ee:
----------------------------------------
$ sudo docker compose up -d # たくさんpullする
$ sudo docker logs -f oraDB21
$ sudo docker ps # healthyになるまで待つ 最初かなりかかる
$ sudo docker exec -it oraDB21 bash
$ sqlplus sys/Passw0rd\!@//localhost:1521/ORCLPDB1 as sysdba
SQL>
sqlplusが起動できて、select * from user_tables;が動けば、たぶんOK
あと、https://localhost:5500/em で EnterpriseManagerにつなげられるか確認
sys/Passw0rd!/ORCLPDB1
昔はあんなに苦労したのにもう動いちゃった。よい時代になったものです。
テスト用データの作成
テスト用のユーザを作成してテストデータをつくる
-- SYS または SYSTEM ユーザーで実行
-- ① ユーザー UXE を作成（デフォルト表領域 USERS、テンポラリ表領域 TEMP）
CREATE USER uxe IDENTIFIED BY uxe DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users;
-- ② DBA 権限を付与
GRANT DBA TO uxe;
-- ③ （任意）接続権限を個別に付与（Oracle 21cではCONNECTはロール化済み）
GRANT CREATE SESSION TO uxe;
-- ④ 確認（実行ユーザーがSYSの場合）
SELECT username, default_tablespace, account_status FROM dba_users WHERE username = 'UXE';
性能をあげるため、OS側の.wsconfigでメモリ設定を変更する（マシンにあわせる128GB積んだマシンなので、半分をWSLに割り当てる感じ）
[wsl2]
memory=96GB # メモリ上限（例：8GB）
swap=128GB # スワップファイルサイズ
localhostForwarding=true # localhost共有（既定はtrue）
nestedVirtualization=true # 仮想化のネスト
---------------------
設定をメモリたくさん使うようにてきとーに変えてDB再起動
チェックサムSQL
説明
実測
実測結果
