Hard Parseに時間が掛かるお手軽なSQLを作ってみる。(Oracle Database)
「Hard Parseに時間が掛かる手軽なSQLを作りたい。彡(ﾟ)(ﾟ)」と呟いた
(※下記twitterモーメント参照)ところ、色々とアイデアを頂いたので、
全部ではないのですがお試し＆まとめてみましたやで彡(ﾟ)(ﾟ)
Hard Parseに時間が掛かるSQL ※twitterモーメント https://twitter.com/i/moments/1001431132234072064
Case1. IN句リテラルを仕様上限(999個)まで
こちらの結果は下記の通り、ぼちぼち彡(ﾟ)(ﾟ)
SELECT A.ITEM_NAME , COUNT(*) FROM ITEM_TBL A WHERE ITEM_NO IN ( 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40, : 975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999) GROUP BY ITEM_NAME call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.08 0.09 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 2 0 10 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.08 0.09 0 2 0 10
Case2. 結合表8個以上
こちらの結果は下記の通り、Case1. よりは増えました彡(ﾟ)(ﾟ)
SELECT A.ITEM_NAME , COUNT(*) FROM ITEM_TBL A , ITEM_TBL B , ITEM_TBL C , ITEM_TBL D , ITEM_TBL E , ITEM_TBL F , ITEM_TBL G , ITEM_TBL H , ITEM_TBL I , ITEM_TBL J WHERE J.REGIST_DATE BETWEEN TO_DATE('2012/08/01', 'YYYY/MM/DD') AND TO_DATE('2012/08/02', 'YYYY/MM/DD') AND A.ITEM_NO = B.ITEM_NO AND B.ITEM_NO = C.ITEM_NO AND C.ITEM_NO = D.ITEM_NO AND D.ITEM_NO = E.ITEM_NO AND E.ITEM_NO = F.ITEM_NO AND F.ITEM_NO = G.ITEM_NO AND G.ITEM_NO = H.ITEM_NO AND H.ITEM_NO = I.ITEM_NO AND I.ITEM_NO = J.ITEM_NO GROUP BY A.ITEM_NAME call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.48 0.48 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 10 0 10 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.48 0.48 0 10 0 10
Case3. 結合表8個以上 + permutation増強
こちらの結果は下記の通り、Case2. より更に増加彡(ﾟ)(ﾟ)
ALTER SESSION SET "_optimizer_max_permutations"=80000; SELECT A.ITEM_NAME , COUNT(*) FROM ITEM_TBL A , ITEM_TBL B , ITEM_TBL C , ITEM_TBL D , ITEM_TBL E , ITEM_TBL F , ITEM_TBL G , ITEM_TBL H , ITEM_TBL I , ITEM_TBL J WHERE J.REGIST_DATE BETWEEN TO_DATE('2012/08/01', 'YYYY/MM/DD') AND TO_DATE('2012/08/02', 'YYYY/MM/DD') AND A.ITEM_NO = B.ITEM_NO AND B.ITEM_NO = C.ITEM_NO AND C.ITEM_NO = D.ITEM_NO AND D.ITEM_NO = E.ITEM_NO AND E.ITEM_NO = F.ITEM_NO AND F.ITEM_NO = G.ITEM_NO AND G.ITEM_NO = H.ITEM_NO AND H.ITEM_NO = I.ITEM_NO AND I.ITEM_NO = J.ITEM_NO GROUP BY A.ITEM_NAME call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.66 0.68 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 10 0 10 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.67 0.69 0 10 0 10
Case4. UNION500行
こちらの結果は下記の通り、エエ感じやで！彡(^)(^)
SELECT ITEM_NO FROM ITEM_TBL WHERE ITEM_NO BETWEEN 1 AND 100 UNION SELECT ITEM_NO FROM ITEM_TBL WHERE ITEM_NO BETWEEN 1 AND 100 UNION SELECT ITEM_NO FROM ITEM_TBL WHERE ITEM_NO BETWEEN 1 AND 100 UNION SELECT ITEM_NO FROM ITEM_TBL WHERE ITEM_NO BETWEEN 1 AND 100 UNION SELECT ITEM_NO FROM ITEM_TBL WHERE ITEM_NO BETWEEN 1 AND 100 UNION : SELECT ITEM_NO FROM ITEM_TBL WHERE ITEM_NO BETWEEN 1 AND 100 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 1.04 1.05 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.01 0.01 0 500 0 10 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 1.06 1.06 0 500 0 10
Case5. DUAL表を200個結合
Kazumasa Obaraさんネタ。元ネタの500個だと
終わらなかったので200個で計測。ぶっちぎりで草ｗｗｗｗｗ
SELECT A.* FROM DUAL A, DUAL, : DUAL, DUAL, DUAL, DUAL, DUAL call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 48.86 49.96 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 48.86 49.96 0 3 0 1
やっぱり結合順序の探索には時間が掛かるんやなぁ……彡(ﾟ)(ﾟ)
アイデアを頂いた皆さん、ありがとうございます！(｀･ω･)ゞ
VirtualBoxのLinuxにGraalVMを入れてpythonのスクリプトを動かしてみた。
GraalVM、かなりアツいOSSですね。各所で話題になってます。
オラクル、JavaやJavaScript、Ruby、Pythonなど多言語対応を単一ランタイムで実現する「GraalVM」をオープンソースで公開。Twitterが本番環境で採用 http://www.publickey1.jp/blog/18/javajavascriptrubypythongraalvmtwitter.html
導入も簡単だったので置いときますやね彡(ﾟ)(ﾟ)
GraalVMのダウンロード＆インストール
下記のリンク(Getting start) ⇒ Downloads ⇒ Community Edition (CE) で
「graalvm-ce-1.0.0-rc1-linux-amd64.tar.gz」をダウンロード
GraalVM Getting start https://www.graalvm.org/docs/getting-started/ Community Edition (CE) https://www.graalvm.org/downloads/
VirtualBoxのゲストOS(Linux、本記事ではOEL6を使用)の適当なディレクトリに展開する。
今回は「/home/oracle/work」配下に展開。超簡単やね。彡(ﾟ)(ﾟ)
tar xvzf graalvm-ce-1.0.0-rc1-linux-amd64.tar.gz
Graal Pythonコンポーネントのインストール
GraalVMのbinディレクトリにPATHを通す。オプションでJAVA_HOMEもGraalVMの展開ディレクトリにセット。
export PATH=/home/oracle/work/graalvm-1.0.0-rc1/bin:$PATH export JAVA_HOME=/home/oracle/work/graalvm-1.0.0-rc1
マニュアル(https://www.graalvm.org/docs/getting-started/#running-python-3)の通りに guコマンド（Graal updater）でpython componentをインストール
gu -c install org.graalvm.python Downloading: Component catalog Processing component archive: Component org.graalvm.python Downloading: Component org.graalvm.python Installing new component: Graal.python (org.graalvm.python, version 1.0.0-rc1)
Graal Pythonの実行
下記マニュアルを参考にしつつ、まずはコマンドラインから。
GraalVM - Python 3 https://www.graalvm.org/docs/reference-manual/languages/python/#python-3
graalpythonコマンドでコマンドラインを起動。
VMを起動しているせいなのか、print文の初めの1回は遅い彡(ﾟ)(ﾟ)
graalpython Please note: This Python implementation is in the very early stages, and can run little more than basic benchmarks at this point. >>> print ("1+2=", 1 + 2) 1+2= 3 >>> print ("1+2=", 1 + 2) 1+2= 3 ：
cat py3test.py # -*- coding: utf-8 -*- print("Hello World!") for i in range(0,6): print(i,"Hello World!") for i in range(0,6): if i%2==0: print(i," is even number.") else: print(i," is odd number.")
スクリプトの実行コマンドは下記、こいつ……動くぞ！彡(ﾟ)(ﾟ)
graalpython --jvm --polyglot py3test.py Please note: This Python implementation is in the very early stages, and can run little more than basic benchmarks at this point. Hello World! 0 Hello World! 1 Hello World! 2 Hello World! 3 Hello World! 4 Hello World! 5 Hello World! 0 is even number. 1 is odd number. 2 is even number. 3 is odd number. 4 is even number. 5 is odd number.
うーん、こいつは凄い。他の言語も動くようだし、polyglotって機能で
言語が混ぜこぜでも同時に動くらしい。試してみないと。。。彡(ﾟ)(ﾟ)
JavaモジュールのNative Image化（※4/25追記）
python実行とは直接関係ないのですが、GraalVMにはNative Image化という
高速化という機能が有り、試してみたのでメモ。
まずワイ環境ではnative-imageコマンドの実行に、
gccとzlib-develのインストールが必要でした彡(ﾟ)(ﾟ)
yum install gcc yum install zlib-devel
以下のjavaをコンパイル＆Native Image化彡(ﾟ)(ﾟ)
cat HelloGraalJava.java import org.graalvm.polyglot.*; class HelloGraalJava { public static void main(String[] args) { System.out.println("Hello, Graal Java!"); } } javac HelloGraalJava.java native-image HelloGraalJava Build on Server(pid: 3847, port: 26682) classlist: 909.56 ms (cap): 3,249.39 ms setup: 5,206.04 ms (typeflow): 15,378.97 ms (objects): 6,795.22 ms (features): 152.68 ms analysis: 22,692.12 ms universe: 1,248.48 ms (parse): 6,206.26 ms (inline): 3,647.32 ms (compile): 27,832.51 ms compile: 39,458.58 ms image: 3,222.35 ms write: 646.89 ms [total]: 73,892.84 ms
ALL小文字の「hellograaljava」というバイナリが生成されました。
timeコマンド付けつつ実行してみた。なるほど彡(ﾟ)(ﾟ)
#通常のjava実行 time java HelloGraalJava Hello, Graal Java! real 0m0.187s user 0m0.140s sys 0m0.043s #Native Image実行 time ./hellograaljava Hello, Graal Java! real 0m0.017s user 0m0.003s sys 0m0.011s
native-imageコマンドはpython関連のオプションも有るんですが、
使い方はイマイチ解らず。今後要検証彡(ﾟ)(ﾟ)
native-image --help
：
Available macro-options are:
--language:python
：
ALTER SYSTEM KILL SESSION…"だけ"の権限付与を12c新機能のCode Based Access Control＋実行者権限プロシージャで実現してみる。(Oracle Database)
前回のエントリ
gonsuke777.hatenablog.com
からの続き。
Qiita の @tlokweng さんから 12c新機能のCode Based Access Control
なるものの存在を教えて頂きますた彡(ﾟ)(ﾟ)
ALTER SYSTEM KILL SESSION…によるユーザーセッションの切断は許可したいけど、
ALTER SYSTEM権限の付与(GRANT)は範囲が広過ぎる、権限が強過ぎる。。。
ALTER SYSTEM KILL SESSIONだけ許可したい……てな要件を、
今回は実行者権限(AUTHID CURRENT_USER)のプロシージャと、
12c新機能のCode Based Access Controlで実現してみるやで彡(ﾟ)(ﾟ)
概要
以下のような構成でDBユーザーとストアド・プロシージャを作成します。
DEFUSER … 権限の強いDBユーザー │ ↓ │ RL_EXE_KILL_SESS … ALTER SYSTEM権限を保持するロール │ │ │ (Code Based Access Controlでシステム権限をプロシージャに対してロール経由で付与) ↓ ↓ DEFUSER.PRC_KILL_SESS … KILL SESSIONを行うストアド・プロシージャ、実行者権限(AUTHID CURRENT_USER)で作成 ↓ 実行権限付与(GRANT EXECUTE…) ↓ EXEUSER ←権限の弱いDBユーザー
DBユーザー作成
プロシージャ所有者(DEFUSER)と実行ユーザー(EXEUSER)をそれぞれ作成します。
-- プロシージャの所有者を作成 CONNECT /AS SYSDBA CREATE USER DEFUSER IDENTIFIED BY xxxxxxxx DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; -- DBAロールを付与 GRANT DBA TO DEFUSER; -- KILL SESSIONを実行するユーザー CREATE USER EXEUSER IDENTIFIED BY xxxxxxxx DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; -- CREATE SESSION権限のみ付与 GRANT CREATE SESSION TO EXEUSER; Connected. User created. Grant succeeded. User created. Grant succeeded.
プロシージャ作成
実行者権限(AUTHID CURRENT_USER)のプロシージャを作成します。
-- 実行者権限(AUTHID CURRENT_USER)のKILL SESSIONプロシージャを作成 CONNECT DEFUSER/xxxxxxxx@yyyyyyyy CREATE OR REPLACE PROCEDURE DEFUSER.PRC_KILL_SESS ( in_sid IN NUMBER , in_serial IN NUMBER ) AUTHID CURRENT_USER IS BEGIN DBMS_OUTPUT.PUT_LINE('Current Schema => ' || SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')); DBMS_OUTPUT.PUT_LINE('Current User => ' || SYS_CONTEXT('USERENV', 'CURRENT_USER')); DBMS_OUTPUT.PUT_LINE('Session User => ' || SYS_CONTEXT('USERENV', 'SESSION_USER')); EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || in_sid || ',' || in_serial || ''''; END; / Connected. Procedure created.
ロール作成＆権限付与 および Code Based Access Controlによる権限付与
プロシージャ所有者でロールを作成して、
ロール経由でALTER SYSTEM権限をプロシージャに付与します。
ユーザーやロールではなく、プロシージャに権限を付与するところがポイントで、
これが12c新機能のCode Based Access Control、、、と理解したやで。彡(ﾟ)(ﾟ)
CONNECT DEFUSER/xxxxxxxx@yyyyyyyy -- ロール作成とロールに対するALTER SYSTEM権限の付与 CREATE ROLE RL_EXEC_KILL_SESS; GRANT ALTER SYSTEM TO RL_EXEC_KILL_SESS; -- ALTER SYSTEM権限をロール経由でプロシージャに付与(Code Based Access Control) GRANT RL_EXEC_KILL_SESS TO PROCEDURE DEFUSER.PRC_KILL_SESS; -- プロシージャの実行権限付与 GRANT EXECUTE ON DEFUSER.PRC_KILL_SESS TO EXEUSER; Role created. Grant succeeded. Grant succeeded. Grant succeeded.
EXEUSERでKILL SESSIONの検証
EXEUSERでKILL SESSIONの検証をしてみます。下記のセッションをKILLしてみます。
CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy Connected. SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'AYSHIBAT'; SID SERIAL# ---------- ---------- 787 62729
まずは ALTER SYSTEM KILL SESSION…から。権限が無いので当然失敗します。
CONNECT EXEUSER/xxxxxxxx@yyyyyyyy Connected. SHOW USER USER is "EXEUSER" ALTER SYSTEM KILL SESSION '787, 62729'; * ERROR at line 1: ORA-01031: insufficient privileges
ストアド・プロシージャ(PRC_KILL_SESS)によるKILL。こちらは成功します。
CONNECT EXEUSER/xxxxxxxx@yyyyyyyy Connected. SHOW USER USER is "EXEUSER" SET SERVEROUTPUT ON SIZE 1000000; EXEC DEFUSER.PRC_KILL_SESS(787, 62729); Current Schema => EXEUSER Current User => EXEUSER Session User => EXEUSER PL/SQL procedure successfully completed.
元のセッション(AYSHIBATユーザ)に戻ってみると……
見事にKILLされています。やったぜ。彡(^)(^)
SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'AYSHIBAT'; * ERROR at line 1: ORA-00028: your session has been killed SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'AYSHIBAT'; * ERROR at line 1: ORA-01012: not logged on Process ID: 4160 Session ID: 787 Serial number: 62729
ちなみにALTER SYSTEM権限をロールから剥奪(REVOKE)すると、
実行ユーザーからはKILL SESSIONが実行できなくなります。
Code Based Access Controlが有効に機能していることが解ります彡(ﾟ)(ﾟ)
CONNECT DEFUSER/xxxxxxxx@yyyyyyyy REVOKE ALTER SYSTEM FROM RL_EXEC_KILL_SESS; Connected. Revoke succeeded. CONNECT EXEUSER/xxxxxxxx@yyyyyyyy SET SERVEROUTPUT ON SIZE 1000000; EXEC DEFUSER.PRC_KILL_SESS(798, 56993); Connected. Current Schema => EXEUSER Current User => EXEUSER Session User => EXEUSER BEGIN DEFUSER.PRC_KILL_SESS(798, 56993); END; * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "DEFUSER.PRC_KILL_SESS", line 11 ORA-06512: at line 1
AUTHID DEFINERだと、問答無用で定義者の権限やロールで処理されてしまう所を、
実行者に付与された権限＆ロールできめ細かく制御可能なのが、
この機能(Code Based Access Control)のメリットやね彡(^)(^)
＃今回のケースだと、メリットが全面的には出し辛かったんやけどね。。。彡(-)(-)
マニュアルも読みませう彡(ﾟ)(ﾟ)
Oracle Database新機能ガイド 12cリリース1 (12.1) B71327-05 2.9.2.2 コードベース・セキュリティ https://docs.oracle.com/cd/E57425_01/121/NEWFT/chapter12101.htm#FEATURENO08676 Oracle Databaseセキュリティ・ガイド 12c リリース1 (12.1) B71285-10 定義者権限および実行者権限のコード・ベース・アクセス制御の使用 https://docs.oracle.com/cd/E57425_01/121/DBSEG/dr_ir.htm#GUID-45E77E8E-587F-42AF-A163-D814264341E2
ALTER SYSTEM KILL SESSION…"だけ"の権限付与を定義者権限のプロシージャで実現してみる。(Oracle Database)
ALTER SYSTEM KILL SESSION…によるユーザーセッションの切断は許可したいけど、
ALTER SYSTEM権限の付与(GRANT)は範囲が広過ぎる、権限が強過ぎる。
ALTER SYSTEM KILL SESSIONだけ許可したい……てな要件を、
定義者権限(AUTHID DEFINER)のプロシージャで実現してみるやで彡(ﾟ)(ﾟ)
概要
以下のような構成でDBユーザーとストアド・プロシージャを作成します。
DEFUSER … 権限の強いDBユーザー ↓ DEFUSER.PRC_KILL_SESS … KILL SESSIONを行うストアド・プロシージャ、定義者権限(AUTHID DEFINER)で作成 ↓ 実行権限付与(GRANT EXECUTE…) ↓ EXEUSER ←権限の弱いDBユーザー
DBユーザー作成
権限の強いユーザー(DEFUSER)と弱いユーザー(EXEUSER)をそれぞれ作成します。
CONNECT /AS SYSDBA -- プロシージャの所有者 CREATE USER DEFUSER IDENTIFIED BY xxxxxxxx DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; -- DBAロールとALTER SYSTEM権限を付与 GRANT DBA TO DEFUSER; GRANT ALTER SYSTEM TO DEFUSER; User created. Grant succeeded. Grant succeeded. -- KILL SESSIONを実行するユーザー CREATE USER EXEUSER IDENTIFIED BY xxxxxxxx DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; -- CREATE SESSION権限のみ付与 GRANT CREATE SESSION TO EXEUSER; User created. Grant succeeded.
ストアド・プロシージャ作成＆権限付与
KILL SESSIONを実行するストアド・プロシージャをDEFUSERスキーマに
作成してEXEUSERに実行権限を付与します。
CONNECT DEFUSER/xxxxxxxx@yyyyyyyy CREATE OR REPLACE PROCEDURE DEFUSER.PRC_KILL_SESS ( in_sid IN NUMBER , in_serial IN NUMBER ) AUTHID DEFINER IS BEGIN EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || in_sid || ',' || in_serial || ''''; END; / Procedure created. GRANT EXECUTE ON DEFUSER.PRC_KILL_SESS TO EXEUSER; Grant succeeded.
EXEUSERでKILL SESSIONの検証
EXEUSERでKILL SESSIONの検証をしてみます。下記のセッションをKILLしてみます。
CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy Connected. SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'AYSHIBAT'; SID SERIAL# ---------- ---------- 775 31102
まずは ALTER SYSTEM KILL SESSION…から。権限が無いので当然失敗します。
CONNECT EXEUSER/xxxxxxxx@yyyyyyyy Connected. SHOW USER USER is "EXEUSER" ALTER SYSTEM KILL SESSION '775, 31102'; * ERROR at line 1: ORA-01031: insufficient privileges
ストアド・プロシージャ(PRC_KILL_SESS)によるKILL。こちらは成功します。
CONNECT EXEUSER/xxxxxxxx@yyyyyyyy Connected. SHOW USER USER is "EXEUSER" EXEC DEFUSER.PRC_KILL_SESS(775, 31102); PL/SQL procedure successfully completed.
元のセッション(AYSHIBATユーザ)に戻ってみると……
見事にKILLされています。やったぜ。彡(^)(^)
SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'AYSHIBAT'; * ERROR at line 1: ORA-00028: your session has been killed SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'AYSHIBAT'; ERROR at line 1: ORA-01012: not logged on Process ID: 4398 Session ID: 775 Serial number: 31102
マニュアルも読みませう彡(ﾟ)(ﾟ)
AUTHID DEFINERでプロシージャ(等)を作成すると、
定義者(今回のケースではDEFUSER)の権限で実行されるってことなんやね。
Oracle Database PL/SQL言語リファレンス 12c リリース2 (12.2) E72879-03 実行者権限および定義者権限(AUTHIDプロパティ) https://docs.oracle.com/cd/E82638_01/LNPLS/plsql-subprograms.htm#GUID-41D23DE7-3C07-41CF-962B-F92B696594B5
Oracle Database の DRCP（データベース常駐接続プーリング）の MINSIZE と MAXSIZE を 1 に設定して、複数セッションから接続してみる。
DRCP は 通常はAPサーバー側で確保するコネクションプール を
Oracle Database側で用意しておく機能やね彡(ﾟ)(ﾟ)
DRCPでMAXSIZEを超える同時接続要求が来た際の挙動を確認してみるんやで。
まずは DRCP の設定を変更して、開始します。
-- DRCPの最大接続数／最小接続数を1に設定 EXECUTE DBMS_CONNECTION_POOL.CONFIGURE_POOL(MINSIZE=>1, MAXSIZE=>1); PL/SQL procedure successfully completed. -- DRCP開始 EXECUTE DBMS_CONNECTION_POOL.START_POOL; PL/SQL procedure successfully completed. -- 確認 SET LINESIZE 100; COLUMN CONNECTION_POOL FORMAT A40; SELECT CONNECTION_POOL, STATUS, MINSIZE, MAXSIZE FROM DBA_CPOOL_INFO; CONNECTION_POOL STATUS MINSIZE MAXSIZE ---------------------------------------- ---------------- ---------- ---------- SYS_DEFAULT_CONNECTION_POOL ACTIVE 1 1
クライアントからDRCPに接続して……(こちらはすぐ繋がります)
11:32:02 SQL> CONNECT AYSHIBAT/xxxxxxxx@127.0.0.1:11521/orcl:POOLED 接続されました。
別のクライアントからも接続します。こちらは待たされます。
そう、待たされるんだよなぁ……彡(ﾟ)(ﾟ)
11:32:05 SQL> CONNECT AYSHIBAT/xxxxxxxx@127.0.0.1:11521/orcl:POOLED （待たされる）
この状態で元クライアントの接続を切断すると……
11:32:08 SQL> 11:32:37 SQL> DISCONNECT Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Productionとの 接続が切断されました。 11:32:37 SQL>
待たされていたクライアントが繋がるんやで彡(ﾟ)(ﾟ)
11:32:05 SQL> CONNECT AYSHIBAT/xxxxxxxx@127.0.0.1:11521/orcl:POOLED 接続されました。 11:32:37 SQL>
DRCP接続の確立が待たされると、V$CPOOL_STATSビューのNUM_WAITSがカウントされるもよう
SET LINESIZE 120; COLUMN POOL_NAME FORMAT A40; SELECT POOL_NAME, NUM_REQUESTS, NUM_HITS, NUM_MISSES, NUM_WAITS FROM V$CPOOL_STATS; POOL_NAME NUM_REQUESTS NUM_HITS NUM_MISSES NUM_WAITS ---------------------------------------- ------------ ---------- ---------- ---------- SYS_DEFAULT_CONNECTION_POOL 2 0 2 1★コレ
例によってマニュアルや各種ドキュメントも見とくんやで彡(ﾟ)(ﾟ)
Oracle Database管理者ガイド 12cリリース2 (12.2) E72998-04
5.2 データベース常駐接続プーリングの理解
https://docs.oracle.com/cd/E82638_01/ADMIN/managing-processes.htm#GUID-BB76E57C-3F16-4C85-AEF6-BA14FC1B4777Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 12c リソース2 (12.2)
E85246-03
35 DBMS_CONNECTION_POOL
https://docs.oracle.com/cd/E82638_01/ARPLS/DBMS_CONNECTION_POOL.htm#GUID-C918B806-48D1-42F6-9B2E-B3F307164873Oracle Database管理者ガイド 12cリリース2 (12.2)
E72998-04
5.4.2.1 データベース常駐接続プーリングの構成パラメータ
https://docs.oracle.com/cd/E82638_01/ADMIN/managing-processes.htm#GUID-ABBE6941-73AC-4343-B320-A1400451B44Fデータベース常駐接続プーリング（DRCP）Oracle Database 11g
テクニカル・ホワイト・ペーパー
http://www.oracle.com/technetwork/jp/topics/oracledrcp11g-132231-ja.pdfOracle Databaseリファレンス 12cリリース2 (12.2)
E72905-03
F バックグラウンド・プロセス
https://docs.oracle.com/cd/E82638_01/REFRN/background-processes.htm#GUID-86184690-5531-405F-AA05-BB935F57B76D
：
Lnnn
プール・サーバー・プロセス
データベース常駐接続プーリングでクライアント要求を処理する
：
Nnnn
接続ブローカ・プロセス
データベース常駐接続プーリングでアイドル状態の接続を監視し、アクティブな接続を渡す
：
こちらの資料も大変参考になります！彡(^)(^)
Oracle常駐接続プーリング(DRCP)を導入した話
https://www.slideshare.net/kenken0807/oracledrcp
Oracle Database 12cR2(12.2)だとTABLEファンクションのTABLE句が省略できるらしいのでDBMS_XPLANで試してみる。
※本エントリで記述されている事項はドキュメント非記載のため、原則非サポートと考えて下さい。
※(2018/12/24追記)マニュアルに載ってました。
Oracle DatabaseデータベースPL/SQL言語リファレンス 18c
12.5.1 テーブル・ファンクションの概要
https://docs.oracle.com/cd/E96517_01/lnpls/plsql-optimization-and-tuning.html#GUID-4E10CBFA-4B6A-4761-8905-83C26C112694
次のように、同等の問合せをTABLE演算子なしで記述できます。
SELECT * FROM table_function_name(parameter_list)
下記ツイートで見かけたOracle Database 12cR2(12.2)の隠し機能彡(ﾟ)(ﾟ)
https://twitter.com/kibeha/status/955557265687359488
Huh? Is this new #OrclDB 12.2 undocumented feature that you can skip TABLE if you just add empty parentheses instead? Or is it just me that didn't know this was possible?
#SQL #PLSQL
12cR2(12.2)だとTABLEファンクションのTABLE句が省略できるらしい？彡(ﾟ)(ﾟ)
TABLEファンクションの代表と云えばDBMS_XPLAN、下記のようTABLE句を付けて使うのがセオリーですが。。。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => '9ht3ba3arrzt3')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 9ht3ba3arrzt3, child number 0 ------------------------------------- UPDATE KET$_CLIENT_TASKS SET STATUS = CASE STATUS WHEN 13 THEN 2 ELSE STATUS END, WINDOW_NAME = NULL, CURR_JOB_NAME = NULL, RETRY_COUNT = RETRY_COUNT+1, LT_JOB_LOG_ID = :log, LT_TERM_CODE = 11, LT_PRIORITY = TASK_PRIORITY, LT_ERROR = :err, LT_DATE = :end, LT_DURATION = :ela, LT_CPU_TIME = :cpu WHERE CLIENT_ID = :cid AND OPERATION_ID = :oid AND TARGET_TYPE = :tgt AND TARGET_NAME = :tgn Plan hash value: 3863298075 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | | | 1 (100)| | 1 | UPDATE | KET$_CLIENT_TASKS | | | | |* 2 | INDEX UNIQUE SCAN| KET$_TSK_PK | 1 | 112 | 0 (0)| ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CLIENT_ID"=:CID AND "OPERATION_ID"=:OID AND "TARGET_TYPE"=:TGT AND "TARGET_NAME"=:TGN) 27 rows selected. SQL>
これを12cR2(12.2)環境でTABLE句無しで実行してみると……彡(ﾟ)(ﾟ)
SQL> SELECT * FROM V$VERSION; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 PL/SQL Release 12.2.0.1.0 - Production 0 CORE 12.2.0.1.0 Production 0 TNS for Linux: Version 12.2.0.1.0 - Production 0 NLSRTL Version 12.2.0.1.0 - Production 0 SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(sql_id => '9ht3ba3arrzt3'); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------- SQL_ID 9ht3ba3arrzt3, child number 0 ------------------------------------- UPDATE KET$_CLIENT_TASKS SET STATUS = CASE STATUS WHEN 13 THEN 2 ELSE STATUS END, WINDOW_NAME = NULL, CURR_JOB_NAME = NULL, RETRY_COUNT = RETRY_COUNT+1, LT_JOB_LOG_ID = :log, LT_TERM_CODE = 11, LT_PRIORITY = TASK_PRIORITY, LT_ERROR = :err, LT_DATE = :end, LT_DURATION = :ela, LT_CPU_TIME = :cpu WHERE CLIENT_ID = :cid AND OPERATION_ID = :oid AND TARGET_TYPE = :tgt AND TARGET_NAME = :tgn Plan hash value: 3863298075 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | | | 1 (100)| | 1 | UPDATE | KET$_CLIENT_TASKS | | | | |* 2 | INDEX UNIQUE SCAN| KET$_TSK_PK | 1 | 112 | 0 (0)| ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CLIENT_ID"=:CID AND "OPERATION_ID"=:OID AND "TARGET_TYPE"=:TGT AND "TARGET_NAME"=:TGN) 27 rows selected.
マジやった！TABLE句省略できるやんけ！彡(ﾟ)(ﾟ)
V$SQLとその周辺でER図を描いてみよう！ - JPOUG Advent Calendar 2017 Day 15 -
JPOUG Advent Calendar 2017 の Day 15 の記事となります。
昨日は おおのたかしさん の記事でした彡(ﾟ)(ﾟ)
Oracle Database 12cR2へのアップグレード後に発生するORA-01017 https://www.ashisuto.co.jp/db_blog/article/201712-ora-01017.html
今年はV$SQLとその周辺でER図を描いてみたやで彡(ﾟ)(ﾟ)
Slideshareの元リンクは下記やで彡(ﾟ)(ﾟ)
V$SQLとその周辺でER図を描いてみよう！ https://www.slideshare.net/shibataayumu/vsqler
今回描いたV$SQL廻りのER図全景はこちら。
アプリでもインフラでも役立つER図/モデリングは最強のスキルなんやで彡(^)(^)
