1, Telnet to the remote database server
Cmd>telnet The remote database IP
2, switch to the database users
Dqxxdbs01:/> su-oracle
3, access to the database with super users
$ sqlplus "/as sysdba"
4, check the parameters, whether you can use the job (can be used to automatically collect Statpack data)
Sql> Show Parameter job_queue_processes
5, this parameter can collect the information of the operating system
Sql> Show Parameter Timed_statistics
6, in order to create a table space, the table space data files where the better
Copy Code code as follows:
Sql> select file_name from Dba_data_files;
7, to see which directory space is relatively large, determine the location of Perfstat table space data files
$ df-g
8, create the table space:
Copy Code code as follows:
sql> Create tablespace perfstat datafile '/oracle/product/10.2.0/db_1/dbs/perfstat.dbf ' size 500M;
--data collected when the table space is used to store snapshots
9, create the Statspack required script
Sql> @:/oracle/product/10.2.0/db_1/rdbms/admin/spcreate.sql--"/oracle/product/10.2.0/db_1/rdbms/admin/" as script directory
After you run the script, enter Perfstat_password, Default_tablespace, Temporary_tablespace
10, test whether successful, generate a snapshot of the database
Sql> Execute Statspack.snap
11. Regenerate into a snapshot
Sql> Execute Statspack.snap
12, take two snapshots between the time period of the Statspack report
Sql> @/oracle/product/10.2.0/db_1/rdbms/admin/spreport.sql
Enter the starting and ending points for generating the Statspack report, such as two point-in-time snapshots, input begin_snap:1 and end_snap:2, and then enter the name of the snapshot to be generated (such as 1.txt)
--Note: If you do not specify the location where the report is placed, the path to the report when you start Sqlplus
13, with the job to produce statspack snapshots, easy to collect data (default for 1 hours to produce a snapshot)
Sql> @/oracle/product/10.2.0/db_1/rdbms/admin/spauto.sql
14. Delete historical data
Select Max (snap_id) from stats$snapshot;--deletes the corresponding data in the Stats$snapshot table, and the corresponding data in other tables is also deleted
Delete from Stats$snapshot where snap_id<3;
You can also delete information for these tables directly by executing sptrunc.sql the script
Precautions:
1. After you generate the required statspack, remember to remove the task and view the task:
sql> Select Job, Log_user, Priv_user, last_date,next_date, interval from user_jobs;
2, remove the task of generating statspack snapshots:
Sql> Execute Dbms_job.remove (' 41 ')
3, if you run spcreate.sql this script error, you can run spdrop.sql this script to delete the created object, and then execute Spcreate.sql
4, after running spcreate.sql This script, you can find the relevant LIS file to view the installation information (under the Sqlplus default path)
Some of the other related scripts
1, Spuexp.par Script-can be used to export Statpack report related data dmp, as shown below
Exp Userid=perfstat/fyzh Parfile=spuexp.par
Note: The current path of the Spuexp.par file must be logged in Sqlplus. such as the landing of the Sqlplus path is C:\Documents and Settings\Administrator,
Then Spuexp.par will be in the directory, otherwise prompted "LRM-00109: Unable to open the parameter file ' Spuexp.par '"
2. Sprepsql.sql script--for generating SQL reports based on a given SQL hash value
Adjust the collecting threshold of statspack
Statspack has two types of collection options:
Level: Controls the type of data collected
Threshold (threshold): Sets the threshold value for the collected data.
1. Grade (level)
Statspack has three snapshot levels, the default value is 5
A.level 0: General performance statistics. Includes wait events, system events, System statistics, rollback segment statistics, row caches, SGA, sessions, locks, buffer pool statistics, and more.
B.level 5: Add SQL statements. In addition to all content that includes Level0, the collection of SQL statements is included, and the results are recorded in Stats$sql_summary.
C.level 10: Increase the number of child latches. Includes all content of the level5. The additional child latches are also stored in the Stats$lathc_children. Caution is required when using this level and is recommended under the guidance of Oracle support.
The default level settings can be modified through the Statspack package
Sql> Execute Statspack.snap (i_snap_level=>0,i_modify_parameter=> ' true ');
If you just want to change the collection level this time, you can ignore the I_modify_parameter parameter.
Sql> Execute Statspack.snap (i_snap_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, each row represents an SQL statement in the database when the snapshot is fetched, so stats$sql_summary will soon become the largest table in the Statspack.
The thresholds are stored in the Stats$statspack_parameter table:
Executions_th This is the number of SQL statement executions (the default value is 100)
Disk_reads_tn This is the number of disk reads executed by the SQL statement (the default is 1000)
Parse_calls_th This is the number of parse calls executed by the SQL statement (the default value is 1000)
Buffer_gets_th This is the number of buffer fetches executed by the SQL statement (the default value is 10000)
A record is generated for any threshold value above the above parameter.
Change the threshold default value by calling the Statspack.modify_statspack_parameter function:
Copy Code code as follows:
Sql>execute Statspack.modify_statspack_parameter (i_buffer_gets_th=>100000,i_disk_reads_th=>100000);
