To generate an Oracle Statpack

Step 1. telnet to the remote database server CMD> telnet to the remote database IP address www.2cto.com 2. Switch to the Database User DQXXDBS01: /> su-oracle 3. Use a super user to access the database $ sqlplus "/as sysdba" 4. view the parameters, can I use a job? (I can use a job to automatically collect Statpack Report data) SQL> show parameter job_queue_processes 5. This parameter can collect information about the operating system. SQL> show parameter timed_statistics 6. To create a tablespace, SQL> select file_name from dba_data_files; 7. Check which directory has a large space, determine the location of the data file in the perfstat tablespace $ df-g 8. The table is empty. SQL> create tablespace perfstat datafile '/oracle/product/10.2.0/db_1/dbs/perfstat. dbf 'size 500 M; -- tablespace is used to store data collected during snapshot 9. Script SQL required to create Statspack> @: /oracle/product/10.2.0/db_1/rdbms/admin/spcreate. SQL -- "/oracle/product/10.2.0/db_1/rdbms/admin/" run the script in the script directory, enter perfstat_password, default_tablespace, temporary_tablespace www.2cto.com 10, and test whether the script is successful, generate a snapshot of the Database SQL> execute statspack. snap 11. Generate a snapshot SQL> execute stats Pack. snap 12. Take the Statspack Report SQL for the time period between two snapshots> @/oracle/product/10.2.0/db_1/rdbms/admin/spreport. SQL inputs the start and end points for generating the Statspack report. As shown above, two snapshots are generated. Enter begin_snap: 1 and end_snap: 2. enter the name of the generated snapshot (such as 1.txt). -- Note: If no location is specified for the report, the report is in the path 13 when sqlplus is started, and a Statspack snapshot is regularly generated using the job to collect data (one snapshot is generated by default) SQL> @/oracle/product/10.2.0/db_1/rdbms/admin/spauto. SQL 14. Delete historical data select max (snap_id) from stats $ snapshot; -- delete the corresponding data in the stats $ snapshot table. The corresponding data will also be deleted from stats $ snapshot where snap_id <3; you can also execute sptrunc. note: 1. After generating 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 that generates the Statspack snapshot: SQL> execute dbms_job.remove ('41') 3. If you are running spcreate. if an error occurs in the SQL script, run spdrop. the SQL script deletes the created object and then executes spcreate. SQL 4. Run spcreate. after the SQL script, you can find the relevant lis file to view the installation information (in the default path of sqlplus) and other related scripts. 1. spuexp. par script -- dmp used to export statpack Report-related data, as shown in the following figure: exp userid = perfstat/fyzh parfile = spuexp. par Note: spuexp. the par file must be in the current path of sqlplus login. If the login sqlplus path is C: \ Documents ents and Settings \ Administrator, then spuexp. the par is under this directory, otherwise the message "LRM-00109: Unable to open the parameter file 'spuexp. par' "2. sprepsql. SQL script -- used to generate an SQL Report based on the given SQL hash value to adjust the STATSPACK collection threshold. There are two types of collection options: level ): control the threshold of data collection type (threshold): set the threshold of collected data. 1. level Statspack has three snapshot levels. The default value is 5a. level 0: general performance statistics. including wait events, system events, system statistics, rollback segment statistics, row cache, SGA, session, lock, buffer pool statistics, and so on. b. level 5: Add SQL statements. in addition to all content of level0, it also includes SQL statement collection, and the collection results are recorded in stats $ SQL _summary. c. level 10: Add sub-lock statistics. includes all content of level5. the attached sub-locks are stored in stats $ lathc_children. exercise caution when using this level. We recommend that you use this level under the guidance of Oracle support. you can use the statspack package to modify the default SQL> execute statspack. snap (I _snap_level => 0, I _modify_parameter => 'true'); if you only 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. the snapshot threshold is only applicable to the SQL statements obtained from 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 obtained, so 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 (the default value is 100) disk_reads_tn this is the number of disk reads executed by SQL statements (the default value is 1000)
Parse_calls_th this is the number of resolution calls executed by SQL statements (the default value is 1000) buffer_gets_th this is the number of buffer obtained by SQL statement execution (the default value is 10000) A record is generated when any of the above thresholds is exceeded. call statspack. modify_statspack_parameter: SQL> execute statspack. modify_statspack_parameter (I _buffer_gets_th => 100000, I _disk_reads_th => 100000 );