Récupérer OSUSER à partir des vues DBA_HIST%

[PpPool]

Bonjour à tous

RDBMS : 11.2.0.2

je cherche à récupérer désespéremment l'OSUSER d'une session à partir des vues DBA_HIST%. visiblement cette information se perd avec le repository AWR (cf dba_hist_active_sess_history)

J'arrive à récupérer le username de connexion, la machine, il me manque le compte windows de connexion

Objectifs : revenir dans le temps sur une période d'activité et identifier les requêtes les plus couteuses

Ma query actuelle :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
 
SELECT  USERNAME, 
          PROGRAM,
          MACHINE, 
          SERVICE_NAME,
          GETS, 
          DISK, 
          CPU, 
          ELAPSED,
          SQL_TEXT
  FROM
  (
    SELECT a.USERNAME, a.SQL_ID, a.PROGRAM, a.MACHINE, a.SERVICE_NAME,
           a.GETS, a.DISK, a.CPU, a.ELAPSED,LTRIM(sq.sql_text) AS SQL_TEXT
    FROM ( SELECT du.username AS USERNAME, 
                  s.sql_id AS SQL_ID,
                  s.module AS PROGRAM, 
                  sh.machine AS MACHINE,
                  sn.service_name AS SERVICE_NAME,
                  ROUND (  SUM (s.buffer_gets_delta) / 1000000  / SUM (s.executions_delta),2) AS GETS,
                  ROUND (  SUM (s.disk_reads_delta)/ 1000/ SUM (s.executions_delta),2 ) AS DISK,
                  ROUND (  SUM (s.cpu_time_delta) / 1000000 / SUM (s.executions_delta), 2 ) AS CPU,
                  ROUND (  SUM (s.elapsed_time_delta) / 1000000 / SUM (s.executions_delta), 2 ) AS ELAPSED  
           FROM  dba_hist_sqlstat s,
                 dba_hist_snapshot snp,
                 dba_hist_active_sess_history sh,
                 dba_hist_service_name sn,
                 dba_users du
           WHERE s.snap_id = snp.snap_id
             AND s.sql_id = sh.sql_id
             AND sh.service_hash = sn.service_name_hash
             AND sh.user_id=du.user_id
             AND snp.snap_id >= nl_begin_snap
             AND snp.snap_id <= nl_end_snap
             AND s.parsing_schema_name NOT IN ('SYS', 'SYSTEM')
             AND sh.program NOT LIKE '%(P0%'
             AND sh.machine NOT LIKE 'xxxxxx%'
             AND sh.machine <> 'xxxxx'
             AND sn.service_name IN
                    ('xxxxxx')
           GROUP BY du.username, s.sql_id, s.module, sh.machine, sn.service_name
           HAVING SUM (s.executions_delta) > 0  AND SUM (s.elapsed_time_delta) > 0
         ) a, dba_hist_sqltext sq
    WHERE a.sql_id=sq.sql_id
    ORDER BY ELAPSED DESC )
  WHERE ROWNUM<11;

Quelqu'un a une idée ?

Merci d'avance