The following Automatic Workload Repository is represented by AWR
12c is self managed most of the time. Maintenance tasks run automatically and diagnostic information is collected regularly.
Diagnosing performance problems is time-consuming and requires skills. 12c has the Alert system and diagnostic advisor installed by default, which can simplify the work of DBA.
MANAGE THE AUTOMATIC WORKLOAD REPOSITORY
Oracle collects a large number of performance and activity statistics, first written to memory, then to disk, specifically AWR.
AWR is a set of tables and objects, located in the SYSAUX table space. After AWR data is stored for a period of time, it will be updated by the latest data.
Gathering AWR Statistics
The statistics "level parameter determines the level of detail to be collected. It defaults to TYPICAL and will collect the information required for normal tuning. It can also be set to BASIC and ALL, the former basically does not collect information, and the latter collects ALL information, but it will affect performance.
ORCLCDB> show parameter STATISTICS_LEVEL NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ client_statistics_level string TYPICAL statistics_level string TYPICAL
AWR first writes to SGA cumulatively, and then writes to disk periodically (operated by MMON, the default is 1 hour), which is called snapshot.
In the early days, Oracle used V $view to view performance information. Although it is still available now, the better way is to use AWR. The cost of AWR is writing to disk. Since it is only once an hour, the cost is very small.
AWR snapshot is reserved for 8 days by default, and will be overwritten by new data.
AWR is a set of tables, belonging to SYS user, stored in SYSAUX table space.
Access to AWR can only be through API or DBMS package.
Another benefit of AWR is to provide historical information, while DBMS ﹣ stats only has current information.
Managing the AWR
AWR snapshot is generated once an hour. It is reserved for 8 days by default. It can also be set as baseline and saved permanently.
The frequency and cycle can be changed. If the frequency is increased, the diagnosis will be more accurate, but more space will be taken up, which may affect the performance; if the frequency is reduced, the peak information may be missed.
It is very important to monitor the space and growth of SYSAUX and AWR. The Alert system can help the former, and V $SYSAUX \
Adjust the AWR hold frequency and hold period through the DBMS? Workload? Repository package.
ORCLCDB> select snap_interval, retention from DBA_HIST_WR_CONTROL; SNAP_INTERVAL RETENTION ------------------------------ ------------------------------ +00000 01:00:00.0 +00008 00:00:00.0 ORCLCDB> exec DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(retention=>43200, interval=>30); PL/SQL procedure successfully completed. ORCLCDB> select snap_interval, retention from DBA_HIST_WR_CONTROL; SNAP_INTERVAL RETENTION ------------------------------ ------------------------------ +00000 00:30:00.0 +00030 00:00:00.0 -- Manual snapshot ORCLCDB> execute DBMS_WORKLOAD_REPOSITORY.create_snapshot; PL/SQL procedure successfully completed. -- Restore default ORCLCDB> exec DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(retention=>11520, interval=>60); PL/SQL procedure successfully completed. ORCLCDB> select snap_interval, retention from DBA_HIST_WR_CONTROL; SNAP_INTERVAL RETENTION ------------------------------ ------------------------------ +00000 01:00:00.0 +00008 00:00:00.0
Monitor AWR usage space:
ORCLCDB> select occupant_desc, space_usage_kbytes from V$SYSAUX_OCCUPANTS where occupant_name='SM/AWR';
OCCUPANT_DESC SPACE_USAGE_KBYTES
---------------------------------------------------------------- ------------------
Server Manageability - Automatic Workload Repository 149056
ORCLCDB> execute DBMS_WORKLOAD_REPOSITORY.create_snapshot;
PL/SQL procedure successfully completed.
ORCLCDB> select occupant_desc, space_usage_kbytes from V$SYSAUX_OCCUPANTS where occupant_name='SM/AWR';
OCCUPANT_DESC SPACE_USAGE_KBYTES
---------------------------------------------------------------- ------------------
Server Manageability - Automatic Workload Repository 150656
ORCLCDB> select 150656 - 149056 "One Snapshot Size(KB)" from dual;
One Snapshot Size(KB)
---------------------
1600
ORCLCDB> select min(begin_interval_time), max(begin_interval_time), count(snap_id) from dba_hist_snapshot;
MIN(BEGIN_INTERVAL_TIME) MAX(BEGIN_INTERVAL_TIME) COUNT(SNAP_ID)
---------------------------------------- ---------------------------------------- --------------
12-OCT-19 08.31.05.000 PM 22-OCT-19 12.46.31.376 PM 67
USE THE AUTOMATIC DATABASE DIAGNOSTIC MONITOR
The database system is preconfigured with many recommenders, one of which is Automatic Database Diagnostic Monitor (ADDM).
ADDM views and compares two AWR snapshots, and then summarizes the activities, finds problems and gives suggestions. ADDM runs automatically when an AWR snapshot is generated for the current and previous snapshot.
The problems that ADDM can diagnose include:
• CPU bottleneck
• competition is like a line lock
• I/O system pressure
• high load SQL
ADDM reports can also be generated manually through addmrpt.sql under ORACLE_HOME/rdbms/admin.
Exercise to generate an ADDM report:
@?/rdbms/admin/addmrpt.sql
The generated file is a txt file.
Describe and use the recommender framework
All kinds of recommenders are based on AWR snapshot or data dictionary, which also provides PL/SQL interface. The main recommenders include:
- Memory Advisor - predicts the impact of changing memory, such as whether increasing daily performance improves
- There are two types of SQL recommenders: SQL Access Advisor and SQL Tuning Advisor. The former will recommend adding or deleting indexes, materialized views, partitions, etc.; the latter will also recommend re SQL besides the former. It is realized by DBMS advisor and DBMS sqltune respectively.
- Undo recommender - recommend the size of undo table space based on the frequency of undo data generation and the time of query running
- MTTR recommender - evaluates the recovery time required to start after an abnormal shutdown of the database, in V $instance "recovery.
- Data Recovery recommender - called through DBMS ﹣ sqldiag package. When the data is damaged, find out the cause of the problem. The suggestion is to recover the file or data block, and how to do it.
- segment Advisor - through the DBMS_ADVISOR call, when to reorg is recommended. Segments do not automatically shrink when they are deleted or updated, only when they are reorg.
- SQL Repair recommender - execute SQL report ORA-600 error, possibly Bug. This advisor generates patches to bypass this Bug.
Set ALERT threshold
Oracle is called self-management system because of Alert system, which saves a lot of DBA work.
Alert Condition Monitoring and Notifications
For example, in space management, a warning is given when the table space is 85% full, and a serious warning is given when the table space is 97% full. Although DBA s can write their own scripts, it is difficult to write the first script correctly, and they should run it regularly and keep updating.
There are two forms of Alert: one is stateful, based on conditions, such as full space, SQL execution timeout, etc.; the other is stateless, based on events, such as deadlock, too old snapshot, etc.
To configure alert, you need to set the threshold value, which is stored in AWR. The MMON background process will monitor in real time and compare the current status and threshold value. If the threshold value is exceeded, an alert event will be raised. Alert events are queued for other applications to read. For example, EMCC will take the alert event out of the queue and display it on the screen. You can also choose to send email or SMS.
Alerts can be viewed in the DBA > outgoing > alerts view. The Alert can be taken from the queue through PL/SQL and the operation can be customized.
Setting threshold
For threshold value, hundreds of indicators (19c nearly 400) can be set, recorded in view V$METRICNAME, and operated through DBMS? Server? Alert package.
The stateful Alert will be written to DBA ﹣ outgoing ﹣ alerts. If the DBA fixes the problem or the problem disappears naturally, the Alert will be cleaned up, that is, moved to DBA ﹣ Alert ﹣ history.
Configure thresholds exercise:
-- fixed extent Size, not automatically expanded, so only 8 can be saved extent create tablespace small datafile 'small.dbf' size 1m uniform size 128k; -- Set 1/2 Full warning, 3/4 Full critical warning execute DBMS_SERVER_ALERT.SET_THRESHOLD ( - metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL, - warning_operator => DBMS_SERVER_ALERT.OPERATOR_GT, - warning_value => '50', - critical_operator => DBMS_SERVER_ALERT.OPERATOR_GT, - critical_value => '75', - observation_period => 1, - consecutive_occurrences => 1, - instance_name => NULL, - object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE, - object_name => 'SMALL'); ORCLCDB> select * from dba_thresholds where object_name = 'SMALL'; METRICS_NAME WARNING_OPER WARNING_VA CRITICAL_OPE CRITICAL_V OBSERVATION_PERIOD CONSECUTIVE_OCCURRENCES INSTANCE_NAME OBJECT_TYP OBJECT STATUS ------------------------ ------------ ---------- ------------ ---------- ------------------ ----------------------- ---------------- ---------- ------ ------- Tablespace Space Usage GT 50 GT 75 1 1 database_wide TABLESPACE SMALL VALID ORCLCDB> create table big(c1 date) tablespace small; Table created. ORCLCDB> alter table big allocate extent; Table altered. ORCLCDB> alter table big allocate extent; Table altered. ORCLCDB> alter table big allocate extent; Table altered. ORCLCDB> alter table big allocate extent; Table altered. ORCLCDB> alter table big allocate extent; Table altered. ORCLCDB> alter table big allocate extent; Table altered. ORCLCDB> alter table big allocate extent; alter table big allocate extent * ERROR at line 1: ORA-01653: unable to extend table SYS.BIG by 16 in tablespace SMALL -- query alter, Data will be available in about 10 minutes. This is a fixed configuration of the system and cannot be changed. ORCLCDB> select * from DBA_OUTSTANDING_ALERTS; -- Add data files to eliminate errors alter tablespace small add datafile 'small2.dbf' size 1m; -- Confirm that the error has been eliminated select * from DBA_OUTSTANDING_ALERTS; select * from DBA_ALERT_HISTORY;
USE AUTOMATED TASKS
Automatic task
Automation task refers to the maintenance task (job) that runs automatically. There are three types:
- Collect statistics
- Run SQL Tuning Advisor
- Run Segment Advisor
Statistics help the optimizer plan SQL execution correctly, such as using indexes or full table scans. Information includes the size of the table, the number of unique values, and so on. As the data changes, the statistics are dynamic and need to be updated regularly.
The SQL Tuning advisor determines the SQL with large load and optimizes it with AWR information. The optimization results are stored in the profile (DBA ﹣ SQL ﹣ profiles view), which can be used by the optimizer next time the SQL is executed, but will not be used automatically, which needs to be specified manually.
The Segment recommender locates tables and index segments with a large amount of free space, which can be freed through reorg. Recommendations are not implemented automatically. In addition, when the system is busy, the CPU share can not exceed 25%.
DBA auto task client can view tasks, and DBMS auto task admin package is used for management.
Control automatic tasks
Automatic task operation requires that the parameters statistics? Level be set to TYPICAL or ALL
The automatic task is started by the background process ABP0. The task runs within the specified time window, i.e. from 10:00 p.m. to 4 hours after the start of the working day, and 20 hours after 6:00 a.m. on the weekend.
ORCLCDB> select client_name, status from dba_autotask_client; CLIENT_NAME STATUS ---------------------------------------------------------------- -------- sql tuning advisor ENABLED auto optimizer stats collection ENABLED auto space advisor ENABLED orclpdb1> select dbms_auto_sqltune.report_auto_tuning_task from dual; REPORT_AUTO_TUNING_TASK ------------------------------------------------------------------------------ GENERAL INFORMATION SECTION ---------------------------------------------------- -- No output means no suggestions exec dbms_auto_task_admin.disable('sql tuning advisor', null, null);