Oracle Performance Diagnosis - Learning Statspack Notes (2) [Overview]
Author: Liuying Bo
Time: 2004-3-3
Mail: liuyingbo@126.com, please correct
Reprint, please indicate the source and the author
Oracle Performance Diagnostics
A. Server, network, and disk (external environment)
B. Instance (SGA, background process)
C. Objects (table, index, paragraph ...)
D. SQL
E. Design (referring to the design design, this part is generally difficult to change)
Oracle Performance Diagnosis To follow the above order, see if there is a problem in the server, mainly from the CPU, RAM, Disk configuration, check the settings of the core parameters of the operating system, etc. If it is a cross-regional sharing multiple Oracle Network communication performance is also critical, Oracle uses TNS (Transparent Network Substrate Transparent Network Layer) Provides distributed transmission between databases; additional individual components of Oracle's corresponding time are disk I / O, which can be reduced Anything of disk I / O will have a positive impact on Oracle performance, such as changing Oracle initialization parameters, adjusting the corresponding SQL, etc .; the problem that should be considered for Oracle is that the overloaded Oracle's SGA will result in severe performance. Question, for the adjustment of the Oracle instance mainly includes: initialization parameters, data buffer storage (DEFAULT, Keep, Recycle) and SGA share pools and libraries, etc., there is an Oracle object adjustment, including storage parameters, etc.; Finally SQL The adjustment of the statement.
STATSPACK Overview
The STATSPACK source is the UTLBSTAT and UTLESTAT tools existing in Oracle. The starting BSTAT-ESTAT tool can get information directly from Oracle's memory structure.
Statspack works by getting a snapshot of the current status of the database. Most of the cases, we plan a Job for collecting data in hours, and request additional snapshots when needed.
When we get a snapshot, StatsPack samples from the RAM memory structure inside the SGA and records in the corresponding StatsPack table, note that in most cases, the V $ view in the SGA and the corresponding StatsPack table There is a direct correspondence relationship, such as:
V $ sysstat ---------> Stats $ sysstat
SQL> DESC V $ sysstat
Name NULL? TYPE
------------------------------------- ---------------------------
Statistic # Number
Name varcha2 (64)
Class Number
Value Number
SQL> DESC Stats $ sysstat
Name NULL? TYPE
------------------------------------- ---------------------------
SNAP_ID NOT NULL NUMBER (6)
DBID NOT NULL NUMBER
Instance_Number Not Null Number, INSTANCE_NUMBER
Statistic # not null Number
Name Not Null Varchar2 (64)
Value Number
When understanding the StatsPack tool, it is critical to understand that the information collected by the StatsPack snapshot is a cumulative value, collect the database information of the start time from the V $ view, and then continuously, know the instance, I think this Perhaps it should be that Statspack cannot generate two reasons for the report of snapshots across the shutdown.
There is a series of StatsPack tables corresponding to Statspack, and different Oracle versions have a certain difference. These tables are generally divided into control tables, parameter tables, event tables, transaction tables, parallel server tables, profiles, system tables, etc.
Below is the StatsPacke table I have listed:
SQL> SELECT TABLE_NAME from DBA_TABLES where Table_Name Like 'Stats $%;
Table_name
------------------------------
Stats $ Database_InStance
Stats $ level_description
Stats $ SNAPSHOT
Stats $ db_cache_advice
Stats $ filestatxs
Stats $ TEMPSTATXS
Stats $ latch
Stats $ latch_children
Stats $ latch_parent
Stats $ LATCH_MISSES_SUMMARY
Stats $ librarycache
Table_name
------------------------------
Stats $ buffer_pool_statistics
Stats $ ROLLSTAT
Stats $ ROWCACHE_SUMMARY
Stats $ SGA
Stats $ SGASTAT
Stats $ sysstat
Stats $ SESSTAT
Stats $ SYSTEM_EVENT
Stats $ session_event
Stats $ BG_EVENT_SUMMARY
Stats $ Waitstat
Table_name
------------------------------
Stats $ enqueue_stat
Stats $ SQL_SUMMARY
Stats $ SQLText
Stats $ SQL_STATISTICS
Stats $ Resource_Limit
Stats $ dlm_misc
Stats $ undostat
Stats $ SQL_PLAN_USAGE
Stats $ SQL_PLAN
Stats $ seg_stat
Stats $ seg_stat_obj
Table_name
------------------------------
Stats $ PGASTATA
Stats $ IDLE_EVENT
Stats $ ParameterStats $ Instance_Recovery
Stats $ statspack_parameter
Stats $ Shared_Pool_Advice
Stats $ SQL_WORKAREA_HISTOGRAM
Stats $ PGA_TARGET_ADVICE
41 rows selected.
The main anchoring point of the StatsPack table is Stats $ database_instance, and the specific table introduces I plan to put it in the following article discussions.
It can be said that our Oracle performance adjustment is mainly a reactive tunning (reactive adjustment), through the StatsPack tool, we can perform long-term trend analysis, performance issues analysis, resource planning, and predictive modeling, we can use one Proactive tunning (forward-looking adjustment). And starting from Oracle9i, Oracle can dynamically change the memory configuration of the Oracle instance, Oracle is also moving towards the dynamic database configuration.
(to be continued)
......................................................................................
reference
Donald K.burleson "Oracle High-Performance Tuning with Statspack"
