Borndba

 We did see our mview going slow. When we traced the session we found our job is waiting for a while in sumdelta$.

if automic_refresh=false due to direct load operation sumdelta$ don’t purge. There are some bug registered for this.

This is the workaround.

CREATE MATERIALIZED VIEW dummy_mview
TABLESPACE tbs_data
NOLOGGING
refresh complete
start with sysdate
next (sysdate+(1/1440))
WITH ROWID
AS select sysdate,col1 from mview mv10 WHERE 1=2 ;

In the select add “sysdate” so that refresh is assumed as complex and refresh every time.

exec DBMS_MVIEW.REFRESH('DUMMY_MVIEW','C');

select count(*) from sys.sumdelta$ where tableobj# in (select obj# from sys.obj$ where name=upper('DUMMY_MVIEW');

the count of this query should reduce to zero.

convert this post to pdf.

I generally create the profile via oem. The below link explains how to create via command line,

http://www.oracle-base.com/articles/10g/AutomaticSQLTuning10g.php

convert this post to pdf.

We have a DB which connects to other DB via LDAP connection. We had an issue where db link to another db server is slow.

I have limited expertise on LDAP. This is how i solved the issue.

select * from dual@db_link;

it’s slow first time. In the same session if we run it again, it’s fast.

Opened a SR.Got all the trace files but no update.

I enabled tnsping trace using below command

#TNSPING.TRACE_LEVEL = 16
#TNSPING.TRACE_DIRECTORY=/tmp

It didn’t have time info. So enabled the client and server level trace.

#trace_level_server=16
#trace_level_client=16
#trace_directory_server=/tmp
#trace_directory_client=/tmp
#trace_file_client=cli1
#trace_file_server=srv1
#trace_unique_client=true

in the trace

[04-AUG-2010 05:20:19:035] nnflilc:  Opening sync conn to server1.domain.com:123
[04-AUG-2010 05:21:33:947] nnflilc:   ldap_open failed ld:0

almost a minute it waits on server1, then it goes to server 2 and connects

[04-AUG-2010 05:21:33:954] nnflilc:  Opening sync conn to server2.domain.com:123
[04-AUG-2010 05:21:33:979] nnflalc: entry
[04-AUG-2010 05:21:34:009] nnflalc:     bind call returns 0

This gives the clue that server1 oid is having issue. So we changed the ldap.ora look up

DIRECTORY_SERVERS= (server1.domain.com:123:636,server2.domain.com:123:636)

to

DIRECTORY_SERVERS= (server2.domain.com:123:636,server1.domain.com:123:636)

command to start and stop oid.

oidmon start
oidctl server=oidldapd1 instance=1 configset=1 start
oidctl server=oidrepld1 instance=1 flags="-h server1 -p 389" start

stop

oidctl server=oidldapd1 instance=1 configset=1 stop;sleep 10
oidctl server=oidrepld1 instance=1 stop;sleep 10
oidmon stop

convert this post to pdf.

Create a sql based user defined metrics with below script. SQL query output will be 2 colums with metrics value as number.

select tablespace_name,
100-(round(((mb_free+mb_total-mb_curr)/mb_total)*100,0)) adj_pct_free
from
(
select d.tablespace_name,
nvl(sum(f.bytes)/1024/1024, 0) mb_free,
sum(d.bytes)/1024/1024 mb_curr,
case when sum(d.maxbytes)>0 then sum(d.maxbytes)/1024/1024
else  sum(d.bytes)/1024/1024 end mb_total
from dba_data_files d left outer join dba_free_space f
on (f.tablespace_name = d.tablespace_name)
group by d.tablespace_name
) used_free
where tablespace_name not like '%UNDO%';

convert this post to pdf.