docker rlwrap周りの整備で粘り勝った話

まえがき

docker images oracleを使う時にさっと環境整備したかったので、粘ってシェルスクリプト書き切った。

参考文献

su oracleがrootユーザーからできないとき、んっってなったので、調べた。

su: cannot open session: Permission denied  

事前にdockerホストにepel-repo登録しておく

ほんとは一元化したいけど、エスケープハマったので、妥協してしまった。粘り勝っていない。。エスケープ上手く扱えるようになりたい。

コード表示

[oracle@centos doclan]$ sudo cp /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7 /etc/yum.repos.d/epel.repo .
[sudo] oracle のパスワード:
[oracle@centos doclan]$ ll
合計 16
drwxr-xr-x. 3 root   root   4096  5月 25 13:14 19cr3
-rw-r--r--. 1 root   root   1662  5月 25 17:45 RPM-GPG-KEY-EPEL-7
-rwxr-xr-x. 1 oracle docker  659  5月 25 17:37 env.sh
-rw-r--r--. 1 root   root    951  5月 25 17:45 epel.repo

env.sh

汚い ^^;まぁ、うまいやりかた思いついたら、描き直そう。

コード表示

[oracle@centos doclan]$ cat env.sh
#!/bin/bash

echo 'root_pwd' | passwd --stdin root
yum install -y passwd && \
yum install -y sudo
echo 'oracle_pwd' | passwd --stdin oracle
echo 'oracle ALL=(ALL) NOPASSWD:ALL' >> /etc/sudoers
sed -i -e 's/^\(oracle   hard   memlock\)/#\1/' /etc/security/limits.d/oracle-database-preinstall-19c.conf

cp /mnt/RPM-GPG-KEY-EPEL-7 /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7 && \
cp /mnt/epel.repo /etc/yum.repos.d/epel.repo

su oracle -c 'cd ~ && \
sudo yum update -y && \
sudo yum install -y rlwrap && \
sudo yum install -y vim && \
sudo mkdir -p rlwrap-extensions && \
cd rlwrap-extensions && \
sudo curl -LO http://www.linuxification.at/download/rlwrap-extensions-V12-0.05.tar.gz && \
sudo tar xvfz rlwrap-extensions-V12-0.05.tar.gz
'

su oracle -c 'sed -i -e "$ a alias sqlplus=\"rlwrap -pRed -if ~/rlwrap-extensions/sqlplus sqlplus\"" ~/.bashrc'

su oracle -c '
cat </opt/oracle/product/19c/dbhome_1/sqlplus/admin/glogin.sql
COLUMN OTHER_PLUS_EXP FORMAT A200
COLUMN PLAN_PLUS_EXP  FORMAT A200
SET NUMWIDTH 13
SET LINESIZE 1000
SET LONG 40000
SET LONGCHUNKSIZE 40000
SET PAGESIZE 50000
SET SERVEROUTPUT ON
SET TAB OFF
SET TRIMSPOOL ON
set feed on
--set autotrace traceonly
set timing on
--set hist on
DEFINE_EDITOR=nano
set sqlp "_USER'''\'@''\''_CONNECT_IDENTIFIER> "
EOF
'
su oracle

実行

コード表示

[oracle@centos doclan]$ docker run -v /home/oracle/doclan:/mnt -v /home/oracle/doclan/19cr3/init:/docker-entrypoint-initdb.d/startup -d --name orcl_19cr3 --shm-size=4g -p 1521:1521 -p 5500:5500 -e TZ=Asia/Tokyo -e ORACLE_PWD=ORACLE_PWD -e ORACLE_SID=ORCL -e ORACLE_PDB=pdb1 oracle/database:19.3.0-ee
[oracle@centos doclan]$ docker ps -a
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                   PORTS                                            NAMES
42b3dd2da618        oracle/database:19.3.0-ee   "/bin/sh -c 'exec $O…"   7 hours ago         Up 7 hours (unhealthy)   0.0.0.0:1521->1521/tcp, 0.0.0.0:5500->5500/tcp   orcl_19cr3
[oracle@centos doclan]$ docker exec --interactive --tty --user root --workdir / orcl_19cr3 bash
bash-4.2# cd /mnt && ./env.sh
[oracle@42b3dd2da618 mnt]$ cat /opt/oracle/product/19c/dbhome_1/sqlplus/admin/glogin.sql
COLUMN OTHER_PLUS_EXP FORMAT A200
COLUMN PLAN_PLUS_EXP  FORMAT A200
SET NUMWIDTH 13
SET LINESIZE 1000
SET LONG 40000
SET LONGCHUNKSIZE 40000
SET PAGESIZE 50000
SET SERVEROUTPUT ON
SET TAB OFF
SET TRIMSPOOL ON
set feed on
--set autotrace traceonly
set timing on
--set hist on
DEFINE_EDITOR=nano
set sqlp "_USER'@'_CONNECT_IDENTIFIER> "
[oracle@42b3dd2da618 mnt]$ sqlplus sys/ORACLE_PWD@pdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 25 19:54:10 2019
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SYS@pdb1> show pdbs

       CON_ID CON_NAME                       OPEN MODE  RESTRICTED
------------- ------------------------------ ---------- ----------
            3 PDB1                           READ WRITE NO

あとがき

エスケープうまくなりたい。

clob型のカンマ区切り文字をちょっとずつ切り取ってちょろちょろcollection型にしていく話

qiitaに書きました。

clob型のカンマ区切り文字をちょっとずつ切り取ってちょろちょろcollection型にしていく話

sql oracle MATCH_RECOGNIZE lesson

ことはじめ

MATCH_RECOGNIZE句の動きを始める前より理解を深めたい。

参考文献

DEEP DIVE INTO 12c MATCH_RECOGNIZE  
Overview of Pattern Matching in Data Warehouses  
Pattern Matching (MATCH_RECOGNIZE) in Oracle Database 12c Release 1 (12.1)  
MATCH_RECOGNIZE - SKIP TO where exactly?  
MATCH_RECOGNIZE - Log file sessionization analysis  
Introduction to MATCH_RECOGNIZE

テストデータの準備

oracle_baseさんのデータを拝借。分かりやすいです。とても。

sales_historyを作成。

コード表示

DROP TABLE sales_history PURGE;

CREATE TABLE sales_history (
  id            NUMBER,
  product       VARCHAR2(20),
  tstamp        TIMESTAMP,
  units_sold    NUMBER,
  CONSTRAINT sales_history_pk PRIMARY KEY (id)
);

ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY';

INSERT INTO sales_history VALUES ( 1, 'TWINKIES', '01-OCT-2014', 17);
INSERT INTO sales_history VALUES ( 2, 'TWINKIES', '02-OCT-2014', 19);
INSERT INTO sales_history VALUES ( 3, 'TWINKIES', '03-OCT-2014', 23);
INSERT INTO sales_history VALUES ( 4, 'TWINKIES', '04-OCT-2014', 23);
INSERT INTO sales_history VALUES ( 5, 'TWINKIES', '05-OCT-2014', 16);
INSERT INTO sales_history VALUES ( 6, 'TWINKIES', '06-OCT-2014', 10);
INSERT INTO sales_history VALUES ( 7, 'TWINKIES', '07-OCT-2014', 14);
INSERT INTO sales_history VALUES ( 8, 'TWINKIES', '08-OCT-2014', 16);
INSERT INTO sales_history VALUES ( 9, 'TWINKIES', '09-OCT-2014', 15);
INSERT INTO sales_history VALUES (10, 'TWINKIES', '10-OCT-2014', 17);
INSERT INTO sales_history VALUES (11, 'TWINKIES', '11-OCT-2014', 23);
INSERT INTO sales_history VALUES (12, 'TWINKIES', '12-OCT-2014', 30);
INSERT INTO sales_history VALUES (13, 'TWINKIES', '13-OCT-2014', 31);
INSERT INTO sales_history VALUES (14, 'TWINKIES', '14-OCT-2014', 29);
INSERT INTO sales_history VALUES (15, 'TWINKIES', '15-OCT-2014', 25);
INSERT INTO sales_history VALUES (16, 'TWINKIES', '16-OCT-2014', 21);
INSERT INTO sales_history VALUES (17, 'TWINKIES', '17-OCT-2014', 35);
INSERT INTO sales_history VALUES (18, 'TWINKIES', '18-OCT-2014', 46);
INSERT INTO sales_history VALUES (19, 'TWINKIES', '19-OCT-2014', 45);
INSERT INTO sales_history VALUES (20, 'TWINKIES', '20-OCT-2014', 30);
COMMIT;

sales_historyの確認

コード表示

ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY';

SET PAGESIZE 50
COLUMN product    FORMAT A10
COLUMN tstamp     FORMAT A11
COLUMN graph      FORMAT A50

SELECT id,
       product,
       tstamp,
       units_sold,
       RPAD('#', units_sold, '#') AS graph
FROM   sales_history
ORDER BY id;
コード表示

           ID PRODUCT    TSTAMP         UNITS_SOLD GRAPH
------------- ---------- ----------- ------------- --------------------------------------------------
            1 TWINKIES   01-OCT-2014            17 #################
            2 TWINKIES   02-OCT-2014            19 ###################
            3 TWINKIES   03-OCT-2014            23 #######################
            4 TWINKIES   04-OCT-2014            23 #######################
            5 TWINKIES   05-OCT-2014            16 ################
            6 TWINKIES   06-OCT-2014            10 ##########
            7 TWINKIES   07-OCT-2014            14 ##############
            8 TWINKIES   08-OCT-2014            16 ################
            9 TWINKIES   09-OCT-2014            15 ###############
           10 TWINKIES   10-OCT-2014            17 #################
           11 TWINKIES   11-OCT-2014            23 #######################
           12 TWINKIES   12-OCT-2014            30 ##############################
           13 TWINKIES   13-OCT-2014            31 ###############################
           14 TWINKIES   14-OCT-2014            29 #############################
           15 TWINKIES   15-OCT-2014            25 #########################
           16 TWINKIES   16-OCT-2014            21 #####################
           17 TWINKIES   17-OCT-2014            35 ###################################
           18 TWINKIES   18-OCT-2014            46 ##############################################
           19 TWINKIES   19-OCT-2014            45 #############################################
           20 TWINKIES   20-OCT-2014            30 ##############################

20 rows selected.

Elapsed: 00:00:00.02

拝借したsqlをそのまま実行

コード表示

SET LINESIZE 110
ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY';

COLUMN start_tstamp FORMAT A11
COLUMN peak_tstamp  FORMAT A11
COLUMN end_tstamp   FORMAT A11
COLUMN cls          FORMAT A5
COLUMN units_sold FORMAT 9999
set linesize 200

SELECT *
FROM   sales_history MATCH_RECOGNIZE (
         PARTITION BY product
         ORDER BY tstamp
         MEASURES  STRT.tstamp AS start_tstamp,
                   FINAL LAST(UP.tstamp) AS peak_tstamp,
                   FINAL LAST(DOWN.tstamp) AS end_tstamp,
                   MATCH_NUMBER() AS mno,
                   CLASSIFIER() AS cls
         ALL ROWS PER MATCH
         AFTER MATCH SKIP TO LAST DOWN
         PATTERN (STRT UP+ FLAT* DOWN+)
         DEFINE
           UP AS UP.units_sold > PREV(UP.units_sold),
           DOWN AS DOWN.units_sold < PREV(DOWN.units_sold),
           FLAT AS FLAT.units_sold = PREV(FLAT.units_sold)
       ) MR
ORDER BY MR.product, MR.mno, MR.tstamp;

FINAL LAST(pattern.col_name)は定義したパタンが最後に現れたcol_name値を返却する。パタンマッチした範囲(フレーム)の中で一番大きい値かな。

STRTはパタン変数の定義のみにとどまっているが、これはパタンマッチの開始点として定義している。開始点はのこりのパタン定義より判断できるからだとおもう。

MATCH_NUMBER()に関してはパタンマッチした範囲における通番を返却。どの範囲ないし期間かを判別できる番号を返却。

pattern句に定義した順にパタンマッチをしていくので、定義する順番を変えてしまうと、異なる結果が得られる。パタンを単純に定義するだけでなく、パタンマッチしていく順番も考慮に入れる必要がある。

CLASSIFIER()に関してはパタンマッチした範囲において当該レコードがどのパタンに属しているのかを判別できる値を返却してくれる。

pattern句に定義した最後のパタンを見つけるまで、走査し、今回の例だとDOWN。発見した次のパタンはSTRT。そこでまたDOWNパタンが見つかるまで走査。多分その繰り返し。パタンを定義する際には最初と最後のパタンをまず決めて、そのパタン走査の中で、起こりうるパタンを定義していくイメージが書きやすそう。

AFTER MATCH SKIP TO LAST パタン変数に関してはパタンマッチした範囲において最後に現れたパタン変数はそのパタンとしては認識せず、次のパタンとして認識するように明示的指定できるもの。今回のAFTER MATCH SKIP TO LAST DOWNに関していえばパタンマッチした範囲の中で、最後に現れたDOWNパタンはスキップし、STRTパタンとしてパタンマッチするように促すことができる。2つのパタン定義を満たす1点が存在するときに、そのどちらのパタンを優先させてやるかを指定できる。id列にその観点が表現されている。

PER MATCHで出力する行をサマリ行か明細行も含めたすべてかを指定することができる。以下の資料が参考になる。

AFTER MATCH SKIP: Defining Where to Restart the Matching Process After a Match Is Found  
Tasks and Keywords in Pattern Matching PER MATCH: Choosing Summaries or Details for Each Match

パーティション違いでも効くか試してみる

コード表示

insert into sales_history select max(id) over () + rownum as ID, 'kiwiiiii' as PRODUCT, TSTAMP, UNITS_SOLD from sales_history;
commit;

SET LINESIZE 110
ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY';

COLUMN start_tstamp FORMAT A11
COLUMN peak_tstamp  FORMAT A11
COLUMN end_tstamp   FORMAT A11
COLUMN cls          FORMAT A5
COLUMN units_sold FORMAT 9999
set linesize 200

SELECT *
FROM   sales_history MATCH_RECOGNIZE (
         PARTITION BY product
         ORDER BY tstamp
         MEASURES  STRT.tstamp AS start_tstamp,
                   FINAL LAST(UP.tstamp) AS peak_tstamp,
                   FINAL LAST(DOWN.tstamp) AS end_tstamp,
                   MATCH_NUMBER() AS mno,
                   CLASSIFIER() AS cls
         ALL ROWS PER MATCH
         AFTER MATCH SKIP TO LAST DOWN
         PATTERN (STRT UP+ FLAT* DOWN+)
         DEFINE
           UP AS UP.units_sold > PREV(UP.units_sold),
           DOWN AS DOWN.units_sold < PREV(DOWN.units_sold),
           FLAT AS FLAT.units_sold = PREV(FLAT.units_sold)
       ) MR
ORDER BY MR.product, MR.mno, MR.tstamp;

パーティションごとにパタンマッチされている

パタン句の順番を変えてみる

STRT→DOWN→FLAT→UP→STRTの順に。

コード表示

SET LINESIZE 110
ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY';

COLUMN start_tstamp FORMAT A11
COLUMN peak_tstamp  FORMAT A11
COLUMN end_tstamp   FORMAT A11
COLUMN cls          FORMAT A5
COLUMN units_sold FORMAT 9999
set linesize 200

SELECT *
FROM   sales_history MATCH_RECOGNIZE (
         PARTITION BY product
         ORDER BY tstamp
         MEASURES  STRT.tstamp AS start_tstamp,
                   FINAL LAST(UP.tstamp) AS peak_tstamp,
                   FINAL LAST(DOWN.tstamp) AS end_tstamp,
                   MATCH_NUMBER() AS mno,
                   CLASSIFIER() AS cls
         ALL ROWS PER MATCH
         AFTER MATCH SKIP TO LAST UP
         PATTERN (STRT DOWN+ FLAT* UP+)
         DEFINE
           UP AS UP.units_sold > PREV(UP.units_sold),
           DOWN AS DOWN.units_sold < PREV(DOWN.units_sold),
           FLAT AS FLAT.units_sold = PREV(FLAT.units_sold)
       ) MR
ORDER BY MR.product, MR.mno, MR.tstamp;

FLATのパタンマッチが見えなくなった。日付をまたいで同じUNITS_SOLDがなくなったから。

LAST()をFIRST()に変えてみる

コード表示

SET LINESIZE 110
ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY';

COLUMN start_tstamp FORMAT A11
COLUMN peak_tstamp  FORMAT A11
COLUMN end_tstamp   FORMAT A11
COLUMN cls          FORMAT A5
COLUMN units_sold FORMAT 9999
set linesize 200

SELECT *
FROM   sales_history MATCH_RECOGNIZE (
         PARTITION BY product
         ORDER BY tstamp
         MEASURES  STRT.tstamp AS start_tstamp,
                   FINAL FIRST(UP.tstamp) AS peak_tstamp,
                   FINAL FIRST(DOWN.tstamp) AS end_tstamp,
                   MATCH_NUMBER() AS mno,
                   CLASSIFIER() AS cls
         ALL ROWS PER MATCH
         AFTER MATCH SKIP TO LAST UP
         PATTERN (STRT DOWN+ FLAT* UP+)
         DEFINE
           UP AS UP.units_sold > PREV(UP.units_sold),
           DOWN AS DOWN.units_sold < PREV(DOWN.units_sold),
           FLAT AS FLAT.units_sold = PREV(FLAT.units_sold)
       ) MR
ORDER BY MR.product, MR.mno, MR.tstamp;

パタンマッチした範囲でUPパタン、DOWNパタンが最初に現れた日付を返却している。

FINALをとってみる

コード表示

SET LINESIZE 110
ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY';

COLUMN start_tstamp FORMAT A11
COLUMN peak_tstamp  FORMAT A11
COLUMN end_tstamp   FORMAT A11
COLUMN cls          FORMAT A5
COLUMN units_sold FORMAT 9999
set linesize 200

SELECT *
FROM   sales_history MATCH_RECOGNIZE (
         PARTITION BY product
         ORDER BY tstamp
         MEASURES  STRT.tstamp AS start_tstamp,
                   FIRST(UP.tstamp) AS peak_tstamp,
                   FINAL FIRST(DOWN.tstamp) AS end_tstamp,
                   MATCH_NUMBER() AS mno,
                   CLASSIFIER() AS cls
         ALL ROWS PER MATCH
         AFTER MATCH SKIP TO LAST UP
         PATTERN (STRT DOWN+ FLAT* UP+)
         DEFINE
           UP AS UP.units_sold > PREV(UP.units_sold),
           DOWN AS DOWN.units_sold < PREV(DOWN.units_sold),
           FLAT AS FLAT.units_sold = PREV(FLAT.units_sold)
       ) MR
ORDER BY MR.product, MR.mno, MR.tstamp;

FINALがついていない列にnullが現れた。パタンマッチした範囲を最初に枠決めすると思われる。その範囲においてカレント行がUPパタンなのか、DOWNパタンなのかをしていくと思うので、まだ検知されていないから、返却する値が分からず、NULLが現れた。

measures句に集計関数をいれてみる

measures句は対象テーブルのカラムに加えて、確認したい列を定義したパタンを使って独自に指定できる。pattern句には正規表現が使える。

コード表示

SET LINESIZE 110
ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY';

COLUMN start_tstamp FORMAT A11
COLUMN peak_tstamp  FORMAT A11
COLUMN end_tstamp   FORMAT A11
COLUMN cls          FORMAT A5
COLUMN units_sold FORMAT 9999
set linesize 200

SELECT *
FROM   sales_history MATCH_RECOGNIZE (
         PARTITION BY product
         ORDER BY tstamp
         MEASURES  STRT.tstamp AS start_tstamp,
                   FINAL LAST(UP.tstamp) AS peak_tstamp,
                   FINAL LAST(DOWN.tstamp) AS end_tstamp,
                   FINAL count(UP.tstamp) AS up_cnt,
                   FINAL count(FLAT.tstamp) AS flat_cnt,
                   FINAL count(DOWN.tstamp) AS down_cnt,
                   RUNNING count(tstamp) AS runnig_cnt,
                   FINAL count(tstamp) AS summury_cnt,
                   units_sold - STRT.units_sold AS diff,
                   MATCH_NUMBER() AS mno,
                   CLASSIFIER() AS cls
         ALL ROWS PER MATCH
         AFTER MATCH SKIP TO LAST DOWN
         PATTERN (STRT UP+ FLAT* DOWN+ )
         DEFINE
           UP AS UP.units_sold > PREV(UP.units_sold),
           DOWN AS DOWN.units_sold < PREV(DOWN.units_sold),
           FLAT AS FLAT.units_sold = PREV(FLAT.units_sold)
       ) MR
ORDER BY MR.product, MR.mno, MR.tstamp;

runningキーワードはパタンマッチした範囲で通番を振ってくれる。集計関数はパタンマッチした範囲の中で、適用される。

ここで、ちょっとグラフ化してみる

対象データはこれにした。

コード表示

insert into sales_history select max(id) over () + rownum as ID, 'kiwiiiii' as PRODUCT, TSTAMP, UNITS_SOLD - 10 as UNITS_SOLD from sales_history;
commit;

エクセルに食わせるデータはこのsqlで生成

コード表示

ALTER SESSION SET nls_timestamp_format = 'YYYYMMDD';

WITH tbl_head AS (
SELECT
	tstamp
	,MIN(twinkies) AS twinkies
	,MIN(kiwiiiii) AS kiwiiiii
FROM
	sales_history
	PIVOT(MIN(units_sold) FOR product IN ('TWINKIES' AS twinkies,'kiwiiiii' AS kiwiiiii))
GROUP BY
	tstamp
ORDER BY
	tstamp
),tbl_side AS (
SELECT
	to_char(dtt,'yyyymmdd') AS dt
FROM
	dual
MODEL
	DIMENSION BY ( 1 AS rn )
	MEASURES ( TO_DATE('20141031') AS dtt )
	RULES ITERATE(31)
	( dtt[iteration_number] = TO_DATE('20141031') - iteration_number )
)
SELECT
	nvl(tstamp,dt) AS tstamp
	,twinkies
	, kiwiiiii
FROM
	tbl_side s1
		LEFT OUTER JOIN tbl_head s2
	ON
		s1.dt = s2.tstamp
ORDER BY
	s1.dt
;

パタン句にパタンを追加してみる

STRT→DOWN+→FLAT*→UP+→DOWN+→FLAT*→UP+。W型パタンあるか。FLATはあれば検知する。

コード表示

SET LINESIZE 110
ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY';

COLUMN start_tstamp FORMAT A11
COLUMN peak_tstamp  FORMAT A11
COLUMN end_tstamp   FORMAT A11
COLUMN cls          FORMAT A5
COLUMN units_sold FORMAT 9999
set linesize 200

SELECT *
FROM   sales_history MATCH_RECOGNIZE (
         PARTITION BY product
         ORDER BY tstamp
         MEASURES  STRT.tstamp AS start_tstamp,
                   LAST(DOWN.tstamp) AS cur_bottom_tstamp,
                   FINAL LAST(DOWN.tstamp) AS bottom_tstamp,
                   LAST(UP.tstamp) AS cur_peak_tstamp,
                   FINAL LAST(UP.tstamp) AS peak_tstamp,
                   MATCH_NUMBER() AS mno,
                   CLASSIFIER() AS cls
         ALL ROWS PER MATCH
         AFTER MATCH SKIP TO LAST UP
         PATTERN (STRT DOWN+ FLAT* UP+ DOWN+ FLAT* UP+)
         DEFINE
           UP AS UP.units_sold > PREV(UP.units_sold),
           DOWN AS DOWN.units_sold < PREV(DOWN.units_sold),
           FLAT AS FLAT.units_sold = PREV(FLAT.units_sold)
       ) MR
ORDER BY MR.product, MR.mno, MR.tstamp;

セッションナイズはじめてみる

セッショナイズの定義はいろいろあるみたいだけど、今回は同一のユーザーの連続するアクセスで、アクセス間隔が3秒未満である一連のアクセスを一つのセッションとしてみる。

コード表示

COL NAME FOR a40;
COL VALUE FOR a100;

SELECT NAME, VALUE FROM v$parameter WHERE NAME LIKE '%' || 'timestamp' ||'%';

ALTER SESSION SET nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS';

DROP TABLE test______ PURGE;

CREATE TABLE test______ AS 
WITH sub AS (
SELECT
	*
FROM
	dual
MODEL
	DIMENSION BY ( 1 AS rn )
	MEASURES ( current_timestamp AS tp )
	RULES ITERATE(30)
	( tp[iteration_number] = current_timestamp - iteration_number/86400 )
ORDER BY
	dbms_random.random()
)
SELECT
	rpad('user0',6,decode(MOD(ROWNUM,6),0,6,MOD(ROWNUM,6))) AS user_id
	,rpad('item',6,CHR(64 + decode(MOD(ROWNUM,4),0,4,MOD(ROWNUM,4)))) AS item
	,TRUNC(ABS(dbms_random.VALUE(1,10)),0) AS qty
	,tp
FROM
	sub
ORDER BY
	1,4
;

SELECT * FROM test______ ORDER BY user_id,tp;

コード表示

[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Feb 22 23:38:47 2019
Version 18.3.0.0.0

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

Last Successful login time: Fri Feb 22 2019 23:35:40 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> col name for a40;
AINE@pdb1> col value for a100;
AINE@pdb1>
AINE@pdb1> SELECT NAME, VALUE FROM v$parameter WHERE NAME LIKE '%' || 'timestamp' ||'%';

NAME                                     VALUE
---------------------------------------- ----------------------------------------------------------------------------------------------------
uniform_log_timestamp_format             TRUE
nls_timestamp_format
nls_timestamp_tz_format

3 rows selected.

Elapsed: 00:00:00.01
AINE@pdb1> ALTER SESSION SET nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS';

Session altered.

Elapsed: 00:00:00.00
AINE@pdb1> SELECT NAME, VALUE FROM v$parameter WHERE NAME LIKE '%' || 'timestamp' ||'%';

NAME                                     VALUE
---------------------------------------- ----------------------------------------------------------------------------------------------------
uniform_log_timestamp_format             TRUE
nls_timestamp_format
nls_timestamp_tz_format                  YYYY-MM-DD HH24:MI:SS

3 rows selected.

Elapsed: 00:00:00.01
AINE@pdb1>

サマリ行だけ出力するようにしてみる

コード表示

SELECT
	*
FROM
	test______ MATCH_RECOGNIZE(
		PARTITION BY user_id
		ORDER BY tp
		MEASURES
			match_number() as session_id
			,CLASSIFIER() AS cls
			,COUNT(*) as evt_cnt
			,FIRST(strt.tp) as start_time
			,LAST(dur.tp) as end_time
			,LAST(dur.tp) - FIRST(strt.tp) as session_duration
   ONE ROW PER MATCH
--   ALL ROWS PER MATCH
   PATTERN (strt dur+)
   DEFINE
       dur as (tp - prev(tp) <= INTERVAL '3' SECOND )
 );

明細行も出力するようにしてみる

コード表示

SELECT
	*
FROM
	test______ MATCH_RECOGNIZE(
		PARTITION BY user_id
		ORDER BY tp
		MEASURES
			match_number() as session_id
			,CLASSIFIER() AS cls
			,COUNT(*) as evt_cnt
			,FIRST(strt.tp) as start_time
			,LAST(dur.tp) as end_time
			,LAST(dur.tp) - FIRST(strt.tp) as session_duration
--   ONE ROW PER MATCH
   ALL ROWS PER MATCH
   PATTERN (strt dur+)
   DEFINE
       dur as (tp - prev(tp) <= INTERVAL '3' SECOND )
 );

コード表示

USER_ID                  ITEM                               QTY TP
------------------------ ------------------------ ------------- ---------------------------------------------------------------------------
user01                   itemCC                               9 2019-02-23 01:14:09←★開始行
user01                   itemAA                               9 2019-02-23 01:14:18←★一行前と比べて3秒以内のアクセスではないため、開始行
user01                   itemAA                               6 2019-02-23 01:14:19←★一行前と比べて3秒以内のアクセスであるため、連続行
user01                   itemAA                               4 2019-02-23 01:14:24←★一行前と比べて3秒以内のアクセスではないため、開始行
user01                   itemCC                               6 2019-02-23 01:14:32←★一行前と比べて3秒以内のアクセスではないため、開始行

コード表示

USER_ID                  ITEM                               QTY TP
------------------------ ------------------------ ------------- ---------------------------------------------------------------------------
user04                   itemBB                               4 2019-02-23 01:14:13←★開始行
user04                   itemDD                               4 2019-02-23 01:14:14←★一行前と比べて3秒以内のアクセスであるため、連続行
user04                   itemBB                               2 2019-02-23 01:14:20←★一行前と比べて3秒以内のアクセスではないため、開始行
user04                   itemDD                               7 2019-02-23 01:14:28←★一行前と比べて3秒以内のアクセスではないため、開始行
user04                   itemDD                               7 2019-02-23 01:14:29←★一行前と比べて3秒以内のアクセスであるため、連続行

partition by で区切ったあと、order byで並べて、パタンマッチを先頭行から開始する。COUNT(*)の振る舞いとしてはパタンマッチした範囲で通番を振ってくれる。ここからデフォルトはrunningキーワードをつけたときと同じことがわかる。今回の場合はPATTERN (strt dur+)を1パタンとして認識した場合のこと。パタンごとの回数をみたいときはパタン変数.カラム名を引数に指定して挙動を調べる。

コード表示

SELECT
	*
FROM
	test______ MATCH_RECOGNIZE(
		PARTITION BY user_id
		ORDER BY tp
		MEASURES
			match_number() as session_id
			,CLASSIFIER() AS cls
			,COUNT(*) as evt_cnt
			,COUNT(strt.tp) as evt__cnt
			,COUNT(dur.tp) as evt___cnt
			,FIRST(strt.tp) as start_time
			,LAST(dur.tp) as end_time
			,LAST(dur.tp) - FIRST(strt.tp) as session_duration
   ONE ROW PER MATCH
--   ALL ROWS PER MATCH
   PATTERN (strt dur+)
   DEFINE
       dur as (tp - prev(tp) <= INTERVAL '3' SECOND )
 );

コード表示

SELECT
	*
FROM
	test______ MATCH_RECOGNIZE(
		PARTITION BY user_id
		ORDER BY tp
		MEASURES
			match_number() as session_id
			,CLASSIFIER() AS cls
			,COUNT(*) as evt_cnt
			,COUNT(strt.tp) as evt__cnt
			,COUNT(dur.tp) as evt___cnt
			,FIRST(strt.tp) as start_time
			,LAST(dur.tp) as end_time
			,LAST(dur.tp) - FIRST(strt.tp) as session_duration
--   ONE ROW PER MATCH
   ALL ROWS PER MATCH
   PATTERN (strt dur+)
   DEFINE
       dur as (tp - prev(tp) <= INTERVAL '3' SECOND )
 );

分析関数でも少し掘り下げてみる。cum列の値が連続している行がONE ROW PER MATCHで出力されている行。開始時刻と終了時刻は連続行をサマレば、出力できる。

コード表示

with tmp as (
select
	s1.*
	,case
		when s1.tp - lag(s1.tp) over (partition by user_id order by s1.tp) <= interval '3' second then 0
		else 1
	end as session_start_flg
from
	TEST______ s1
)
select
	s1.*
	,sum(s1.session_start_flg) over (partition by user_id order by tp rows between unbounded preceding and current row) as cum
from
	tmp s1
;

セッションをグラフ化してみる

以下のsqlでEXCELに食わせるデータを作成。nls_timestamp_tz_format型を使っていると、インクリうまくいかなくてあーだーこーだなったので、sysdateでうまくいったやつをあとでのせた。こいつは汎用性ナイ。nls_date_formatのほうがいいね。これはたまたまうまくいった。

コード表示

WITH cal AS (
	SELECT
		tp
	FROM
		test______
),tmp AS (
	SELECT
		s1.*
		,s0.*
	FROM
		cal s1
		LEFT OUTER JOIN 
		test______ MATCH_RECOGNIZE(
			PARTITION BY user_id
			ORDER BY tp
			MEASURES
				match_number() AS session_id
				,classifier() AS cls
				,COUNT(*) AS evt_cnt
				,COUNT(strt.tp) AS evt__cnt
				,COUNT(dur.tp) AS evt___cnt
				,FIRST(strt.tp) AS start_time
				,LAST(dur.tp) AS end_time
				,LAST(dur.tp) - FIRST(strt.tp) AS session_duration
	   ONE ROW PER MATCH
	--   ALL ROWS PER MATCH
	   PATTERN (strt dur+)
	   DEFINE
		   dur AS (tp - prev(tp) <= INTERVAL '3' SECOND )
	)s0 
		ON
			s1.tp BETWEEN s0.start_time AND s0.end_time
)
SELECT
	s1.tp
	, nvl(s1.user01,0) AS user01
	, nvl(s1.user02,0) AS user02
	, nvl(s1.user03,0) AS user03
	, nvl(s1.user04,0) AS user04
	, nvl(s1.user05,0) AS user05
	, nvl(s1.user06,0) AS user06
FROM
	tmp s0
	PIVOT(MIN(s0.evt__cnt) FOR user_id IN ('user01' AS user01,'user02' AS user02,'user03' AS user03,'user04' AS user04,'user05' AS user05,'user06' AS user06)) s1
;

取得できたデータ

グラフにした。本当はパルスで書きたいけど、いろいろねばってもメンテめんどくさすぎて妥協。。簡単にできるやつないかな。エクセルで。時間幅細かくするにもデータ増幅複写しないと実現できないからぜんぜんハンディじゃない。手軽さがほしい。まあ、雰囲気わかるからいっか。いったん。

セッションに対してパタンマッチしてみる

URLへのアクセスをシグネチャに置き換えることでパタンマッチしやすくする。マスタデータみたいなものを用意しておく。

コード表示

DROP TABLE url_sig PURGE;
CREATE TABLE url_sig AS
WITH URL AS (
SELECT
	'/' AS req_path
	,'T' AS sig
	,'top page' AS desp
FROM
	dual
UNION ALL
SELECT
	'search' AS req_path
	,'S' AS sig
	,'item search' AS desp
FROM
	dual
UNION ALL
SELECT
	'items' AS req_path
	,'L' AS sig
	,'item list' AS desp
FROM
	dual
UNION ALL
SELECT
	'items/detail' AS req_path
	,'ID' AS sig
	,'item detail' AS desp
FROM
	dual
UNION ALL
SELECT
	'cart' AS req_path
	,'C' AS sig
	,'cart list' AS desp
FROM
	dual
UNION ALL
SELECT
	'cart/add' AS req_path
	,'CA' AS sig
	,'add item into cart' AS desp
FROM
	dual
UNION ALL
SELECT
	'cart/remove' AS req_path
	,'CR' AS sig
	,'remove item from cart' AS desp
FROM
	dual
UNION ALL
SELECT
	'cart/chkout' AS req_path
	,'CO' AS sig
	,'checkout' AS desp
FROM
	dual
)
SELECT
	*
FROM
	URL
;
コード表示

AINE@pdb1> select * from url_sig;

REQ_PATH      SI DESP
------------- -- ---------------------
/             T  top page
/search       S  item search
/items        L  item list
/items/detail ID item detail
/cart         C  cart list
/cart/add     CA add item into cart
/cart/remove  CR remove item from cart
/cart/chkout  CO checkout

8 rows selected.

Elapsed: 00:00:00.02

セッションに対してパタンマッチしてみるためのトランデータつくる

以下のSQLで生成。

コード表示

DROP TABLE test_________ PURGE;

CREATE TABLE test_________ AS 
WITH sub AS (
SELECT
	rownum as rn
	,tp
FROM(
	SELECT
		DISTINCT tp
	FROM
		dual
	MODEL
		DIMENSION BY ( 1 AS rn )
		MEASURES ( sysdate AS tp )
		RULES ITERATE(100)
		( tp[iteration_number] = sysdate - TRUNC(ABS(dbms_random.VALUE(1, 100)), 0)/86400 )
	)tmp
	WHERE
		ROWNUM <= 30
)
SELECT
	rpad('user0',6,decode(MOD(ROWNUM,6),0,6,MOD(ROWNUM,6))) AS user_id
	,s1.tp
	,s2.req_path
FROM
	sub s1
		inner join (SELECT ROWNUM AS rn ,s1.req_path FROM url_sig s1 ORDER BY dbms_random.random()) s2
			on
				decode(mod(s1.rn,8),0,8,mod(s1.rn,8)) = s2.rn
ORDER BY
	1,2
;

SELECT * FROM test_________ ORDER BY user_id,tp;
コード表示

[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Feb 23 20:42:04 2019
Version 18.3.0.0.0

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

Last Successful login time: Sat Feb 23 2019 19:43:30 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> ALTER SESSION SET nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS';

Session altered.

Elapsed: 00:00:00.00
AINE@pdb1> SELECT * FROM test_________ ORDER BY user_id,tp;

USER_ID                  TP                                                                          REQ_PATH
------------------------ --------------------------------------------------------------------------- -------------
user01                   2019-02-23 20:39:50                                                         /
user01                   2019-02-23 20:39:52                                                         /items
user01                   2019-02-23 20:39:58                                                         /cart/remove
user01                   2019-02-23 20:40:22                                                         /cart
user01                   2019-02-23 20:40:27                                                         /cart/add
user02                   2019-02-23 20:40:04                                                         /
user02                   2019-02-23 20:40:07                                                         /items
user02                   2019-02-23 20:40:15                                                         /cart/remove
user02                   2019-02-23 20:40:36                                                         /cart
user02                   2019-02-23 20:40:37                                                         /cart/add
user03                   2019-02-23 20:39:51                                                         /search
user03                   2019-02-23 20:39:54                                                         /items/detail
user03                   2019-02-23 20:40:17                                                         /
user03                   2019-02-23 20:40:20                                                         /items
user03                   2019-02-23 20:40:28                                                         /cart/remove
user04                   2019-02-23 20:40:00                                                         /cart/chkout
user04                   2019-02-23 20:40:06                                                         /search
user04                   2019-02-23 20:40:08                                                         /items/detail
user04                   2019-02-23 20:40:32                                                         /
user04                   2019-02-23 20:40:34                                                         /items
user05                   2019-02-23 20:39:55                                                         /cart
user05                   2019-02-23 20:39:57                                                         /cart/add
user05                   2019-02-23 20:40:16                                                         /cart/chkout
user05                   2019-02-23 20:40:18                                                         /search
user05                   2019-02-23 20:40:21                                                         /items/detail
user06                   2019-02-23 20:40:09                                                         /cart
user06                   2019-02-23 20:40:11                                                         /cart/add
user06                   2019-02-23 20:40:29                                                         /cart/chkout
user06                   2019-02-23 20:40:33                                                         /search
user06                   2019-02-23 20:40:35                                                         /items/detail

30 rows selected.

Elapsed: 00:00:00.00

サマリ行出力してみる

コード表示

alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
SELECT
	*
FROM
	test_________ MATCH_RECOGNIZE (
		PARTITION BY user_id
		ORDER BY tp
		MEASURES
			match_number() AS session_id
		, classifier() AS cls
		, COUNT(*) AS evt_cnt
		, COUNT(strt.tp) AS evt__cnt
		, COUNT(dur.tp) AS evt___cnt
		, FIRST(strt.tp) AS start_time
		, LAST(dur.tp) AS end_time
		, round( (LAST(dur.tp) - FIRST(strt.tp) ) * 24 * 60 * 60, 0) AS session_duration
		ONE ROW PER MATCH
	--   ALL ROWS PER MATCH
	PATTERN ( strt dur +) DEFINE
		dur AS ( round( (tp - prev(tp) ) * 24 * 60 * 60, 0) <= 3 )
	);

グラフ化してみる

以下のSQLで生成。

コード表示

alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
WITH cal AS (
		SELECT
			 A.date_from + (ROWNUM - 1)/86400 AS tp
		FROM
			(
				SELECT
					MIN(tp) AS date_from
					,MAX(tp) AS date_to
					,round((MAX(tp) - MIN(tp))*24*60*60,0) AS diff
				FROM
					test_________
			) A
		CONNECT BY
			LEVEL <= A.diff + 1
),tmp AS (
	SELECT
		s1.*
		,s0.*
	FROM
		cal s1
		LEFT OUTER JOIN 
		test_________ MATCH_RECOGNIZE(
			PARTITION BY user_id
			ORDER BY tp
			MEASURES
				match_number() AS session_id
				,classifier() AS cls
				,COUNT(*) AS evt_cnt
				,COUNT(strt.tp) AS evt__cnt
				,COUNT(dur.tp) AS evt___cnt
				,FIRST(strt.tp) AS start_time
				,LAST(dur.tp) AS end_time
				,round((LAST(dur.tp) - FIRST(strt.tp))*24*60*60,0) AS session_duration
	   ONE ROW PER MATCH
	--   ALL ROWS PER MATCH
	   PATTERN (strt dur+)
	   DEFINE
		   dur AS (round((tp - prev(tp))*24*60*60,0) <= 3)
	)s0 
		ON
			s1.tp BETWEEN s0.start_time AND s0.end_time
)
SELECT
	s1.tp
	, nvl(s1.user01,0) AS user01
	, nvl(s1.user02,0) AS user02
	, nvl(s1.user03,0) AS user03
	, nvl(s1.user04,0) AS user04
	, nvl(s1.user05,0) AS user05
	, nvl(s1.user06,0) AS user06
FROM
	tmp s0
	PIVOT(MIN(s0.evt__cnt) FOR user_id IN ('user01' AS user01,'user02' AS user02,'user03' AS user03,'user04' AS user04,'user05' AS user05,'user06' AS user06)) s1
ORDER BY
	s1.tp
;
コード表示

TP                         USER01        USER02        USER03        USER04        USER05        USER06
------------------- ------------- ------------- ------------- ------------- ------------- -------------
2019/02/23 21:14:58             0             0             1             0             0             0
2019/02/23 21:14:59             0             0             1             0             0             0
2019/02/23 21:15:00             0             0             0             0             0             0
2019/02/23 21:15:01             0             0             0             0             0             0
2019/02/23 21:15:02             0             0             0             0             0             0
2019/02/23 21:15:03             1             0             0             0             0             0
2019/02/23 21:15:04             1             0             0             0             0             0
2019/02/23 21:15:05             1             0             0             0             0             0
2019/02/23 21:15:06             0             0             0             0             0             0
2019/02/23 21:15:07             0             0             0             0             0             0
2019/02/23 21:15:08             0             0             0             0             0             0
2019/02/23 21:15:09             0             0             0             0             0             0
2019/02/23 21:15:10             0             0             0             1             0             0
2019/02/23 21:15:11             0             0             0             1             0             0
2019/02/23 21:15:12             0             0             0             1             0             0
2019/02/23 21:15:13             0             0             0             1             0             0
2019/02/23 21:15:14             0             0             0             0             0             0
2019/02/23 21:15:15             0             0             0             0             0             0
2019/02/23 21:15:16             0             0             0             0             0             0
2019/02/23 21:15:17             0             1             0             0             0             0
2019/02/23 21:15:18             0             1             0             0             0             0
2019/02/23 21:15:19             0             1             0             0             0             0
2019/02/23 21:15:20             0             0             0             0             0             0
2019/02/23 21:15:21             0             0             0             0             0             0
2019/02/23 21:15:22             0             0             0             0             1             0
2019/02/23 21:15:23             0             0             0             0             1             0
2019/02/23 21:15:24             0             0             0             0             0             0
2019/02/23 21:15:25             0             0             0             0             0             0
2019/02/23 21:15:26             0             0             0             0             0             0
2019/02/23 21:15:27             0             0             0             0             0             0
2019/02/23 21:15:28             0             0             1             0             0             0
2019/02/23 21:15:29             0             0             1             0             0             0
2019/02/23 21:15:30             0             0             1             0             0             0
2019/02/23 21:15:31             0             0             1             0             0             0
2019/02/23 21:15:32             0             0             0             0             0             0
2019/02/23 21:15:33             0             0             0             0             0             0
2019/02/23 21:15:34             0             0             0             0             0             1
2019/02/23 21:15:35             0             0             0             0             0             1
2019/02/23 21:15:36             0             0             0             0             0             0
2019/02/23 21:15:37             0             0             0             0             0             0
2019/02/23 21:15:38             0             0             0             0             0             0
2019/02/23 21:15:39             0             0             0             0             0             0
2019/02/23 21:15:40             0             0             0             0             0             0

43 rows selected.

Elapsed: 00:00:00.01

セッションアクセスパタンを検知してみる

コンバージョン率を求めるときに使える。

コンバージョン率(成約率、コンバージョンレート、CVRともいう)とは、Webサイトの目標に達した数を、目標に達する最初の段階に入った数で割った割合のこと。 インターネット広告やECサイトで、効率を計るために用いる。

コード表示

WITH tmp AS (
SELECT
	s1.user_id
	,s1.session_id
	,MIN(s1.tp) AS start_tp
	,MAX(s1.tp) AS end_tp
	,LISTAGG(s0.sig,'') WITHIN GROUP (ORDER BY s1.tp) AS sig
FROM
	test_________ s1
	MATCH_RECOGNIZE (
		PARTITION BY user_id
		ORDER BY tp
		MEASURES
			match_number() AS session_id
		, classifier() AS cls
		, COUNT(*) AS evt_cnt
		, COUNT(strt.tp) AS evt__cnt
		, COUNT(dur.tp) AS evt___cnt
		, FIRST(strt.tp) AS start_time
		, LAST(dur.tp) AS end_time
		, round( (LAST(dur.tp) - FIRST(strt.tp) ) * 24 * 60 * 60, 0) AS session_duration
--		ONE ROW PER MATCH
	   ALL ROWS PER MATCH
	PATTERN ( strt dur +) DEFINE
		dur AS ( round( (tp - prev(tp) ) * 24 * 60 * 60, 0) <= 3 )
	)s1
	INNER JOIN url_sig s0
		ON
			s1.req_path = s0.req_path
GROUP BY
	s1.user_id
	,s1.session_id
)
SELECT
	s0.*
	,SUM(CASE WHEN REGEXP_LIKE(s0.sig,'T+S+(CA)*(CO)*','i') THEN 1 ELSE 0 END) OVER (PARTITION BY s0.user_id,s0.session_id) AS flg
	,SUM(CASE WHEN REGEXP_LIKE(s0.sig,'T+S+(CA)*(CO)*','i') THEN 1 ELSE 0 END) OVER (PARTITION BY s0.user_id,s0.session_id)
	/ COUNT(*) OVER (PARTITION BY s0.user_id)
	AS cvr
FROM
	tmp s0
;

ことおわり

始める前より理解はすこし深まったとおもう。長くなったので、いったんここで区切る。

sql oracle statspack インストールからレポートの作成まで

STATSPACKのインストール

statspack 用の表領域作成前

これがプラガブルデータベースのデフォルトのデータファイルたち

コード表示

[oracle@6898242bcdd5 ~]$ cd $ORACLE_BASE/oradata/ORCL
[oracle@6898242bcdd5 ORCL]$ pwd
/opt/oracle/oradata/ORCL
[oracle@6898242bcdd5 ORCL]$ ll
total 2728360
drwxr-x---. 2 oracle oinstall       104 Jan 20 08:55 PDB1
-rw-r-----. 1 oracle oinstall  18726912 Jan 26 07:23 control01.ctl
-rw-r-----. 1 oracle oinstall  18726912 Jan 26 07:23 control02.ctl
drwxr-x---. 2 oracle oinstall       111 Jan 20 08:49 pdbseed
-rw-r-----. 1 oracle oinstall 209715712 Jan 26 07:22 redo01.log
-rw-r-----. 1 oracle oinstall 209715712 Jan 25 08:55 redo02.log
-rw-r-----. 1 oracle oinstall 209715712 Jan 25 23:11 redo03.log
-rw-r-----. 1 oracle oinstall 880812032 Jan 26 07:21 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 901783552 Jan 26 07:21 system01.dbf
-rw-r-----. 1 oracle oinstall  34611200 Jan 26 07:21 temp01.dbf
-rw-r-----. 1 oracle oinstall 335552512 Jan 26 07:22 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Jan 25 23:16 users01.dbf

statspack用の表領域作成

/opt/oracle/oradata/ORCL/stats.dbf表領域をstatspack用に作成。あとに作成するPERFSTATユーザーのデフォルトの表領域サイズは180MB以上を指定しないといけないぽい。。2地点間のダイナミックパフォーマンスビューを格納していくので、専用の表領域を作成したほうがよいぽい。たしかにそうおもう。

コード表示

create tablespace stats datafile '/opt/oracle/oradata/ORCL/stats.dbf' size 300m autoextend on next 30m segment space management auto;
コード表示

[oracle@6898242bcdd5 ~]$ sqlplus sys/ORACLE_PWD@pdb1 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Jan 26 07:32:45 2019
Version 18.3.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SYS@pdb1> create tablespace stats datafile '/opt/oracle/oradata/ORCL/stats.dbf' size 300m autoextend on next 30m segment space management auto;

Tablespace created.

Elapsed: 00:00:00.40

statspack用の表領域作成後

/opt/oracle/oradata/ORCL/stats.dbf表領域がstatspack用に作成されていることを確認

コード表示

[oracle@6898242bcdd5 ~]$ cd $ORACLE_BASE/oradata/ORCL
[oracle@6898242bcdd5 ORCL]$ ll
total 3035568
drwxr-x---. 2 oracle oinstall       104 Jan 20 08:55 PDB1
-rw-r-----. 1 oracle oinstall  18726912 Jan 26 07:37 control01.ctl
-rw-r-----. 1 oracle oinstall  18726912 Jan 26 07:37 control02.ctl
drwxr-x---. 2 oracle oinstall       111 Jan 20 08:49 pdbseed
-rw-r-----. 1 oracle oinstall 209715712 Jan 26 07:37 redo01.log
-rw-r-----. 1 oracle oinstall 209715712 Jan 25 08:55 redo02.log
-rw-r-----. 1 oracle oinstall 209715712 Jan 25 23:11 redo03.log
-rw-r-----. 1 oracle oinstall 314580992 Jan 26 07:32 stats.dbf
-rw-r-----. 1 oracle oinstall 880812032 Jan 26 07:37 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 901783552 Jan 26 07:37 system01.dbf
-rw-r-----. 1 oracle oinstall  34611200 Jan 26 07:21 temp01.dbf
-rw-r-----. 1 oracle oinstall 335552512 Jan 26 07:37 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Jan 25 23:16 users01.dbf

PERFSTATユーザーの確認

PERFSTATユーザーが作成されていないことを確認

コード表示

select username,created from dba_users where username = 'PERFSTAT';
コード表示

SYS@pdb1> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
SYS@pdb1> col username for a40
SYS@pdb1> select username,created from dba_users where username = 'PERFSTAT';

no rows selected

Elapsed: 00:00:00.02

spcreate.sqlスクリプトの格納場所

spcreate.sqlの格納を確認。spcreate.sqlスクリプトは内部的にはspcusr.sqlspctab.sqlspcpkg.sqlの3つのスクリプトを実行。

コード表示

[oracle@6898242bcdd5 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@6898242bcdd5 admin]$ pwd
/opt/oracle/product/18c/dbhome_1/rdbms/admin
[oracle@6898242bcdd5 admin]$ ll | grep create
-rw-r--r--. 1 oracle dba    1334 May 29  2017 dbfs_create_filesystem.sql
-rw-r--r--. 1 oracle dba    6165 May 29  2017 dbfs_create_filesystem_advanced.sql
-rw-r--r--. 1 oracle dba    1498 May 29  2017 sbcreate.sql
-rw-r--r--. 1 oracle dba    1796 May 29  2017 spcreate.sql
[oracle@6898242bcdd5 admin]$ vim spcreate.sql

コード表示

--  Create PERFSTAT user and required privileges
@@spcusr

-- Next two scripts run as perfstat user
ALTER SESSION SET CURRENT_SCHEMA = PERFSTAT;

-- Create statspack tables
@@spctab

-- Create the statistics Package
@@spcpkg

spcreate.sqlスクリプトの実行

実行されたそれぞれのスクリプトは、spcusr.lisspctab.lisspcpkg.lisというファイル名でカレントディレクトリにログを出力。インストール後は、これらのファイルでエラーが発生していないことを確認。

コード表示

/error
コード表示

SYS@pdb1> host pwd
/home/oracle

SYS@pdb1> @@$ORACLE_HOME/rdbms/admin/spcreate.sql

Session altered.

Elapsed: 00:00:00.00

Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: perfstat
perfstat
Elapsed: 00:00:00.00


Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data.  Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace.  This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS              STATSPACK DEFAULT TABLESPACE
------------------------------ --------------------- ----------------------------
STATS                          PERMANENT
SYSAUX                         PERMANENT             *
USERS                          PERMANENT
Elapsed: 00:00:00.02

Pressing  will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: stats

Using tablespace STATS as PERFSTAT default tablespace.
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01


Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas).  Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS              DB DEFAULT TEMP TABLESPACE
------------------------------ --------------------- --------------------------
TEMP                           TEMPORARY             *
Elapsed: 00:00:00.02

Pressing  will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: temp

Using tablespace temp as PERFSTAT temporary tablespace.
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00


... Creating PERFSTAT user
Elapsed: 00:00:00.06
Elapsed: 00:00:00.01


... Installing required packages
Elapsed: 00:00:00.00
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00


... Creating views
Elapsed: 00:00:00.01
Elapsed: 00:00:00.02
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.02
Elapsed: 00:00:00.00


... Granting privileges
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.02
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.02
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.03
Elapsed: 00:00:00.03
Elapsed: 00:00:00.00
Elapsed: 00:00:00.02
Elapsed: 00:00:00.01
Elapsed: 00:00:00.02
Elapsed: 00:00:00.02
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.02
Elapsed: 00:00:00.00
Elapsed: 00:00:00.02
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.02
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.02
Elapsed: 00:00:00.04
Elapsed: 00:00:00.02
Elapsed: 00:00:00.02
Elapsed: 00:00:00.00
Elapsed: 00:00:00.02
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.03
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.02
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00

NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.

SYS@pdb1>
SYS@pdb1> -- Next two scripts run as perfstat user
SYS@pdb1> ALTER SESSION SET CURRENT_SCHEMA = PERFSTAT;

Session altered.

Elapsed: 00:00:00.00
SYS@pdb1>
SYS@pdb1> -- Create statspack tables
SYS@pdb1> @@spctab
SYS@pdb1> Rem
SYS@pdb1> Rem $Header: rdbms/admin/spctab.sql /main/56 2017/05/28 22:46:10 stanaya Exp $
SYS@pdb1> Rem
SYS@pdb1> Rem spctab.sql
SYS@pdb1> Rem
SYS@pdb1> Rem Copyright (c) 1999, 2017, Oracle and/or its affiliates.
SYS@pdb1> Rem All rights reserved.
SYS@pdb1> Rem
SYS@pdb1> Rem    NAME
SYS@pdb1> Rem      spctab.sql
SYS@pdb1> Rem
SYS@pdb1> Rem    DESCRIPTION
SYS@pdb1> Rem      SQL*PLUS command file to create tables to hold
SYS@pdb1> Rem      start and end "snapshot" statistical information
SYS@pdb1> Rem
SYS@pdb1> Rem    NOTES
SYS@pdb1> Rem      Should be run as STATSPACK user, PERFSTAT
SYS@pdb1> Rem
SYS@pdb1> Rem    BEGIN SQL_FILE_METADATA
SYS@pdb1> Rem    SQL_SOURCE_FILE: rdbms/admin/spctab.sql
SYS@pdb1> Rem    SQL_SHIPPED_FILE: rdbms/admin/spctab.sql
SYS@pdb1> Rem    SQL_PHASE: UTILITY
SYS@pdb1> Rem    SQL_STARTUP_MODE: NORMAL
SYS@pdb1> Rem    SQL_IGNORABLE_ERRORS: NONE
SYS@pdb1> Rem    END SQL_FILE_METADATA
SYS@pdb1> Rem
SYS@pdb1> Rem    MODIFIED   (MM/DD/YY)
SYS@pdb1> Rem    pjotawar    09/16/16 - Bug 23481673 Support Integrated Replicat
SYS@pdb1> Rem    zhefan      11/06/14 - Bug #19933671
SYS@pdb1> Rem    pmurthy     02/20/14 - To Fix Bug - 18284201 and 18273117
SYS@pdb1> Rem    kchou       10/30/13 - Bug# 17504669: Add New Column
SYS@pdb1> Rem                             remaster_type to STATS$DYNAMIC_REMASTER_STATS
SYS@pdb1> Rem    shsong      06/29/11 - shsong 06/28/11 - Bug 12702106: display
SYS@pdb1> Rem                           v$IOSTAT_FUNCTION_DETAIL
SYS@pdb1> Rem    traney      04/06/11 - 35209: long identifiers dictionary upgrade
SYS@pdb1> Rem    kchou       01/10/11 - Forward Merge of Bug Fix 9800868 to 12.1 Mainline
SYS@pdb1> Rem    kchou       08/11/10 - Bug#9800868 - Add Missing Idle Events for
SYS@pdb1> Rem                           11.2.0.2for Statspack & Standby Statspack
SYS@pdb1> Rem    kchou       08/11/10 - Bug#9800868 - Add missing idle events to 11.2.0.2
SYS@pdb1> Rem    kchou       01/10/11 - XbranchMerge kchou_bug-9800868 from
SYS@pdb1> Rem                           st_rdbms_11.2.0
SYS@pdb1> Rem    cgervasi    05/13/09 - add idle event: cell worker idle
SYS@pdb1> Rem    cgervasi    04/02/09 - bug8395154: missing idle events
SYS@pdb1> Rem    rhlee       02/22/08 -
> Rem    cdgreen     03/14/07 - 11 F2
SYS@pdb1> Rem    shsong      06/14/07 - Add idle events
SYS@pdb1> Rem    cdgreen     02/28/07 - 5908354
SYS@pdb1> Rem    cdgreen     04/26/06 - 11 F1
SYS@pdb1> Rem    cdgreen     06/26/06 - Increase column length
SYS@pdb1> Rem    cdgreen     05/10/06 - 5215982
SYS@pdb1> Rem    cdgreen     05/24/05 - 4246955
SYS@pdb1> Rem    cdgreen     04/18/05 - 4228432
SYS@pdb1> Rem    cdgreen     03/08/05 - 10gR2 misc
SYS@pdb1> Rem    vbarrier    02/18/05 - 4081984
SYS@pdb1> Rem    cdgreen     10/29/04 - 10gR2_sqlstats
SYS@pdb1> Rem    cdgreen     07/16/04 - 10gR2
SYS@pdb1> Rem    cdialeri    03/25/04 - 3516921
SYS@pdb1> Rem    vbarrier    02/12/04 - 3412853
SYS@pdb1> Rem    cdialeri    12/04/03 - 3290482
SYS@pdb1> Rem    cdialeri    11/05/03 - 3202706
SYS@pdb1> Rem    cdialeri    10/14/03 - 10g - streams - rvenkate
SYS@pdb1> Rem    cdialeri    08/05/03 - 10g F3
SYS@pdb1> Rem    cdialeri    02/27/03 - 10g F2: baseline, purge
SYS@pdb1> Rem    vbarrier    02/25/03 - 10g RAC
SYS@pdb1> Rem    cdialeri    11/15/02 - 10g F1
SYS@pdb1> Rem    cdialeri    09/27/02 - sleep4
SYS@pdb1> Rem    vbarrier    03/20/02 - 2143634
SYS@pdb1> Rem    vbarrier    03/05/02 - Segment Statistics
SYS@pdb1> Rem    cdialeri    02/07/02 - 2218573
SYS@pdb1> Rem    cdialeri    01/30/02 - 2184717
SYS@pdb1> Rem    cdialeri    01/11/02 - 9.2 - features 2
SYS@pdb1> Rem    cdialeri    11/30/01 - 9.2 - features 1
SYS@pdb1> Rem    cdialeri    04/22/01 - Undostat changes
SYS@pdb1> Rem    cdialeri    03/02/01 - 9.0
SYS@pdb1> Rem    cdialeri    09/12/00 - sp_1404195
SYS@pdb1> Rem    cdialeri    04/07/00 - 1261813
SYS@pdb1> Rem    cdialeri    03/20/00 - Support for purge
SYS@pdb1> Rem    cdialeri    02/16/00 - 1191805
SYS@pdb1> Rem    cdialeri    01/26/00 - 1169401
SYS@pdb1> Rem    cdialeri    11/01/99 - Enhance, 1059172
SYS@pdb1> Rem    cmlim       07/17/97 - Added STATS$SQLAREA to store top sql stmts
SYS@pdb1> Rem    gwood       10/16/95 - Version to run as sys without using many views
SYS@pdb1> Rem    cellis.uk   11/15/89 - Created
SYS@pdb1> Rem
SYS@pdb1>
SYS@pdb1> set showmode off echo off;

If this script is automatically called from spcreate (which is
the supported method), all STATSPACK segments will be created in
the PERFSTAT user's default tablespace.

Using stats tablespace to store Statspack objects

... Creating STATS$SNAPSHOT_ID Sequence

Sequence created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00
... Creating STATS$... tables

Table created.

Elapsed: 00:00:00.02

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.39

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.01

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

Commit complete.

Elapsed: 00:00:00.00

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.04

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.02

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.02

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.00

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.02

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.02

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.02

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.02

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.03

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.00

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.02

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.03

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.03

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.02

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.02

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Index created.

Elapsed: 00:00:00.00

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.02

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.02

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.02

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.02

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.00

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.02

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.02

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.03

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.04

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.02

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.02

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.02

Synonym created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.01

View created.

Elapsed: 00:00:00.00

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.01

1 row created.

Elapsed: 00:00:00.01

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.01

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.01

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.01

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.01

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.01

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.01

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.01

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.01

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

Commit complete.

Elapsed: 00:00:00.00

Synonym created.

Elapsed: 00:00:00.00

Synonym created.

Elapsed: 00:00:00.01

NOTE:
SPCTAB complete. Please check spctab.lis for any errors.

SYS@pdb1>
SYS@pdb1> -- Create the statistics Package
SYS@pdb1> @@spcpkg
SYS@pdb1> Rem
SYS@pdb1> Rem $Header: rdbms/admin/spcpkg.sql /main/56 2017/05/28 22:46:10 stanaya Exp $
SYS@pdb1> Rem
SYS@pdb1> Rem spcpkg.sql
SYS@pdb1> Rem
SYS@pdb1> Rem Copyright (c) 1999, 2017, Oracle and/or its affiliates.
SYS@pdb1> Rem All rights reserved.
SYS@pdb1> Rem
SYS@pdb1> Rem    NAME
SYS@pdb1> Rem      spcpkg.sql
SYS@pdb1> Rem
SYS@pdb1> Rem    DESCRIPTION
SYS@pdb1> Rem      SQL*PLUS command file to create statistics package
SYS@pdb1> Rem
SYS@pdb1> Rem    NOTES
SYS@pdb1> Rem      Must be run as the STATSPACK owner, PERFSTAT
SYS@pdb1> Rem
SYS@pdb1> Rem    BEGIN SQL_FILE_METADATA
SYS@pdb1> Rem    SQL_SOURCE_FILE: rdbms/admin/spcpkg.sql
SYS@pdb1> Rem    SQL_SHIPPED_FILE: rdbms/admin/spcpkg.sql
SYS@pdb1> Rem    SQL_PHASE: UTILITY
SYS@pdb1> Rem    SQL_STARTUP_MODE: NORMAL
SYS@pdb1> Rem    SQL_IGNORABLE_ERRORS: NONE
SYS@pdb1> Rem    END SQL_FILE_METADATA
SYS@pdb1> Rem
SYS@pdb1> Rem    MODIFIED   (MM/DD/YY)
SYS@pdb1> Rem    kchou       11/04/13 - Bug# 17504669:Add New Column remaster_type to
SYS@pdb1> Rem                             STATS$DYNAMIC_REMASTER_STATS
SYS@pdb1> Rem    shsong      06/29/11 - shsong 06/28/11 - Bug 12702106: display
SYS@pdb1> Rem                           v$IOSTAT_FUNCTION_DETAIL
SYS@pdb1> Rem    arogers     01/23/08 - 6523482 - change VM_IN/OUT_BYTES id numbers
SYS@pdb1> Rem    cdgreen     03/14/07 - 11 F2
SYS@pdb1> Rem    shsong      06/14/07 - Fix BUFFER_GETS
SYS@pdb1> Rem    cdgreen     04/05/07 - 5691086
SYS@pdb1> Rem    cdgreen     03/02/07 - use _FG for v$system_event
SYS@pdb1> Rem    cdgreen     03/02/07 - 5913378
SYS@pdb1> Rem    cdgreen     05/16/06 - 11 F1
SYS@pdb1> Rem    cdgreen     05/10/06 - 5215982
SYS@pdb1> Rem    cdgreen     05/24/05 - 4246955
SYS@pdb1> Rem    cdgreen     04/18/05 - 4228432
SYS@pdb1> Rem    cdgreen     02/28/05 - 10gR2 misc
SYS@pdb1> Rem    vbarrier    02/18/05 - 4081984
SYS@pdb1> Rem    cdgreen     01/25/05 - 4143812
SYS@pdb1> Rem    cdgreen     10/29/04 - 10gR2_sqlstats
SYS@pdb1> Rem    cdgreen     10/25/04 - 3970898
SYS@pdb1> Rem    cdgreen     07/16/04 - 10g R2
SYS@pdb1> Rem    vbarrier    03/18/04 - 3517841
SYS@pdb1> Rem    vbarrier    02/12/04 - 3412853
SYS@pdb1> Rem    cdialeri    12/04/03 - 3290482
SYS@pdb1> Rem    cdialeri    11/05/03 - 3202706
SYS@pdb1> Rem    cdialeri    10/14/03 - 10g - streams - rvenkate
SYS@pdb1> Rem    cdialeri    08/05/03 - 10g F3
SYS@pdb1> Rem    cdialeri    07/31/03 - 2804307
SYS@pdb1> Rem    vbarrier    02/25/03 - 10g RAC
SYS@pdb1> Rem    cdialeri    01/28/03 - 10g F2: baseline, purge
SYS@pdb1> Rem    cdialeri    11/15/02 - 10g F1
SYS@pdb1> Rem    cdialeri    10/29/02 - 2648471
SYS@pdb1> Rem    cdialeri    09/11/02 - 1995145
SYS@pdb1> Rem    vbarrier    04/18/02 - 2271895
SYS@pdb1> Rem    vbarrier    03/20/02 - 2184504
SYS@pdb1> Rem    spommere    03/19/02 - 2274095
SYS@pdb1> Rem    vbarrier    03/05/02 - Segment Statistics
SYS@pdb1> Rem    spommere    02/14/02 - cleanup RAC stats that are no longer needed
SYS@pdb1> Rem    spommere    02/08/02 - 2212357
SYS@pdb1> Rem    cdialeri    02/07/02 - 2218573
SYS@pdb1> Rem    cdialeri    01/30/02 - 2184717
SYS@pdb1> Rem    cdialeri    01/09/02 - 9.2 - features 2
SYS@pdb1> Rem    cdialeri    11/30/01 - 9.2 - features 1
SYS@pdb1> Rem    hbergh      08/23/01 - 1940915: use substrb on sql_text
SYS@pdb1> Rem    cdialeri    04/26/01 - 9.0
SYS@pdb1> Rem    cdialeri    09/12/00 - sp_1404195
SYS@pdb1> Rem    cdialeri    04/07/00 - 1261813
SYS@pdb1> Rem    cdialeri    03/28/00 - sp_purge
SYS@pdb1> Rem    cdialeri    02/16/00 - 1191805
SYS@pdb1> Rem    cdialeri    11/01/99 - Enhance, 1059172
SYS@pdb1> Rem    cgervasi    06/16/98 - Remove references to wrqs
SYS@pdb1> Rem    cmlim       07/30/97 - Modified system events
SYS@pdb1> Rem    gwood.uk    02/30/94 - Modified
SYS@pdb1> Rem    densor.uk   03/31/93 - Modified
SYS@pdb1> Rem    cellis.uk   11/15/89 - Created
SYS@pdb1> Rem
SYS@pdb1>
SYS@pdb1> set echo off;
Creating Package STATSPACK...

Package created.

Elapsed: 00:00:00.04
No errors.
Creating Package Body STATSPACK...

Package body created.

Elapsed: 00:00:00.32
No errors.

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

SYS@pdb1>
SYS@pdb1> -- Bug#25233027: xxx Set this parameter to FALSE for creating common objects in consolidated database
SYS@pdb1> alter session set "_oracle_script" = FALSE;

Session altered.

Elapsed: 00:00:00.00
SYS@pdb1>

コード表示

SYS@pdb1> host ls
epel-release-7-11.noarch.rpm  rlwrap-extensions  setPassword.sh  spcpkg.lis  spctab.lis  spcusr.lis

SYS@pdb1> host vim spcpkg.lis

コード表示

Creating Package STATSPACK...

Package created.

Elapsed: 00:00:00.04
No errors.
Creating Package Body STATSPACK...

Package body created.

Elapsed: 00:00:00.32
No errors.

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
コード表示

SYS@pdb1> host vim spctab.lis
コード表示

If this script is automatically called from spcreate (which is
the supported method), all STATSPACK segments will be created in
the PERFSTAT user's default tablespace.

Using stats tablespace to store Statspack objects

... Creating STATS$SNAPSHOT_ID Sequence

Sequence created.

Elapsed: 00:00:00.01

Synonym created.

Elapsed: 00:00:00.00
... Creating STATS$... tables

Table created.

Elapsed: 00:00:00.02

Synonym created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.39

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.01

1 row created.

Elapsed: 00:00:00.00

                                                                                                                                                                 1,0-1         Top

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

Commit complete.

Elapsed: 00:00:00.00

Synonym created.

Elapsed: 00:00:00.00

Synonym created.

Elapsed: 00:00:00.01

NOTE:
SPCTAB complete. Please check spctab.lis for any errors.
コード表示

SYS@pdb1> host vim spcusr.lis
コード表示

Elapsed: 00:00:00.00


Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data.  Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace.  This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS              STATSPACK DEFAULT TABLESPACE
------------------------------ --------------------- ----------------------------
STATS                          PERMANENT
SYSAUX                         PERMANENT             *
USERS                          PERMANENT
Elapsed: 00:00:00.02

Pressing  will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: stats

Using tablespace STATS as PERFSTAT default tablespace.
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01


Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas).  Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS              DB DEFAULT TEMP TABLESPACE
------------------------------ --------------------- --------------------------
TEMP                           TEMPORARY             *
Elapsed: 00:00:00.02

Pressing  will result in the database's default Temporary
tablespace (identified by *) being used.
"spcusr.lis" 182L, 4570C                                                                                                                                         1,1           Top
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.02
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.02
Elapsed: 00:00:00.04
Elapsed: 00:00:00.02
Elapsed: 00:00:00.02
Elapsed: 00:00:00.00
Elapsed: 00:00:00.02
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.03
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.02
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00

NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.

スナップショットの取得

スナップショットレベルのデフォは57がスタンダードらしいので、今回はそれで。

取得可能なパフォーマンス統計情報(下位レベル込みのレベル7)
・待機統計
・システム・イベント
・システム統計
・ロールバック・セグメント・データ
・行キャッシュ
・SGA
・バックグラウンド・イベント
・セッション・イベント
・ロック統計
・バッファ・プール統計
・親ラッチ統計
・リソース使用率の高いSQLに関するパフオーマンス・データ
・リソース使用量の多い取得済みSQLのそれぞれのSQL実行計画
・SQL計画使用状況データ
・使用頻度の高いセグメントに関するパフォーマンス・データ
・RAC固有のセグメント・レベルの統計

スナップショットレベルの変更

perfstatユーザーで実行する

コード表示

select username,created from dba_users where username = 'PERFSTAT';
コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@6898242bcdd5 ~]$ sqlplus sys/ORACLE_PWD@pdb1 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Jan 26 13:51:12 2019
Version 18.3.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SYS@pdb1> col username for a12
SYS@pdb1> select username,created from dba_users where username = 'PERFSTAT';

USERNAME     CREATED
------------ ---------
PERFSTAT     26-JAN-19

1 row selected.

Elapsed: 00:00:00.01
コード表示

execute statspack.modify_statspack_parameter(i_snap_level=> 7);
コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@6898242bcdd5 ~]$ sqlplus perfstat/perfstat@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Jan 26 14:06:09 2019
Version 18.3.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

PERFSTAT@pdb1> execute statspack.modify_statspack_parameter(i_snap_level=> 7);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
PERFSTAT@pdb1>

スナップショットの実行

perfstatユーザーで実行する

コード表示

execute statspack.snap;
コード表示

PERFSTAT@pdb1> execute statspack.snap;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.80
PERFSTAT@pdb1>

スナップショットの確認

perfstatユーザーで実行する

コード表示

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
select snap_id, snap_time from stats$snapshot order by snap_id;
コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@6898242bcdd5 ~]$ sqlplus perfstat/perfstat@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Jan 26 14:14:14 2019
Version 18.3.0.0.0

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

Last Successful login time: Sat Jan 26 2019 14:06:09 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

PERFSTAT@pdb1> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

Session altered.

Elapsed: 00:00:00.00
PERFSTAT@pdb1> select snap_id, snap_time from stats$snapshot order by snap_id;

      SNAP_ID SNAP_TIME
------------- -------------------
            1 2019-01-26 14:11:58

1 row selected.

Elapsed: 00:00:00.01
PERFSTAT@pdb1>

なんかてきとうなSQL実行してみる

aineユーザーで実行してみる。プラガブルデータベースはperfstatユーザーと同じpdb1で。

コード表示

SELECT
	s1.warehouse_code
	,s1.item
	,s1.item_kbn
	,s2.loc
	,s2.ZONE
	,s2.BLOCK
	,s2.area
	,s2.LINE
	,s3.zone_kbn
FROM
	aine.item_mst@testlink s1
	,aine.loc_mst@testlink s2
	,aine.zone_mst@testlink s3
WHERE
	s1.warehouse_code = s2.warehouse_code
AND s1.main_loc = s2.loc
AND s2.warehouse_code = s2.warehouse_code
AND s2.ZONE = s3.ZONE
;
コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@6898242bcdd5 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Jan 26 14:24:12 2019
Version 18.3.0.0.0

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

Last Successful login time: Mon Jan 21 2019 21:59:40 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> SELECT
  2  s1.warehouse_code
  3  ,s1.item
  4  ,s1.item_kbn
  5  ,s2.loc
  6  ,s2.ZONE
  7  ,s2.BLOCK
  8  ,s2.area
  9  ,s2.LINE
 10  ,s3.zone_kbn
 11  FROM
 12  aine.item_mst@testlink s1
 13  ,aine.loc_mst@testlink s2
 14  ,aine.zone_mst@testlink s3
 15  WHERE
 16  s1.warehouse_code = s2.warehouse_code
 17  AND s1.main_loc = s2.loc
 18  AND s2.warehouse_code = s2.warehouse_code
 19  AND s2.ZONE = s3.ZONE
 20  ;
39 rows selected.

Elapsed: 00:00:00.08

スナップショットの実行

perfstatユーザーで実行する

コード表示

execute statspack.snap;
コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@6898242bcdd5 ~]$ sqlplus perfstat/perfstat@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Jan 26 14:27:01 2019
Version 18.3.0.0.0

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

Last Successful login time: Sat Jan 26 2019 14:14:14 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

PERFSTAT@pdb1> execute statspack.snap;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.44

スナップショットの確認

perfstatユーザーで実行する

コード表示

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
select snap_id, snap_time from stats$snapshot order by snap_id;
コード表示

PERFSTAT@pdb1> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

Session altered.

Elapsed: 00:00:00.01
PERFSTAT@pdb1> select snap_id, snap_time from stats$snapshot order by snap_id;

      SNAP_ID SNAP_TIME
------------- -------------------
            1 2019-01-26 14:11:58
            2 2019-01-26 14:27:11

2 rows selected.

Elapsed: 00:00:00.00

レポートの作成

spreport.sqlスクリプトの格納場所

spreport.sqlの格納場所を確認。

コード表示

[oracle@6898242bcdd5 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@6898242bcdd5 admin]$ ll | grep report
-rw-r--r--. 1 oracle dba   16782 Feb  8  2018 prvtwrr_report.plb
-rw-r--r--. 1 oracle dba     730 May 29  2017 sbreport.sql
-rw-r--r--. 1 oracle dba    1577 May 29  2017 spreport.sql
[oracle@6898242bcdd5 admin]$ vim spreport.sql

spreport.sqlスクリプトの実行

spreport.sqlの格納場所を確認してから実行。

コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@6898242bcdd5 ~]$ sqlplus perfstat/perfstat@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Jan 26 14:38:16 2019
Version 18.3.0.0.0

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

Last Successful login time: Sat Jan 26 2019 14:27:01 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

PERFSTAT@pdb1> host pwd
/home/oracle

PERFSTAT@pdb1> host ls
epel-release-7-11.noarch.rpm  rlwrap-extensions  setPassword.sh  spcpkg.lis  spctab.lis  spcusr.lis

PERFSTAT@pdb1> @?/rdbms/admin/spreport.sql
PERFSTAT@pdb1> host ls
epel-release-7-11.noarch.rpm  rlwrap-extensions  setPassword.sh  spcpkg.lis  spctab.lis  spcusr.lis  test_statspack.lst
PERFSTAT@pdb1> host vim test_statspack.lst

コード表示



PERFSTAT@pdb1> host ls
epel-release-7-11.noarch.rpm  rlwrap-extensions  setPassword.sh  spcpkg.lis  spctab.lis  spcusr.lis  test_statspack.lst

PERFSTAT@pdb1> vim test_statspack.lst
SP2-0734: unknown command beginning "vim test_s..." - rest of line ignored.
PERFSTAT@pdb1> host vim test_statspack.lst

PERFSTAT@pdb1> host vim test_statspack.lst

コード表示

STATSPACK report for

Database    DB Id    Instance     Inst Num  Startup Time   Release     RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
          1525818796 ORCL                1 20-Jan-19 08:55 18.0.0.0.0  NO

Host Name             Platform                CPUs Cores Sockets   Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
     6898242bcdd5     Linux x86 64-bit          12     6       1         31.1

Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- ------------------
Begin Snap:          1 26-Jan-19 14:11:58        7       7.3
  End Snap:          2 26-Jan-19 14:27:11        7       7.0
   Elapsed:      15.22 (mins) Av Act Sess:       0.0
   DB time:       0.03 (mins)      DB CPU:       0.03 (mins)

Cache Sizes            Begin        End
~~~~~~~~~~~       ---------- ----------
    Buffer Cache:     7,456M              Std Block Size:         8K
     Shared Pool:     1,376M                  Log Buffer:    19,888K

Load Profile              Per Second    Per Transaction    Per Exec    Per Call
~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------
      DB time(s):                0.0                2.0        0.00        0.02
       DB CPU(s):                0.0                1.9        0.00        0.02
       Redo size:            7,964.8        7,271,836.0
   Logical reads:               36.8           33,553.0
   Block changes:               28.3           25,789.0
  Physical reads:                0.0                0.0
 Physical writes:                0.0                0.0
      User calls:                0.1               95.0
          Parses:                0.8              754.0
     Hard parses:                0.2              163.0
W/A MB processed:                0.0               22.7
          Logons:                0.0                6.0
        Executes:                2.9            2,628.0
       Rollbacks:                0.0                0.0
    Transactions:                0.0

Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.99       Redo NoWait %:  100.00
            Buffer  Hit   %:  100.00  Optimal W/A Exec %:  100.00
            Library Hit   %:   96.81        Soft Parse %:   78.38
就test_statspack.lst" 2650L, 162575C                                                                                                                            1,0-1         Top

STATSPACK report for

Database    DB Id    Instance     Inst Num  Startup Time   Release     RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
          1525818796 ORCL                1 20-Jan-19 08:55 18.0.0.0.0  NO

Host Name             Platform                CPUs Cores Sockets   Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
     6898242bcdd5     Linux x86 64-bit          12     6       1         31.1

Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- ------------------
Begin Snap:          1 26-Jan-19 14:11:58        7       7.3
  End Snap:          2 26-Jan-19 14:27:11        7       7.0
   Elapsed:      15.22 (mins) Av Act Sess:       0.0
   DB time:       0.03 (mins)      DB CPU:       0.03 (mins)

Cache Sizes            Begin        End
~~~~~~~~~~~       ---------- ----------
    Buffer Cache:     7,456M              Std Block Size:         8K
     Shared Pool:     1,376M                  Log Buffer:    19,888K

Load Profile              Per Second    Per Transaction    Per Exec    Per Call
~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------
      DB time(s):                0.0                2.0        0.00        0.02
       DB CPU(s):                0.0                1.9        0.00        0.02
       Redo size:            7,964.8        7,271,836.0
   Logical reads:               36.8           33,553.0
   Block changes:               28.3           25,789.0
  Physical reads:                0.0                0.0
 Physical writes:                0.0                0.0
      User calls:                0.1               95.0
          Parses:                0.8              754.0
     Hard parses:                0.2              163.0
W/A MB processed:                0.0               22.7
          Logons:                0.0                6.0
        Executes:                2.9            2,628.0
       Rollbacks:                0.0                0.0
    Transactions:                0.0

Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.99       Redo NoWait %:  100.00
            Buffer  Hit   %:  100.00  Optimal W/A Exec %:  100.00
            Library Hit   %:   96.81        Soft Parse %:   78.38
"test_statspack.lst" 2650L, 162575C                                                                                                                             1,0-1         Top
shared free memory                             166.6          176.3     5.78
shared keomg: entry list                         8.7            8.7     0.00
       buffer_cache                          7,456.0        7,456.0     0.00
       fixed_sga                                11.9           11.9     0.00
       log_buffer                               20.1           20.1     0.00
       shared_io_pool                          480.0          480.0     0.00
          -------------------------------------------------------------
^LSQL Memory Statistics  DB/Inst: ORCL/ORCL  Snaps: 1-2

                                   Begin            End         % Diff
                          -------------- -------------- --------------
   Avg Cursor Size (KB):           54.17          47.69         -13.58
 Cursor to Parent ratio:            2.44           2.42          -1.05
          Total Cursors:           2,642          2,663            .79
          Total Parents:           1,081          1,101           1.82
          -------------------------------------------------------------
^Linit.ora Parameters  DB/Inst: ORCL/ORCL  Snaps: 1-2

                                                                  End value
Parameter Name                Begin value                       (if different)
----------------------------- --------------------------------- --------------
audit_file_dest               /opt/oracle/admin/ORCL/adump
audit_sys_operations          FALSE
audit_trail                   NONE
compatible                    18.0.0
control_files                 /opt/oracle/oradata/ORCL/control0
                              1.ctl, /opt/oracle/oradata/ORCL/c
                              ontrol02.ctl
db_block_size                 8192
db_name                       ORCL
diagnostic_dest               /opt/oracle
dispatchers                   (PROTOCOL=TCP) (SERVICE=ORCLXDB)
enable_pluggable_database     TRUE
local_listener
nls_language                  AMERICAN
nls_territory                 AMERICA
open_cursors                  300
pga_aggregate_target          3342860288
processes                     960
remote_login_passwordfile     EXCLUSIVE
sga_target                    0
undo_tablespace               UNDOTBS1
          -------------------------------------------------------------

End of Report ( test_statspack.lst )

過去のSQLの実行計画を確認

sprepsql.sqlスクリプトの格納場所

sprepsql.sqlの格納場所を確認。

コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@6898242bcdd5 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@6898242bcdd5 admin]$ ll | grep prep
-rw-r--r--. 1 oracle dba    5497 May 29  2017 sprepcon.sql
-rw-r--r--. 1 oracle dba  286581 May 29  2017 sprepins.sql
-rw-r--r--. 1 oracle dba    1577 May 29  2017 spreport.sql
-rw-r--r--. 1 oracle dba    1558 May 29  2017 sprepsql.sql
[oracle@6898242bcdd5 admin]$ vim sprepsql.sql

hash_valueの確認

hash_valueの値を確認。

コード表示

col service for a12;
col module for a12;
SELECT
    TO_CHAR(to_timestamp(last_load_time DEFAULT NULL ON CONVERSION ERROR, 'YYYY/MM/DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS') AS last_load_time
    , sql_id
    , plan_hash_value
    , hash_value
    , service
    , module
FROM
    v$sql
WHERE
    regexp_replace(sql_fulltext, '[[:space:]]', '', 1, 0, 'i') LIKE '%'
                                                                       || '&1'
                                                                       || '%'
ORDER BY
    1 DESC;
コード表示

AINE@pdb1> col service for a12;
AINE@pdb1> col module for a12;
AINE@pdb1> SELECT
  2      TO_CHAR(to_timestamp(last_load_time DEFAULT NULL ON CONVERSION ERROR, 'YYYY/MM/DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS') AS last_load_time
  3      , sql_id
  4      , plan_hash_value
  5      , hash_value
  6      , service
  7      , module
  8  FROM
  9      v$sql
 10  WHERE
 11      regexp_replace(sql_fulltext, '[[:space:]]', '', 1, 0, 'i') LIKE '%'
 12                                                                         || '&1'
 13                                                                         || '%'
 14  ORDER BY
 15      1 DESC;
Enter value for 1: warehouse_code
old  12:                                                                        || '&1'
new  12:                                                                        || 'warehouse_code'

LAST_LOAD_TIME      SQL_ID        PLAN_HASH_VALUE    HASH_VALUE SERVICE      MODULE
------------------- ------------- --------------- ------------- ------------ ------------
2019/01/26 15:15:26 614rt0r1u8rnt      2836784050    3282329241 pdb1         SQL*Plus
2019/01/26 14:24:31 2t60hp73dv6y1               0    3336412097 pdb1         SQL*Plus

2 rows selected.

Elapsed: 00:00:00.43
AINE@pdb1>

sprepsql.sqlの実行

sprepsql.sqlの格納場所を確認してから実行。ちなみに拡張子sqlは省略してもいける。この例だといけると思ったけど、実行計画確認できなかった。。

コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@6898242bcdd5 ~]$ sqlplus perfstat/perfstat@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Jan 26 15:22:03 2019
Version 18.3.0.0.0

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

Last Successful login time: Sat Jan 26 2019 15:18:14 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

PERFSTAT@pdb1> @?/rdbms/admin/sprepsql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1525818796 ORCL                1 ORCL

1 row selected.

Elapsed: 00:00:00.01


Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
 1525818796        1 ORCL         ORCL         6898242bcdd5

Using 1525818796 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing  without
specifying a number lists all completed snapshots.



Listing all Completed Snapshots

                               Snap                    Snap
Instance     DB Name             Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
ORCL         ORCL                 1 26 Jan 2019 14:11     7
                                  2 26 Jan 2019 14:27     7



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End   Snapshot Id specified: 2



Specify the old (i.e. pre-10g) Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for hash_value: 3336412097
Hash Value specified is: 3336412097


declare
*
ERROR at line 1:
ORA-20200: Hash value 3336412097 does not exist in end snapshot
ORA-06512: at line 66


Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

sql oracle sqlplus バインド変数

置換変数の定義

パターン1


SELECT sysdate FROM dual WHERE rownum = '&bind1' or rownum = '&bind2';

パターン2


SELECT sysdate FROM dual WHERE rownum = '&1' or rownum = '&2';

置換変数の設定

パターン1


[oracle@d102981b4117 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Jan 12 09:31:23 2019
Version 18.3.0.0.0

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

Last Successful login time: Sat Jan 12 2019 09:28:00 +00:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> define
DEFINE _DATE           = "12-JAN-19" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "pdb1" (CHAR)
DEFINE _USER           = "AINE" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1803000000" (CHAR)
DEFINE _EDITOR         = "nano" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1803000000" (CHAR)
AINE@pdb1> define
DEFINE _DATE           = "12-JAN-19" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "pdb1" (CHAR)
DEFINE _USER           = "AINE" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1803000000" (CHAR)
DEFINE _EDITOR         = "nano" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1803000000" (CHAR)
AINE@pdb1> define bind1=1
AINE@pdb1> define bind2=2
AINE@pdb1> define
DEFINE _DATE           = "12-JAN-19" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "pdb1" (CHAR)
DEFINE _USER           = "AINE" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1803000000" (CHAR)
DEFINE _EDITOR         = "nano" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1803000000" (CHAR)
DEFINE BIND1           = "1" (CHAR)
DEFINE BIND2           = "2" (CHAR)

AINE@pdb1> SELECT sysdate FROM dual WHERE rownum = '&bind1' or rownum = '&bind2';
old   1: SELECT sysdate FROM dual WHERE rownum = '&bind1' or rownum = '&bind2'
new   1: SELECT sysdate FROM dual WHERE rownum = '1' or rownum = '2'

1 row selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 208277575

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  COUNT           |      |       |            |          |
|*  2 |   FILTER         |      |       |            |          |
|   3 |    FAST DUAL     |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

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

   2 - filter(ROWNUM=1 OR ROWNUM=2)


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

置換変数の設定

パターン2


AINE@pdb1> host vim test.sql

AINE@pdb1> host ls
epel-release-7-11.noarch.rpm  rlwrap-extensions  setPassword.sh  test.sql

AINE@pdb1> @test.sql 1 2
old   1: SELECT sysdate FROM dual WHERE rownum = '&1' or rownum = '&2'
new   1: SELECT sysdate FROM dual WHERE rownum = '1' or rownum = '2'

1 row selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 208277575

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  COUNT           |      |       |            |          |
|*  2 |   FILTER         |      |       |            |          |
|   3 |    FAST DUAL     |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

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

   2 - filter(ROWNUM=1 OR ROWNUM=2)


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