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 ;
知道有问题的SPID情况下查看正在运行的SQL .
SELECT a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text
from v$session a,v$process b,v$sqltext c
WHERE b.spid='14150'
AND b.addr=a.paddr AND a.sql_address=c.address(+)order BY c.piece
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及查询哪个SID最消耗资源
查看占I/O较大的正在运行的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 ;
查询物理读写严重的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
查询哪个SID最消耗资源
select s.sid,s.value "CPU Used"
from v$sesstat s,v$statname n
where s.statistic#=n.statistic# and n.name='CPU used by this session'
and s.value>0
order by 2 desc;
查找前十条性能差的SQL
SELECT * FROM
(select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text
FROM v$sqlarea order BY disk_reads DESC )
where ROWNUM<10 ;
6. 查询物件统计信息是否不是最新的。
select * from dba_tables
where wner='DFMS' and table_name='TEST' and LAST_ANALYZED >= sysdate-1
and status='VALID';
select * from dba_indexes where LAST_ANALYZED
where wner='DFMS' and index_name='IDX_TEST' and LAST_ANALYZED >= sysdate-1;
select * from dba_tab_columns where LAST_ANALYZED
where wner='DFMS' and table_name='TEST' and LAST_ANALYZED >= sysdate-1
and COLUMN_NAME='XXXXX' ;
7. 绑定变量窥视(Peeking)问题
9i, 10g 绑定变量窥视使得执行计划出现变化 。11g有改善。如果出现此类
问题导致的性能问题， 需要升级到11g, 或者加入hint进行强制改变执行计划。
8. 坏块导致系统性能 (当然一般alert log中都有error, 查询坏块)
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. Oracle9i, 10g buffer cache及Library 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;
监控SGA Library Cache的命中率，应该小于1%
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
監控內存和硬盤的排序比率，最好使它小于 .10，增加PGA
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
9. redo log都处于active状态。
select * from v$log ; 查看redo log是否都出于active状态，以及大小及组数
是否需要调大 。
10. 碎片程度定期查看
查看碎片程度高的表
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);
分析index的碎片程度：
SQL> analyze index dfms.IDX1_WIP_D_WO_DETAIL_COMID validate structure ;
SQL> select name, del_lf_rows, lf_rows, round((del_lf_rows/(lf_rows+0.0000000001))*100)
2 "Frag Percent" from index_stats where name = 'IDX1_WIP_D_WO_DETAIL_COMID' ;
11. OS及网络查看命令
top, sar, vmstat, iostat, ping , tnsping, route, traceroute,
---------------------------
性能调整FAQ .
[Q]如果设置自动跟踪
[A]用system登录
执行$ORACLE_HOME/rdbms/admin/utlplan.sql创建计划表
执行$ORACLE_HOME/rdbms/admin/plustrce.sql创建plustrace角色
如果想计划表让每个用户都能使用，则
SQL>create public synonym plan_table for plan_table;
SQL> grant all on plan_table to public;
如果想让自动跟踪的角色让每个用户都能使用，则
SQL> grant plustrace to public;
通过如下语句开启/停止跟踪
SET AUTOTRACE ON |OFF
| ON EXPLAIN | ON STATISTICS | TRACEONLY | TRACEONLY EXPLAIN
[Q]如果跟踪自己的会话或者是别人的会话
[A]跟踪自己的会话很简单
Alter session set sql_trace true|false
Or
Exec dbms_session.set_sql_trace(TRUE);
如果跟踪别人的会话，需要调用一个包
exec dbms_system.set_sql_trace_in_session(sid,serial#,true|false)
跟踪的信息在user_dump_dest 目录下可以找到或通过如下脚本获得文件名称（适用于Win环境，如果是unix需要做一定修改）
SELECT p1.value||'\'||p2.value||'_ora_'||p.spid||'.ora' filename
FROM
v$process p,
v$session s,
v$parameter p1,
v$parameter p2
WHERE p1.name = 'user_dump_dest'
AND p2.name = 'db_name'
AND p.addr = s.paddr
AND s.audsid = USERENV ('SESSIONID')
最后，可以通过Tkprof来解析跟踪文件，如
Tkprof 原文件 目标文件 sys=n
[Q]怎么设置整个数据库系统跟踪
[A]其实文档上的alter system set sql_trace=true是不成功的
但是可以通过设置事件来完成这个工作，作用相等
alter system set events
'10046 trace name context forever,level 1';
如果关闭跟踪，可以用如下语句
alter system set events
'10046 trace name context off';
其中的level 1与上面的8都是跟踪级别
level 1：跟踪SQL语句，等于sql_trace=true
level 4：包括变量的详细信息
level 8：包括等待事件
level 12：包括绑定变量与等待事件
[Q]怎么样分析表或索引
[A]命令行方式可以采用analyze命令
如Analyze table tablename compute statistics;
Analyze index|cluster indexname estimate statistics;
ANALYZE TABLE tablename COMPUTE STATISTICS
FOR TABLE
FOR ALL [LOCAL] INDEXES
FOR ALL [INDEXED] COLUMNS;
ANALYZE TABLE tablename DELETE STATISTICS
ANALYZE TABLE tablename VALIDATE REF UPDATE
ANALYZE TABLE tablename VALIDATE STRUCTURE
[CASCADE]|[INTO TableName]
ANALYZE TABLE tablename LIST CHAINED ROWS [INTO TableName]
等等。
如果想分析整个用户或数据库，还可以采用工具包，可以并行分析
Dbms_utility(8i以前的工具包)
Dbms_stats(8i以后提供的工具包)
如
dbms_stats.gather_schema_stats(User,estimate_percent=>100,cascade=> TRUE);
dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);
这是对命令与工具包的一些总结
1、对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句。
a) 可以并行进行，对多个用户，多个Table
b) 可以得到整个分区表的数据和单个分区的数据。
c) 可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区
d) 可以倒出统计信息
e) 可以用户自动收集统计信息
2、DBMS_STATS的缺点
a) 不能Validate Structure
b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，这两个仍旧需要使用Analyze语句。
c) DBMS_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True
3、对于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息。
[Q]怎么样快速重整索引
[A]通过rebuild语句，可以快速重整或移动索引到别的表空间
rebuild有重建整个索引数的功能，可以在不删除原始索引的情况下改变索引的存储参数
语法为
alter index index_name rebuild tablespace ts_name
storage(……);
如果要快速重建整个用户下的索引，可以用如下脚本，当然，需要根据你自己的情况做相应修改
SQL> set heading off
SQL> set feedback off
SQL> spool d:\index.sql
SQL> SELECT 'alter index ' || index_name || ' rebuild '
||'tablespace INDEXES storage(initial 256K next 256K pctincrease 0);'
FROM all_indexes
WHERE ( tablespace_name != 'INDEXES'
OR next_extent != ( 256 * 1024 )
)
AND wner = USER
SQL>spool off
另外一个合并索引的语句是
alter index index_name coalesce，这个语句仅仅是合并索引中同一级的leaf block
消耗不大，对于有些索引中存在大量空间浪费的情况下，有一些作用。
[Q]如何使用Hint提示
[A] 在select/delete/update后写/*+ hint */
如 select /*+ index(TABLE_NAME INDEX_NAME) */ col1...
注意/*和+之间不能有空格
如用hint指定使用某个索引
select /*+ index(cbotab) */ col1 from cbotab;
select /*+ index(cbotab cbotab1) */ col1 from cbotab;
select /*+ index(a cbotab1) */ col1 from cbotab a;
其中
TABLE_NAME是必须要写的，且如果在查询中使用了表的别名，在hint也要用表的别名来代替表名;
INDEX_NAME可以不必写，Oracle会根据统计值选一个索引;
如果索引名或表名写错了，那这个hint就会被忽略;
[Q]怎么样能固定我的执行计划
[A]可以使用OUTLINE来固定SQL语句的执行计划
用如下语句可以创建一个OUTLINE
Create oe replace outline OutLn_Name on
Select Col1,Col2 from Table
where ……
如果要删除Outline，可以采用
Drop Outline OutLn_Name;
对于已经创建了的OutLine，存放在OUTLN用户的OL$HINTS表下面
对于有些语句，你可以使用update outln.ol$hints来更新outline
如update outln.ol$hints(ol_name,'TEST1','TEST2','TEST2','TEST1)
where ol_name in ('TEST1','TEST2');
这样，你就把Test1 OUTLINE与Test2 OUTLINE互换了
如果想利用已经存在的OUTLINE，需要设置以下参数
Alter system/session set Query_rewrite_enabled = true
Alter system/session set use_stored_outlines = true
[Q]v$sysstat中的class分别代表什么
[A]统计类别
1 代表事例活动
2 代表Redo buffer活动
4 代表锁
8 代表数据缓冲活动
16 代表OS活动
32 代表并行活动
64 代表表访问
128 代表调试信息
[Q]怎么快速查找锁与锁等待
[A]数据库的锁是比较耗费资源的，特别是发生锁等待的时候，我们必须找到
发生等待的锁，有可能的话，杀掉该进程。
这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML
语句其实产生了两个锁，一个是表锁，一个是行锁。
可以通过alter system kill session ‘sid,serial#’来杀掉会话
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL
如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待
以下的语句可以查询到谁锁了表，而谁在等待。
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC
以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。如果想知道
锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN
[Q]什么是STATSPACK，我怎么使用它？
[A]Statspack是Oracle 8i以上提供的一个非常好的性能监控与诊断工具，基本上全部包含了BSTAT/ESTAT的功能，更多的信息
可以参考附带文档$ORACLE_HOME/rdbms/admin/spdoc.txt。
安装Statspack:
cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spdrop.sql -- 卸载，第一次可以不需要
sqlplus "/ as sysdba" @spcreate.sql -- 需要根据提示输入表空间名
使用Statspack:
sqlplus perfstat/perfstat
exec statspack.snap; -- 进行信息收集统计，每次运行都将产生一个快照号
-- 获得快照号，必须要有两个以上的快照，才能生成报表
select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;
@spreport.sql -- 输入需要查看的开始快照号与结束快照号
其他相关脚本s:
spauto.sql - 利用dbms_job提交一个作业，自动的进行STATPACK的信息收集统计
sppurge.sql - 清除一段范围内的统计信息，需要提供开始快照与结束快照号
sptrunc.sql - 清除(truncate)所有统计信息