Oracle 常用性能查看语句

Myth丶恋晨 2022-07-12 04:30 333阅读 0赞

判断回滚段竞争的SQL语句：（当Ratio大于2时存在回滚段竞争，需要增加更多的回滚段）  
select rn.name, rs.GETS, rs.WAITS, (rs.WAITS / rs.GETS) \* 100 ratio  
from v$rollstat rs, v$rollname rn  
where rs.USN = rn.usn

判断恢复日志竞争的SQL语句：（immediate\_contention或wait\_contention的值大于1时存在竞争）  
select name,  
(t.IMMEDIATE\_MISSES /  
decode((t.IMMEDIATE\_GETS + t.IMMEDIATE\_MISSES),  
0,  
\-1,  
(t.IMMEDIATE\_GETS + t.IMMEDIATE\_MISSES))) \* 100 immediate\_contention,  
(t.MISSES / decode((t.GETS + t.MISSES), 0, -1, (t.GETS + t.MISSES))) \* 100 wait\_contention  
from v$latch t  
where name in ('redo copy', 'redo allocation')

判断表空间碎片：（如果最大空闲空间占总空间很大比例则可能不存在碎片，如果比例较小，且有许多空闲空间，则可能碎片很多）  
select t.tablespace\_name,  
sum(t.bytes),  
max(t.bytes),  
count(\*),  
max(t.bytes) / sum(t.bytes) radio  
from dba\_free\_space t  
group by t.tablespace\_name  
order by t.tablespace\_name

确定命中排序域的次数：  
select t.NAME, t.VALUE from v$sysstat t where t.NAME like 'sort%'

查看当前SGA值：  
select \* from v$sga

确定高速缓冲区命中率：（如果命中率低于70％，则应该加大init.ora参数中的DB\_BLOCK\_BUFFER的值）  
select 1 - sum(decode(name, 'physical reads', value, 0)) /  
(sum(decode(name, 'db block gets', value, 0)) +  
sum(decode(name, 'consistent gets', value, 0))) hit\_ratio  
from v$sysstat t  
where name in ('physical reads', 'db block gets', 'consistent gets')

确定共享池中的命中率：（如果ratio1大于1时，需要加大共享池，如果ratio2大于10％时，需要加大共享池SHARED\_POOL\_SIZE）  
select sum(pins) pins,  
sum(reloads) reloads,  
(sum(reloads) / sum(pins)) \* 100 ratio1  
from v$librarycache

select sum(gets) gets,  
sum(getmisses) getmisses,  
(sum(getmisses) / sum(gets)) \* 100 ratio2  
from v$rowcache

查询INIT.ORA参数：  
select \* from v$parameter

/  
Oracle性能参数查看（转）  
0、数据库参数属性  
col PROPERTY\_NAME format a25  
col PROPERTY\_VALUE format a30  
col DESCRIPTION format a100  
select \* from database\_properties;  
select \* from v$version;  
1、求当前会话的SID，SERIAL\#  
SELECT Sid, Serial\# FROM V$session  
WHERE Audsid = Sys\_Context('USERENV', 'SESSIONID');  
2、查询session的OS进程ID  
SELECT p.Spid "OS Thread", b.NAME "Name-User", s.Program, s.Sid, s.Serial\#,s.Osuser, s.Machine  
FROM V$process p, V$session s, V$bgprocess b  
WHERE p.Addr = s.Paddr  
AND p.Addr = b.Paddr And (s.sid=&1 or p.spid=&1)  
UNION ALL  
SELECT p.Spid "OS Thread", s.Username "Name-User", s.Program, s.Sid,s.Serial\#, s.Osuser, s.Machine  
FROM V$process p, V$session s  
WHERE p.Addr = s.Paddr  
And (s.sid=&1 or p.spid=&1)  
AND s.Username IS NOT NULL;

3、根据sid查看对应连接正在运行的sql   
SELECT /\*+ PUSH\_SUBQ \*/ Command\_Type, Sql\_Text, Sharable\_Mem, Persistent\_Mem, Runtime\_Mem, Sorts,  
Version\_Count, Loaded\_Versions, Open\_Versions, Users\_Opening, Executions,  
Users\_Executing, Loads, First\_Load\_Time, Invalidations, Parse\_Calls,  
Disk\_Reads, Buffer\_Gets, Rows\_Processed, SYSDATE Start\_Time,  
SYSDATE Finish\_Time, '>' || Address Sql\_Address, 'N' Status  
FROM V$sqlarea WHERE Address = (SELECT Sql\_Address  
FROM V$session WHERE Sid = &sid );

4、查找object为哪些进程所用  
SELECT p.Spid, s.Sid, s.Serial\# Serial\_Num, s.Username User\_Name,  
a.TYPE Object\_Type, s.Osuser Os\_User\_Name, a.Owner,  
a.OBJECT Object\_Name,  
Decode(Sign(48 - Command), 1, To\_Char(Command), 'Action Code \#' || To\_Char(Command)) Action,  
p.Program Oracle\_Process, s.Terminal Terminal, s.Program Program,  
s.Status Session\_Status  
FROM V$session s, V$access a, V$process p  
WHERE s.Paddr = p.Addr  
AND s.TYPE = 'USER'  
AND a.Sid = s.Sid  
AND a.OBJECT = '&obj'  
ORDER BY s.Username, s.Osuser

5、查看有哪些用户连接  
SELECT s.Osuser Os\_User\_Name,Decode(Sign(48 - Command),1,To\_Char(Command),  
'Action Code \#' || To\_Char(Command)) Action,  
p.Program Oracle\_Process, Status Session\_Status, s.Terminal Terminal,  
s.Program Program, s.Username User\_Name,  
s.Fixed\_Table\_Sequence Activity\_Meter, '' Query, 0 Memory,  
0 Max\_Memory, 0 Cpu\_Usage, s.Sid, s.Serial\# Serial\_Num  
FROM V$session s, V$process p  
WHERE s.Paddr = p.Addr  
AND s.TYPE = 'USER'  
ORDER BY s.Username, s.Osuser

6、根据v.sid查看对应连接的资源占用等情况  
SELECT n.NAME, v.VALUE, n.CLASS, n.Statistic\# FROM V$statname n, V$sesstat v  
WHERE v.Sid = &sid  
AND v.Statistic\# = n.Statistic\#  
ORDER BY n.CLASS, n.Statistic\#

7、查询耗资源的进程（top session）  
SELECT s.Schemaname Schema\_Name,Decode(Sign(48 - Command),  
1, To\_Char(Command), 'Action Code \#' || To\_Char(Command)) Action,Status Session\_Status, s.Osuser Os\_User\_Name, s.Sid, p.Spid,s.Serial\# Serial\_Num, Nvl(s.Username, '\[Oracle process\]') User\_Name,  
s.Terminal Terminal, s.Program Program, St.VALUE Criteria\_Value  
FROM V$sesstat St, V$session s, V$process p  
WHERE St.Sid = s.Sid  
AND St.Statistic\# = To\_Number('38')  
AND ('ALL' = 'ALL' OR s.Status = 'ALL')  
AND p.Addr = s.Paddr  
ORDER BY St.VALUE DESC, p.Spid ASC, s.Username ASC, s.Osuser ASC

8、查看锁（lock）情况  
SELECT /\*+ RULE \*/ Ls.Osuser Os\_User\_Name, Ls.Username User\_Name,Decode(Ls.TYPE,  
'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock','TX', 'Transaction enqueue lock', 'UL', 'User supplied lock') Lock\_Type,o.Object\_Name OBJECT,Decode(Ls.Lmode,1, NULL, 2, 'Row Share', 3, 'Row Exclusive',  
4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive',NULL) Lock\_Mode,o.Owner, Ls.Sid, Ls.Serial\# Serial\_Num, Ls.Id1, Ls.Id2 FROM Sys.Dba\_Objects o,   
(SELECT s.Osuser, s.Username, l.TYPE, l.Lmode, s.Sid, s.Serial\#, l.Id1,l.Id2 FROM V$session s, V$lock l  
WHERE s.Sid = l.Sid) Ls  
WHERE o.Object\_Id = Ls.Id1  
AND o.Owner <> 'SYS'  
ORDER BY o.Owner, o.Object\_Name;  
9、查看等待（wait）情况  
SELECT Ws.CLASS, Ws.COUNT COUNT, SUM(Ss.VALUE) Sum\_Value  
FROM V$waitstat Ws, V$sysstat Ss  
WHERE Ss.NAME IN ('db block gets', 'consistent gets')  
GROUP BY Ws.CLASS, Ws.COUNT;

10、求process/session的状态  
SELECT p.Pid, p.Spid, s.Program, s.Sid, s.Serial\#  
FROM V$process p, V$session s  
WHERE s.Paddr = p.Addr;

11、求谁阻塞了某个session(10g)  
SELECT Sid, Username, Event, Blocking\_Session, Seconds\_In\_Wait, Wait\_Time  
FROM V$session  
WHERE State IN ('WAITING')  
AND Wait\_Class != 'Idle';

12、查会话的阻塞  
col user\_name format a32  
SELECT /\*+ rule \*/ Lpad(' ', Decode(l.Xidusn, 0, 3, 0)) || l.Oracle\_Username User\_Name,  
o.Owner, o.Object\_Name, 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;  
col username format a15  
col lock\_level format a8  
col owner format a18  
col object\_name format a32  
SELECT /\*+ rule \*/ s.Username,Decode(l.TYPE, 'tm', 'table lock', 'tx', 'row lock', NULL) Lock\_Level,  
o.Owner, o.Object\_Name, s.Sid, s.Serial\#  
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;

13、求等待的事件及会话信息/求会话的等待及会话信息  
SELECT Se.Sid, s.Username, Se.Event, Se.Total\_Waits, Se.Time\_Waited,Se.Average\_Wait  
FROM V$session s, V$session\_Event Se  
WHERE s.Username IS NOT NULL  
AND Se.Sid = s.Sid  
AND s.Status = 'ACTIVE'  
AND Se.Event NOT LIKE '%SQL\*Net%'  
ORDER BY s.Username;  
SELECT s.Sid, s.Username, Sw.Event, Sw.Wait\_Time, Sw.State,Sw.Seconds\_In\_Wait  
FROM V$session s, V$session\_Wait Sw  
WHERE s.Username IS NOT NULL  
AND Sw.Sid = s.Sid  
AND Sw.Event NOT LIKE '%SQL\*Net%'  
ORDER BY s.Username;

14、求会话等待的file\_id/block\_id  
col event format a24  
col p1text format a12  
col p2text format a12  
col p3text format a12  
SELECT Sid, Event, P1text, P1, P2text, P2, P3text, P3  
FROM V$session\_Wait  
WHERE Event NOT LIKE '%SQL%'  
AND Event NOT LIKE '%rdbms%'  
AND Event NOT LIKE '%mon%'  
ORDER BY Event;  
SELECT NAME, Wait\_Time  
FROM V$latch l  
WHERE EXISTS (SELECT 1  
FROM (SELECT Sid, Event, P1text, P1, P2text, P2, P3text, P3  
FROM V$session\_Wait  
WHERE Event NOT LIKE '%SQL%'  
AND Event NOT LIKE '%rdbms%'  
AND Event NOT LIKE '%mon%') x  
WHERE x.P1 = l.Latch\#);

15、求会话等待的对象  
col owner format a18  
col segment\_name format a32  
col segment\_type format a32  
SELECT Owner, Segment\_Name, Segment\_Type  
FROM Dba\_Extents  
WHERE File\_Id = &File\_Id  
AND &Block\_Id BETWEEN Block\_Id AND Block\_Id + Blocks - 1;

16、求出某个进程，并对它进行跟踪  
SELECT s.Sid, s.Serial\#  
FROM V$session s, V$process p  
WHERE s.Paddr = p.Addr  
AND p.Spid = &1;  
Exec Dbms\_System.Set\_Sql\_Trace\_In\_Session(&1, &2, TRUE);  
Exec Dbms\_System.Set\_Sql\_Trace\_In\_Session(&1, &2, FALSE);

17、求当前session的跟踪文件  
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 = 'instance\_name'  
AND p.Addr = s.Paddr  
AND s.Audsid = Userenv('SESSIONID')  
AND p.Background IS NULL  
AND Instr(p.Program, 'CJQ') = 0;

18、求出锁定的对象  
SELECT Do.Object\_Name, Session\_Id, Process, Locked\_Mode  
FROM V$locked\_Object Lo, Dba\_Objects Do  
WHERE Lo.Object\_Id = Do.Object\_Id;  
19、DB\_Cache建议  
SELECT size\_for\_estimate, buffers\_for\_estimate, estd\_physical\_read\_factor, estd\_physical\_reads  
FROM V$DB\_CACHE\_ADVICE  
WHERE name = 'DEFAULT'  
AND block\_size = (SELECT value FROM V$PARAMETER WHERE name = 'db\_block\_size')  
AND advice\_status = 'ON';  
20、查看各项SGA相关参数：SGA，SGASTAT  
select substr(name,1,10) name,substr(value,1,10) value   
from v$parameter where name = 'log\_buffer';  
select \* from v$sgastat ;  
select \* from v$sga;  
show parameters area\_size   \#查看 各项区域内存参数， 其中sort\_area为排序参数用；  
   
各项视图建议参数值：V$DB\_CACHE\_ADVICE、V$SHARED\_POOL\_ADVICE），关于PGA  
也有相关视图V$PGA\_TARGET\_ADVICE 等。  
21、内存使用锁定在物理内存：  
AIX 5L（AIX 4.3.3 以上）  
logon aix as root  
cd /usr/samples/kernel  
./vmtune (信息如下) v\_pingshm已经是1  
./vmtune -S 1  
然后oracle用户修改initSID.ora 中 lock\_sga = true  
重新启动数据库  
HP UNIX  
Root身份登陆  
Create the file "/etc/privgroup": vi /etc/privgroup  
Add line "dba MLOCK" to file  
As root, run the command "/etc/setprivgrp -f /etc/privgroup":  
$/etc/setprivgrp -f /etc/privgroup  
oracle用户修改initSID.ora中lock\_sga=true  
重新启动数据库  
SOLARIS (solaris2.6以上)  
8i版本以上数据库默认使用隐藏参数 use\_ism = true ，自动锁定SGA于内存中,不用设置  
lock\_sga, 如果设置 lock\_sga =true 使用非 root 用户启动数据库将返回错误。  
WINDOWS （作用不大）  
不能设置lock\_sga=true,可以通过设置pre\_page\_sga=true,使得数据库启动的时候就把所有内  
存页装载，这样可能起到一定的作用。  
22、内存参数调整  
数据缓冲区命中率  
select value from v$sysstat where name ='physical reads';  
select value from v$sysstat where name ='physical reads direct';  
select value from v$sysstat where name ='physical reads direct (lob)';  
select value from v$sysstat where name ='consistent gets';  
select value from v$sysstat where name = 'db block gets';  
这里命中率的计算应该是  
令 x = physical reads direct + physical reads direct (lob)  
命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)\*100  
通常如果发现命中率低于90%,则应该调整应用可可以考虑是否增大数据缓冲区；  
共享池的命中率  
select sum(pinhits)/sum(pins)\*100 "hit radio" from v$librarycache;  
假如共享池的命中率低于95%,就要考虑调整应用（通常是没使用bind var ）或者增加内存；  
关于排序部分  
select name,value from v$sysstat where name like '%sort%';  
假如我们发现sorts (disk)/ (sorts (memory)+ sorts (disk))的比例过高，则通常意味着  
sort\_area\_size 部分内存较小，可考虑调整相应的参数。  
关于log\_buffer  
select name,value from v$sysstat  
where name in('redo entries','redo buffer allocation retries');  
假如 redo buffer allocation retries/ redo entries 的比例超过1%我们就可以考虑增大log\_buffer  
   
/  
July 28  
oracle 常用SQL查询，望对大家有所启示  
1、查看表空间的名称及大小  
select t.tablespace\_name, round(sum(bytes/(1024\*1024)),0) ts\_size  
from dba\_tablespaces t, dba\_data\_files d  
where t.tablespace\_name = d.tablespace\_name  
group by t.tablespace\_name;  
2、查看表空间物理文件的名称及大小  
select tablespace\_name, file\_id, file\_name,  
round(bytes/(1024\*1024),0) total\_space  
from dba\_data\_files  
order by tablespace\_name;  
3、查看回滚段名称及大小  
select segment\_name, tablespace\_name, r.status,  
(initial\_extent/1024) InitialExtent,(next\_extent/1024) NextExtent,  
max\_extents, v.curext CurExtent  
From dba\_rollback\_segs r, v$rollstat v  
Where r.segment\_id = v.usn(+)  
order by segment\_name ;  
4、查看控制文件  
select name from v$controlfile;  
5、查看日志文件  
select member from v$logfile;  
6、查看表空间的使用情况  
select sum(bytes)/(1024\*1024) as free\_space,tablespace\_name  
from dba\_free\_space  
group by tablespace\_name;

SELECT A.TABLESPACE\_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,  
(B.BYTES\*100)/A.BYTES "% USED",(C.BYTES\*100)/A.BYTES "% FREE"  
FROM SYS.SM$TS\_AVAIL A,SYS.SM$TS\_USED B,SYS.SM$TS\_FREE C  
WHERE A.TABLESPACE\_NAME=B.TABLESPACE\_NAME AND A.TABLESPACE\_NAME=C.TABLESPACE\_NAME;  
7、查看数据库库对象  
select owner, object\_type, status, count(\*) count\# from all\_objects group by owner, object\_type, status;  
8、查看数据库的版本　  
Select version FROM Product\_component\_version  
Where SUBSTR(PRODUCT,1,6)='Oracle';  
9、查看数据库的创建日期和归档方式  
Select Created, Log\_Mode, Log\_Mode From V$Database;  
10、捕捉运行很久的SQL  
column username format a12  
column opname format a16  
column progress format a8

select username,sid,opname,  
round(sofar\*100 / totalwork,0) || '%' as progress,  
time\_remaining,sql\_text  
from v$session\_longops , v$sql  
where time\_remaining <> 0  
and sql\_address = address  
and sql\_hash\_value = hash\_value  
/  
11。查看数据表的参数信息  
SELECT partition\_name, high\_value, high\_value\_length, tablespace\_name,  
pct\_free, pct\_used, ini\_trans, max\_trans, initial\_extent,  
next\_extent, min\_extent, max\_extent, pct\_increase, FREELISTS,  
freelist\_groups, LOGGING, BUFFER\_POOL, num\_rows, blocks,  
empty\_blocks, avg\_space, chain\_cnt, avg\_row\_len, sample\_size,  
last\_analyzed  
FROM dba\_tab\_partitions  
\--WHERE table\_name = :tname AND table\_owner = :towner  
ORDER BY partition\_position

12.查看还没提交的事务  
select \* from v$locked\_object;  
select \* from v$transaction;  
13。查找object为哪些进程所用  
select  
p.spid,  
s.sid,  
s.serial\# serial\_num,  
s.username user\_name,  
a.type object\_type,  
s.osuser os\_user\_name,  
a.owner,  
a.object object\_name,  
decode(sign(48 - command),  
1,  
to\_char(command), 'Action Code \#' || to\_char(command) ) action,  
p.program oracle\_process,  
s.terminal terminal,  
s.program program,  
s.status session\_status  
from v$session s, v$access a, v$process p  
where s.paddr = p.addr and  
s.type = 'USER' and  
a.sid = s.sid and  
a.object='SUBSCRIBER\_ATTR'  
order by s.username, s.osuser

14。回滚段查看  
select rownum, sys.dba\_rollback\_segs.segment\_name Name, v$rollstat.extents  
Extents, v$rollstat.rssize Size\_in\_Bytes, v$rollstat.xacts XActs,  
v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes,  
sys.dba\_rollback\_segs.status status from v$rollstat, sys.dba\_rollback\_segs,  
v$rollname where v$rollname.name(+) = sys.dba\_rollback\_segs.segment\_name and  
v$rollstat.usn (+) = v$rollname.usn order by rownum

15。耗资源的进程（top session）  
select s.schemaname schema\_name, decode(sign(48 - command), 1,  
to\_char(command), 'Action Code \#' || to\_char(command) ) action, status  
session\_status, s.osuser os\_user\_name, s.sid, p.spid , s.serial\# serial\_num,  
nvl(s.username, '\[Oracle process\]') user\_name, s.terminal terminal,  
s.program program, st.value criteria\_value from v$sesstat st, v$session s , v$process p  
where st.sid = s.sid and st.statistic\# = to\_number('38') and ('ALL' = 'ALL'  
or s.status = 'ALL') and p.addr = s.paddr order by st.value desc, p.spid asc, s.username asc, s.osuser asc

16。查看锁（lock）情况  
select /\*+ RULE \*/ ls.osuser os\_user\_name, ls.username user\_name,  
decode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX',  
'Transaction enqueue lock', 'UL', 'User supplied lock') lock\_type,  
o.object\_name object, decode(ls.lmode, 1, null, 2, 'Row Share', 3,  
'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null)  
lock\_mode, o.owner, ls.sid, ls.serial\# serial\_num, ls.id1, ls.id2  
from sys.dba\_objects o, ( select s.osuser, s.username, l.type,  
l.lmode, s.sid, s.serial\#, l.id1, l.id2 from v$session s,  
v$lock l where s.sid = l.sid ) ls where o.object\_id = ls.id1 and o.owner  
<> 'SYS' order by o.owner, o.object\_name

17。查看等待（wait）情况  
SELECT v$waitstat.class, v$waitstat.count count, SUM(v$sysstat.value) sum\_value  
FROM v$waitstat, v$sysstat WHERE v$sysstat.name IN ('db block gets',  
'consistent gets') group by v$waitstat.class, v$waitstat.count

18。查看sga情况  
SELECT NAME, BYTES FROM SYS.V\_$SGASTAT ORDER BY NAME ASC

19。查看catched object  
SELECT owner, name, db\_link, namespace,  
type, sharable\_mem, loads, executions,  
locks, pins, kept FROM v$db\_object\_cache

20。查看V$SQLAREA  
SELECT SQL\_TEXT, SHARABLE\_MEM, PERSISTENT\_MEM, RUNTIME\_MEM, SORTS,  
VERSION\_COUNT, LOADED\_VERSIONS, OPEN\_VERSIONS, USERS\_OPENING, EXECUTIONS,  
USERS\_EXECUTING, LOADS, FIRST\_LOAD\_TIME, INVALIDATIONS, PARSE\_CALLS, DISK\_READS,  
BUFFER\_GETS, ROWS\_PROCESSED FROM V$SQLAREA

21。查看object分类数量  
select decode (o.type\#,1,'INDEX' , 2,'TABLE' , 3 , 'CLUSTER' , 4, 'VIEW' , 5 ,  
'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) object\_type , count(\*) quantity from  
sys.obj$ o where o.type\# > 1 group by decode (o.type\#,1,'INDEX' , 2,'TABLE' , 3  
, 'CLUSTER' , 4, 'VIEW' , 5 , 'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) union select  
'COLUMN' , count(\*) from sys.col$ union select 'DB LINK' , count(\*) from

22。按用户查看object种类  
select u.name schema, sum(decode(o.type\#, 1, 1, NULL)) indexes,  
sum(decode(o.type\#, 2, 1, NULL)) tables, sum(decode(o.type\#, 3, 1, NULL))  
clusters, sum(decode(o.type\#, 4, 1, NULL)) views, sum(decode(o.type\#, 5, 1,  
NULL)) synonyms, sum(decode(o.type\#, 6, 1, NULL)) sequences,  
sum(decode(o.type\#, 1, NULL, 2, NULL, 3, NULL, 4, NULL, 5, NULL, 6, NULL, 1))  
others from sys.obj$ o, sys.user$ u where o.type\# >= 1 and u.user\# =  
o.owner\# and u.name <> 'PUBLIC' group by u.name order by  
sys.link$ union select 'CONSTRAINT' , count(\*) from sys.con$

23。有关connection的相关信息  
1）查看有哪些用户连接  
select s.osuser os\_user\_name, decode(sign(48 - command), 1, to\_char(command),  
'Action Code \#' || to\_char(command) ) action, p.program oracle\_process,  
status session\_status, s.terminal terminal, s.program program,  
s.username user\_name, s.fixed\_table\_sequence activity\_meter, '' query,  
0 memory, 0 max\_memory, 0 cpu\_usage, s.sid, s.serial\# serial\_num  
from v$session s, v$process p where s.paddr=p.addr and s.type = 'USER'  
order by s.username, s.osuser  
2）根据v.sid查看对应连接的资源占用等情况  
select n.name,  
v.value,  
n.class,  
n.statistic\#  
from v$statname n,  
v$sesstat v  
where v.sid = 71 and  
v.statistic\# = n.statistic\#  
order by n.class, n.statistic\#  
3）根据sid查看对应连接正在运行的sql  
select /\*+ PUSH\_SUBQ \*/  
command\_type,  
sql\_text,  
sharable\_mem,  
persistent\_mem,  
runtime\_mem,  
sorts,  
version\_count,  
loaded\_versions,  
open\_versions,  
users\_opening,  
executions,  
users\_executing,  
loads,  
first\_load\_time,  
invalidations,  
parse\_calls,  
disk\_reads,  
buffer\_gets,  
rows\_processed,  
sysdate start\_time,  
sysdate finish\_time,  
'>' || address sql\_address,  
'N' status  
from v$sqlarea  
where address = (select sql\_address from v$session where sid = 71)

24．查询表空间使用情况select a.tablespace\_name "表空间名称",  
100-round((nvl(b.bytes\_free,0)/a.bytes\_alloc)\*100,2) "占用率(%)",  
round(a.bytes\_alloc/1024/1024,2) "容量(M)",  
round(nvl(b.bytes\_free,0)/1024/1024,2) "空闲(M)",  
round((a.bytes\_alloc-nvl(b.bytes\_free,0))/1024/1024,2) "使用(M)",  
Largest "最大扩展段(M)",  
to\_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "采样时间"  
from (select f.tablespace\_name,  
sum(f.bytes) bytes\_alloc,  
sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes  
from dba\_data\_files f  
group by tablespace\_name) a,  
(select f.tablespace\_name,  
sum(f.bytes) bytes\_free  
from dba\_free\_space f  
group by tablespace\_name) b,  
(select round(max(ff.length)\*16/1024,2) Largest,  
ts.name tablespace\_name  
from sys.fet$ ff, sys.file$ tf,sys.ts$ ts  
where ts.ts\#=ff.ts\# and ff.file\#=tf.relfile\# and ts.ts\#=tf.ts\#  
group by ts.name, tf.blocks) c  
where a.tablespace\_name = b.tablespace\_name and a.tablespace\_name = c.tablespace\_name

25. 查询表空间的碎片程度

select tablespace\_name,count(tablespace\_name) from dba\_free\_space group by tablespace\_name  
having count(tablespace\_name)>10;

alter tablespace name coalesce;  
alter table name deallocate unused;

create or replace view ts\_blocks\_v as  
select tablespace\_name,block\_id,bytes,blocks,'free space' segment\_name from dba\_free\_space  
union all  
select tablespace\_name,block\_id,bytes,blocks,segment\_name from dba\_extents;

select \* from ts\_blocks\_v;

select tablespace\_name,sum(bytes),max(bytes),count(block\_id) from dba\_free\_space  
group by tablespace\_name;

26.查看有哪些实例在运行：

select \* from v$active\_instances;  
12:15 PM | Add a comment | Permalink | Blog it | Oracle  
ORACLE性能调优原则  
数据库的硬件配置：CPU、内存、网络条件  
1.         CPU：在任何机器中CPU的数据处理能力往往是衡量计算机性能的一个标志，并且ORACLE是一个提供并行能力的数据库系统，在CPU方面的要求就更高了，如果运行队列数目超过了CPU处理的数目，性能就会下降，我们要解决的问题就是要适当增加CPU的数量了，当然我们还可以将需要许多资源的进程KILL掉；

2.         内存：衡量机器性能的另外一个指标就是内存的多少了，在ORACLE中内存和我们在建数据库中的交换区进行数据的交换，读数据时，磁盘I/O必须等待物理I/O操作完成，在出现ORACLE的内存瓶颈时，我们第一个要考虑的是增加内存，由于I/O的响应时间是影响ORACLE性能的主要参数，我将在这方面进行详细的讲解

3.         网络条件：NET\*SQL负责数据在网络上的来往，大量的SQL会令网络速度变慢。比如10M的网卡和100的网卡就对NET\*SQL有非常明显的影响，还有交换机、集线器等等网络设备的性能对网络的影响很明显，建议在任何网络中不要试图用3个集线器来将网段互联。

?         OS参数的设置

下表给出了OS的参数设置及说明，DBA可以根据实际需要对这些参数进行设置

内核参数名

说明

bufpages

对buffer空间不按静态分配，采用动态分配，使bufpages值随nbuf一起对buffer空间进行动态分配。

create\_fastlinks

对HFS文件系统允许快速符号链接，

dbc\_max\_pct

加大最大动态buffer空间所占物理内存的百分比，以满足应用系统的读写命中率的需要。

dbc\_min\_pct

设置最小动态buffer空间所占物理内存的百分比

desfree

提高开始交换操作的最低空闲内存下限，保障系统的稳定性，防止出现不可预见的系统崩溃（Crash）。

fs\_async

允许进行磁盘异步操作，提高CPU和磁盘的利用率

lotsfree

提高系统解除换页操作的空闲内存的上限值，保证应用程序有足够的可用内存空间。

maxdsiz

针对系统数据量大的特点，加大最大数据段的大小，保证应用的需要。（32位）

maxdsiz\_64bit

maximum process data segment size for 64\_bit

Maxssiz

加大最大堆栈段的大小。（32\_bit）

maxssiz\_64bit

加大最大堆栈段的大小（64\_bit）

Maxtsiz

提高最大代码段大小，满足应用要求

maxtsiz\_64bit

原值过大，应调小

Minfree

提高停止交换操作的自由内存的上限

Shmem

允许进行内存共享，以提高内存的利用率。

Shmmax

设置最大共享内存段的大小，完全满足目前的需要。

Timeslice

由于系统的瓶颈主要反映在磁盘I/O上，因此　降低时间片的大小，一方面可避免因磁盘I/O不畅造成CPU的等待，从而提高了CPU的综合利用率。另一方面减少了进程的阻塞量。

unlockable\_mem

提高了不可锁内存的大小，使可用于换页和交换的内存空间扩大,用以满足系统对内存管理的要求。

摘自： [http://www.zdnet.com.cn/developer/database/story/0,3800066906,39276980,00.htm][http_www.zdnet.com.cn_developer_database_story_0_3800066906_39276980_00.htm]  
11:27 AM | Add a comment | Permalink | Blog it | Oracle  
UNIX内存占用基本检查  
   
1: 使用top指令.

top指令是按cpu占用率排序的,如果想一次获得所有进程的快照,使用命令

top -n \[最大进程数\] -f 输出到文件,比如top -n 1000 -f topsnapshot.log

top指令输出内存的统计信息包括

Memory: 2614368K (2249100K) real, 5838616K (5264696K) virtual, 113028K free  Page\# 1/1  
其中没有括号起来的是总数,括号括起来的部分是活动进程使用的内存数,free则是真实空闲的物理内存数.

进程信息的列包括

CPU TTY  PID USERNAME PRI NI   SIZE    RES STATE    TIME %WCPU  %CPU COMMAND

和内存相关的只有SIZE和RES

SIZE：任务的代码加上数据再加上栈空间的大小。   
RES：任务使用的物理内存的总数量

要检查进程是否有内存泄露,和实际占用的内存大小,看RES列即可.

2:检查共享内存占用的内存容量

使用ipcs -m -b命令,-m表示检查共享内存,-b表示输出每个内存的字节数,得到的共享内存信息输出列包括:

T         ID     KEY        MODE        OWNER     GROUP      SEGSZ

SEGSZ列则是字节数.把每列相加则是共享内存占用的内存总数.

3: 调整内核动态高速缓冲区参数

HP-UX某些型号的服务器运行的时候需要几乎1G的内存维持系统运行,比如作为设备缓冲什么的.

可以用kmtune命令检查内核配置参数,动态高速缓冲区参数dbc\_min\_pct 和 dbc\_max\_pct参数表示一个高速缓冲区允许的可用内存的最小和最大百分比,dbc\_max\_pct的缺省值是50,一般设置为10即可.

4:在HP-UX上还可以使用glance

glance是个很强的工具，可惜不是免费的....

11:16 AM | Add a comment | Permalink | Blog it | HPUX  
July 27  
Oracle的优化器(Optimizer)  
本文的目的：   
1、说一说Oracle的Optimizer及其相关的一些知识。   
2、回答一下为什么有时一个表的某个字段明明有索引,当观察一些SQL的执行计划时,发现确不走索引的问题。   
3、如果你对 FIRST\_ROWS、 ALL\_ROWS这两种模式有疑惑时也可以看一下这篇文章。

开始吧:

Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的。不同的情况,一条SQL可能有多种执行计划,但在某一时点,一定只有一种执行计划是最优的,花费时间是最少的。相信你一定会用Pl/sql Developer、Toad等工具去看一个语句的执行计划,不过你可能对Rule、Choose、First rows、All rows这几项有疑问,因为我当初也是这样的,那时我也疑惑为什么选了以上的不同的项,执行计划就变了?

1、优化器的优化方式

Oracle的优化器共有两种的优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO)。   
A、RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。   
B、CBO方式：依词义可知,它是看语句的代价(Cost)了,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小 、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些我们应及时更新这些信息。在Oracle8及以后的版本,Oracle列推荐用CBO的方式。

我们要明了,不一定走索引就是优的 ,比如一个表只有两行数据,一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时对这个表做全表扫描(full table scan)是最好的。

2、优化器的优化模式(Optermizer Mode)

优化模式包括Rule,Choose,First rows,All rows这四种方式,也就是我们以上所提及的。如下我解释一下：

Rule:不用多说,即走基于规则的方式。

Choolse:这是我们应观注的,默认的情况下Oracle用的便是这种方式。指的是当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。

First Rows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。

All Rows:也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式。

3、如何设定选用哪种优化模式

a、Instance级别

我们可以通过在init<SID>.ora文件中设定OPTIMIZER\_MODE=RULE、OPTIMIZER\_MODE=CHOOSE、OPTIMIZER\_MODE=FIRST\_ROWS、OPTIMIZER\_MODE=ALL\_ROWS去选用3所提的四种方式,如果你没设定OPTIMIZER\_MODE参数则默认用的是Choose这种方式。

B、Sessions级别

通过SQL> ALTER SESSION SET OPTIMIZER\_MODE=<Mode>;来设定。

C、语句级别

这些需要用到Hint,比如:   
SQL> SELECT /\*+ RULE \*/ a.userid,   
2 b.name,   
3 b.depart\_name   
4 FROM tf\_f\_yhda a,   
5 tf\_f\_depart b   
6 WHERE a.userid=b.userid;

4、为什么有时一个表的某个字段明明有索引,当观察一些语的执行计划确不走索引呢？如何解决呢 ？

A、不走索引大体有以下几个原因   
♀你在Instance级别所用的是all\_rows的方式   
♀你的表的统计信息(最可能的原因)   
♀你的表很小,上文提到过的,Oracle的优化器认为不值得走索引。   
B、解决方法   
♀可以修改init<SID>.ora中的OPTIMIZER\_MODE这个参数,把它改为Rule或Choose,重起数据库。也可以使用4中所提的Hint.   
♀删除统计信息   
SQL>analyze table table\_name delete statistics;   
♀表小不走索引是对的,不用调的。

5、其它相关

A、如何看一个表或索引是否是统计信息

SQL>SELECT \* FROM user\_tables   
2 WHERE table\_name=<table\_name>   
3 AND num\_rows is not null;

SQL>SELECT \* FROM user\_indexes   
2 WHERE table\_name=<table\_name>   
3 AND num\_rows is not null;

b、如果我们先用CBO的方式,我们应及时去更新表和索引的统计信息,以免生形不切合实的执行计划。

SQL> ANALYZE TABLE table\_name COMPUTE STATISTICS;   
SQL> ANALYZE INDEX index\_name ESTIMATE STATISTICS;

具体的ANALYZE语句请参照Oracle8i/9i 的refrence文档。

12:42 PM | Add a comment | Permalink | Blog it | Oracle  
oracle大数据量的导入和导出  
在oracle中批量数据的导出是借助sqlplus的spool来实现的。批量数据的导入是通过sqlload来实现的。  
大量数据的导出部分如下：  
/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
\* sql脚本部分 demo.sql begin  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/  
/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
\* @author meconsea  
\* @date 20050413  
\* @msn  [meconsea@hotmail.com][meconsea_hotmail.com]  
\* @Email  [meconsea@163.com][meconsea_163.com]  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/  
//\#\#--markup html：html格式输出，缺省为off  
//\#\#--autocommit：自动提交insert、update、delete带来的记录改变，缺省为off  
//\#\#--define：识别命令中的变量前缀符，缺省为on，也就是'&'，碰到变量前缀符，后面的字符串作为变量处理.  
set colsep''; //\#\#--域输出分隔符  
set echo off; //\#\#--显示start启动的脚本中的每个sql命令，缺省为on  
set feedback off; //\#\#--回显本次sql命令处理的记录条数，缺省为on   
set heading off; //\#\#--输出域标题，缺省为on  
set pagesize 0; //\#\#--输出每页行数，缺省为24,为了避免分页，可设定为0。  
set linesize 80; //\#\#--输出一行字符个数，缺省为80  
set numwidth 12; //\#\#--输出number类型域长度，缺省为10  
set termout off; //\#\#--显示脚本中的命令的执行结果，缺省为on  
set timing off; //\#\#--显示每条sql命令的耗时，缺省为off  
set trimout on; //\#\#--去除标准输出每行的拖尾空格，缺省为off  
set trimspool on; //\#\#--去除重定向（spool）输出每行的拖尾空格，缺省为off  
spool C:datadmczry.txt;   
select trim(czry\_dm),trim(swjg\_dm),trim(czry\_mc) from dm\_czry;  
spool off;  
EOF  
/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
\* demo.sql end  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/

在数据导入的时候采用sqlload来调用，在该部分调用的时候用java来调用sqlload。  
sqlload包括ctl控制文件。例如：  
/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
\* meconsea ctl  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/  
load data  
infile 'C:datadmczry.txt'  
replace into table DM\_CZRY  
fields terminated by X'09'  
(CZRY\_DM,SWJG\_DM,CZRY\_MC)

/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
\* end  
\* 注释：里面的replace可以改为append  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/

java程序如下：  
在java程序用可以根据需求写成一个bat文件。 把数据库的配置和文件的路径写到一个properties  
文件。  
/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
\* ide properties  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/  
[Dserver=test/test@SJJZ][Dserver_test_test_SJJZ]  
sqlldr=D:\\oracle\\ora92\\bin\\SQLLDR.EXE  
ctldmczry=C:\\data\\ctl\\dmczry.ctl  
txtdmczry=C:\\data\\dmczry.txt  
写个PropertyBean.java来操作properties文件。(偷懒不写了！)  
用DmCzry.java来把记录导入db中。部分代码如下：  
/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
\* 代码摘要  
\*  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/  
..............  
sqlldr = pb.getSqlldr();  
txt = pb.getTxtdmczry();  
ctl = pb.getCtldmczry();  
Dserver= pb.getDserver();

Process processCmd = Runtime.getRuntime().exec(sqlldr+" "+cmdStr);  
.............

12:42 PM | Add a comment | Permalink | Blog it | Oracle  
在Oracle中查看各个表、表空间占用空间的大小  
查看当前用户每个表占用空间的大小：  
Select Segment\_Name,Sum(bytes)/1024/1024 From User\_Extents Group By Segment\_Name  
查看每个表空间占用空间的大小：  
Select Tablespace\_Name,Sum(bytes)/1024/1024 From Dba\_Segments Group By Tablespace\_Name

12:36 PM | Add a comment | Permalink | Blog it | Oracle  
July 22  
Oracle 数据类型  
Oracle 数据类型

数据类型

描述

VARCHAR2(size)

NVARCHAR2(size)

可变长度的字符串，其最大长度为 size 个字节。size 的最大值是 4000，而最小值是 1。您必须指定一个 VARCHAR2 的 size。

可变长度的字符串，依据所选的国家字符集，其最大长度为 size 个字符或字节。size 的最大值取决于存储每个字符所需要的字节数，其上限为 4000 个字节。您必须为 NVARCHAR2 指定一个 size。

NUMBER(p,s)

精度为 p 并且数值范围为 s 的数值。精度 p 的范围是从 1 到 38。数值范围 s 的范围是从 -84 到 127。

LONG

可变长度的字符数据，其最大长度可达 2G 或 231 –1 个字节。

DATE

有效日期范围从公元前 4712 年 1 月 1 日到公元后 4712 年 12 月 31 日。

RAW(size)

长度为 size 字节的原始二进制数据。size 的最大值为 2000 字节。您必须为 RAW 值指定一个 size。

LONG RAW

可变长度的原始二进制数据，其最大长度可达 2G 字节。

CHAR(size)

NCHAR(size)

固定长度的字符数据，其长度为 size 字节。size 的最大值为 2000 字节。默认或最小的 size 是一个字节。

固定长度的字符数据，其长度依据国家字符集的选择为 size 个字符或字节。size 的最大值取决于存储每个字符所需要的字节数，其上限为 2000 个字节。默认或最小的 size 是一个字符或字节，这取决于字符集。

CLOB

NCLOB

一个字符大型对象，可容纳单字节的字符。不支持宽度不等的字符集。最大大小为 4G 字节。

一个字符大型对象，可容纳固定宽度的多字节字符。不支持宽度不等的字符集。最大大小为 4G 字节。储存国家字符集数据。

BLOB

一个二进制大对象。最大大小为 4G 字节。

BFILE

包含一个大型二进制文件的定位器，其存储在数据库的外面。使得可以以字节流 I/O 访问存在数据库服务器上的外部 LOB。最大大小为 4G 字节。

3:57 PM | Add a comment | Permalink | Blog it | Oracle  
July 19  
ORACLE锁的管理  
 ORACLE里锁有以下几种模式:

0：none  
1：null      空   
2：Row-S     行共享(RS)：共享表锁    
3：Row-X     行专用(RX)：用于行的修改  
4：Share     共享锁(S)：阻止其他DML操作  
5：S/Row-X   共享行专用(SRX)：阻止其他事务操作  
6：exclusive 专用(X)：独立访问使用

数字越大锁级别越高, 影响的操作越多。

一般的查询语句如select ... from ... ;是小于2的锁, 有时会在v$locked\_object出现。

select ... from ... for update;      是2的锁。

当对话使用for update子串打开一个游标时，  
所有返回集中的数据行都将处于行级(Row-X)独占式锁定，  
其他对象只能查询这些数据行，不能进行update、delete或select...for update操作。

insert / update / delete ... ;      是3的锁。

没有commit之前插入同样的一条记录会没有反应,   
因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。

创建索引的时候也会产生3,4级别的锁。

locked\_mode为2,3,4不影响DML(insert,delete,update,select)操作,   
但DDL(alter,drop等)操作会提示ora-00054错误。

有主外键约束时 update / delete ... ; 可能会产生4,5的锁。

DDL语句时是6的锁。

以DBA角色, 查看当前数据库里锁的情况可以用如下SQL语句：

select object\_id,session\_id,locked\_mode from v$locked\_object;

select t2.username,t2.sid,t2.serial\#,t2.logon\_time   
from v$locked\_object t1,v$session t2   
where t1.session\_id=t2.sid order by t2.logon\_time;

如果有长期出现的一列，可能是没有释放的锁。

我们可以用下面SQL语句杀掉长期没有释放非正常的锁：

alter system kill session 'sid,serial\#';

如果出现了锁的问题, 某个DML操作可能等待很久没有反应。

当你采用的是直接连接数据库的方式，  
也不要用OS系统命令 $kill process\_num 或者 $kill -9 process\_num来终止用户连接，  
因为一个用户进程可能产生一个以上的锁, 杀OS进程并不能彻底清除锁的问题。

记得在数据库级别用alter system kill session 'sid,serial\#';杀掉不正常的锁。  
5:42 PM | Add a comment | Permalink | Blog it | Oracle  
July 18  
利用STATSPAGK调整ORACLE性能  
一、摘要

大部分DBA都利用数据缓冲区命中率，latch free wait time等指标来做数据库性能调整。ORACLE提供的几个简单工具如STATSPACK（或以前版本的BSTAT/ESTAT）中包含了DBA 所需要的主要指标。但如何有效地利用这些数据调整性能？从那里开始调整？本文使用YAPP性能优化方法，结合STATSPACK提供的数据，给出了应该采取的调整步骤。

二、YAPP性能优化方法

YAPP提供了另一种数据库性能调整方法，它不使用命中率等指标来衡量数据库的性能而是通过响应时间来衡量：

Response time = service time + wait time

即用户面对的响应时间由服务时间和等待时间组成。服务时间是处理你的请求实际使用的CPU时间，等待时间即等待资源可用所花费的时间。例如如果执行一个需要查找索引的SQL语句，CPU时间可能包括buffer cache中的索引数据块的处理时间，扫描该数据块找到所需行的时间等，此过程中ORACLE可能需要从盘上读数据，此时可能出现磁盘等待。

YAPP方法的主要思路是找出service time，wait time的主要组成部分，然后进行排序并根据顺序调整。因此在IO不是引起问题的原因时，你不会做出象‘数据缓冲区命中率太低,最好增加缓冲的结论’,在SQL语句的CPU处理时间为20分钟时，你也不会做出‘必须将latchwait time减少20秒’的调整决定。另外用YAPP做优化时，你可以通过减少整个时间（如用更快的磁盘）或单位时间（如减少访问磁盘次数）。因此我们称YAPP为基于时间的调优方法，基本步骤如下：

（1）、得到服务时间和等待时间及其组成部分   
　　（2）、将所有组成部分排序   
　　（3）、依次优化每个部分   
　　（4）、对表中的每一项,减少每次执行的代价或执行次数

STATSPACK或bstat/estat中的数据完全能满足基于时间而不是基于命中率的优化方法。然而实际上要找出所有耗时的部分有些困难，在分析细节时service和wait本身并不精确，例如当你等待磁盘IO时，实际是从OS的缓冲中读写，实际上它是service （即CPU）时间,因此响应时间更好的表达为：

Response time = time compnent1+….+time componentn

用户感知的响应时间由一系列时间成分组成，所谓性能优化就是优化最耗时的成分，依次类推。从ORACLE instance的角度，请求一般含三个部分：client （如SQLPLUS，TUXEDO），前台进程（如LOCAL = NO的服务进程），后台进程（如DBWR）。

三、ORACLE中的时间记录

所有的ORACLE进程（前台和后台）都会将所使用的CPU时间（service time）和各种等待事件所费时间记录下来，这些信息记录在SGA，用户可通过V$▁视图访问这些数据。这种数据分为session级和system级，用户可访问V$SYSTEM▁EVENT和V$SYSSTAT等视图来获取这些信息。ORACLE的STATSPACK工具（老版本中的BSTAT/ESTAT）就是查询这些视图来收集，计算和生成性能数据。要在ORACLE中产生时间记录，DBA必须在INIT.ORA中将TIMED▁STATISTICS设置为TRUE或通过ALTERSYSTEM将其定义为TRUE。8i以前的版本中时间单位为1/100秒。9i以后时间单位为1/1,000,000秒(微秒)。本文主要面向system级的数据，但使用的方法适用于session级的数据。

在基于时间的优化方法中，最重要的视图是V$SYSTEM▁EVENT，V$SYSSTAT，V$LATCH，V$SQLAREA。V$SYSSTAT记录使用的CPU时间，V$SYSTEM▁EVENT记录进程等待时间花费的间，V$SQLAREA能用于找出最耗资源的SQL语句，而V$LATCH则可用于各种LATCH的等待信息。这些视图的详细结构和含义见Sewer Reference Mannual

四、利用STATSPACK优化性能

前一节所说的V$▁ 中记录的数据都是系统启动后的累加值，从某一个时间点看这些累加值没有实际意义。只有每隔一段时间对这些累加值取样，计算出抽样之间的差别对优化才有价值。ORACLE的STATSPACK就是完成定期取样的工作，一般可用ORACLE的JOB来自动完成定期取样。数据收集完成后，DBA可以运行STATSPACK带的SPREPORT生成某两个取样点之间的差别。STATSPACK生成的报告中含有各种数据，包括上述四个视图中的数据。

1、从STATSPACK报告中找出晌应时间组成部分

基于时间的优化方法YAPP就是要找出最值得优化（最耗时）的成分。我们需找出前台进程使用的sevice time及等待事件花费的时间，service time信息可以从V$SYSSTAT中得到而事件等待花费的时间可从V$SYSTEM▁EVENT中得到。在STATSPACK报告中它们分别在 '''' Instance Activity Stats for DB '''' 和 '''' Wait Eventsfor DB '''' 一节中。尤其要注意三个时间成分：

CPU used by this session Total CPU time spent.   
　　Recursive cpu usage   
　　Time spent doing recursive work in the foreground .     
　　This includes data dictionary lookup and any PL /SQL work, including time spent   
by SQL inside   
　　PL/SQL parse time cpu   
　　CPU time spent parsing SQL statements

Recursive cpu usage和parse time cpu CPU是CPU used by this session的组成部分，除此之外的CPU时间我们一律定义为other CPU

下一步需找出wait time的组成部分，最简单的方法就是找出 '''' Top5Wait Events '''' 下的5个等待事件，另一种方法即在 '''' Wait events for DB '''' 一节中找出最主要的事件。

下面是根据STATSPACK报告的数据，用基于YAPP方法的优化步骤：

（1）、找出parse time cpu所花费的时间   
　　（2）、找出CPU used by this session的值，减去parse time cpu，得出other CPU   
　　（3）、找出最耗时的等待事件   
　　（4）、将1—3的成分倒序排序，从第一项开始优化   
　　（5）、如果最耗时的等待事件不是latch free，见Tuning possibilities for wait events   
　　（6）、如果最耗时的等待事件是latch free，见Tuning possibilities for  latches   
　　（7）、如果最耗时的成分是与CPU有关的成分,见Tuning possibilities for CPU

2、Tuning possibilities for CPU

recursive cpu usage  如果处理大量的PLSQL此成分可能很高，本文不深入讨论此问题产生的原因，但你需要找出你所有的PLSQL，包括存储过程。找出CPU开销最大的PLSQL过程并对其优化。如果PLSQL中的主要工作是完成过程处理而非执行SQL，高开销的recursive cpu usage可能是需要优化的成分。

parse time cpu  分析（parsing）SQL是一个开销很大的，它可以通过SQL语句的重用来避免。在预编译程序中，可通过增加MAXOPENCURSORS参数减少这部分开销。V$SQL的PARSE▁CALLS和EXECUTIONS可用来找出经常parse的语句。

Other cpu  其它CPU主要用于处理缓冲区中的缓冲。一般而言，SQL语句花费的CPU时间与访问的缓冲区个数成比例，因此可以从V$SQL中的buffer gets得到SQL所防问的缓冲区个数，在STATSPACK中，可以查看 '''' SQL ordered by Gets for DB ''''。应对清单中的SQL语句优化。在bstat /estat报告中没有SQL语句，需定期查询V$SQLAREA，找出buffer gets增加最快的语句。9i中V$SQL中含有CPU▁T|ME字段,记录语句所花费的时间。

3、Tuning possibilities for wait events

db file scattered read  当ORACLE全表扫描时,一次需读多个数据块，此时使用这一等待事件。i n i t .o r a中的db▁ file▁mutiblock▁read▁count定义了多数据块读取时，一次能读取的最大块数。一般此参数定义为4—16，与数据库大小无关。但值越大DB▁BLOCK▁SlZE应越小。如果db file scattered read所占比例较大,必须减少IO的代价（如使用更快的磁盘,均衡IO分布），或减少全表扫描的次数（优化SQL语句）。参见下面IO优化。

db file sequential read  表示ORACLE顺序读数据块，一般出现在读索引。如果db file sequential read等待很长，必须减少IO的代价（如使用更快的磁盘，均衡IO分布），或增加缓冲区。参见下面IO优化。

buffer busy waits  多个进程访问（修改）缓冲区中同一数据块时出现此等待事件。当表没有free lists而对表并行插入时，或回滚段个数太少时，会出现此事件，V$WA|TSTAT及   
STATSPACK报告可辅助找出原因。

latch free  见下节。

Enqueue  一般为应用程序使用的锁，例如SEELECT ... FOR UPDATE。如果此部分占用的时间较大，需分析应用系统，尤其是长时间占有锁资源的代码。要分析每个锁的等待时间不太可能，虽然V$LOCK记录了每种所等待的次数。

log file sync  任何时候一个事物提交时，它将通知LGWR将LOG▁BUFFER写入日志文件，如果此部分占用时间较长，应减少COMMIT的次数，此外应使用性能更好的IO系统，另一个相关的事件是'''' log buffer parallel write '''' ，也与IO系统或CPU资源太少有关。

free buffer wait   当一个SESSION需要空闲缓冲区但不能获取时，出现此等待事件。它将通知DBWR将脏的缓冲区写入数据文件。需要确定是否需要优化IO系统或者增加DBWR的个数，如果此事件不是由于IO系统性能引起的，可考虑增加缓冲区。

rdbms ipc message  这些事件为空闲事件，一般应占主要的时间。''''

pmon timer

smon timer

SQL\*Net message

from client

Tuning possibilities for latches

shared pool   在parsing，尤其是hard parsing时。如果应用程序使用常量而不是BIND变量，可能会对此latch大量竞争，8.1.6以后可在init.ora中设置cursor▁sharing为force来减少hard parsing和对此latch的竞争，应用程序应保证只分析一次，执行多次。

library cache  在soft parsing和hard parsing时都会大量使用此latch，如有可能应修改应用程序，减少竞争。在init.ora中设置cursor▁sharing为force可减少soft parsing和hard parsing需要的library cache。此外定义session▁cached▁cursors也能减少同一session中soft parsing对library cache的竞争。此外还可以定义cursor▁space▁for▁time=true。

mw cache  row cache保护字典信息，如表和列的信息。hard parsing需要row cache 。在init.ora中设置cursor▁sharing为force可减少竞争。

cache buffer chain   保护缓冲区的hash chain，用于对缓冲区的每次访问。一般可通过减少访问缓冲区次数来减少对l|atch的竞争。利用X$BH可以找出某些hash chain是否有许多的缓冲区，经常会有热块（如root index block）可能引起竞争。

cache buffer lru chain  数据缓冲一组LRU块组成的链。每一个由一个cache buffer lru  chain 保护通过增加db▁ block▁lru▁latches可减少竞争。

4、Tuning possibilities for I/O

如果db file scattered/sequential read等直接的事件或file write（DBWR/LGWR）等非直接事件占用的时间比例较大，需要检查IO的效率。STATSPACK报告中有一节为 ''''Tablespace IO Summary for DB'''' 其中列出了表空间名称和它们的lO rate。另一节'''' file IO Statistics for DB''''列出了每个数据文件和它们的IO rate。首先应检查IO rate是否在期望的范围，其次应检查IO分布。如果IO rate 在可接受的范围（带cache的文件2—10ms或裸设备的每次IO 5—20ms）而且所有的数据文件IO  rate相似，那么可以肯定IO系统的性能符合要求。这种情况下减少每次IO代价没有必要，应该减少IO的次数（增加缓冲区或优化SQL）。然而如果IO rate大大超出合理范围或分布不合理，你需要重组IO子系统，如使用更多的磁盘驱动器，修改结构（如不使用RAID5），或IO重新分布。

五、值得注意的地方

虽然ORACLE的统计和等待事件可以为你找出系统瓶颈提供了很好的数据，但有些情况这些数据可能会误导用户：

·ORACLE 8i以前的版本时间单位是1/100秒，但在某些特别|的系统中其精度不够。因而某些发生过的事件可能没有记录，而某些发生时间并不很长的事件记录的时间要比实际的时间长。这个问题在9i中不会出现（计量单位是1/1,000,000秒）。

·ORACLE前台进程花费的CPU时间记录比较粗糙，CPU used by this session远远大于实际使用配的时间，唯一能做的估计是所用CPU时间与所访问的缓冲区个数成比例，但在运行大的PLSQL，复杂的表达式，表连接时这种估计是不精确的。一般而言，这类估计在OLTP类型的应用系统是有效的，对DSS系统这种估计是不精确的。

·V$SYSSTAT视图包含前台和后台进程时间的总和，然而CPU时间成分中只有前台进程所用的时间值得注意，某些后台进程（尤其是DBWR，LGWR）使用了大量的CPU，导致前台进程统计配的不精确。

·某些时间没有计算。如SQL\*NET的时间，但它影响响应时间。

·YAPP中只考虑了ORACLE前台进程使用的时间，如果ORACLE所用的时间只占响应时间的很小部分，优化ORACLE不会带来任何性能改进。

六、IBSTAT/ESTAT的使用

STATSPACK只有8.1.6以后的版本才有，如果使用的老的版本只有BSTAT/ESTAT，两者的主要区别是：

·BSTAT/ESAT由DBA直接手工运行而不通过ORACLE JOB自动运行，每运行一次只收集一个时间间隔的数据。

·BSTAT/ESAT没有SQL语句的信息，如果OTHER CPU是开销最大的成分，需要查询V$SQL找出最耗资源的SQL。

·没有TOP5 WAIT EVENT,需查找视图找出最耗时的事件。

\[关闭窗口\]  
1:41 PM | Add a comment | Permalink | Blog it | Oracle  
Oracle性能调优实践中的几点心得  
很多的时侯，做Oracle DBA的我们，当应用管理员向我们通告现在应用很慢、数据库很慢的时侯，我们到数据库时做几个示例的Select也发现同样的问题时，有些时侯我们会无从下手，因为我们认为数据库的各种命种率都是满足Oracle文档的建议。实际上如今的优化己经向优化等待(waits)转型了，实际中性能优化最根本的出现点也都集中在IO，这是影响性能最主要的方面，由系统中的等待去发现Oracle库中的不足、操作系统某些资源利用的不合理是一个比较好的办法，下面把我的一点实践经验与大家分享一下，本文测重于Unix环境。  
一、通过操作系统的一些工具检查系统的状态，比如CPU、内存、交换、磁盘的利用率，根据经验或与系统正常时的状态相比对，有时系统表面上看起来看空闲这也可能不是一个正常的状态，因为cpu可能正等待IO的完成。除此之外我们还应观注那些占用系统资源(cpu、内存)的进程。

1、如何检查操作系统是否存在IO的问题？使用的工具有sar,这是一个比较通用的工具。  
  Rp1\#Sar -u 2 10  
  即每隔2秒检察一次，共执行20次，当然这些都由你决定了。  
  示例返回：  
  HP-UX hpn2 B.11.00 U 9000/800    08/05/03  
  18:26:32    %usr    %sys    %wio   %idle  
  18:26:34      80       9      12       0  
  18:26:36      78      11      11       0  
  18:26:38      78       9      13       1  
  18:26:40      81      10       9       1  
  18:26:42      75      10      14       0  
  18:26:44      76       8      15       0  
  18:26:46      80       9      10       1  
  18:26:48      78      11      11       0  
  18:26:50      79      10      10       0  
  18:26:52      81      10       9       0

Average       79      10      11       0

其中的%usr指的是用户进程使用的cpu资源的百分比，%sys指的是系统资源使用cpu资源的百分比，%wio指的是等待io完成的百分比，这是值得我们观注的一项，%idle即空闲的百分比。如果wio列的值很大，如在35%以上，说明你的系统的IO存在瓶颈，你的CPU花费了很大的时间去等待IO的完成。Idle很小说明系统CPU很忙。像我的这个示例，可以看到wio平均值为11说明io没什么特别的问题，而我的idle值为零，说明我的cpu已经满负荷运行了。

当你的系统存在IO的问题，可以从以下几个方面解决

♀联系相应的操作系统的技术支持对这方面进行优化，比如hp-ux在划定卷组时的条带化等方面。  
  ♀查找Oracle中不合理的sql语句，对其进行优化  
  ♀对Oracle中访问量频繁的表除合理建索引外，再就是把这些表分表空间存放以免访问上产生热点，再有就是对表合理分区。

2、关注一下内存。  
    常用的工具便是vmstat，对于hp-unix来说可以用glance,Aix来说可以用topas,当你发现vmstat中pi列非零，memory中的free列的值很小，glance,topas中内存的利用率多于80%时，这时说明你的内存方面应该调节一下了，方法大体有以下几项。  
  ♀划给Oracle使用的内存不要超过系统内存的1/2,一般保在系统内存的40%为益。  
  ♀为系统增加内存  
  ♀如果你的连接特别多，可以使用MTS的方式  
  ♀打全补丁，防止内存漏洞。

3、如何找到点用系用资源特别大的Oracle的session及其执行的语句。  
Hp-unix可以用glance,top  
IBM AIX可以用topas  
些外可以使用ps的命令。  
通过这些程序我们可以找到点用系统资源特别大的这些进程的进程号，我们就可以通过以下的sql语句发现这个pid正在执行哪个sql，这个sql最好在pl/sql developer,toad等软件中执行, 把<>中的spid换成你的spid就可以了。  
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=<spid>    
   AND b.addr=a.paddr  
   AND a.sql\_address=c.address(+)  
 ORDER BY c.piece

我们就可以把得到的这个sql分析一下，看一下它的执行计划是否走索引，对其优化避免全表扫描，以减少IO等待，从而加快语句的执行速度。

提示：我在做优化sql时，经常碰到使用in的语句，这时我们一定要用exists把它给换掉，因为Oracle在处理In时是按Or的方式做的，即使使用了索引也会很慢。  
比如：  
SELECT  col1,col2,col3 FROM table1 a   
 WHERE a.col1 not in (SELECT  col1 FROM table2)  
       可以换成：  
SELECT  col1,col2,col3 FROM table1 a   
 WHERE not exists  
 (SELECT  'x'  FROM table2 b  
WHERE  a.col1=b.col1)

4、另一个有用的脚本：查找前十条性能差的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 ;

二、迅速发现Oracle Server的性能问题的成因，我们可以求助于v$session\_wait这个视图，看系统的这些session在等什么，使用了多少的IO。以下是我提供的参考脚本：

脚本说明：查看占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

对检索出的结果的几点说明：  
1、我是按每个正在等待的session已经发生的物理读排的序，因为它与实际的IO相关。

2、你可以看一下这些等待的进程都在忙什么，语句是否合理？  
  Select sql\_address from v$session where sid=<sid>;  
  Select \* from v$sqltext where address=<sql\_address>;  
执行以上两个语句便可以得到这个session的语句。  
你也以用alter system kill session 'sid,serial\#';把这个session杀掉。

3、应观注一下event这列，这是我们调优的关键一列，下面对常出现的event做以简要的说明：  
a、buffer busy waits，free buffer waits这两个参数所标识是dbwr是否够用的问题，与IO很大相关的，当v$session\_wait中的free buffer wait的条目很小或没有的时侯，说明你的系统的dbwr进程决对够用，不用调整；free buffer wait的条目很多，你的系统感觉起来一定很慢，这时说明你的dbwr已经不够用了，它产生的wio已经成为你的数据库性能的瓶颈，这时的解决办法如下：  
a.1增加写进程，同时要调整db\_block\_lru\_latches参数  
示例：修改或添加如下两个参数  
  db\_writer\_processes=4  
  db\_block\_lru\_latches=8  
a.2开异步IO，IBM这方面简单得多，hp则麻烦一些，可以与Hp工程师联系。  
b、db file sequential read，指的是顺序读，即全表扫描，这也是我们应该尽量减少的部分，解决方法就是使用索引、sql调优，同时可以增大db\_file\_multiblock\_read\_count这个参数。

c、db file scattered read,这个参数指的是通过索引来读取，同样可以通过增加db\_file\_multiblock\_read\_count这个参数来提高性能。

d、latch free,与栓相关的了，需要专门调节。

e、其他参数可以不特别观注

[http_www.zdnet.com.cn_developer_database_story_0_3800066906_39276980_00.htm]: http://www.zdnet.com.cn/developer/database/story/0,3800066906,39276980,00.htm
[meconsea_hotmail.com]: mailto:meconsea@hotmail.com
[meconsea_163.com]: mailto:meconsea@163.com
[Dserver_test_test_SJJZ]: mailto:Dserver=test/test@SJJZ