Go back to OCM 12c Preparation Project
Hands On Lab – Index
[1] Measuring Database Performance
[2] Gathering Database Statistics
[3] Automatic Performance Diagnostics
[4] Comparing Database Performance Over Time
[5] Analyzing Sampled Data
[1] Measuring Database Performance -> About Database Statistics #Time Model Statistics (V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views). #Active Session History Statistics V$ACTIVE_SESSION_HISTORY DBA_HIST_ACTIVE_SESS_HISTORY Note: Active session history sampling is also available for Active Data Guard physical standby instances and Oracle Automatic Storage Management ( Oracle ASM) instances. On these instances, the current session activity is collected and displayed in the V$ACTIVE_SESSION_HISTORY view, but not written to disk. #Wait Events Statistics The V$SYSTEM_EVENT view shows wait event statistics for the foreground activities of a database instance and the wait event statistics for the database instance. The V$SYSTEM_WAIT_CLASS view shows these foreground and wait event statistics at the instance level after aggregating to wait classes. V$SESSION_EVENT and V$SESSION_WAIT_CLASS show wait event and wait class statistics at the session level. #Session and System Statistics A large number of cumulative database statistics on a system and session level are accessible using the V$SYSSTAT and V$SESSTAT views. -> Interpreting Database Statistics Using Hit Ratios Using Wait Events with Timed Statistics Using Wait Events without Timed Statistics Using Idle Wait Events Comparing Database Statistics with Other Factors Using Computed Statistics [2] Gathering Database Statistics -> About Gathering Database Statistics By default, Oracle Database captures snapshots once every hour and retains them in the database for 8 days. With these default settings, a typical system with an average of 10 concurrent active sessions can require approximately 200 to 300 MB of space for its AWR data. DBA_HIST_WR_CONTROL -> Managing the Automatic Workload Repository #Creating Snapshots Using the Command-Line Interface BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); END; / #Dropping Snapshots Using the Command-Line Interface BEGIN DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 22, high_snap_id => 32, dbid => 3310949047); END; / #Modifying Snapshot Settings BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200, interval => 30, topnsql => 100, dbid => 3310949047); END; / The retention period is specified as 43200 minutes (30 days). The interval between each snapshot is specified as 30 minutes. The number of Top SQL to flush for each SQL criteria is specified as 100. To verify the current settings for your database, use the DBA_HIST_WR_CONTROL view. ------------------ Managing Baselines ------------------ #Creating a Baseline BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 270, end_snap_id => 280, baseline_name => 'peak baseline', dbid => 3310949047, expiration => 30); END; / BEGIN DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'peak baseline', cascade => FALSE, dbid => 3310949047); END; / BEGIN DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE ( old_baseline_name => 'peak baseline', new_baseline_name => 'peak mondays', dbid => 3310949047); END; / BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE ( window_size => 30, dbid => 3310949047); END; / ---------------------------- Managing Baseline Templates ---------------------------- -Creating a Single Baseline Template BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE ( start_time => '2012-04-02 17:00:00 PST', end_time => '2012-04-02 20:00:00 PST', baseline_name => 'baseline_120402', template_name => 'template_120402', expiration => 30, dbid => 3310949047); END; / -Creating a Repeating Baseline Template BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE ( day_of_week => 'monday', hour_in_day => 17, duration => 3, expiration => 30, start_time => '2012-04-02 17:00:00 PST', end_time => '2012-12-31 20:00:00 PST', baseline_name_prefix => 'baseline_2012_mondays_', template_name => 'template_2012_mondays', dbid => 3310949047); END; / -Dropping a Baseline Template BEGIN DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE ( template_name => 'template_2012_mondays', dbid => 3310949047); END; / #Transporting Automatic Workload Repository Data Extracting AWR Data @$ORACLE_HOME/rdbms/admin/awrextr.sql Loading AWR Data @$ORACLE_HOME/rdbms/admin/awrload.sql #Using Automatic Workload Repository Views DBA_HIST_ACTIVE_SESS_HISTORY DBA_HIST_BASELINE DBA_HIST_BASELINE_DETAILS DBA_HIST_BASELINE_TEMPLATE DBA_HIST_DATABASE_INSTANCE DBA_HIST_DB_CACHE_ADVICE DBA_HIST_DISPATCHER DBA_HIST_DYN_REMASTER_STATS DBA_HIST_IOSTAT_DETAIL DBA_HIST_SHARED_SERVER_SUMMARY DBA_HIST_SNAPSHOT DBA_HIST_SQL_PLAN DBA_HIST_WR_CONTROL -> Generating Automatic Workload Repository Reports Generating an AWR Report for the Local Database @$ORACLE_HOME/rdbms/admin/awrrpt.sql Generating an AWR Report for a Specific Database @$ORACLE_HOME/rdbms/admin/awrrpti.sql Generating an Oracle RAC AWR Report for the Local Database @$ORACLE_HOME/rdbms/admin/awrgrpt.sql Generating an Oracle RAC AWR Report for a Specific Database @$ORACLE_HOME/rdbms/admin/awrgrpti.sql Generating an AWR Report for a SQL Statement on the Local Database @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql Generating an AWR Report for a SQL Statement on a Specific Database @$ORACLE_HOME/rdbms/admin/awrsqrpi.sql -> Generating Performance Hub Active Report Performance Hub feature of EM Express provides an active report with a consolidated view of all performance data for a specified time period. The report is fully interactive; its contents are saved in a HTML file, which you can access offline using a web browser. To generate a Performance Hub active report: @$ORACLE_HOME/rdbms/admin/perfhubrpt.sql [3] Automatic Performance Diagnostics -> Overview of the Automatic Database Diagnostic Monitor #ADDM Analysis An ADDM analysis can be performed on a pair of AWR snapshots and a set of instances from the same database. The pair of AWR snapshots define the time period for analysis, and the set of instances define the target for analysis. If you are using Oracle Real Application Clusters (Oracle RAC), then ADDM has three analysis modes: 1. Database In Database mode, ADDM analyzes all instances of the database. 2. Instance In Instance mode, ADDM analyzes a particular instance of the database. 3. Partial In Partial mode, ADDM analyzes a subset of all database instances. An ADDM analysis is performed each time an AWR snapshot is taken and the results are saved in the database. The time period analyzed by ADDM is defined by the last two snapshots (the last hour by default). ADDM will always analyze the specified instance in Instance mode. #Using ADDM with Oracle Real Application Clusters If you are using Oracle RAC, then run ADDM in Database analysis mode to analyze the throughput performance of all instances of the database. In Database mode, ADDM considers DB time as the sum of the database time for all database instances. Using the Database analysis mode enables you to view all findings that are significant to the entire database in a single report, instead of reviewing a separate report for each instance. The Database mode report includes findings about database resources (such as I/O and interconnect). The report also aggregates findings from the various instances if they are significant to the entire database. For example, if the CPU load on a single instance is high enough to affect the entire database, then the finding appears in the Database mode analysis, which points to the particular instance responsible for the problem. #Real-Time ADDM Analysis Introduced in Oracle Enterprise Manager Cloud Control 12c, Real-Time ADDM helps you to analyze and resolve problems in unresponsive or hung databases that traditionally require you to restart the database. Real-Time ADDM runs through a set of predefined criteria to analyze the current performance of the database. After analyzing the problem, Real-Time ADDM helps you to resolve the identified issues—such as deadlocks, hangs, shared pool contention, and other exception situations—without having to restart the database. 1. Real-Time ADDM Connection Modes Normal connection In this mode, Real-Time ADDM performs a normal JDBC connection to the database. This mode is intended to perform extensive performance analysis of the database when some connectivity is available. Diagnostic connection In this mode, Real-Time ADDM performs a latch-less connection to the database. This mode is intended for extreme hang situations when a normal JDBC connection is not possible. 2. Real-Time ADDM Triggers Starting with Oracle Database 12c, Real-Time ADDM proactively detects transient database performance issues. To do this, Real-Time ADDM runs automatically every 3 seconds and uses in-memory data to diagnose any performance spikes in the database. Real-Time ADDM triggers an analysis automatically when a performance problem is detected. 1. Every 3 seconds, the manageability monitor process (MMON) performs an action to obtain performance statistics without lock or latch. 2. The MMON process checks these statistics and triggers a Real-Time ADDM analysis if any of the issues listed in Table 7-1 are detected. 3. The MMON slave process creates the report and stores it in the AWR. To view metadata for the report, use the DBA_HIST_REPORTS view. #Real-Time ADDM Trigger Controls Duration between reports Oracle RAC control Repeated triggers Newly identified issues -> Setting Up ADDM CONTROL_MANAGEMENT_PACK_ACCESS The default setting is DIAGNOSTIC+TUNING. Setting CONTROL_MANAGEMENT_PACK_ACCESS to NONE disables ADDM. The STATISTICS_LEVEL parameter should be set to the TYPICAL or ALL to enable automatic database diagnostic monitoring. The default setting is TYPICAL. Setting STATISTICS_LEVEL to BASIC disables many Oracle Database features, including ADDM, and is strongly discouraged. ADDM analysis of I/O performance partially depends on a single argument, DBIO_EXPECTED, that describes the expected performance of the I/O subsystem. The value of DBIO_EXPECTED is the average time it takes to read a single database block in microseconds. Oracle Database uses the default value of 10 milliseconds, which is an appropriate value for most modern hard drives. If your hardware is significantly different—such as very old hardware or very fast RAM disks—then consider using a different value. For example, if the measured value if 8000 microseconds, you should execute the following command as SYS user: EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER( 'ADDM', 'DBIO_EXPECTED', 8000); -> Diagnosing Database Performance Problems with ADDM 1. Running ADDM in Database Mode VAR tname VARCHAR2(30); BEGIN :tname := 'ADDM for 7PM to 9PM'; DBMS_ADDM.ANALYZE_DB(:tname, 137, 145); END; / 2. Running ADDM in Instance Mode VAR tname VARCHAR2(30); BEGIN :tname := 'my ADDM for 7PM to 9PM'; DBMS_ADDM.ANALYZE_INST(:tname, 137, 145, 1); END; / 3. Running ADDM in Partial Mode VAR tname VARCHAR2(30); BEGIN :tname := 'my ADDM for 7PM to 9PM'; DBMS_ADDM.ANALYZE_PARTIAL(:tname, '1,2,4', 137, 145); END; / 4. Displaying an ADDM Report SET LONG 1000000 PAGESIZE 0; SELECT DBMS_ADDM.GET_REPORT(:tname) FROM DUAL; -> Views with ADDM Information DBA_ADVISOR_FINDINGS DBA_ADDM_FINDINGS DBA_ADVISOR_FINDING_NAMES DBA_ADVISOR_RECOMMENDATIONS DBA_ADVISOR_TASKS addmrpt.sql run ADDM analysis on a pair on AWR snapshots and to display the textual ADDM report of the analysis. addmrpti.sql SQL*Plus script that prompts for dbid and instance_number to run ADDM analysis on a pair on AWR snapshots and display the textual ADDM report of the analysis. [4] Comparing Database Performance Over Time -> About Automatic Workload Repository Compare Periods Reports -> Generating Automatic Workload Repository Compare Periods Reports Generating an AWR Compare Periods Report for the Local Database @$ORACLE_HOME/rdbms/admin/awrddrpt.sql Generating an AWR Compare Periods Report for a Specific Database @$ORACLE_HOME/rdbms/admin/awrddrpi.sql Generating an Oracle RAC AWR Compare Periods Report for the Local Database @$ORACLE_HOME/rdbms/admin/awrgdrpt.sql Generating an Oracle RAC AWR Compare Periods Report for a Specific Database @$ORACLE_HOME/rdbms/admin/awrgdrpi.sql -> Interpreting Automatic Workload Repository Compare Periods Reports [5] Analyzing Sampled Data -> About Active Session History -> Generating Active Session History Reports Generating an ASH Report on the Local Database Instance @$ORACLE_HOME/rdbms/admin/ashrpt.sql Generating an ASH Report on a Specific Database Instance @$ORACLE_HOME/rdbms/admin/ashrpti.sql Generating an ASH Report for Oracle RAC @$ORACLE_HOME/rdbms/admin/ashrpti.sql -> Interpreting Results from Active Session History Reports ====================== Path to Documentation: ====================== Part II Diagnosing and Tuning Database Performance 5 Measuring Database Performance 6 Gathering Database Statistics 7 Automatic Performance Diagnostics 8 Comparing Database Performance Over Time 9 Analyzing Sampled Data
Thank you for visiting this blog 🙂
