Oracle Logbook

July 25, 2011

Dealing with 4031

Filed under: Uncategorized — Helio Dias @ 4:45 pm

 

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;

 

/

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: