この記事は約148分27秒で読むことができます。

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

One thought on “sql oracle statspack インストールからレポートの作成まで”

Leave a Reply

Your email address will not be published. Required fields are marked *