Performance Oracle 8.1.6
Hello to all,
although I have many books of Oracle, I do not have time in order to read them!
The script protcat.sql (?) creates tables in order to control the performance of an Oracle database. I would want to know as they work and as I can interpret their information. Short explanation serves me one in order to understand and in order to resolve the problems of performance of mine database Oracle 8.1.6.
Thanks and excused my terrible English.
Ivan.
although I have many books of Oracle, I do not have time in order to read them!
The script protcat.sql (?) creates tables in order to control the performance of an Oracle database. I would want to know as they work and as I can interpret their information. Short explanation serves me one in order to understand and in order to resolve the problems of performance of mine database Oracle 8.1.6.
Thanks and excused my terrible English.
Ivan.
ASKER
Hi,
I have need of information generates them and tools in order to improve the performance and moreover one explanation of the tools statspak,
Thank you.
Ivan.
I have need of information generates them and tools in order to improve the performance and moreover one explanation of the tools statspak,
Thank you.
Ivan.
StatsPack was created in response to a need for more relevant and more extensive statistical reporting beyond what was available via UTLBSTAT/UTLESTAT reports. Further, this information can be stored permanently in the database instance itself so that historical data is always available for comparison and diagnosis.
Statspack has been available since version 816, but can be installed on 806 and above. Snapshots created using older versions of statspack can usually be read using newer versions of Statspack although the newer features will not be available.
See the following notes for information on installing, configuring snapshots, and generating reports:
- Installing and Configuring StatsPack Package
- Gathering a StatsPack snapshot
- Creating a StatsPack performance report
- FAQ- StatsPack Complete Reference
Timed_statistics must be set to true prior to the creation of a snapshot. If it is not, the data within statspack will not be relevant. You can tell if timed_statistics was not set by looking at the total times columns in the report. If these are zero then timed_statistics was not set.
Snapshots during which the instance was recycled will not contain accurate information and should not be included in a statspack report.
In general, we suggest that snapshots intervals be 15 minutes in length. This allows fine-grained reporting when hangs are suspected/detected. The snapshots can also be combined into hourly reports for general performance tuning.
When a value is too large for the statspack field it will be represented by a series of pound signs such as #######. Should this occur and you need to see the value in the field you will need to decrease the number of snapshots in the report until the field can be read. Should there only be one snapshot in the report, then you will need to decrease the snapshot interval.
Profiles created using statspack information are quite helpful in determining long-term trends such as load increases, usage trends, resource consumption, latch activity, etc. It is especially important that a DBA know these things and be able to demonstrate changes in them that necessitate hardware improvements and load balancing policies. This document will describe the main sections of an statspack report, which will help to understand what information is available to diagnose and resolve performance tuning problems. Some of the sections of the statspack report may contain different information based on the Statspack release that was used to generate the report. This document will also indicate these changes for the different sections.
Summary Information
The summary information begins with the identification of the database on which the statspack report was run along with the time interval of the statspack report. Here is the 8i instance information:
STATSPACK report for
DB Name DB Id Instance Inst Num Release OPS Host
------------ ----------- ------------ -------- ----------- --- ------------
PHS2 975244035 phs2 2 8.1.7.2.0 YES leo2
Snap Id Snap Time Sessions
------- ------------------ --------
Begin Snap: 100 03-Jan-02 08:00:01 #######
End Snap: 104 03-Jan-02 09:00:01 #######
Elapsed: 60.00 (mins)
The database name, id, instance name, instance number if OPS is being utilized, Oracle binary release information, host name and snapshot information are provided.
Note that here the number of sessions during the snapshot was too large for the sessions field and so the overflow symbol is displayed.
Here is an example of an 806 instance using statspack:
STATSPACK report for
DB Name DB Id Instance Inst Num Release OPS Host
---------- ----------- ---------- -------- ---------- ---- ----------
GLOVP 1409723819 glovp 1 8.0.6.1.0 NO shiver
Snap Length
Start Id End Id Start Time End Time (Minutes)
-------- -------- -------------------- -------------------- -----------
454 455 07-Jan-03 05:28:20 07-Jan-03 06:07:53 39.55
Here is the 9i instance information. Note that the OPS column is now entitled 'Cluster' to accommodate the newer Real Applications Cluster (RAC) terminology and that the Cursors/Session and Comment columns have been added.
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
ETSPRD7 1415901831 etsprd7a 1 9.2.0.2.0 YES tsonode1
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 20 03-Jan-03 00:00:05 ####### .0
End Snap: 21 03-Jan-03 01:00:05 ####### .0
Elapsed: 60.00 (mins)
Instance Workload Information
Every statspack report start with a section that describes the instance's workload profile and instance metrics that may help to determine the instance efficiency.
- Instance cache information:
In the 8i report the buffer cache size can be determined by multiplying the db_block_buffers by the db_block_size.
Cache Sizes
~~~~~~~~~~~
db_block_buffers: 6400 log_buffer: 104857600
db_block_size: 32768 shared_pool_size: 150000000
In 9i this has been done for you. Std Block size indicates the primary block size of the instance.
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 704M Std Block Size: 8K
Shared Pool Size: 256M Log Buffer: 1,024K
Note that the buffer cache size is that of the standard buffer cache. If you have multiple buffer caches, you will need to calculate the others separately.
- Load profile Information:
The load profile information is next. It is identical in both 8i and 9i.
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 351,530.67 7,007.37
Logical reads: 5,449.81 108.64
Block changes: 1,042.0 8 20.77
Physical reads: 37.71 0.75
Physical writes: 134.68 2.68
User calls: 1,254.72 25.01
Parses: 4.92 0.10
Hard parses: 0.02 0.00
Sorts: 15.73 0.31
Logons: -0.01 0.00
Executes: 473.73 9.44
Transactions: 50.17
% Blocks changed per Read: 19.12 Recursive Call %: 4.71
Rollback per transaction %: 2.24 Rows per Sort: 20.91
Where:
. Redo size: This is the amount of redo generated during this report.
. Logical Reads: This is calculated as Consistent Gets + DB Block Gets = Logical Reads
. Block changes: The number of blocks modified during the sample interval
. Physical Reads: The number of requests for a block that caused a physical I/O.
. Physical Writes: The number of physical writes issued.
. User Calls: The number of queries generated
. Parses: Total of all parses: both hard and soft
. Hard Parses: Those parses requiring a completely new parse of the SQL statement. These consume both latches and shared pool area.
. Soft Parses: Not listed but derived by subtracting the hard parses from parses. A soft parse reuses a previous hard parse and hence consumes far fewer resources.
. Sorts, Logons, Executes and Transactions are all self explanatory
- Instance Efficiency Ratios:
Hit ratios are calculations that may provide information regarding different structures and operations in the Oracle instance. Database tuning never must be driven by hit ratios. They only provide additional information to understand how the instance is operating. For example, in a DSS systems a low cache hit ratio may be acceptable due the amount of recycling needed due the large volume of data accesed. So if you increase the size of the buffer cache based on this number, the corrective action may not take affect and you may be wasting memory resources.
See - THE COE PERFORMANCE METHOD , for further reference on how to approach a performance tuning problem.
This section is identical in 8i and 9i.
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.99 Redo NoWait %: 100.00
Buffer Hit %: -45.57 In-memory Sort %: 97.55
Library Hit %: 99.89 Soft Parse %: 99.72
Execute to Parse %: -1.75 Latch Hit %: 99.11
Parse CPU to Parse Elapsd %: 52.66 % Non-Parse CPU: 99.99
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 42.07 43.53
% SQL with executions>1: 73.79 75.08
% Memory for SQL w/exec>1: 76.93 77.64
It is possible for both the 'buffer hit ratio' and the 'execute to parse' ratios to be negative. In the case of the buffer hit ration, the buffer cache is too small and the data in is being aged out before it can be used so it must be retrieved again. This is a form of thrashing which degrades performance immensely.
The execute to parse ratio can be negative when the number of parses is larger than the number of executions. The Execute to Parse ratio is determined by the following formula:
100 * (1 - Parses/Executions) = Execute to Parse
Here this becomes:
100 * (1 - 42,757 / 42,023 ) = 100 * (1 - 1.0175) = 100* -0.0175 = -1.75
This can be caused by the snapshot boundary occurring during a period of high parsing so that the executions have not occurred before the end of the snapshot. Check the next snapshot to see if there are enough executes to account for the parses in this report.
Another cause for a negative execute to parse ratio is if the shared pool is too small and queries are aging out of the shared pool and need to be reparsed. This is another form of thrashing which also degrades performance tremendously.
- Top 5 Events section:
This section shows the Top 5 timed events that must be considered to focus the tuning efforts. Before Oracle 9.2 this section was called "Top 5 Wait Events". It was renamed in Oracle 9.2 to "Top 5 Timed Events" to include the "CPU Time" based on the 'CPU used by this session'. This information will allow you to determine SQL tuning problems.
For further see the Statspack readme file called $ORACLE_HOME/rdbms/admin/s
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
--------------------------
db file sequential read 12,131,221 173,910 58.04
db file scattered read 93,310 86,884 29.00
log file sync 18,629 9,033 3.01
log file parallel write 18,559 8,449 2.82
buffer busy waits 304,461 7,958 2.66
Notice that in Oracle 9.2 references are made "Elapsed Time" rather than to "Wait Time". Also the "CPU Time" is included as part of the Top events section.
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
--------------------------
log file sync 3,223,927 32,481 64.05
CPU time 7,121 14.04
global cache open x 517,153 3,130 6.17
log file parallel write 985,732 2,613 5.15
KJC: Wait for msg sends to complete 568,061 1,794 3.54
--------------------------
Note that db file scattered and sequential read are generally the top wait events when the instance is tuned well and not OPS/RAC. Wait Events
Wait Events Information
The following section will describe in detail most of the sections provided in a statspack report.
- Foreground Wait Events:
Foreground wait events are those associated with a session or client process waiting for a resource. The 8i version looks like this:
Wait Events for DB: PHS2 Instance: phs2 Snaps: 100 -104
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
--------------------------
PX Deq: Execution Msg 15,287 6,927 1,457,570 953 694.9
enqueue 30,367 28,591 737,906 243 ######
direct path read 45,484 0 352,127 77 ######
PX Deq: Table Q Normal 7,185 811 241,532 336 326.6
PX Deq: Execute Reply 13,925 712 194,202 139 633.0
....
The 9.2 version is much the same but has different time intervals in the header.
Wait Events for DB: FUSION Instance: ecfsc2 Snaps: 161 -162
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
--------------------------
log file sync 3,223,927 1 32,481 10 1.0
global cache open x 517,153 777 3,130 6 0.2
log file parallel write 985,732 0 2,613 3 0.3
KJC: Wait for msg sends to c 568,061 34,529 1,794 3 0.2
- Background Wait Events:
Background wait events are those not associated with a client process. They indicate waits encountered by system and non-system processes. The output is the same for all the Oracle releases.
Background Wait Events for DB: PHS2 Instance: phs2 Snaps: 100 -104
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
--------------------------
latch free 88,578 32,522 18,341 2 ######
enqueue 319 230 5,932 186 14.5
row cache lock 4,941 0 2,307 5 224.6
control file parallel write 1,172 0 332 3 53.3
db file parallel write 176 0 67 4 8.0
log file parallel write 315 0 65 2 14.3
db file scattered read 137 0 62 5 6.2
LGWR wait for redo copy 66 10 47 7 3.0
Examples of background system processes are LGWR and DBWR. An example of a non-system background process would be a parallel query slave.
Note that it is possible for a wait event to appear in both the foreground and background wait events statistics. Examples of this are the enqueue and latch free events.
The idle wait events appear at the bottom of both sections and can generally safely be ignored. Typically these type of events keep record of the time while the clien is connected to the database but not requests are being made to the server.
- Notes Regarding Waitevents:
- The idle wait events associated with pipes are often a major source of concern for some DBAs. Pipe gets and waits are entirely application dependent. To tune these events you must tune the application generating them. High pipe gets and waits can affect the library cache latch performance. Rule out all other possible causes of library cache contention prior to focusing on pipe waits as it is very expensive for the client to tune their application.A list of most wait events used by the RDBMS kernel can be found in Appendix A of the Oracle Reference manual for the version being used.
Some wait events to watch:
- global cache cr request: (OPS) This wait event shows the amount of time that an instance has waited for a requested data block for a consistent read and the transferred block has not yet arrived at the requesting instance. See Note 157766.1 'Sessions Wait Forever for 'global cache cr request' Wait Event in OPS or RAC'. In some cases the 'global cache cr request' wait event may be perfectly normal if large buffer caches are used and the same data is being accessed concurrently on multiple instances. In a perfectly tuned, non-OPS/RAC database, I/O wait events would be the top wait events but since we are avoiding I/O's with RAC and OPS the 'global cache cr request' wait event often takes the place of I/O wait events.
- Buffer busy waits, write complete waits, db file parallel writes and enqueue waits: If all of these are in the top wait events the client may be experiencing disk saturation. See Note 155971.1 Resolving Intense and "Random" Buffer Busy Wait Performance Problems for troubleshooting tips.
- log file switch, log file sync or log switch/archive: If the waits on these events appears excessive check for checkpoint tuning issues.
SQL Information
The SQL that is stored in the shared pool SQL area (Library cache) is reported to the user via three different formats in 8i. Each has their own usefulness.
. SQL ordered by Buffer Gets
. SQL ordered by Physical Reads
. SQL ordered by Executions
9i has an additional section:
. SQL ordered by Parse Calls
- SQL ordered by Gets:
SQL ordered by Gets for DB: PHS2 Instance: phs2 Snaps: 100 -104
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100
Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
198,924 37,944 5.2 41.7 2913840444
select length from fet$ where file#=:1 and block#=:2 and ts#=:3
111,384 7 15,912.0 23.4 1714733582
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe
re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0
105,365 16 6,585.3 22.1 4111567099
CREATE TABLE "PHASE".:Q3236003("PID","C
","SCPOS3","SCPOS4","SCPOS
,"SCPOS10","SCPOS11","SCPO
POS16","SCPOS17","MCELL","
RSPCRED","MAILDATE","RSPTD
....
Statspack has been available since version 816, but can be installed on 806 and above. Snapshots created using older versions of statspack can usually be read using newer versions of Statspack although the newer features will not be available.
See the following notes for information on installing, configuring snapshots, and generating reports:
- Installing and Configuring StatsPack Package
- Gathering a StatsPack snapshot
- Creating a StatsPack performance report
- FAQ- StatsPack Complete Reference
Timed_statistics must be set to true prior to the creation of a snapshot. If it is not, the data within statspack will not be relevant. You can tell if timed_statistics was not set by looking at the total times columns in the report. If these are zero then timed_statistics was not set.
Snapshots during which the instance was recycled will not contain accurate information and should not be included in a statspack report.
In general, we suggest that snapshots intervals be 15 minutes in length. This allows fine-grained reporting when hangs are suspected/detected. The snapshots can also be combined into hourly reports for general performance tuning.
When a value is too large for the statspack field it will be represented by a series of pound signs such as #######. Should this occur and you need to see the value in the field you will need to decrease the number of snapshots in the report until the field can be read. Should there only be one snapshot in the report, then you will need to decrease the snapshot interval.
Profiles created using statspack information are quite helpful in determining long-term trends such as load increases, usage trends, resource consumption, latch activity, etc. It is especially important that a DBA know these things and be able to demonstrate changes in them that necessitate hardware improvements and load balancing policies. This document will describe the main sections of an statspack report, which will help to understand what information is available to diagnose and resolve performance tuning problems. Some of the sections of the statspack report may contain different information based on the Statspack release that was used to generate the report. This document will also indicate these changes for the different sections.
Summary Information
The summary information begins with the identification of the database on which the statspack report was run along with the time interval of the statspack report. Here is the 8i instance information:
STATSPACK report for
DB Name DB Id Instance Inst Num Release OPS Host
------------ ----------- ------------ -------- ----------- --- ------------
PHS2 975244035 phs2 2 8.1.7.2.0 YES leo2
Snap Id Snap Time Sessions
------- ------------------ --------
Begin Snap: 100 03-Jan-02 08:00:01 #######
End Snap: 104 03-Jan-02 09:00:01 #######
Elapsed: 60.00 (mins)
The database name, id, instance name, instance number if OPS is being utilized, Oracle binary release information, host name and snapshot information are provided.
Note that here the number of sessions during the snapshot was too large for the sessions field and so the overflow symbol is displayed.
Here is an example of an 806 instance using statspack:
STATSPACK report for
DB Name DB Id Instance Inst Num Release OPS Host
---------- ----------- ---------- -------- ---------- ---- ----------
GLOVP 1409723819 glovp 1 8.0.6.1.0 NO shiver
Snap Length
Start Id End Id Start Time End Time (Minutes)
-------- -------- -------------------- -------------------- -----------
454 455 07-Jan-03 05:28:20 07-Jan-03 06:07:53 39.55
Here is the 9i instance information. Note that the OPS column is now entitled 'Cluster' to accommodate the newer Real Applications Cluster (RAC) terminology and that the Cursors/Session and Comment columns have been added.
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
ETSPRD7 1415901831 etsprd7a 1 9.2.0.2.0 YES tsonode1
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 20 03-Jan-03 00:00:05 ####### .0
End Snap: 21 03-Jan-03 01:00:05 ####### .0
Elapsed: 60.00 (mins)
Instance Workload Information
Every statspack report start with a section that describes the instance's workload profile and instance metrics that may help to determine the instance efficiency.
- Instance cache information:
In the 8i report the buffer cache size can be determined by multiplying the db_block_buffers by the db_block_size.
Cache Sizes
~~~~~~~~~~~
db_block_buffers: 6400 log_buffer: 104857600
db_block_size: 32768 shared_pool_size: 150000000
In 9i this has been done for you. Std Block size indicates the primary block size of the instance.
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 704M Std Block Size: 8K
Shared Pool Size: 256M Log Buffer: 1,024K
Note that the buffer cache size is that of the standard buffer cache. If you have multiple buffer caches, you will need to calculate the others separately.
- Load profile Information:
The load profile information is next. It is identical in both 8i and 9i.
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 351,530.67 7,007.37
Logical reads: 5,449.81 108.64
Block changes: 1,042.0 8 20.77
Physical reads: 37.71 0.75
Physical writes: 134.68 2.68
User calls: 1,254.72 25.01
Parses: 4.92 0.10
Hard parses: 0.02 0.00
Sorts: 15.73 0.31
Logons: -0.01 0.00
Executes: 473.73 9.44
Transactions: 50.17
% Blocks changed per Read: 19.12 Recursive Call %: 4.71
Rollback per transaction %: 2.24 Rows per Sort: 20.91
Where:
. Redo size: This is the amount of redo generated during this report.
. Logical Reads: This is calculated as Consistent Gets + DB Block Gets = Logical Reads
. Block changes: The number of blocks modified during the sample interval
. Physical Reads: The number of requests for a block that caused a physical I/O.
. Physical Writes: The number of physical writes issued.
. User Calls: The number of queries generated
. Parses: Total of all parses: both hard and soft
. Hard Parses: Those parses requiring a completely new parse of the SQL statement. These consume both latches and shared pool area.
. Soft Parses: Not listed but derived by subtracting the hard parses from parses. A soft parse reuses a previous hard parse and hence consumes far fewer resources.
. Sorts, Logons, Executes and Transactions are all self explanatory
- Instance Efficiency Ratios:
Hit ratios are calculations that may provide information regarding different structures and operations in the Oracle instance. Database tuning never must be driven by hit ratios. They only provide additional information to understand how the instance is operating. For example, in a DSS systems a low cache hit ratio may be acceptable due the amount of recycling needed due the large volume of data accesed. So if you increase the size of the buffer cache based on this number, the corrective action may not take affect and you may be wasting memory resources.
See - THE COE PERFORMANCE METHOD , for further reference on how to approach a performance tuning problem.
This section is identical in 8i and 9i.
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.99 Redo NoWait %: 100.00
Buffer Hit %: -45.57 In-memory Sort %: 97.55
Library Hit %: 99.89 Soft Parse %: 99.72
Execute to Parse %: -1.75 Latch Hit %: 99.11
Parse CPU to Parse Elapsd %: 52.66 % Non-Parse CPU: 99.99
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 42.07 43.53
% SQL with executions>1: 73.79 75.08
% Memory for SQL w/exec>1: 76.93 77.64
It is possible for both the 'buffer hit ratio' and the 'execute to parse' ratios to be negative. In the case of the buffer hit ration, the buffer cache is too small and the data in is being aged out before it can be used so it must be retrieved again. This is a form of thrashing which degrades performance immensely.
The execute to parse ratio can be negative when the number of parses is larger than the number of executions. The Execute to Parse ratio is determined by the following formula:
100 * (1 - Parses/Executions) = Execute to Parse
Here this becomes:
100 * (1 - 42,757 / 42,023 ) = 100 * (1 - 1.0175) = 100* -0.0175 = -1.75
This can be caused by the snapshot boundary occurring during a period of high parsing so that the executions have not occurred before the end of the snapshot. Check the next snapshot to see if there are enough executes to account for the parses in this report.
Another cause for a negative execute to parse ratio is if the shared pool is too small and queries are aging out of the shared pool and need to be reparsed. This is another form of thrashing which also degrades performance tremendously.
- Top 5 Events section:
This section shows the Top 5 timed events that must be considered to focus the tuning efforts. Before Oracle 9.2 this section was called "Top 5 Wait Events". It was renamed in Oracle 9.2 to "Top 5 Timed Events" to include the "CPU Time" based on the 'CPU used by this session'. This information will allow you to determine SQL tuning problems.
For further see the Statspack readme file called $ORACLE_HOME/rdbms/admin/s
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
--------------------------
db file sequential read 12,131,221 173,910 58.04
db file scattered read 93,310 86,884 29.00
log file sync 18,629 9,033 3.01
log file parallel write 18,559 8,449 2.82
buffer busy waits 304,461 7,958 2.66
Notice that in Oracle 9.2 references are made "Elapsed Time" rather than to "Wait Time". Also the "CPU Time" is included as part of the Top events section.
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
--------------------------
log file sync 3,223,927 32,481 64.05
CPU time 7,121 14.04
global cache open x 517,153 3,130 6.17
log file parallel write 985,732 2,613 5.15
KJC: Wait for msg sends to complete 568,061 1,794 3.54
--------------------------
Note that db file scattered and sequential read are generally the top wait events when the instance is tuned well and not OPS/RAC. Wait Events
Wait Events Information
The following section will describe in detail most of the sections provided in a statspack report.
- Foreground Wait Events:
Foreground wait events are those associated with a session or client process waiting for a resource. The 8i version looks like this:
Wait Events for DB: PHS2 Instance: phs2 Snaps: 100 -104
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
--------------------------
PX Deq: Execution Msg 15,287 6,927 1,457,570 953 694.9
enqueue 30,367 28,591 737,906 243 ######
direct path read 45,484 0 352,127 77 ######
PX Deq: Table Q Normal 7,185 811 241,532 336 326.6
PX Deq: Execute Reply 13,925 712 194,202 139 633.0
....
The 9.2 version is much the same but has different time intervals in the header.
Wait Events for DB: FUSION Instance: ecfsc2 Snaps: 161 -162
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
--------------------------
log file sync 3,223,927 1 32,481 10 1.0
global cache open x 517,153 777 3,130 6 0.2
log file parallel write 985,732 0 2,613 3 0.3
KJC: Wait for msg sends to c 568,061 34,529 1,794 3 0.2
- Background Wait Events:
Background wait events are those not associated with a client process. They indicate waits encountered by system and non-system processes. The output is the same for all the Oracle releases.
Background Wait Events for DB: PHS2 Instance: phs2 Snaps: 100 -104
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
--------------------------
latch free 88,578 32,522 18,341 2 ######
enqueue 319 230 5,932 186 14.5
row cache lock 4,941 0 2,307 5 224.6
control file parallel write 1,172 0 332 3 53.3
db file parallel write 176 0 67 4 8.0
log file parallel write 315 0 65 2 14.3
db file scattered read 137 0 62 5 6.2
LGWR wait for redo copy 66 10 47 7 3.0
Examples of background system processes are LGWR and DBWR. An example of a non-system background process would be a parallel query slave.
Note that it is possible for a wait event to appear in both the foreground and background wait events statistics. Examples of this are the enqueue and latch free events.
The idle wait events appear at the bottom of both sections and can generally safely be ignored. Typically these type of events keep record of the time while the clien is connected to the database but not requests are being made to the server.
- Notes Regarding Waitevents:
- The idle wait events associated with pipes are often a major source of concern for some DBAs. Pipe gets and waits are entirely application dependent. To tune these events you must tune the application generating them. High pipe gets and waits can affect the library cache latch performance. Rule out all other possible causes of library cache contention prior to focusing on pipe waits as it is very expensive for the client to tune their application.A list of most wait events used by the RDBMS kernel can be found in Appendix A of the Oracle Reference manual for the version being used.
Some wait events to watch:
- global cache cr request: (OPS) This wait event shows the amount of time that an instance has waited for a requested data block for a consistent read and the transferred block has not yet arrived at the requesting instance. See Note 157766.1 'Sessions Wait Forever for 'global cache cr request' Wait Event in OPS or RAC'. In some cases the 'global cache cr request' wait event may be perfectly normal if large buffer caches are used and the same data is being accessed concurrently on multiple instances. In a perfectly tuned, non-OPS/RAC database, I/O wait events would be the top wait events but since we are avoiding I/O's with RAC and OPS the 'global cache cr request' wait event often takes the place of I/O wait events.
- Buffer busy waits, write complete waits, db file parallel writes and enqueue waits: If all of these are in the top wait events the client may be experiencing disk saturation. See Note 155971.1 Resolving Intense and "Random" Buffer Busy Wait Performance Problems for troubleshooting tips.
- log file switch, log file sync or log switch/archive: If the waits on these events appears excessive check for checkpoint tuning issues.
SQL Information
The SQL that is stored in the shared pool SQL area (Library cache) is reported to the user via three different formats in 8i. Each has their own usefulness.
. SQL ordered by Buffer Gets
. SQL ordered by Physical Reads
. SQL ordered by Executions
9i has an additional section:
. SQL ordered by Parse Calls
- SQL ordered by Gets:
SQL ordered by Gets for DB: PHS2 Instance: phs2 Snaps: 100 -104
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100
Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
198,924 37,944 5.2 41.7 2913840444
select length from fet$ where file#=:1 and block#=:2 and ts#=:3
111,384 7 15,912.0 23.4 1714733582
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe
re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0
105,365 16 6,585.3 22.1 4111567099
CREATE TABLE "PHASE".:Q3236003("PID","C
","SCPOS3","SCPOS4","SCPOS
,"SCPOS10","SCPOS11","SCPO
POS16","SCPOS17","MCELL","
RSPCRED","MAILDATE","RSPTD
....
ASKER
Increase to 500 points the question: I have need of one explanation "step by step" in order to install the statspack and on the methodology in order to interpret the report. I do not have much experience in this field (I have not still understood what are the latch) and therefore to understand the bases of these processes is for me a lot important.
Thanks and excused my terrible English.
Ivan.
Thanks and excused my terrible English.
Ivan.
Dorogoi drug Iwan!
Ok! I will give you instruction how to run statspack!
But this is the last step of your eforts.
You have to begin with simple measures to stabilize your DB.
1. Run statistics as user SYS. This will improve the optimizer planning.
CREATE OR REPLACE PROCEDURE compute_statistics IS
BEGIN
dbms_stats.gather_database
END compute_statistics;
/
execute compute_statistics;
2. Run simple scripts to check the size of the key RAM structures:
Rem Scripts to be run periodically
Rem as user sys from SQL*Plus
Set linesize 10000
Set pagesize 500
set trimspool on
Set heading on
Set echo on
set underline '-'
Rem DB_CACHE_SIZE
Rem Script 1
select round(((1-(sum(decode(name
'physical reads', value,0))/
(sum(decode(name, 'db block gets', value,0))+
(sum(decode(name, 'consistent gets', value, 0))))))*100),2)
|| '%' "Buffer Cache Hit Ratio"
from v$sysstat;
Rem Buffer Cache Hit Ratio > 90.00%
Rem SHARED_POOL_SIZE
Rem Script 2
select (1-(sum(getmisses)/sum(get
from v$rowcache;
rem should be value above 90%
Rem The unused part of the SHARED_POOL_SIZE
Rem Script 3
select name, bytes/1024/1024 "Size in MB"
from v$sgastat
where name='free memory';
Rem ROLLBACK_SEGMENTS
Rem Script 4
Select a. name, b.extents, b.rssize, b.xacts, b.waits,
b. gets, optsize, status
From v$rollname a, v$rollstat b
Where a.usn = b.usn;
Rem column "xacts" (which are active transactions) are continually above 1 for the RBS's, you will probably need to
Rem increase the number of rollback segments to avoid the possibility of contention.
Rem If the waits are greater than zero, and this is normal processing, there may also be a need to increase
Rem the number of rollback segments.
Rem From Performance Tuning - Now You are the V8 Expert
Rem Richard J. Niemiec, TUSC
rem Finding the values of ‘KEY’ INIT.ORA parameters
Rem Script 5
show sga;
select name, value
from v$parameter
where name in ('db_cache_size', 'db_block_size', 'shared_pool_size', 'sort_area_size',
'large_pool_size', 'java_pool_size', 'log_buffer', 'dbwr_io_slaves',
'rollback_segments', 'sort_area_retained_size',
'shared_pool_reserved_size
Rem Determine dictionary cache miss ratio:
Rem Script 6
select sum(gets) "Gets", sum(getmisses) "Misses",
(1 - (sum(getmisses) / (sum(gets) +
sum(getmisses))))*100 "HitRate"
from v$rowcache;
Rem should be > 90%
Rem Determine library cache hit ratio
Rem Script 7
select sum(pins) Executions, sum(pinhits) "Execution Hits",
((sum(pinhits) / sum(pins)) * 100) phitrat,
sum(reloads) Misses,
((sum(pins) / (sum(pins) + sum(reloads))) * 100) hitrat
from v$librarycache;
Rem If the hit ratio or reloads is high, increase the shared_pool_size INIT.ora parameter.
Rem Reloads indicate that statements that were once in memory now had to be reloaded because they were pushed
Rem out, whereas misses include statements that are loaded for the first time.
Rem How much memory is left for SHARED_POOL_SIZE:
Rem Script 8
col value for 999,999,999,999 heading 'Shared Pool Size'
col bytes for 999,999,999,999 heading 'Free Bytes'
select to_number(v$parameter.valu
(v$sgastat.bytes/v$paramet
from v$sgastat, v$parameter
where v$sgastat.name = 'free memory'
and v$parameter.name = 'shared_pool_size';
rem A better query
Rem Script 9
select sum(ksmchsiz) Bytes, ksmchcls Status
from x$ksmsp
group by ksmchcls;
Rem To see how fast the SGA gets using x$bh
Rem Script 10
select state, count(*)
from x$bh
group by state;
Rem STATE COUNT(*)
Rem --------- -----------------
Rem 0 371
Rem 1 429
Rem In the above result:
Rem Total DB_BLOCK_BUFFERS = 800
Rem Total that have been used = 429
Rem Total that have NOT been used = 371
Rem A better query
Rem Script 11
select decode(state,0, 'FREE', 1, decode(lrba_seq,0,'AVAILAB
3, 'BEING USED', state) "BLOCK STATUS", count(*)
from x$bh
group by decode(state,0,'FREE',1,de
'AVAILABLE','BEING USED'),3, 'BEING USED', state);
Rem BLOCK STATUS COUNT(*)
Rem AVAILABLE 779
Rem BEING USED 154
Rem FREE 167
Rem Finding the largest amount of physical reads by query
Rem Script 12
select disk_reads, sql_text
from v$sqlarea
where disk_reads > 10000
order by disk_reads desc;
Rem Finding the largest amount of logical reads by query
Rem Script 13
select buffer_gets, sql_text
from v$sqlarea
where buffer_gets > 200000
order by buffer_gets desc;
Rem Script 14
select * from v$version;
Rem Script 15
select * from v$option;
Rem Script 16
Rem List the logswitches of the 4 last days
select sequence#,to_char(first_ti
I am sure you will find using these basic simple scripts what causes troubles.
Ok! I will give you instruction how to run statspack!
But this is the last step of your eforts.
You have to begin with simple measures to stabilize your DB.
1. Run statistics as user SYS. This will improve the optimizer planning.
CREATE OR REPLACE PROCEDURE compute_statistics IS
BEGIN
dbms_stats.gather_database
END compute_statistics;
/
execute compute_statistics;
2. Run simple scripts to check the size of the key RAM structures:
Rem Scripts to be run periodically
Rem as user sys from SQL*Plus
Set linesize 10000
Set pagesize 500
set trimspool on
Set heading on
Set echo on
set underline '-'
Rem DB_CACHE_SIZE
Rem Script 1
select round(((1-(sum(decode(name
'physical reads', value,0))/
(sum(decode(name, 'db block gets', value,0))+
(sum(decode(name, 'consistent gets', value, 0))))))*100),2)
|| '%' "Buffer Cache Hit Ratio"
from v$sysstat;
Rem Buffer Cache Hit Ratio > 90.00%
Rem SHARED_POOL_SIZE
Rem Script 2
select (1-(sum(getmisses)/sum(get
from v$rowcache;
rem should be value above 90%
Rem The unused part of the SHARED_POOL_SIZE
Rem Script 3
select name, bytes/1024/1024 "Size in MB"
from v$sgastat
where name='free memory';
Rem ROLLBACK_SEGMENTS
Rem Script 4
Select a. name, b.extents, b.rssize, b.xacts, b.waits,
b. gets, optsize, status
From v$rollname a, v$rollstat b
Where a.usn = b.usn;
Rem column "xacts" (which are active transactions) are continually above 1 for the RBS's, you will probably need to
Rem increase the number of rollback segments to avoid the possibility of contention.
Rem If the waits are greater than zero, and this is normal processing, there may also be a need to increase
Rem the number of rollback segments.
Rem From Performance Tuning - Now You are the V8 Expert
Rem Richard J. Niemiec, TUSC
rem Finding the values of ‘KEY’ INIT.ORA parameters
Rem Script 5
show sga;
select name, value
from v$parameter
where name in ('db_cache_size', 'db_block_size', 'shared_pool_size', 'sort_area_size',
'large_pool_size', 'java_pool_size', 'log_buffer', 'dbwr_io_slaves',
'rollback_segments', 'sort_area_retained_size',
'shared_pool_reserved_size
Rem Determine dictionary cache miss ratio:
Rem Script 6
select sum(gets) "Gets", sum(getmisses) "Misses",
(1 - (sum(getmisses) / (sum(gets) +
sum(getmisses))))*100 "HitRate"
from v$rowcache;
Rem should be > 90%
Rem Determine library cache hit ratio
Rem Script 7
select sum(pins) Executions, sum(pinhits) "Execution Hits",
((sum(pinhits) / sum(pins)) * 100) phitrat,
sum(reloads) Misses,
((sum(pins) / (sum(pins) + sum(reloads))) * 100) hitrat
from v$librarycache;
Rem If the hit ratio or reloads is high, increase the shared_pool_size INIT.ora parameter.
Rem Reloads indicate that statements that were once in memory now had to be reloaded because they were pushed
Rem out, whereas misses include statements that are loaded for the first time.
Rem How much memory is left for SHARED_POOL_SIZE:
Rem Script 8
col value for 999,999,999,999 heading 'Shared Pool Size'
col bytes for 999,999,999,999 heading 'Free Bytes'
select to_number(v$parameter.valu
(v$sgastat.bytes/v$paramet
from v$sgastat, v$parameter
where v$sgastat.name = 'free memory'
and v$parameter.name = 'shared_pool_size';
rem A better query
Rem Script 9
select sum(ksmchsiz) Bytes, ksmchcls Status
from x$ksmsp
group by ksmchcls;
Rem To see how fast the SGA gets using x$bh
Rem Script 10
select state, count(*)
from x$bh
group by state;
Rem STATE COUNT(*)
Rem --------- -----------------
Rem 0 371
Rem 1 429
Rem In the above result:
Rem Total DB_BLOCK_BUFFERS = 800
Rem Total that have been used = 429
Rem Total that have NOT been used = 371
Rem A better query
Rem Script 11
select decode(state,0, 'FREE', 1, decode(lrba_seq,0,'AVAILAB
3, 'BEING USED', state) "BLOCK STATUS", count(*)
from x$bh
group by decode(state,0,'FREE',1,de
'AVAILABLE','BEING USED'),3, 'BEING USED', state);
Rem BLOCK STATUS COUNT(*)
Rem AVAILABLE 779
Rem BEING USED 154
Rem FREE 167
Rem Finding the largest amount of physical reads by query
Rem Script 12
select disk_reads, sql_text
from v$sqlarea
where disk_reads > 10000
order by disk_reads desc;
Rem Finding the largest amount of logical reads by query
Rem Script 13
select buffer_gets, sql_text
from v$sqlarea
where buffer_gets > 200000
order by buffer_gets desc;
Rem Script 14
select * from v$version;
Rem Script 15
select * from v$option;
Rem Script 16
Rem List the logswitches of the 4 last days
select sequence#,to_char(first_ti
I am sure you will find using these basic simple scripts what causes troubles.
Configuration and Installation of the StatsPack Package.
--------------------------
- StatsPack Database Space Requirements
Oracle does not recommend installing the package in the SYSTEM tablespace. A more appropriate tablespace (if it exists) would be a "TOOLS" tablespace. If you install the package in a locally-managed tablespace, storage clauses are not required, as the storage characteristics are automatically managed. If you install the package in a dictionary-managed tablespace, Oracle suggests you monitor the space used by the objects created, and adjust the storage clauses of the segments, if required.
The default initial and next extent size is 1MB for all tables and indexes which contain changeable data. The minimum default space requirement is approximately 35MB.
The amount of database space required by the package will vary considerably based on the frequency of snapshots, the size of the database and instance, and the amount of data collected (which is configurable). It is therefore difficult to provide general storage clauses and space utilization predictions, which will be accurate at each site.
Example:
CREATE TABLESPACE tools
DATAFILE '/u03/stat/statspack.dat' SIZE 300M
DEFAULT STORAGE (INITIAL 1M NEXT 1M
MINEXTENTS 1 unlimited PCTINCREASE 0)
ONLINE;
- Installing the StatsPack package
Interactive Installation:
========================
During the installation process, the user PERFSTAT will be created, default password PERFSTAT. This user will own all PL/SQL code and database objects created (including the STATSPACK tables, constraints and STATSPACK package).
The installation SQL script will prompt for the PERFSTAT user's default and temporary tablespaces and also for the tablespace in which the tables and indexes will be created
NOTE:
o Do not specify the SYSTEM tablespace for the PERFSTAT users
DEFAULT or TEMPORARY tablespaces; if SYSTEM is specified the
installation will abort with an error indicating this is the
problem. This is enforced as Oracle do not recommend using
the SYSTEM tablespace to store statistics data, nor for
sorting.
Use a TOOLS tablespace to store the data, and your
instance's TEMPORARY tablespace for sorting.
o During the installation, the dbms_shared_pool and dbms_job
PL/SQL packages are created. dbms_shared_pool is used to
pin the Statspack package in the shared pool; dbms_job
is created on the assumption the DBA will want to schedule
periodic snapshots automatically using dbms_job.
o The installation grants SELECT privilege to PUBLIC for all
of the Statspack owned tables (prefixed by STATS$).
Even though SVRMGRL is available with Oracle8i releases 8.1.6 and 8.1.7, installation of StatsPack can only be done with SQL*Plus to do special formatting commands that are used in the scripts. Oracle9i releases do not include SVRMGRL.
To install the package, using SQL*Plus and based on the correct platform and RDBMS version, do the following:
Oracle8i 8.1.7 and Oracle9i 9.x
on Unix:
SQL> connect / as sysdba
SQL> @?/rdbms/admin/spcreate
Batch mode installation
=======================
To install in batch mode, you must assign values to the SQL*Plus
variables which specify the default and temporary tablespaces
before running spcreate.
The variables are:
default_tablespace -> for the default tablespace
temporary_tablespace -> for the temporary tablespace
e.g. on Unix:
SQL> connect / as sysdba
SQL> define default_tablespace='tools'
SQL> define temporary_tablespace='temp
SQL> @?/rdbms/admin/spcreate
spcreate will no longer prompt for the above information.
- Log files created during installation
The StatsPack installation script runs 3 other scripts - you do not need to run these - the scripts are called automatically:
Oracle8i 8.1.7 or Oracle9i 9.x
1. spcusr -> creates the user and grants privileges
2. spctab -> creates the tables
3. spcpkg -> creates the package
The installation script will generate spooled output file based on the name of the script being run and end with a 'lis' extension. Check each of the three output files produced by the installation to ensure no errors were encountered, before continuing on to the next step.
For example, on Oracle8i 8.1.6, an output file called statcusr.lis will be created. Under Oracle8i 8.1.7 or Oracle9i 9.x, an output file spcusr.lis will be created.
- Errors found during installation process
If the scripts were incorrectly run while in SVRMGRL, an ORA-1012 error indicating that the session is not logged in or a PLS-00201 error indicating that stats$statspack_parameter must be declared may be generated. To correct this problem, first use SQL*Plus to remove StatsPack, and attempt installation using the above steps:
Oracle8i 8.1.7 and Oracle9i 9.x
on Unix:
SQL> connect / as sysdba
SQL> @?/rdbms/admin/spdrop
on NT:
SQL> connect / as sysdba
SQL> @%ORACLE_HOME%\rdbms\admin
- Batch mode installation
There are two ways to install Statspack - interactively (as shown above), or in 'batch' mode (as shown below). Batch mode is useful when you do not wish to be prompted for the PERFSTAT user's default and temporary tablespaces.
To install in batch mode, you must assign values to the SQL*Plus variables which specify the default and temporary tablespaces before running the StatsPack installation script.
The variables are:
default_tablespace -> for the default tablespace
temporary_tablespace -> for the temporary tablespace
e.g.
on Unix:
SQL> connect / as sysdba
SQL> define default_tablespace='tools'
SQL> define temporary_tablespace='temp
Oracle8i 8.1.7 or Oracle9i 9.x
SQL> @?/rdbms/admin/spcreate
The StatsPack installation script will no longer prompt for the above information.
--------------------------
- StatsPack Database Space Requirements
Oracle does not recommend installing the package in the SYSTEM tablespace. A more appropriate tablespace (if it exists) would be a "TOOLS" tablespace. If you install the package in a locally-managed tablespace, storage clauses are not required, as the storage characteristics are automatically managed. If you install the package in a dictionary-managed tablespace, Oracle suggests you monitor the space used by the objects created, and adjust the storage clauses of the segments, if required.
The default initial and next extent size is 1MB for all tables and indexes which contain changeable data. The minimum default space requirement is approximately 35MB.
The amount of database space required by the package will vary considerably based on the frequency of snapshots, the size of the database and instance, and the amount of data collected (which is configurable). It is therefore difficult to provide general storage clauses and space utilization predictions, which will be accurate at each site.
Example:
CREATE TABLESPACE tools
DATAFILE '/u03/stat/statspack.dat' SIZE 300M
DEFAULT STORAGE (INITIAL 1M NEXT 1M
MINEXTENTS 1 unlimited PCTINCREASE 0)
ONLINE;
- Installing the StatsPack package
Interactive Installation:
========================
During the installation process, the user PERFSTAT will be created, default password PERFSTAT. This user will own all PL/SQL code and database objects created (including the STATSPACK tables, constraints and STATSPACK package).
The installation SQL script will prompt for the PERFSTAT user's default and temporary tablespaces and also for the tablespace in which the tables and indexes will be created
NOTE:
o Do not specify the SYSTEM tablespace for the PERFSTAT users
DEFAULT or TEMPORARY tablespaces; if SYSTEM is specified the
installation will abort with an error indicating this is the
problem. This is enforced as Oracle do not recommend using
the SYSTEM tablespace to store statistics data, nor for
sorting.
Use a TOOLS tablespace to store the data, and your
instance's TEMPORARY tablespace for sorting.
o During the installation, the dbms_shared_pool and dbms_job
PL/SQL packages are created. dbms_shared_pool is used to
pin the Statspack package in the shared pool; dbms_job
is created on the assumption the DBA will want to schedule
periodic snapshots automatically using dbms_job.
o The installation grants SELECT privilege to PUBLIC for all
of the Statspack owned tables (prefixed by STATS$).
Even though SVRMGRL is available with Oracle8i releases 8.1.6 and 8.1.7, installation of StatsPack can only be done with SQL*Plus to do special formatting commands that are used in the scripts. Oracle9i releases do not include SVRMGRL.
To install the package, using SQL*Plus and based on the correct platform and RDBMS version, do the following:
Oracle8i 8.1.7 and Oracle9i 9.x
on Unix:
SQL> connect / as sysdba
SQL> @?/rdbms/admin/spcreate
Batch mode installation
=======================
To install in batch mode, you must assign values to the SQL*Plus
variables which specify the default and temporary tablespaces
before running spcreate.
The variables are:
default_tablespace -> for the default tablespace
temporary_tablespace -> for the temporary tablespace
e.g. on Unix:
SQL> connect / as sysdba
SQL> define default_tablespace='tools'
SQL> define temporary_tablespace='temp
SQL> @?/rdbms/admin/spcreate
spcreate will no longer prompt for the above information.
- Log files created during installation
The StatsPack installation script runs 3 other scripts - you do not need to run these - the scripts are called automatically:
Oracle8i 8.1.7 or Oracle9i 9.x
1. spcusr -> creates the user and grants privileges
2. spctab -> creates the tables
3. spcpkg -> creates the package
The installation script will generate spooled output file based on the name of the script being run and end with a 'lis' extension. Check each of the three output files produced by the installation to ensure no errors were encountered, before continuing on to the next step.
For example, on Oracle8i 8.1.6, an output file called statcusr.lis will be created. Under Oracle8i 8.1.7 or Oracle9i 9.x, an output file spcusr.lis will be created.
- Errors found during installation process
If the scripts were incorrectly run while in SVRMGRL, an ORA-1012 error indicating that the session is not logged in or a PLS-00201 error indicating that stats$statspack_parameter must be declared may be generated. To correct this problem, first use SQL*Plus to remove StatsPack, and attempt installation using the above steps:
Oracle8i 8.1.7 and Oracle9i 9.x
on Unix:
SQL> connect / as sysdba
SQL> @?/rdbms/admin/spdrop
on NT:
SQL> connect / as sysdba
SQL> @%ORACLE_HOME%\rdbms\admin
- Batch mode installation
There are two ways to install Statspack - interactively (as shown above), or in 'batch' mode (as shown below). Batch mode is useful when you do not wish to be prompted for the PERFSTAT user's default and temporary tablespaces.
To install in batch mode, you must assign values to the SQL*Plus variables which specify the default and temporary tablespaces before running the StatsPack installation script.
The variables are:
default_tablespace -> for the default tablespace
temporary_tablespace -> for the temporary tablespace
e.g.
on Unix:
SQL> connect / as sysdba
SQL> define default_tablespace='tools'
SQL> define temporary_tablespace='temp
Oracle8i 8.1.7 or Oracle9i 9.x
SQL> @?/rdbms/admin/spcreate
The StatsPack installation script will no longer prompt for the above information.
Gathering a StatsPack snapshot
--------------------------
The simplest interactive way to take a snapshot is to login to SQL*Plus as the PERFSTAT user, and execute the procedure statspack.snap:
e.g.
SQL> connect perfstat/perfstat
SQL> execute statspack.snap;
Note: In an OPS environment, you must connect to the instance you wish to collect data for. A snapshot must be taken on each instance so that later comparisons can be made. A snapshot taken on one instance can only be compared to another snapshot taken on the same instance.
Although statistics are cumulative, this will store the current values for the performance statistics in the StatsPack schema tables, and can be used as a baseline snapshot for comparison with another snapshot taken at a later time.
For easier performance analysis, set the init.ora parameter timed_statistics to true; this way, statspack data collected will include important timing information. The timed_statistics parameter is also dynamically changeable using the 'alter system' command. Timing data is important and often required by Oracle support to diagnose performance problems.
Parameters able to be passed in to the statspack.snap and
statspack.modify_statspack
--------------------------
==========================
Parameter Name Valid Values Value Meaning
==========================
i_snap_level 0, 5, 6, 10 5 Snapshot Level
--------------------------
i_ucomment Text Blank Comment to be stored with
Snapshot
--------------------------
i_executions_th Integer >=0 100 SQL Threshold: number of
times the statement was
executed
--------------------------
i_disk_reads_th Integer >=0 1,000 SQL Threshold: number of
disk reads the statement made
--------------------------
i_parse_calls_th Integer >=0 1,000 SQL Threshold: number of
parse calls the statement
made
--------------------------
i_buffer_gets_th Integer >=0 10,000 SQL Threshold: number of
buffer gets the statement
made
--------------------------
i_session_id Valid sid 0 (no Session Id of the Oracle
Session
from session) to capture session
granular
v$session statistics for
--------------------------
i_modify_parameter True, False False Save the parameters
specified for future
snapshots?
==========================
Configuring the amount of data captured
--------------------------
- Snapshot Level
It is possible to change the amount of information gathered by the package, by specifying a different snapshot 'level'. In other words, the level chosen (or defaulted) will decide the amount of data collected.
Levels = 0 General performance statistics
Statistics gathered:
This level and 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, parent latch statistics.
Levels = 5 Additional data: SQL Statements
This level includes all statistics gathered in the lower
level(s), and additionally gathers the performance data on
high resource usage SQL statements.
SQL 'Thresholds'
The SQL statements gathered by Statspack are those which
exceed one of four predefined threshold parameters:
- number of executions of the SQL statement (default 100)
- number of disk reads performed by the SQL statement
(default 1,000)
- number of parse calls performed by the SQL statement
(default 1,000)
- number of buffer gets performed by the SQL statement
(default 10,000)
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 the above threshold values, it is captured during the snapshot.
The SQL threshold levels used are either those stored in the table stats$statspack_parameter,
Levels = 6
This level includes all statistics gathered in the lower level(s). Additionally, it gathers SQL plans and plan usage data for each of the high resource usage SQL statements captured. Therefore, level 6 snapshots should be used whenever there is the possibility that a plan may change.
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.
Levels = 10 Additional statistics: Child latches
This level includes all statistics gathered in the lower levels, and additionally gathers high Child Latch information. Data gathered at this level can sometimes cause the snapshot to take longer to complete i.e. this level can be resource intensive, and should only be used when advised by Oracle personnel.
- Snapshot SQL thresholds
There are other parameters which can be configured in addition to the level. These parameters are used as thresholds when collecting SQL statements; if any SQL statements breach the threshold, these are the statements which are captured during the snapshot.
Snapshot level and threshold information used by the package is stored in the stats$statspack_parameter table.
- Changing the default values for Snapshot Level and SQL Thresholds
The default parameters used for taking snapshots can be adjusted/modified so that they better capture data about an instance's workload.
This can be done either by:
o Taking a snapshot, and specifying the new defaults to be saved to the database (using statspack.snap, and using the i_modify_parameter input variable).
SQL> execute statspack.snap -
(i_snap_level=>10, i_modify_parameter=>'true'
Setting the i_modify_parameter value to true will save the new
thresholds in the stats$statspack_parameter table; these
thresholds will be used for all subsequent snapshots.
If the i_modify_parameter was false or omitted, the snapshot taken at that point will use the specified values, any subsequent snapshots use the preexisting values in the stats$statspack_parameter table.
o Changing the defaults immediately without taking a snapshot, using the statspack.modify_statspack
SQL> execute statspack.modify_statspack
(i_snap_level=>10, i_buffer_gets_th=>10000,
i_disk_reads_th=>1000);
This procedure changes the values permanently, but does not
take a snapshot.
The full list of parameters which can be passed into the
modify_statspack_parameter
the snap procedure.
- Specifying a Session Id
If session statistics are needed for a particular session, it is possible to specify the session id in the call to StatsPack. The statistics gathered for the session will include session statistics, session events and lock activity.
The default behavior is to not to gather session level statistics.
SQL> execute statspack.snap(i_session_i
--------------------------
The simplest interactive way to take a snapshot is to login to SQL*Plus as the PERFSTAT user, and execute the procedure statspack.snap:
e.g.
SQL> connect perfstat/perfstat
SQL> execute statspack.snap;
Note: In an OPS environment, you must connect to the instance you wish to collect data for. A snapshot must be taken on each instance so that later comparisons can be made. A snapshot taken on one instance can only be compared to another snapshot taken on the same instance.
Although statistics are cumulative, this will store the current values for the performance statistics in the StatsPack schema tables, and can be used as a baseline snapshot for comparison with another snapshot taken at a later time.
For easier performance analysis, set the init.ora parameter timed_statistics to true; this way, statspack data collected will include important timing information. The timed_statistics parameter is also dynamically changeable using the 'alter system' command. Timing data is important and often required by Oracle support to diagnose performance problems.
Parameters able to be passed in to the statspack.snap and
statspack.modify_statspack
--------------------------
==========================
Parameter Name Valid Values Value Meaning
==========================
i_snap_level 0, 5, 6, 10 5 Snapshot Level
--------------------------
i_ucomment Text Blank Comment to be stored with
Snapshot
--------------------------
i_executions_th Integer >=0 100 SQL Threshold: number of
times the statement was
executed
--------------------------
i_disk_reads_th Integer >=0 1,000 SQL Threshold: number of
disk reads the statement made
--------------------------
i_parse_calls_th Integer >=0 1,000 SQL Threshold: number of
parse calls the statement
made
--------------------------
i_buffer_gets_th Integer >=0 10,000 SQL Threshold: number of
buffer gets the statement
made
--------------------------
i_session_id Valid sid 0 (no Session Id of the Oracle
Session
from session) to capture session
granular
v$session statistics for
--------------------------
i_modify_parameter True, False False Save the parameters
specified for future
snapshots?
==========================
Configuring the amount of data captured
--------------------------
- Snapshot Level
It is possible to change the amount of information gathered by the package, by specifying a different snapshot 'level'. In other words, the level chosen (or defaulted) will decide the amount of data collected.
Levels = 0 General performance statistics
Statistics gathered:
This level and 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, parent latch statistics.
Levels = 5 Additional data: SQL Statements
This level includes all statistics gathered in the lower
level(s), and additionally gathers the performance data on
high resource usage SQL statements.
SQL 'Thresholds'
The SQL statements gathered by Statspack are those which
exceed one of four predefined threshold parameters:
- number of executions of the SQL statement (default 100)
- number of disk reads performed by the SQL statement
(default 1,000)
- number of parse calls performed by the SQL statement
(default 1,000)
- number of buffer gets performed by the SQL statement
(default 10,000)
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 the above threshold values, it is captured during the snapshot.
The SQL threshold levels used are either those stored in the table stats$statspack_parameter,
Levels = 6
This level includes all statistics gathered in the lower level(s). Additionally, it gathers SQL plans and plan usage data for each of the high resource usage SQL statements captured. Therefore, level 6 snapshots should be used whenever there is the possibility that a plan may change.
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.
Levels = 10 Additional statistics: Child latches
This level includes all statistics gathered in the lower levels, and additionally gathers high Child Latch information. Data gathered at this level can sometimes cause the snapshot to take longer to complete i.e. this level can be resource intensive, and should only be used when advised by Oracle personnel.
- Snapshot SQL thresholds
There are other parameters which can be configured in addition to the level. These parameters are used as thresholds when collecting SQL statements; if any SQL statements breach the threshold, these are the statements which are captured during the snapshot.
Snapshot level and threshold information used by the package is stored in the stats$statspack_parameter table.
- Changing the default values for Snapshot Level and SQL Thresholds
The default parameters used for taking snapshots can be adjusted/modified so that they better capture data about an instance's workload.
This can be done either by:
o Taking a snapshot, and specifying the new defaults to be saved to the database (using statspack.snap, and using the i_modify_parameter input variable).
SQL> execute statspack.snap -
(i_snap_level=>10, i_modify_parameter=>'true'
Setting the i_modify_parameter value to true will save the new
thresholds in the stats$statspack_parameter table; these
thresholds will be used for all subsequent snapshots.
If the i_modify_parameter was false or omitted, the snapshot taken at that point will use the specified values, any subsequent snapshots use the preexisting values in the stats$statspack_parameter table.
o Changing the defaults immediately without taking a snapshot, using the statspack.modify_statspack
SQL> execute statspack.modify_statspack
(i_snap_level=>10, i_buffer_gets_th=>10000,
i_disk_reads_th=>1000);
This procedure changes the values permanently, but does not
take a snapshot.
The full list of parameters which can be passed into the
modify_statspack_parameter
the snap procedure.
- Specifying a Session Id
If session statistics are needed for a particular session, it is possible to specify the session id in the call to StatsPack. The statistics gathered for the session will include session statistics, session events and lock activity.
The default behavior is to not to gather session level statistics.
SQL> execute statspack.snap(i_session_i
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hello , I have not had time in order to read your answers, however thanks for the timely answer. Thanks.
you know hoempage where I can find other information to care? I have felt to speak about metalink... .
you know hoempage where I can find other information to care? I have felt to speak about metalink... .
Download for free from OTN the .pdf with Oracle docs and read performnce Tuning.
Also OTN (Oracle Technology network) has published manu articles. See Also 'Oracle magazine", "Ask Tome".
But your case seems not be so terible and do neeed to use STATSPACK.
Also OTN (Oracle Technology network) has published manu articles. See Also 'Oracle magazine", "Ask Tome".
But your case seems not be so terible and do neeed to use STATSPACK.
Hi!
Do you have any specific performance problem at hand? Or just want to looka t general performance?
Probably the best thing to do is to install statspack and start working with it. I believe that statspack is available for 8.1.6, although is not shipped with original installation. You can either search google for statspack for 8.1.6 or go to Metalink and look for it there.
Also, there is a good site that will help ypu to analyze teh statspack report or bstat/estat report as well. Look at: http://www.oraperf.com/
Udachi! :)
Pontis