1、 安装statspack

包都在oraclehome/rdbms/admin里

Cmd

Sqlplus / as sysdba;

@D:oracleproduct10.2.0db_1RDBMSADMINspcreate.sql

输入perfstat的密码

定义默认表空间 （如直接回车用temp，没有temp会自己建）

定义临时表空间 （如直接回车用temp，没有temp会自己建）

SQL> Rem cdialeri 02/16/00 - 1191805

SQL> Rem cdialeri 11/01/99 - Enhance, 1059172

SQL> Rem cgervasi 06/16/98 - Remove references to wrq

SQL> Rem cmlim 07/30/97 - Modified system events

SQL> Rem gwood.uk 02/30/94 - Modified

SQL> Rem densor.uk 03/31/93 - Modified

SQL> Rem cellis.uk 11/15/89 - Created

SQL> Rem

SQL>

SQL> set echo off;

Creating Package STATSPACK...

程序包已创建。

没有错误。

Creating Package Body STATSPACK...

程序包体已创建。

没有错误。

NOTE:

SPCPKG complete. Please check spcpkg.lis for any errors.

2.创建snap

Once you have installed Statspack the simplest way to collect a ‘snapshot’ of performance data is by executing the snap function from the Statspack package.

SQL> execute statspack.snap;

PL/SQL procedure successfully completed

SQL>

The example below will capture session level statistics for the session with a session id

(Oracle sid) of 32.

SQL> execute statspack.snap(i_session_id=>32);

It is recommended to set the parameter timed_statistics to true for your instance, as setting this parameter provides timing data which is invaluable for performance tuning, more than outweighing any additional overhead.

3:获取报告

以perfstat连进去

sqlplus perfstat/abc123;

SQL> @D:oracleproduct10.2.0db_1RDBMSADMINspreport.sql;

Current Instance

~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance

----------- ------------ -------- ------------

1174867431 ORCL 1 orcl

Instances in this Statspack schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host

----------- -------- ------------ ------------ ------------

1174867431 1 ORCL orcl CCCGW

Using 1174867431 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

Instance DB Name Snap Id Snap Started Level Comment

------------ ------------ --------- ----------------- ----- -------------------

orcl ORCL 1 22 2月 2008 09:0 5

9

11 22 2月 2008 09:1 5

7

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

输入 begin_snap 的值: 1

Begin Snapshot Id specified: 1

输入 end_snap 的值: 11

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is sp_1_11. To use this name,

press to continue, otherwise enter an alternative.

输入 report_name 的值:

之后就会显示结果。

自动SNAP：

SQL> @D:oracleproduct10.2.0db_1RDBMSADMINspauto.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

----------

21

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 1

Next scheduled run

~~~~~~~~~~~~~~~~~~

The next scheduled run for this job is:

JOB NEXT_DATE NEXT_SEC

---------- -------------- ----------------

21 22-2月 -08 15:00:00

SQL>

停止自动SNAP：

SQL> execute dbms_job.remove (21);

PL/SQL 过程已成功完成。

附注：

1、statspack相关sql作用

2、Statspack 相关参数设置：

Parameter Valid Values Default Value Meaning

i_snap_level 0, 5, 10 5 Snapshot level

i_ucomment text blank Comment to be stored with snapshot

i_executions_th integer >=0 50 SQL threshold: number of executions

i_disk_reads_th integer >=0 1,000 SQL threshold: number of disk reads

i_parse_calls_th integer >=0 1,000 SQL threshold: number of parse calls

i_buffer_gets_th integer >=0 10,000 SQL threshold: number of buffer gets

i_session_id valid SID from v$session 0 (no session) Session ID; capture session granular statistics

i_modify_parameter true, false true Save specified parameters for future snapshots

案例：

To change the parameters,simply specify the new defaults that Statspack should use. To save your new parameter values permanently, use statspack.snap to set the i_modify_parameter input variable to true; this will save the new values in the stats$statspack_parameter table, as

follows:

SQL> execute statspack.snap (i_snap_level=>10, i_modify_parameter=> ‘true’);

Alternatively, you can change the defaults without taking a snapshot immediately, using the statspack.modify_statspack_parameter procedure. For example, to change the snapshot level to 10 and the SQL thresholds for buffer_gets and disk_reads without actually taking a snapshot, issue the following statement:

SQL> execute statspack.modify_statspack_parameter (i_snap_level=>10, i_buffer_gets_th=>1000, i_disk_reads_th=>1000);

3、自动SNAP相关说明：

You can use d b m s _ j o b to automate statistics collection. The file s t a t s a u t o . s q l contains an

example of how to do this, scheduling a snapshot every hour. When you create a job by using d b m s _ j o b, Oracle assigns the job a unique number that you can use for changing or removing the job. In order to use d b m s _ j o b to schedule snapshots automatically, you must set the j o b _ q u e u e _ p r o c e s s e s initialization parameter to greater than 0 in the i n i t . o r a f i l e :

# Set to enable the job-queue process to start. This allows dbms_job

# to schedule automatic statistics collection, using Statspack

Alter system set job_queue_processes=1

Change the interval of statistics collection by using the dbms_job.interval procedure:

execute dbms_job.interval(,’SYSDATE+(1/48)’);

In this case, ‘SYSDATE+(1/48)’ causes the statistics to be gathered each 1/48 day—every half hour. To stop and remove the automatic-collection job:

execute dbms_job.remove(); Remove STATSPACK from the Database
After a STATSPACK session you want to remove the STATSPACK tables.
sqlplus "/ as sysdba"
SQL> @?/rdbms/admin/spdrop.sql
SQL> drop tablespace statspack including contents and datafiles;

找出可用的SNAP:

select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')"Date/Time" from stats$snapshot,v$database;

SQL ordered by Gets
Here you will find the most CPU-Time consuming SQL statements
SQL ordered by Gets DB/Inst: AKI1/AKI1 Snaps: 5-6
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 720,588
-> Captured SQL accounts for 3.1% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer Gets
CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
16,926 1 16,926.0 2.3 2.36 3.46 1279400914
Module: SQL*Plus
create table test as select * from all_objects

Generate Execution Plan for given SQL statement
If you have identified one or more problematic SQL statement, you may want to check the execution plan.
Remember the "Old Hash Value" from the report above (1279400914), then execute the scrip to generate the
execution plan.
sqlplus perfstat/perfstat
SQL> @?/rdbms/admin/sprepsql.sql
Enter the Hash Value, in this example: 1279400914
SQL Text
~~~~~~~~
create table test as select * from all_objects
Known Optimizer Plan(s) for this Old Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows all known Optimizer Plans for this database instance, and the Snap Id's
they were first found in the shared pool. A Plan Hash Value will appear
multiple times if the cost has changed
-> ordered by Snap Id
First First Plan
Snap Id Snap Time Hash Value Cost
--------- --------------- ------------ ----------
6 14 Nov 04 11:26 1386862634 52
Plans in shared pool between Begin and End Snap Ids