分类:
2009-03-10 18:58:12
优化
1. 找到需要tuning的sql。可以通过wait event来找
2. 查询sql语句的执行计划
3. 检查表和索引的分析情况.是否有索引,是否分析过
4. 检查表内部块的情况。 行迁移和高水平位的问题
5. 较少IO次数:
a.cache表和索引。将表和索引keep到内存中
b.重新组织表结构
c.考虑能否不回表操作
6. 改写sql
ps:
select 'kill -9 ' || pr.spid || ';',
'alter system kill session ' || '''' || s.sid || ',' || s.serial# || '''' || ';',
s.username,s.serial#,s.machine,s.status,pr.spid,sw.*,sq.sql_text,sq.address
from v$session s, v$session_wait sw, v$process pr, v$sqlarea sq
where s.username is not null and sw.sid = s.sid and s.paddr = pr.ADDR
and (s.sql_hash_value = sq.hash_value or s.prev_hash_value = sq.hash_value)
and (s.sql_address = sq.address or s.prev_sql_addr = sq.address) and sw.event not like '%SQL*Net%'
order by s.username;
SELECT /*+ ORDERED */ sql_text FROM v$sqltext a
WHERE (a.hash_value, a.address) IN (
SELECT DECODE (sql_hash_value,0, prev_hash_value,sql_hash_value ),
DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b WHERE b.sid= '&sid')
ORDER BY piece ASC