Search This Blog

Total Pageviews

Thursday 3 February 2011

Oracle Drill down with ASH ( active session history )

Oracle Drill down with ASH... 



SQL> select distinct wait_class from gv$active_session_history ;
 
WAIT_CLASS
----------------------------------------------------------------
Administrative
Application
Commit
Concurrency
Configuration
Idle
Network
Other
System I/O
User I/O
 
11 rows selected.
 
 
 
set linesize 200 pagesize 500
col sql_text for a100 wrap
select ''''||b.session_id ||','|| b.session_serial#||',@'||b.inst_id ||'''' kill,a.sql_id,b.event, sum(b.time_waited) "time waited",a.sql_text from gv$sqlarea a, gv$active_session_history b
where b.sample_time >= to_timestamp('17.07.2017 15:00:00','dd.mm.yyyy hh24:mi:ss') and b.sample_time <= to_timestamp('17.07.2017 16:00:00','dd.mm.yyyy hh24:mi:ss')
-- and b.wait_class = 'user i/o'
and b.sql_id = a.sql_id
and a.inst_id=b.inst_id
group by ''''||b.session_id ||','|| b.session_serial#||',@'||b.inst_id ||'''',a.sql_id,a.sql_text,b.sql_id,b.event
having sum(b.time_waited)>0
order by 3 desc;
 
 
col kill for a15
select ''''||b.session_id ||','|| b.session_serial#||',@'||b.inst_id ||'''' kill,sql_id,b.sample_time,event,p1 "file#", p2 "block#", p3 "class#"
from gv$active_session_history b
where 1=1
and  b.sample_time >= to_timestamp('17.07.2017 15:00:00','dd.mm.yyyy hh24:mi:ss') and b.sample_time <= to_timestamp('17.07.2017 16:00:00','dd.mm.yyyy hh24:mi:ss')
and event like 'direct path read temp%'
order by 2;
 
 
For Object info
select tablespace_name, owner, segment_name, segment_type from dba_extents
where file_id =206 and 1247360 between block_id and block_id + blocks - 1;
 
 
 
 
set long 10000
select sql_fulltext from gv$sql where sql_id='&sql_id';
 
set linesize 300 pages 2000 long 9999999
select sql_fulltext from gv$sqlarea where sql_id='&sql_id' ;
 
 
 
set pages 0
col sql_text for a32000
prompt ### The Statement (DBA_HIST_SQLTEXT):
select sql_text from dba_hist_sqltext where sql_id='&sql_Id' and rownum=1;
set lines 238
 
 
For Explain Plan
 
 
set pages 300 lines 200
-- Shared Pool
select * from table(dbms_xplan.display_cursor('&SQL_ID',null,'ALL'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID',0,'ALLSTATS LAST'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID',0,'TYPICAL OUTLINE'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID',null,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));
 
 
set pages 300 lines 200
col PLAN_TABLE_OUTPUT for a200
select plan_table_output from   gv$sql s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number,'basic')) t
where sql_id='&sql_id'
 
set pages 300 lines 200
col PLAN_TABLE_OUTPUT for a200
select plan_table_output from   gv$sql s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS')) t
where sql_id='&sql_id'
 
 
set pages 300 lines 200
-- AWR
select * from table(dbms_xplan.display_awr('&SQL_ID',null,null,'ALL'));
select * from table(dbms_xplan.display_awr('&SQL_ID',null,DBID,'ALL'));
-- select * from table(dbms_xplan.display_awr('&1',null,null,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));
 

Oracle datafile More then 20% disk I/O

SELECT
TO_CHAR(sn.end_interval_time,'yyyy-mm-dd HH24:MI:SS') end_interval_time,
new.filename file_name,
new.phywrts-old.phywrts writes
FROM dba_hist_filestatxs old, dba_hist_filestatxs new,
dba_hist_snapshot sn
WHERE
sn.snap_id = (select max(snap_id) from dba_hist_snapshot)
AND new.snap_id = sn.snap_id
AND old.snap_id = sn.snap_id-1
AND new.filename = old.filename
AND (new.phywrts-old.phywrts)*20 > (SELECT(newsnap.value-oldsnap.value) writes
FROM
dba_hist_sysstat oldsnap, dba_hist_sysstat newsnap, dba_hist_snapshot sn1
WHERE
sn.snap_id = sn1.snap_id
AND newsnap.snap_id = sn.snap_id
AND oldsnap.snap_id = sn.snap_id-1
AND oldsnap.stat_name = 'physical writes'
AND newsnap.stat_name = 'physical writes'
AND (newsnap.value-oldsnap.value) > 0);

Oracle ADDM Report for last 24 hr .....




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;




tnsnames.ora and listener.ora

ANUJ =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.210)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = APTDB)
)
)



solomon =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.69)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = jostest)
)
)



solmon =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.69)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = jostest)
)
)


======



oracle@novagenesis$ cat listener.ora
# listener.ora Network Configuration File: /aptus/oracle/product/10g/network/admin/listener.ora
# Generated by Oracle configuration tools.

# (ADDRESS = (PROTOCOL = TCP)(HOST = genesis.aptus.co.uk )(PORT = 1521))

#SID_LIST_LISTENER =
# (SID_LIST =
# (SID_DESC =
# (SID_NAME = PLSExtProc)
# (ORACLE_HOME = /tus/oracle/product/10g)
# (PROGRAM = extproc)
# )
# )

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.210 )(PORT = 1521))
)
)

#APTDB =
# (DESCRIPTION_LIST =
# (DESCRIPTION =
# (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.4 )(PORT = 1521))
# )
# )

#TRACE_LEVEL_CLIENT = SUPPORT
#TRACE_TIMESTAMP_CLIENT = ON
#TRACE_LEVEL_LISTENER=16
#TRACE_TIMESTAMP_LISTENER=TRUE
#TRACE_DIRECTORY_LISTENER=/tus/oracle/product/10g/network/admin
#TRACE_FILELEN_LISTENER=500000
#TRACE_FILENO_LISTENER=10

Oracle DBA

anuj blog Archive