********************************************************************
■Performance Metrics
1.
・A statistic is a raw figure, which may be useless in itself
・a metric is a statistic converted into something meaningful
2.
・Dynamic performance views that are populated from the instance,
such as V$INSTANCE or V$SYSSTAT, are available at all times, even when the instance is in NOMOUNT mode
・Dynamic performance views that are populated from the controlfile,
such as V$DATABASE or V$DATAFILE, cannot be queried unless the database has been mounted
・the data dictionary views (prefixed DBA,ALL, or USER) can only be queried after the database—including the data dictionary—has been opened
※The dynamic performance views are created at startup, updated during the lifetime of the instance, and dropped at shutdown.
eg:
v$sysstat
V$SYSTEM_WAIT_CLASS
■Performance Issues:
・The run queue length, which indicates whether the server machine’s CPU resources are being strained
・The paging rate, which will increase if the server is short of memory
・The count of database sessions waiting, and why
・The number of logins and transactions per second
・The number of physical reads and amount redo generated by second
*******************************************************************
■■AWR:The Automatic Workload Repository
■STATISTICS_LEVEL default TYPICAL
•BASIC:disable virtually all statistics, with no appreciable performance benefit
•TYPICAL:force the collection of all the statistics that are needed for normal tuning, without collecting any whose collection would impact adversely on performance
•ALL:collect extremely detailed statistics on SQL statement execution: these may occasionally be necessary if you are doing advanced SQL statement tuning, but they may cause a slight performance drop while being collected
※Statistics are saved as a snapshot to the AWR by the MMON(manageability monitor)process, by default every sixty minutes.
■The Size and Location of the AWR
・The AWR is a set of tables located in the SYSAUX tablespace; these tables cannot be relocated to anywhere else.
・They exist in the SYSMAN schema.
・Snapshots are kept in the AWR, by default, for seven days. This period is configurable.
■■ADDM:The Automatic Database Diagnostic Monitor
•The ADDM is run automatically by the MMON whenever a snapshot is taken.
•As with all the advisors, it takes statistics and other information from the AWR.
•The ADDM is triggered by automatic snapshots(the period between the current snapshot and the previous one)
and also if you gather a snapshot manually(the period between any two snapshots).
•The reports are purged by default after thirty days
■Generating an ADDM Report
①Force the creation of an AWR snapshot
SQL> exec dbms_workload_repository.create_snapshot;
②Activities
③Generate another snapshot.
SQL> exec dbms_workload_repository.create_snapshot;
④Database Control view the reports
■■The Diagnostic and Tuning Advisors
The Advisor Central window gives you the options of viewing the results of previous advisor tasks, or of using any of the seven advisors:
• The Automatic Database Diagnostic Monitor (the ADDM)
• The SQL Tuning Advisor
takes as its input a set of one or more SQL statements and investigates their structure and manner of execution.
• The SQL Access Advisor
it estimates whether performance could be improved by creating additional indexes and materialized views, and it also can recommend dropping ones that exist but are not being used.
• The Memory Advisor
• The Mean Time to Recover (MTTR) Advisor
• The Segment Advisor
• The Undo Advisor
■■Server-Generated Alerts
■The Alert System Architecture
Alerts are sent by the MMON(manageability monitor) background process and displayed by Database Control.
*You can use the advanced queueing API (the DBMS_AQ package) to write your own routines for viewing alerts
■Alerts two forms:
①threshold, or stateful
Threshold alerts must be configured by setting values for some metric to be monitored,will persist until some action is taken.
such as the percentage of space used in a tablespace
②non-threshold, or stateless
The non-threshold alerts are triggered by an event that happens but does not persist.
such as an “ORA-1555: snapshot too old” error.
■Setting Thresholds
■Using Baselines
■■Tips
1.
85% and 97% are the database defaults for warning and critical alerts on tablespace usage
2.
With regard to the collection of monitoring information,steps in the correct order:
①Data accumulates in the SGA.
②MMON writes data to the AWR.
③MMON generates an ADDM report.
④Snapshots are purged.
⑤Reports are purged.
3.
By default, AWR snapshots are kept for a week and ADDM reports for thirty days.