sql oracle statspack tkprof hard parse回数の確認

初期化パラメータの確認

job_queue_processesの確認

ジョブ実行用に作成できるプロセスの最大数を指定。ジョブキュー内のジョブを実行するために、少なくとも1以上を指定する必要。

コード表示

col name for a24;
col value for a12;
select name,value from V$PARAMETER where NAME like '%job_queue_processes%';

dockerコンテナ内で出力したspool結果をdocker起動時に指定したdockerホストから見えるディレクトリに移動させ、winscpで出力したスプールファイルを刈り取り。というか/mntに移動してからsqlplus起動してスプールすればいい。。

コード表示

set markup html on
spool test.html
select * from STATS$PARAMETER where NAME like '%timed_statistics%';
spool off
set markup html off
コード表示

[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 17:18:52 2019
Version 18.3.0.0.0

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

Last Successful login time: Sat Jan 26 2019 17:16:12 +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 -lt
total 212
-rw-r--r--. 1 oracle oinstall    913 Jan 26 17:16 test.html
-rw-r--r--. 1 oracle oinstall 162575 Jan 26 14:39 test_statspack.lst
-rw-r--r--. 1 oracle oinstall    237 Jan 26 09:41 spcpkg.lis
-rw-r--r--. 1 oracle oinstall  12864 Jan 26 09:41 spctab.lis
-rw-r--r--. 1 oracle oinstall   4570 Jan 26 09:41 spcusr.lis
drwxr-xr-x. 2 oracle oinstall   4096 Jan 20 10:58 rlwrap-extensions
-rw-r--r--. 1 root   root      15080 Jan 20 10:52 epel-release-7-11.noarch.rpm
lrwxrwxrwx. 1 root   root         26 Jan 19 23:19 setPassword.sh -> /opt/oracle/setPassword.sh

PERFSTAT@pdb1> set markup html on
PERFSTAT@pdb1> spool test.html
PERFSTAT@pdb1> select * from STATS$PARAMETER where NAME like '%job_queue_processes%';
PERFSTAT@pdb1> spool off

PERFSTAT@pdb1> set markup html off

PERFSTAT@pdb1> host ls -lt
total 212
-rw-r--r--. 1 oracle oinstall    913 Jan 26 17:20 test.html
-rw-r--r--. 1 oracle oinstall 162575 Jan 26 14:39 test_statspack.lst
-rw-r--r--. 1 oracle oinstall    237 Jan 26 09:41 spcpkg.lis
-rw-r--r--. 1 oracle oinstall  12864 Jan 26 09:41 spctab.lis
-rw-r--r--. 1 oracle oinstall   4570 Jan 26 09:41 spcusr.lis
drwxr-xr-x. 2 oracle oinstall   4096 Jan 20 10:58 rlwrap-extensions
-rw-r--r--. 1 root   root      15080 Jan 20 10:52 epel-release-7-11.noarch.rpm
lrwxrwxrwx. 1 root   root         26 Jan 19 23:19 setPassword.sh -> /opt/oracle/setPassword.sh
PERFSTAT@pdb1> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
[oracle@6898242bcdd5 ~]$ ll
total 212
-rw-r--r--. 1 root   root      15080 Jan 20 10:52 epel-release-7-11.noarch.rpm
drwxr-xr-x. 2 oracle oinstall   4096 Jan 20 10:58 rlwrap-extensions
lrwxrwxrwx. 1 root   root         26 Jan 19 23:19 setPassword.sh -> /opt/oracle/setPassword.sh
-rw-r--r--. 1 oracle oinstall    237 Jan 26 09:41 spcpkg.lis
-rw-r--r--. 1 oracle oinstall  12864 Jan 26 09:41 spctab.lis
-rw-r--r--. 1 oracle oinstall   4570 Jan 26 09:41 spcusr.lis
-rw-r--r--. 1 oracle oinstall    913 Jan 26 17:20 test.html
-rw-r--r--. 1 oracle oinstall 162575 Jan 26 14:39 test_statspack.lst
[oracle@6898242bcdd5 ~]$ mv test.html /mnt
[oracle@6898242bcdd5 ~]$ ll
total 208
-rw-r--r--. 1 root   root      15080 Jan 20 10:52 epel-release-7-11.noarch.rpm
drwxr-xr-x. 2 oracle oinstall   4096 Jan 20 10:58 rlwrap-extensions
lrwxrwxrwx. 1 root   root         26 Jan 19 23:19 setPassword.sh -> /opt/oracle/setPassword.sh
-rw-r--r--. 1 oracle oinstall    237 Jan 26 09:41 spcpkg.lis
-rw-r--r--. 1 oracle oinstall  12864 Jan 26 09:41 spctab.lis
-rw-r--r--. 1 oracle oinstall   4570 Jan 26 09:41 spcusr.lis
-rw-r--r--. 1 oracle oinstall 162575 Jan 26 14:39 test_statspack.lst
[oracle@6898242bcdd5 ~]$ cd /mnt
[oracle@6898242bcdd5 mnt]$ ll
total 4
drwxrwxrwx. 5   1000     1001  46 Jan  2 15:05 11gr2
drwxrwxrwx. 5   1000     1001  46 Jan  2 15:05 12cr2
drwxrwxrwx. 5   1000     1001  46 Jan 12 22:21 18cr3
-rw-r--r--. 1 oracle oinstall 913 Jan 26 17:20 test.html
SNAP_ID DBID INSTANCE_NUMBER NAME VALUE ISDEFAULT ISMODIFIED
2 1525818796 1 job_queue_processes 4000 TRUE FALSE
1 1525818796 1 job_queue_processes 4000 TRUE FALSE

timed_statisticsの確認

時間に関する統計情報を収集するか否かを決定するパラメータ。デフォは収集する。

コード表示

select * from STATS$PARAMETER where NAME like '%timed_statistics%';
SNAP_ID DBID INSTANCE_NUMBER NAME VALUE ISDEFAULT ISMODIFIED
2 1525818796 1 timed_statistics TRUE TRUE FALSE
1 1525818796 1 timed_statistics TRUE TRUE FALSE

statistics_levelの確認

収集される統計情報量を調節できる。

コード表示

select * from STATS$PARAMETER where NAME like '%statistics_level%';
SNAP_ID DBID INSTANCE_NUMBER NAME VALUE ISDEFAULT ISMODIFIED
2 1525818796 1 statistics_level TYPICAL TRUE FALSE
1 1525818796 1 statistics_level TYPICAL TRUE FALSE

スナップショットの閾値の確認

スナップショットを取得すると、ライブラリ・キャッシュにあるSQL情報はSTATS$SUMMARYテーブルに格納されるが、すべて格納すると、膨大になるため、閾値を越えたSQLだけを格納するように組まれているぽい。そこで閾値を確認してみる。なお$ORACLE_HOME/rdbms/admin/spdoc.txtが原文。

閾値 概要 原文(引用)
i_executions_th SQLの実行回数。デフォルト値は100 SQL Threshold: number of times the statement was executed
i_disk_reads_th SQLによるディスク・アクセス数。デフォルト値は1000 SQL Threshold: number of disk reads
the statement made
i_parse_calls_th SQLが実行する解析コール数。デフォルト値は1000 SQL Threshold: number of parse
calls the statement made
i_buffer_gets_th SQLがアクセスされたバッファのフロック取得数。デフォルト値は10000 SQL Threshold: number of buffer
gets the statement made
i_sharable_mem_th SQLの共有可能メモリ量(単位:byte)。デフォルトは1048576バイト SQL Threshold: amount of sharable
memory
i_version_count_th SQLの種類の数。デフォルト値は20 SQL Threshold: number of versions
of a SQL statement
i_seg_log_reads_th セグメントに対する論理読み込み数。デフォルト値は10000 Segment statistic Threshold: number
of logical reads on a segment.
i_seg_phy_reads_th セグメントに対する物理読み込み数。デフォルト値は1000 Segment statistic Threshold: number
of physical reads on a segment.
i_seg_buff_busy_th セグメントに対するバッファビジー待機数。デフォルト値は100 Segment statistic Threshold: number
of buffer busy waits for a segment.
i_seg_rowlock_w_th セグメントに対する行ロック待機数。デフォルト値は100 Segment statistic Threshold: number
of row lock waits for a segment.
i_seg_itl_waits_th セグメントに対するITL待機数。デフォルト値は100 Segment statistic Threshold: number
of ITL waits for a segment.
i_seg_cr_bks_sd_th セグメントに対する、指定インスタンスのCRブロック転送数。デフォル
ト値は1000(RAC環境のみ)
Segment statistic Threshold: number
of Consistent Reads blocks served by
the instance for the segment*.
i_seg_cu_bks_sd_th セグメントに対する、指定インスタンスのカレントブロック転送数。デフ
ォルト値は1000(RAC環境のみ)
Segment statistic Threshold: number
of CUrrent blocks served by the
instance for the segment*.

今設定されてあるパラメータ値の確認はSTATS$STATSPACK_PARAMETERより確認できる。

コード表示

select * from STATS$STATSPACK_PARAMETER;
Name Null? Type
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
SESSION_ID NOT NULL NUMBER
SNAP_LEVEL NOT NULL NUMBER
NUM_SQL NOT NULL NUMBER
EXECUTIONS_TH NOT NULL NUMBER
PARSE_CALLS_TH NOT NULL NUMBER
DISK_READS_TH NOT NULL NUMBER
BUFFER_GETS_TH NOT NULL NUMBER
SHARABLE_MEM_TH NOT NULL NUMBER
VERSION_COUNT_TH NOT NULL NUMBER
PIN_STATSPACK NOT NULL VARCHAR2(10)
ALL_INIT NOT NULL VARCHAR2(5)
LAST_MODIFIED DATE
UCOMMENT VARCHAR2(160)
JOB NUMBER
SEG_PHY_READS_TH NOT NULL NUMBER
SEG_LOG_READS_TH NOT NULL NUMBER
SEG_BUFF_BUSY_TH NOT NULL NUMBER
SEG_ROWLOCK_W_TH NOT NULL NUMBER
SEG_ITL_WAITS_TH NOT NULL NUMBER
SEG_CR_BKS_RC_TH NOT NULL NUMBER
SEG_CU_BKS_RC_TH NOT NULL NUMBER
OLD_SQL_CAPTURE_MTH NOT NULL VARCHAR2(10)

hard parseに時間のかかるsqlを実行してみる。

sqlは以下の参考文献より拝借。

Hard Parseに時間が掛かるお手軽なSQLを作ってみる。(Oracle Database)

コード表示

WITH a AS (
	SELECT /*+ inline */
		1
	FROM
		dual
		, dual
		, dual
		, dual
		, dual
		, dual
		, dual
		, dual
		, dual
		, dual
), b AS (
	SELECT /*+ inline */
		1
	FROM
		a
		, a
		, a
		, a
		, a
		, a
		, a
		, a
		, a
		, a
) SELECT
	1
  FROM
	b
	, b
	, b;

v$sysstatでsql実行前の状況を確認してみる。

9.96 V$SYSSTAT

システム全体でのサマリを参照することができる。

コード表示

col name for a24
col value for 999999999999
select * from v$sysstat where NAME like 'parse%';
コード表示

AINE@pdb1> col name for a24
AINE@pdb1> col value for 999999999999
AINE@pdb1> select * from v$sysstat where NAME like 'parse%';

   STATISTIC# NAME                             CLASS         VALUE       STAT_ID        CON_ID
------------- ------------------------ ------------- ------------- ------------- -------------
         1892 parse time cpu                      64         12981     206905303             3
         1893 parse time elapsed                  64         13826    1431595225             3
         1894 parse count (total)                 64        784665      63887964             3
         1895 parse count (hard)                  64         33672     143509059             3
         1896 parse count (failures)              64          1793    1118776443             3
         1897 parse count (describe)              64           161     469016317             3

6 rows selected.

Elapsed: 00:00:00.01

hard parseの回数を確認する。

sqlトレースファイルをtkprofで整形して確認する。

処理 sql
検索系・更新系 alter system flush shared_pool;
検索系・更新系 alter system flush buffer_cache;
更新系 alter system checkpoint;
更新系 alter system switch logfile;
コード表示

col name for a24;
col value for a12;
select name,value from V$PARAMETER where NAME like '%tracefile_identifier%';
alter session set tracefile_identifier='test_trace';
col name for a24;
col value for a12;
select name,value from V$PARAMETER where NAME like '%sql_trace%';
ALTER SESSION SET sql_trace=TRUE;
alter system checkpoint;
alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
ALTER SESSION SET sql_trace=FASLE;
コード表示

[oracle@6898242bcdd5 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 27 11:52:06 2019
Version 18.3.0.0.0

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

Last Successful login time: Sun Jan 27 2019 11:38:51 +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 a24;
AINE@pdb1> col value for a12;
AINE@pdb1> select name,value from V$PARAMETER where NAME like '%tracefile_identifier%';

NAME                     VALUE
------------------------ ------------
tracefile_identifier

1 row selected.

Elapsed: 00:00:00.01
AINE@pdb1> alter session set tracefile_identifier='test_trace';

Session altered.

Elapsed: 00:00:00.00
AINE@pdb1> select name,value from V$PARAMETER where NAME like '%tracefile_identifier%';

NAME                     VALUE
------------------------ ------------
tracefile_identifier     test_trace

1 row selected.

Elapsed: 00:00:00.01
AINE@pdb1> col name for a24;
AINE@pdb1> col value for a12;
AINE@pdb1> select name,value from V$PARAMETER where NAME like '%sql_trace%';

NAME                     VALUE
------------------------ ------------
sql_trace                FALSE

1 row selected.

Elapsed: 00:00:00.02
AINE@pdb1> ALTER SESSION SET sql_trace=TRUE;

Session altered.

Elapsed: 00:00:00.01
AINE@pdb1> select name,value from V$PARAMETER where NAME like '%sql_trace%';

NAME                     VALUE
------------------------ ------------
sql_trace                TRUE

1 row selected.

Elapsed: 00:00:00.01
AINE@pdb1> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.21
AINE@pdb1> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.03
AINE@pdb1> host ls -lt
total 212
-rw-r--r--. 1 oracle oinstall    265 Jan 27 11:41 test.sql
-rw-r--r--. 1 oracle oinstall 162575 Jan 26 14:39 test_statspack.lst
-rw-r--r--. 1 oracle oinstall    237 Jan 26 09:41 spcpkg.lis
-rw-r--r--. 1 oracle oinstall  12864 Jan 26 09:41 spctab.lis
-rw-r--r--. 1 oracle oinstall   4570 Jan 26 09:41 spcusr.lis
drwxr-xr-x. 2 oracle oinstall   4096 Jan 20 10:58 rlwrap-extensions
-rw-r--r--. 1 root   root      15080 Jan 20 10:52 epel-release-7-11.noarch.rpm
lrwxrwxrwx. 1 root   root         26 Jan 19 23:19 setPassword.sh -> /opt/oracle/setPassword.sh

AINE@pdb1> host vim test.sql
AINE@pdb1> @test.sql

別セッションにてtraceファイルが吐かれているかを確認する。

コード表示

[oracle@6898242bcdd5 ~]$ cd /opt/oracle/diag/rdbms/orcl/ORCL/trace
[oracle@6898242bcdd5 trace]$ ll -lt | grep test_trace
-rw-r-----. 1 oracle oinstall  551104 Jan 27 12:00 ORCL_ora_3725_test_trace.trc
-rw-r-----. 1 oracle oinstall   84736 Jan 27 12:00 ORCL_ora_3725_test_trace.trm
-rw-r--r--. 1 oracle oinstall   60233 Jan 27 09:43 ORCL_ora_29903_test_trace.trc.tkprof
-rw-r-----. 1 oracle oinstall  395099 Jan 27 09:20 ORCL_ora_29903_test_trace.trc
-rw-r-----. 1 oracle oinstall   31189 Jan 27 09:20 ORCL_ora_29903_test_trace.trm
[oracle@6898242bcdd5 trace]$ tkprof ORCL_ora_3725_test_trace.trc ORCL_ora_3725_test_trace.trc.tkprof

TKPROF: Release 18.0.0.0.0 - Development on Sun Jan 27 12:02:46 2019

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.


[oracle@6898242bcdd5 trace]$ ll -lt | grep test_trace
-rw-r--r--. 1 oracle oinstall  161006 Jan 27 12:02 ORCL_ora_3725_test_trace.trc.tkprof
-rw-r-----. 1 oracle oinstall  551104 Jan 27 12:00 ORCL_ora_3725_test_trace.trc
-rw-r-----. 1 oracle oinstall   84736 Jan 27 12:00 ORCL_ora_3725_test_trace.trm
-rw-r--r--. 1 oracle oinstall   60233 Jan 27 09:43 ORCL_ora_29903_test_trace.trc.tkprof
-rw-r-----. 1 oracle oinstall  395099 Jan 27 09:20 ORCL_ora_29903_test_trace.trc
-rw-r-----. 1 oracle oinstall   31189 Jan 27 09:20 ORCL_ora_29903_test_trace.trm

コード表示

[oracle@6898242bcdd5 trace]$ cat -n ORCL_ora_3725_test_trace.trc.tkprof | head -n 2062 | tail -`expr 2062 - 1407 + 1`
  1407  ********************************************************************************
  1408
  1409  WITH a AS (
  1410          SELECT /*+ inline */
  1411                  1
  1412          FROM
  1413                  dual
  1414                  , dual
  1415                  , dual
  1416                  , dual
  1417                  , dual
  1418                  , dual
  1419                  , dual
  1420                  , dual
  1421                  , dual
  1422                  , dual
  1423  ), b AS (
  1424          SELECT /*+ inline */
  1425                  1
  1426          FROM
  1427                  a
  1428                  , a
  1429                  , a
  1430                  , a
  1431                  , a
  1432                  , a
  1433                  , a
  1434                  , a
  1435                  , a
  1436                  , a
  1437  ) SELECT
  1438          1
  1439    FROM
  1440          b
  1441          , b
  1442          , b
  1443
  1444  call     count       cpu    elapsed       disk      query    current        rows
  1445  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  1446  Parse        1     18.05      18.05          0          0          0           0
  1447  Execute      1      0.00       0.00          0          0          0           0
  1448  Fetch        2      0.00       0.00          0          0          0           1
  1449  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  1450  total        4     18.05      18.05          0          0          0           1
********************************************************************************

サマリ。Parse行のcpu列とelapsed列に着目。

コード表示

  1444  call     count       cpu    elapsed       disk      query    current        rows
  1445  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  1446  Parse        1     18.05      18.05          0          0          0           0
  1447  Execute      1      0.00       0.00          0          0          0           0
  1448  Fetch        2      0.00       0.00          0          0          0           1
  1449  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  1450  total        4     18.05      18.05          0          0          0           1

v$sysstatでsql実行後の状況を確認してみる。

9.96 V$SYSSTAT

システム全体でのサマリを参照することができる。

コード表示

col name for a24
col value for 999999999999
select * from v$sysstat where NAME like 'parse%';
コード表示

AINE@pdb1> col name for a24
AINE@pdb1> col value for 999999999999
AINE@pdb1> select * from v$sysstat where NAME like 'parse%';

   STATISTIC# NAME                             CLASS         VALUE       STAT_ID        CON_ID
------------- ------------------------ ------------- ------------- ------------- -------------
         1892 parse time cpu                      64         14793     206905303             3
         1893 parse time elapsed                  64         15639    1431595225             3
         1894 parse count (total)                 64        784920      63887964             3
         1895 parse count (hard)                  64         33797     143509059             3
         1896 parse count (failures)              64          1793    1118776443             3
         1897 parse count (describe)              64           161     469016317             3

6 rows selected.

Elapsed: 00:00:00.02
AINE@pdb1> host expr 33797 - 33672
125

tkprofで加工されたsqlトレースファイルに出力されている項目に関して

コード表示

  1444  call     count       cpu    elapsed       disk      query    current        rows
  1445  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  1446  Parse        1     18.05      18.05          0          0          0           0
  1447  Execute      1      0.00       0.00          0          0          0           0
  1448  Fetch        2      0.00       0.00          0          0          0           1
  1449  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  1450  total        4     18.05      18.05          0          0          0           1

トレース統計(表側)

項目名 概要
Parse もろもろのチェックを行って、SQLを実行計画に変換する。
Execute Oracleによって実行されるSQL。INSERT文、UPDATE文、DELETE文、SFIECT文、MERGE文とかとか。
Fetch 問い合わせを満たす行数。

トレース統計(表頭)

項目名 概要
count 表側の項目に対する回数。
cpu 表側の項目に対するcpuの処理時間の合計(秒)。TIMED_STATISTICSがONになっている場合のみ。
elapsed 表側の項目に対する経過時間の合計(秒)。TIMED_STATISTICSがONになっている場合のみ。
disk ディスク上のデータファイルから物理読み込みしたデータブロックの総数。
query 一貫モードで取得されたバッファの総数。SELECT文は一貫モード。
current 一貫モードで取得されたバッファの総数。DML文は現行モード。
rows 処理された行数。サブクエリの総数は含まない。

トレース統計(実行計画)

項目名 概要
cr 行単位での結果に対して、バッファから読み取ったブロック総数
pr 行単位での結果に対して、物理読み込みブロックの総数
pw 行単位での結果に対して、物理書き込みブロックの総数
time 処理にかかった時間(単位:マイクロ秒)

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