Oracle管理及常用基础脚本.doc

Oracle管理及常用基础脚本 1 1. 检测数据库中的事件和等待 ? SELECT event, total_waits, total_timeouts,time_waited, average_wait FROM v$system_event ? 2. 查询会话中的事件和等待时间 ? select sid, event, total_waits,average_wait from v$session_event where sid=10; 3. 查询等待进程 ? SELECT sid, seq#, event, wait_time, state FROM v$session_wait; ? 4. 监控全局区的性能 ? select * from v$sgastat; ? 5. 查询命中率 ? select gethitratio from v$librarycache where namespace = SQL AREA; 6. 当前 sql 语句 select sql_text, users_executing, executions, loads from v$sqlarea; 7. 查询高速缓存中的命中率 ? select sum(pins) Executions, sum(reloads) Cache Misses, sum(reloads)/sum(pins) from v$librarycache; 8. 查询全局字典中的有效装载次数 ? select namespace,pins,reloads,invalidations from v$librarycache; ? 9. 回滚段的争用情况 ? select name, waits, gets, waits/gets Ratio from v$rollstat a, v$rollname b where a.usn = b.usn; ? 10. 监控表空间的 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; 11. 监控文件系统的 I/O 比例 select substr(a.file#,1,2) #, substr(,1,30) Name, a.status, a.bytes, b.phyrds, b.phywrts from v$datafile a, v$filestat b where a.file# = b.file#; ? 12. 在某个用户下找所有的索引 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; ? 13. 监控 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; ? 14. 监控 SGA 中共享缓