Posts Tagged ‘Data Dictionary’

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

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.