查询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）
