The ADDM analysis includes the following.
CPU load
Memory usage
I/O usage
Resource intensive SQL
Resource intensive PL/SQL and Java
RAC issues
Application issues
Database configuration issues
Concurrency issues
Object contention
Note:
The analysis of I/O performance is affected by the DBIO_EXPECTED parameter which should be set to the average time (in microseconds) it takes to read a single database block from disk. Typical values range from 5000 to 20000 microseconds. The parameter can be set using the following.
To activate:
alter system set statistics_level = typical;
alter system set statistics_level = all;
To De-active:
alter system set statistics_level = basic;
The addmrpt.sql script can be used to create an ADDM report from SQL*Plus.
-- Windows
@C:\app\oracle\product\12.1.0\dbhome_2\rdbms\admin\addmrpt.sql
(the script asks for a range of snapshots (without a restart in between), adn the output report filename. The report is created in the local directory unless you state otherwise).
The basic SQL for this report is listed below, just with a few parameters and a spool slapped around it.
BEGIN
-- Create an ADDM task.
DBMS_ADVISOR.create_task (
advisor_name => 'ADDM',
task_name => '455_478_AWR_SNAPSHOT',
task_desc => 'Advisor for snapshots 455 to 478.');
-- Set the start and end snapshots.
DBMS_ADVISOR.set_task_parameter (
task_name => '455_478_AWR_SNAPSHOT',
parameter => 'START_SNAPSHOT',
value => 455);
DBMS_ADVISOR.set_task_parameter (
task_name => '455_478_AWR_SNAPSHOT',
parameter => 'END_SNAPSHOT',
value => 478);
-- Execute the task.
DBMS_ADVISOR.execute_task(task_name => '455_478_AWR_SNAPSHOT');
END;
/
-- Display the report.
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report('455_478_AWR_SNAPSHOT') AS report
FROM dual;
SET PAGESIZE 24
The snapshots are controlled by dba_hist_wr_control. This seems to be hourly.
col snap_interval for a20
col retention for a20
select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID
---------- -------------------- -------------------- ---------- ----------
3043802518 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT 1
To change the collection settings (interval is in minutes, retention in seconds)
exec dbms_workload_repository.modify_snapshot_settings ( interval => 60, retention => 43200);
To display snapshots
col begin_interval_time for a25
col end_interval_time for a25
select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1;
Create a snapshot : exec dbms_workload_repository.create_snapshot;
Delete snapshots : exec dbms_workload_repository.drop_snapshot_range (low_snap_id=>455, high_snap_id=>478);
The report itself.....
-------------------------------
Analysis Period
---------------
AWR snapshot range from 455 to 478.
Time period starts at 31-JUL-14 11.04.16
Time period ends at 01-AUG-14 10.00.34
Analysis Target
---------------
Database 'JHCDB' with DB ID 3043802518.
Database version 12.1.0.1.0.
ADDM performed an analysis of instance jhcdb, numbered 1 and hosted at
Serverxxx
Activity During the Analysis Period
-----------------------------------
Total database time was 742 seconds.
The average number of active sessions was .01.
Summary of Findings
-------------------
Description Active Sessions Recommendations
Percent of Activity
--------------------- ------------------- ---------------
1 Top SQL Statements .01 | 61.11 2
2 "User I/O" wait Class 0 | 7.69 0
3 Hard Parse 0 | 4.3 0
4 Soft Parse 0 | 2.45 2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and Recommendations
----------------------------
Finding 1: Top SQL Statements
Impact is .01 active sessions, 61.11% of total activity.
--------------------------------------------------------
SQL statements consuming significant database time were found. These
statements offer a good opportunity for performance improvement.
Recommendation 1: SQL Tuning
Estimated benefit is 0 active sessions, 31.94% of total activity.
-----------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"bj08huw9tc01y".
Related Object
SQL statement with SQL_ID bj08huw9tc01y.
select /*+ no_monitor */ CON_ID, OBJOID, CLSOID, RUNTIME, PRI,
JOBTYPE, SCHLIM, WT, INST, RUNNOW, ENQ_SCHLIM from ( select
...
DESC, OBJOID
Action
Use bigger fetch arrays while fetching results from the SELECT statement
with SQL_ID "bj08huw9tc01y".
Related Object
SQL statement with SQL_ID bj08huw9tc01y.
select /*+ no_monitor */ CON_ID, OBJOID, CLSOID, RUNTIME, PRI,
...
DESC, OBJOID
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "bj08huw9tc01y" was executed 60 times and had
an average elapsed time of 5.5 seconds.
Rationale
At least one execution of the statement ran in parallel.
Recommendation 2: SQL Tuning
Estimated benefit is 0 active sessions, 29.17% of total activity.
-----------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"bj08huw9tc01y".
Related Object
SQL statement with SQL_ID bj08huw9tc01y.
select /*+ no_monitor */ CON_ID, OBJOID, CLSOID, RUNTIME, PRI,
JOBTYPE, SCHLIM, WT, INST, RUNNOW, ENQ_SCHLIM from ( select
...
and ik.logins = 'ALLOWED') order by RUNTIME, JOBTYPE, CLSOID, PRI, WT
DESC, OBJOID
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Finding 2: "User I/O" wait Class
Impact is 0 active sessions, 7.69% of total activity.
-----------------------------------------------------
Wait class "User I/O" was consuming significant database time.
The throughput of the I/O subsystem was not significantly lower than expected.
The Oracle instance memory (SGA and PGA) was adequately sized.
No recommendations are available.
Finding 3: Hard Parse
Impact is 0 active sessions, 4.3% of total activity.
----------------------------------------------------
Hard parsing of SQL statements was consuming significant database time.
Hard parses due to cursor environment mismatch were not consuming significant
database time.
Hard parsing SQL statements that encountered parse errors was not consuming
significant database time.
Hard parses due to literal usage and cursor invalidation were not consuming
significant database time.
The Oracle instance memory (SGA and PGA) was adequately sized.
No recommendations are available.
Finding 4: Soft Parse
Impact is 0 active sessions, 2.45% of total activity.
-----------------------------------------------------
Soft parsing of SQL statements was consuming significant database time.
Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 2.45% of total activity.
----------------------------------------------------------------
Action
Investigate application logic to keep open the frequently used cursors.
Note that cursors are closed by both cursor close calls and session
disconnects.
Recommendation 2: Database Configuration
Estimated benefit is 0 active sessions, 2.45% of total activity.
----------------------------------------------------------------
Action
Consider increasing the session cursor cache size by increasing the
value of parameter "session_cached_cursors".
Rationale
The value of parameter "session_cached_cursors" was "50" during the
analysis period.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
CPU was not a bottleneck for the instance.
Wait class "Network" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.
The database's maintenance windows were active during 17% of the analysis
period.
Happyjohn
No comments:
Post a Comment