4031
First query – show the size of missing space
select sum(kghlunfu) Total_4031 ,kghlunfs Size_miss from x$kghlu group by kghlunfs
Show the possible query with problem
select substr(kglnaobj,1,30) sub, count(*) from x$kglob
group by substr(kglnaobj,1,30)
having count(*)>200
order by 1;
_kghdsidx_count >500M per _
Shows the number of subpools (default is 1)
select nam.ksppinm NAME, val.KSPPSTVL VALor
from x$ksppi nam, x$ksppsv val
where nam.indx = val.indx and
nam.ksppinm like ‘%kghdsidx%’ order by 1 ;
Shows the segmentation of shared pool
SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),’999,999.00′)||’k’ “AVG SIZE”
FROM X$KSMSP GROUP BY KSMCHCLS;
oradebug setmypid
oradebug unlimit
oradebug dump heapdump 536870914
oradebug dump library_cache 11
oradebug dump heapdump 2
oradebug tracefile_name
close_trace
SPOOL FRIDAY_31AUGU_1159PM_MEMINFO.out
select * from v$sgastat;
select ksmchcom ChunkComment, ksmchcls Status, sum(ksmchsiz) Bytes
from x$ksmsp group by ksmchcom, ksmchcls;
select KSMCHIDX “SubPool”, ‘sga
heap(‘||KSMCHIDX||’,0)’sga_heap,ksmchcom ChunkComment,
decode(round(ksmchsiz/1000),0,’0-1K’, 1,’1-2K’, 2,’2-3K’,3,’3-4K’,
4,’4-5K’,5,’5-6k’,6,’6-7k’,7,’7-8k’,8,
‘8-9k’, 9,’9-10k’,’> 10K’) “size”,
count(*),ksmchcls Status, sum(ksmchsiz) Bytes, max(ksmchsiz) Max_Bytes
from x$ksmsp
where KSMCHCOM = ‘free memory’
group by ksmchidx, ‘sga heap(‘||KSMCHIDX||’,0)’,ksmchcom,
ksmchcls,
decode(round(ksmchsiz/1000),0,’0-1K’, 1,’1-2K’, 2,’2-3K’,3,’3-4K’,
4,’4-5K’,5,’5-6k’,6,’6-7k’,7,’7-8k’,8,
‘8-9k’, 9,’9-10k’,’> 10K’);
SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),’999,999.00′)||’k’ “AVG
SIZE”
FROM X$KSMSP GROUP BY KSMCHCLS;
SPOOL OFF;
Save the spool files.
2. $ sqlplus /nolog
connect / as sysdba
Alter session set max_dump_file_size = unlimited
oradebug setmypid
oradebug unlimit
oradebug dump heapdump 2
oradebug tracefile_name <<< this command provides the full path and filename of the
trace file just generated >>>
exit
/dados/db/10.2.0/admin/BRMDEV_brmdev/udump/brmprj_ora_8023.trc
3. Close and open a new session .
sqlplus /nolog
connect / as sysdba
alter session set max_dump_file_size = unlimited
oradebug setmypid
oradebug unlimit
oradebug dump library_cache 11
oradebug tracefile_name <<< this command provides the full path and filename of the
trace file just generated >>>
exit
/dados/db/10.2.0/admin/BRMDEV_brmdev/udump/brmprj_ora_8629.trc
Save the trace files.
PROCEDURE MONITORAMENTO
CREATE OR REPLACE PROCEDURE GC_DBA_4031_PROD2 AS
qtd_errors number;
begin
select sum(total_4031) into qtd_errors from gc_dba_4031;
if qtd_errors>0 then
dbms_system.ksdwrt(2,’ORA-00600: URGENTE – 4031 iniciando em PRODUCAO02 BY HELIO DBA’);
end if;
END;
/
Oracle Logbook 