oracle 脚本优化,oracle优化常用脚本

在日常生活中，我们总会遇见数据库慢，但是却无从下手的问题，首先数据库应该是一个软件，所以我们需要对其操作系统平台进行检查，看是否有相关故障，包括I/O、内存、CPU，当然也有硬件方面的检查，在确定不是这些外在因素的影响的情况下，多半会是数据库上，一般一个应用良好的系统，突然变慢，我们需要问在这之前，对当前应用做了什么改变，然后，通过top等工具检查是否存在异常进程，也就是消耗CPU百分比较高的oracle进程,如果有恭喜你，那么问题可能就出现了，如何根据pid来来捕获这个sql呢？以下是我通常所用到的脚本：

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.paddr = (select addr from v$process c where c.spid ='&pid'))

order by piece ASC;

根据提示输入PID，即可捕获该sql语句，那么接下来就是对该sql进行优化，最常用采用autotrace 来收集sql执行计划，优化索引结构，当然在此不细讲这块。

如果在操作系统层面不能发现异常进程，看起来都很正常，那么这时数据库的sql也可能不正常，需要根据v$session_wait视图找到一些等待事件：

SELECT a.EVENT, b.SID , b.SERIAL#,c.HASH_VALUE,c.SQL_TEXT FROM v$session_wait a , v$session b , v$sqlarea c WHERE a.SID=b.SID AND b.SQL_HASH_VALUE=c.HASH_VALUE AND a.EVENT NOT LIKE 'SQL*Net%' AND a.EVENT NOT LIKE 'rdbms%'；

那么接下来的问题也就不言而喻，继续优化sql语句去。

令查看当前会话sid锁表情况可通过如下脚本：

select o.owner,o.object_name,l.session_id,l.os_user_name

from v$locked_object l,dba_objects o

where o.object_id = l.object_id;

select spid from v$process where addr = (select paddr from v$session where sid= &sid);

对一些异常进程占用PGA情况分析脚本

show parameter workarea_size_policy

SELECT b.SID,b.SERIAL#,b.PROGRAM,a.pga_used_mem/1024/1024 pga_used_mem, a.pga_alloc_mem , a.pga_max_mem

FROM v$process a, v$session b WHERE a.addr = b.paddr ORDER BY a.pga_used_mem DESC;

以下脚本可以查看单个用户占用资源情况

select se.SID, ses.username, ses.osuser, n.NAME, se.VALUE

from v$statname n, v$sesstat se, v$session ses

where n.statistic# = se.statistic# and

se.sid = ses.sid and

ses.username is not null and

n.name in ('CPU used by this session',

'db block gets',

'consistent gets',

'physical reads',

'free buffer requested',

'table scans (long tables)',

'table scan rows gotten',

'sorts (memory)',

'sorts (disk)',

'sorts (rows)',

'session uga memory max' ,

'session pga memory max')

order by sid, n.statistic#;