|
|
dbget.sql ：
set serveroutput on;
set pages 10000;
set echo off
spool temp.txt
exec dbms_output.put_line('***************************');
exec dbms_output.put_line('v$Databse ');
select name,dbid,created,resetlogs_change#,log_mode from v$database;
exec dbms_output.put_line('***************************');
exec dbms_output.put_line('v$Parateter');
select name,value from v$parameter where name in
('aq_tm_processes','background_dump_dest','compatible','control_files','core_dump_dest',
'db_block_size','db_cache_size','db_domain','db_file_multiblock_read_count','db_name',
'dispatchers','fast_start_mttr_target','hash_join_enabled','instance_name','java_pool_size',
'job_queue_processes','large_pool_size','log_archive_dest_1','log_archive_format',
'log_archive_start','open_cursors','pga_aggregate_target','processes','query_rewrite_enabled',
'remote_login_passwordfile','shared_pool_size','sort_area_size','star_transformation_enabled',
'timed_statistics','undo_management','undo_retention','undo_tablespace','user_dump_dest')
exec dbms_output.put_line('***************************');
exec dbms_output.put_line('v$Datafile and v$tablespace');
select tbs.name tablespacename,daf.file#,daf.name,daf.bytes,daf.blocks,daf.block_size,daf.online_time
from v$datafile daf, v$tablespace tbs
where daf.ts#=tbs.ts#
order by daf.file#,tbs.name;
exec dbms_output.put_line('~~~~~~~~~');
exec dbms_output.put_line('***************************');
exec dbms_output.put_line('Background session DBWR:');
SELECT se.event, se.total_waits waits, se.total_timeouts timeouts,
se.time_waited total_time, se.average_wait avg
FROM v$session_event se,v$bgprocess bgp,v$session s
WHERE s.paddr = bgp.paddr and se.sid=s.sid
and bgp.name like '%DBW%'
ORDER BY time_waited DESC;
exec dbms_output.put_line('***************************');
exec dbms_output.put_line('Background session PMON:');
SELECT se.event, se.total_waits waits, se.total_timeouts timeouts,
se.time_waited total_time, se.average_wait avg
FROM v$session_event se,v$bgprocess bgp,v$session s
WHERE s.paddr = bgp.paddr and se.sid=s.sid
and bgp.name like '%PMON%'
ORDER BY time_waited DESC;
exec dbms_output.put_line('***************************');
exec dbms_output.put_line('Background session LGWR:');
SELECT se.event, se.total_waits waits, se.total_timeouts timeouts,
se.time_waited total_time, se.average_wait avg
FROM v$session_event se,v$bgprocess bgp,v$session s
WHERE s.paddr = bgp.paddr and se.sid=s.sid
and bgp.name like '%LGWR%'
ORDER BY time_waited DESC;
exec dbms_output.put_line('***************************');
exec dbms_output.put_line('Background session ARC:');
SELECT se.event, se.total_waits waits, se.total_timeouts timeouts,
se.time_waited total_time, se.average_wait avg
FROM v$session_event se,v$bgprocess bgp,v$session s
WHERE s.paddr = bgp.paddr and se.sid=s.sid
and bgp.name like '%ARC%'
ORDER BY time_waited DESC;
exec dbms_output.put_line('~~~~~~~~~');
exec dbms_output.put_line('***************************');
exec dbms_output.put_line('Cache size');
select name,to_char(value/(1024*1024))||'M' sizem from v$parameter where name='db_cache_size';
select name,to_char(value/(1024*1024))||'M' sizem from v$parameter where name='shared_pool_size';
select name,to_char(value/1024)||'K' sizek from v$parameter where name='log_buffer';
select name,to_char(value/1024)||'K' sizek from v$parameter where name='db_block_size';
exec dbms_output.put_line('***************************');
exec dbms_output.put_line('Users:');
select username,user_id,default_tablespace,temporary_tablespace,profile from dba_users where account_status='OPEN' ;
exec dbms_output.put_line('***************************');
/*DATA BUFFER命中率查询*/
exec dbms_output.put_line('Buffer hit ratio %: ');
SELECT round((1-(physical_reads/(db_block_gets+consistent_gets)))*100,2) H_RATIO FROM v$buffer_pool_statistics;
/*Soft parse ratio*/
exec dbms_output.put_line('Soft parse ratio %: ');
select round((1-(select value FROM V$SYSSTAT where statistic#=233)
/(select value FROM V$SYSSTAT where statistic#=232))*100,2) as soft_parse from dual;
/*In-memory sort ratio*/
exec dbms_output.put_line('In-memory sort ratio %: ');
select round(((select value FROM V$SYSSTAT where statistic#=242)
/((select value FROM V$SYSSTAT where statistic#=242)
+(select value FROM V$SYSSTAT where statistic#=243)))*100,2) as mem_sort from dual;
/*Parse to execute ratio*/
exec dbms_output.put_line('Parse to execute ratio %: ');
select round((1-(select value FROM V$SYSSTAT where statistic#=232)
/(select value FROM V$SYSSTAT where statistic#=235))*100,2) as parse_exec from dual;
/*Parse CPU to total CPU ratio:This shows how much of the total CPU time used was */
/*spent on activities other than parsing. When this ratio is low, the system is performing too many parses.*/
exec dbms_output.put_line('Parse CPU to total CPU ratio %: ');
select round((1-(select value FROM V$SYSSTAT where statistic#=230)
/(select value FROM V$SYSSTAT where statistic#=12))*100,2) as parse_cpu from dual;
/*Parse time CPU to parse time elapsed: this can indicate latch contention.*/
exec dbms_output.put_line('Parse time CPU to parse time elapsed %: ');
select round(((select value FROM V$SYSSTAT where statistic#=230)
/(select value FROM V$SYSSTAT where statistic#=12))*100,2) as parse_cpu_latch from dual;
/*five top Waits on the System */
exec dbms_output.put_line('five top Waits on the System %: ');
select event, total_waits waits, total_timeouts timeouts,
time_waited total_time, average_wait avg
from
(SELECT *
FROM V$SYSTEM_EVENT
ORDER BY 4 DESC)
where rownum<=5;
spool off;
set echo on; |
|
楼主: benny2002
IP卡
狗仔卡
发表于 2005-3-2 20:06
显身卡
楼主