SQL*Plus: Release 10.2.0.2.0 - Production on Tue Dec 9 23:04:02 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning and Data Mining options
SQL> @?/rdbms/admin/spcreate
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value for perfstat_password: perfstat ←ユーザ名を指定。
perfstat
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.
Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace: ←リターンキーを押します。
Using tablespace SYSAUX 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.
Using 2429168160 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
InstanceDB NameSnap IdSnap StartedLevel Comment
------------ ------------ --------- ----------------- ----- -------------
testdbTESTDB1 09 Dec 2008 23:08 7 2 09 Dec 2008 23:187 3 09 Dec 2008 23:427 4 09 Dec 2008 23:527
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1 ←開始点となる「Snap Id」を指定します。
Begin Snapshot Id specified: 1
Enter value for end_snap: 2 ←終了点となる「Snap Id」を指定します。
End Snapshot Id specified: 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.
Enter value for report_name: report1 ←任意のレポート名を入力します。
Using the report name report1
・・・・ 以下略 ・・・・
※ spreport.sql実行結果（インデックス無）
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 3 ←開始点となる「Snap Id」を指定します。
Begin Snapshot Id specified: 1
Enter value for end_snap: 4 ←終了点となる「Snap Id」を指定します。
End Snapshot Id specified: 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.
Enter value for report_name: report2 ←任意のレポート名を入力します。
Top 5 Timed EventsAvg %Total
~~~~~~~~~~~~~~~~~~wait Call
EventWaits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------ CPU time1444.8
db file parallel write1,2517520.8
log file sync1,1993310.2
log file parallel write1,221339.7
db file sequential read2,185217.6 -------------------------------------------------------------
※ Top 5 timed Events（インデックス無）
Top 5 Timed EventsAvg %Total
~~~~~~~~~~~~~~~~~~wait Call
EventWaits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------ CPU time11492.7
log file sync1,179332.6
log file parallel write1,183322.2
control file parallel write203291.4
db file parallel write236161.1 -------------------------------------------------------------
SQL ordered by CPU DB/Inst: TESTDB/testdb Snaps: 1-2
-> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
-> Total DB CPU (s): 11
-> Captured SQL accounts for 44.7% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU
CPUCPUperElapsdOld Time (s) ExecutionsExec (s) %TotalTime (s)Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ---------- 2.855,4700.0025.42.8776,927 1431284007
select * from ITEMLIST where CATEGORY = :1
0.706180.006.30.7210,115 3439545154
insert into ORDERLIST values (SEQ_ORDER_ID.nextval, :1, sysdate)
※ SQL ordered by CPU（インデックス無）
SQL ordered by CPU DB/Inst: TESTDB/testdb Snaps: 3-4
-> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
-> Total DB CPU (s): 102
-> Captured SQL accounts for 95.7% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU
CPUCPUperElapsdOld Time (s) ExecutionsExec (s) %TotalTime (s)Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ---------- 95.775,3490.02 93.5115.37 11,803,437 1431284007
select * from ITEMLIST where CATEGORY = :1
それぞれ該当SQL（select * from ITEMLIST where CATEGORY = :1）の実行回数（Executionsカラム）の数値はほぼ同一であるものの、"Buffer Gets"の回数が大幅に異なっている（インデックス無のほうが約150倍）ことがわかります。Buffer Getsとは、該当SQLによってデータベースバッファキャッシュから取得されたデータブロック数を示しています。
続いてSQL（「select * from ITEMLIST where CATEGORY = :1」HashValue：1431284007）の詳細情報を確認するため、該当SQLの実行計画の取得を行います。SQL$Plusを使用してPERFSTATユーザにてデータベースに接続し、スクリプト「$ORACLE_HOME/rdbms/admin/sprepsql.sql」を実行してください。
※ sprepsql.sql実行結果（インデックス有）
SQL> @?/rdbms/admin/sprepsql
・・・・ 中略 ・・・・
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 2429168160 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
InstanceDB NameSnap IdSnap StartedLevel Comment
------------ ------------ --------- ----------------- ----- -------------
testdbTESTDB1 09 Dec 2008 23:08 7 2 09 Dec 2008 23:187 3 09 Dec 2008 23:427 4 09 Dec 2008 23:527
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1 ←開始点となる「Snap Id」を指定します。
Begin Snapshot Id specified: 1
Enter value for end_snap: 2 ←終了点となる「Snap Id」を指定します。
End Snapshot Id specified: 2
Specify the old (i.e. pre-10g) Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for hash_value: 1431284007 ←該当SQLのHashValueを入力します。
Hash Value specified is: 1431284007
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.
Enter value for report_name: report3 ←任意のレポート名を入力します。
Using the report name report1
・・・・ 以下略 ・・・・
※ sprepsql.sql実行結果（インデックス無）
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 3 ←開始点となる「Snap Id」を指定します。
Begin Snapshot Id specified: 1
Enter value for end_snap: 4 ←終了点となる「Snap Id」を指定します。
End Snapshot Id specified: 2
Specify the old (i.e. pre-10g) Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for hash_value: 1431284007 ←該当SQLのHashValueを入力します。
Hash Value specified is: 1431284007
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.
Enter value for report_name: report4 ←任意のレポート名を入力します。
Using the report name report1
・・・・ 以下略 ・・・・
Step14
- 実行計画の比較・分析 -
以下は「インデックス有」、「インデックス無」のSQL（select * from ITEMLIST where CATEGORY = :1）の実行計画です。
※ 実行計画（インデックス有）
・・・・ 上略 ・・・・
% Snap Statement Total Per ExecuteTotal --------------- --------------- ------ Buffer Gets:76,92714.161.60 Disk Reads:1,1510.251.85 Rows processed:273,50050.0 CPU Time(s/ms):3.5 Elapsed Time(s/ms):3.5
・・・・ 中略 ・・・・
--------------------------------------------------------------------------------
| Operation| PHV/Object Name|Rows | Bytes|Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT|----- 3603706253 ----|||4 |
|TABLE ACCESS BY INDEX ROWID|ITEMLIST|50 |8K|4 |
| INDEX RANGE SCAN|IDX_ITEMLIST_CATEGOR |50 ||1 |
--------------------------------------------------------------------------------
