オラクる。

oracle専門ブログにしてみようかな~っと

2016年10月

oracleの階層問い合わせではテーブルの木構造を意識した問い合わせを行なうことが出来ます。
言葉では伝えづらいので、まずはテストしてみましょう。

SQL> select * from t1;

        ID      OYAID
---------- ----------
         1
         2          1
         3          2
         4          3
         5          1
         6          5
         7          2
        20
        21         20
        22         21

10行が選択されました。


上記のようなIdとOyaIdで構成されたテーブルがあります。
ある行のIDと、ある行のOyaIDが一致する場合は親子関係を結ぶものとします。

SQL> select ID,OyaID,Level
  2  from t1
  3  start with OyaID is null
  4  connect by prior ID = OyaID;

        ID      OYAID      LEVEL
---------- ---------- ----------
         1                     1
         2          1          2
         3          2          3
         4          3          4
         7          2          3
         5          1          2
         6          5          3
        20                     1
        21         20          2
        22         21          3

10行が選択されました。


「start with OyaID is null」よりOyaIDがnullの行は親のいない行、木構造の根っこの部分、一番てっぺんの行になります。
「connect by prior ID = OyaID」より親のIDと子のOyaIDが一致する場合は親子関係を結ぶものとします。
さらにlevelというのは疑似列で、木構造の何段目にいるかを表します。
例えば一番てっぺんの親はlevel=1、その親の子はlevel=2、さらにその親の子の子はlevel=3と言ったふうになります。

oracleのAWRレポートを取得することで、パフォーマンスの問題を確認、分析し、改善案を策定する手助けとなることが出来ます。
なお、AWRレポートを利用する場合はOracleのEditionがEnterpriseで、有償オプションのDiagnostics Packを購入する必要があります。

[oracle@DB01 ~]$ sqlplus / as sysdba

SQL> show parameter STATISTICS_LEVEL

NAME_COL_PLUS_SHOW_P TYPE        VALUE_COL_PLUS_SHOW_PARAM
-------------------- ----------- ------------------------------
statistics_level     string      TYPICAL


まず、sysdba権限でログインし、statistics_levelパラメーターがTYPICAL以上であることを確認します。

SQL> @?/rdbms/admin/awrrpt.sql

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

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1452348317 ORCL                1 orcl


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats.  Please enter the
name of the format at the prompt.  Default value is 'html'.

'html'          HTML format (default)
'text'          Text format
'active-html'   Includes Performance Hub active report

report_typeに値を入力してください: html

Type Specified:  html


レポートを作成するため、awrrpt.sqlを実行します。

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 1452348317        1 ORCL         orcl         DB01

Using 1452348317 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 <return> without
specifying a number lists all completed snapshots.


num_daysに値を入力してください: 3

Listing the last 3 days of Completed Snapshots


awrレポートは2地点のスナップショット間の統計情報が記載されています。
まず表示するスナップショットの期間(日)を指定します。

Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
orcl         ORCL               216 16 10月 2016 00:00     1
                                217 16 10月 2016 01:00     1
                                218 16 10月 2016 02:00     1
                                219 16 10月 2016 03:00     1

(中略)

                                258 17 10月 2016 18:00     1
                                259 17 10月 2016 19:00     1
                                260 17 10月 2016 20:00     1
                                261 17 10月 2016 21:00     1
                                262 17 10月 2016 22:00     1
                                263 17 10月 2016 23:00     1
                                264 18 10月 2016 00:00     1



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
begin_snapに値を入力してください: 250
Begin Snapshot Id specified: 250

end_snapに値を入力してください: 259
End   Snapshot Id specified: 259


表示されたスナップショットの一覧から始点と終点を選びます。

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_250_259.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

report_nameに値を入力してください:

Using the report name awrrpt_1_250_259.html


最後にレポートのファイル名を指定します。
これでレポートが作成されます。

1

statspackでは取得する情報がレベルで区分けされていて、通常はレベル5で情報が取得されます。
より詳細に情報を取得したい場合は、さらに上のレベルを指定します。

[oracle@DB01 ~]$ sqlplus perfstat/password

SQL> execute statspack.snap(i_snap_level=>7)

PL/SQLプロシージャが正常に完了しました。


i_snap_levelパラメーターでレベルを指定し、statspackを取得します。
例ではレベル7で取得しています。

Segments by Logical Reads  DB/Inst: ORCL/orcl  Snaps: 98-99
-> End Segment Logical Reads Threshold:     10000
-> Pct Total shows % of logical reads for each top segment compared with total
   logical reads for all segments captured by the Snapshot

                                           Subobject    Obj.       Logical   Pct
Owner      Tablespace Object Name          Name         Type         Reads Total
---------- ---------- -------------------- ------------ ----- ------------ -----
SYS        SYSAUX     WRH$_SQLSTAT_INDEX   2348317_25   INDEX       10,016  48.3
SYS        SYSTEM     OBJ$                              TABLE        4,736  22.9
PERFSTAT   TOOLS      STATS$SQL_SUMMARY                 TABLE        1,200   5.8
PERFSTAT   TOOLS      STATS$SQL_SUMMARY_PK              INDEX        1,088   5.3
PERFSTAT   TOOLS      STATS$LATCH_PK                    INDEX          816   3.9
          -------------------------------------------------------------


レベル7のstatspackはレベル5の場合と違い、セグメント(表、索引)レベルの情報が取得されます。
レポートの「Segments by・・・」以降に表示されます。

SQL ordered by CPU  DB/Inst: ORCL/orcl  Snaps: 98-99
-> Total DB CPU (s):              10
-> Captured SQL accounts for  191.2% of Total DB CPU
-> SQL reported below exceeded  1.0% of Total DB CPU

    CPU                  CPU per             Elapsd                     Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)    Buffer Gets  Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
      9.83            1       9.83   99.6      10.10          52,203 2435229694
Module: SQL*Plus
BEGIN statspack.snap(i_snap_level=>7); END;


また、レベル6の場合はレベル5と違い、SQLの詳細レポートが取得することが出来ます。
レポートの「SQL ordered by・・・」に、それぞれの値で悪かった順にSQLが表示されます。
そこからSQLのHash値を確認し、sprepsql.sqlでsql詳細レポートを取得します。

SQL> @?/rdbms/admin/sprepsql.sql

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

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1452348317 ORCL                1 orcl



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
begin_snapに値を入力してください: 98
Begin Snapshot Id specified: 98

end_snapに値を入力してください: 99
End   Snapshot Id specified: 99



Specify the old (i.e. pre-10g) Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
hash_valueに値を入力してください: 2435229694
Hash Value specified is: 2435229694

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_98_99_2435229694.  To use this name,
press <return> to continue, otherwise enter an alternative.

report_nameに値を入力してください:

Using the report name sp_98_99_2435229694



レポートの番号とSQLハッシュ値を指定します。
これでSQLの詳細レポートが出力されます。

statspackの取得を自動化することが出来ます。

[oracle@DB01 ~]$ sqlplus perfstat/password

SQL> @?/rdbms/admin/spauto.sql

PL/SQLプロシージャが正常に完了しました。


Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:

     JOBNO
----------
         1


Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000


Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

       JOB NEXT_DAT NEXT_SEC
---------- -------- --------------------------------
         1 16-10-15 01:00:00


スキーマユーザでログインし、spauto.sqlを実行します。
oracle管理のstatspack取得jobが作成され、スケジュールされます。
デフォルトでは1時間毎に取得されます。

SQL> select job,what,next_date,next_sec from dba_jobs;

 JOB WHAT                           NEXT_DATE                      NEXT_SEC
---- ------------------------------ ------------------------------ ------------------------------
   1 statspack.snap;                16-10-15                       01:00:00


dba_jobsビューでjobの登録、次回実行時刻を確認します。

SQL> execute dbms_job.interval(1,'SYSDATE+(1/48)');

PL/SQLプロシージャが正常に完了しました。


ジョブの実行間隔はdbms_job.intervalで変更できます。
job番号を指定し、30分間隔の場合は「SYSDATE+(1/48)」、2時間間隔の場合は「SYSDATE+(1/12)」というふうに指定します。

SQL> execute dbms_job.remove(1);

PL/SQLプロシージャが正常に完了しました。


作成したjobはdbms_job.removeで削除します。

statspackはある一時点から一時点までの統計情報を取得するツールです。
エディションを問わずに利用できるので、とても手軽です。

[oracle@DB01 ~]$ sqlplus / as sysdba

CREATE TABLESPACE TOOLS
DATAFILE '+DATA/ORCL/DATAFILE/tools01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;


statspackを利用するにはまず専用のテーブルスペースを作成します。

SQL> @?/rdbms/admin/spcreate.sql

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

perfstat_passwordに値を入力してください: password
password


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
------------------------------ --------- ----------------------------
EXAMPLE                        PERMANENT
SYSAUX                         PERMANENT *
TOOLS                          PERMANENT
USERS                          PERMANENT

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

default_tablespaceに値を入力してください: TOOLS

Using tablespace TOOLS as PERFSTAT default tablespace.


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 *

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

temporary_tablespaceに値を入力してください: TEMP




続いてstatspak作成用のsql、spcreate.sqlを実行します。
専用スキーマのパスワード、表領域を指定します。

[oracle@DB01 ~]$ sqlplus perfstat/password

SQL> execute statspack.snap

PL/SQLプロシージャが正常に完了しました。



SQL> execute statspack.snap

PL/SQLプロシージャが正常に完了しました。

statspackのレポートを作成するためには、まず2時点以上のスナップショットを取得する必要があります。
pertstatスキーマが作成されているのでsqlplusで接続します。
「execute statspack.snap」でスナップショットを取得します。

SQL> @?/rdbms/admin/spreport.sql

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

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1452348317 ORCL                1 orcl



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

   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
 1452348317        1 ORCL         orcl         DB01

Using 1452348317 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 <return> without
specifying a number lists all completed snapshots.



Listing all Completed Snapshots

                                                       Snap
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
orcl         ORCL                 1 14 10月 2016 00:4     5
                                    9
                                  2 14 10月 2016 00:5     5
                                    0



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
begin_snapに値を入力してください: 1
Begin Snapshot Id specified: 1

end_snapに値を入力してください: 2

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2.  To use this name,
press <return> to continue, otherwise enter an alternative.

report_nameに値を入力してください: test_report.txt



End of Report ( test_report.txt )


そして、レポートを作成するため、spreport.sqlを実行します。
番号で始点となるスナップショットと終点となるスナップショットを選択します。
レポートでは始点から終点の統計情報が記載されます。

このページのトップヘ