« 2021年12月 | トップページ | 2022年4月 »

2022年3月31日 (木)

Difference of Initialization Parameters between 19c (19.3.0.0.0) and 21c (21.3.0.0.0) - including hidden params

Difference of Initialization Parameters between 19c (19.3.0.0.0) and 21c (21.3.0.0.0) - including hidden params

Previously on Mac De Oracle
前回は、21cではお約束のパラメータ増加傾向を確認しました。今回もお約束のパラメータ差分をw

新しいメジャーリリースでたらやっとかないとねw

利用しているSQLなどは、昔のエントリーにサンプルがあるので参考にしてみてください。(うまく動作しないなどあれば、コメント頂けると幸いです。細かく確認してないので)

作成した19cと21cのパラメータ差分は↓↓↓↓↓↓↓
Difference of Initialization Parameters between 19c (19.3.0.0.0) and 21c (21.3.0.0.0)

作成方法は、みなさん知っていると思うので省略してもいいかもしれないですがw 軽めに書いておきます。
19cのパラメータをcsv化して、21cでは外部表として使います(詳細は前述のリンク先エントリーで確認ください)

適当なディレクトリに19cのパラメータのcsvファイルを置いて、ディレクトリオブジェクトを作成したあとに、外部表を作成しています。

[oracle@localhost ~]$ pwd
/home/oracle
[oracle@localhost ~]$ mkdir exttab
[oracle@localhost ~]$
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on 水 3月 30 18:39:26 2022
Version 21.3.0.0.0

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

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
に接続されました。
SYS@ORCLCDB>
SYS@ORCLCDB> !ls -l exttab
合計 2688
-rw-r--r--. 1 oracle oinstall 2749046 3月 30 18:38 19.3.0.0.0.ksppi.csv

SYS@ORCLCDB>
SYS@ORCLCDB>
SYS@ORCLCDB> CREATE DIRECTORY ext_tab AS '/home/oracle/exttab';
SYS@ORCLCDB>
SYS@ORCLCDB> @cre_19c_paramtab
SYS@ORCLCDB>
SYS@ORCLCDB> !cat cre_19c_paramtab.sql
CREATE TABLE ksppi_19_3_0_0_0 (
ksppinm VARCHAR2(80)
,ksppdesc VARCHAR2(255)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tab
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
(
ksppinm
,ksppdesc
)
)
LOCATION (
'19.3.0.0.0.ksppi.csv'
)
)
/

できたみたいなので、外部表を問い合わせてみましょう! 

SYS@ORCLCDB> select * from ksppi_19_3_0_0_0 fetch first 10 rows only;

KSPPINM KSPPDESC
------------------------------------------- -------------------------------------------------------------------
_appqos_qt System Queue time retrieval interval
_appqos_po_multiplier Multiplier for PC performance objective value
_appqos_cdb_setting QoSM CDB Performance Class Setting
_ior_serialize_fault inject fault in the ior serialize code
_shutdown_completion_timeout_mins minutes for shutdown operation to wait for sessions to complete
_inject_startup_fault inject fault in the startup code
_session_modp_list send session's modified parameter list to client
_wait_outlier_detection_enable Enable wait outlier detection module?
_wait_outlier_event_names Wait events to watch for outliers
_wait_outlier_lambda_x1000 Lambda (in thousands) to compute outliers

10行が選択されました。


最後に、htmlファイル形式で 差分を取った結果を出力します :)
簡単ですよね :)

以前作ったものを多少変更しただけ

[oracle@localhost ~]$ cat get_diff.sql
col "19c 19.3.0.0.0" for a17
col "21c 21.3.0.0.0" for a17
set pagesize 10000
set timi off
set feed off
set markup html on spool on
spo param-diff.html
SELECT
CASE
WHEN prev.ksppinm = curr.ksppinm
OR (
prev.ksppinm IS NULL
AND curr.ksppinm IS NOT NULL
)
THEN curr.ksppinm
ELSE prev.ksppinm
END AS ksppinm
,CASE
WHEN prev.ksppinm = curr.ksppinm THEN '○'
WHEN prev.ksppinm IS NOT NULL AND curr.ksppinm IS NULL THEN '○'
ELSE 'n/a'
END AS "19c 19.3.0.0.0"
,CASE
WHEN prev.ksppinm = curr.ksppinm THEN '○'
WHEN prev.ksppinm IS NULL AND curr.ksppinm IS NOT NULL THEN '○'
ELSE 'n/a'
END AS "21c 21.3.0.0.0"
,CASE
WHEN prev.ksppdesc = curr.ksppdesc THEN curr.ksppdesc
WHEN prev.ksppinm IS NULL AND curr.ksppinm IS NOT NULL THEN curr.ksppdesc
WHEN prev.ksppinm iS NOT NULL AND curr.ksppinm IS NULL
THEN prev.ksppdesc
ELSE prev.ksppdesc
END AS kspdesc
,CASE
WHEN prev.ksppdesc = curr.ksppdesc
OR (prev.ksppinm IS NULL AND curr.ksppinm IS NOT NULL)
OR (prev.ksppinm IS NOT NULL AND curr.ksppinm IS NULL)
THEN NULL
ELSE curr.ksppdesc
END AS "New description"
FROM
(
SELECT * FROM x$ksppi WHERE con_id = 0
) curr
FULL OUTER JOIN ksppi_19_3_0_0_0 prev
ON
curr.ksppinm = prev.ksppinm
ORDER BY
1
;
spool off
set feed on
set markup html off

実行するとこんな感じで HTML ファイルの出来上がり!!

SYS@ORCLCDB> @get_diff.sql
...略...
are XML DB events enabled
</td>
<td>
 
</td>
</tr>
</table>
<p>
SYS@ORCLCDB>


さて、次は、再び、実行計画というレントゲンのお話に戻りますかw 



関連エントリー
21cの初期化パラメータ数や隠しパラメータ数変化
Difference of Initialization Parameters between 11gR2 (11.2.0.4.0) and 19c (19.3.0.0.0) - including hidden params
19cの初期化パラメータ数や隠しパラメータ数などどう変化したのか、久々に確認してみた
Difference of Initialization Parameters between 11g r1 (11.1.0.6.0) and 12c r1 (12.1.0.1.0) - including hidden params
Difference of Initialization Parameters between 11g and 12c #2

| | コメント (0)

21cの初期化パラメータ数や隠しパラメータ数変化

もうお約束になった感じですが、
21c環境を作ったので、お約束のパラメータ数差分確認を。増えることはあっても減ることは無いですよね。

09:31:44 SYS@ORCLCDB> select banner_full from v$version;

BANNER_FULL
------------------------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0


いい感じで、隠しパラメータが増加してますよね。もう手に負えない数ですけども。 それ以外は、びっくりするほどの増加ではないですね。増えてはいますが。

CATEGORY                                 Num Of Parameters
---------------------------------------- -----------------
1. Single underscore parameters 5486
2. Double underscore parameters 32
3. Non hidden parameters 479
-----------------
Total 5997

リリース毎の各パラメータ数の変化をまとめたグラフ
20220330-225912


さて、次回は、19cと21cのパラメータ差分。(新バージョンリリースされたあとのルーティーンになってきたw



関連エントリー
Difference of Initialization Parameters between 11gR2 (11.2.0.4.0) and 19c (19.3.0.0.0) - including hidden params
19cの初期化パラメータ数や隠しパラメータ数などどう変化したのか、久々に確認してみた
Difference of Initialization Parameters between 11g r1 (11.1.0.6.0) and 12c r1 (12.1.0.1.0) - including hidden params
Difference of Initialization Parameters between 11g and 12c #2

| | コメント (0)

2022年3月30日 (水)

Oracle Database 21c EE for Linux on VirtualBox 6.1

諸事情w によりまだやってなかった Oracle Database 21c お遊び環境構築しました。今の所 VirtualBox向けrebuildなのはなさそうなので、400億年振りにw Linuxインストールから実施w とはいっても、インストール含め楽すぎて良いですね。(preinstallな方しか試して無いですが)

これで、またパラメータ数比較もできる:)


環境
VirtualBox (現時点の最新版 6.1 for macOS intel)
https://www.virtualbox.org/

Oracle Linux Installation Media (今回は8.4を使った)
https://yum.oracle.com/oracle-linux-isos.html

Oracle Database 21c (21.3) for Linux x86-64 (RPM)
https://www.oracle.com/database/technologies/oracle21c-linux-downloads.html

VirtualBoxのバージョン確認

discus-mother:~ oracle$ VBoxManage -v
6.1.32r149290

Oracle Linux 8.4のVM作成(ここは本題じゃないので省略)

Oracle Linuxのバージョン確認

[master@localhost ~]$ uname -rv
5.4.17-2102.201.3.el8uek.x86_64 #2 SMP Fri Apr 23 09:05:57 PDT 2021
[master@localhost ~]$
[master@localhost ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux release 8.4 (Ootpa)
[master@localhost ~]$ cat /etc/oracle-release
Oracle Linux Server release 8.4
[master@localhost ~]$


Oracle Database 21c EE 21.3.0.0.0のインストール

大人の事情w インストールからやるのかなり久々な上に、rpmでやるのは初!!!! しかも環境変数のあたり、ちょいと忘れているので怪しい(でツッコミ歓迎w)

では、first attempting... rpmは事前にダウンロード済み


master@localhost ~]$ ll -h oracle-database-ee-21c-1.0-1.ol8.x86_64.rpm
-rw-r--r--. 1 master master 2.6G 3月 29 03:07 oracle-database-ee-21c-1.0-1.ol8.x86_64.rpm

Install

[master@localhost ~]$ sudo dnf install oracle-database-ee-21c-1.0-1.ol8.x86_64.rpm
メタデータの期限切れの最終確認: 1:30:07 時間前の 2022年03月29日 02時07分01秒 に実施しました。
依存関係が解決しました。
======================================================================================================================
パッケージ アーキテクチャー バージョン リポジトリー サイズ
======================================================================================================================
インストール:
oracle-database-ee-21c x86_64 1.0-1 @commandline 2.6 G
依存関係のインストール:
compat-openssl10 x86_64 1:1.0.2o-3.el8 ol8_appstream 1.1 M
ksh x86_64 20120801-254.0.1.el8 ol8_appstream 927 k
libnsl x86_64 2.28-151.0.1.el8 ol8_baseos_latest 102 k
oracle-database-preinstall-21c x86_64 1.0-1.el8 ol8_appstream 30 k

トランザクションの概要
======================================================================================================================
インストール 5 パッケージ

合計サイズ: 2.6 G
インストール後のサイズ: 7.1 G
これでよろしいですか? [y/N]: y
パッケージのダウンロード:
[SKIPPED] libnsl-2.28-151.0.1.el8.x86_64.rpm: Already downloaded
[SKIPPED] compat-openssl10-1.0.2o-3.el8.x86_64.rpm: Already downloaded
[SKIPPED] ksh-20120801-254.0.1.el8.x86_64.rpm: Already downloaded
[SKIPPED] oracle-database-preinstall-21c-1.0-1.el8.x86_64.rpm: Already downloaded
----------------------------------------------------------------------------------------------------------------
合計 217 MB/s | 2.2 MB 00:00
トランザクションの確認を実行中
トランザクションの確認に成功しました。
トランザクションのテストを実行中
トランザクションのテストに成功しました。
トランザクションを実行中
準備 : 1/1
インストール中 : ksh-20120801-254.0.1.el8.x86_64 1/5
scriptletの実行中: ksh-20120801-254.0.1.el8.x86_64 1/5
インストール中 : compat-openssl10-1:1.0.2o-3.el8.x86_64 2/5
scriptletの実行中: compat-openssl10-1:1.0.2o-3.el8.x86_64 2/5
/sbin/ldconfig: /etc/ld.so.conf.d/kernel-5.4.17-2102.201.3.el8uek.x86_64.conf:6: hwcap directive ignored

インストール中 : libnsl-2.28-151.0.1.el8.x86_64 3/5
インストール中 : oracle-database-preinstall-21c-1.0-1.el8.x86_64 4/5
scriptletの実行中: oracle-database-ee-21c-1.0-1.x86_64 5/5
インストール中 : oracle-database-ee-21c-1.0-1.x86_64 5/5
scriptletの実行中: oracle-database-ee-21c-1.0-1.x86_64 5/5
[INFO] Executing post installation scripts...
[INFO] Oracle home installed successfully and ready to be configured.
To configure a sample Oracle Database you can execute the following service
configuration script as root: /etc/init.d/oracledb_ORCLCDB-21c configure

scriptletの実行中: oracle-database-preinstall-21c-1.0-1.el8.x86_64 5/5
scriptletの実行中: oracle-database-ee-21c-1.0-1.x86_64 5/5
/sbin/ldconfig: /etc/ld.so.conf.d/kernel-5.4.17-2102.201.3.el8uek.x86_64.conf:6: hwcap directive ignored

検証 : libnsl-2.28-151.0.1.el8.x86_64 1/5
検証 : compat-openssl10-1:1.0.2o-3.el8.x86_64 2/5
検証 : ksh-20120801-254.0.1.el8.x86_64 3/5
検証 : oracle-database-preinstall-21c-1.0-1.el8.x86_64 4/5
検証 : oracle-database-ee-21c-1.0-1.x86_64 5/5

インストール済み:
compat-openssl10-1:1.0.2o-3.el8.x86_64 ksh-20120801-254.0.1.el8.x86_64
libnsl-2.28-151.0.1.el8.x86_64 oracle-database-ee-21c-1.0-1.x86_64
oracle-database-preinstall-21c-1.0-1.el8.x86_64

完了しました!
[master@localhost ~]$


Configure
今回は、preinstallを構成します。カスタムな構成は今の所必要ないので。

[root@localhost ~]# cd /etc/init.d
[root@localhost init.d]# ll
合計 40
-rw-r--r--. 1 root root 1161 5月 19 2021 README
-rw-r--r--. 1 root root 18434 2月 15 2021 functions
-rwx------. 1 root root 1281 7月 13 2021 oracle-database-preinstall-21c-firstboot
-r-xr-xr-x. 1 root root 11307 7月 27 2021 oracledb_ORCLCDB-21c
[root@localhost init.d]#
[root@localhost ~]# /etc/init.d/oracledb_ORCLCDB-21c configure
Configuring Oracle Database ORCLCDB.
DB操作の準備
8%完了
データベース・ファイルのコピー中
31%完了
Oracleインスタンスの作成および起動中
32%完了
36%完了
40%完了
43%完了
46%完了
データベース作成の完了
51%完了
54%完了
プラガブル・データベースの作成
58%完了
77%完了
構成後アクションの実行
100%完了
データベースの作成が完了しました。詳細は、次の場所にあるログ・ファイルを参照してください:
/opt/oracle/cfgtoollogs/dbca/ORCLCDB。
データベース情報:
グローバル・データベース名:ORCLCDB
システム識別子(SID):ORCLCDB
詳細はログ・ファイル"/opt/oracle/cfgtoollogs/dbca/ORCLCDB/ORCLCDB.log"を参照してください。

Database configuration completed successfully. The passwords were auto generated,
you must change them by connecting to the database using 'sqlplus / as sysdba' as the oracle user.
[root@localhost ~]#


おおおおおおーーー、起動してるーーーー!! 21c 楽だw
20220329-182633

いろいろ見ていくと、oracleユーザーや必要なグループの作成等はおこなわれているが、なぜか、home directoryが存在しなかった(ご愛嬌ってところかw)

ということで、なぜか存在しなかったホームディレクトリ作成とパスワードを変更して、.bash_profileに、ほぼ忘れかけてた環境変数設定など。(漏れてないかな。。w)

[root@localhost ~]# cat /etc/passwd | grep oracle
oracle:x:54321:54321::/home/oracle:/bin/bash
[root@localhost ~]#
[root@localhost ~]# mkdir -p /home/oracle
[root@localhost ~]# chown oracle:oinstall /home/oracle
[root@localhost ~]# passwd oracle
ユーザー oracle のパスワードを変更。
新しいパスワード:
新しいパスワードを再入力してください:
passwd: すべての認証トークンが正しく更新できました。
[root@localhost ~]# su - oracle
[oracle@localhost ~]$ cat .bash_profile
export ORACLE_BASE=/opt/oracle
export ORACLE_BASE_CONFIG=$ORACLE_BASE/dbs
export ORACLE_HOME=$ORACLE_BASE/product/21c/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=ORCLCDB
export NLS_LANG=Japanese_Japan.UTF8
export ORA_NLS10=$ORACLE_HOME/nls/data
[oracle@localhost ~]$ . .bash_profile
[oracle@localhost ~]$


そして、忘れる前にやっときましょうw glogin.sqlでSQL*Plusの環境を整えると。

[oracle@localhost ~]$ cat $ORACLE_HOME/sqlplus/admin/glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle. All Rights Reserved.
--
-- NAME
-- glogin.sql
--
-- DESCRIPTION
-- SQL*Plus global login "site profile" file
--
-- Add any SQL*Plus commands here that are to be executed when a
-- user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
-- This script is automatically run
--
set linesize 300
set pagesize 1000
set timi on
set time on
set tab off
define _EDITOR=vi
set sqlp "_user""@""_connect_identifier> "
[oracle@localhost ~]$

SQL*Plusで接続!!!

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on 火 3月 29 06:14:52 2022
Version 21.3.0.0.0

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


Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
に接続されました。
06:14:52 SYS@ORCLCDB> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 READ WRITE NO
06:14:57 SYS@ORCLCDB>
06:14:58 SYS@ORCLCDB>
06:14:58 SYS@ORCLCDB>
06:15:26 SYS@ORCLCDB> select banner_full from v$version;

BANNER_FULL
-------------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0


経過: 00:00:00.00
06:15:40 SYS@ORCLCDB> exit

Lisenerの状態も見ておきましょう

[oracle@localhost ~]$ lsnrctl status

LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 29-3月 -2022 07:09:29

Copyright (c) 1991, 2021, Oracle. All rights reserved.

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))に接続中
リスナーのステータス
------------------------
別名 LISTENER
バージョン TNSLSNR for Linux: Version 21.0.0.0.0 - Production
開始日 29-3月 -2022 03:57:55
稼働時間 0 日 3 時間 11 分 34 秒
トレース・レベル off
セキュリティ ON: Local OS Authentication
SNMP OFF
パラメータ・ファイル /opt/oracle/homes/OraDBHome21cEE/network/admin/listener.ora
ログ・ファイル /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
リスニング・エンドポイントのサマリー...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=localhost)(PORT=5500))
(Security=(my_wallet_directory=/opt/oracle/admin/ORCLCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))
サービスのサマリー...
サービス"ORCLCDB"には、1件のインスタンスがあります。
インスタンス"ORCLCDB"、状態READYには、このサービスに対する1件のハンドラがあります...
サービス"ORCLCDBXDB"には、1件のインスタンスがあります。
インスタンス"ORCLCDB"、状態READYには、このサービスに対する1件のハンドラがあります...
サービス"db57eedbd976b81ce055000000000001"には、1件のインスタンスがあります。
インスタンス"ORCLCDB"、状態READYには、このサービスに対する1件のハンドラがあります...
サービス"orclpdb1"には、1件のインスタンスがあります。
インスタンス"ORCLCDB"、状態READYには、このサービスに対する1件のハンドラがあります...
コマンドは正常に終了しました。
[oracle@localhost ~]$

停止してみましょう!

おっと、systemctlで起動停止というところまではやってくれないのか... ということで、自分で止めましょうw あとで作ろう。。w

[master@localhost ~]$ sudo service oracle stop
[sudo] master のパスワード:
Redirecting to /bin/systemctl stop oracle.service
Failed to stop oracle.service: Unit oracle.service not loaded.
[master@localhost ~]$ sudo su - oracle
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on 火 3月 29 07:16:55 2022
Version 21.3.0.0.0

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

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
に接続されました。
07:16:55 SYS@ORCLCDB> shutdown immediate
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。
07:17:01 SYS@ORCLCDB>

インストール楽すぎて、ワロタ。



一気に桜が咲いた。がいつまでもつのだろう。今年は東北方面も開花が早そうで、GWはもう葉桜だろうな。
では

| | コメント (0)

2022年3月26日 (土)

小ネタ : Live SQL! でもexplain plan を使う

Oracle Live SQL

でauto traceはできないけど、explain plan for文で解析した後に

 

SELECT plan_table_output FROMTABLE(DBMS_XPLAN.DISPLAY(upper('plan_table'),null,'typical'));


を実行すればできます!
新たな気づき :)

PLAN_TABLEは大文字にするところがポイントです!

ちなみに、

誰がどんな名前のペットを飼っているのかな? 解答編 / JPOUG Advent Calendar Day 23 
のSQL文は全て実行できた!
ただ、まだ 21cではなくて、19cですね 21c版出るのだろうか...

20220326-114537

| | コメント (0)

実行計画は、SQL文のレントゲン写真だ! No.31 - TEMP TABLE TRANSFORMATION LOAD AS SELECT (CURSOR DURATION MEMORY)

さて、実行計画のバリエーションの数だけ、レントゲンはありますよー(まだネタには余裕があるw)

ということで、今回は、12c前後で変化したところを見ておきたいと思います。

タイトルにも書いた

TEMP TABLE TRANSFORMATION
LOAD AS SELECT (CURSOR DURATION MEMORY)

は、12c以降のリリースから見られるWITH CTEで繰り返し利用されることが自明で性能改善につながると想定される場合に、一時表にマテリアライズされたときのオペレーションですよね。
(みなさんご存知だとおもいます。

12c以降のリリースしか利用したことのない方は、気づかないと思いますが、11gまで少々違いました。

TEMP TABLE TRANSFORMATION
  LOAD AS SELECT


違いといっても、CURSOR DURATION MEMORY があるかないかなのですがw、細かい改善の一つでだよね。と。


ちょうど良いネタなので、昨年末のAdvent CalendarのSQL文と実行計画でバージョン間のレントゲン写真の差を確認しておきましょう:)

まず、一つまえの 19cから。
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Id=2のオペレーションは、LOAD AS SELECT (CURSOR DURATION MEMORY) ですね。

もう一点、Predicate Informationセクションには、/*+ CACHE ("T1") */ というヒントが内部的に利用されていますね。ふーむ。
また、アダプティブな挙動もレポートされていないようですね。計画自体もindex only scanですし、まあ、想定通りというところですね。

参考までに、OPTIMIZER_DYNAMIC_SAMPLING = 2 と。デフォルトのままです。

CURSOR DURATION MEMORYの挙動については、
SQLチューニング・ガイド cursor-duration一時表

にあるように、シリアル実行では、PGAを利用するようですね。パラレルだと違うのか... でいずれもメモリ上に乗らなくなると、一時セグメントがストレージ上に確保されると記載されているのでTEMP表領域が利用されそうですね。
メモリに余裕があり、かつ、繰り返し参照されるケースでは効果はありそうですよね。ストレージに落ちてしまうと、direct path read from tempが発生するでしょうし、ストレージを繰り返しアクセスするかしないかの違いは大きいかも。
とはいえ、WITH句のCTEで性能改善を狙うケースでポイントになるところは同じなので、その点は忘れないようにしておきたいところですね。(例外は巨大なSQLで可読性向上を目的とした場合ぐらい)

Execution Plan
----------------------------------------------------------
Plan hash value: 3964084889

----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 553 | 12 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6640_6FF953 | | | | |
| 3 | WINDOW SORT | | 7 | 119 | 8 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 7 | 119 | 8 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 7 | 77 | 4 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | SYS_C0012896 | 4 | 24 | 1 (0)| 00:00:01 |
|* 7 | INDEX FAST FULL SCAN | SYS_C0012900 | 2 | 10 | 1 (0)| 00:00:01 |
|* 8 | INDEX FAST FULL SCAN | SYS_C0012898 | 1 | 6 | 1 (0)| 00:00:01 |
| 9 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6641_6FF953 | | | | |
| 10 | WINDOW SORT | | 7 | 371 | 2 (0)| 00:00:01 |
|* 11 | FILTER | | | | | |
| 12 | VIEW | | 7 | 371 | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6640_6FF953 | 7 | 119 | 2 (0)| 00:00:01 |
|* 14 | VIEW | | 7 | 210 | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6640_6FF953 | 7 | 119 | 2 (0)| 00:00:01 |
|* 16 | VIEW | | 7 | 210 | 2 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6640_6FF953 | 7 | 119 | 2 (0)| 00:00:01 |
| 18 | VIEW | | 7 | 553 | 2 (0)| 00:00:01 |
| 19 | WINDOW SORT | | 7 | 371 | 2 (0)| 00:00:01 |
|* 20 | FILTER | | | | | |
| 21 | VIEW | | 7 | 371 | 2 (0)| 00:00:01 |
| 22 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6641_6FF953 | 7 | 217 | 2 (0)| 00:00:01 |
|* 23 | VIEW | | 1 | 30 | 1 (0)| 00:00:01 |
| 24 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6641_6FF953 | 7 | 217 | 2 (0)| 00:00:01 |
|* 25 | VIEW | | 1 | 30 | 1 (0)| 00:00:01 |
| 26 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6641_6FF953 | 7 | 217 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - filter("NAME"='Scott' AND "ANIMALS"."KIND"='Snake' OR "NAME"='Steve' AND "ANIMALS"."KIND"<>'Dog' AND
"ANIMALS"."KIND"<>'Snake' OR "NAME"='Hiro' AND "ANIMALS"."KIND"<>'Turtle' AND "ANIMALS"."KIND"<>'Snake' OR
"NAME"='Larry' AND "ANIMALS"."KIND"<>'Snake')
8 - filter(("OWNERS"."NAME"='Hiro' AND "PETS"."NAME"='Tiger' OR "OWNERS"."NAME"<>'Hiro' AND
"PETS"."NAME"<>'Tiger') AND ("NAME"='Wendy' AND "KIND"='Dog' OR "NAME"='Tiger' AND "KIND"<>'Dog' AND
"KIND"<>'Turtle' OR "NAME"='Lisa' AND "KIND"<>'Snake' AND "KIND"<>'Dog' OR "NAME"='Taro' AND "KIND"<>'Dog'))
11 - filter("NUM_OF_ROWS"=1 OR "NUM_OF_ROWS">1 AND NOT EXISTS (SELECT 0 FROM (SELECT /*+ CACHE ("T1") */
"C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3" "ANIMAL_KIND" FROM
"SYS"."SYS_TEMP_0FD9D6640_6FF953" "T1") "UNKNOWN_PET_OWNERS" WHERE "PET_NAME"=:B1 AND "NUM_OF_ROWS"=1) AND
NOT EXISTS (SELECT 0 FROM (SELECT /*+ CACHE ("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2"
"PET_NAME","C3" "ANIMAL_KIND" FROM "SYS"."SYS_TEMP_0FD9D6640_6FF953" "T1") "UNKNOWN_PET_OWNERS" WHERE
"ANIMAL_KIND"=:B2 AND "NUM_OF_ROWS"=1))
14 - filter("PET_NAME"=:B1 AND "NUM_OF_ROWS"=1)
16 - filter("ANIMAL_KIND"=:B1 AND "NUM_OF_ROWS"=1)
20 - filter("NUM_OF_ROWS"=1 OR "NUM_OF_ROWS">1 AND NOT EXISTS (SELECT 0 FROM (SELECT /*+ CACHE ("T1") */
"C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3" "ANIMAL_KIND" FROM
"SYS"."SYS_TEMP_0FD9D6641_6FF953" "T1") "TEMP_PET_OWNERS" WHERE "PET_NAME"=:B1 AND "NUM_OF_ROWS"=1) AND NOT
EXISTS (SELECT 0 FROM (SELECT /*+ CACHE ("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3"
"ANIMAL_KIND" FROM "SYS"."SYS_TEMP_0FD9D6641_6FF953" "T1") "TEMP_PET_OWNERS" WHERE "ANIMAL_KIND"=:B2 AND
"NUM_OF_ROWS"=1))
23 - filter("PET_NAME"=:B1 AND "NUM_OF_ROWS"=1)
25 - filter("ANIMAL_KIND"=:B1 AND "NUM_OF_ROWS"=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
49 consistent gets
0 physical reads
0 redo size
922 bytes sent via SQL*Net to client
2090 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
4 rows processed

では次、12R1の場合です。(12R2は19cと同じなので省略)
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

基本的にデフォルト設定のままなので、 OPTIMIZER_DYNAMIC_SAMPLING = 2 でもろもろ止めたりしていませんw

12cR2以降との違いの1つめ!
12cR1では、CURSOR DURATION MEMORY オペレーションがありません!! 

12cR2以降との違いの2つめ!
/*+ CACHE ("T1") */ではなく、/*+ CACHE_TEMP_TABLE ("T1") */ という一時表専用のヒントが担っている点。
CACHE_TEMP_TABLEヒントって解説がないヒントなのですが、12cR2以降はCACHEヒントという解説のある通常の表と同じヒントに置き換えられいますね。一時表だけ特別なわけではないので統一したのでしょうか(中の人のみぞ知るw)

Note部分にもでてますが、動的統計とプランディレクティブが動いてますね。再起コールが減らないのもその影響のようです。19cの挙動とは興味深い違いですね。データ量が多くなった場合にどう変化するのかなという気はしますがw

ちなみに、この挙動は、11cR2とくらべて、プランディレクティブが無い以外の挙動は同じ。


Execution Plan
----------------------------------------------------------
Plan hash value: 3787387246

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 448 | 13 (8)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6647_566AED | | | | |
| 3 | WINDOW SORT | | 8 | 136 | 8 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 8 | 136 | 8 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 8 | 88 | 4 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | SYS_C0014925 | 4 | 24 | 1 (0)| 00:00:01 |
|* 7 | INDEX FAST FULL SCAN | SYS_C0014929 | 2 | 10 | 1 (0)| 00:00:01 |
|* 8 | INDEX FAST FULL SCAN | SYS_C0014927 | 1 | 6 | 1 (0)| 00:00:01 |
| 9 | LOAD AS SELECT | SYS_TEMP_0FD9D6648_566AED | | | | |
| 10 | WINDOW SORT | | 7 | 371 | 2 (0)| 00:00:01 |
|* 11 | FILTER | | | | | |
| 12 | VIEW | | 7 | 371 | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6647_566AED | 7 | 119 | 2 (0)| 00:00:01 |
|* 14 | VIEW | | 1 | 30 | 1 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6647_566AED | 7 | 119 | 2 (0)| 00:00:01 |
|* 16 | VIEW | | 1 | 30 | 1 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6647_566AED | 7 | 119 | 2 (0)| 00:00:01 |
| 18 | SORT ORDER BY | | 7 | 448 | 3 (34)| 00:00:01 |
|* 19 | FILTER | | | | | |
| 20 | VIEW | | 7 | 448 | 2 (0)| 00:00:01 |
| 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6648_566AED | 7 | 217 | 2 (0)| 00:00:01 |
|* 22 | VIEW | | 7 | 210 | 2 (0)| 00:00:01 |
| 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6648_566AED | 7 | 217 | 2 (0)| 00:00:01 |
|* 24 | VIEW | | 7 | 210 | 2 (0)| 00:00:01 |
| 25 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6648_566AED | 7 | 217 | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - filter("OWNERS"."NAME"='Scott' AND "ANIMALS"."KIND"='Snake' OR "OWNERS"."NAME"='Steve'
AND "ANIMALS"."KIND"<>'Dog' AND "ANIMALS"."KIND"<>'Snake' OR "OWNERS"."NAME"='Hiro' AND
"ANIMALS"."KIND"<>'Turtle' AND "ANIMALS"."KIND"<>'Snake' OR "OWNERS"."NAME"='Larry' AND
"ANIMALS"."KIND"<>'Snake')
8 - filter(("OWNERS"."NAME"='Hiro' AND "PETS"."NAME"='Tiger' OR "OWNERS"."NAME"<>'Hiro' AND
"PETS"."NAME"<>'Tiger') AND ("PETS"."NAME"='Wendy' AND "ANIMALS"."KIND"='Dog' OR
"PETS"."NAME"='Tiger' AND "ANIMALS"."KIND"<>'Dog' AND "ANIMALS"."KIND"<>'Turtle' OR
"PETS"."NAME"='Lisa' AND "ANIMALS"."KIND"<>'Snake' AND "ANIMALS"."KIND"<>'Dog' OR
"PETS"."NAME"='Taro' AND "ANIMALS"."KIND"<>'Dog'))
11 - filter("NUM_OF_ROWS"=1 OR "NUM_OF_ROWS">1 AND NOT EXISTS (SELECT 0 FROM (SELECT /*+
CACHE_TEMP_TABLE ("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3"
"ANIMAL_KIND" FROM "SYS"."SYS_TEMP_0FD9D6647_566AED" "T1") "PET_OWNER_UNKNOWN" WHERE
"PET_NAME"=:B1 AND "NUM_OF_ROWS"=1) AND NOT EXISTS (SELECT 0 FROM (SELECT /*+
CACHE_TEMP_TABLE ("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3"
"ANIMAL_KIND" FROM "SYS"."SYS_TEMP_0FD9D6647_566AED" "T1") "PET_OWNER_UNKNOWN" WHERE
"ANIMAL_KIND"=:B2 AND "NUM_OF_ROWS"=1))
14 - filter("PET_NAME"=:B1 AND "NUM_OF_ROWS"=1)
16 - filter("ANIMAL_KIND"=:B1 AND "NUM_OF_ROWS"=1)
19 - filter("NUM_OF_ROWS"=1 OR "NUM_OF_ROWS">1 AND NOT EXISTS (SELECT 0 FROM (SELECT /*+
CACHE_TEMP_TABLE ("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3"
"ANIMAL_KIND" FROM "SYS"."SYS_TEMP_0FD9D6648_566AED" "T1") "PET_OWNERS" WHERE "PET_NAME"=:B1
AND "NUM_OF_ROWS"=1) AND NOT EXISTS (SELECT 0 FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */
"C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3" "ANIMAL_KIND" FROM
"SYS"."SYS_TEMP_0FD9D6648_566AED" "T1") "PET_OWNERS" WHERE "ANIMAL_KIND"=:B2 AND
"NUM_OF_ROWS"=1))
22 - filter("PET_NAME"=:B1 AND "NUM_OF_ROWS"=1)
24 - filter("ANIMAL_KIND"=:B1 AND "NUM_OF_ROWS"=1)

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 5 Sql Plan Directives used for this statement

Statistics
----------------------------------------------------------
4 recursive calls
36 db block gets
126 consistent gets
2 physical reads
1160 redo size
811 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
4 rows processed


そして、最後は、私の環境で最も古いOracle 11.1.0.7

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

わかっちゃいたけど、オプティマイザの進化が見えて嬉しいですねw

なかなか苦しい実行計画ですね。これは。。。w

Index only Scanにはなっていますが、マテリアライズする部分の実行計画が.. オプティマイザありがとう。(主に最新版のほうですけどw)

本題にもどると、このリリースではCURSOR DURATION MEMORYがないのは当然ですが、CACHE_TEMP_TABLEヒントが利用されているという点に関しては、12cR1と同じ
そして、アダプティブな挙動も一切ない自体の実行計画ですね。ここには戻りたくないですよね。みなさんw

アダプティブな挙動はないにしても、再起コールがおおいし、実行計画が行けてないのでヒントで補正してみたくなりますよねw

Plan hash value: 3067991639

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 448 | 14 (15)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | WINDOW SORT | | 8 | 240 | 9 (12)| 00:00:01 |
| 4 | CONCATENATION | | | | | |
| 5 | NESTED LOOPS | | 7 | 210 | 7 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 9 | 225 | 7 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 5 | 100 | 4 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 7 | 105 | 4 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 4 | 40 | 1 (0)| 00:00:01 |
| 10 | INDEX FULL SCAN | SYS_C009964 | 4 | 20 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | SYS_C009964 | 1 | 5 | 0 (0)| 00:00:01 |
|* 12 | INDEX FAST FULL SCAN| SYS_C009962 | 2 | 10 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | SYS_C009962 | 1 | 5 | 0 (0)| 00:00:01 |
|* 14 | INDEX FAST FULL SCAN | SYS_C009960 | 2 | 10 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | SYS_C009960 | 1 | 5 | 0 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 1 | 30 | 1 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 2 | 50 | 1 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 20 | 0 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 15 | 0 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 10 | 0 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | SYS_C009962 | 1 | 5 | 0 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | SYS_C009960 | 1 | 5 | 0 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | SYS_C009962 | 4 | 20 | 0 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | SYS_C009960 | 4 | 20 | 0 (0)| 00:00:01 |
|* 25 | INDEX FULL SCAN | SYS_C009964 | 2 | 10 | 1 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | SYS_C009964 | 1 | 5 | 0 (0)| 00:00:01 |
| 27 | LOAD AS SELECT | | | | | |
| 28 | WINDOW SORT | | 7 | 217 | 2 (0)| 00:00:01 |
|* 29 | FILTER | | | | | |
| 30 | VIEW | | 7 | 217 | 2 (0)| 00:00:01 |
| 31 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660C_11BDA4 | 7 | 210 | 2 (0)| 00:00:01 |
|* 32 | VIEW | | 1 | 30 | 1 (0)| 00:00:01 |
| 33 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660C_11BDA4 | 7 | 210 | 2 (0)| 00:00:01 |
|* 34 | VIEW | | 1 | 30 | 1 (0)| 00:00:01 |
| 35 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660C_11BDA4 | 7 | 210 | 2 (0)| 00:00:01 |
| 36 | SORT ORDER BY | | 7 | 448 | 3 (34)| 00:00:01 |
|* 37 | FILTER | | | | | |
| 38 | VIEW | | 7 | 448 | 2 (0)| 00:00:01 |
| 39 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_11BDA4 | 7 | 217 | 2 (0)| 00:00:01 |
|* 40 | VIEW | | 7 | 210 | 2 (0)| 00:00:01 |
| 41 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_11BDA4 | 7 | 217 | 2 (0)| 00:00:01 |
|* 42 | VIEW | | 7 | 210 | 2 (0)| 00:00:01 |
| 43 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_11BDA4 | 7 | 217 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

11 - access("ANIMALS"."KIND"="ANIMALS"."KIND")
12 - filter("PETS"."NAME"='Wendy' AND "ANIMALS"."KIND"='Dog' OR "PETS"."NAME"='Tiger' AND
"ANIMALS"."KIND"<>'Dog' AND "ANIMALS"."KIND"<>'Turtle' OR "PETS"."NAME"='Lisa' AND
"ANIMALS"."KIND"<>'Snake' AND "ANIMALS"."KIND"<>'Dog' OR "PETS"."NAME"='Taro' AND
"ANIMALS"."KIND"<>'Dog')
13 - access("PETS"."NAME"="PETS"."NAME")
filter("PETS"."NAME"<>'Tiger')
14 - filter("OWNERS"."NAME"='Scott' AND "ANIMALS"."KIND"='Snake' OR "OWNERS"."NAME"='Steve' AND
"ANIMALS"."KIND"<>'Dog' AND "ANIMALS"."KIND"<>'Snake' OR "OWNERS"."NAME"='Hiro' AND
"ANIMALS"."KIND"<>'Turtle' AND "ANIMALS"."KIND"<>'Snake' OR "OWNERS"."NAME"='Larry' AND
"ANIMALS"."KIND"<>'Snake')
15 - access("OWNERS"."NAME"="OWNERS"."NAME")
filter("OWNERS"."NAME"<>'Hiro')
21 - access("PETS"."NAME"='Tiger')
22 - access("OWNERS"."NAME"='Hiro')
filter(LNNVL("OWNERS"."NAME"<>'Hiro') OR LNNVL("PETS"."NAME"<>'Tiger'))
23 - access("PETS"."NAME"="PETS"."NAME")
24 - access("OWNERS"."NAME"="OWNERS"."NAME")
25 - filter("OWNERS"."NAME"='Scott' AND "ANIMALS"."KIND"='Snake' OR "OWNERS"."NAME"='Steve' AND
"ANIMALS"."KIND"<>'Dog' AND "ANIMALS"."KIND"<>'Snake' OR "OWNERS"."NAME"='Hiro' AND
"ANIMALS"."KIND"<>'Turtle' AND "ANIMALS"."KIND"<>'Snake' OR "OWNERS"."NAME"='Larry' AND
"ANIMALS"."KIND"<>'Snake')
26 - access("ANIMALS"."KIND"="ANIMALS"."KIND")
filter("PETS"."NAME"='Wendy' AND "ANIMALS"."KIND"='Dog' OR "PETS"."NAME"='Tiger' AND
"ANIMALS"."KIND"<>'Dog' AND "ANIMALS"."KIND"<>'Turtle' OR "PETS"."NAME"='Lisa' AND
"ANIMALS"."KIND"<>'Snake' AND "ANIMALS"."KIND"<>'Dog' OR "PETS"."NAME"='Taro' AND
"ANIMALS"."KIND"<>'Dog')
29 - filter("NUM_OF_ROWS"=1 OR "NUM_OF_ROWS">1 AND NOT EXISTS (SELECT 0 FROM (SELECT /*+
CACHE_TEMP_TABLE ("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3"
"ANIMAL_KIND" FROM "SYS"."SYS_TEMP_0FD9D660C_11BDA4" "T1") "PET_OWNER_UNKNOWN" WHERE
"PET_NAME"=:B1 AND "NUM_OF_ROWS"=1) AND NOT EXISTS (SELECT 0 FROM (SELECT /*+ CACHE_TEMP_TABLE
("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3" "ANIMAL_KIND" FROM
"SYS"."SYS_TEMP_0FD9D660C_11BDA4" "T1") "PET_OWNER_UNKNOWN" WHERE "ANIMAL_KIND"=:B2 AND
"NUM_OF_ROWS"=1))
32 - filter("PET_NAME"=:B1 AND "NUM_OF_ROWS"=1)
34 - filter("ANIMAL_KIND"=:B1 AND "NUM_OF_ROWS"=1)
37 - filter("NUM_OF_ROWS"=1 OR "NUM_OF_ROWS">1 AND NOT EXISTS (SELECT 0 FROM (SELECT /*+
CACHE_TEMP_TABLE ("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3"
"ANIMAL_KIND" FROM "SYS"."SYS_TEMP_0FD9D660D_11BDA4" "T1") "PET_OWNERS" WHERE "PET_NAME"=:B1 AND
"NUM_OF_ROWS"=1) AND NOT EXISTS (SELECT 0 FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0"
"NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3" "ANIMAL_KIND" FROM
"SYS"."SYS_TEMP_0FD9D660D_11BDA4" "T1") "PET_OWNERS" WHERE "ANIMAL_KIND"=:B2 AND "NUM_OF_ROWS"=1))
40 - filter("PET_NAME"=:B1 AND "NUM_OF_ROWS"=1)
42 - filter("ANIMAL_KIND"=:B1 AND "NUM_OF_ROWS"=1)

統計
----------------------------------------------------------
4 recursive calls
16 db block gets
85 consistent gets
2 physical reads
1156 redo size
807 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
4 rows processed


アダプティブな挙動がないにしても、再起コールがおおいし、実行計画が行けてないのでヒントで補正してみたくなりますよねw
NO_EXPANDヒントでCONCATENATIONを抑止してみましたw
それでもイマイチだ。古いオプティマイザとの挙動の違いをみると、ほんと、最新版のオプテマイザの良さが身にしみますw

Plan hash value: 2841796482

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 448 | 13 (8)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | WINDOW SORT | | 7 | 210 | 8 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 7 | 210 | 8 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 12 | 300 | 8 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 7 | 140 | 4 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 7 | 105 | 4 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 4 | 40 | 1 (0)| 00:00:01 |
| 9 | INDEX FULL SCAN | SYS_C009964 | 4 | 20 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | SYS_C009964 | 1 | 5 | 0 (0)| 00:00:01 |
|* 11 | INDEX FAST FULL SCAN| SYS_C009962 | 2 | 10 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | SYS_C009962 | 1 | 5 | 0 (0)| 00:00:01 |
|* 13 | INDEX FAST FULL SCAN | SYS_C009960 | 2 | 10 | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | SYS_C009960 | 1 | 5 | 0 (0)| 00:00:01 |
| 15 | LOAD AS SELECT | | | | | |
| 16 | WINDOW SORT | | 7 | 217 | 2 (0)| 00:00:01 |
|* 17 | FILTER | | | | | |
| 18 | VIEW | | 7 | 217 | 2 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6624_11BDA4 | 7 | 210 | 2 (0)| 00:00:01 |
|* 20 | VIEW | | 1 | 30 | 1 (0)| 00:00:01 |
| 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6624_11BDA4 | 7 | 210 | 2 (0)| 00:00:01 |
|* 22 | VIEW | | 1 | 30 | 1 (0)| 00:00:01 |
| 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6624_11BDA4 | 7 | 210 | 2 (0)| 00:00:01 |
| 24 | SORT ORDER BY | | 7 | 448 | 3 (34)| 00:00:01 |
|* 25 | FILTER | | | | | |
| 26 | VIEW | | 7 | 448 | 2 (0)| 00:00:01 |
| 27 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6625_11BDA4 | 7 | 217 | 2 (0)| 00:00:01 |
|* 28 | VIEW | | 7 | 210 | 2 (0)| 00:00:01 |
| 29 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6625_11BDA4 | 7 | 217 | 2 (0)| 00:00:01 |
|* 30 | VIEW | | 7 | 210 | 2 (0)| 00:00:01 |
| 31 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6625_11BDA4 | 7 | 217 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

10 - access("ANIMALS"."KIND"="ANIMALS"."KIND")
11 - filter("PETS"."NAME"='Wendy' AND "ANIMALS"."KIND"='Dog' OR "PETS"."NAME"='Tiger' AND
"ANIMALS"."KIND"<>'Dog' AND "ANIMALS"."KIND"<>'Turtle' OR "PETS"."NAME"='Lisa' AND
"ANIMALS"."KIND"<>'Snake' AND "ANIMALS"."KIND"<>'Dog' OR "PETS"."NAME"='Taro' AND
"ANIMALS"."KIND"<>'Dog')
12 - access("PETS"."NAME"="PETS"."NAME")
13 - filter("OWNERS"."NAME"='Scott' AND "ANIMALS"."KIND"='Snake' OR "OWNERS"."NAME"='Steve'
AND "ANIMALS"."KIND"<>'Dog' AND "ANIMALS"."KIND"<>'Snake' OR "OWNERS"."NAME"='Hiro' AND
"ANIMALS"."KIND"<>'Turtle' AND "ANIMALS"."KIND"<>'Snake' OR "OWNERS"."NAME"='Larry' AND
"ANIMALS"."KIND"<>'Snake')
14 - access("OWNERS"."NAME"="OWNERS"."NAME")
filter("OWNERS"."NAME"='Hiro' AND "PETS"."NAME"='Tiger' OR "OWNERS"."NAME"<>'Hiro' AND
"PETS"."NAME"<>'Tiger')
17 - filter("NUM_OF_ROWS"=1 OR "NUM_OF_ROWS">1 AND NOT EXISTS (SELECT 0 FROM (SELECT /*+
CACHE_TEMP_TABLE ("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3"
"ANIMAL_KIND" FROM "SYS"."SYS_TEMP_0FD9D6624_11BDA4" "T1") "PET_OWNER_UNKNOWN" WHERE
"PET_NAME"=:B1 AND "NUM_OF_ROWS"=1) AND NOT EXISTS (SELECT 0 FROM (SELECT /*+ CACHE_TEMP_TABLE
("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3" "ANIMAL_KIND" FROM
"SYS"."SYS_TEMP_0FD9D6624_11BDA4" "T1") "PET_OWNER_UNKNOWN" WHERE "ANIMAL_KIND"=:B2 AND
"NUM_OF_ROWS"=1))
20 - filter("PET_NAME"=:B1 AND "NUM_OF_ROWS"=1)
22 - filter("ANIMAL_KIND"=:B1 AND "NUM_OF_ROWS"=1)
25 - filter("NUM_OF_ROWS"=1 OR "NUM_OF_ROWS">1 AND NOT EXISTS (SELECT 0 FROM (SELECT /*+
CACHE_TEMP_TABLE ("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3"
"ANIMAL_KIND" FROM "SYS"."SYS_TEMP_0FD9D6625_11BDA4" "T1") "PET_OWNERS" WHERE "PET_NAME"=:B1 AND
"NUM_OF_ROWS"=1) AND NOT EXISTS (SELECT 0 FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0"
"NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3" "ANIMAL_KIND" FROM
"SYS"."SYS_TEMP_0FD9D6625_11BDA4" "T1") "PET_OWNERS" WHERE "ANIMAL_KIND"=:B2 AND
"NUM_OF_ROWS"=1))
28 - filter("PET_NAME"=:B1 AND "NUM_OF_ROWS"=1)
30 - filter("ANIMAL_KIND"=:B1 AND "NUM_OF_ROWS"=1)

統計
----------------------------------------------------------
4 recursive calls
16 db block gets
84 consistent gets
2 physical reads
1156 redo size
807 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
4 rows processed


SQLのレントゲン写真、バージョンと共に基本形wが変化していくので、常に差分と最新の内容を把握しておきたいですよね。いざ、診療するとなったときには役に立つ、はず!!

今回利用したSQL文や表定義およびデータは、誰がどんな名前のペットを飼っているのかな? 解答編 / JPOUG Advent Calendar Day 23を参照ください。

では、また。






Related article on Mac De Oracle
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 1 / TABLE FULL SCAN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 2 / INDEX UNIQUE SCAN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 3 / INDEX RANGE SCAN, Index Only Scan
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 4 / INDEX RANGE SCAN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 5 / INDEX RANGE SCAN, INLIST ITERATOR
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 6 / INDEX FAST SCAN, Index Only Scan
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 7 / INDEX FULL SCAN,Index Only Scan
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 8 / INDEX SKIP SCAN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 9 / TABLE ACCESS INMEMORY FULL
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 10 / NESTED LOOP JOIN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 11 / MERGE JOIN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 12 / HASH JOIN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 13 / HASH JOIN OUTER
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 14 / HASH JOIN FULL OUTER
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 15 / PX, TABLE ACCESS FULL
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 16 / CONCATENATION
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 17 / SORT UNIQUE, UNION-ALL = UNION
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 18 / UNION-ALL
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 19 / INTERSECTION
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 20 / MINUS
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 21 / WINDOW NOSORT STOPKEY
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 22 / COUNT STOPKEY
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 23 / HASH JOIN - LEFT-DEEP JOIN vs RIGHT-DEEP JOIN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 24 / CONNECT BY NO FILTERING WITH START-WITH
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 25 / UNION ALL (RECURSIVE WITH) DEPTH FIRST, RECURSIVE WITH PUMP
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#1 / STAR TRANSFORM, VECTOR TRANSFORM (DWH向け)
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#2 / MERGE (UPSERT)
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#3 / RDFView
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#4 / INDEX FULL SCAN (MIN/MAX) - Index Only Scan
・実行計画は、SQL文のレントゲン写真だ! No.30 - LOAD TABLE CONVENTIONAL vs. LOAD AS SELECT

| | コメント (0)

2022年3月21日 (月)

Oracle vagrant-projects

今日は約3ヶ月振りにVIrtualBoxの起動やら、Oracle VMの起動でハマって、このあたりの環境どうしようかなあぁ

と思いつつ 21cとかもあるしなぁと考えていたら。横目でみつつも、大人の事情でスルーしていたやつが結構育ってて来てたので、これやるのもいいかなぁと。まだ軽く調べ始めたところだが、来月早々にメインマシン変えるからそのあとな。

 

ひとまず、メモ 

Oracle Vagrant-projects

https://github.com/oracle/vagrant-projects

 

20220321-203926

| | コメント (0)

古くて新しい? 遅延ブロッククリーンアウト (deferred block cleanout) #13

随分間が空いてしまったのです、覚えてますかねw 前回のネタ。私もほぼ忘れてました orz. w

思い出しながら進めましょう:)

Previously on Mac De Oracle
前回は、CTASだと、内部的にdirect path writeされてしまうのでクリーンアウトが必要な状況にはならないという動きを確認しました。

scattered read同様に、sequential readだとどうなの?(という遠くから声が聞こえたきがしたのでw)

とはいえ、遅延ブロッククリーンアウトって、自分の理解だと、全表走査時の動きだと思っているので、多分、起きないよなーと。頭の上に雲型の吹き出した出た状態でイメージしているところ。


では、試してみましょう。

これまでと異なる箇所は、sequential read でぐるぐるループさせたいので、該当表に主キー索引を作成します。また、table full scanではなく、index unique scan で全行アクセスさせてみます。(この動きが大きく異なる部分です)





strong>0) 対象表のdrop/create と主キー作成
対象表のHOGE2は削除して作り直し。このテストケースでは主キーアクセスさせるため、合わせて主キーも作成しておきます。
SCOTT@orcl> @droppurge_create_hoge2_with_pk.sql
1* drop table hoge2 purge

Table dropped.

Elapsed: 00:00:00.51
1* create table hoge2 (id number, data varchar2(2000), constraint pk_hoge2 primary key (id) using index)

Table created.

Elapsed: 00:00:00.07
1* select segment_name,blocks from user_segments where segment_name like '%HOGE2%'

no rows selected

Elapsed: 00:00:00.13


1) 統計をクリアするためOracle再起動


$ sudo service oracle restart

[sudo] password for oracle:

Restarting oracle (via systemctl): [ OK ]



2) PDBのscottでログインしてclient_infoをセット
v$sessionのclient_info列の'TargetSession'文字列で他のSCOTTユーザーのセッションを特定するため。

SCOTT@orcl> @set_client_info
1 begin
2 DBMS_APPLICATION_INFO.SET_CLIENT_INFO('Target Session');
3* end;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SCOTT@orcl>


3) CDBのSYSで統計取得(初回)
内容は省略! (思い出したいかたはこの辺りを参照ください。。


4) PDBのSCOTTユーザーでNOAPPENDヒント付きIASを実行(データサイズは、コミットクリーンアウトではクリーンアウト仕切れないサイズ)/ 未コミット

NOAPPENDヒントを利用している理由は前回までの内容を見ていただければわかりますが、direct path writeさせたないためです。これによりコミットクリーンアウトではクリーンアウトできない状況、つまり、遅延ブロッククリーンアウトが必要となる状態を作っています。

SCOTT@orcl> @ias_noappend_from_hoge.sql
1* insert /*+ noappend */ into hoge2 select * from hoge

200000 rows created.

Elapsed: 00:00:19.63
14:32:40 SCOTT@orcl>


5) CDBのSYSで統計取得(未コミット)

検証中に統計値が変動したもののみ記載

狙い通りdirect path writeは抑止できていることは確認できます。
(CDB)システム統計

SOURCE  NAME                                                           VALUE
------- ---------------------------------------------------- ---------------
sysstat DBWR checkpoint buffers written 22,918
sysstat DBWR checkpoints 72
sysstat DBWR thread checkpoint buffers written 22,918
sysstat DBWR transaction table writes 22
sysstat DBWR undo block writes 983
sysstat cleanouts only - consistent read gets 0
sysstat commit cleanout failures: block lost 0
sysstat commit cleanout failures: callback failure 0
sysstat commit cleanouts 1,601
sysstat commit cleanouts successfully completed 1,601
sysstat consistent gets 122,604
sysstat db block changes 564,554
sysstat deferred (CURRENT) block cleanout applications 12
sysstat free buffer requested 70,607
sysstat immediate (CR) block cleanout applications 0
sysstat immediate (CURRENT) block cleanout applications 1,752
sysstat no work - consistent read gets 67,108
sysstat physical reads 66,750
sysstat physical reads direct 66,709
sysstat physical writes 22,918
sysstat physical writes from cache 22,918
sysstat physical writes non checkpoint 22,918
sysstat redo blocks written 967,108
sysstat redo size 479,412,088
sysstat redo writes 378

(PDB) SCOTTのセッション統計
こちらから見てもdirect path writeは発生していません

SOURCE  NAME                                                           VALUE
------- ---------------------------------------------------- ---------------
sesstat cleanouts only - consistent read gets 0
sesstat commit cleanout failures: block lost 0
sesstat commit cleanouts 1,589
sesstat commit cleanouts successfully completed 1,589
sesstat consistent gets 122,204
sesstat db block changes 564,506
sesstat deferred (CURRENT) block cleanout applications 7
sesstat free buffer requested 70,595
sesstat immediate (CR) block cleanout applications 0
sesstat immediate (CURRENT) block cleanout applications 1,748
sesstat no work - consistent read gets 66,884
sesstat physical reads 66,740
sesstat physical reads direct 66,709
sesstat redo size 478,945,336


該当期間の待機イベント記録されていないですよね。狙い通りなので準備としては問題なしです。

EVENT                                                            WAIT_CLASS
---------------------------------------------------------------- -----------------
SQL*Net message from client Idle
log buffer space Configuration
events in waitclass Other Other
direct path read User I/O
log file switch (private strand flush incomplete) Configuration
log file switch completion Configuration
log file sync Commit
db file sequential read User I/O
SQL*Net message to client Network
Disk file operations I/O User I/O
db file scattered read User I/O

6) CDB or PDBのSYSユーザで接続し、buffer cacheをflushする

SYS@orclcdb> @flush_buffercache.sql
1* alter system flush buffer_cache

System altered.

Elapsed: 00:00:09.04
14:36:26 SYS@orclcdb>


7) CDBのSYSで統計取得(buffer cacheをflush後)
strong>(CDB)システム統計

検証中に統計値が変動したもののみ記載

バッファキャッシュをフラッシュしたのでキャッシュから書き出されたことを示す physical writes from cache および関連する統計値が上昇しています。これも想定通りの挙動ですね。

SOURCE  NAME                                                           VALUE
------- ---------------------------------------------------- ---------------
sysstat DBWR checkpoint buffers written 0
sysstat DBWR checkpoints 0
sysstat DBWR thread checkpoint buffers written 0
sysstat DBWR transaction table writes 8
sysstat DBWR undo block writes 1912
sysstat cleanouts only - consistent read gets 0
sysstat commit cleanout failures: block lost 0
sysstat commit cleanout failures: callback failure 0
sysstat commit cleanouts 43
sysstat commit cleanouts successfully completed 43
sysstat consistent gets 20793
sysstat db block changes 429
sysstat deferred (CURRENT) block cleanout applications 21
sysstat free buffer requested 1013
sysstat immediate (CR) block cleanout applications 0
sysstat immediate (CURRENT) block cleanout applications 9
sysstat no work - consistent read gets 12321
sysstat physical reads 977
sysstat physical reads direct 0
sysstat physical writes 47900
sysstat physical writes from cache 47900
sysstat physical writes non checkpoint 47900
sysstat redo blocks written 2017
sysstat redo size 994652
sysstat redo writes 23

(PDB) SCOTTのセッション統計

キャッシュをフラッシュしただけなので、該当セッションの統計には変化がありません。(まあ、そうですよねw)


SOURCE NAME VALUE
------- ---------------------------------------------------- ---------------
sesstat cleanouts only - consistentread gets 0
sesstat commit cleanout failures: block lost 0
sesstat commit cleanouts 0
sesstat commit cleanouts successfully completed 0
sesstat consistent gets 0
sesstat db block changes 0
sesstat deferred (CURRENT) block cleanout applications 0
sesstat free buffer requested 0
sesstat immediate (CR) block cleanout applications 0
sesstat immediate (CURRENT) block cleanout applications 0
sesstat no work - consistent read gets 0
sesstat physical reads 0
sesstat physical reads direct 0
sesstat physical writes 0
sesstat physical writes direct 0
sesstat physical writes from cache 0
sesstat physical writes non checkpoint 0
sesstat redo blocks written 0
sesstat redo size 0

8) PDBのSCOTTユーザーでコミットの実行

SCOTT@orcl> commit;

Commit complete.

Elapsed: 00:00:00.01


9) CDBのSYSで統計取得(コミット後)
strong>(CDB)システム統計
コミット時点ではクリーンアウト仕切れないほどの更新ブロック数かつ、コミット前にバッファキャッシュをフラッシュしているので、コミット時のクリーンアウトもできない状態なっています(これも事前の準備の通りの結果。なので問題なし)
commit cleanouts と commit cleanout failures: block lost がポイントです。コミット時にクリーンアウトできる量(過去の記事を参照のこと)の閾値以下の量ですら、事前にキャッシュからフラッシュしまっているので、クリーンアウトに失敗(できないようにしたので当然ですが)しています。両方の統計値が同一であるとくことは、コミットクリーンアウトしようとした全てでくりアウトできなかった。理由はキャッシュに該当ブロックがキャッシュから落ちていたから(意図的に落としたわけですが)ということを意味します。予定通りですね。

コミットの時点で最低現おこなうクリーンアウトもさせていないですが、このケースでは 11140 ブロックをコミット時にクリーンアウトしようとしているので、コミット時のクリーンアウトが行われていたとしても 50,000 ブロック以上は遅延されていたことになりますね。今回は、 66,667 ブロックがク後続のSQLで参照されるついでにクリーンアウトされることになります。

検証中に統計値が変動したもののみ記載

SOURCE  NAME                                                           VALUE
------- ---------------------------------------------------- ---------------
sysstat DBWR checkpoint buffers written 0
sysstat DBWR checkpoints 0
sysstat DBWR thread checkpoint buffers written 0
sysstat DBWR transaction table writes 0
sysstat DBWR undo block writes 0
sysstat cleanouts only - consistent read gets 0
sysstat commit cleanout failures: block lost 11140
sysstat commit cleanout failures: callback failure 0
sysstat commit cleanouts 11140
sysstat commit cleanouts successfully completed 0
sysstat consistent gets 34
sysstat db block changes 1
sysstat deferred (CURRENT) block cleanout applications 0
sysstat free buffer requested 8
sysstat immediate (CR) block cleanout applications 0
sysstat immediate (CURRENT) block cleanout applications 0
sysstat no work - consistent read gets 5
sysstat physical reads 8
sysstat physical reads direct 0
sysstat physical writes 0
sysstat physical writes from cache 0
sysstat physical writes non checkpoint 0
sysstat redo blocks written 3
sysstat redo size 456
sysstat redo writes 3

(PDB) SCOTTのセッション統計
セッションレベルでみるよりはっきり見るとことができます。

SOURCE  NAME                                                           VALUE
------- ---------------------------------------------------- ---------------
sesstat cleanouts only - consistent read gets 0
sesstat commit cleanout failures: block lost 11140
sesstat commit cleanouts 11140
sesstat commit cleanouts successfully completed 0
sesstat consistent gets 0
sesstat db block changes 1
sesstat deferred (CURRENT) block cleanout applications 0
sesstat free buffer requested 1
sesstat immediate (CR) block cleanout applications 0
sesstat immediate (CURRENT) block cleanout applications 0
sesstat no work - consistent read gets 0
sesstat physical reads 1
sesstat physical reads direct 0
sesstat physical writes 0
sesstat physical writes direct 0
sesstat physical writes from cache 0
sesstat physical writes non checkpoint 0
sesstat redo blocks written 0
sesstat redo size 176


10) PDBのSCOTTユーザーで遅延ブロッククリーンアウト有無確認(対象表を主キーによる一意検索で全行アクセス)

さて、事前に想定している実行計画になっているかプランだけ確認しておきましょう。主キーによる索引ユニークスキャンであることが確認できます。想定通りです。この実行計画で、200,000 行をグルグル参照した場合、遅延されたブロッククリーンアウトが行われるでしょうか。。。。(してくれないと思いますが。。。試してみましょう)

SCOTT@orcl> set autot trace exp
14:41:47 SCOTT@orcl> select * from hoge2 where id = 1;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3319133482

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1015 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| HOGE2 | 1 | 1015 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_HOGE2 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=1)

SCOTT@orcl> set autot off
SCOTT@orcl>
SCOTT@orcl> @table_unique_scan_hoge2.sql
1 begin
2 for i in 1..&1 loop
3 for hoge2_rec in (select * from hoge2 where id = i) loop
4 null;
5 end loop;
6 end loop;
7* end;
Enter value for 1: 200000
old 2: for i in 1..&1 loop
new 2: for i in 1..200000 loop

PL/SQL procedure successfully completed.

Elapsed: 00:00:14.94
14:39:36 SCOTT@orcl>


11) CDBのSYSで統計取得(遅延ブロッククリーンアウト有無確認。対象表を主キーによる一意検索で全行アクセス)
strong>(CDB)システム統計

クリーンアウトが行われていれば、データブロックの総数 66,667 ブロックは少なくともクリーンアップの対象になっているはずですが。。。。。
cleanout系の統計値に動きはありませんよね。

SOURCE  NAME                                                           VALUE
------- ---------------------------------------------------- ---------------
sysstat DBWR checkpoint buffers written 21
sysstat DBWR checkpoints 0
sysstat DBWR thread checkpoint buffers written 0
sysstat DBWR transaction table writes 5
sysstat DBWR undo block writes 5
sysstat cleanouts only - consistent read gets 0
sysstat commit cleanout failures: block lost 0
sysstat commit cleanout failures: callback failure 15
sysstat commit cleanouts 366
sysstat commit cleanouts successfully completed 351
sysstat consistent gets 632775
sysstat db block changes 7848
sysstat deferred (CURRENT) block cleanout applications 169
sysstat free buffer requested 76662
sysstat immediate (CR) block cleanout applications 0
sysstat immediate (CURRENT) block cleanout applications 169
sysstat no work - consistent read gets 21822
sysstat physical reads 69501
sysstat physical reads direct 0
sysstat physical writes 21
sysstat physical writes from cache 21
sysstat physical writes non checkpoint 10
sysstat redo blocks written 5132
sysstat redo size 2536084
sysstat redo writes 28

(PDB) SCOTTのセッション統計

physical reads は、67706 ブロックなのでほ、HOGE2表の索引と表のセグメントサイズほどは物理読み込みされたということになります。ただ、cleanouts only - consistent read gets などのクリーンアウトを示す統計値はほぼ動いていません。ようするに、db file sequential readとなる index unique scan ではブロッククリーンアウトは発生しないということを意味しています。

おおおーーー。だったら、グルグル系の処理で一行ごとに処理するのが最高じゃん! という単純な話ではないので要注意ですからねw

SOURCE  NAME                                                           VALUE
------- ---------------------------------------------------- ---------------
sesstat cleanouts only - consistent read gets 0
sesstat commit cleanout failures: block lost 0
sesstat commit cleanouts 4
sesstat commit cleanouts successfully completed 4
sesstat consistent gets 600005
sesstat db block changes 14
sesstat deferred (CURRENT) block cleanout applications 2
sesstat free buffer requested 74540
sesstat immediate (CR) block cleanout applications 0
sesstat immediate (CURRENT) block cleanout applications 2
sesstat no work - consistent read gets 0
sesstat physical reads 67706
sesstat physical reads direct 0
sesstat physical writes 0
sesstat physical writes direct 0
sesstat physical writes from cache 0
sesstat physical writes non checkpoint 0
sesstat redo blocks written 0
sesstat redo size 2152


10) PDBのSCOTTユーザーで遅延ブロッククリーンアウト有無確認(sacattered read)

single block readを行うindex unique scanではクリーンアウトが発生しなかったということは、全表走査させる table full scan かつ scattered readさせれば発生するのではないでしょうか? という確認です。
auto traceで統計情報をみると、なにやら、REDOログが大量に生成されてますよね。。。これは!!!

SCOTT@orcl> @table_full_scan_hoge2.sql
1* alter session set "_serial_direct_read" = never

Session altered.

Elapsed: 00:00:00.01
1* alter session set "_very_large_object_threshold" = 20400

Session altered.

Elapsed: 00:00:00.00
set autot trace exp stat

1* select * from hoge2

200000 rows selected.

Elapsed: 00:00:06.55

Execution Plan
----------------------------------------------------------
Plan hash value: 1530105727

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 172K| 167M| 18223 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| HOGE2 | 172K| 167M| 18223 (1)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
10 recursive calls
1 db block gets
147157 consistent gets
377 physical reads
5866968 redo size
406775148 bytes sent via SQL*Net to client
147265 bytes received via SQL*Net from client
13335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
200000 rows processed

set autot off

14:56:56 SCOTT@orcl>


11) CDBのSYSで統計取得(遅延ブロッククリーンアウト有無確認。対象表を主キーによる一意検索で全行アクセス)
strong>(CDB)システム統計

SOURCE  NAME                                                           VALUE
------- ---------------------------------------------------- ---------------
sysstat DBWR checkpoint buffers written 1121
sysstat DBWR checkpoints 0
sysstat DBWR thread checkpoint buffers written 0
sysstat DBWR transaction table writes 36
sysstat DBWR undo block writes 347
sysstat cleanouts only - consistent read gets 66676
sysstat commit cleanout failures: block lost 0
sysstat commit cleanout failures: callback failure 0
sysstat commit cleanouts 806
sysstat commit cleanouts successfully completed 806
sysstat consistent gets 174296
sysstat db block changes 80773
sysstat deferred (CURRENT) block cleanout applications 383
sysstat free buffer requested 2095
sysstat immediate (CR) block cleanout applications 66676
sysstat immediate (CURRENT) block cleanout applications 75
sysstat no work - consistent read gets 27953
sysstat physical reads 1842
sysstat physical reads direct 0
sysstat physical writes 1121
sysstat physical writes from cache 1121
sysstat physical writes non checkpoint 981
sysstat redo blocks written 16550
sysstat redo size 8074260
sysstat redo writes 351

(PDB) SCOTTのセッション統計
HOGE2の表のクリーンアウト対象ブロック数、66,667 きっちり同じ数字が現れました。しかも cleanoutに関わる統計でかつ、大量のREDOログも生成されています。でました〜〜〜w クリーンアウト。全表走査かつ、scattered readで。

SOURCE  NAME                                                           VALUE
------- ---------------------------------------------------- ---------------
sesstat cleanouts only - consistent read gets 66667
sesstat commit cleanout failures: block lost 0
sesstat commit cleanouts 1
sesstat commit cleanouts successfully completed 1
sesstat consistent gets 153731
sesstat db block changes 66718
sesstat deferred (CURRENT) block cleanout applications 0
sesstat free buffer requested 690
sesstat immediate (CR) block cleanout applications 66667
sesstat immediate (CURRENT) block cleanout applications 0
sesstat no work - consistent read gets 16317
sesstat physical reads 680
sesstat physical reads direct 0
sesstat redo size 5877252




まとめ

index unique scanでグルグルさせつつ、全行アクセスしても発生しないですよね!
single block readになるunique index scanでは、遅延ブロッククリーンアウトは発生しない。(想像通りでよかったw)
で、そのあとで全表走査(scattered read)させてみると、やはり、遅延ブロッククリーンアウトは発生するわけですよ。面白いです。ほんと。



さあ、5月末まで追い込みだw ブログ書かないとw (頑張って、アドベントカレンダー全部俺やっとくほうが実は楽なのかもしれないw)

では、また。



古くて新しい? 遅延ブロッククリーンアウト (deferred block cleanout) #1
古くて新しい? 遅延ブロッククリーンアウト (deferred block cleanout) #2
古くて新しい? 遅延ブロッククリーンアウト (deferred block cleanout) #3
古くて新しい? 遅延ブロッククリーンアウト (deferred block cleanout) #4
古くて新しい? 遅延ブロッククリーンアウト (deferred block cleanout) #5
古くて新しい? 遅延ブロッククリーンアウト (deferred block cleanout) #6
古くて新しい? 遅延ブロッククリーンアウト (deferred block cleanout) #7
古くて新しい? 遅延ブロッククリーンアウト (deferred block cleanout) #8
古くて新しい? 遅延ブロッククリーンアウト (deferred block cleanout) #9
古くて新しい? 遅延ブロッククリーンアウト (deferred block cleanout) #10
古くて新しい? 遅延ブロッククリーンアウト (deferred block cleanout) #11
古くて新しい? 遅延ブロッククリーンアウト (deferred block cleanout) #12



| | コメント (0)

VBOX_E_IPRT_ERROR (0x80BB0005) / VirtualBox 6.1.32 r14929 (いろいろでるなぁw)

えーっとw

先ほどのエラーが解消したと思ったら、起動したいVMが起動しなーーーいw

( When it rains, it pours  LOL )

VirtualBoxアップデートするのはいいけど、やれやれなことも多い。

英語圏のネタをググってもヒットしなかったのですが、やはり、一足先にハマっている人がいたw (ありがとう)


今回のは、なんと日本語環境というかマルチバイト文字をつかう世界でだけ起きるやつw (昔のOracleにもそんなの結構多かったな〜。遠い目w)

今回はマルチバイト特有のエラー、それまでは問題なかったわけだからw マルチバイトに対応しないようなコードが今回のアップデートで混入してしまったということだろうw ʅ(◞‿◟)ʃ

20220321-114144

VirtualBox 6.1.23でVM名にマルチバイト文字あると、なんと起動しない!
https://blog.apar.jp/linux/15987/

 

引用先のブログだとマルチバイトだとわかりやすいのですが、私のケースだとものすごく気づきにくかった(エラーコードは同じだったので、しばらく悩んで気づきましたが)

 

太った空白の影響でしたw


 

VM名をよーーーーく見ると、なんだか、空白が多い箇所ありますよね。。。。
選択してみると、なんと、全角空白!!! w    

 

これだ!!!!


半角空白に置き換えた。
20220321-114204

無事起動した〜〜〜

20220321-120214

これで、やっと、本題に取りかかれるw 

 

| | コメント (0)

NS_ERROR_SOCKET_FAIL (0xC1F30200) / VirtualBox 6.1 on macOS

えーっと、やっと重い腰を上げてw、なんと本年度最初のエントリー。

もろもろ他のことに気を取られていたら、ACEのKPI評価期末がもう少しだのに気づきw 溜めたままになっていたネタを書かないと!

と、VirtualBox 6.1をアップデートして起動しようしたら、なんと、エラーで起動しない (まあ、よくあります。似たようなことはw
20220321-80633

そんなときは、たぶん、みんな、ハマってるよね。ということで、ググりましょうw

Virtualbox 6.1 Code NS_ERROR_SOCKET_FAIL (0xC1F30200) - IPC daemon socket error (extended info n
https://forums.virtualbox.org/viewtopic.php?f=7&t=105337

もろこれです。で、知らなかったのですが、これ結構前から起こることもあったらしい(私は初でしたが)

/private/tmp のパーミッションが変わってしまった?影響らしい。(パーミッションの設定の詳細はあとで調べることにして、とりえず、確認)

[Solved] NS_ERROR_SOCKET_FAIL (0xC1F30200) PLEASE HELP
https://forums.virtualbox.org/viewtopic.php?f=8&t=87275#p416888

ここでハマってるのも時間がもったいないので、ちゃちゃっとチャッつけちゃいましょw (やりたいのはこれじゃないのでw)

 

 


discus-mother:private oracle$ ll
total 0
drwxr-xr-x 155 root wheel 4960 3 20 15:19 etc
drwxr-xr-x 2 root wheel 64 8 18 2018 tftpboot
drwxr-xr-x 15 root wheel 480 3 21 07:55 tmp
drwxr-xr-x 31 root wheel 992 4 6 2021 var
discus-mother:private oracle$
discus-mother:private oracle$ sudo chmod 1777 tmp
discus-mother:private oracle$ ll
total 0
drwxr-xr-x 155 root wheel 4960 3 20 15:19 etc
drwxr-xr-x 2 root wheel 64 8 18 2018 tftpboot
drwxrwxrwt 15 root wheel 480 3 21 07:55 tmp
drwxr-xr-x 31 root wheel 992 4 6 2021 var
discus-mother:private oracle$

 

これで解決!
20220321-80807 やれやれw

 

ではではあと、2ヶ月ほどで、14エントリー以上かかないとw。とはいえ、アドベントカレンダー全部俺のペースよりは楽ではありますね。w ガンガレ>俺

 

| | コメント (0)