Oracle???????????

???????????? ???????[ 2014/7/28 13:02:38 ] ????????????? ???????

?????????????39????￡???????????????ˉ????????????????????????????????????????????????????????:)

????????????????????岥???????oracle?????????????伯????oracle?????Щ???????????????

????1. ???????????

????select event??sum(decode(wait_Time??0??0??1)) "rev"??

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