Oracle性能 - 常用查询脚本(final)
1. 查询长时间操作的SQL(或者通过OEM图形界面查看)
V$SESSION_LONGOPS
查询运行超过6秒钟的操作，这些操作包括很多备份恢复功能，统计信息收集，查询操作，
不同版本可能有更多的操作加入 。 Oracle OEM中的长时间操作就是读取这个视图。
要监控query execution progress，必须满足以下前提条件:
1) Oracle优化器使用 CBO;
2) 数据对象已经收集了统计信息;
3) 初始化参数 TIMED_STATISTICS = true 或是 SQL_TRACE = true ;
如果是Oracle RAC, 可以使用以下脚本：
SELECT a.opname,
a.SID,
a.serial#,
a.sql_id,
a.start_time,
a.time_remaining,
a.elapsed_seconds,
b.sql_fulltext,
a.MESSAGE
FROM gv$session_longops a, gv$sql b
WHERE a.start_time > SYSDATE - 0.1
AND a.time_remaining > 0
AND a.sql_id = b.sql_id
ORDER BY a.start_time DESC;
如果是单机, 可以使用以下脚本：
SELECT a.opname,
a.SID,
a.serial#,
a.sql_id,
a.start_time,
a.time_remaining,
a.elapsed_seconds,
b.sql_fulltext,
a.MESSAGE,
b.module,
b.executions
FROM v$session_longops a, v$sql b
WHERE a.start_time > SYSDATE - 0.1
AND a.time_remaining > 0
AND a.sql_id = b.sql_id
ORDER BY a.start_time DESC;
2. 通过Linux PID及SID查询相关SQL及程序：
select a.sid,a.serial#, a.program,b.spid from v$session a,v$process b
where a.paddr=b.addr and b.spid in ('1245','2985','5884');
spid为top查看到的os段的process id .
紧急处理时候可以通过kill session方式或直接kill os process来结束进程。
Alter system kill session 'SID,SERIAL#' ;
Kill -9 SPID (unix,linux)
orakill sid(oracle_instance_name) spid (windows)
select a.username,a.machine,a.program,b.spid,c.sql_text
from v$session a,v$process b,v$sqlarea c
where a.paddr=b.addr and c.hash_value=a.sql_hash_value
and c.address=a.sql_address and b.spid=12984 ;
3. 查询Oracle库中的Lock (或通过Toad中session browser参看lock情况)
查看数据库中的锁(LOCK),找出程序及SQL
SELECT se.inst_id, se.SID, se.serial#,lk.SID,
se.username,se.OSUser,se.Machine,se.program,
DECODE (lk.TYPE,
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
lk.TYPE)
lock_type,
DECODE (lk.lmode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR (lk.lmode))
mode_held,
DECODE (lk.request,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR (lk.request))
mode_requested,
TO_CHAR (lk.id1) lock_id1,
TO_CHAR (lk.id2) lock_id2,
ob.owner,
ob.object_type,
ob.object_name,
DECODE (lk.Block,
0,
'No',
1,
'Yes',
2,
'Global')
block,
se.lockwait,
sq.sql_fulltext
FROM GV$lock lk, dba_objects ob, GV$session se, GV$sql sq
WHERE lk.TYPE IN ('TM', 'UL')
AND lk.SID = se.SID
AND lk.id1 = ob.object_id(+)
AND (lk.inst_id = se.inst_id)
AND sq.address=se.sql_address ;
4. 通过AWR查询性能问题(默认有1小时延迟，不能查询当前时间的session)
SQL> conn / AS SYSDBA
SQL> @/u01/product/oracle/rdbms/admin/awrrpt.sql
输入 report_type 的值:
输入 num_days 的值: 2 --- 现在到过去两天时间内的snap id (可以查看到).
输入 begin_snap 的值: 2147 --- 输入的开始及结束的snap id 对应您要查找的出现问题的时间段。
输入 end_snap 的值: 2182
输入 report_name 的值:
Report written to awrrpt_1_2177_2182.html
SQL> exit
下载awrrpt_1_2177_2182.html并打开查看。
ASH保存了系统最新的处于等待的会话记录，可以用来诊断数据库的当前状态；
而AWR中的信息最长可能有1小时的延迟，所以其采样信息并不能用于诊断数据
库的当前状态，但可以用来作为一段时期内数据库性能调整的参考。
5. 查询物理讀寫嚴重的SQL
SELECT *
FROM (SELECT sql_text, module,
disk_reads / DECODE (executions, 0, 1, executions) AS tt
FROM v$sqlarea a
ORDER BY tt DESC)
WHERE ROWNUM <= 20
6. 查询物件统计信息是否不是最新的。
7. 坏块导致系统性能 (查询坏块)
SELECT segment_name,segment_type,extent_id,block_id, blocks
from dba_extents t
where file_id = 10
AND 51896 between block_id and (block_id + blocks - 1)
8. 绑定变量窥视(Peeking)问题
绑定变量窥视使得执行计划发生改变。
8. Oracle9i, 10g buffer cache的命中率
9i 命中率 :
// oracle9i data buffer hit ratio .
select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 40 and b.statistic# = 41 and c.statistic# = 42;
10g 命中率 :
// oracle10g data buffer hit ratio .
select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 47 and b.statistic# = 50 and c.statistic# = 54;
8. Oracle