DBA常用的一些性能优化方面的SQL语句.doc

1. 监控事例的等待select event, sum(decode(wait_Time,0,0,1) Prev, sum(decode(wait_Time,0,1,0) Curr, count(*) Tot fromv$session_Wait group by event order by 4;2. 回滚段的争用情况 select name, waits, gets, waits/gets Ratio fromv$rollstat a, v$rollname b wherea.usn = b.usn; 3. 监控表空间的 I/O 比例 select df.tablespace_name name,df.file_name file,f.phyrds pyr, f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw fromv$filestat f, dba_data_files df wheref.file# = df.file_id order by df.tablespace_name;4. 监控文件系统的 I/O 比例 select substr(a.file#,1,2) #, substr(,1,30) Name, a.status, a.bytes, b.phyrds, b.phywrts fromv$datafile a, v$filestat b wherea.file# = b.file#; 5.在某个用户下找所有的索引及状态 select b.table_name, b.index_name, uniqueness, column_name,b.status from user_ind_columns a, user_indexes b where a.index_name = b.index_name and a.table_name = b.table_name order by b.table_type, b.table_name, b.index_name, column_position;6. 监控 SGA 的命中率 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 fromv$sysstat a, v$sysstat b, v$sysstat c wherea.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40; 7. 监控 SGA 中字典缓冲区的命中率 select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 miss ratio, (1-(sum(getmisses)/ (sum(gets)+sum(getmisses)*100 Hit ratio fromv$rowcache wheregets+getmisses <0 group by parameter, gets, getmisses; 8. 监控 SGA 中共享缓存区的命中率，应该小于1% select sum(pins) Total Pins, sum(reloads) Total Reloads, sum(reloads)/sum(pins) *100 libcache fromv$librarycache; select sum(pinhits-reloads)/sum(pins) hit radio, sum(reloads)/sum(pins) reload percent fromv$librarycache;9. 显示所有数据库对象的类别和大小 select type, count(name) num_instances, sum(source_size) source_size, sum(parsed_size) parsed_size, sum(code_size) code_size, sum(error_size) error_size, sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required fromdba_object_size group by type order by 1;10. 监控 SGA 中重做日志缓存区的命中率，应该小于1% SELECT name, gets, misses, immediate_gets, immediate_misses, Decode(gets,0,0,misses/gets*100) ratio1, Decode(immediate_gets+immediate_misses,0,0, immediate_misses/(immediate_gets+immediate_misses)*100) ratio2 FROMv$latch WHEREname IN (redo allocation, redo copy); 11.监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size SELECT name, value FROMv$sysstat WHEREname IN (sorts (memory), sorts (disk); 12. 监控当前数据库谁在运行什么SQL语句 SELECT osuser, username, sql_text fromv$session a, v$sqltext b wherea.sql_address =b.address order by address, piece;12.1 查看sql的执行时间select a.USERNAME 用户,a.SID 进程ID,a.TARGET 目标,b.SQL_TEXT 语句,a.START_TIME 开始时间,a.LAST_UPDATE_TIME 结束时间,round(a.TIME_REMAINING/60,2) 剩余时间,round(sofar*100/totalwork,0)|% 进度,a.OPNAME 操作,a.TOTALWORK|a.UNITS 工作量,round(a.ELAPSED_SECONDS/60,2) 已用时间,a.MESSAGE 提示信息from v$session_longops a,v$sql bwhere a.SQL_ADDRESS = b.ADDRESSand a.sql_hash_value = b.hash_value and a.TIME_REMAINING 0;13. 监控字典缓冲区 SELECT (SUM(PINS - RELOADS) / SUM(PINS) LIB CACHE FROMV$LIBRARYCACHE; SELECT (SUM(GETS - GETMISSES - USAGE - FIXED) / SUM(GETS) ROW CACHE FROMV$ROWCACHE; SELECT SUM(PINS) EXECUTIONS, SUM(RELOADS) CACHE MISSES WHILE EXECUTING FROMV$LIBRARYCACHE; 后者除以前者,此比率小于1%,接近0%为好。 SELECT SUM(GETS) DICTIONARY GETS,SUM(GETMISSES) DICTIONARY CACHE GET MISSES FROMV$ROWCACHE;14. 找ORACLE字符集 select * from ps$ where name=NLS_CHARACTERSET; 15. 监控 MTSselect busy/(busy+idle) shared servers busy from v$dispatcher;此值大于0.5时，参数需加大select sum(wait)/sum(totalq) dispatcher waits from v$queue where type=dispatcher;select count(*) from v$dispatcher;select servers_highwater from v$mts;servers_highwater接近mts_max_servers时，参数需加大16. 碎片程度 select tablespace_name,count(tablespace_name) fromdba_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, segment_name fromdba_free_space union all select tablespace_name,block_id,bytes,blocks, segment_name fromdba_extents; select * from ts_blocks_v; select tablespace_name,sum(bytes),max(bytes),count(block_id) fromdba_free_space group by tablespace_name; 查看碎片程度高的表 SELECT segment_name table_name , COUNT(*) extents FROMdba_segments WHEREowner NOT IN (SYS, SYSTEM) GROUP BY segment_name HAVING COUNT(*) = (SELECT MAX(COUNT(*) FROM dba_segments GROUP BY segment_name);17. 表、索引的存储情况检查 select segment_name,sum(bytes),count(*) ext_quan fromdba_extents where tablespace_name=&tablespace_name and segment_type=TABLE group by tablespace_name,segment_name; select segment_name,count(*) fromdba_extents wheresegment_type=INDEX and wner=&owner group by segment_name;18、找使用CPU多的用户session 12是cpu used by this session select a.sid,spid,status,substr(gram,1,40) prog, a.terminal,osuser,value/60/100 value fromv$session a,v$process b,v$sesstat c wherec.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;19.对可疑/性能不好的Server Process来进行Trace.,可以用tkprof来分析Trace的结果.比较方便.使用Unix的KSH. (1) start_trc: #!/bin/ksh if ( $# != 1 ) then echo Usuage: start_trc pid fi sid_serial=$(print connect / as sysdba; set heading off; set feedback off; select a.sid,a.serial# from v$session a,v$process b where a.paddr=b.addr and b.spid=$1; exit; | sqlplus -s /nolog | grep -v Connected | sed -e s/(0-91,)/1,/ -e /$/d ) if -z $sid_serial then print Seems that this process $1 is not an Oracle process! exit 1 fi print connect / as sysdba; execute dbms_system.set_sql_trace_in_session($sid_serial,true); exit; | sqlplus -s /nolog (2) stop_trc: #!/bin/ksh if ( $# != 1 ) then echo Usuage: stop_trc pid fi sid_serial=$(print connect / as sysdba; set heading off; set feedback off; select a.sid,a.serial# from v$session a,v$process b where a.paddr=b.addr and b.spid=$1; exit; | sqlplus -s /nolog | grep -v Connected | sed -e s/(0-91,)/1,/ -e /$/d ) if -z $sid_serial then print Seems that this process $1 is not an Oracle process! exit 1 fi print connect / as sysdba; execute dbms_system.set_sql_trace_in_session($sid_serial,false); exit; | sqlplus -s /nolog20.查看Lock SELECT sn.username, m.sid, m.type, DECODE(m.lmode, 0, None, 1, Null, 2, Row Share, 3, Row Excl., 4, Share, 5, S/Row Excl., 6, Exclusive, lmode, ltrim(to_char(lmode,990) lmode, DECODE(m.request,0, None, 1, Null, 2, Row Share, 3, Row Excl., 4, Share, 5, S/Row Excl., 6, Exclusive, request, ltrim(to_char(m.request,990) request, m.id1, m.id2 FROMv$session sn, v$lock m WHERE(sn.sid = m.sid AND m.request != 0) OR ( sn.sid = m.sid AND m.request = 0 AND lmode != 4 AND (id1, id2) IN ( SELECT s.id1, s.id2 FROMv$lock s WHERErequest != 0 AND s.id1 = m.id1 AND s.id2 = m.id2 ) ) ORDER BY id1, id2, m.request; select l.sid,s.serial#,s.username,s.terminal, decode(l.type,RW,RW - Row Wait Enqueue, TM,TM - DML Enqueue, TX,TX - Trans Enqueue, UL,UL - User,l.type|System) res, substr(,1,10) tab, owner, l.id1,l.id2, decode(l.lmode,1,No Lock, 2,Row Share, 3,Row Exclusive, 4,Share, 5,Shr Row Excl, 6,Exclusive,null) lmode, decode(l.request,1,No Lock, 2,Row Share, 3,Row Excl, 4,Share, 5,Shr Row Excl, 6,Exclusive,null) request fromv$lock l, v$session s, sys.user$ u,sys.obj$ t wherel.sid = s.sid and s.type != BACKGROUND and t.obj# = l.id1 and u.user# = t.owner#;監控登入登出的用戶:創建如下的兩張表:create table login_log - 登入登出信息表( session_id int not null, - sessionid login_on_timedate, - 登入時間 login_off_timedate, - 登出時間 user_in_db varchar2(30), - 登入的db user machine varchar2(20), - 機器名 ip_address varchar2(20), - ip地址 run_program varchar2(20) - 以何程序登入);create table allow_user - 網域用戶表( ip_address varchar2(20), - ip地址 login_user_name nvarchar2(20) - 操作者姓名);創建如下的兩個觸發器:create or replace trigger login_on_info - 紀錄登入信息的觸發器after logon on databaseBegin insert into login_log(session_id,login_on_time,login_off_time,user_in_db,machine,ip_address,run_program) select AUDSID,sysdate,null,sys.login_user,machine,SYS_CONTEXT(USERENV,IP_ADDRESS),program from v$session where AUDSID = USERENV(SESSIONID);-當前SESSIONEND;create or replac