How to configure statspack in Oracle Database 10 GB

 

First, perform some basic settings.

1. Input in SQL * Plus

SQL> connect sys/sys as sysdba;

SQL> alter system set job_queue_processes = 6; -- this parameter must be greater than 0 during automatic data collection.

System altered

SQL> alter system set timed_statistics = true;

System altered

-- It is recommended that this value be set to true when statspack is used to collect statistics. Otherwise, the collected statistics can only be 10%.

 

SQL> @ C: \ oracle \ ora92 \ RDBMS \ admin \ spcreate. SQL;

Enter the perfstat_password value: perfstat

Enter the value of default_tablespace: perfstat

Enter the value of temporary_tablespace: temp

 

Note:

Spcpkg complete. Please check spcpkg. Lis for any errors.

-- The preceding statement is successful. Otherwise, view and execute the. Lis file and recreate it.

SQL> @ C: \ oracle \ ora92 \ RDBMS \ admin \ spdrop. SQL

SQL> @ C: \ oracle \ ora92 \ RDBMS \ admin \ spcreate. SQL;

 

2. view the folder to generate three files

C: \ oracle \ ora92 \ bin

Spcpkg. Lis

Spctl. Lis

Spcusr. Lis

 

3. manually execute statspack to collect statistics

SQL> show user

The user is "perfstat"

SQL> execute statspack. Snap;

 

4. Generate the statspack adjustment report

SQL> @ C: \ oracle \ ora92 \ RDBMS \ admin \ spreport. SQL;

Current instance

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

Db id dB name inst num instance

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

357371480 Colm 1 Colm

Instances in this statspack Schema

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

Db id inst num dB name instance host

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

357371480 1 Colm Steven huang3

Using 357371480 for database ID

Using 1 for instance number

Completed snapshots

Snap

Instance dB name ID snap started level comment

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

Colm 1 11 October 20 5

0

October 20

0

Specify the begin and end snapshot IDS

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

Input begin_snap value: 1

Input end_snap value: 2

End snapshot ID specified: 2

Specify the report name

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

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

Press <return> </return> to continue, otherwise enter an alternative.

Input report_name value: report1.txt

End of report

 

5. view the generated report1 document

C: \ oracle \ ora92 \ bin \ report1.txt

6. automatically execute statspack to collect statistics

SQL> @ C: \ oracle \ ora92 \ RDBMS \ admin \ spauto. 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_col_plus_show_param

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

Type

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

Value_col_plus_show_param

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

Job_queue_processes

Integer

6

Next scheduled run

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

The next scheduled run for this job is:

Job next_date

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

Next_sec

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

1-11-10-06

23:00:00

Spauto. SQL mainly calls dbms_job.submit, which is collected once an hour by default (1/24)

Variable jobno number;

Variable instno number;

Begin

Select instance_number into: instno from V $ instance;

Dbms_job.submit (: jobno, 'statspack. snap; ', trunc (sysdate + 1/24, 'hh'), 'trunc (sysdate + 1/24, ''hh '')', true,: instno );

Commit;

End;

/

You can set the collection time by modifying this value.

1/24 HH every hour

1/48 mi every 30 minutes

1/144 mi every ten minutes

1/288 mi every five minutes

7. Remove automatic statspack collection

SQL> select job, log_user, priv_user, last_date, next_date, interval from user_jobs; -- first view jobs automatically collected

SQL> execute dbms_job.remove ('1'); -- remove Task 1

8. Delete statistical data (all system tables related to statspack)

SQL> select max (snap_id) from stats $ snapshot;

SQL> @ C: \ oracle \ ora92 \ RDBMS \ admin \ sptrunc. SQL;

Warning

~~~~~~~

Running sptrunc. SQL removes all data from statspack tables. You may

Wish to export the data before continuing.

About to truncate statspack tables

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

If you wowould like to continue, press <return> </return>

Input return value:

Entered-starting truncate operation