Session info 1

[ptheriault]

I've written the following query to return me info for each active session.

SELECT a.Spid
      ,b.Username
      ,b.Status
      ,b.Osuser
      ,b.Process
      ,b.Machine
      ,b.Program
      ,b.Sid
      ,a.Spid
FROM V$PROCESS a
 JOIN V$SESSION b
   ON a.Addr = b.Paddr
WHERE b.USERNAME like 'APOLL%'
  AND b.STATUS = 'ACTIVE'

I would like to see the Total CPU time for these sessions as well. I tried to join on the V$SESSTAT view but was taking way to long to return a result set. My question is...
Is there an already made view which is similar to 'sp_who2' in SQL server for Oracle 10G?

Thanks


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SantaMufasa]

Paul,

Could you please post your query that is running too slowly?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Paul,

Could you also please post the results of this query:

select count(*) from v$sesstat;

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ptheriault]

Dave,
Happy New year.

V$SESSTAT = 31150

SELECT a.Spid
      ,b.Username
      ,b.Status
      ,b.Osuser
      ,b.Process
      ,SUM(c.VALUE) as 'CPU TIME'
      ,b.Machine
      ,b.Program
      ,b.Sid
      ,a.Spid
FROM V$PROCESS a
 JOIN V$SESSION b
   ON a.Addr = b.Paddr
 JOIN V$SESSTAT c
   ON c.Sid = b.Sid
WHERE b.USERNAME like 'APOLL%'
--  AND b.STATUS = 'ACTIVE'
GROUP BY a.Spid
      ,b.Username
      ,b.Status
      ,b.Osuser
      ,b.Process
      ,b.Machine
      ,b.Program
      ,b.Sid

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SantaMufasa]

Thanks, Paul, for posting your code. Did you copy and paste your code from a successful execution of the code?...The way it appears, above, won't work since column aliases (e.g., 'CPU TIME') must appear within double quotes (i.e., "CPU TIME").

The reason that your query is slow is most likely because queryable Oracle Data Dictionary objects are not tables; they are views. And as a result, views cannot have indexes, and therefore must join (i.e., "JOIN V$SESSTAT c ON c.Sid = b.Sid") using an inefficient full table scan instead of an INDEX SCAN on c.Sid.

To prove the point (and see how much faster the query runs using an index), you can use the following code:

create table my_sesstat as select * from v$sesstat;
create index my_sesstat_ndx on my_sesstat(SID);
SELECT a.Spid
      ,b.Username
      ,b.Status
      ,b.Osuser
      ,b.Process
      ,SUM(c.VALUE) as "CPU TIME"
      ,b.Machine
      ,b.Program
      ,b.Sid
      ,a.Spid
FROM V$PROCESS a
 JOIN V$SESSION b
   ON a.Addr = b.Paddr
 JOIN my_SESSTAT c
   ON c.Sid = b.Sid
WHERE
 b.STATUS = 'ACTIVE'
GROUP BY a.Spid
      ,b.Username
      ,b.Status
      ,b.Osuser
      ,b.Process
      ,b.Machine
      ,b.Program
      ,b.Sid;

To get an idea of how much faster this runs, in SQL*Plus, "SET TIMING ON". The actual "indexed" query (using MY_SESSTAT and its index) should run approximately 5 times faster. The downside is that the time it takes to "CREATE TABLE MY_SESSTAT" virtually eats up all the time savings of using it and its index in the query.

So, it's a case of "Pay me now or pay me later." <grin>

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ptheriault]

Thanks Dave. I posted, than ran the query, then realized I need double guotes. : )

I guess I'll go with the "pay me later" option. It ran about 6x faster.

Thanks again!




- Paul
- If at first you don't succeed, find out if the loser gets anything.