oracle9i 常用问题三：虚拟字段133~173

? ? ?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?

158. 监控 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时，参数需加大?

159. 如何知道当前用户的ID号??

? ? ?SQL>SHOW USER；?

? ? ?或 ? ?SQL>select user from dual；?

160. 如何查看碎片程度高的表??

? ? ?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)；?

162. 如何知道表在表空间中的存储情况??

? ? ?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；?

163. 如何知道索引在表空间中的存储情况??

? ? ?select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner'?

? ? ?group by segment_name；?

164、如何知道使用CPU多的用户session??

? ? ?11是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#=11 and c.sid=a.sid and a.paddr=b.addr order by value desc；?

165. 如何知道监听器日志文件??

? ? ?以8I为例?

? ? ?$ORACLE_HOME/NETWORK/LOG/LISTENER.LOG?

166. 如何知道监听器参数文件??

? ? ?以8I为例?

? ? ?$ORACLE_HOME/NETWORK/ADMIN/LISTENER.ORA?

167. 如何知道TNS 连接文件??

? ? ?以8I为例?

? ? ?$ORACLE_HOME/NETWORK/ADMIN/TNSNAMES.ORA?

168. 如何知道Sql*Net 环境文件??

? ? ?以8I为例?

? ? ?$ORACLE_HOME/NETWORK/ADMIN/SQLNET.ORA?

169. 如何知道警告日志文件??

? ? ?以8I为例?

? ? ?$ORACLE_HOME/ADMIN/SID/BDUMP/SIDALRT.LOG?

170. 如何知道基本结构??

? ? ?以8I为例?

? ? ?$ORACLE_HOME/RDBMS/ADMIN/STANDARD.SQL?

171. 如何知道建立数据字典视图??

? ? ?以8I为例?

? ? ?$ORACLE_HOME/RDBMS/ADMIN/CATALOG.SQL?

172. 如何知道建立审计用数据字典视图??

? ? ?以8I为例?

? ? ?$ORACLE_HOME/RDBMS/ADMIN/CATAUDIT.SQL?

173. 如何知道建立快照用数据字典视图??

? ? ?以8I为例?

? ? ?$ORACLE_HOME/RDBMS/ADMIN/CATSNAP.SQL?