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?
