Shulou(Shulou.com)06/02 Report--

Statspack script storage directory:

$ORACLE_HOME/RDBMS/ADMIN

1. Parameters to be set:

1) .job_queue_processes

SQL > alter system set job_queue_processes = 6

SQL > alter system set job_queue_processes = 6 scope=both

In order to be able to set up automatic tasks and perform data collection, this parameter needs to be greater than 0. 0.

You can modify this parameter in the pilot parameter file (to make it valid after restart).

This parameter can be dynamically modified at the system level (invalidated after restart).

2). Timed_statistics

SQL > alter system set timed_statistics = true

Collect timing information of the operating system, which can be used to display statistics such as time, optimize databases and SQL statements.

False- prevents overhead caused by requesting time from the operating system

True- enables statspack to collect statistics, otherwise the collected statistics can only play a role of about 10%. You can change the parameter in system before using statspack, and dynamically modify the parameter to false. It can also be opened unanimously. The advantages outweigh the disadvantages.

This parameter stores the collected time information in dynamic performance views such as V$SESSTATS and V$SYSSTATS.

two。 Install statspack

1)。 Log in as internal or a user with SYSDBA authority

Cd $ORACLE_HOME/RDBMS/ADMIN

Sqlplus / as sysdba

2)。 Check the data file path and disk space to determine the location of the data file and establish a table space of more than 100m.

SQL > select file_name from dba_data_files

SQL > create tablespace perfstat datafile'd:\ oracle\ oradata\ eygle\ perfstat.dbf' size 500m extent management local

3)。 Execute the creation script. During the creation process, you will be prompted to enter the contents of default_tablespace and temporary_tablespace. If there are any errors, you can view the generated .lis file.

SQL > @ spcreate

If you need to rebuild, you can run the spdrop.sql script to delete these objects. Then rerun spcreate.sql

SQL > @ spdrop.sql

SQL > @ spcreate

3. Test the usability of Statspack

Run statspack.snap to generate a snapshot of the system, run it twice, and then execute spreport.sql to generate a report based on two points in time.

If everything is normal, the installation is successful.

SQL > execute statspack.snap

SQL > execute statspack.snap

SQL > @ spreport.sql

4. Set up scheduled tasks

SQL > @ spauto

Script about the time interval settings, you can modify the contents of spauto.sql to change the execution interval, the default interval is one hour.

Dbms_job.submit (: jobno, 'statspack.snap;',trunc (sysdate+1/24,'HH'),' trunc (SYSDATE+1/24,''HH'')', TRUE,: instno)

This job task defines the interval at which data is collected:

There are 24 hours and 1440 minutes in a day, so:

One stop 24 HH once an hour

1pm 48 MI once every half hour

1According to 144 MI every ten minutes

1pm 288 MI once every five minutes

5. Remove scheduled tasks

SQL > select job,log_user,priv_user,last_date,next_date,interval from user_jobs

SQL > execute dbms_job.remove ('28')

6. Generate an analysis report

SQL > @ spreport

7. Delete historical data

The first method: delete the corresponding data in the stats$snapshot data table, and the data in other tables will be cascaded to delete:

SQL > select max (snap_id) from stats$snapshot

SQL > delete from stats$snapshot where snap_id @ sptrunc

8. Use the spuexp.par file exp to save the appropriate data

Spuexp.par content:

File=spuexp.dmp log=spuexp.log compress=y grants=y indexes=y rows=y constraints=y wner=PERFSTAT consistent=y

Export statement:

Exp userid=perfstat/perfstat parfile=spuexp.par

9. Use sprepsql.sql to generate an SQL report based on a given SQL Hash value

SQL > @ sprepsql

10. Adjust the collection threshold of STATSPACK

Statspack has two types of collection options:

Level (level): controls the type of data collected

Threshold (threshold): sets the threshold for collected data.

1). Level (level)

There are three snapshot levels in Statspack. The default value is 5.

A.level 0: general performance statistics. Including wait events, system events, system statistics, rollback segment statistics, row cache, SGA, session, locks, buffer pool statistics and so on.

B.level 5: add SQL statements. In addition to including all the contents of level0, but also includes the collection of SQL statements, the collection results are recorded in stats$sql_summary.

C.level 10: add sub-latch statistics. Including all the contents of level5. Additional child locks are also stored in stats$lathc_children. This level needs to be used with caution and is recommended under the guidance of Oracle support.

The default level settings can be modified through the statspack package

SQL > execute statspack.snap (irecording snapshots level = > 0theconditionmodifyparameter = > 'true')

If you just want to change the collection level this time, you can ignore the i_modify_parameter parameter.

SQL > execute statspack.snap (iSnapshot level = > 0)

2)。 Snapshot threshold

The snapshot threshold applies only to SQL statements obtained in the stats$sql_summary table.

Because each snapshot collects a lot of data, and each row represents a SQL statement in the database when the snapshot is taken, stats$sql_summary will soon become the largest table in Statspack.

The threshold is stored in the stats$statspack_parameter table:

Executions_th this is the number of SQL statements executed (default is 100)

Disk_reads_tn this is the number of disk reads performed by the SQL statement (default is 1000)

Parse_calls_th this is the number of parse calls performed by the SQL statement (default is 1000)

Buffer_gets_th this is the number of buffers fetched by SQL statements (default is 10000)

Any threshold value that exceeds the above parameters will result in a record.

Change the default value of the threshold by calling the statspack.modify_statspack_parameter function:

SQL > execute statspack.modify_statspack_parameter (iSuppli bufferless getskeeper = > 1000000 recordings iDesignreadsreaders = > 100000)

Important contents of 11.Statspack report

1). "Execute to Parse%" perform analysis ratio

= 100 * (1-Parses/Executions)

Parses = "parse count (total)"

Executions = "execute count"

When Parses > Executions, the ratio is less than 0.

This value

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.