ITPub博客

首页 > 应用开发 > IT综合 > 性能參數範例

性能參數範例

原创 IT综合 作者:hzh_hu 时间:2005-05-09 10:47:46 0 删除 编辑
您的位置:你的博客网 --> NASTAR的博客网 --> SQL SERVER --> 监控数据库性能的sql 最 近 更 新 ·SQL语句性能调整原则 ·SQL Server数据.. ·SQL语句导入导出大全 ·监控数据库性能的sql ·SQLServer和Ac.. ·SQL 语法参考手册(S.. 本 月 点 击 ·SQL语句性能调整原则 ·SQL语句导入导出大全 ·SQL Server数据.. ·监控数据库性能的sql ·SQLServer和Ac.. ·SQL 语法参考手册(S.. 最 新 推 荐 ·SQL语句性能调整原则 日期:2004年2月14日 星期六 天气:晴朗 作者:NASTAR 监控数据库性能的sql 发表:2004-2-14 11:22:18 出处:你的博客网(yourblog.org) -------------------------------------------------------------------------------- 监控数据库性能的SQL 1. 监控事例的等待 select event,sum(decode(wait_Time,0,0,1)) "Prev", sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot" from v$session_Wait group by event order by 4; 2. 回滚段的争用情况 select name, waits, gets, waits/gets "Ratio" from v$rollstat a, v$rollname b where a.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 from v$filestat f, dba_data_files df where f.file# = df.file_id order by df.tablespace_name; 4. 监控文件系统的 I/O 比例 select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name", a.status, a.bytes, b.phyrds, b.phywrts from v$datafile a, v$filestat b where a.file# = b.file#; 5.在某个用户下找所有的索引 select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name from user_ind_columns, user_indexes where user_ind_columns.index_name = user_indexes.index_name and user_ind_columns.table_name = user_indexes.table_name order by user_indexes.table_type, user_indexes.table_name, user_indexes.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" from v$sysstat a, v$sysstat b, v$sysstat c where a.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" from v$rowcache where gets+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 from v$librarycache; select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent" from v$librarycache; 9. 显示所有数据库对象的类别和大小 select count(name) num_instances ,type ,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 from dba_object_size group by type order by 2; 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 FROM v$latch WHERE name IN (’redo allocation’, ’redo copy’); 11. 监控内存和硬盘的排序比率,最好使它小于 .10,增加 sort_area_size SELECT name, value FROM v$sysstat WHERE name IN (’sorts (memory)’, ’sorts (disk)’); 12. 监控当前数据库谁在运行什么SQL语句 SELECT osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address =b.address order by address, piece; 13. 监控字典缓冲区 SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE; SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE; SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE; 后者除以前者,此比率小于1%,接近0%为好。 SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES" FROM V$ROWCACHE 14. 找ORACLE字符集 select * from sys.props$ where name=’NLS_CHARACTERSET’; 15. 监控 MTS select 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) 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; 查看碎片程度高的表 SELECT segment_name table_name , COUNT(*) extents FROM dba_segments WHERE owner 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 from dba_extents where tablespace_name=’&tablespace_name’ and segment_type=’TABLE’ group by tablespace_name,segment_name; select segment_name,count(*) from dba_extents where segment_type=’INDEX’ and owner=’&owner’ group by segment_name; 18、找使用CPU多的用户session 12是cpu used by this session select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value from v$session a,v$process b,v$sesstat c where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc; 查看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 FROM v$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 FROM v$lock s WHERE request != 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(t.name,1,10) tab,u.name 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 from v$lock l, v$session s, sys.user$ u,sys.obj$ t where l.sid = s.sid and s.type != ’BACKGROUND’ and t.obj# = l.id1 and u.user# = t.owner# 显示表空间的使用情况: col tsname format a16 justify c heading ’Tablespace’ col nfrags format 999,990 justify c heading ’Free|Frags’ col mxfrag format 999,999,990 justify c heading ’Largest|Frag (KB)’ col totsiz format 999,999,990 justify c heading ’Total|(KB)’ col avasiz format 999,999,990 justify c heading ’Available|(KB)’ col pctusd format 990 justify c heading ’Pct|Used’ select total.tablespace_name tsname, count(free.bytes) nfrags, nvl(max(free.bytes)/1024,0) mxfrag, total.bytes/1024 totsiz, nvl(sum(free.bytes)/1024,0) avasiz, (1-nvl(sum(free.bytes),0)/total.bytes)*100 pctusd from dba_data_files total, dba_free_space free where total.tablespace_name = free.tablespace_name(+) group by total.tablespace_name, total.bytes; SELECT * FROM V$RESERVED_WORDS 数据库的保留字 SELECT * FROM V$INSTANCE 实例建立信息 SELECT * FROM V$LICENSE 连接许可 SELECT * FROM V$RESOURCE_LIMIT 资源使用限制 SELECT * FROM V$DBLINK 数据库连接 SELECT * FROM V$PWFILE_USERS 在密码文件中的用户 SELECT * FROM V$OPTION 数据库的应用选项 SELECT * FROM V$COMPATIBILITY 数据库的兼容性 SELECT * FROM V$FIXED_TABLE ORACLE数据字典 SELECT * FROM V$FIXED_VIEW_DEFINITION ORACLE视图及定义 SELECT * FROM V$INDEXED_FIXED_COLUMN ORACLE索引的列 SELECT * FROM SYS.PROPS$ 数据库NLS格式 SELECT * FROM V$TABLESPACE 表空间名字及数量 SELECT * FROM V$CONTROLFILE 控制文件名称及位置 SELECT * FROM V$LOGFILE 日志文件名称及位置 SELECT * FROM V$DATAFILE 数据文件位置及当前状态 SELECT * FROM V$BACKUP 数据文件备份状态 SELECT * FROM V$FILESTAT 数据文件读、写状态 SELECT * FROM V$ARCHIVED_LOG 数据库当前所有归档日志 SELECT * FROM V$ARCHIVE_DEST 归档日志路径及状态 SELECT * FROM V$ARCHIVE_PROCESSES 日志写进程状态 SELECT * FROM V$ROLLNAME 回滚段名 SELECT * FROM V$POLLSTAT 回滚段状态 SELECT * FROM V$SGASTAT SGA分配状态 SELECT * FROM V$SYSSTAT 系统资源使用状态 SELCT * FROM V$LIBRARYCACHE 对象使用命中率 SELECT * FROM V$RESERVED_WORDS 数据库的保留字 SELECT * FROM V$INSTANCE 实例建立信息 SELECT * FROM V$LICENSE 连接许可 SELECT * FROM V$RESOURCE_LIMIT 资源使用限制 SELECT * FROM V$DBLINK 数据库连接 SELECT * FROM V$PWFILE_USERS 在密码文件中的用户 SELECT * FROM V$OPTION 数据库的应用选项 SELECT * FROM V$COMPATIBILITY 数据库的兼容性 SELECT * FROM V$FIXED_TABLE ORACLE数据字典 SELECT * FROM V$FIXED_VIEW_DEFINITION ORACLE视图及定义 SELECT * FROM V$INDEXED_FIXED_COLUMN ORACLE索引的列 SELECT * FROM SYS.PROPS$ 数据库NLS格式 SELECT * FROM V$TABLESPACE 表空间名字及数量 SELECT * FROM V$CONTROLFILE 控制文件名称及位置 SELECT * FROM V$LOGFILE 日志文件名称及位置 SELECT * FROM V$DATAFILE 数据文件位置及当前状态 SELECT * FROM V$BACKUP 数据文件备份状态 SELECT * FROM V$FILESTAT 数据文件读、写状态 SELECT * FROM V$ARCHIVED_LOG 数据库当前所有归档日志 SELECT * FROM V$ARCHIVE_DEST 归档日志路径及状态 SELECT * FROM V$ARCHIVE_PROCESSES 日志写进程状态 SELECT * FROM V$ROLLNAME 回滚段名 SELECT * FROM V$POLLSTAT 回滚段状态 SELECT * FROM V$SGASTAT SGA分配状态 SELECT * FROM V$SYSSTAT 系统资源使用状态 SELCT * FROM V$LIBRARYCACHE 对象使用命中率 SELECT * FROM V$THREAD 数据库进程状态 SELECT SUM(sharable_mem) FROM V$DB_OBJECT_CACHE 对象占用数据库缓存数 SELECT SUM(locks) FROM V$DB_OBJECT_CACHE WHERE owner <> ‘SYS’ 缓存当前对象使用数 SELECT COUNT(*) FROM V$LOCKED_OBJECT 被锁住的对象数 SELECT COUNT(*) FROM V$SESSION WHERE lock_view IS NOT NULL 被阻塞的会话数 SELECT COUNT(*) FROM V$LOCKWHERE ADDR NOT IN(SELECT ADDR FROM V$BGPROCESS) ; 当前锁总数(除后台进程) SELECT COUNT(decode(status,’free’,block#,NULL)) FREE , COUNT(decode(status,’cr’,block#,NULL)) 一致的读, COUNT(decode(status,’read’,block#,NULL)) Disk_Read, COUNT(decode(status,’scur’,block#,NULL)) Share_Mode, COUNT(decode(status,’xcur’,block#,NULL)) Exclu_Mode, COUNT(decode(dirty,’Y’,block#,NULL)) Dirty_BlockFROM V$BH 数据库缓存使用状态(按快数统计) SELECT sum(decode(n.statistic#, 15, s.value,0)) UGA, Trunc(sum(decode(n.statistic#, 20, s.value,0))/1024)||’K’ PGA,sum(decode(n.statistic#, 180, s.value,0)) Sore_In_Member,sum(decode(n.statistic#, 181, s.value,0)) Sore_In_Disk,sum(decode(n.statistic#, 182, s.value,0)) Sore_Of_Row,sum(decode(n.statistic#, 6, s.value,0)) User_Call,sum(decode(n.statistic#, 5, s.value,0)) User_Rollback,sum(decode(n.statistic#, 4, s.value,0)) User_Commit,sum(decode(n.statistic#, 3, s.value,0)) Current_Opened_Cursor,sum(decode(n.statistic#, 1, s.value,0)) Current_Logon,sum(decode(n.statistic#, 9, s.value,0)) Session_Logical_Read ,sum(decode(n.statistic#, 150, s.value,0)) Short_Table_Scans ,sum(decode(n.statistic#, 151, s.value,0)) Long_Table_Scans FROM V$SESSTAT s,V$STATNAME nWHERE s.statistic# = n.statistic# 数据库当前使用的UGA、SGA大小,打开的游标数,当前登入数、在内存和磁盘中的排序数等。 SELECT owner,name,type,referenced_name, referenced_name,referenced_typeFROM DBA_DEPENDENCIES WHERE owner = ’wner’ AND name = ’:name’; 一个对象所依赖的对象 SELECT ss.sid, SUM(decode(sn.statistic#, 15, ss.value,0)) UGA, TRUNC(SUM(decode(sn.statistic#, 20, ss.value,0))/1024)||’K’ PGA, SUM(decode(sn.statistic#, 180, ss.value,0)) Sore_In_Member, SUM(decode(sn.statistic#, 181, ss.value,0)) Sore_In_Disk, SUM(decode(sn.statistic#, 182, ss.value,0)) Sore_Of_Row, SUM(decode(sn.statistic#, 6, ss.value,0)) User_Call, SUM(decode(sn.statistic#, 5, ss.value,0)) User_Rollback, SUM(decode(sn.statistic#, 4, ss.value,0)) User_Commit, SUM(decode(sn.statistic#, 3, ss.value,0)) Current_Opened_Cursor, SUM(decode(sn.statistic#, 9, ss.value,0)) Session_Logical_Read , SUM(decode(sn.statistic#, 150, ss.value,0)) Short_Table_Scans , SUM(decode(sn.statistic#, 151, ss.value,0)) Long_Table_Scans FROM v$sesstat ss,v$statname sn WHERE ss.sid in( SELECT sid FROM v$session WHERE terminal = ’Machine_Name’ AND USERNAME IS NOT NULL) AND ss.statistic# = sn.statistic#GROUP BY ss.sid 得到一台计算机当前使用的UGA、SGA大小,打开的游标数,在内存和磁盘中的排序数等。 SELECT a.sid,s.terminal,s.program,count(a.sid) FROM V$ACCESS a,V$SESSION sWHERE a.owner <> ’SYS’AND s.sid = a.sid GROUP BY a.sid,s.terminal,s.programORDER BY count(a.sid); 当前每个会话使用的对象数 SELECT DECODE(dbo.object_type,’PACKAGE BODY’, ’ALTER PACKAGE ’||dbo.owner||’.’||dbo.object_name||’ COMPILE BODY’, ’ALTER ’||dbo.object_type||’ ’||dbo.owner||’.’||dbo.object_name||’ COMPILE’)ReComplie_Invalid_Object FROM DBA_OBJECTS dbo,SYS.ORDER_OBJECT_BY_DEPENDENCY oodWHERE dbo.object_id = ood.object_id(+) AND dbo.status = ’INVALID’ ANDdbo.object_type in(’PACKAGE BODY’,’PACKAGE’,’FUNCTION’,’PROCEDURE’,’TRIGGER’,’VIEW’)ORDER BY ood.dlevel, dbo.object_type, dbo.object_name 得到重新编译无效的数据库对象的SQL语句 SELECT BG.NAME, SW.SID, SW.EVENT,SW.P1TEXT, SW.P1, SW.WAIT_TIME, SW.SECONDS_IN_WAIT, SW.STATE FROM V$SESSION_WAIT SW, V$SESSION SS, V$BGPROCESS BGWHERE SW.SID = SS.SIDAND BG.PADDR = SS.PADDR; 后台进程会话等状态 SELECT SID,SQL_TEXTFROM V$SESSION_LONGOPS SL,V$SQLTEXT STWHERE SL.SQL_ADDRESS = ST.ADDRESSAND SL.SQL_HASH_value = ST.HASH_valueAND SID IN(SELECT SID FROM V$ACCESS)ORDER BY SID,PIECE; 当前会话长时间执行的SQL语句 SELECT DECODE(default_tablespace,’SYSTEM’,’S.’||username,’USER_DATA’,’U.’||username,NULL) DEFAULT_TABLESPACE,DECODE(temporary_tablespace,’SYSTEM’,’S.’||username,’U.’||’USER_DATA’,username,NULL) DEFA_TEMP_TABLESPACE FROM DBA_USERS WHERE ((default_tablespace IN(’SYSTEM’,’USER_DATA’) OR temporary_tablespace IN(’SYSTEM’,’USER_DATA’))AND username IN(SELECT owner FROM DBA_TABLES GROUP BY owner))UNIONSELECT DECODE(default_tablespace,’SYSTEM’,’S.’||username,’USER_DATA’,’U.’||username,NULL) DEFAULT_TABLESPACE,DECODE(temporary_tablespace,’SYSTEM’,’S.’||username,’U.’||’USER_DATA’,username,NULL) DEFA_TEMP_TABLESPACE FROM DBA_USERS WHERE (default_tablespace IN(’SYSTEM’,’USER_DATA’) OR temporary_tablespace IN(’SYSTEM’,’USER_DATA’))AND username IN(SELECT owner FROM DBA_VIEWS GROUP BY owner); 默认表空间在SYSTME、USER_DATA的模式 SELECT owner,table_name FROM DBA_TABLESWHERE tablespace_name in(’SYSTEM’,’USER_DATA’) AND owner NOT IN(’SYSTEM’,’SYS’,’OUTLN’, ’ORDSYS’,’MDSYS’,’SCOTT’, ’HOSTEAC’); 非系统用户建在SYSTEM表空间中的表 SELECT owner,name,line,position,textFROM DBA_ERRORSORDER BY name,sequence; 对象编译错误信息 SELECT name,text FROM DBA_SOURCEWHERE name IN(SELECT object_name FROM DBA_OBJECTS WHERE status = ’INVALID’ AND object_type <> ’JAVA CLASS’ AND owner NOT IN(’ORDSYS’,’SKYWALKER’)) ORDER BY name,line;; 错误对象脚本 【声明】你的博客网刊载此文并不代表同意其说法或描述,也不构成任何投资建议。转载请注明出处。 流量统计 暂时取消统计 评论:0 [大 中 小] 推荐 打印 举报 关闭 相 关 评 论 所有评论内容仅代表网友个人观点,与你的博客网无关。 -------------------------------------------------------------------------------- 发 表 评 论 用户名: 密码: 免费注册 忘记密码? 最多4800字 ┋ 关于本站 ┋ 广告服务 ┋ 项目合作 ┋ 联系方式 ┋ 版权声明 ┋ BLOGBUS ┋ 网上开店 Copyright © 2003-2004 WWW.YOURBLOG.ORG All Rights Reserved 版权所有 © 2003-2004 你的博客网(YOURBLOG.ORG)[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/95530/viewspace-797830/,如需转载,请注明出处,否则将追究法律责任。

下一篇: What is a latch?
请登录后发表评论 登录
全部评论

注册时间:2011-01-06

  • 博文量
    47
  • 访问量
    573419