查询Oracle正在执行的sql语句及执行该语句的用户

SELECT b.sid oracleID,
    b.username 登录Oracle用户名,
    b.serial#,
    spid 操作系统ID,
    paddr,
    sql_text 正在执行的SQL,
    b.machine 计算机名
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr
  AND b.sql_hash_value = c.hash_value

或者

select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT
 from v$session a, v$sqlarea b 
where a.sql_address = b.address

查询Oracle执行过的sql语句及执行该语句的用户

---执行过的
select a.USERNAME        登录Oracle用户名,
       a.MACHINE         计算机名,
       SQL_TEXT,
       b.FIRST_LOAD_TIME,
       b.SQL_FULLTEXT
  from v$sqlarea b, v$session a
 where a.sql_hash_value = b.hash_value
   and b.FIRST_LOAD_TIME between '2016-11-01/09:24:47' and
       '2016-11-31/09:24:47'
 order by b.FIRST_LOAD_TIME desc;

查看正在执行sql的发起者的发放程序

SELECT OSUSER 电脑登录身份,
    PROGRAM 发起请求的程序,
    USERNAME 登录系统的用户名,
    SCHEMANAME,
    B.Cpu_Time 花费cpu的时间,
    STATUS,
    B.SQL_TEXT 执行的sql
FROM V$SESSION A
LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS
          AND A.SQL_HASH_VALUE = B.HASH_VALUE
ORDER BY b.cpu_time DESC

 v$sql、v$sqlarea 、v$sqltext

这三哥视图都可以用于查询共享池中已经解析过的SQL语句及其相关信息。

V$SQL中列出了共享SQL区中所有语句的信息，它不包含GROUP BY字句，并且为每一条SQL语句中单独存放一条记录；

V$SQLAREA中一条记录显示了一条共享SQL区中的统计信息。它提供了有在内存中、解析过的和准备运行的SQL语句的统计信息；

V$SQLTEXT包含了库缓存中所有共享游标对应的SQL语句。它将SQL语句分片显示。

查出oracle当前的被锁对象

SELECT l.session_id sid,
    s.serial#,
    l.locked_mode 锁模式,
    l.oracle_username 登录用户,
    l.os_user_name 登录机器用户名,
    s.machine 机器名,
    s.terminal 终端用户名,
    o.object_name 被锁对象名,
    s.logon_time 登录数据库时间
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
  AND l.session_id = s.sid
ORDER BY sid, s.serial#;

kill掉当前的锁对象可以为

alter system kill session 'sid， s.serial#‘;

查看占io较大的正在运行的session

SELECT se.sid,
       se.serial#,
       pr.SPID,
       se.username,
       se.status,
       se.terminal,
       se.program,
       se.MODULE,
       se.sql_address,
       st.event,
       st. p1text,
       si.physical_reads,
       si.block_changes
  FROM v$session se, v$session_wait st, v$sess_io si, v$process pr
 WHERE st.sid = se.sid
   AND st. sid = si.sid
   AND se.PADDR = pr.ADDR
   AND se.sid > 6
   AND st. wait_time = 0
   AND st.event NOT LIKE '%SQL%'
 ORDER BY physical_reads DESC

查询碎片程度高

（实际使用率小于30%）的表，也就是可以收缩的表

条件为什么block>100，因为一些很小的表，只有几行数据实际大小很小，但是block一次性分配就是5个（11g开始默认一次性分配1M的block大小了，见create table storged的NEXT参数），5个block相对于几行小表数据来说就相差太大了

算法中/0.9是因为块的pfree一般为10%，所以一个块最多只用了90%，而且一行数据大于8KB时容易产生行链接，把一行分片存储，一样的一个块连90%都用不满

AVG_ROW_LEN还是比较准的，比如个人实验情况一表6个字段，一个number，其他5个都是char(100)但是实际数据都是’1111111’7位，AVG_ROW_LEN显示依然为513

SELECT TABLE_NAME,(BLOCKS*8192/1024/1024)"理论大小M",
(NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)"实际大小M",
round((NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024),3)*100||'%' "实际使用率%"
FROM USER_TABLES where blocks>100 and (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024)<0.3
order by (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024) desc

查询索引碎片的比例

（索引删除行数除以索引总行数的百分比>30%即认为索引碎片大），也就是需要重建的索引

select name,
       del_lf_rows,
       lf_rows,
       round(del_lf_rows / decode(lf_rows, 0, 1, lf_rows) * 100, 0) || '%' frag_pct
  from index_stats
 where round(del_lf_rows / decode(lf_rows, 0, 1, lf_rows) * 100, 0) > 30;

集群因子clustering_factor高的表

集群因子越接近块数越好，接近行数则说明索引列的列值相等的行分布极度散列，可能不走索引扫描而走全表扫描

select tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor,
round(nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows),3)*100||'%' "集群因子接近行数"
from user_tables tab, user_indexes ind where tab.table_name=ind.table_name
and tab.blocks>100
and nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows) between 0.35 and 3

或

select tab.owner,tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor,
round(nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows),3)*100||'%' "集群因子接近行数"
from dba_tables tab, dba_indexes ind where tab.table_name=ind.table_name and tab.owner
not in ('SYS','SYSTEM','WMSYS','DBSNMP','CTXSYS','XDB','ORDDATA','SYSMAN','CATALOG','APEX_030200','MDSYS','OLAPSYS','EXFSYS')
and tab.blocks>100
and nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows) between 0.35 and 3

根据sid查spid或根据spid查sid

select s.sid, s.serial#, s.LOGON_TIME, s.machine, p.spid, p.terminal
  from v$session s, v$process p
 where s.paddr = p.addr
   and s.sid = XX
    or p.spid = YY

根据sid查看具体的sql语句

select username, sql_text, machine, osuser
  from v$session a, v$sqltext_with_newlines b
 where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value) =
       b.hash_value
   and a.sid = &sid
 order by piece;

根据spid查询具体的sql语句

select ss.SID,
       ss.SERIAL#,
       ss.LOGON_TIME,
       pr.SPID,
       ss.action,
       sa.SQL_FULLTEXT,
       ss.machine,
       ss.TERMINAL,
       ss.PROGRAM,
       ss.USERNAME,
       ss.STATUS,
       ss.OSUSER,
       ss.last_call_et
  from v$process pr, v$session ss, v$sqlarea sa
 where ss.status = 'ACTIVE'
   and ss.username is not null
   and pr.ADDR = ss.PADDR
   and ss.SQL_ADDRESS = sa.ADDRESS
   and ss.SQL_HASH_VALUE = sa.HASH_VALUE
   and pr.spid = XX

查看历史session_id的SQL来自哪个IP

文件里面有sid和具体sql，如果trace存在incident，那trace就看不到具体sql，但是可以在incident文件中看到具体的sql，如DW_ora_17751.trc中17751就是spid，里面有这样的内容Incident 115 created, dump file: /XX/incident/incdir_115/DW_ora_17751_i115.trc，那么在DW_ora_17751_i115.trc就可以看到具体的sql语句)

DB_ora_29349.trc中出现如下

* SESSION ID:(5057.12807) 2016-10-26 14:45:52.726

通过表V$ACTIVE_SESSION_HISTORY来查，如下

select a.sql_id, a.machine, a.*
  from V$ACTIVE_SESSION_HISTORY a
 where a.session_id = 5057
   and a.SESSION_SERIAL# = 12807

查询上面的machine的IP是多少

select s.sid, s.serial#, s.LOGON_TIME, s.machine, p.spid, p.terminal
  from v$session s, v$process p
 where s.paddr = p.addr
   and s.machine = 'localhost'

通过上面的spid在oracle服务器上执行netstat -anp |grep spid即可

[oracle@dwdb trace]$ netstat -anp |grep 17630
tcp      210      0 192.168.64.228:11095        192.168.21.16:1521          ESTABLISHED 17630/oracleDB
tcp        0      0 ::ffff:192.168.64.228:1521  ::ffff:192.168.64.220:59848 ESTABLISHED 17630/oracleDB

出现两个，说明来自220，连接了228数据库服务器，但是又通过228服务器的dblink去连接了16服务器

查询DML死锁会话sid

（对象锁被释放的等待者），及引起死锁的堵塞者会话blocking_session（对象加锁者）

select sid,
       blocking_session,
       LOGON_TIME,
       sql_id,
       status,
       event,
       seconds_in_wait,
       state,
       BLOCKING_SESSION_STATUS
  from v$session
 where event like 'enq%'
   and state = 'WAITING'
   and BLOCKING_SESSION_STATUS = 'VALID'

BLOCKING_SESSION:Session identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID.

可以在v$session.LOGON_TIME上看到引起死锁的堵塞者会话比等待者要早

如果遇到RAC环境，一定要用gv$来查，并且执行alter system kill session ‘sid,serial#’要到RAC对应的实例上去执行

或如下也可以

select
           (select username from v$session where sid=a.sid) blocker,
         a.sid,
         a.id1,
         a.id2,
       ' is blocking ' "IS BLOCKING",
         (select username from v$session where sid=b.sid) blockee,
             b.sid
    from v$lock a, v$lock b
   where a.block = 1
     and b.request > 0
     and a.id1 = b.id1
     and a.id2 = b.id2;

查询DDL锁的sql

查询x开头的动态性能视图，只能用sys用户

SELECT sid, event, p1raw, seconds_in_wait, wait_time
  FROM sys.v_$session_wait
 WHERE event like 'library cache %'

p1raw结果为’0000000453992440’

SELECT s.sid, kglpnmod "Mode", kglpnreq "Req", s.LOGON_TIME
FROM x$kglpn p, v$session s
WHERE p.kglpnuse=s.saddr
AND kglpnhdl='0000000453992440';

结果为671 0 3 2011-11-1 12:00:00

525 2 0 2011-11-4 12:00:00

查询锁住的DDL对象

select d.session_id, s.SERIAL#, d.name
  from dba_ddl_locks d, v$session s
 where d.owner = 'CC'
   and d.SESSION_ID = s.sid

查询当前正在执行的sql

SELECT s.sid,
       s.serial#,
       s.username,
       spid,
       v$sql.sql_id,
       machine,
       s.terminal,
       s.program,
       sql_text
  FROM v$process, v$session s, v$sql
 WHERE addr = paddr
   and s.sql_id = v$sql.sql_id
   AND sql_hash_value = hash_value

查询正在执行的SCHEDULER_JOB

select owner, job_name, sid, b.SERIAL#, b.username, spid
  from ALL_SCHEDULER_RUNNING_JOBS, v$session b, v$process
 where session_id = sid
   and paddr = addr

查询正在执行的dbms_job

select job,b.sid,b.SERIAL#,b.username,spid from DBA_JOBS_RUNNING a ,v$session b,v$process  where a.sid=b.sid and paddr=addr

查询一个会话session、process平均消耗多少内存，查看下面avg_used_M值

select round(sum(pga_used_mem) / 1024 / 1024, 0) total_used_M,
       round(sum(pga_used_mem) / count(1) / 1024 / 1024, 0) avg_used_M,       
       round(sum(pga_alloc_mem) / 1024 / 1024, 0) total_alloc_M,
       round(sum(pga_alloc_mem) / count(1) / 1024 / 1024, 0) avg_alloc_M
  from v$process;

TOP 10 执行次数排序

select *
from (select executions,username,PARSING_USER_ID,sql_id,sql_text  
   from v$sql,dba_users where user_id=PARSING_USER_ID order by executions desc)
where rownum <=5;

TOP 10 物理读排序（消耗IO排序，即最差性能SQL、低效SQL排序）

select *
from (select DISK_READS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text  
   from v$sql,dba_users where user_id=PARSING_USER_ID order by DISK_READS desc)
where rownum <=5;

（不要使用DISK_READS/ EXECUTIONS来排序，因为任何一条语句不管执行几次都会耗逻辑读和cpu，可能不会耗物理读（遇到LRU还会耗物理读，LRU规则是执行最不频繁的且最后一次执行时间距离现在最久远的就会被交互出buffer cache），是因为buffer cache存放的是数据块，去数据块里找行一定会消耗cpu和逻辑读的。Shared pool执行存放sql的解析结果，sql执行的时候只是去share pool中找hash value，如果有匹配的就是软解析。所以物理读逻辑读是在buffer cache中，软解析硬解析是在shared pool）

TOP 10 逻辑读排序（消耗内存排序）

select *
from (select BUFFER_GETS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text  
   from v$sql,dba_users where user_id=PARSING_USER_ID order by BUFFER_GETS desc)
where rownum <=5;

（不要使用BUFFER_GETS/ EXECUTIONS来排序，因为任何一条语句不管执行几次都会耗逻辑读和cpu，可能不会耗物理读（遇到LRU还会耗物理读，LRU规则是执行最不频繁的且最后一次执行时间距离现在最久远的就会被交互出buffer cache），是因为buffer cache存放的是数据块，去数据块里找行一定会消耗cpu和逻辑读的。Shared pool执行存放sql的解析结果，sql执行的时候只是去share pool中找hash value，如果有匹配的就是软解析。所以物理读逻辑读是在buffer cache中，软解析硬解析是在shared pool）

ORACLE常用性能监控SQL【二】（上）