Re: oradebug dump processstate 10

From: Radoulov, Dimitre <cichomitiko_at_gmail.com>
Date: Fri, 10 Jun 2005 09:35:49 +0200
Message-ID: <007801c56d8f$06d7e410$1a03310a@IBME1D11967173>

I didn't get that ...

If you just run the "alter ... compile" command and it hangs ...., if you run from another session:

select

       V$S.LOGON_TIME,
       V$S.SID,
       V$S.SERIAL#,
       V$S.USERNAME,
       V$S.PROCESS,
       V$P.SPID,
       V$S.STATUS,
       V$S.MACHINE,
       V$S.CLIENT_INFO
  from v$session V$S,
       v$process V$P
 where
       V$S.PADDR = V$P.ADDR

   and V$S.SID in
(select distinct sid from v$access where object like upper('&obj')) order by

         V$S.PROCESS,
         V$S.SID

&obj --> the object_name you're trying to compile .....

You want to see who is locking your object, or understand what is the "parent lock" concept?

Cheers
Dimitre Radoulov

ETNØTEAM

• Original Message ----- From: "Egor Starostin" <egorst_at_gmail.com> To: "Dimitar Radoulov" <cichomitiko_at_gmail.com> Cc: <oracle-l_at_freelists.org> Sent: Friday, June 10, 2005 4:50 AM Subject: Re: oradebug dump processstate 10

> I think it will be much easier to check the v$access view and find who
> is accessing the object ...

I think it will be much harder (at least to Oracle instance). Here's the test case:
***
exec runstats_pkg.rs_start;
alter session set events 'immediate trace name hanganalyze level 2'; exec runstats_pkg.rs_middle;
select sid from v$access where object='OBJ$'; exec runstats_pkg.rs_stop;
***

And here's the extract from results:
***
[skipped]
Run1 ran in 7 hsecs
Run2 ran in 6062 hsecs
run 1 ran in .12% of the time
[skipped]
Run1 latches total versus runs -- difference and pct

Run1        Run2        Diff       Pct
7,067   2,301,590   2,294,523       .31%

***

To me, v$access is only usable on idle instances.

-- 
Egor
http://www.oracledba.ru/orasrp/
Free Oracle Session Resource Profiler 

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jun 10 2005 - 03:40:49 CDT