Oracle 性能视图

ASH：

SELECT t.sql_id, round(SUM(delta_time) / 1000000)
  FROM gv$active_session_history t
WHERE t.inst_id = 2
   AND session_id = 13953
   AND session_serial# = 36495
GROUP BY t.sql_id
ORDER BY 2 DESC;

JOB：

SELECT s.serial#, j.*
   FROM (SELECT /*+ rule*/
          l.inst_id, l.sid, j.*
           FROM dba_jobs j, gv$lock l
          WHERE j.log_user IN ('username')
            AND j.job = l.id2
            AND l.type = 'JQ'
          ORDER BY this_date) j,
        gv$session s
  WHERE j.inst_id = s.inst_id
    AND j.sid = s.sid

-- and what like 'dc_manage_instance_pkg.%'

Long Transaction

SELECT s.serial#, j.*
   FROM (SELECT /*+ rule*/
          l.inst_id, l.sid, j.*
           FROM dba_jobs j, gv$lock l
          WHERE j.log_user IN ('username')
            AND j.job = l.id2
            AND l.type = 'JQ'
          ORDER BY this_date) j,
        gv$session s
  WHERE j.inst_id = s.inst_id
    AND j.sid = s.sid
-- and what like 'xxx_pkg.%'

Long Transaction：

SELECT t.inst_id,
       t.status,
       round(t.used_ublk * 8 / 1024 / 1024, 4) gb,
       t.used_urec,
       to_char(t.start_date, 'YYYY-MM-DD HH24:MI:SS') start_date,
       to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') as cSYSDATE,
       round((SYSDATE - t.start_date) * 24, 2) hours,
       round(s.last_call_et / 3600, 2) last_,
       o.owner,
       o.object_name,
       s.sid,
       s.serial#,
       s.status,
       s.module,
       s.osuser,
       s.machine,
       s.sql_id,
       to_char(s.sql_exec_start, 'YYYY-MM-DD HH24:MI:SS') sql_exec_start,
       s.prev_sql_id,
       to_char(s.prev_exec_start, 'YYYY-MM-D HH24:MI:SS') prev_exec_start,
       t.xid
  FROM gv$transaction t, gv$session s, gv$locked_object l, dba_objects o
 WHERE t.inst_id = s.inst_id
   AND t.addr = s.taddr
   AND t.ses_addr = s.saddr
   AND (t.start_date <= SYSDATE - 2 / 24 OR t.used_urec >= 5000000 OR
       t.used_ublk * 8 / 1024 / 1024 >= 10)
   AND s.inst_id = l.inst_id
   AND s.sid = l.session_id
   AND l.object_id = o.object_id
   AND o.object_type = 'TABLE'
   AND t.xidusn = l.xidusn
   AND t.xidslot = l.xidslot
   AND t.xidsqn = l.xidsqn
   AND s.username IN ('username')
 ORDER BY t.start_date, o.owner, o.object_nameSELECT (to_date('1970-01-01 08', 'yyyy-mm-dd hh24') + asda / 1000 / 60 / 60 / 24) FROM dual;

大事务回滚：

SELECT todo / avg_rate / 3600 / 24 + SYSDATE end_time, t.*
  FROM (SELECT round(undoblocksdone / cputime) avg_rate,
               SYSDATE - cputime / 3600 / 24 start_rollback_time,
               undoblockstotal - undoblocksdone todo,
               t.*,
               SYSDATE
          FROM gv$fast_start_transactions t
         WHERE undoblocksdone <> undoblockstotal
        --  and xid = 'BA0008004E19CF03'
         ORDER BY usn) t;

会话：

SELECT t.sql_exec_start, t.sql_id, t.username, t.event, t.*
  FROM gv$session t
WHERE t.status = 'ACTIVE'
   AND t.username IN ('')
      --and t.osuser = 'xxx'
      --   and sid  IN(24394)
   AND (sid <> sys_context('USERENV', 'SID') OR inst_id <> sys_context('USERENV', 'INSTANCE'))
   AND ownerid <> (sys_context('USERENV', 'INSTANCE') * 65536 + sys_context('USERENV', 'SID'))
ORDER BY 1;

SQL 查看，执行计划：

SELECT elapsed_time / CASE
         WHEN executions = 0 THEN
          1
         ELSE
          executions
       END AVG,
       executions - 0, /*rows_processed, executions + 32426 - rows_processed, child_number,to_char(force_matching_signature),*/
       sql_profile,
       t.*
  FROM gv$sql t
WHERE sql_id = 'gp42a0zypwr1j'
ORDER BY inst_id;

/*

select * from gv$sql_monitor where sql_id='cuzjggxc8asqh'

*/

/*

select * from dba_hist_sql_plan t where sql_id='gp42a0zypwr1j'

select * from gv$sql_plan t where sql_id='cuzjggxc8asqh' and inst_id=4

select * from gv$sqlstats where sql_id='7aqxjfzdm4rjj'

SELECT REPLACE(lpad(' ', 7 * depth - 1), '       ', '      |') || operation || ' ' || options,

       object_owner,

       object_name,

       object_alias,

       id,

       parent_id,

       depth,

       cost,

       cardinality,

       bytes,

       access_predicates,

       filter_predicates

  FROM gv$sql_plan t

WHERE sql_id = 'fg810g4m7t2ra'

   AND child_number = 1

   AND inst_id = 1;

SQL 执行计划：

SELECT REPLACE(lpad(' ', 7 * depth - 1), '       ', '      |') || operation || ' ' || options,
       object_owner,
       object_name,
       object_alias,
       id,
       parent_id,
       depth,
       cost,
       cardinality,
       bytes,
       access_predicates,
       filter_predicates
  FROM gv$sql_plan t
WHERE sql_id = 'fg810g4m7t2ra'
   AND child_number = 1
   AND inst_id = 1;  

temp空间：

 SELECT SUM(blocks) FROM gv$sort_usage WHERE session_addr = '0000006B83643C70';

会话已使用undo空间：

SELECT s.serial#,
       s.saddr,
       s.sid,
       s.username,
       used_ublk,
       t.used_ublk * 8192 / 1024 / 1024 || 'M' "Mb",
       substr(s.program, 1, 78),
       prev_exec_start,
       t.start_date,
       s.sql_id
  FROM gv$session s, gv$transaction t
WHERE t.addr = s.taddr
   AND t.inst_id = s.inst_id
   AND sid = 6830
ORDER BY t.used_ublk DESC