オラクる。

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

カテゴリ： oracle

SQL階層問い合わせではconnect句で複数条件を指定することが可能です。

SQL> select * from t2;

        ID        SEQ
---------- ----------
         1          1
         1          2
         1          3
         3          1
         4          1
         4          3
         5          1
         5          2
         5          3

9行が選択されました。

上記のようなテーブルがあったとします。

SQL> select ID,Seq,Level
  2  from t2
  3  start with Seq = 1
  4  connect by prior ID = ID
  5  and prior Seq = Seq - 1;

        ID        SEQ      LEVEL
---------- ---------- ----------
         1          1          1
         1          2          2
         1          3          3
         3          1          1
         4          1          1
         5          1          1
         5          2          2
         5          3          3

8行が選択されました。

「start with Seq = 1」はSeq=1の列が親になります。
connect byに一つ目の条件「prior ID = ID」ではIDが一致する場合、
なおかつ二つ目の条件「Seq = Seq - 1」はSeqの差が1の場合、親子関係が成立します。

さらにstart with句を省略することも可能です。
この場合、全ての列が親になります。

SQL> select * from t3;

       VAL
----------
         1
         2
         3
         4
         5

上記のような列があったとします。

select Val,Level,
sys_connect_by_path(to_char(Val),',') as Path
from t3
connect by prior Val = Val - 1;

       VAL      LEVEL PATH
---------- ---------- ----------
         1          1 ,1
         2          2 ,1,2
         3          3 ,1,2,3
         4          4 ,1,2,3,4
         5          5 ,1,2,3,4,5
         2          1 ,2
         3          2 ,2,3
         4          3 ,2,3,4
         5          4 ,2,3,4,5
         3          1 ,3
         4          2 ,3,4

       VAL      LEVEL PATH
---------- ---------- ----------
         5          3 ,3,4,5
         4          1 ,4
         5          2 ,4,5
         5          1 ,5

15行が選択されました。

「connect by prior Val = Val - 1;」でValの差が1の場合に親子関係を結びます。
なお、sys_connect_by_pathでは親にたどり着くまでのパスを表示することが出来ます。

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レポートは２地点のスナップショット間の統計情報が記載されています。
まず表示するスナップショットの期間（日）を指定します。

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では取得する情報がレベルで区分けされていて、通常はレベル５で情報が取得されます。
より詳細に情報を取得したい場合は、さらに上のレベルを指定します。

[oracle@DB01 ~]$ sqlplus perfstat/password

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

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

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

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が作成され、スケジュールされます。
デフォルトでは１時間毎に取得されます。

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番号を指定し、３０分間隔の場合は「SYSDATE+(1/48)」、２時間間隔の場合は「SYSDATE+(1/12)」というふうに指定します。

SQL> execute dbms_job.remove(1);

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

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

このページのトップヘ