Oracle statspack install 11g

Also, note that the statsrep. By using the statsauto. The statsauto. You can change this as follows for different sample times. There are 1, minutes in a day, and you can use this figure to adjust the execution times.

Hence, if we want a snapshot every ten minutes we would issue the following command:. For example, if you have noticed that a performance problem happens every day between p. For normal use, you probably want to accept the hourly default and execute a snapshot every hour. Below is the standard output from running the statsauto. Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise.

All legitimate Oracle experts publish their Oracle qualifications. Oracle technology is changing and we strive to update our BC Oracle support information. The Statspack saved all the performance statistics permanently in the tables. The table data is used for reporting and analysis later. The Statspack reports collected data can be analyzed, which include a database instance health and load a summary page, high resource-consuming SQL statements, and the traditional wait events and initialization parameters.

A minimum of 64 MB space is required for the installation of statspack which is used for creating tables and indexes. In other words, you can say that the amount of space required by the statspack package and its depends on the recap of snapshots, the size of the DB and instance, and the range of data collected, which can be configured. It is therefore difficult to provide unprivileged storage clauses and database space utilization predictions that are accurate at each site.

Using the below query, you can check statspack is installed or not. The result if statspack installed :. The best method to gather snapshots is to automate the collection at regular intervals.

You have the following options:. SQL , which schedules a snapshot every hour, on the hour. You might want to schedule snapshots at regular times each day to reflect your system's OLTP or batch peak loads. For example, you could take snapshots at 9 a. SQL script once on each instance in the cluster.

After snapshots are taken, you can generate performance reports. The Statspack package includes two reports. It is not correct to specify begin and end snapshots where the begin snapshot and end snapshot were taken from different instance startups. In other words, the instance must not have been shutdown between the times that the begin and end snapshots were taken.

This is necessary because the database's dynamic performance tables, which Statspack queries to gather the data, reside in memory. Hence, shutting down the database resets the values in the performance tables to 0.

Because Statspack subtracts the begin-snapshot statistics from the end-snapshot statistics, the resulting output is invalid. If begin and end snapshots taken between shutdowns are specified in the report, then the report shows an appropriate error to indicate this. Because data gathering is separate from report production, you have flexibility to base a report on any data points you select.

For example, as DBA you might want to use the supplied automation script to automate data collection every hour, on the hour.

If, at some later point, a performance issue arose that might be better investigated by looking at a three-hour data window, all you have to do is specify the required start point and end point when running the report. In an Oracle Real Application Clusters environment, you must connect to the instance on which you want to report.

The blank lines thus identify begin and end snapshots that cannot be used together when running a Statspack report. Example shows the SQL commands to run the report and an example of the partial report output. When you examine the instance report, you often find high-load SQL statements that you want to examine more closely.

SQL , displays statistics, the complete SQL text, and if a level six snapshot has been taken , information on any SQL plan s associated with that statement.

The SQL statement to be reported on is identified by a hash value, which is a numerical representation of the statement's SQL text. The hash value for each statement is displayed for each statement in the SQL sections of the instance report.

SQL script. Both the snapshot level and the thresholds specified affect the amount of data Statspack captures. You can change the amount of information gathered by specifying a different snapshot level.

The higher the snapshot level, the more data is gathered. The default level set at installation is level 5. For typical usage, level 5 snapshot is effective on most sites.

There are certain situations when using a level 6 snapshot is beneficial. These include the following:. There are other parameters you can configure, in addition to the snapshot level. These parameters are used as thresholds when collecting data on SQL statements; data is captured on any SQL statements that breach the specified thresholds. You can change the default parameters used for taking snapshots so that they are tailored to the instance's workload.

To temporarily use a snapshot level or threshold that is different from the instance's default snapshot values, you specify the required threshold or snapshot level when taking the snapshot. This value is used only for the immediate snapshot taken; the new value is not saved as the default.

These thresholds are used for all subsequent snapshots. Only the snapshot taken at that point uses the specified values. Any level greater than 0 collects general performance statistics, such as wait statistics, system events, system statistics, rollback segment data, row cache, SGA, background events, session events, lock statistics, buffer pool statistics, and parent latch statistics. This level includes all statistics gathered in the lower levels, as well as performance data on SQL statements with high resource usage.

The larger the shared pool, the longer it takes to complete the snapshot. SQL statements gathered by Statspack are those that exceed one of six predefined threshold parameters:. The values of each of these threshold parameters are used when deciding which SQL statements to collect.

If a SQL statement's resource usage exceeds any one of these threshold values, then it is captured during the snapshot. This level includes all statistics gathered in the lower levels, as well a SQL plans and plan usage data for each of the high-resource SQL statements captured. A level 6 snapshot gathers valuable information for determining whether the execution plan used for a SQL statement has changed.

Therefore, level 6 snapshots should be used whenever a plan might have changed. To gather the plan for a SQL statement, the statement must be in the shared pool at the time the snapshot is taken, and it must exceed one of the SQL thresholds. To gather plans for all statements in the shared pool, specify the executions threshold to be zero 0 for those snapshots. This level includes all statistics gathered in the lower levels, and additionally gathers the performance data on highly used segments.

RAC specific segment level statistics are also captured with level 7. A level 7 snapshot gathers information which determines what segments are more heavily accessed and contended.