Oracle Performance Diagnostics-Learning Statspack Notes (ii) [Overview]

Oracle| Notes | performance
Oracle Performance Diagnostics-Learning Statspack Notes (ii) [Overview]



Author: Liu Yingbo

Time: 2004-3-3

Mail:liuyingbo@126.com, please correct me.



Reprint please indicate the source and the author



Oracle Performance Diagnostics involves objects



A. Servers, networks, and disks (external environment)

b. Example (SGA, background process)

C. Objects (table, index, paragraph ...). ）

D. SQL

E. Design (refers to the design of the application, which is generally difficult to change)



Oracle Performance Diagnostics to follow the order above, first look at whether the server is a problem, mainly from the Cpu,ram,disk configuration of the problem, check the operating system's core parameters, and so on; If you are sharing multiple Oracle across geographies, network communication performance is also critical, Oracle leverages TNS (transparent network substrate transparent network layer) to provide distributed transport between databases, and the largest individual component of Oracle's corresponding time is disk I/O, which reduces disk i/ o Anything can have a positive impact on Oracle performance, such as changing the Oracle initialization parameters, adjusting the corresponding SQL, etc. the problem with Oracle's instance tuning is that overloaded Oracle's SGA can cause serious performance problems. Adjustments to Oracle instances include initialization parameters, data buffer storage (DEFAULT, KEEP, recycle), shared pools and library caches in the SGA, and adjustments to Oracle objects, including storage parameters, and finally the adjustment of SQL statements.



Statspack Overview



Statspack source Utlbstat and Utlestat tools that existed in the earliest versions of Oracle. The Bstat-estat tool at the beginning can get information directly from Oracle's memory structure.

Statspack works by getting a snapshot of the current state of the database. In most cases, we plan a job that collects data in hours, and asks for a quick photo when needed.

When we take the snapshot, Statspack takes a sample from the RAM memory structure inside the SGA and logs it to the corresponding Statspack table, noting that in most cases there is a direct correspondence between the v$ view in the SGA and the corresponding Statspack table, for example:



V$sysstat--------->stats$sysstat



Sql> DESC V$sysstat

Name Null? Type

----------------------------------------- -------- ----------------------------

statistic# number

NAME VARCHAR2 (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

Statistic# not NULL number

NAME not NULL VARCHAR2 (64)

VALUE number



In understanding the Statspack tool, it is important to understand that the information collected through the Statspack snapshot is cumulative, from the v$ view to collect the starting time of the database information, and then continue to accumulate, know the instance aborted, I think, This should probably be the reason why Statspack cannot produce two reports spanning shutdown snapshots.

There are a series of statspack tables in the corresponding Statspack, and different versions of Oracle will have some differences. These tables are roughly divided into control tables, parameter tables, event tables, transaction tables, parallel server tables, profile tables, system tables, and so on.

Here are the Statspacke tables for the oracle9i I 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$pgastat

Stats$idle_event

Stats$parameter

Stats$instance_recovery

Stats$statspack_parameter

Stats$shared_pool_advice

Stats$sql_workarea_histogram

Stats$pga_target_advice



Selected rows.



The main anchor point of the Statspack table is stats$database_instance, the introduction of the concrete table I intend to put to the later article for discussion.

It can be said that before our Oracle performance tuning is mainly a reactive tunning (reactive adjustment), through the Statspack tool, we can conduct long-term trend analysis, performance problems after the analysis, resource planning and forecasting modeling, etc. We can totally adopt a proactive tunning (forward-looking adjustment). And starting with Oracle9i, Oracle can dynamically change the memory configuration of Oracle instances, and Oracle is moving toward dynamic database configuration.



adjourned

................................................................................................

Reference

Donald K.burleson "ORACLE high-performance tuning with Statspack"