Posts Tagged ‘dynamic performance views’

Performance Monitoring

List of Common Oracle Metrics

Monitoring Methodologies

 

  • Reactive
  • Proactive

Reactive monitoring is not a recommend approach but at times it becomes inevitable. Oracle 10g provide tools that aid in proactively monitoring database. Server-generated alerts and Automated Database Diagnostic Monitor(AADM) are going to be discussed in another post.

 

Database and Instance Metrics

Performance measurements are commonly referred as databases metrics which is basically the rate of change of cumulative statistic that need to be monitored. Metrics are indicators of the health of various database targets. Excerpt from Oracle Database 10g New Features by Rampant TechPress

“The Metrics are the statistics derived from base statistics. They represent the delta values between the snapshot periods. Metrics are used by internal components (clients) for system health monitoring, problem detection and self-tuning. There are hundreds of different measurement points available to monitor database system.

Each metric is also associated with a metric name. You can query the view v$metricname to find the names of all the metrics.

SQL> select METRIC_NAME, METRIC_UNIT from v$metricname; "

 

Metric                                                  Description                                                                                                  
Event Class Metrics Metrics collected on the wait event class level. e.g.  DB_TIME_WAITING
Event Metrics Metrics collected on various wait events
File Metrics Long Duration Metrics collected at the file level. e.g. AVERAGE_FILE_WRITE_TIME
Service Metrics Metrics collected at the service level. e.g. CPU_TIME_PER_CALL
Session Metrics Short Duration Metrics collected at the session level. e.g. BLOCKED_USERS
System Metrics Short Duration Metrics collected at the system level
Tablespae Metrics Metrics collected at the Tablespace level e.g. TABLESPACE_PCT_FULL

 

Viewing Metrics

The new MMON background process collects database metrics continuously and automatically saves them in the SGA for one hour.It also transfers the memory version of AWR stats to disk on regular basis(in snapshots). Performance metrics are available through

  • Data Dictionary
  • Dynamic Performance Views
  • Optimizer Statistics

Data Dictionary Metrics

Data dictionary metrics provide information about space consumption and object status. Database indexes and stored procedures both need to be VALID to be used.

Unusable indexes always require DBA intervention to restore them to a valid state. Invalid
PL/SQL objects will normally recompile automatically the first time they are called, but
sometimes require DBA intervention in cases where the automatic recompilation fails.

Compiling PL/SQL procedures and Packages

Indexes can become unusable due to normal maintenance operations on tables. Unusable indexes can be rebuild to make them valid.

Rebuilding Indexes

 

Viewing In-Memory Metrics

All system related metrics saved in memory and they can be viewed using dynamic performance views. Most of the cumulative statistics can be viewed through following:

  • V$SYSSTAT
  • V$SESSSTAT
  • V$SYSMETRIC
  • V$SYSMETRIC_HISTORY

Example of few system metrics maintained in V$SYSMETRIC view:

  • Buffer Cache Hit Ratio
  • CPU Usage Per Sec
  • Disk Sort Per Sec
  • Host CPU Utilization (%)
  • Library Cache Hit Ratio
  • SQL Service Response Time
  • Shared Pool Free (%)

Viewing Saved Metrics

After every 60 min, MMON places metric info from SGA to disk through AWR snapshot mechanism. The metric data that AWR collects through MMON is permanently stored in DBA_HIST_* views like

  • DBA_HIST_SERVICE_NAME
  • DBA_HIST_SESSMETRIC_HISTORY

Optimizer Statistics

Optimizer statistics for tables and indexes are stored in the data dictionary. These statistics are not intended to provide real-time data. They provide the optimizer a statistically correct snapshot of data storage and distribution which the optimizer uses to make decisions on how to access data.

Metrics collected include:

  • Size of the table or index in database blocks
  • Number of rows
  • Average row size and chain count (tables only)
  • Height and number of deleted leaf rows (indexes only)

As data is inserted, deleted, and modified these values change. The performance impact of
maintaining real-time data distribution statistics would be prohibitive, so these statistics are updated by periodically gathering statistics on tables and indexes.

Advertisements

CONTROL FILE

The control file is a small binary file necessary for the database to start and operate
successfully. Each control file is associated with only one Oracle database. Before a database is opened, the control file is read to determine if the database is in a valid state to use. A control file is updated continuously by the Oracle server during database use, so it must be available for writing whenever the database is open.

The control file is a binary file that defines the current state of the physical database..
• Loss of the control file requires recovery
• Is read at MOUNT stage
• Is required to operate
• Is linked to a single database
• Should be multiplexed
• Maintains integrity of database
• Sized initially by CREATE DATABASE

Control File Contents

The information in the control file includes:
• Database name is taken from either the name specified by the initialization parameter
DB_NAME or the name used in the CREATE DATABASE statement.
• Database identifier is recorded when the database is created.
• Time stamp of database creation is also recorded at database creation.
• Names and locations of associated data files and online redo log files are updated when
a data file or redo log is added to, renamed in, or dropped from the database.
• Tablespace information is updated as tablespaces are added or dropped.
• Redo log history is recorded during log switches.
• Location and status of archived logs are recorded when archiving occurs.
• Location and status of backups are recorded by the Recovery Manager utility.
• Current log sequence number is recorded when log switches occur.
• Checkpoint information is recorded as checkpoints are made.

 

Obtaining Control File Information

The SHOW PARAMETERS command can also be used to find the location of the control files.

show parameters control_files;

SELECT name FROM V$CONTROLFILE;

Information in several of the other dynamic performance views is obtained from the control
file:
• V$BACKUP
• V$DATAFILE
• V$TEMPFILE
• V$TABLESPACE
• V$ARCHIVE
• V$LOG
• V$LOGFILE
• V$LOGHIST
• V$ARCHIVED_LOG
• V$DATEBASE

The minimum number of days that a reusable record is kept in the controlfile is controlled by the control_file_record_keep_time parameter.

These sections consist of records. The size, total number and number of used record are exposed through v$controlfile_record_section.

Oracle Dynamic Performance Views

Dynamic Performance v$ Views

Real-time performance metrics covering memory usage, wait events, input/output device
throughput, and instance activity are available through dynamic performance views, also known as v$ views.

Throughout its operation, the Oracle server records current database activity in a set of virtual tables called dynamic performance views. These virtual tables exist in memory only when the database is running, to reflect real-time conditions of the database operation. They point to actual sources of information in memory and the control file.

Purpose of Dynamic Performance Views

Dynamic performance views provide a snapshot of real-time data.

The dynamic performance views answer questions such as:

  • Is the object online and available?
  • Is the object open?
  • What locks are being held?
  • Is the session active?

Dynamic performance views record current database activity.

  • Views are continually updated while the database is operational
  • Information is accessed from  Memory  and Control file
  • DBA uses dynamic views to monitor and tune the database
  • Dynamic views are owned by SYS user
  • Dynamic views data is non persistent
  • Dynamic performance metrics should be considered in context of performance trends or against baseline data

Dynamic performance views should not be used to make configuration decisions until the instance hasbeen operational for a while.

Architecture of Dynamic Performance Views

Dynamic Performance views are based on very low level Oracle views X$ views. The X$views are actually not tables rather Oracle internal structures(database representation of C Structures) maintained by Oracle kernel.

On top of X$ views, Oracle has created complete set of views called V_$ views. These V_$ views have synonyms with same names starting with V$. These V$ synonyms are called dynamic performance views and V$ views as well.

The dynamic performance tables are owned by SYS.

Dynamic Performance Views Examples

v$controlfile

Displays the location and status of each controlfile in the database.

v$instance

Instance_role can be used to determine if an instance is an active instance (=primary instance) or a secondary instance (in a standby environment.

v$database

This view lets you access database information. For example, you can check (using log_mode) whether or not the database is in archivelog mode

Data Dictionary

Oracle Dynamic Performance Views

Oracle Data Dictionary Tables

Data Dictionary

A repository of information describing the data in the database. This includes the name and structures of all tables, constraints, indices, views, synonyms, sequences triggers, procedures, functions, and packages.

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, LAST_CHANGE
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME = ‘EMPLOYEES’
and OWNER = ‘SCOTT’

 

DBA_TABLES
select TABLE_NAME,LAST_ANALYZED,ROW_LEN, AVG_ROW_LEN,NUM_ROWS,,STATUS,PCTFREE, PCTUSED,
CACHE, BUFFER_POOL,
from dba_tables
where table_name = ‘EMPLOYEE’

SELECT * FROM DBA_ROLE_PRIVS
WHERE GRANTEE =’FKHALID’

 

Table and Index

SELECT DT.TABLE_NAME, DI.INDEX_NAME,DT.CACHE TBL_CACHE, DT.BUFFER_POOL TBL_BUFFER_POOL,
BLEVEL,DI.LOGGING, DI.LAST_ANALYZED "INDX LAST ANALYZED"
FROM DBA_TABLES DT, DBA_INDEXES DI
WHERE DT.OWNER=’HR_DUP’ AND DT.TABLE_NAME= DI.TABLE_NAME

CONSTRAINTS

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, LAST_CHANGE
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME = ‘EMPLOYEES’

SELECT Owner,Constraint_name, Table_name, Column_name 
FROM dba_cons_columns
where  table_name = ‘EMPLOYEES’
and owner = ‘HR_DUP’

 

SESSIONS

SELECT sid,serial#,process,username,status,machine,terminal,
lockwait,machine,terminal,blocking_session,blocking_session_status,state,
program,type,logon_time
FROM v$session

  

 

 

Some Useful Data Dictionary Tables

USER_TABLES
Lists each table that belongs to your Oracle user.

USER_TAB_COMMENTS
Shows comments on the tables and views.

USER_TAB_COLUMNS
Tells you the names, data types, default values, etc. of each column in each table.

USER_COL_COMMENTS
Shows comments on the columns.

USER_CONSTRAINTS
Gives you all constraints (either single- or multi-column), such as primary key, foreign key, not null, check constraints, etc.

USER_CONS_COLUMNS
Maps constraints to columns (since a constraint can act on one or many columns).

USER_INDEXES
Lists indexes defined on columns (either defined explicitly when creating the data model or defined automatically by Oracle, as is the case with indexes on primary keys).

USER_IND_COLUMNS
Maps indexes to columns.

USER_VIEWS
Lists all views, along with the text used to originally create them.

USER_SYNONYMS
Lists the synonyms and original table names.

USER_SEQUENCES
Lists all sequences, including min value, max value, and amount by which to increment.

USER_TRIGGERS
Contains trigger names, criteria for activating each trigger, and the code that is run.

USER_SOURCE
Contains the source code for all PL/SQL objects, including functions, procedures, packages, and package bodies.