VARIABLE bid NUMBER
VARIABLE eid NUMBER
VARIABLE DBID NUMBER
VARIABLE inst_num number
exec select max(snap_id) -24 into :bid from dba_hist_snapshot ;
exec select max(snap_id) into :eid from dba_hist_snapshot ;
exec select DBID into :DBID from v$database;
exec select INSTANCE_NUMBER into :inst_num from v$instance ;
DECLARE
task_name VARCHAR2(30) := 'ADDM_ANUJ';
task_desc VARCHAR2(30) := 'ADDM ANUJ';
task_id NUMBER;
BEGIN
dbms_advisor.create_task('ADDM', task_id, task_name, task_desc, null);
dbms_advisor.set_task_parameter('ADDM_ANUJ', 'START_SNAPSHOT', :bid);
dbms_advisor.set_task_parameter('ADDM_ANUJ', 'END_SNAPSHOT', :eid);
dbms_advisor.set_task_parameter('ADDM_ANUJ', 'INSTANCE', :inst_num);
dbms_advisor.set_task_parameter('ADDM_ANUJ', 'DB_ID', :DBID );
dbms_advisor.execute_task('ADDM_ANUJ');
END;
/
PL/SQL procedure successfully completed.
SET LONG 1000000
SET PAGES 0
SET LONGCHUNKSIZE 1000
COL get_clob FORMAT a80
SELECT DBMS_ADVISOR.GET_TASK_REPORT('ADDM_ANUJ','TEXT','TYPICAL') FROM dual;
DETAILED ADDM REPORT FOR TASK 'ADDM_ANUJ' WITH ID 3300
------------------------------------------------------
Analysis Period: from 02-FEB-2011 14:00 to 03-FEB-2011 14:00
Database ID/Instance: 1257031792/1
Database/Instance Names: ORCL/orcl
Host Name: apt-lnxtst-01
Database Version: 10.2.0.4.0
Snapshot Range: from 3052 to 3076
Database Time: 316 seconds
Average Database Load: 0 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 36% impact (114 seconds)
-----------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 15% benefit (48 seconds)
ACTION: Investigate the SQL statement with SQL_ID "b6usrg82hwsa3" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID b6usrg82hwsa3
call dbms_stats.gather_database_stats_job_proc ( )
RATIONALE: SQL statement with SQL_ID "b6usrg82hwsa3" was executed 1
times and had an average elapsed time of 47 seconds.
RECOMMENDATION 2: SQL Tuning, 7.2% benefit (23 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "cb75rw3w1tt0s". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID cb75rw3w1tt0s
begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end;
RATIONALE: SQL statement with SQL_ID "cb75rw3w1tt0s" was executed 51753
times and had an average elapsed time of 0.00072 seconds.
RECOMMENDATION 3: SQL Tuning, 7.1% benefit (22 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "6gvch1xu9ca3g". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID 6gvch1xu9ca3g
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN
EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF
broken THEN :b := 1; ELSE :b := 0; END IF; END;
RATIONALE: SQL statement with SQL_ID "6gvch1xu9ca3g" was executed 1441
times and had an average elapsed time of 0.019 seconds.
RECOMMENDATION 4: SQL Tuning, 4.6% benefit (14 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"bunssq950snhf".
RELEVANT OBJECT: SQL statement with SQL_ID bunssq950snhf and
PLAN_HASH 2694099131
insert into wrh$_sga_target_advice (snap_id, dbid, instance_number,
SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select
:snap_id, :dbid, :instance_number, SGA_SIZE, SGA_SIZE_FACTOR,
ESTD_DB_TIME, ESTD_PHYSICAL_READS from v$sga_target_advice
RATIONALE: SQL statement with SQL_ID "bunssq950snhf" was executed 24
times and had an average elapsed time of 0.6 seconds.
RECOMMENDATION 5: SQL Tuning, 4.5% benefit (14 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"8hk7xvhua40va".
RELEVANT OBJECT: SQL statement with SQL_ID 8hk7xvhua40va
INSERT INTO MGMT_METRICS_RAW(COLLECTION_TIMESTAMP, KEY_VALUE,
METRIC_GUID, STRING_VALUE, TARGET_GUID, VALUE) VALUES ( :1, NVL(:2, '
'), :3, :4, :5, :6)
RATIONALE: SQL statement with SQL_ID "8hk7xvhua40va" was executed 2452
times and had an average elapsed time of 0.0057 seconds.
FINDING 2: 17% impact (55 seconds)
----------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.
RECOMMENDATION 1: Application Analysis, 17% benefit (55 seconds)
ACTION: Investigate application logic for possible reduction in the
number of COMMIT operations by increasing the size of transactions.
RATIONALE: The application was performing 11 transactions per minute
with an average redo size of 8087 bytes per transaction.
RECOMMENDATION 2: Host Configuration, 17% benefit (55 seconds)
ACTION: Investigate the possibility of improving the performance of I/O
to the online redo log files.
RATIONALE: The average size of writes to the online redo log files was 6
K and the average time per write was 6 milliseconds.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Commit" was consuming significant database time.
(17% impact [55 seconds])
FINDING 3: 16% impact (50 seconds)
----------------------------------
PL/SQL execution consumed significant database time.
RECOMMENDATION 1: SQL Tuning, 15% benefit (48 seconds)
ACTION: Investigate the SQL statement with SQL_ID "b6usrg82hwsa3" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID b6usrg82hwsa3
call dbms_stats.gather_database_stats_job_proc ( )
RATIONALE: SQL statement with SQL_ID "b6usrg82hwsa3" was executed 1
times and had an average elapsed time of 47 seconds.
RATIONALE: Average time spent in PL/SQL execution was 6.5 seconds.
RECOMMENDATION 2: SQL Tuning, 4.8% benefit (15 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "cb75rw3w1tt0s". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID cb75rw3w1tt0s
begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end;
RATIONALE: SQL statement with SQL_ID "cb75rw3w1tt0s" was executed 51753
times and had an average elapsed time of 0.00072 seconds.
RATIONALE: Average time spent in PL/SQL execution was 0.00029 seconds.
RECOMMENDATION 3: SQL Tuning, 3.4% benefit (11 seconds)
ACTION: Investigate the SQL statement with SQL_ID "6mcpb06rctk0x" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 6mcpb06rctk0x
call dbms_space.auto_space_advisor_job_proc ( )
RATIONALE: SQL statement with SQL_ID "6mcpb06rctk0x" was executed 1
times and had an average elapsed time of 10 seconds.
RATIONALE: Average time spent in PL/SQL execution was 9.3 seconds.
RECOMMENDATION 4: SQL Tuning, 3.3% benefit (10 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "2b064ybzkwf1y". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID 2b064ybzkwf1y
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
RATIONALE: SQL statement with SQL_ID "2b064ybzkwf1y" was executed 2869
times and had an average elapsed time of 0.0037 seconds.
RATIONALE: Average time spent in PL/SQL execution was 0.0035 seconds.
RECOMMENDATION 5: SQL Tuning, 3.1% benefit (10 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"8szmwam7fysa3".
RELEVANT OBJECT: SQL statement with SQL_ID 8szmwam7fysa3 and
PLAN_HASH 2976124318
insert into wri$_adv_objspace_trend_data select timepoint,
space_usage, space_alloc, quality from
table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL,
NULL, 'FALSE', :5, 'FALSE'))
RATIONALE: SQL statement with SQL_ID "8szmwam7fysa3" was executed 13
times and had an average elapsed time of 0.73 seconds.
RATIONALE: Average time spent in PL/SQL execution was 0.71 seconds.
FINDING 4: 6.7% impact (21 seconds)
-----------------------------------
The SGA was inadequately sized, causing additional I/O or hard parses.
RECOMMENDATION 1: DB Configuration, 4.4% benefit (14 seconds)
ACTION: Increase the size of the SGA by setting the parameter
"sga_target" to 640 M.
ADDITIONAL INFORMATION:
The value of parameter "sga_target" was "512 M" during the analysis
period.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(8% impact [25 seconds])
FINDING 5: 3.3% impact (10 seconds)
-----------------------------------
Soft parsing of SQL statements was consuming significant database time.
RECOMMENDATION 1: Application Analysis, 3.3% benefit (10 seconds)
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: DB Configuration, 3.3% benefit (10 seconds)
ACTION: Consider increasing the maximum number of open cursors a session
can have by increasing the value of parameter "open_cursors".
ACTION: Consider increasing the session cursor cache size by increasing
the value of parameter "session_cached_cursors".
RATIONALE: The value of parameter "open_cursors" was "300" during the
analysis period.
RATIONALE: The value of parameter "session_cached_cursors" was "20"
during the analysis period.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ADDITIONAL INFORMATION
----------------------
Wait class "Application" 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.
Hard parsing of SQL statements was not consuming significant database time.
The database's maintenance windows were active during 33% of the analysis
period.
====
for PDB
alter session set container=ANUJ;
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '5bbqkc6pmfhwy',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => '5bbqkc6pmfhwy_tuning_task',
description => 'Tuning task for statement 5bbqkc6pmfhwy');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
2. Execute Tuning task:
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '5bbqkc6pmfhwy_tuning_task');
3. Get the Tuning advisor report.
set long 50000 longchunksize 5000 linesize 300
col REPORT_TUNING_TASK for a100 wrap
select dbms_sqltune.report_tuning_task('5bbqkc6pmfhwy_tuning_task') REPORT_TUNING_TASK from dual;
====================
--- Finding All Snapshot List
SQL>
SELECT SNAP_ID,
SNAP_LEVEL,BEGIN_INTERVAL_TIME,
TO_CHAR(BEGIN_INTERVAL_TIME, 'dd/mm/yy hh24:mi:ss') BEGIN
FROM
DBA_HIST_SNAPSHOT
ORDER BY SNAP_ID desc;
SNAP_ID SNAP_LEVEL BEGIN_INTERVAL_TIME BEGIN
---------- ---------- --------------------------------------------------------------------------- -----------------
39004 2 18-JAN-22 02.00.23.142 AM 18/01/22 02:00:23
39004 2 18-JAN-22 02.00.23.175 AM 18/01/22 02:00:23
39003 2 18-JAN-22 01.00.13.555 AM 18/01/22 01:00:13
39003 2 18-JAN-22 01.00.13.591 AM 18/01/22 01:00:13
39002 2 18-JAN-22 12.00.04.937 AM 18/01/22 00:00:04
39002 2 18-JAN-22 12.00.04.996 AM 18/01/22 00:00:04
39001 2 17-JAN-22 11.00.07.027 PM 17/01/22 23:00:07
39001 2 17-JAN-22 11.00.06.984 PM 17/01/22 23:00:06
39000 2 17-JAN-22 10.00.48.335 PM 17/01/22 22:00:48
39000 2 17-JAN-22 10.00.48.366 PM 17/01/22 22:00:48
--- Executing Oracle Package DBMS_ADVISOR to Generate advisory task.
SQL>
BEGIN
-- Create an ADDM task.
DBMS_ADVISOR.create_task (
advisor_name => 'ADDM',
task_name => '39001_39003_AWR_SNAP',
task_desc => 'Advisor for snapshots 39001 to 39003');
-- Set the start and end snapshots.
DBMS_ADVISOR.set_task_parameter (
task_name => '39001_39003_AWR_SNAP',
parameter => 'START_SNAPSHOT',
value => 39001);
DBMS_ADVISOR.set_task_parameter (
task_name => '39001_39003_AWR_SNAP',
parameter => 'END_SNAPSHOT',
value => 39004);
-- Execute the task.
DBMS_ADVISOR.execute_task(task_name => '39001_39003_AWR_SNAP');
END;
PL/SQL procedure successfully completed.
------ Showing ADDM Report data.
SQL>
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report('39001_39003_AWR_SNAP') AS report FROM dual;
====
col SQL_ID form a16
col Benefit form 9999999999999
select * from (
select b.ATTR1 as SQL_ID, max(a.BENEFIT) as "Benefit"
from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b
where a.REC_ID = b.OBJECT_ID
and a.TASK_ID = b.TASK_ID
and a.TASK_ID in (select distinct b.task_id
from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l
where a.begin_interval_time > sysdate - 7
and a.dbid = (select dbid from v$database)
and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)
and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24')
and b.advisor_name = 'ADDM'
and b.task_id = l.task_id
and l.status = 'COMPLETED')
and length(b.ATTR4) > 1
group by b.ATTR1
order by max(a.BENEFIT) desc) where rownum < 6;
/
or
-- with task name
col SQL_ID form a16
col Benefit form 9999999999999
select * from (
select b.ATTR1 as SQL_ID,a.TASK_NAME, max(a.BENEFIT) as "Benefit"
from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b
where a.REC_ID = b.OBJECT_ID
and a.TASK_ID = b.TASK_ID
and a.TASK_ID in (select distinct b.task_id
from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l
where a.begin_interval_time > sysdate - 7
and a.dbid = (select dbid from v$database)
and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)
and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24')
and b.advisor_name = 'ADDM'
and b.task_id = l.task_id
and l.status = 'COMPLETED')
and length(b.ATTR4) > 1
group by b.ATTR1,a.TASK_NAME
order by max(a.BENEFIT) desc) where rownum < 6;
SQL_ID TASK_NAME Benefit
---------------- ------------------------------ --------------
g8ayhu2g4wh1f ADDM:3316548098_1_571783 14128696816
1rn43zb7tm2jg ADDM:3316548098_1_571783 14128696816
1rn43zb7tm2jg ADDM:3316548098_1_571785 13952920387
9783wcf3s3634 ADDM:3316548098_1_571646 13662000000
87a333ujtm7q3 ADDM:3316548098_1_571862 13178202405
set linesize 300 pagesize 300
col TASK_NAME for a30
select task_name, execution_end ,task_id from dba_advisor_tasks
where advisor_name='ADDM'
and status='COMPLETED'
and owner='SYS'
--and instr(TASK_NAME,'_3_',1,1)=xx
order by execution_end desc;
/
TASK_NAME EXECUTION TASK_ID
------------------------------ --------- ----------
ADDM:1222414252_2_40551 23-MAR-14 164533
from above task name !!!!
SET linesize 300 LONG 999999 pages 1000 longchunksize 999999
col MESSAGE for a50 wrap
col IMPACT_TYPE for a30
SELECT
r.type,
r.Rank,
r.benefit,
f.impact_type,
f.impact,
f.message
FROM
dba_advisor_recommendations r,
dba_advisor_findings f
WHERE 1=1
and r.task_name = 'ADDM:1222414252_2_40551'
AND r.finding_id = f.finding_id
AND r.task_id = f.task_id
ORDER BY r.rank ASC, r.benefit DESC;
/
SET linesize 300 LONG 999999 pages 1000 longchunksize 999999
select DBMS_ADVISOR.GET_TASK_REPORT('ADDM:1222414252_2_40551', 'TEXT', 'TYPICAL', 'ALL', 'SYS') from dual;
/
====
ADDM
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot where DBID= (select DBID from v$database);
exec select max(snap_id) into :EndSnap from dba_hist_snapshot where DBID= (select DBID from v$database);
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
DECLARE
task_name VARCHAR2(30) := 'SYSTEM_ADDM';
task_desc VARCHAR2(30) := 'ADDM Feature Test';
task_id NUMBER;
BEGIN
select count(*)
into task_id
from dba_advisor_tasks
where task_name = 'SYSTEM_ADDM';
if task_id = 0 then
dbms_advisor.create_task('ADDM', task_id, task_name, task_desc, null);
else
dbms_advisor.reset_task(task_name => 'SCOTT_ADDM');
end if;
dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'START_SNAPSHOT', :BgnSnap);
dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'END_SNAPSHOT', :EndSnap);
dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'INSTANCE', :INST_NUMBER);
dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'DB_ID', :DID);
dbms_advisor.execute_task('SYSTEM_ADDM');
END;
/
select dbms_advisor.get_task_report('SYSTEM_ADDM', 'TEXT', 'ALL') from dual;
