4 avg. rating (80% score) - 5879 votes
Are you commendable enough in Oracle Database? Do you have a certificate or degree in Oracle Database then log onto to www.wisdomjobs.com. Stats pack is a set of performance monitoring and reporting utilities provided by Oracle. It is a set of SQL, PL/SQL and SQL plus scripts that allow the collection, automation, storage and viewing of performance data. Stats pack stores the performance statistics permanently in Oracle tables which can later be used for reporting and analysis. It stores snapshots of system statistics overtime allowing greater accuracy and flexibility.It is a diagnosis tool for instance wide performance problems, it also supports application tuning activities by providing data which identifies high load SQL statements. So place yourself as Oracle Performance Tuning Expert, Technical Specialist, Oracle Database Administrator, Software Professional by looking into Stats pack job interview question and answers given.
Statspack is the abbreviation for statistics package. The statspack consists of a collection of SQL, PL/SQL and SQL*Plus scripts. The statspack helps you to collect, store, compare, format and analyze performance data for an Oracle instance.
With the statspack, you can, for example, determine the cause of performance problems or determine and quantify the effects of profile parameter changes. Refer to the section about typical usage scenarios for more information.
The statspack scripts are installed automatically in the $ORACLE_HOME/rdbms/admin directory (Unix) or the %ORACLE_HOME%rdbms admin directory (Windows) when you install the Oracle9i database software.
The most important scripts are:
spcreate.sql and spdrop.sql must be executed with the SYSDBA privilege.
spauto.sql and spreport.sql should be executed under the PERFSTAT user.
- Oracle9i Database Performance Tuning Guide and Reference
Chapter 21: Using Statspack
- Statistics Package (Statspack) README: spdoc.txt
This readme is located under:
$ORACLE_HOME/rdbms/admin spdoc.txt (UNIX) or
%ORACLe_HOME%rdbms admin spdoc.txt (Windows)
The statspack is available as of Oracle 8i (8.1.6) and was enhanced with Oracle9i. This note only refers to Oracle9i Release 2 (9.2.0).
The data collected by the statspack is more extensive and is saved in tables in the Oracle database so that you can use it later to diagnose performance problems or to create trend analyses. The overall diagnosis generated by the statspack includes an overview over the instance status (instance health), a load profile, the main SQL statements with the highest resource consumption and information about queues, events and profile parameters.
Yes, we recommend that you install the statspack in a separate tablespace called PERFSTAT. You should create the tablespace as follows:
SQL>CREATE TABLESPACE "PERFSTAT"
DATAFILE '<sap data>/perfstat 1/PERFSTAT.data1'
SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE 2000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
brspace only supports the creation of data tablespaces for SAP data, but not for non-SAP data. Therefore, you should use sqlplus, not brspace, to create the PERFSTAT tablespace.
When you install the statspack, the PERFSTAT statspack database user is created automatically with the minimum access authorizations for the Oracle dictionary (V$-Views). The statspack and all required tables, indexes, and so on belong to the PERFSTAT user. You collect the statistics, write the collected data to the database and evaluate the data during the creation of performance reports under this user.
At the beginning, approximately 100 MB are required for the installation.
The future space requirements of the statspack depend on how often you are generating snapshots, the size of the database and the size of the instance. In addition, they are also influenced by the snapshot level which determines the amount of data that is collected in a snapshot. Therefore, we cannot give you any general recommendations about the size of the statspack. We only recommend that you observe the expansion of the statspack, especially if you are generating snapshots automatically, so that you can make an estimate about future space requirements.
The background installation proceeds as follows:
SQL>connect / as sysdba
SQL>define default tablespace='PERFSTAT'
SQL>define temporary tablespace='PSAPTEMP'
SQL>define perfstat password=perfstat
The PERFSTAT tablespace must already have been created.
SYSTEM is not allowed as a default tablespace.
SQL>connect / as sysdba
The interactive installation requires that you enter the password, the default tablespace as well as the temporary tablespace of the PERFSTAT database user.
The execution of the spcreate.sql script executes three scripts one after the other (spcusr.sql, spctab.sql, spcpkg.sql). The system creates three log files (spcusr.lis, spctab.lis, spcpkg.lis) in the current directory. You should check these for the occurrence of errors during the installation. After that, you can delete them.
After you install the statspack, we recommend that you change the password for the PERFSTAT user.
SQL>alter user perfstat identified by
Step 1: Delete the entered statspack jobs -
Log on as the user under which the statspack jobs run (usually PERFSTAT):
SQL>select job, what from user_jobs;
This displays a list of the jobs that are currently entered under the user PERFSTAT.
You can delete these jobs using DBMS_JOB.REMOVE
Execute the following SQL statement for all jobs you want to delete that have the print job number :
SQL>execute dbms_job.remove( <job> );
Step 2: Delete the PERFSTAT objects and the PERFSTAT schema -
SQL>connect / as sysdba
As a result, the tables of the PERFSTAT user and, after that, the user PERFSTAT are deleted.
Step 3: Delete the PERFSTAT tablespace -
Since the PERFSTAT tablespace was created specifically for the PERFSTAT user and the statspack data, it should be empty once you have deleted the PERFSTAT user. You can check this using
SQL>select * from dba_segments where tablespace_name = 'PERFSTAT';
If the tablespace is empty, you can delete it.
Installing the statspack in a separate tablespace using a dedicated database user means that the statspack is kept completely separate from the SAP schema. You can install, configure or uninstall the statspack during SAP operation without affecting the SAP system. The amount of statspack data should generally by small in relation to the overall size of the database. The collection and analysis of statistical data has only a minimal effect on system performance, since only those statistics that are contained in the memory (SGA) are being accessed.
The installation and use of the statspack is therefore largely independent of and transparent for the SAP System.
Apart from the tablespace naming convention during creation with brspace, there are no SAP specifications.
No, not up to now.
Yes, you must set the following parameters to the values specified to ensure that you receive useful statistics:
SQL>show parameter statistics
To automate the generation of statspack snapshots, you must set the parameter job_queue_processes > 0.
The use of the statspack to perform a once-off collection of performance data is called a snapshot, that is, a snapshot of the current system status. You can use two snapshots to create a performance report.
Check the files for errors during the installation.
Prerequisite: job_queue_processes > 0
This sets up an automatic job in the database that generates a snapshot every hour.
There are different types of snapshot levels. A snapshot level determines which performance data is collected. The higher the level, the more data is collected. The default value of 5 is generally sufficient. For RAC, you should generate snapshots with at least level 7.
Temporarily changing the snapshot level (for one snapshot only):
Changing the snapshot level and saving it as a new default:
For simultaneous generation of a snapshot:
For details regarding the individual snapshot levels, see spdoc.txt.
The performance report calculates differences in the statistical numbers of two snapshots (final snapshot minus the start snapshot). To ensure that these calculated values can be usefully interpreted, the instance between the two snapshots must not have been stopped. The reason for this is that a snapshot determines its figures from the V$ tables, which are reset to 0 each time an instance starts.
In general, we recommend that you set the snapshot level to the value 7 in the case of RAC. This means that the most heavily loaded segments are recognized at segment level. These statistics help you to localize hot spots.
To generate a snapshot in an RAC environment, you must log on to the RAC instance for which you want to generate statistics.
To set up automatic generation of snapshots in an RAC environment with the spauto.sql script for each RAC instance, you must run this script once on each instance.
To create an performance report in an RAC environment, you must log on to the RAC instance for which you want to generate a performance report.
Statspack Related Interview Questions
|Oracle DBA Interview Questions||Linux Interview Questions|
|Windows Administration Interview Questions||SQL Interview Questions|
|Solaris Interview Questions||IBM AIX 7 Administration Interview Questions|
|Data modeling Interview Questions||Oracle Performance Tuning Interview Questions|
|Oracle DBA Troubleshooting Interview Questions||Oracle Order Management Interview Questions|
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.