DBA Quick Notes
TEMP Space Issues
=================
Below SQL gives the temp usage of sessions.
SELECT ss.sid, sum(st.blocks)/1024*8 FROM v$sort_usage st, v$session ss where ss.saddr=st.session_addr group by ss.sid order by 2;
Below SQL gives the free space in TEMP.
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
User DBMS Jobs
===========
select job,what from dba_jobs where SCHEMA_USER='&1';
select sid,job,instance from DBA_JOBS_RUNNING;
ASM Diskgroup Usage
===============
select name,total_mb/1024 "Total Size(GB)",free_mb/1024 "Free (MB)", round(free_mb/total_mb*100,2) "Free (%)" from v$asm_diskgroup where name like '%&Diskgroup_Name%';
if output is less that 2 or 3GB... then run @temp_usage.sql and check which session is consuming high TEMP space ... and mail to the schema owners.
UNDO Tablespace Issues
======================
The below SQL returns the session occupying high undo
SELECT s.sid, s.serial#, s.username, s.program, t.used_ublk*8192/1024/1024 "RBS Usage in MB" FROM v$session s, v$transaction t WHERE s.taddr = t.addr AND t.used_ublk > 100 ORDER BY 5 desc;
Open Cursor Issue
=================
select s.sid,s.module,s.username,s.status,count(*) "Open_Cursors_count" from v$session s,v$open_cursor c where c.sid=s.sid group by s.sid,s.module,s.username,s.status having count(*) >10 order by 5;
Session running a particular SQL Statement
============================
select sid from v$session where sql_address in (select address from v$sqlarea where sql_text like '%&sql_statement%');
Validation
=======
select distinct checkpoint_change# from v$datafile;
select count(*) from v$recover_file;
select count(*) from v$recovery_log;
select count(*) from v$recovery_status;
select distinct fhsta from x$kcvfh;
select count(*) from v$recovery_file_status;
Dynamic Kill statement generation
======================
select 'kill -9 '||b.spid,a.status from v$session a ,v$process b where a.paddr=b.addr and upper(a.username)='&Schema_Name';
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where username like '&Usr_name' and machine like '&machine';
Session Trace Enablement
=================
select s.sid,p.spid,s.serial# from v$session s,v$process p where s.paddr=p.addr and s.sid=&SID;
oradebug setospid
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
oradebug tracefile_name
oradebug event 10046 trace name context off
Hang Analysis
=============
oradebug setmypid
oradebug unlimit
oradebug -g all dump systemstate 267 (or 258 if it takes long)
Global Hang Analysis
====================
connect as "/ AS SYSDBA"
oradebug setmypid
oradebug setinst all
oradebug -g def hanganalyze 3
System State Dump
=============
$ sqlplus /nolog
connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 10
Table Last Analyzed On
======================
select TABLE_NAME "Table Name",to_char(LAST_ANALYZED,'DD-MON-YY HH24:MI:SS') "Date and Time" from dba_TABLES where upper(TABLE_NAME)=upper('&tname');
Normal Analyze
==========
exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SCHEMA', tabname => 'TABLE_NAME', estimate_percent => 10, block_sample => TRUE, degree => 1, method_opt => 'FOR ALL COLUMNS SIZE 1', granularity => 'ALL', cascade => TRUE, no_invalidate => FALSE);
to calculate the % analyzed.
==================
select owner,TABLE_NAME,NUM_ROWS,to_char(LAST_ANALYZED,'DD-MON-YYYY HH24:MI:SS') "LAST_ANALYZED", SAMPLE_SIZE,
decode(NUM_ROWS,0,0,(SAMPLE_SIZE/NUM_ROWS)*100) "ANALYZE %", CHAIN_CNT,DEGREE,COMPRESSION,PARTITIONED
from dba_tables where table_name in ('&TABLE_NAME');
CREATING HISTOGRAMS
===================
execute dbms_stats.gather_table_stats (ownname => 'oe', tabname => 'inventories', METHOD_OPT => 'FOR COLUMNS SIZE 10 quantity_on_hand');
METHOD_OPT => 'FOR COLUMNS SIZE <# of buckets> ‘
Verify the analyzed objects
============================
select table_name,to_char(last_analyzed,'dd-mon-yyyy hh24:mi:ss') from dba_tables where table_name='&tbl_nam';
CHECKING DATAFILE HEADER
========================
SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME FROM V$DATAFILE_HEADER WHERE NAME LIKE '&your_datafile';
Put tablespaces to end backup
=============================
select 'alter tablespace '||name||' end backup;' from v$tablespace where ts# in (select ts# from v$datafile where file# in (select file# from v$backup where status='ACTIVE'));
Verifying for Block Corruption
==============================
drop table corrupted_objects;
create table corrupted_objects
as
select owner, segment_name, partition_name, segment_type, 1 file#, 1 block#
from dba_extents
where 1=2;
declare
cursor C1 is select file#, block#, blocks
from v$database_block_corruption;
begin
for r1 in C1 loop
for r2 in 1..r1.blocks loop
insert into corrupted_objects
select owner, segment_name, partition_name,segment_type, r1.file#, r1.block#+r2-1
from dba_extents
where file_id=r1.file#
and (r1.block#+r2-1) between block_id and block_id + blocks - 1;
if sql%notfound then
/* segment header block might be corrupt causing dba_extents not returning rows. */
insert into corrupted_objects
select owner, segment_name, partition_name, segment_type, r1.file#, r1.block#
from dba_segments
where header_file=r1.file# and header_block = (r1.block#+r2-1);
end if;
commit;
end loop;
end loop;
end;
/
REMOVING AN OBJECT FROM SHARED POOL
The below command will remove the object from shared pool
exec dbms_shared_pool.unkeep('SCHEMA.OBJECT','P');
DG LAG
select round((sysdate-max(first_time))*24,2) "Hours Behind" from v$log_history;
FINDING SESSION WAIT LONG OPERATION
select SID, OPNAME, TIME_REMAINING, MESSAGE from v$session_longops where SID=1491;
FINDING UNUSABLE INDEXES
set lines 400
select index_name,table_name,owner from dba_indexes where status='UNUSABLE';
Flashback Area Usage
select * from v$flash_recovery_area_usage;
Connect to RMAN catalog and purge the expired archives.
crosscheck archivelog all;
delete expired archivelog all;
DETAILED EVENT DETAILS
col EVENT for a50;
col OSUSER for a20;
col USERNAME for a20;
col module for a30
set linesize 175;
select s.sid,s.username,to_char(s.logon_time,'DD-MON-YY HH24:MI:SS'),s.status,s.sql_hash_value,s.module,LPAD(((last_call_et/60)-mod((last_call_et/60),60))/60,2,'0') ||':'|| LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr' "Hr",x.event from v$session s,v$session_wait x
where x.sid=s.sid and x.event like '&event_name' order by 3,7;
DATABASE GROWTH
select q1 ,q2 ,q3 ,q4 from
(select round(sum(bytes)/1024/1024/1024) q1 from v$datafile where to_char(creation_time,'MON-YYYY') in ('AUG-2011','SEP-2011','OCT-2011')) q1t,
(select round(sum(bytes)/1024/1024/1024) q2 from v$datafile where to_char(creation_time,'MON-YYYY') in ('JAN-2012','DEC-2011','NOV-2011')) q2t,
(select round(sum(bytes)/1024/1024/1024) q3 from v$datafile where to_char(creation_time,'MON-YYYY') in ('APR-2012','MAR-2012','FEB-2012')) q3t,
(select round(sum(bytes)/1024/1024/1024) q4 from v$datafile where to_char(creation_time,'MON-YYYY') in ('MAY-2012','JUN-2012','JUL-2012')) q4t;
CHECKING SQLID IN SHARED POOL
select address, hash_value, inst_id from gv$sql where sql_id = '9hy79u9b5607a';
FLUSHING SQL ID FROM SHARED POOL
select sql_id, inst_id, address, hash_value from gv$sqlarea where sql_id = '8vn0arjbfyruj';
exec dbms_shared_pool.purge ('000000085778A100,1448280298','C');
CHECKING SQLID IN SHARED POOL
select address, hash_value, inst_id from gv$sql where sql_id = '9hy79u9b5607a';
FLUSHING SQL ID FROM SHARED POOL
select sql_id, inst_id, address, hash_value from gv$sqlarea where sql_id = '8vn0arjbfyruj';
exec dbms_shared_pool.purge ('000000085778A100,1448280298','C');
CHECK CORRUPTED OBJECT
SELECT SEGMENT_TYPE,OWNER||'.'||SEGMENT_NAME FROM DBA_EXTENTS WHERE FILE_ID = 38 AND 39018 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS -1;
IDENTIFYING LIBRARY CACHE LOCK/LIBRARY CACHE PIN
select inst_id, handle, grant_level, request_level, resource_name1, resource_name2, pid , transaction_id0, transaction_id1,owner_node, blocked, blocker, state from gv$ges_blocking_enqueue
CURSOR PIN S WAIT ON X ISSUE
Get the p2raw column value of the session waiting on Cursor Pins S wait on X wait event.
Top 4 values in 32bit machine and Top 8 values in 64bit machine are the holder session id.
select p2raw,to_number(substr(to_char(rawtohex(p2raw)),1,8),'XXXXXXXX') sid from v$session where event = 'cursor: pin S wait on X';
TO CHECK FOR TRACING ENABLED PROCESSES
set serveroutput on
declare
event_level number;
begin
for i in 1..99999 loop
sys.dbms_system.read_ev(i,event_level);
if (event_level > 0) then
dbms_output.put_line('Event '||to_char(i)||' set at level '||to_char(event_level));
end if;
end loop;
end;
/
Logical Memory Consumption
select pid,spid,program,pga_used_mem/1024/1024/1024 as PGA_USED_GB,pga_alloc_mem/1024/1024/1024 as PGA_ALOC_GB ,pga_freeable_mem/1024/1024/1024 PGA_FREE_GB,pga_max_mem/1024/1024/1024 as PGA_MAX_GB from v$process where rownum<20 4="" by="" desc="" div="" order="">
Long Running Operations
select
lo.target,lo.sofar,seg.blocks,
lo.ELAPSED_SECONDS*seg.blocks/lo.sofar-lo.ELAPSED_SECONDS TIME_REMAINING,
100*lo.sofar/seg.blocks PCT_DONE
from
dba_segments seg,
v$session_longops lo
where
lo.units='Blocks'
and lo.totalwork>0 and (lo.time_remaining>0 or lo.time_remaining is null)
and regexp_substr(lo.target,'[^.]+') = seg.owner
and regexp_substr(lo.target,'[^.]+$') = seg.segment_name;
COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid,
s.serial#,
s.machine,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v$session s,
v$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#;
Command to check whether host is VM or not
cat /sys/class/dmi/id/sys_vendor
cat /proc/scsi/scsi.
Above commands will return the manufacturer name
Redo Log Group Details
select a.group#,a.thread#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;
No comments:
Post a Comment