Oracle MVA

While doing a routine checkup I found that four processes are eating up CPU’s on one node of a two (2) node RAC cluster. These processes are ora_q000_SID2, ora_Q001_SID2, ora_Q002_SID2 & ora_Q003_SID2. This suggests there is some extra activity with AQ on the SID2 instance. (This is a 10.2.0.4 RDBMS RAC running on 64-bit OEL 4.7, CRS and ASM on 11.1.0.7)

So first thing I investigated were the queues:

Read the rest of this entry »

While troubleshooting some problem on a RAC database for a customer, I queried gv$session. I knew I was the only user logged in with my username, but somehow I found four rows in gv$session for my one session. Some further investigation told me this:

MY_USER@SOME_DB SQL> select inst_id, username
  2    from gv$session
  3   where username = ‘MY_USER’;

   INST_ID USERNAME
———- ——————–
         1 MY_USER
         1 MY_USER
         2 MY_USER
         3 MY_USER

Obviously I was annoyed by the fact that four results came back from gv$session, even though I was the only person logged in. Luckily my colleague Jeroen explained to me that I was completely overlooking the fact that querying gv$session actually invokes a parallel query over the complete RAC:

MY_USER@SOME_DB SQL> select inst_id, osuser, program
2 from gv$session
3 where username = ‘MY_USER’;

INST_ID OSUSER PROGRAM
———- ——————– ————————————————–
1 LandlustJH sqlplus@some_server.nl (TNS V1-V3)
1 LandlustJH oracle@some_server.nl (PZ99)
2 LandlustJH oracle@some_server.nl (PZ99)
3 LandlustJH oracle@some_server.nl (PZ99)

Somehow I could have know this…. Somehow this also might explain why sometimes EM gives four audit alerts after one login. Anyway, this post is to share this with the world: remember that sometimes results from Oracle seem wrong, but in fact they’re not.

P.S.: and call Jeroen if you need a real pro on the job

Today I spent some time on fixing performance issues with the BPEL dehydration store. The database is a three (3) node RAC cluster, running RBDMS 10.2.0.4 on ASM 11.1.0.7. BPEL connects to a service name that has one (1) preferred instance and two (2) available instances.

HW contention usually means: trouble with lobs. Just to be sure, I started to check metalink. First of all I discovered I had to set event 44951 to fix bug 6376915 (setting an event without restarting the database means setting events =, next set the event in the spfile). This didn’t clear my HW contention problems though.  What did solve the problems, was moving the lobsegments to a separate tablespace. Here’s the script I used to move the lobsegments (with some extra segment space shrinking):

Read the rest of this entry »