数据库优化(Oracle)SQL

--查看CUP的占用率
select c.spid spid, b.osuser osuser, b.machine machine, b.username username, ''''||b.sid||','|| b.serial#||'''' sid, a.sql_text
      from v$sqltext a, v$session b, v$process c
      where a.address = b.sql_address
      and b.status = 'ACTIVE' /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE.
      --ACTVE TRANSACTION ON THAT MOMENT */
      and b.paddr = c.addr
      and a.hash_value = b.sql_hash_value
      and c.spid=&1
      order by c.spid,a.hash_value,a.piece;

--查看数据库等待事件
Select count(*), event
  from v$session_wait
where event not in ('smon timer', 'pmon timer', 'rdbms ipc message',
        'SQL*Net message from client')
group by event
order by 1 desc;

--捕捉运行很久的SQL
select username,sid,opname, round(sofar*100 / totalwork,0) || '%' as progress, time_remaining,sql_text
       from v$session_longops , v$sql
       where time_remaining <> 0
       and sql_address = address
       and sql_hash_value = hash_value;