OTN の VirtualBoxイメージ で Oracle DB 18c環境 を 楽々構築【Oracle Database or GoldenGate Advent Calendar 2018 Day 5】
- 1. VirtualBox をインストール
- 2. Database Virtual Box Appliance / Virtual Machine の ovaイメージをダウンロード
- 3. ovaイメージのインポート
- 4. 仮想マシンの起動
- 5. 仮想マシンへのアクセス(ssh + sqlplus)
- 6. 仮想マシン上のDBへのアクセス(SQL Developer)
- 7. 仮想マシンのAPEX ADMIN環境にホストマシンのWebブラウザからアクセス(Oracle APEX)
- 8. まとめ
このエントリは Oracle Database or GoldenGate Advent Calendar 2018 の Day 5 の記事となります。
Oracle Database or GoldenGate Advent Calendar 2018
https://adventar.org/calendars/3525
当初の予定から変更！ OTNからダウンロードできる VirtualBoxイメージ(ovaファイル)で
Oracle Database 18c環境をサクっと構築してみるやで彡(ﾟ)(ﾟ)
1. VirtualBox をインストール
まずは VirtualBox をダウンロードしてインストール、 既に導入済みの人は不要ですやで彡(ﾟ)(ﾟ)
Oracle VM VirtualBox
https://www.oracle.com/technetwork/server-storage/virtualbox/downloads/index.html?ssSourceSiteId=otnjp
インストールで詰まることは無いと思いますが、色々な記事も有ります。
VirtualBoxのインストール方法
https://eng-entrance.com/virtualbox-install
VirtualBox を Windows にインストール
https://www.shookuro.com/entry/2018/01/28/162252
2. Database Virtual Box Appliance / Virtual Machine の ovaイメージをダウンロード
下記ページから ovaイメージ を ダウンロードします。
7.7GB位あるので、ダウンロードしたら少し待ちます彡(ﾟ)(ﾟ)
Oracle Technology Network
Database Virtual Box Appliance / Virtual Machine
https://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html
スグに次の手順(インポート)してもエエんですが、ダウンロード失敗の可能性も加味して、
MD5のハッシュ値を確認してダウンロード元のと比較するのをオススメします。
下記はWindowsのコマンドプロンプトでのMD5ハッシュ値確認例彡(ﾟ)(ﾟ)
certutil -hashfile C:\Users\AYSHIBAT\Downloads\DeveloperDaysVM2018-10-16_09.ova MD5 MD5 ハッシュ (ファイル C:\Users\AYSHIBAT\Downloads\DeveloperDaysVM2018-10-16_09.ova): bf 2b 8e cd 1c c2 1f 0b de c6 39 73 f7 2c ea 83 CertUtil: -hashfile コマンドは正常に完了しました。
3. ovaイメージのインポート
ダウンロードしたovaイメージをインポートします。基本ポチポチするだけ彡(ﾟ)(ﾟ)
4. 仮想マシンの起動
インポートが成功したら、仮想マシンを起動します彡(ﾟ)(ﾟ)
5. 仮想マシンへのアクセス(ssh + sqlplus)
インポートされた仮想マシンにはNATのポートフォワードが下記のように設定されています。
sshのポートフォワードは 2222(HOST)→22(GUEST) に設定されているので、
「localhost:2222」でsshログインが可能です。
ユーザ名とパスワードは元ページに書かれている通り oracle 彡(ﾟ)(ﾟ)
(Username and password is oracle.)
sshログイン後、環境変数ORACLE_HOMEとPATHを設定します。
tnsnames.ora にはCDBへの接続文字列ORCLCDBと、
PDBへの接続文字列ORCLがセットされています。
後は sqlplus でログインするだけや！PDBに接続してみます。彡(ﾟ)(ﾟ)
export ORACLE_HOME=/u01/app/oracle/product/version/db_1/
export PATH=${PATH}:${ORACLE_HOME}/bin
cd $ORACLE_HOME/network/admin
cat tnsnames.ora
sqlplus /nolog
CONNECT PDBADMIN/oracle@ORCL
SELECT SYS_CONTEXT('USERENV', 'CON_NAME') AS CON_NAME FROM DUAL;
実行結果は下記の通り。DBにログイン成功や！彡(^)(^)
[oracle@localhost admin]$ export ORACLE_HOME=/u01/app/oracle/product/version/db_1/
[oracle@localhost admin]$ export PATH=${PATH}:${ORACLE_HOME}/bin
[oracle@localhost admin]$ cd $ORACLE_HOME/network/admin
[oracle@localhost admin]$ cat tnsnames.ora
ORCLCDB=localhost:1521/orclcdb
ORCL=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
[oracle@localhost admin]$ sqlplus /nolog
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Dec 5 09:02:47 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
SQL> CONNECT PDBADMIN/oracle@ORCL
Connected.
SQL> SELECT SYS_CONTEXT('USERENV', 'CON_NAME') AS CON_NAME FROM DUAL;
CON_NAME
--------------------------------------------------------------------------------
ORCL
SQL>
6. 仮想マシン上のDBへのアクセス(SQL Developer)
お次は SQL Developer で仮想マシンのDBにログインしてみます彡(ﾟ)(ﾟ)
仮想マシンのOracleリスナーのポートは 1521(HOST)→1521(GUEST) に
フォワードされているので、localhost:1521 でDB接続できます。
サービス名はPDBのサービス(orcl)で、PDB管理者(PDBADMIN)でログインしてみます。
接続後、試しにコンテナ名(PDB名)をSELECTしてみます。
成功(｀･ω･)Ъ
7. 仮想マシンのAPEX ADMIN環境にホストマシンのWebブラウザからアクセス(Oracle APEX)
(2019/2/12追記)Oracle APEX UG Meetup 2019 #1開催の勢いに任せて、追記するやで彡(ﾟ)(ﾟ)
仮想マシンの8080ポート(HOST)は8080ポート(GUEST)にフォワードされていて、
ホストマシンのブラウザから仮想マシンのAPEX ADMIN環境にアクセスできます。
URL…………http://localhost:8080/apex
Workspace…INTERNAL
Username …ADMIN
Password …oracle
接続画面キャプチャはこちら
接続後のキャプチャはこちら
この接続情報元ネタは下記、publicなのは無いのかな…？
Installing and Configuring an Oracle Developer Day VirtualBox Image
https://mikesmithers.wordpress.com/2015/01/25/installing-and-configuring-an-oracle-developer-day-virtualbox-image/
このADMIN環境からワークスペースを作成すれば、
もりもりAPEXアプリを作れるやで。ワイの過去記事も見てね彡(^)(^)
Oracle APEX Meetup 第2回「2時間でできるAPEXハンズオン」(6/1・木) に行ってみたのでご報告
https://gonsuke777.hatenablog.com/entry/20170613/1497339652
8. まとめ
VirtualBoxでお手軽にOracle Database 18c環境を構築できたやで！どんどん活用してね。
彡(^)(^)
Oracle DatabaseでSQLの性能計測1(SET AUTOTRACE TRACEONLY と SET TIMING ON編)【Oracle Database or GoldenGate Advent Calendar 2018 Day 1】
このエントリは Oracle Database or GoldenGate Advent Calendar 2018 の Day 1 の記事となります。
Oracle Database or GoldenGate Advent Calendar 2018
https://adventar.org/calendars/3525
1日目はSQL*Plusの SET AUTOTRACE TRACEONLY と SET TIMING ON で、SQLの実行時間を計測してみるやで。
彡(ﾟ)(ﾟ)
1. AUTOTRACE とは？
Oracle Databaseユーティリティ の SQL*Plus(sqlplus)の機能で、
SQLの実行計画や実行統計の取得できます。
SQL*Plus ユーザーズ・ガイドおよびリファレンス 18c
12.41.6 SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
https://docs.oracle.com/cd/E96517_01/sqpug/SET-system-variable-summary.html#GUID-0791433C-CA4A-4C79-BFF6-B7976E1534BA
8.1 文のトレースについて
https://docs.oracle.com/cd/E96517_01/sqpug/tuning-SQL-Plus.html#GUID-1425180A-9917-429E-B908-B217C0CAC3DD
2. SET TIMING とは？
Oracle Databaseユーティリティ の SQL*Plus(sqlplus)の機能で、
SQL や PL/SQL を実行した際の経過時間を表示できます。
SQL*Plus ユーザーズ・ガイドおよびリファレンス 18c
12.41.64 SET TIMI[NG] {ON | OFF}
https://docs.oracle.com/cd/E96517_01/sqpug/SET-system-variable-summary.html#GUID-35276053-FA28-4CA3-94A8-8806682C9EA5
3. 実行サンプル
下記に実行サンプルを提示してみますやで彡(ﾟ)(ﾟ)
CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy SET TIMING ON; -- TIMING を有効化しています。 SET AUTOTRACE TRACEONLY; -- AUTOTRACE を有効化しています。 -- サンプルSQL SELECT /*+ MONITOR */ B.ITEM_NAME , TRUNC(A.ORDER_DATE, 'DD') AS ORDER_DAILY , COUNT(*) FROM ORDER_TBL A , ITEM_TBL B WHERE A.ITEM_NO = B.ITEM_NO -- AND B.ITEM_NO BETWEEN 1 AND 100 AND TO_CHAR(B.REGIST_DATE, 'YYYYMMDD') = '20120801' GROUP BY B.ITEM_NAME, TRUNC(A.ORDER_DATE, 'DD') ORDER BY 1; Connected. 261 rows selected. Elapsed: 00:00:05.22 ★SET TIMING ON によって出力されたSQLの経過時間です。 Execution Plan ---------------------------------------------------------- Plan hash value: 44130803 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 208 | 30 (7)| 00:00:01 | | 1 | SORT GROUP BY | | 8 | 208 | 30 (7)| 00:00:01 | |* 2 | HASH JOIN | | 26 | 676 | 29 (4)| 00:00:01 | | 3 | TABLE ACCESS FULL| ORDER_TBL | 26 | 286 | 3 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL| ITEM_TBL | 300 | 4500 | 26 (4)| 00:00:01 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."ITEM_NO"="B"."ITEM_NO") 4 - filter(TO_CHAR(INTERNAL_FUNCTION("B"."REGIST_DATE"),'YYYYMMDD')='2 0120801') Statistics ★SET AUTOTRACE TRACEONLY によって出力されたSQLの実効統計 ---------------------------------------------------------- 97 recursive calls 0 db block gets 9012 consistent gets 15579 physical reads 0 redo size 7401 bytes sent via SQL*Net to client 795 bytes received via SQL*Net from client 19 SQL*Net roundtrips to/from client 11 sorts (memory) 0 sorts (disk) 261 rows processed
4. 出力された統計の見方
SET TIMING ON の方はシンプル、経過時間そのものを確認します。
： Elapsed: 00:00:05.22 ←コレを確認する。 ：
SET AUTOTRACE TRACEONLY では様々な統計が出力されますが、
SQL性能観点だと「consistent gets」「physical reads」辺りをよく見ますかね彡(ﾟ)(ﾟ)
： Statistics ---------------------------------------------------------- 97 recursive calls 0 db block gets 9012 consistent gets ★SQLの読込ブロック数 ←これはよく見る 15579 physical reads ★SQLのDisk IOブロック数 ←これはよく見る 0 redo size 7401 bytes sent via SQL*Net to client 795 bytes received via SQL*Net from client 19 SQL*Net roundtrips to/from client 11 sorts (memory) 0 sorts (disk) 261 rows processed ：
8.1.3 統計
https://docs.oracle.com/cd/E96517_01/sqpug/tuning-SQL-Plus.html#GUID-32806E04-0E05-4579-B2DB-E2C757CEA448
統計は、文を実行したときにサーバーによって記録され、文の実行に必要とされた
システム・リソースを示します。結果には、次の統計が含まれます。
recursive calls
db block gets
：
SQLチューニング観点で言えば、SET TIMING ON で確認できる「Elapsed」や
AUTOTRACEの「consistent gets」「physical reads」を血眼になって減らすんやね彡(ﾟ)(ﾟ)
5. AUTOTRACE TRACEONLY の注意点
まずひとつ目、AUTOTRACE TRACEONLY はその字面に反して
SQLのトランザクションが実行されてしまいます。下記記事参照彡(ﾟ)(ﾟ)
sqlplus の SET AUTOTRACE TRACEONLY は トレースオンリーじゃなくてトランザクションが実行されてしまう。
https://gonsuke777.hatenablog.com/entry/20160120/1453266062
ふたつ目は、AUTOTRACE TRACEONLY で出てくる実行計画と
(共有プール上に格納された)実際の実行計画は異なるケースが有ります。下記記事参照
EXPLAIN PLAN FOR …(※AUTOTRACE TRACEONLY含む)で出てくる実行計画 と
SQL実行時の実行計画が異なるケースを作ってみる。
https://gonsuke777.hatenablog.com/entry/20150723/1437662167
これは下記マニュアルにも制限事項として記載が有ります。
6.2.5 EXPLAIN PLANの制限事項
https://docs.oracle.com/cd/E96517_01/tgsql/generating-and-displaying-execution-plans.html#GUID-E2463C7B-F71A-4F06-85D3-1AF3D4D71CE8
Oracle Databaseでは、日付バインド変数の暗黙的な型変換を実行する文でのEXPLAIN PLANをサポートしません。
一般にバインド変数では、EXPLAIN PLANが実際の実行計画を表していない場合があります。
過去あるいは後日記載する DBMS_XPLAN.DISPLAY_CURSOR や DBMS_SQLTUNE も併用や！彡(ﾟ)(ﾟ)
6. まとめ
上記のような制限事項が有るとはいえ、特に AUTOTRACE TRACEONLY は便利です。
sqlplus だけで操作が完結するんで、SQLチューニングの最盛期で
超高速PDCAを廻してるタイミングでは重宝しますやね彡(^)(^)
増分統計(INCREMENTALプリファレンス)を有効にした状態で統計を採取して、シノプシス(synopsis)が使われる様子をSQLトレースで確認する。
表題の通り、増分統計(INCREMENTALプリファレンス=TRUE)を採取してみて、 内部動作をSQLトレースで確認してみるやで彡(ﾟ)(ﾟ)
Oracle Database SQLチューニング・ガイド 18c
13.2.8.2 DBMS_STATSによるパーティション表のグローバル統計の導出方法
https://docs.oracle.com/cd/E96517_01/tgsql/gathering-optimizer-statistics.html#GUID-6ECF96F3-72DD-4B09-974D-70D3FABE6F47
増分統計メンテナンスが有効な場合、DBMS_STATSでは、変更されたパーティションのみの統計を収集し、シノプシスを作成します。また、データベースは、パーティション・レベルのシノプシスをグローバル・シノプシスに自動的にマージし、パーティション・レベルの統計およびグローバル・シノプシスからグローバル統計を導出します。
1. オブジェクト作成と増分統計の有効化
まずはオブジェクトの作成と増分統計の有効化を実行彡(ﾟ)(ﾟ)
CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy -- テーブル作成 DROP TABLE TBL_A PURGE; CREATE TABLE TBL_A ( C1 NUMBER , C2 VARCHAR2(10) , C3 DATE ) PARTITION BY RANGE (C1) INTERVAL(10000) (PARTITION VALUES LESS THAN (10001)); -- 索引作成 CREATE INDEX TBL_A_I1 ON TBL_A(C1, C3) LOCAL; CREATE INDEX TBL_A_I2 ON TBL_A(C1) GLOBAL; -- INCREMENTALプリファレンス有効化と確認 COLUMN PREFERENCE FORMAT A30; EXEC DBMS_STATS.SET_TABLE_PREFS('AYSHIBAT', 'TBL_A', 'INCREMENTAL', 'TRUE'); SELECT DBMS_STATS.GET_PREFS('INCREMENTAL', 'AYSHIBAT', 'TBL_A') AS PREFERENCE FROM DUAL; Table dropped. Table created. Index created. Index created. PL/SQL procedure successfully completed. PREFERENCE ------------------------------ TRUE
2. テストデータの作成
次にテストデータを投入しますやで彡(ﾟ)(ﾟ)
-- テストデータ作成 INSERT INTO TBL_A SELECT LEVEL, 'C2_' || LEVEL, SYSDATE + (LEVEL/24/60) FROM DUAL CONNECT BY LEVEL <= 50000; COMMIT; SELECT COUNT(*) FROM TBL_A; 50000 rows created. Commit complete. COUNT(*) ---------- 50000
3. SQLトレースを取得しながら統計を採取
SQLトレースを取得しながらトレースを採取彡(ﾟ)(ﾟ)
-- 統計採取およびSQLトレース取得 CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy ALTER SESSION SET TRACEFILE_IDENTIFIER = 'AYSHIBAT'; EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE, plan_stat => 'ALL_EXECUTIONS'); EXEC DBMS_STATS.GATHER_TABLE_STATS('AYSHIBAT', 'TBL_A'); EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE; -- オプティマイザ統計確認 SET LINESIZE 300; SET PAGESIZE 100; COLUMN OWNER FORMAT A20; COLUMN TABLE_NAME FORMAT A20; COLUMN INDEX_NAME FORMAT A20; COLUMN PARTITION_NAME FORMAT A20; COLUMN SUBPARTITION_NAME FORMAT A20; ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS'; SELECT OWNER, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, NUM_ROWS, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A' ORDER BY OWNER, TABLE_NAME, PARTITION_NAME NULLS FIRST, SUBPARTITION_NAME NULLS FIRST; SELECT OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, NUM_ROWS, STALE_STATS FROM DBA_IND_STATISTICS WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A' ORDER BY OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME NULLS FIRST, SUBPARTITION_NAME NULLS FIRST; Connected. Session altered. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Session altered. OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME LAST_ANALYZED NUM_ROWS STA -------------------- -------------------- -------------------- -------------------- ------------------- ---------- --- AYSHIBAT TBL_A 2018/11/28 02:09:12 50000 NO AYSHIBAT TBL_A SYS_P4443 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4444 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4445 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4446 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4447 2018/11/28 02:09:11 10000 NO OWNER TABLE_NAME INDEX_NAME PARTITION_NAME SUBPARTITION_NAME LAST_ANALYZED NUM_ROWS STA -------------------- -------------------- -------------------- -------------------- -------------------- ------------------- ---------- --- AYSHIBAT TBL_A TBL_A_I1 2018/11/28 02:09:13 50000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4443 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4444 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4445 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4446 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4447 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I2 2018/11/28 02:09:13 50000 NO 7 rows selected.
4. SQLトレース(整形後)の確認
SQLトレースを確認しますやで。tkprofの整形後のトレースです彡(ﾟ)(ﾟ) シノプシス表(wri\$_optstat_synopsis_head\$)にデータをINSERTしてますね！
TKPROF: Release 12.2.0.1.0 - Development on Wed Nov 28 02:11:48 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Trace file: orcl_ora_4417_AYSHIBAT.trc Sort options: default ： ： SQL ID: 808a0gzdt26kc Plan Hash: 0 insert into sys.wri$_optstat_synopsis_head$ (bo#, group#, intcol#, spare1, spare2) values (:1, :2, :3, :4, :5) ★シノプシス表へのINSERT文 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 15 0.00 0.00 0 0 0 0 Execute 15 0.00 0.00 0 25 135 15 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 30 0.00 0.00 0 25 135 15 ：
5. 追加データ投入と統計失効確認
追加データを投入します。DBMS_STATS.FLUSH_DATABASE_MONITORING_INFOで 失効状態をフラッシュした後に、統計が失効したことを確認彡(ﾟ)(ﾟ)
CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy -- 追加データ投入 INSERT INTO TBL_A SELECT LEVEL+50000, 'C2_' || (LEVEL+50000), SYSDATE + (LEVEL/24/60) FROM DUAL CONNECT BY LEVEL <= 10000; COMMIT; -- 統計情報の失効を即座に反映 EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; -- オプティマイザ統計確認 SET LINESIZE 300; SET PAGESIZE 100; COLUMN OWNER FORMAT A20; COLUMN TABLE_NAME FORMAT A20; COLUMN INDEX_NAME FORMAT A20; COLUMN PARTITION_NAME FORMAT A20; COLUMN SUBPARTITION_NAME FORMAT A20; ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS'; SELECT OWNER, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, NUM_ROWS, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A' ORDER BY OWNER, TABLE_NAME, PARTITION_NAME NULLS FIRST, SUBPARTITION_NAME NULLS FIRST; SELECT OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, NUM_ROWS, STALE_STATS FROM DBA_IND_STATISTICS WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A' ORDER BY OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME NULLS FIRST, SUBPARTITION_NAME NULLS FIRST; Connected. 10000 rows created. Commit complete. PL/SQL procedure successfully completed. Session altered. OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME LAST_ANALYZED NUM_ROWS STA -------------------- -------------------- -------------------- -------------------- ------------------- ---------- --- AYSHIBAT TBL_A 2018/11/28 02:09:12 50000 YES ★統計が失効 AYSHIBAT TBL_A SYS_P4443 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4444 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4445 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4446 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4447 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4448 7 rows selected. OWNER TABLE_NAME INDEX_NAME PARTITION_NAME SUBPARTITION_NAME LAST_ANALYZED NUM_ROWS STA -------------------- -------------------- -------------------- -------------------- -------------------- ------------------- ---------- --- AYSHIBAT TBL_A TBL_A_I1 2018/11/28 02:09:13 50000 YES ★統計が失効 AYSHIBAT TBL_A TBL_A_I1 SYS_P4443 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4444 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4445 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4446 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4447 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4448 AYSHIBAT TBL_A TBL_A_I2 2018/11/28 02:09:13 50000 YES ★統計が失効 8 rows selected.
6. SQLトレースを取得しながら増分統計を採取
グローバル統計が失効した状態で、統計を採取します彡(ﾟ)(ﾟ)
-- 増分統計採取およびSQLトレース取得 CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy ALTER SESSION SET TRACEFILE_IDENTIFIER = 'AYSHIBAT'; EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE, plan_stat => 'ALL_EXECUTIONS'); EXEC DBMS_STATS.GATHER_TABLE_STATS('AYSHIBAT', 'TBL_A'); EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE; -- オプティマイザ統計確認 SET LINESIZE 300; SET PAGESIZE 100; COLUMN OWNER FORMAT A20; COLUMN TABLE_NAME FORMAT A20; COLUMN INDEX_NAME FORMAT A20; COLUMN PARTITION_NAME FORMAT A20; COLUMN SUBPARTITION_NAME FORMAT A20; ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS'; SELECT OWNER, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, NUM_ROWS, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A' ORDER BY OWNER, TABLE_NAME, PARTITION_NAME NULLS FIRST, SUBPARTITION_NAME NULLS FIRST; SELECT OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, NUM_ROWS, STALE_STATS FROM DBA_IND_STATISTICS WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A' ORDER BY OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME NULLS FIRST, SUBPARTITION_NAME NULLS FIRST; Connected. Session altered. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Session altered. OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME LAST_ANALYZED NUM_ROWS STA -------------------- -------------------- -------------------- -------------------- ------------------- ---------- --- AYSHIBAT TBL_A 2018/11/28 02:16:03 60000 NO AYSHIBAT TBL_A SYS_P4443 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4444 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4445 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4446 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4447 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4448 2018/11/28 02:16:03 10000 NO 7 rows selected. OWNER TABLE_NAME INDEX_NAME PARTITION_NAME SUBPARTITION_NAME LAST_ANALYZED NUM_ROWS STA -------------------- -------------------- -------------------- -------------------- -------------------- ------------------- ---------- --- AYSHIBAT TBL_A TBL_A_I1 2018/11/28 02:16:04 60000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4443 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4444 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4445 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4446 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4447 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4448 2018/11/28 02:16:04 10000 NO AYSHIBAT TBL_A TBL_A_I2 2018/11/28 02:16:04 60000 NO 8 rows selected.
7. 増分統計採取時のSQLトレース(整形後)確認
増分統計によるSQLトレースは下記の通り。 でもこれだけだと増分統計の効果が判らないので…彡(ﾟ)(ﾟ)
TKPROF: Release 12.2.0.1.0 - Development on Wed Nov 28 02:17:18 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Trace file: orcl_ora_4496_AYSHIBAT.trc Sort options: default ： ： insert into sys.wri$_optstat_synopsis_head$ (bo#, group#, intcol#, spare1, spare2) values (:1, :2, :3, :4, :5) ： ： OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 143 0.01 0.01 0 0 0 0 Execute 455 0.04 0.04 3 136 292 50 Fetch 382 0.65 0.65 0 3009 0 318 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 980 0.71 0.71 3 3145 292 368 ：
8. 参考：増分統計を有効化しなかった場合のSQLトレース(※7.のトレースと同じ条件)
増分統計を有効化しなかった場合のSQLトレースも見てみます。 増分統計を有効化していない以外は7.のSQLトレースと同じ条件です。
TKPROF: Release 12.2.0.1.0 - Development on Wed Nov 28 02:36:53 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Trace file: orcl_ora_4648_AYSHIBAT.trc Sort options: default ： OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 199 0.02 0.02 0 0 0 0 Execute 833 0.10 0.11 0 273 631 117 Fetch 670 1.22 1.23 0 4419 0 638 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1702 1.35 1.37 0 4692 631 755 ：
増分統計の方が query, current, rows と云った負荷が 少ない事が解りますやね！彡(^)(^)
9. マニュアル
マニュアルも読んでおくんやで彡(ﾟ)(ﾟ)
13.2.8.3.2 SET_TABLE_PREFSを使用した増分統計の有効化
https://docs.oracle.com/cd/E96517_01/tgsql/gathering-optimizer-statistics.html#GUID-75AD8460-A5F6-43B1-ADE0-4AD77AF72274
変更されたパーティションのみをスキャンしてグローバル統計を増分的に更新するデータベースでは、次の条件を満たす必要があります。
・パーティション表のPUBLISH値がtrueです。
・パーティション表のINCREMENTAL値がtrueです。
・統計収集プロシージャでは、ESTIMATE_PERCENTにAUTO_SAMPLE_SIZE、GRANULARITYにAUTOを指定する必要があります。Oracle Database PL/SQL Packages and Types Reference 18c
162 DBMS_STATS
https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_STATS.html#GUID-01FAB8ED-E4A3-4C3E-8FE2-88717DCDDA06
10. 追記：シノプシス(synopsis)を集計してるっぽい内部SQL
select /*+ parallel(1) OPT_PARAM('_parallel_syspls_obey_force' 'false') */ b.intcol#, b.nnv, b.nmin, b.nmax, b.minval, b.maxval, b.acl, nvl(n.ndv, 0) sndv from ( -- all basic stats except ndv select /*+ no_merge */ intcol#, greatest(0, :total_rows - sum(h.null_cnt)) nnv, sum(h.avgcln * t.rowcnt)/greatest(:total_rows, 1) acl, -- if total_rows is 0, avgcln is 0 min(nvl2(h.lowval, h.minimum, null)) nmin, -- normalized min max(nvl2(h.hival, h.maximum, null)) nmax, -- normalized max min(h.lowval) minval, -- raw value max(h.hival) maxval from sys.tabpart$ t, sys."_HIST_HEAD_DEC" h where t.bo# = :tab_num and t.obj# = h.obj# group by h.intcol# union all select intcol#, greatest(0, :total_rows - sum(h.null_cnt)) nnv, sum(h.avgcln * t.rowcnt)/greatest(:total_rows, 1) avgcln, min(nvl2(h.lowval, h.minimum, null)) nmin, -- normalized min max(nvl2(h.hival, h.maximum, null)) nmax, -- normalized max min(h.lowval) minval, -- raw value max(h.hival) maxval from sys.tabcompart$ t, sys."_HIST_HEAD_DEC" h where t.bo# = :tab_num and t.obj# = h.obj# group by h.intcol# ) b, ( select intcol#, to_approx_count_distinct(approx_count_distinct_agg(spare2)) ndv from wri$_optstat_synopsis_head$ where bo# = :tab_num group by intcol# ) n where b.intcol# = n.intcol#(+)
shell の while read で -r を付与して \（バックスラッシュ）によるクォートを防止する。
珍しくOracle Database以外のネタ、shell の while read に -r を付与して、
\（バックスラッシュ）によるクォートを防止してみるやで。彡(ﾟ)(ﾟ)
1. テスト用 の shell script
data.txt の内容を1行ずつ出力する単純なシェルです。
while_read.sh #!/bin/bash CNT=0; cat data.txt | while read line do CNT=`expr ${CNT} + 1`; echo "${CNT}:${line}"; done;
2. テストデータ（data.txt）
テストデータの中身です。
1行目の最終文字が\（バックスラッシュ）です。
cat data.txt
iii\
jjj
kkk
3. テスト用 shell script の実行結果
この状態での実行結果は下記の通りです。1行目の最終文字の
\（バックスラッシュ）で改行がクォートされてしまうため、
1行目と2行目が繋がって出力されてしまいますやね彡(ﾟ)(ﾟ)
./while_read.sh 1:iiijjj 2:kkk
4. read に -r を付与して実行
read に -r を付与して shell script を実行してみます。
改行がクォートされず、3行出力されましたやね彡(^)(^)
cat while_read.sh #!/bin/bash CNT=0; cat data.txt | while read -r line do CNT=`expr ${CNT} + 1`; echo "${CNT}:${line}"; done; ./while_read.sh 1:iii\ 2:jjj 3:kkk
5. 参考
read コマンドの使い方 - 拡張 POSIX
シェルスクリプト Advent Calendar 2013 - ダメ出し Blog
https://fumiyas.github.io/2013/12/14/read.sh-advent-calendar.html
表のオンライン再定義(DBMS_REDEFINITION)で、データを更新しながら RANGE PARTITION＆グローバル索引 -> HASH PARTITION＆ローカル索引への定義変更をやってみる。(Oracle Database)
タイトルが長いですが、表のオンライン再定義はDMLによるデータ更新を 制限せずに、表定義(論理構造や物理構造)を変更する機能です。
Oracle Database管理者ガイド 12cリリース1 (12.1)
20.7 表のオンライン再定義
https://docs.oracle.com/cd/E57425_01/121/ADMIN/tables.htm#GUID-92361F74-4796-407D-A3B9-569C6E544E34
今回は下記のパーティション構造とグローバル索引⇒ローカル索引への 変更を検証してみますやで彡(ﾟ)(ﾟ) バージョンは訳あって 12cR1(12.1.0.2) です。
RANGE PARTITION＆グローバル索引の表 ⇒ HASH PARTITION＆ローカル索引の表
1. テスト表の作成とテストデータのINSERT
テスト表(対象表／仮表)を作成して、対象表にテストデータをINSERTします。 1000件を初期セットした後、1秒に1件ずつINSERTしていくやで彡(ﾟ)(ﾟ) ＃最終的には対象表に1180件をINSERTしていきます。
-- Old Table CREATE TABLE TBL_A ( C1 NUMBER , C2 NUMBER ) PARTITION BY RANGE (C1) ( PARTITION P1 VALUES LESS THAN (100), PARTITION P2 VALUES LESS THAN (200), PARTITION PMAX VALUES LESS THAN (MAXVALUE) ); ALTER TABLE TBL_A ADD CONSTRAINT TBL_A_PK PRIMARY KEY (C1) USING INDEX GLOBAL; CREATE INDEX TBL_A_I1 on TBL_A (C2) GLOBAL; -- New Table CREATE TABLE NEW_TBL_A ( C1 NUMBER , C2 NUMBER ) PARTITION BY HASH (C1) ( PARTITION P1 , PARTITION P2 , PARTITION P3 , PARTITION P4 ); --※仮表の索引と主キーは後で作成 BEGIN -- Test Data INSERT INTO TBL_A SELECT LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 1000; COMMIT; FOR i IN 1001..1180 LOOP INSERT INTO TBL_A VALUES(i, i); COMMIT; DBMS_LOCK.SLEEP(1); END LOOP; END; / Table created. Table altered. Index created. Table created. : （PL/SQLはしばらく動作する）
（※ここから別セッション）この時点でのディクショナリ情報は下記の通りです。
10:26:24 SQL> -- Part_tables 10:26:24 SQL> SELECT TABLE_NAME, PARTITIONING_TYPE, STATUS 10:26:24 2 FROM USER_PART_TABLES WHERE TABLE_NAME LIKE '%TBL_A%'; TABLE_NAME PARTITION STATUS --------------- --------- -------- NEW_TBL_A HASH VALID ★仮表はHASHパーティション TBL_A RANGE VALID ★対象表はRANGEパーティション 10:26:24 SQL> -- Indexes 10:26:24 SQL> SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME, UNIQUENESS, STATUS, GLOBAL_STATS 10:26:24 2 FROM USER_INDEXES WHERE TABLE_NAME LIKE '%TBL_A%'; TABLE_OWNER TABLE_NAME INDEX_NAME UNIQUENES STATUS GLO --------------- --------------- --------------- --------- -------- --- AYSHIBAT TBL_A TBL_A_PK UNIQUE VALID YES ★対象表のグローバル索引(主キー) AYSHIBAT TBL_A TBL_A_I1 NONUNIQUE VALID YES ★対象表のグローバル索引 10:26:24 SQL> -- Constraints 10:26:24 SQL> SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE 10:26:24 2 FROM USER_CONSTRAINTS WHERE TABLE_NAME LIKE '%TBL_A%'; OWNER TABLE_NAME CONSTRAINT_NAME C --------------- --------------- --------------- - AYSHIBAT TBL_A TBL_A_PK P 10:26:26 SQL> 10:26:26 SQL> -- Data Check 10:26:26 SQL> SELECT COUNT(*) FROM TBL_A; COUNT(*) ---------- 1021 10:26:27 SQL> SELECT COUNT(*) FROM NEW_TBL_A; COUNT(*) ---------- 0
2. DBMS_REDEFINITION.CAN_REDEF_TABLEによる事前チェック
DBMS_REDEFINITION.CAN_REDEF_TABLEプロシージャで表がオンライン再定義可能かチェックします。
-- Check redefinition possibility. EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'TBL_A', DBMS_REDEFINITION.CONS_USE_PK); PL/SQL procedure successfully completed.
オンライン表再定義が不可能な場合は、下記のようなエラーが出力されます彡(ﾟ)(ﾟ)
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'TBL_B', DBMS_REDEFINITION.CONS_USE_PK); BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'TBL_B', DBMS_REDEFINITION.CONS_USE_PK); END; * ERROR at line 1: ORA-12088: cannot online redefine table "AYSHIBAT"."TBL_B" with unsupported datatype ORA-06512: at "SYS.DBMS_REDEFINITION", line 173 ORA-06512: at "SYS.DBMS_REDEFINITION", line 3664 ORA-06512: at line 1
3. DBMS_REDEFINITION.START_REDEF_TABLEプロシージャでオンライン再定義を開始
DBMS_REDEFINITION.START_REDEF_TABLEプロシージャでオンライン再定義を開始します。 対象表⇒仮表への初期データ移行が、この時点でのデータ断面で実行されます。
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'TBL_A', 'NEW_TBL_A'); SELECT COUNT(*) FROM TBL_A; SELECT COUNT(*) FROM NEW_TBL_A; 10:26:27 SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'TBL_A', 'NEW_TBL_A'); PL/SQL procedure successfully completed. 10:26:29 SQL> SELECT COUNT(*) FROM TBL_A; COUNT(*) ---------- 1023 10:26:29 SQL> SELECT COUNT(*) FROM NEW_TBL_A; COUNT(*) ---------- 1022 ★仮表にデータが挿入、件数差異は裏で対象表のデータINSERTを実行しているため。
4. 仮表にローカル索引(主キー、非ユニーク索引)を作成
仮表に主キーと非ユニークのローカル索引を作成します。
CREATE TABLE時に作らないのは、上記 3. の初期データ移行の 負荷(処理量)を減らすため。索引無い方がINSERTは速いんで彡(ﾟ)(ﾟ)
ALTER TABLE NEW_TBL_A ADD CONSTRAINT NEW_TBL_A_PK PRIMARY KEY (C1) USING INDEX LOCAL; CREATE INDEX NEW_TBL_A_I1 on NEW_TBL_A (C2) LOCAL; Table altered. Index created.
この時点でのディクショナリ情報は下記の通りです。
10:26:29 SQL> -- Part_tables 10:26:29 SQL> SELECT TABLE_NAME, PARTITIONING_TYPE, STATUS 10:26:29 2 FROM USER_PART_TABLES WHERE TABLE_NAME LIKE '%TBL_A%'; TABLE_NAME PARTITION STATUS --------------- --------- -------- NEW_TBL_A HASH VALID TBL_A RANGE VALID 10:26:29 SQL> -- Indexes 10:26:29 SQL> SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME, UNIQUENESS, STATUS, GLOBAL_STATS 10:26:29 2 FROM USER_INDEXES WHERE TABLE_NAME LIKE '%TBL_A%'; TABLE_OWNER TABLE_NAME INDEX_NAME UNIQUENES STATUS GLO --------------- --------------- --------------- --------- -------- --- AYSHIBAT NEW_TBL_A NEW_TBL_A_I1 NONUNIQUE N/A NO ★仮表のローカル索引 AYSHIBAT NEW_TBL_A NEW_TBL_A_PK UNIQUE N/A NO ★仮表のローカル索引 AYSHIBAT TBL_A TBL_A_I1 NONUNIQUE VALID YES AYSHIBAT TBL_A TBL_A_PK UNIQUE VALID YES AYSHIBAT MLOG$_TBL_A I_MLOG$_TBL_A NONUNIQUE VALID YES 10:26:29 SQL> -- Constraints 10:26:29 SQL> SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE 10:26:29 2 FROM USER_CONSTRAINTS WHERE TABLE_NAME LIKE '%TBL_A%'; OWNER TABLE_NAME CONSTRAINT_NAME C --------------- --------------- --------------- - AYSHIBAT NEW_TBL_A NEW_TBL_A_PK P ★仮表の主キー制約 AYSHIBAT TBL_A TBL_A_PK P
5. DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECTプロシージャによる索引＆主キー制約の関連付け
DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECTプロシージャで 対象表と仮表の索引と主キーを関連付けます。
BEGIN DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT( USER -- uname , 'TBL_A' -- orig_table , 'NEW_TBL_A' -- int_table , DBMS_REDEFINITION.CONS_INDEX -- dep_type , USER -- dep_owner , 'TBL_A_PK' -- dep_orig_name , 'NEW_TBL_A_PK' -- dep_int_name ); END; / BEGIN DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT( USER -- uname , 'TBL_A' -- orig_table , 'NEW_TBL_A' -- int_table , DBMS_REDEFINITION.CONS_CONSTRAINT -- dep_type , USER -- dep_owner , 'TBL_A_PK' -- dep_orig_name , 'NEW_TBL_A_PK' -- dep_int_name ); END; / BEGIN DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT( USER -- uname , 'TBL_A' -- orig_table , 'NEW_TBL_A' -- int_table , DBMS_REDEFINITION.CONS_INDEX -- dep_type , USER -- dep_owner , 'TBL_A_I1' -- dep_orig_name , 'NEW_TBL_A_I1' -- dep_int_name ); END; / PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTSプロシージャで 対象表の関連オブジェクトも仮表にコピーします。
ただし今回のように、REGISTER_DEPENDENT_OBJECT で明示的に 関連付けを行うケースだと、この手順は不要かも……彡(-)(-)
SET SERVEROUTPU ON SIZE 1000000; DECLARE error_count pls_integer := 0; BEGIN DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS( USER -- uname , 'TBL_A' -- orig_table , 'NEW_TBL_A' -- int_table , 0 -- copy_indexes 0...NO_COPY , FALSE -- copy_triggers , FALSE -- copy_constraints , FALSE -- copy_privileges , FALSE -- ignore_errors , error_count -- num_errors , FALSE -- copy_statistics , FALSE -- copy_mlogs ); DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count)); END; / errors := 0 PL/SQL procedure successfully completed.
6. DBMS_REDEFINITION.FINISH_REDEF_TABLEプロシージャでオンライン再定義を完了する。
DBMS_REDEFINITION.FINISH_REDEF_TABLEプロシージャでオンライン再定義を完了します。
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'TBL_A', 'NEW_TBL_A'); PL/SQL procedure successfully completed.
完了後のディクショナリとデータ件数は以下の通りです。
10:26:34 SQL> -- Part_tables 10:26:34 SQL> SELECT TABLE_NAME, PARTITIONING_TYPE, STATUS 10:26:34 2 FROM USER_PART_TABLES WHERE TABLE_NAME LIKE '%TBL_A%'; TABLE_NAME PARTITION STATUS --------------- --------- -------- NEW_TBL_A RANGE VALID TBL_A HASH VALID ★HASHパーティションに変わっている。 10:26:34 SQL> -- Indexes 10:26:34 SQL> SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME, UNIQUENESS, STATUS, GLOBAL_STATS 10:26:34 2 FROM USER_INDEXES WHERE TABLE_NAME LIKE '%TBL_A%'; TABLE_OWNER TABLE_NAME INDEX_NAME UNIQUENES STATUS GLO --------------- --------------- --------------- --------- -------- --- AYSHIBAT NEW_TBL_A NEW_TBL_A_PK UNIQUE VALID YES AYSHIBAT NEW_TBL_A NEW_TBL_A_I1 NONUNIQUE VALID YES AYSHIBAT TBL_A TBL_A_PK UNIQUE N/A NO ★ローカル索引に変わっている。 AYSHIBAT TBL_A TBL_A_I1 NONUNIQUE N/A NO ★ローカル索引に変わっている。 10:26:35 SQL> -- Constraints 10:26:35 SQL> SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE 10:26:35 2 FROM USER_CONSTRAINTS WHERE TABLE_NAME LIKE '%TBL_A%'; OWNER TABLE_NAME CONSTRAINT_NAME C --------------- --------------- --------------- - AYSHIBAT TBL_A TBL_A_PK P AYSHIBAT NEW_TBL_A NEW_TBL_A_PK P 10:26:38 SQL> 10:26:38 SQL> -- Redifiniitoned Table Data Check 10:26:38 SQL> SELECT COUNT(*) FROM NEW_TBL_A; COUNT(*) ---------- 1028 10:26:38 SQL> SELECT COUNT(*) FROM TBL_A; COUNT(*) ---------- 1032 ★件数が増えていってるのは裏でINSERTしているため 10:26:38 SQL> SELECT COUNT(*) FROM TBL_A; COUNT(*) ---------- 1038 ★件数が増えていってるのは裏でINSERTしているため ： ： 10:32:43 SQL> SELECT COUNT(*) FROM TBL_A; COUNT(*) ---------- 1180 ★最終的なデータ件数 10:32:36 SQL> SELECT COUNT(*) FROM TBL_A PARTITION (P1); COUNT(*) ---------- 285 ★HASHパーティションP1の件数 10:32:36 SQL> SELECT COUNT(*) FROM TBL_A PARTITION (P2); COUNT(*) ---------- 282 ★HASHパーティションP2の件数 10:32:36 SQL> SELECT COUNT(*) FROM TBL_A PARTITION (P3); COUNT(*) ---------- 305 ★HASHパーティションP3の件数 10:32:36 SQL> SELECT COUNT(*) FROM TBL_A PARTITION (P4); COUNT(*) ---------- 308 ★HASHパーティションP4の件数 10:32:36 SQL>
7. まとめ
HASHパーティション化とローカル索引への表定義変更を、 データの更新を止めずに実行できたで！彡(^)(^)
12cR1からのDBMS_REDEFINITIONには、上記で説明した一連のプロシージャを 一まとめにしたREDEF_TABLEプロシージャが提供されています。こちらは別の機会に彡(ﾟ)(ﾟ)
Oracle Database PL/SQLパッケージおよびタイプ・リファレンス
12c リリース1 (12.1)
122 DBMS_REDEFINITION
REDEF_TABLEプロシージャ
https://docs.oracle.com/cd/E57425_01/121/ARPLS/d_redefi.htm#CHDDDDFG
8. 参考情報
マニュアルやサポートドキュメントも読んでおくんやで彡(ﾟ)(ﾟ)
Oracle Database管理者ガイド 12cリリース1 (12.1)
20.7 表のオンライン再定義
https://docs.oracle.com/cd/E57425_01/121/ADMIN/tables.htm#GUID-92361F74-4796-407D-A3B9-569C6E544E34
Oracle Database PL/SQLパッケージおよびタイプ・リファレンス
12c リリース1 (12.1)
122 DBMS_REDEFINITION
https://docs.oracle.com/cd/E57425_01/121/ARPLS/d_redefi.htm#CBBJJAIF
MOSドキュメント：How To Partition Existing Table Using DBMS_REDEFINITION (ドキュメントID 472449.1)
https://support.oracle.com/epmos/faces/DocumentDisplay?id=472449.1
※ログインが必要です。
9iR2環境から18c環境のPDBにTTS(トランスポータブル表領域)でデータを移行してみる(Oracle Database)
表題の通り、9iR2環境から18c環境のPDBにTTS(トランスポータブル表領域)で
データを移行してみるやで彡(ﾟ)(ﾟ) 以下の構成で検証します。
9iR2 ⇒ (トランスポータブル表領域) ⇒ 18c(PDB)
1. プラットフォーム、キャラクタセット、ブロックサイズの確認(9iR2 and 18c)
まず制限事項をクリアするため、プラットフォーム(OS)、キャラクタセット、ブロックサイズを確認します。
※TTS の 制限事項についてはマニュアルや My Oracle Support のドキュメントもご覧下さい。
# 9iR2環境 uname -a Linux xxxxxxxx 2.6.9-55.ELsmp #1 SMP Fri Apr 20 17:03:35 EDT 2007 i686 i686 i386 GNU/Linux
-- 9iR2環境 SET LINESIZE 170; SELECT * FROM V$VERSION; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production PL/SQL Release 9.2.0.6.0 - Production CORE 9.2.0.6.0 Production TNS for Linux: Version 9.2.0.6.0 - Production NLSRTL Version 9.2.0.6.0 - Production SET LINESIZE 170; SET PAGESIZE 100; COLUMN PARAMETER FORMAT A50; COLUMN VALUE FORMAT A80; SELECT * FROM NLS_DATABASE_PARAMETERS; PARAMETER VALUE -------------------------------------------------- -------------------------------------------------------------------------------- NLS_LANGUAGE AMERICAN ： NLS_CHARACTERSET JA16SJIS ★ ： NLS_NCHAR_CHARACTERSET AL16UTF16 ★ ： SHOW PARAMETER DB_BLOCK_SIZE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_size integer 8192
# 18c環境 uname -a Linux xxxxxxxx 4.1.12-112.14.10.el6uek.x86_64 #2 SMP Mon Jan 8 18:24:01 PST 2018 x86_64 x86_64 x86_64 GNU/Linux
-- 18c環境 SHOW CON_NAME; CON_NAME ------------------------------ PDB01 SET LINESIZE 170; SELECT * FROM V$VERSION; BANNER -------------------------------------------------------------------------------- BANNER_FULL ------------------------------------------------------------------------------------------- BANNER_LEGACY CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.2.0.0.0 Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production 0 PARAMETER VALUE -------------------------------------------------- -------------------------------------------------------------------------------- NLS_RDBMS_VERSION 18.0.0.0.0 : NLS_NCHAR_CHARACTERSET AL16UTF16 ★ NLS_CHARACTERSET JA16SJIS ★ : SHOW PARAMETER DB_BLOCK_SIZE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_size integer 8192
2. 表領域を作成(9iR2) ※元々存在する表領域を移行する場合は不要
移行元の9iR2環境で表領域を作成します。
※元々存在する表領域を移行する場合は不要です彡(ﾟ)(ﾟ)
-- 9iR2 CREATE TABLESPACE TEST01 DATAFILE '/u01/oracle/orcl/oradata/orcl/test01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED; CREATE TABLESPACE TEST02 DATAFILE '/u01/oracle/orcl/oradata/orcl/test02.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED; 表領域が作成されました。 表領域が作成されました。
3. オブジェクト作成(9iR2) ※元々存在するオブジェクトを移行する場合は不要
移行元の9iR2環境でオブジェクトを作成します。
※元々存在するオブジェクトを移行する場合は不要です彡(ﾟ)(ﾟ)
-- 9iR2 CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy CREATE TABLE TBL_A (C1 NUMBER) TABLESPACE TEST01; CREATE UNIQUE INDEX TBL_A_PK ON TBL_A(C1) TABLESPACE TEST02; INSERT INTO TBL_A SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 1000; COMMIT; 表が作成されました。 索引が作成されました。 1000行が作成されました。 コミットが完了しました。
4. 表領域が自己完結しているかを確認（DBMS_TTS.TRANSPORT_SET_CHECK）
表領域が自己完結しているかどうかをDBMS_TTSパッケージのTRANSPORT_SET_CHECKプロシージャと
TRANSPORT_SET_VIOLATIONSディクショナリで確認します。
下記例では、TEST02 が自己完結していない事が示されていますやね彡(ﾟ)(ﾟ)
TEST01表領域と一緒にトランスポートする必要があります。
# 9iR2 SET LINESIZE 100; EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TEST01', TRUE); SELECT * FROM TRANSPORT_SET_VIOLATIONS; PL/SQLプロシージャが正常に完了しました。 レコードが選択されませんでした。 EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TEST02', TRUE); SELECT * FROM TRANSPORT_SET_VIOLATIONS; PL/SQLプロシージャが正常に完了しました。 VIOLATIONS ---------------------------------------------------------------------------------------------------- Index AYSHIBAT.TBL_A_PK in tablespace TEST02 points to table AYSHIBAT.TBL_A in tablespace TEST01 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^★TEST02は自己完結していない。 EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TEST01,TEST02', TRUE); SELECT * FROM TRANSPORT_SET_VIOLATIONS; PL/SQLプロシージャが正常に完了しました。 レコードが選択されませんでした。
5. コピーする表領域を READ ONLY にする(9iR2)
移行元(9iR2)のコピー対象表領域を READ ONLY にします。
-- 9iR2 ALTER TABLESPACE TEST01 READ ONLY; ALTER TABLESPACE TEST02 READ ONLY; 表領域が変更されました。 表領域が変更されました。
6. メタデータをエクスポート(9iR2)
移行元(9iR2)でメタデータ(定義情報)をエクスポートします。
9iR2環境なので Datapump は未実装です。従来型エクスポートを実行します。
# 9iR2 exp \'SYS/xxxxxxxx as sysdba\' TRANSPORT_TABLESPACE=y TABLESPACES=(TEST01,TEST02) FILE=test_meta.dmp LOG=exp_test_meta.log Export: Release 9.2.0.6.0 - Production on 火 Sep 25 14:26:05 2018 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 接続先: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production JA16SJISキャラクタ・セットおよびAL16UTF16 NCHARキャラクタ・セットでエクスポートが終了しました 注意: 表データ(行)はエクスポートされません。 トランスポータブル表領域メタデータをエクスポートします... 表領域TEST01 ... . クラスタ定義をエクスポート中 . 表定義をエクスポート中 . 表 TBL_Aをエクスポート中 表領域TEST02 ... . クラスタ定義をエクスポート中 . 表定義をエクスポート中 . 参照整合性制約をエクスポート中 . トリガーをエクスポート中 . トランスポータブル表領域メタデータのエクスポートが終了しました。 エクスポートは警告なしで正常終了しました。
7. データファイルとメタデータdmpを移行先に転送(9iR2 ⇒ 18c)
scp等でデータファイルとメタデータdmpを移行先(18c)に転送します。 下記はデータファイルとメタデータを移行した後の結果です。
# 18c環境・転送後 $ pwd /u01/app/oracle/oradata/SJISDB/7676F6B5B0D33571E0534656860A3EB5/datafile/ $ ls -la total 22384272 drwxr-x--- 2 oracle oinstall 4096 Sep 25 06:00 . drwxr-x--- 3 oracle oinstall 4096 Sep 22 13:45 .. -rw-r----- 1 oracle oinstall 408952832 Sep 25 05:57 o1_mf_sysaux_ftdkv25s_.dbf -rw-r----- 1 oracle oinstall 283123712 Sep 25 05:57 o1_mf_system_ftdkv25l_.dbf -rw-r----- 1 oracle oinstall 65019904 Sep 24 01:15 o1_mf_temp_ftdkv25t_.dbf -rw-r----- 1 oracle oinstall 104865792 Sep 25 05:57 o1_mf_undotbs1_ftdkv25t_.dbf -rw-r----- 1 oracle oinstall 104865792 Sep 25 05:51 o1_mf_users_ftdplvyt_.dbf -rw-r--r-- 1 oracle oinstall 104865792 Sep 25 2018 test01.dbf ★データファイル -rw-r--r-- 1 oracle oinstall 104865792 Sep 25 2018 test02.dbf ★データファイル -rw-r--r-- 1 oracle oinstall 16384 Sep 25 2018 test_meta.dmp ★メタデータdmp
8. 移行元の表領域を READ WRITE に戻す(9iR2)
移行元(9iR2)の表領域を READ WRITE に戻します。
-- 9iR2 ALTER TABLESPACE TEST01 READ WRITE; ALTER TABLESPACE TEST02 READ WRITE; 表領域が変更されました。 表領域が変更されました。
9. メタデータを対象のPDBにインポート(18c)
メタデータを対象のPDBにインポートします。IMP-00403 の警告が出ますが、
下記マニュアルの記述を元に、必要に応じて生成されたSQLを手動実行して下さい。
※生成されたSQLが0バイトの場合は実行不要です。
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/oracle-original-import-utility.html#GUID-83111A9F-A2FC-4DC0-83BB-EDA398BBD4E0
If the import job is run by a user with the DBA role, and not all objects can be re-created by user SYSTEM,
then the following warning message is written to the log file:
IMP-00403:
Warning: This import generated a separate SQL file "logfilename_sys" which contains DDL that failed due to a privilege issue.
The SQL file that is generated contains the failed DDL of objects that could not be re-created by user SYSTEM.
To re-create those objects, you must manually execute the failed DDL after the import finishes.
# 18c環境 imp \'SYS@xxxxxxxx as sysdba\' transport_tablespace=y \ datafiles='/u01/app/oracle/oradata/SJISDB/7676F6B5B0D33571E0534656860A3EB5/datafile/test01.dbf\ ,/u01/app/oracle/oradata/SJISDB/7676F6B5B0D33571E0534656860A3EB5/datafile/test02.dbf'\ file=test_meta.dmp log=imp_test_meta.log Import: Release 18.0.0.0.0 - Production on Tue Sep 25 06:03:32 2018 Version 18.2.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.2.0.0.0 Export file created by EXPORT:V09.02.00 via conventional path About to import transportable tablespace(s) metadata... import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses JA16SJIS character set (possible charset conversion) export client uses JA16SJIS character set (possible charset conversion) IMP-00403: Warning: This import generated a separate SQL file "imp_test_meta_sys.sql" which contains DDL that failed due to a privilege issue. . importing SYS's objects into SYS . importing SYS's objects into SYS . importing AYSHIBAT's objects into AYSHIBAT . . importing table "TBL_A" Import terminated successfully with warnings. $ ls -la imp_test_meta_sys.sql -rw-r--r-- 1 oracle oinstall 0 Sep 25 06:03 imp_test_meta_sys.sql ★0バイト $ cat imp_test_meta_sys.sql $
10. インポート結果の確認(18c)
移行先(18c)のPDBに接続してインポート結果を確認します。
# 18c CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy SHOW CON_NAME; CON_NAME ------------------------------ PDB01 SELECT COUNT(*) FROM TBL_A; COUNT(*) ---------- 1000
11. 表領域の READ WRITE化(18c)
移行先(18c)のPDBの表領域を READ WRITE にします。
-- 18c ALTER TABLESPACE TEST01 READ ONLY; ALTER TABLESPACE TEST02 READ ONLY; 表領域が変更されました。 表領域が変更されました。
12. まとめ
9iR2環境の表領域を18c環境にTTSで移行できたで！彡(ﾟ)(ﾟ)
両バージョンの年月差は実に16年(2002年 vs 2018年)、
バージョンは2倍、威力は100倍、16年の時を経て移行できるTTS最高や！彡(^)(^)
13. 参考情報
マニュアルとサポートドキュメントも参照するんやで彡(ﾟ)(ﾟ)
Oracle9i Database Release 2 プラットフォーム共通 ドキュメント
https://www.oracle.com/technetwork/jp/content/general-082543-ja.html#db
Oracle9i データベース管理者ガイド リリース2（9.2）
部品番号：J06242-01
http://otndnld.oracle.co.jp/document/oracle9i/920/generic/server/J06242-01.pdf
データベース間での表領域のトランスポート（11-34）
MOSドキュメント：トランスポータブル表領域移行手順(KROWN:27536) (ドキュメントID 1709003.1)
MOSドキュメント：IMP-00403 "Warning: This import generated a separate SQL file" Using 12.2 imp Command (ドキュメントID 2298963.1)
MOSドキュメント：異なるバージョンのデータベース間でExport/Importを実行する方法(KROWN:45271) (ドキュメントID 1715793.1)
セミナー「デモとディスカッションで体験するOracle DBトラブル対応・2018/8/22(水)」の開催報告と資料共有
昨日 2018/8/22(水) に、下記セミナーを開催させて頂きました。
ご参加いただいた方々、誠に有難うございました！彡(^)(^)
デモとディスカッションで体験するOracle DBトラブル対応
https://study-oracle-technology-workshop.connpass.com/event/95420/
デモとディスカッションで体験するOracle DBトラブル対応
https://techplay.jp/event/685958
反省点は色々……ITエンジニアと言えど、今時はNote PCを持ち歩く人は
レアですかね。参加者の方々にデモ環境に確実に繋いでもらうなら、
ほぼ全員が持っているスマホ前提でデモを作った方がエエですやね彡(ﾟ)(ﾟ)
次に似たような機会が有れば、Oracle APEX辺りでちょっとした作り込みかなぁ……彡(-)(-)
セミナー資料はSlideshareに公開しています。
ご参考／ご意見下さいませ(｀･ω･)ゞ
(Slideshare)デモとディスカッションで体験するOracle DBトラブル対応
https://www.slideshare.net/shibataayumu/oracle-db-111087832
