unix delete mail
solaris delete mail
on linux
$mail
then
d *
this will delete all the mail for the user
===============
On solaris
now it is Oracle solaris
ln-ora-01# uname
SunOS
ln-ora-01# uname -a
SunOS ln-ora-01 5.10 Generic_137111-07 sun4u sparc SUNW,Sun-Fire-880
ln-ora-01# mailx -N give any number like 1-10 mails
? d 1-10
? q
Held 282 messages in /var/mail//root
ln-ora-01# mailx -N
? d 1-282
? q
ln-ora-01# mail
No mail.
Friday, 1 July 2011
Thursday, 30 June 2011
Unix Kill all the process for the user
latest command
Don't try this command on prod ...
$ kill -9 %1
Don't try this command on prod ...
$ kill -9 %1
Oracle Sort Info
Oracle Sort Info In PGA as well
select a.event,
a.sid,
c.serial# ,
c.sql_hash_value hash_value,
decode(d.ktssosegt,1,'SORT', 2,'HASH',3,'DATA',4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX',null) as segment_type,
b.tablespace_name,
b.file_name
from v$session_wait a, dba_data_files b, v$session c, x$ktsso d
where c.saddr = d.ktssoses(+)
and c.serial# = d.ktssosno(+)
and d.inst_id(+) = userenv('instance')
and a.sid = c.sid
and a.p1 = b.file_id
and a.event = 'direct path read'
union all
select a.event,
a.sid,
d.serial# ,
d.sql_hash_value hash_value,
decode(e.ktssosegt,1,'SORT', 2,'HASH',3,'DATA',4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX', null) as segment_type,
b.tablespace_name,
b.file_name
from v$session_wait a, dba_temp_files b, v$parameter c, v$session d, x$ktsso e
where d.saddr = e.ktssoses(+)
and d.serial# = e.ktssosno(+)
and e.inst_id(+) = userenv('instance')
and a.sid = d.sid
and b.file_id = a.p1 - c.value
and c.name = 'db_files'
and a.event = 'direct path read'
order by 1,2;
select a.event,
a.sid,
c.serial# ,
c.sql_hash_value hash_value,
decode(d.ktssosegt,1,'SORT', 2,'HASH',3,'DATA',4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX',null) as segment_type,
b.tablespace_name,
b.file_name
from v$session_wait a, dba_data_files b, v$session c, x$ktsso d
where c.saddr = d.ktssoses(+)
and c.serial# = d.ktssosno(+)
and d.inst_id(+) = userenv('instance')
and a.sid = c.sid
and a.p1 = b.file_id
and a.event = 'direct path read'
union all
select a.event,
a.sid,
d.serial# ,
d.sql_hash_value hash_value,
decode(e.ktssosegt,1,'SORT', 2,'HASH',3,'DATA',4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX', null) as segment_type,
b.tablespace_name,
b.file_name
from v$session_wait a, dba_temp_files b, v$parameter c, v$session d, x$ktsso e
where d.saddr = e.ktssoses(+)
and d.serial# = e.ktssosno(+)
and e.inst_id(+) = userenv('instance')
and a.sid = d.sid
and b.file_id = a.p1 - c.value
and c.name = 'db_files'
and a.event = 'direct path read'
order by 1,2;
Oracle Weblogic Server patch info
Patch WDJ7 is mapped to patch 9438213 which is a combo patch includes the following patches:
- Patch 4D53 is mapped to patch 9100465
- Patch XLXA is mapped to patch 9221722
- Patch NIXN is mapped to patch 8990616
(Doc ID 1116655.1) - How to manually patch the 11g Grid Control Weblogic Server prior to installing Grid Control.
(Doc ID 1072763.1) - How to Download and Apply recommended WLS patch (WDJ7) on WLS home for 11g Grid Control Install/Upgrade
- Patch 4D53 is mapped to patch 9100465
- Patch XLXA is mapped to patch 9221722
- Patch NIXN is mapped to patch 8990616
(Doc ID 1116655.1) - How to manually patch the 11g Grid Control Weblogic Server prior to installing Grid Control.
(Doc ID 1072763.1) - How to Download and Apply recommended WLS patch (WDJ7) on WLS home for 11g Grid Control Install/Upgrade
Oracle waiting ---
Oracle Waiting currently ......
Oracle Waits
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill , username, blocking_session,seconds_in_wait, wait_time ,event from gv$session s where state = 'WAITING' and wait_class != 'Idle'; set linesize 300 pagesize 300 col SQL_TEXT for a70 wrap select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill , s.sql_id ,s.prev_sql_id,sql_text from gv$session s, gv$sql q where sid = &sid and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id ) and q.inst_id=s.inst_id; select wait_class_id, wait_class,total_waits, time_waited from gv$session_wait_class where sid = &sid; WAIT_CLASS_ID WAIT_CLASS TOTAL_WAITS TIME_WAITED ------------- ---------------------------------------------------------------- ----------- ----------- 1893977003 Other 31 7022 3875070507 Concurrency 19 14 3386400367 Commit 2 0 2723168908 Idle 36 3300 2000153315 Network 36 0 1740759767 User I/O 16344 1074 6 rows selected. select event, total_waits, time_waited from v$system_event e, v$event_name n where n.event_id = e.event_id and e.wait_class_id = &wait_class_id ; EVENT TOTAL_WAITS TIME_WAITED ---------------------------------------------------------------- ----------- ----------- os thread startup 12601 148864 latch: cache buffers chains 65878 55103 buffer busy waits 2393 1509 enq: TX - index contention 8 458 latch: In memory undo latch 3 2 latch: row cache objects 515 340 row cache lock 4 45 cursor: mutex X 1 0 cursor: pin S 110 230 cursor: pin S wait on X 13 22 latch: shared pool 1219 7622 library cache lock 5 227 library cache load lock 17 260 library cache: mutex X 82 301 resmgr:internal state change 54 734 15 rows selected.
Resetting v$filestat timings dbms_system.kcfrms
apt-rdbms-01.co.uk:APTDB\sys> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
apt-rdbms-01.co.uk:APTDB\sys> select file#, maxiortm from v$filestat;
FILE# MAXIORTM
------------------ ------------------
1 71
2 131
3 8
4 1
5 1
apt-rdbms-01.co.uk:APTDB\sys> exec dbms_system.kcfrms();
PL/SQL procedure successfully completed.
apt-rdbms-01.co.uk:APTDB\sys> select file#, maxiortm from v$filestat;
FILE# MAXIORTM
------------------ ------------------
1 0
2 0
3 0
4 0
5 0
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
apt-rdbms-01.co.uk:APTDB\sys> select file#, maxiortm from v$filestat;
FILE# MAXIORTM
------------------ ------------------
1 71
2 131
3 8
4 1
5 1
apt-rdbms-01.co.uk:APTDB\sys> exec dbms_system.kcfrms();
PL/SQL procedure successfully completed.
apt-rdbms-01.co.uk:APTDB\sys> select file#, maxiortm from v$filestat;
FILE# MAXIORTM
------------------ ------------------
1 0
2 0
3 0
4 0
5 0
Wednesday, 29 June 2011
Oracle 11g ADDM Report for Oracle RAC
ADDM Report
ADDM for RAC
DBMS_ADDM for RAC
SELECT MIN(snap_id), MAX(snap_id) FROM dba_hist_snapshot;
All instance in RAC ...
_________________________
VAR tname VARCHAR2(30);
VAR start_snap_id number;
VAR end_snap_id number;
BEGIN
:tname := 'DB_TEST_ANUJ';
:start_snap_id := 884 ;
:end_snap_id := 1052 ;
DBMS_ADDM.ANALYZE_DB(:tname, :start_snap_id, :end_snap_id);
END;
DBMS_ADDM for particulate Instance
_________________________________
VAR tname VARCHAR2(30);
VAR start_snap_id number;
VAR end_snap_id number;
VAR INST_NUM number;
BEGIN
:tname := 'INST_TEST_ANUJ';
:start_snap_id := 884 ;
:end_snap_id := 1052 ;
:INST_NUM := 1;
DBMS_ADDM.ANALYZE_INST(:tname,:start_snap_id,:end_snap_id, :INST_NUM );
END;
/
DBMS_ADDM for partial mode
___________________________
DBMS_ADDM
analyze instance 1 and 3 out of four node RAC
VAR tname VARCHAR2(30);
VAR start_snap_id number;
VAR end_snap_id number;
BEGIN
:tname := 'PART_MODE_TEST_ANUJ';
:start_snap_id :=884;
:end_snap_id := 1052 ;
DBMS_ADDM.ANALYZE_PARTIAL(:tname,'1,3', :start_snap_id, :end_snap_id);
END;
/
ADDM report output
__________________
SET LONG 500000 PAGESIZE 0;
SELECT DBMS_ADDM.GET_REPORT(:tname) FROM DUAL;
or
SELECT DBMS_ADDM.GET_REPORT('INST_TEST_ANUJ') FROM DUAL;
===========
SELECT MIN(snap_id), MAX(snap_id) FROM dba_hist_snapshot;
BEGIN
--
DBMS_ADVISOR.create_task (advisor_name => 'ADDM',task_name => '33033_33035_AWR_SNAPSHOT',task_desc => 'Advisor for snapshots 33033 to 33035');
DBMS_ADVISOR.set_task_parameter (task_name => '33033_33035_AWR_SNAPSHOT',parameter => 'START_SNAPSHOT',value => 33033);
DBMS_ADVISOR.set_task_parameter ( task_name => '33033_33035_AWR_SNAPSHOT',parameter => 'END_SNAPSHOT',value => 33035);
DBMS_ADVISOR.execute_task(task_name => '33033_33035_AWR_SNAPSHOT');
END;
/
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report('33033_33035_AWR_SNAPSHOT') AS report FROM dual;
=============
some sql !!!
set linesize 500 pagesize 300
col ACTION_MESSAGE for a70 wrap
col MESSAGE for a50 wrap
Select a.execution_end, b.type, b.impact, d.rank, d.type,
'Message:'|| b.message MESSAGE,
'Command To correct:'||c.command COMMAND,
'Action Message:'|| c.message ACTION_MESSAGE
From dba_advisor_tasks a, dba_advisor_findings b,Dba_advisor_actions c, dba_advisor_recommendations d
Where a.owner=b.owner
and a.task_id=b.task_id
And b.task_id=d.task_id
and b.finding_id=d.finding_id
And a.task_id=c.task_id
and d.rec_id=c.rec_Id
And a.task_name like 'ADDM%'
and a.status='COMPLETED'
and to_char(execution_end,'dd/mm/yyyy')='06/05/2021'
Order by 3 desc;
set linesize 500 pagesize 300
col ACTION_MESSAGE for a70 wrap
col MESSAGE for a50 wrap
Select a.execution_end, b.type, b.impact, d.rank, d.type,
'Message:'|| b.message MESSAGE,
'Command To correct:'||c.command COMMAND,
'Action Message:'|| c.message ACTION_MESSAGE
From dba_advisor_tasks a, dba_advisor_findings b,Dba_advisor_actions c, dba_advisor_recommendations d
Where a.owner=b.owner
and a.task_id=b.task_id
And b.task_id=d.task_id
and b.finding_id=d.finding_id
And a.task_id=c.task_id
and d.rec_id=c.rec_Id
And a.task_name like 'ADDM%'
and a.status='COMPLETED'
and to_char(execution_end,'dd/mm/yyyy')='06/05/2021'
Order by 3 desc;
EXECUTION TYPE IMPACT RANK TYPE MESSAGE COMMAND ACTION_MESSAGE
--------- ----------- ---------- ---------- ------------------------------ -------------------------------------------------- ----------------------------------------------------------------------------------- ----------------------------------------------------------------------
06-MAY-21 PROBLEM 338002056 2 SQL Tuning Message:SQL statements consuming significant datab Command To correct:UNDEFINED Action Message:Investigate the PL/SQL statement with SQL_ID "b6usrg82h
ase time were found. These statements offer a good wsa3" for possible performance improvements. You can supplement the in
opportunity for performance improvement. formation given here with an ASH report for this SQL_ID.
06-MAY-21 PROBLEM 338002056 4 SQL Tuning Message:SQL statements consuming significant datab Command To correct:UNDEFINED Action
set linesize 300 pagesize 300
col ACTION_MESSAGE for a150 wrap
Select distinct c.message ACTION_MESSAGE
From dba_advisor_tasks a, dba_advisor_findings b,Dba_advisor_actions c,
dba_advisor_recommendations d
Where a.owner=b.owner and a.task_id=b.task_id
And b.task_id=d.task_id and b.finding_id=d.finding_id
And a.task_id=c.task_id and d.rec_id=c.rec_Id
And a.task_name like 'ADDM%' and a.status = 'COMPLETED'
-- And c.message like '%involving I/O on TABLE%'
--And c.message like 'Investigate application logic involving I/O on TABLE%'
Order by 1;
ACTION_MESSAGE
------------------------------------------------------------------------------------------------------------------------------------------------------
Consider not using the compression option for RMAN jobs. Alternatively, consider slowing down RMAN activity, or scheduling RMAN jobs when user activit
y is lower.
Increase the size of the SGA by setting the parameter "sga_target" to 4608 M.
Increase the size of the SGA by setting the parameter "sga_target" to 5120 M.
Increase the size of the SGA by setting the parameter "sga_target" to 5632 M.
Increase the size of the SGA by setting the parameter "sga_target" to 6144 M.
Increase the size of the SGA by setting the parameter "sga_target" to 6656 M.
Increase the size of the SGA by setting the parameter "sga_target" to 7168 M.
Increase the size of the SGA on affected instances. Check the ADDM analysis of affected instances for more details.
Increase throughput of the Global Cache Service (LMSn) processes. Increase the number of Global Cache Service processes by increasing the value of the
parameter "gcs_server_processes". Alternatively, if the host is CPU bound consider increasing the OS priority of the Global Cache Service processes.
Investigate application logic involving I/O on database object with ID 82.
Investigate application logic to eliminate parse errors.
Investigate appropriateness of DDL operations.
Investigate the PL/SQL statement with SQL_ID "b6usrg82hwsa3" for possible performance improvements. You can supplement the information given here with
an ASH report for this SQL_ID.
Set pages 1000 lines 300
col ACTION_MESSAGE for a70
col MESSAGE for a20
col COMMAND for a15
Select a.execution_end, b.type, b.impact, d.rank, d.type,
'Message : '||b.message MESSAGE,
'Command To correct: '||c.command COMMAND,
'Action Message : '||c.message ACTION_MESSAGE
from dba_advisor_tasks a, dba_advisor_findings b,dba_advisor_actions c, dba_advisor_recommendations d
where a.owner=b.owner and a.task_id=b.task_id
and b.task_id=d.task_id and b.finding_id=d.finding_id
and a.task_id=c.task_id and d.rec_id=c.rec_Id
and a.task_name like 'ADDM%'
and a.status='COMPLETED'
and a.description like '%53996%' --- <<<< from above sql
and d.type='SQL Tuning'
-- and b.owner not in ('SYS','SYSTEM')
-- and a.description like (select snap_id from (select snap_id from dba_hist_snapshot order by begin_interval_time desc) where rownum < 3)
Order by b.impact, d.rank;
Set pages 1000 lines 300
col ACTION_MESSAGE for a70
col MESSAGE for a20
col COMMAND for a15
Select a.execution_end, b.type, b.impact, d.rank, d.type,
'Message : '||b.message MESSAGE,
'Command To correct: '||c.command COMMAND,
'Action Message : '||c.message ACTION_MESSAGE
from dba_advisor_tasks a, dba_advisor_findings b,dba_advisor_actions c, dba_advisor_recommendations d
where a.owner=b.owner and a.task_id=b.task_id
and b.task_id=d.task_id and b.finding_id=d.finding_id
and a.task_id=c.task_id and d.rec_id=c.rec_Id
and a.task_name like 'ADDM%'
and a.status='COMPLETED'
--and a.description like '%b742waswg25n7_tuning_task11%' --- <<<< from above sql
and d.type='SQL Tuning'
-- and b.owner not in ('SYS','SYSTEM')
-- and a.description like (select snap_id from (select snap_id from dba_hist_snapshot order by begin_interval_time desc) where rownum < 3)
--and a.execution_end > sysdate -1
Order by b.impact, d.rank;===
select round((ratio_to_report(max(Benefit)) over () *100)) as overall_benefit_pct
, type
, min(benefit) min_benefit
, max(Benefit) max_benefit
, count(*) cnt
from dba_Advisor_recommendations
where type is not null
group by type
order by 1 desc
;
col command for a15
col message for a100
select command, message , count(*) from dba_advisor_Actions
group by command, message
;
col TASK_NAME for a27
col description for a100
select task_name, description, created from dba_advisor_tasks
where advisor_name = 'ADDM'
and created > sysdate -7
order by created desc;
SELECT type, count(*) FROM dba_advisor_findings NATURAL JOIN dba_advisor_tasks WHERE created between sysdate -1 and sysdate GROUP BY type
SELECT distinct message FROM dba_advisor_recommendations JOIN dba_advisor_findings USING (finding_id, task_id) WHERE rank = 0;
SET LONG 500000 PAGESIZE 0
SELECT DBMS_ADDM.GET_REPORT('ADDM:1825264339_1_19982') FROM DUAL;
Subscribe to:
Posts (Atom)
