Verificar usuário do sistema Operacional - OAS

[valchaca]

Boa tarde! Estou com um problema: migramos para o forms 10g, porém com a necessidade da instalação do OAS, não consigi visualizar o usuário do SO na v$session. Aparece o usuário do OAS. Assim não tem como eu verificar qual usuário que está travando. Alguém teria uma solução?



Informações do Ambiente:
* Versão do Oracle: 10g
* Sistema Operacional: Windows 2003 - 32 bits

[diegopedrao]

Geralmente esses serviços do OAS, ORACLE 10, 11g geralmente o usuário de conexão do sistema é o SYSTEM boa parte das vezes.


Att,

Diego Monteiro

[valchaca]

E como posso conseguir descobrir quem está travando ou consumindo muito recurso do banco? Existe alguma forma?

[diegopedrao]

Só executar esse select, ele retornará as 20 top sessions.

Selecionar tudo

select * from

(select b.sid sid,

     decode (b.username,null,e.name,b.username) user_name,

     d.spid os_id,

     b.machine machine_name,

     to_char(logon_time,'dd-mon-yy hh:mi:ss pm') logon_time,

    (sum(decode(c.name,'physical reads  ',value,0)) +

     sum(decode(c.name,'physical writes',value,0)) +

     sum(decode(c.name,'physical writes direct',value,0)) +

     sum(decode(c.name,'physical writes direct (lob)',value,0))+

     sum(decode(c.name,'physical reads  direct (lob)',value,0)) +

     sum(decode(c.name,'physical reads   direct',value,0)))

     total_physical_io,

    (sum(decode(c.name,'db block gets',value,0)) +

     sum(decode(c.name,'db block changes',value,0)) +

     sum(decode(c.name,'consistent changes',value,0)) +

     sum(decode(c.name,'consistent gets ',value,0)) ) 

     total_logical_io,

    (sum(decode(c.name,'session pga memory',value,0))+

     sum(decode(c.name,'session uga memory',value,0)) )

     total_memory_usage,

     sum(decode(c.name,'parse count (total)',value,0)) parses,

     sum(decode(c.name,'cpu used by this session',value,0)) 

     total_cpu,

     sum(decode(c.name,'parse time cpu',value,0)) parse_cpu,

     sum(decode(c.name,'recursive cpu usage',value,0)) 

       recursive_cpu,

     sum(decode(c.name,'cpu used by this session',value,0)) -

     sum(decode(c.name,'parse time cpu',value,0)) -

     sum(decode(c.name,'recursive cpu usage',value,0)) 

       other_cpu,

     sum(decode(c.name,'sorts (disk)',value,0)) disk_sorts,

     sum(decode(c.name,'sorts (memory)',value,0)) memory_sorts,

     sum(decode(c.name,'sorts (rows)',value,0)) rows_sorted,

     sum(decode(c.name,'user commits',value,0)) commits,

     sum(decode(c.name,'user rollbacks',value,0)) rollbacks,

     sum(decode(c.name,'execute count',value,0)) executions

from sys.v_$sesstat  a,

     sys.v_$session b,

     sys.v_$statname c,

     sys.v_$process d,

     sys.v_$bgprocess e

where a.statistic#=c.statistic# and

SEE CODE DEPOT FOR FULL SCRIPTS

      c.NAME in ('physical reads  ',

                 'physical writes',

                 'physical writes direct',

                 'physical reads   direct',

                 'physical writes direct (lob)',

                 'physical reads   direct (lob)',

                 'db block gets',

                 'db block changes',

                 'consistent changes',

                 'consistent gets ',

                 'session pga memory',

                 'session uga memory',

                 'parse count (total)',

                 'CPU used by this session',

                 'parse time cpu',

                 'recursive cpu usage',

                 'sorts (disk)',

                 'sorts (memory)',

                 'sorts (rows)',

                 'user commits',

                 'user rollbacks',

                 'execute count'

)

group by b.sid,

         d.spid,

         decode (b.username,null,e.name,b.username),

         b.machine,

         to_char(logon_time,'dd-mon-yy hh:mi:ss pm')

order by 6 desc)

where rownum < 21

Att,

Diego Monteiro

[valchaca]

Bom dia Diego!

Tentei executar esta consulta em meu banco, mas está estourando a temp, e olha que ela estava com 7 Gb de espaço livre...

[diegopedrao]

Valchaca,

Tenta esse:

Selecionar tudo

SELECT   a.sql_text "SQL statement", b.avgcputime "CPU time (s/exec)", b.avgelapstime "Elaps. time 
(s/exec)",
         MAX (c.COST) "Cost", b.executions "Execs", b.TIME "Date/Time", b.module "Module"
    FROM stats$sqltext a,
         (SELECT DISTINCT sql_id, snap_id, executions, avgcputime, avgelapstime, module,
                          TO_CHAR (last_active_time, 'dd-mm-yy hh24:mi') TIME
                     FROM (SELECT DISTINCT sql_id, ROWNUM, snap_id, executions,
                                           ROUND (cpu_time / (executions * 1000000), 0) avgcputime,
                                           ROUND (elapsed_time / (executions * 1000000), 0) 
avgelapstime,
                                           module, last_active_time
                                      FROM stats$sql_summary
                                     WHERE executions != 0
                                       AND module IN (SELECT DISTINCT module
-- To get SQL of application only
                                                      FROM            stats$sql_summary
                                                                WHERE LOWER (module) LIKE 
'yourappname%')
                                       AND TO_CHAR (last_active_time, 'DD.MM.YYYY') =
                                                                               TO_CHAR (SYSDATE, 
'DD.MM.YYYY')
                                  ORDER BY avgelapstime DESC)
                    WHERE ROWNUM <= 10) b,
         stats$sql_plan_usage c
   WHERE LOWER (a.text_subset) NOT LIKE 'insert%' AND a.sql_id = b.sql_id AND a.sql_id = c.sql_id
GROUP BY a.sql_text,
         a.sql_id,
         b.avgcputime,
         b.avgelapstime,
         b.executions,
         b.TIME,
         b.module,
         a.piece,
         a.text_subset
ORDER BY a.sql_id, a.piece;

Att,
Diego Monteiro