Top 100+ Statspack Interview Questions And Answers
Question 1. What Is A Statspack?
Answer :
Statspack is the abbreviation for statistics package. The statspack consists of a group of SQL, PL/SQL and SQL*Plus scripts. The statspack lets you gather, save, evaluate, format and examine overall performance statistics for an Oracle instance.
With the statspack, you may, as an instance, decide the cause of performance troubles or determine and quantify the consequences of profile parameter adjustments. Refer to the segment about standard usage eventualities for more statistics.
Question 2. Where Are The Statspack Scripts Located?
Answer :
The statspack scripts are installed mechanically in the $ORACLE_HOME/rdbms/admin directory (Unix) or the %ORACLE_HOMEp.Crdbms admin directory (Windows) whilst you set up the Oracle9i database software program.
The maximum important scripts are:
spcreate.Sq.: Installs the statspack;
spdrop.Sq. : Uninstalls the statspack;
spauto.Square : Automates the gathering of facts;
spreport.Square: Creates a performance file;
spdoc.Txt : Provides documentation for the statspack;
spcreate.Sq. And spdrop.Square should be finished with the SYSDBA privilege.
Spauto.Sq. And spreport.Sq. Have to be finished beneath the PERFSTAT user.
Oracle DBA Interview Questions
Question three. Where Can I Find More Information About The Statspack?
Answer :
- Oracle9i Database Performance Tuning Guide and Reference
Chapter 21: Using Statspack
- Statistics Package (Statspack) README: spdoc.Txt
This readme is positioned underneath:
$ORACLE_HOME/rdbms/admin spdoc.Txt (UNIX) or
%ORACLe_HOMEp.Crdbms admin spdoc.Txt (Windows)
Question 4. For Which Oracle Releases Is The Statspack Available?
Answer :
The statspack is available as of Oracle 8i (8.1.6) and was better with Oracle9i. This be aware handiest refers to Oracle9i Release 2 (9.2.0).
Oracle DBA Tutorial
Question five. What Are The Advantages Of The Statspack Compared With Utlbstat/utlestat?
Answer :
The facts accrued with the aid of the statspack is extra massive and is stored in tables inside the Oracle database so you can use it later to diagnose overall performance troubles or to create fashion analyses. The universal diagnosis generated by means of the statspack consists of an overview over the instance fame (instance fitness), a load profile, the main SQL statements with the very best useful resource intake and records about queues, activities and profile parameters.
Linux Interview Questions
Question 6. Should I Install The Statspack In Its Own Tablespace?
Answer :
Yes, we advise which you set up the statspack in a separate tablespace known as PERFSTAT. You ought to create the tablespace as follows:
$sqlplus
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 simplest helps the creation of records tablespaces for SAP data, however no longer for non-SAP information. Therefore, you must use sqlplus, not brspace, to create the PERFSTAT tablespace.
Question 7. What User Do I Use To Execute The Statspack?
Answer :
When you put in the statspack, the PERFSTAT statspack database user is created mechanically 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 data, write the gathered information to the database and evaluate the records for the duration of the introduction of overall performance reviews under this consumer.
Linux Tutorial Windows Administration Interview Questions
Question 8. How Much Memory Is Required For The Statspack?
Answer :
At the start, about 100 MB are required for the installation.
The future space necessities of the statspack rely on how regularly you're producing snapshots, the dimensions of the database and the dimensions of the example. In addition, they're additionally inspired via the image stage which determines the amount of facts this is accrued in a photo. Therefore, we cannot provide you with any popular recommendations approximately the dimensions of the statspack. We only propose which you have a look at the enlargement of the statspack, specially if you are generating snapshots automatically, so you could make an estimate about destiny area requirements.
Question nine. How Do I Install The Statspack?
Answer :
The background set up proceeds as follows:
$sqlplus
SQL>connect / as sysdba
SQL>outline default tablespace='PERFSTAT'
SQL>define temporary tablespace='PSAPTEMP'
SQL>outline perfstat password=perfstat
SQL>@?/rdbms/admin/spcreate.Square
The PERFSTAT tablespace should have already got been created.
SYSTEM isn't allowed as a default tablespace.
Interactive set up:
$sqlplus
SQL>join / as sysdba
SQL>@?/rdbms/admin/spcreate.Square
The interactive installation calls for which you input the password, the default tablespace as well as the transient tablespace of the PERFSTAT database person.
The execution of the spcreate.Sq. Script executes three scripts one after the other (spcusr.Sq., spctab.Sq., spcpkg.Sq.). The system creates three log documents (spcusr.Lis, spctab.Lis, spcpkg.Lis) within the present day listing. You ought to take a look at these for the occurrence of errors at some stage in the installation. After that, you could delete them.
After you put in the statspack, we propose that you alternate the password for the PERFSTAT person.
SQL>regulate consumer perfstat identified through
SQL Interview Questions
Question 10. How Do I Uninstall The Statspack?
Answer :
Step 1: Delete the entered statspack jobs -
Log on as the person underneath which the statspack jobs run (generally PERFSTAT):
SQL>join PERFSTAT/
SQL>choose task, what from user_jobs;
This displays a listing of the jobs which are currently entered below the user PERFSTAT.
You can delete those jobs using DBMS_JOB.REMOVE
SQL>connect PERFSTAT/
Execute the subsequent SQL assertion for all jobs you want to delete that have the print activity quantity :
SQL>execute dbms_job.Remove( <job> );
Step 2: Delete the PERFSTAT objects and the PERFSTAT schema -
SQL>connect / as sysdba
SQL>@?/rdbms/admin/spdrop
As a result, the tables of the PERFSTAT person and, after that, the consumer PERFSTAT are deleted.
Step three: Delete the PERFSTAT tablespace -
Since the PERFSTAT tablespace became created specifically for the PERFSTAT user and the statspack statistics, it ought to be empty once you have deleted the PERFSTAT consumer. You can test this the use of
SQL>pick * from dba_segments in which tablespace_name = 'PERFSTAT';
If the tablespace is empty, you may delete it.
Question eleven. Will The Performance Of The Sap System Be Impeded If I Install The Statspack Or Generate Snapshots?
Answer :
Installing the statspack in a separate tablespace using a committed database person means that the statspack is kept completely cut loose the SAP schema. You can install, configure or uninstall the statspack at some point of SAP operation with out affecting the SAP gadget. The quantity of statspack data must generally via small with regards to the overall size of the database. The series and analysis of statistical statistics has only a minimum impact on device overall performance, on account that best the ones records that are contained inside the memory (SGA) are being accessed.
The installation and use of the statspack is therefore largely impartial of and transparent for the SAP System.
Solaris Interview Questions
Question 12. Are There Any Special Issues That I Need To Be Aware Of When I Install The Statspack In The Sap Environment?
Answer :
Apart from the tablespace naming convention all through creation with brspace, there are not any SAP specifications.
Oracle DBA Interview Questions
Question 13. Is The Statspack Integrated In Any Form In Sap Tools?
Answer :
No, now not so far.
Question 14. Do I Have To Set Any Oracle Profile Parameters?
Answer :
Yes, you have to set the subsequent parameters to the values particular to make certain that you get hold of useful data:
SQL>display parameter facts
statistics_level=TYPICAL
timed_os_statistics=0
timed_statistics=TRUE
To automate the technology of statspack snapshots, you should set the parameter job_queue_processes > zero.
Question 15. What Is A Snapshot?
Answer :
The use of the statspack to carry out a once-off series of performance information is called a photograph, this is, a picture of the contemporary system reputation. You can use snapshots to create a performance record.
IBM AIX 7 Administration Interview Questions
Question sixteen. How Do I Generate A Snapshot?
Answer :
$sqlplus
SQL>connect perfstat/perfstat
SQL>execute statspack.Snap;
Question 17. Can I Generate Snapshots Automatically?
Answer :
Check the documents for mistakes in the course of the set up.
Sqlplus
SQL>connect perfstat/perfstat
SQL>@?/rdbms/admin/spauto.Sq.
Prerequisite: job_queue_processes > 0
This sets up an automated activity in the database that generates a picture each hour.
Data modeling Interview Questions
Question 18. What Are Snapshot Levels?
Answer :
There are exceptional sorts of photograph levels. A photo level determines which performance records is amassed. The higher the extent, the extra information is accrued. The default cost of five is commonly sufficient. For RAC, you must generate snapshots with at the least level 7.
Temporarily converting the snapshot stage (for one snapshot best):
SQL>execute STATSPACK.SNAP(i_snap_level=>7);
Changing the photo level and saving it as a new default:
SQL>execute statspack.Modify_statspack_parameter(i_snap_level=>7);
For simultaneous generation of a photo:
SQL>execute STATSPACK.SNAP(i_snap_level=>7,
i_modify_parameter= >'authentic');
For information concerning the individual photo levels, see spdoc.Txt.
Linux Interview Questions
Question 19. How Do I Create A Performance Report?
Answer :
$sqlplus
SQL>connect perfstat/perfstat
SQL>@?/rdbms/admin/spreport.Square
The performance file calculates differences inside the statistical numbers of two snapshots (very last image minus the begin photograph). To make certain that those calculated values may be usefully interpreted, the instance among the 2 snapshots must not were stopped. The purpose for this is that a photograph determines its figures from the V$ tables, which can be reset to 0 every time an example starts offevolved.
Question 20. Are There Any Special Features In Oracle Real Application Clusters (rac)?
Answer :
In popular, we advocate that you set the photograph stage to the fee 7 inside the case of RAC. This method that the maximum closely loaded segments are recognized at phase level. These records assist you to localize hot spots.
To generate a image in an RAC surroundings, you need to log on to the RAC example for that you need to generate records.
To set up automated generation of snapshots in an RAC surroundings with the spauto.Square script for each RAC instance, you must run this script once on each example.
To create an overall performance record in an RAC surroundings, you ought to log on to the RAC instance for which you want to generate a performance document.
Oracle Performance Tuning Interview Questions
Question 21. What Are The Typical Scenarios For Using The Statspack?
Answer :
To check whether tuning measures have the anticipated impact.
To check the consequences of adjustments within the configuration ( profile parameters, tablespace layout).
To take a look at whether or not a better workload calls for an adjustment of profile parameters.
To gain comparison metrics for the overall performance behavior earlier than a scheduled exchange within the machine (new platform, new storage, software program improve, new Oracle release).
To check machine person court cases about bad reaction instances and to decide the reasons for the terrible performance.
To understand trends, that is, sluggish changes over an extended length, for example, an increasing workload, an increasing I/O load or an growing CPU workload
To decide load peaks, bottlenecks and their causes.
To determine the SQL statements with the best useful resource intake (top SQL).
