dba.txt

DBA具体操作方法以及常见问题解决办法

AND a.sql_address=c.address(+) ORDER BY c.piece;



数据库共享池性能检查:

Select namespace,gets,gethitratio,pins,pinhitratio,reloads,

Invalidations from v$librarycache where namespace in

('SQLAREA','TABLE/PROCEDURE','BODY','TRIGGER');



检查数据重载比率:

select sum(reloads)/sum(pins)*100 "reload ratio" from

v$librarycache;



检查数据字典的命中率:

select 1-sum(getmisses)/sum(gets) "data dictionary hit

ratio" from v$rowcache;

(对于library cache, gethitratio和pinhitratio应该大于90%,对于数据重载比率,reload ratio应该小于1%,对于数据字典的命中率,data dictionary hit ratio应该大于85%)



检查共享内存的剩余情况:

select request_misses, request_failures from v$shared_pool_reserved;

(对于共享内存的剩余情况, request_misses 和request_failures应该接近0)



数据高速缓冲区性能检查:

select 1-p.value/(b.value+c.value) "db buffer cache hit

ratio" from v$sysstat p,v$sysstat b,v$sysstat c where

p.name='physical reads' and b.name='db block gets' and

c.name='consistent gets';

检查buffer pool HIT_RATIO执行

select name, (physical_reads/(db_block_gets+consistent_gets))

"MISS_HIT_RATIO" FROM v$buffer_pool_statistics WHERE (db_block_gets+ consistent_gets)> 0;

(正常时db buffer cache hit ratio 应该大于90%,正常时buffer pool MISS_HIT_RATIO 应该小于10%)



数据库回滚段性能检查:

检查Ratio执行

select sum(waits)* 100 /sum(gets) "Ratio", sum(waits)

"Waits", sum(gets) "Gets" from v$rollstat;

检查count/value执行:

select class,count from v$waitstat where class like '%undo%';

select value from v$sysstat where name='consistent gets';

(两者的value值相除)



检查average_wait执行:

select event,total_waits,time_waited,average_wait from v$system_event

where event like '%undo%';



检查RBS header get ratio执行:

select n.name,s.usn,s.wraps, decode(s.waits,0,1,1- s.waits/s.gets)"RBS

header get ratio" from v$rollstat s,v$rollname n where s.usn=n.usn;

(正常时Ratio应该小于1%, count/value应该小于0.01%,average_wait最好为0，该值越小越好,RBS header get ratio应该大于95%)



杀会话的脚本:

select A.SID,B.SPID,A.SERIAL#,a.lockwait,A.USERNAME,A.OSUSER,a.logon_time,a.last_call_et/3600 LAST_HOUR,A.STATUS,
'orakill '||sid||' '||spid HOST_COMMAND,
'alter system kill session '''||A.sid||','||A.SERIAL#||'''' SQL_COMMAND
from v$session A,V$PROCESS B where A.PADDR=B.ADDR AND SID>6;



查看排序段的性能:

SQL>SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');

7、查看数据库库对象:

select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;



8、查看数据库的版本:　

Select * from v$version;



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、查看回滚段:

SQL>col name format a10

SQL>set linesize 100

SQL>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;



根据PID查找相应的语句:

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;



根据SID找ORACLE的某个进程:

SQL> select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr;



监控当前数据库谁在运行什么SQL语句:
SQL>SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;



如何查看数据库中某用户，正在运行什么SQL语句

SQL>SELECT SQL_TEXT FROM V$SQLTEXT T, V$SESSION S WHERE T.ADDRESS=S.SQL_ADDRESS

AND T.HASH_VALUE=S.SQL_HASH_VALUE AND S.MACHINE='XXXXX' OR USERNAME='WACOS';



如何查出前台正在发出的sql语句:

SQL> select user_name,sql_text from v$open_cursor where sid in(select sid from (select sid,serial# from v$session where status='ACTIVE'));



查询当前所执行的SQL语句：



SQL> select program ,sql_address from v$session where paddr in (select addr

from v$process where spid=3556);



PROGRAM SQL_ADDRESS

------------------------------------------------ ----------------

sqlplus@ctc20 (TNS V1-V3) 000000038FCB1A90



SQL> select sql_text from v$sqlarea where address='000000038FCB1A90';



找出消耗CPU最高的进程对应的SQL语句：

set line 240

set verify off

column sid format 999

column pid format 999

column S_# format 999

column username format A9 heading "ORA User"

column program format a29

column SQL format a60

COLUMN OSname format a9 Heading "OS User"

SELECT P.pid pid,S.sid sid,P.spid spid,S.username username,

S.osuser osname,P.serial# S_#,P.terminal,P.program program,

P.background,S.status,RTRIM(SUBSTR(a.sql_text, 1, 80)) SQL

FROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr

AND S.sql_address = a.address (+) AND P.spid LIKE '%&1%';



Enter value for 1: PID（这里输入占用CPU最高的进程对应的PID）





set termout off

spool maxcpu.txt

SELECT '++'||S.username username,

RTRIM(REPLACE(a.sql_text,chr(10),''))||';'FROM v$process P, v$session S,

v$sqlarea A WHERE P.addr = s.paddr AND S.sql_address = a.address (+)

AND P.spid LIKE '%&&1%';

Enter value for 1: PID（这里输入占用CPU最高的进程对应的PID）

spool off(这句放在最后执行)



CPU用率最高的2条SQL语句的获取

执行：top，通过top获得CPU占用率最高的进程的pid。

SQL>select sql_text,spid,v$session.program,process from v$sqlarea,v$session,v$process where v$sqlarea.address=v$session.sql_address and v$sqlarea.hash_value=v$session.sql_hash_value

and v$session.paddr=v$process.addr and v$process.spid in (pid);



col machine format a30
col program format a40
set line 200
SQL>select sid,serial# ,username,osuser,machine,program,process,to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') from v$session where paddr in(select addr from v$process where spid in([$spid]));



select sql_text from v$sqltext_with_newlines
where hash_value=(select SQL_HASH_VALUE from v$session where sid=&sid)
order by piece;



16、查看锁（lock）情况:

SQL>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;



SQL>select sys.v_$session.osuser,sys.v_$session.machine,v$lock.sid,
sys.v_$session.serial#,decode(v$lock.type,'MR','Media Recovery',
'RT','Redo Thread','UN','User Name','TX', 'Transaction','TM','DML',
'UL','PL/SQL User Lock','DX','Distributed Xaction','CF','Control File',
'IS','Instance State','FS','File Set','IR','Instance Recovery',
'ST','Disk Space Transaction','TS','Temp Segment','IV','Library Cache Invalida-tion','LS','Log Start or Switch','RW','Row Wait','SQ','Sequence Number','TE','Extend Table','TT','Temp Table','Unknown') LockType,
rtrim(object_type) || ' ' || rtrim(owner) || '.' || object_name object_name,decode(lmode, 0, 'None',1, 'Null',2, 'Row-S',3, 'Row-X',4, 'Share',
5, 'S/Row-X',6, 'Exclusive','Unknown') LockMode,decode(request, 0, 'None',1, 'Null',2, 'Row-S',3, 'Row-X', 4, 'Share',5, 'S/Row-X',
6, 'Exclusive', 'Unknown') RequestMode,ctime, block b
from v$lock, all_objects, sys.v_$session
where v$Lock.sid > 6
and sys.v_$session.sid = v$lock.sid
and v$lock.id1 = all_objects.object_id;



以DBA角色, 查看当前数据库里锁的情况可以用如下SQL语句：
col owner for a12
col object_name for a16
select b.owner,b.object_name,l.session_id,l.locked_mode
from v$locked_object l, dba_objects b
where b.object_id=l.object_id;

SQL>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>Select sql_address from v$session where sid=;
SQL>Select * from v$sqltext where address=;



SQL>select COMMAND_TYPE,PIECE,sql_text from v$sqltext where address=(select sql_address from v$session a where sid=18);



SQL>select object_id from v$locked_object;

SQL>select object_name,object_type from dba_objects where object_id=’’;

如果有长期出现的一列，可能是没有释放的锁。我们可以用下面SQL语句杀掉长期没有释放非正常的锁：
SQL>alter system kill session 'sid,serial#';


17、查看等待（wait）情况:

SQL>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情况:

SQL>SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC;

19、查看catched object:

SQL>SELECT owner,name,db_link,namespace,type,sharable_mem,loads, executions,locks,pins,kept FROM v$db_object_cache;

20、查看V$SQLAREA:

SQL>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 all_objects;

22、有关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=18 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=8);



根据pid查看sql语句:
select sql_text from v$sql
where address in
(select sql_address from v$session
where sid in
(select sid from v$session where paddr in (select addr from v$process where spid=&pid)));



23、查询表空间使用情况:

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;