Что видно в динамических таблицах

select   b.username, a.disk_reads, a.executions,
                a.disk_reads/decode(a.executions, 0, 1, a.executions) rds_exec_ratio, a.command_type, a.sql_text from    v$sqlarea a, dba_users b where a.parsing_user_id = b.user_id and     a.disk_reads > 30 order  by a.disk_reads desc;

ACCEPT indicator CHAR PROMPT 'Enter indicator to investigate
                   (either  ''disk_reads'' or ''buffer_gets''): ' ACCEPT nreads NUMBER PROMPT 'Enter minimal the value: ' SET VERIFY OFF select b.username, a.&indicator, a.executions,
             a.&indicator/decode(a.executions, 0, 1, a.executions) rds_exec_ratio, a.command_type, a.sql_text from    v$sqlarea a, dba_users b where a.parsing_user_id = b.user_id and     a.&indicator > &nreads order  by a.&indicator desc / 

select oc.user_name, s.sql_text from     v$open_cursor oc, v$sqltext s where oc.address = s.address and     oc.hash_value = s.hash_value order  by oc.user_name, s.piece;

select decode(state, 0, 'Free', 1, 'Modified', 2, 'Not Modified',
             3, 'Being    Read', 'Other') State, count(*) cnt from sys.x$bh group by state;

 select decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE', 'BEING USED'),3,'BEING USED',state) "BLOCK STATUS",count(*) from x$bh group by decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE', 'BEING USED'),3,'BEING USED',state);

 select a.file#, a.name, a.status, a.bytes, b.phyrds, b.phywrts from v$datafile a, v$filestat b where a.file# = b.file#;

select 1-(sum(decode(name, 'physical reads', value,0))/ (sum(decode(name, 'db block gets', value,0)) + (sum(decode(name, 'consistent gets', value,0))))) "Read Hit Ratio" from v$sysstat; 

 select sum(pins) "Executions", sum(pinhits) "Execution Hits", ((sum(pinhits) / sum(pins)) * 100) "Hit Ratio", sum(reloads) "Misses",
            ((sum(pins) / (sum(pins) + sum(reloads))) * 100) "Hit Ratio" from v$librarycache 

 select sum(gets), sum(getmisses), (1 - (sum(getmisses) / (sum(gets) + sum(getmisses)))) *
              100 HitRat from v$rowcache; 

select a.name, b.xacts, c.sid, c.serial#, c.username, d.sql_text from v$rollname a, v$rollstat b, v$session c, v$sqltext d,v$transaction    e where a.usn = b.usn 
and b.usn = e.xidusn 
and c.taddr = e.addr 
and c.sql_address    = d.address and c.sql_hash_value = d.hash_value order by a.name, c.sid, d.piece; 

alter system kill session 'sid,serial#'; 

select a.username, b.block_gets, b.consistent_gets, b.physical_reads,
             b.block_changes, b.consistent_changes from v$session a, v$sess_io b where a.sid = b.sid order by a.username; 

select a.sid, a.username, s.sql_text from v$session a, v$sqltext s where a.sql_address = s.address and a.sql_hash_value = s.hash_value order by a.username, a.sid, s.piece; 

select username, count(*) from v$session group by username;

select a.sid, a.username, b.owner, b.object, b.type from v$session a, v$access b 
where a.sid = b.sid; 

select a.sid, a.username, c.name, b.value from    v$session a, v$sesstat b, v$statname c where a.sid = b.sid and b.statistic# = c.statistic# and     b.value != 0; 

4. Некоторые параметры системы и текущей    конфигурации

select * from v$database;

select * from v$license; 

select * from v$option; 

select * from v$sga; 

select * from v$sgastat;

select * from v$parameter order by name; 

select * from v$nls_parameter; 

select a.member, b.* from v$logfile a, v$log b where a.group# = b.group#; 

select * from v$log_history;