Link to home
Create AccountLog in
Avatar of Geert G
Geert GFlag for Belgium

asked on

Oracle 11 slow query on v$process

I've got this query in a login trigger which takes a little over a second

INSERT INTO secu.trclogon (id, spid, sid, serial, timestamp,
                               osuser, username, process, machine, prog, inout)
SELECT secu.sq_trclogon.nextval, spid, sid, SE.serial#, sysdate,
           osuser, SE.username, process, machine, SE.program, 'I'
      FROM v$process   PR,
           v$session   SE
     WHERE audsid  = userenv('SESSIONID')
       AND PR.addr = SE.paddr
       AND rownum < 2;

in oracle 10.2 this was fast,
after upgrade to oracle 11.2.0.3 this is slow

i've nailed it down to v$process
this works in 30ms:
SELECT secu.sq_trclogon.nextval, se.sid, se.serial#, sysdate,
           se.osuser, se.username, se.process, se.machine, se.program, 'I'
      FROM v$session SE
     WHERE se.audsid  = userenv('SESSIONID')
       AND rownum < 2;

any ideas how to speed up query on v$process ?
(yes, i need the spid ... for tracking purposes in a logoff procedure)
Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

may be good to log a case with the oracle support if that is the only thing you wanted to do.
ASKER CERTIFIED SOLUTION
Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
my second update is for the performance issues on the application tables ( not the tables/views owned & internally managed by Oracle Database )
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
no need of points. i am not behind points and hence not worried. I do not have 11.2.0.3 or 10.2.0.4 and i just have one here with 11.2.0.1 database version.

I just try to help when i get time and nothing more than that. I am sure i am not a genius in everything what i do :) that is just the way rank is shown based on the points.

I am happy that you fixed it for yourself and so you are a genius here. :)
just close the question saying you have found the answer yourself and it will be helpful for someone like you if they are looking for a solution for this type of issue.
Hmm... .You should take the points as you did tune it yourself by reworking the query etc.
Avatar of Geert G

ASKER

following the pointers in your comment would also lead to the solution
>> thanx for the help and pointers