set pagesize 9999
set linesize 999	
set long 9999
set echo off
set termout off

alter session set nls_language=american;


prompt 
prompt
prompt '*********************************************************************'
prompt '*********************** 数据库配置 **********************************'
prompt '*********************************************************************'
prompt '1,软件版本:'
select * from v$version;
prompt
select created,name,log_mode,open_mode from v$database;
prompt
prompt "补丁状态:"
host opatch lsinventory
prompt  --如果没有输出 ,有可能是环境变量里没有设置路径,请到$ORACLE_HOME/OPatch目录下执行
prompt 
prompt
prompt '组件状态'
col comp_id format a10;
col comp_name format a35;
col version format a15;
col status format a8;
select comp_id,replace(comp_name,' ','.') comp_name,version,status,replace(replace(modified,' ',':'),'-','/')  modified from dba_registry;

prompt
prompt
prompt '2,数据库例程名称:'
col instance_name format a10;
col db_name format a10;
select b.log_mode,decode(a.name,'instance_name',a.value) instance_name,decode(a.name,'db_name',a.value) db_name 
from v$parameter a,v$database b where a.name in('db_name','instance_name') ;
prompt
prompt
prompt '目前数据量(G):'
select  c.sum3 "dmp(G)",a.sum1 "RMAN(G)",b.sum2 "datafiles(G)" from (SELECT ceil(SUM(BYTES)/1024/1024/1024) sum1 FROM DBA_segments) a,(select ceil(sum(bytes)/1024/1024/1024) sum2 from v$datafile) b,(select ceil(sum(bytes)/1024/1024/1024) sum3 from dba_extents where segment_type not like 'INDEX%' and segment_type not in('ROLLBACK','CACHE','LOBINDEX','TYPE2 UNDO')) c ;
prompt
prompt
prompt '瞬时活动会话数:'
SELECT COUNT(*) "瞬时活动会话数" FROM V$SESSION WHERE STATUS='ACTIVE' AND USERNAME IS NOT NULL;
prompt
prompt
prompt '4,数据库字符集和语言参数:'
col value$ format a35;
col name format a35;
select name,value$ from sys.props$ where name in('NLS_TERRITORY','NLS_LANGUAGE','NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');
prompt
prompt
prompt '5,表空间数量及数据文件数量'
select b.sum2 "表空间数量",a.sum1 "数据文件数量" from (select count(1) sum1 from v$datafile) a, (select count(1) sum2 from v$tablespace )b ;
prompt
prompt
prompt '6,重要性能参数:'
col value format a45;
col name format a35;
select name ,value from v$parameter where name 
in('cpu_count' ,'sga_target','db_cache_size','db_2k_cache_size','db_4k_cache_size','db_8k_cache_size','db_16k_cache_size','db_32k_cache_size',
'shared_pool_size','large_pool_size','java_pool_size','log_buffer','pga_aggregate_target','sort_area_size','db_block_buffers','db_block_size','optimizer_mode','cursor_sharing','optimizer_index_cost_adj','optimizer_index_caching','db_file_multiblock_read_count','hash_join_enabled');
prompt
prompt
prompt '7,基本运行统计(v$waitstat):'
select * from v$waitstat ;
prompt
prompt
prompt '8,会话数量:'
select a.sum1 "目前连接数",b.sum2 "活动连接数" from (select count(*) sum1 from v$session where username is not null) a, (select count(*) sum2 from v$session where username is not null and status='ACTIVE') b;
select * from v$license;
prompt
prompt
prompt '*********************************************************************'
prompt '*********************** 数据库监控 **********************************'
prompt '*********************************************************************'
prompt '******************************************'
prompt '**************数据库init参数**************'
prompt '******************************************'
col value format a55;
col name format a30;
col trim(num) format a10;
select trim(num),name,value,ismodified,isadjusted from v$parameter where isdefault='FALSE' and name not like '%l_files';
prompt
prompt
prompt '******************************************'
prompt '**************数据库资源限制**************'
prompt '******************************************'
col RESOURCE_NAME format a30;
col CURRENT_UTILIZATION format a10;
col MAX_UTILIZATION format a10;
col INITIAL_ALLOCATION format a10;
col LIMIT_VALUE format a10;
select trim(RESOURCE_NAME) RESOURCE_NAME,trim(CURRENT_UTILIZATION) CURRENT_UTILIZATION,trim(MAX_UTILIZATION) MAX_UTILIZATION,trim(INITIAL_ALLOCATION) INITIAL_ALLOCATION,trim(LIMIT_VALUE) LIMIT_VALUE from v$resource_limit;
prompt
prompt
prompt '******************************************'
prompt '************Physical structure************'
prompt '******************************************' 
prompt '************************'
prompt '*******控制文件*********'
prompt '************************'
col name format a60
select * from v$controlfile;
select type,record_size,records_total,records_used from v$controlfile_record_section;

prompt
prompt
prompt '************************'
prompt '*******日志文件*********'
prompt '************************'
col member format a60;
col thread# format 99;
col group# format 99;
select trim(a.thread#) thread#,trim(a.group#) group#,b.member member,a.status status,a.bytes/1024/1024 "size(M)" from v$log a,v$logfile b where a.group#=b.group#;
prompt
prompt
prompt '日志归档情况:'
select a.f_time "日期",
       a.thread#,
       ceil(sum(a.blocks * a.block_size) / 1024 / 1024 / 1024) "每天归档量(G)",
       ceil(sum(a.blocks * a.block_size) / 1024 / 1024 / 24) "每小时avg归档量(M)"
  from (select distinct sequence#,
                        thread#,
                        blocks,
                        block_size,
                        to_char(first_time, 'yyyy/mm/dd') f_time
          from v$archived_log) a
 group by a.f_time, a.thread#
 order by 3 desc;
prompt
prompt
prompt


select to_char(first_time,'yyyy/mm/dd:hh24') "日期",thread#,count(1) "高峰时每小时归档个数"
 from v$log_history 
 where trunc(first_time) 
 	in (select d_time 
 			from (select max(count(1)) m_arch 
 								from v$log_history group by trunc(first_time)) a,
 					(select trunc(first_time) d_time,count(1) d_arch 
 								from v$log_history group by trunc(first_time)) b 
 	where a.m_arch=b.d_arch) 
 	group by to_char(first_time,'yyyy/mm/dd:hh24'),thread# order by 3 desc ,thread#,1 ;
prompt
prompt
prompt '************************'
prompt '*******数据文件*********'
prompt '************************'
col tablespace_name format a20;
col file_name format a45
col file_id format a8;
select trim(a.file_id) "FILE_ID",b.tablespace_name "TABLESPACE_NAME",b.status "TABLESPACE_STATUS",a.file_name "FILE_NAME",a.status "FILE_STATUS",ceil(a.bytes/1024/1024) "FILE_SIZE(M)",a.AUTOEXTENSIBLE "AUTOEXTENSIBLE" from dba_data_files a,dba_tablespaces b where a.tablespace_name=b.tablespace_name  order by a.AUTOEXTENSIBLE desc,a.file_id;

prompt
prompt
prompt '************************'
prompt '******临时数据文件******'
prompt '************************'
col tablespace_name format a20;
col file_id format a8;
select trim(a.file_id) "FILE_ID",b.tablespace_name "TABLESPACE_NAME",b.status "TABLESPACE_STATUS",a.file_name "FILE_NAME",a.status "FILE_STATUS",ceil(a.bytes/1024/1024) "FILE_SIZE(M)",a.AUTOEXTENSIBLE "AUTOEXTENSIBLE" from dba_temp_files a,dba_tablespaces b where a.tablespace_name=b.tablespace_name  order by a.AUTOEXTENSIBLE desc,a.file_id;

prompt
prompt
prompt
prompt '************************'
prompt '******数据库链路状态******'
prompt '************************'
prompt '以创建日期先后顺序及owner排序'

col username for a25
col host for a20
col userid for a15 
col name for a25
col pwd  for a20
col created for  a15
set line 200
 
 select a.name ,a.host,b.username,nvl(a.userid,'NULL') userid ,nvl(a.password,'NULL') pwd,replace(a.ctime,'-','/') "created" from link$ a,dba_users b where a.owner#=b.user_id(+);
 
prompt
prompt
prompt '************************'
prompt '**状态不正常的数据文件**'
prompt '************************'
col file_id format a8;
col file_name format a45;
select trim(f.FILE#) "FILE_ID",f.name "FILE_NAME",f.status "STATUS",f.bytes/1024/1024 "FILE_SIZE(M)",t.name tablespace_name from v$datafile f,v$tablespace t where f.ts#=t.ts# and  (status<>'ONLINE'  AND status<>'SYSTEM' )or  enabled<>'READ WRITE';

prompt
prompt
prompt '******************************************'
prompt '***************Tablespaces****************'
prompt '******************************************'
prompt '************************'
prompt '*****表空间碎片情况*****'
prompt '************************'
select a.tablespace_name ,count(1) "碎片量" from dba_free_space a,dba_tablespaces  b where a.tablespace_name=b.tablespace_name and b.EXTENT_MANAGEMENT='DICTIONARY' group by a.tablespace_name having count(1) >20 order by 2;
prompt '碎片整理脚本'
select 'alter tablespace '||tablespace_name||' coalesce;' "整理脚本"  from dba_tablespaces where EXTENT_MANAGEMENT='DICTIONARY';

prompt
prompt
prompt '************************'
prompt '*****表空间空闲监控*****'
prompt '************************'
select a.tablespace_name "表空间名",
       nvl(ceil((1 - b.free / a.total) * 100),100) "表空间已使用比例%",
       nvl(b.free,0) "剩余空间大小M",
       c.EXTENT_MANAGEMENT "范围管理方式"
  from (select tablespace_name, sum(nvl(bytes, 0)) / 1024 / 1024 total
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(nvl(bytes, 0)) / 1024 / 1024 free
          from dba_free_space
         group by tablespace_name) b,
       dba_tablespaces c
 where a.tablespace_name = c.tablespace_name
   and c.tablespace_name = b.tablespace_name(+)
 order by (1 - b.free / a.total) * 100 desc;

prompt
prompt
prompt '******************************************'
prompt '************Rollback Segments*************'
prompt '******************************************'
prompt '************************'
prompt '*****回滚段监控情况*****'
prompt '************************'

col usn format a8;
col name format a20
select trim(a.usn) usn, a.name, b.status,b.xacts, (b.rssize+8192)/1024/1024 Ssize, b.optsize/1024/1024 OPT  from v$rollname a, v$rollstat b where a.usn<>0 and a.usn=b.usn  order by 1;
prompt '************************'
prompt '*****回滚段的争用情况v$rollstat*****'
prompt '************************'
select sum(gets),sum(waits),sum(waits)/sum(gets)*100 ratio from v$rollstat ;


prompt '************************'
prompt '********无效索引********'
prompt '************************'
col index_owner format a12;
col index_name format a30;
col index_type format a20;
select owner index_owner,      index_name,index_type, 'N/A' partition_name,status ,table_name, tablespace_name from dba_indexes where status='UNUSABLE'
union all
select a.index_owner,          a.index_name,b.index_type,      a.partition_name, a.status ,        b.table_name, a.tablespace_name from dba_ind_partitions a, dba_indexes b where a.index_name=b.index_name and a.index_owner=b.owner and a.status='UNUSABLE';
prompt
prompt
prompt '************************'
prompt '*在同一表空间的表和索引*'
prompt '************************'
col owner for a20
col table_name for a20
select tab.owner ,tab.table_name,idx.index_name,tab.tablespace_name from dba_tables tab,dba_indexes idx where tab.owner=idx.table_owner and tab.table_name=idx.table_name and tab.tablespace_name=idx.tablespace_name and tab.owner not in('WKSYS','WK_TEST','EXFSYS','SYS','SYSTEM','SYSMAN','DBSNMP','ANONYMOUS','CTXSYS','OLAPSYS','WMSYS','OUTLN','XDB','CTXSYS','MDSYS','ORDSYS','PERFSTAT');
prompt
prompt
prompt '************************'
prompt '*系统表空间中非系统对象*'
prompt '************************'
col tablespace_name format a25;
col segment_name format a40;
col segment_type for a15
col owner format a20;
select owner,segment_name,segment_type,ceil(bytes/1024/1024) "SIZE(M)",tablespace_name from dba_segments where tablespace_name='SYSTEM' and owner NOT in ('ORDSYS','WKSYS','WK_TEST','SYS','SYSTEM','SYSMAN','DBSNMP','ANONYMOUS','CTXSYS','OLAPSYS','WMSYS','OUTLN','XDB','CTXSYS','MDSYS','EXFSYS');
prompt
prompt
prompt '************************'
prompt '********失效约束********'
prompt '************************'
select owner,constraint_name,constraint_type,table_name from dba_constraints where status='DISABLED' and owner not in('SYS','SYSTEM');
prompt
prompt
prompt '************************'
prompt '********失效触发器********'
prompt '************************'
select OWNER,TRIGGER_NAME,replace(TRIGGER_TYPE,' ','/'),replace(TRIGGERING_EVENT,' ','/') TRIGGERING_EVENT,replace(BASE_OBJECT_TYPE,' ','/') BASE_OBJECT_TYPE from dba_triggers where status='DISABLED' and owner<>'SYS';
prompt
prompt
prompt '************************'
prompt '********无效对象********'
prompt '************************'
col owner format a12;
col object_name format a30;
col object_type format a20;
select owner,object_name,object_type from dba_objects where status='INVALID' and owner not in ('SYS','SYSTEM','SCOTT','PERFSTAT');
	



prompt
prompt
prompt '******************************************'
prompt '****Tables/Indexes/Constraints/Triggers***'
prompt '******************************************'
prompt '************************'
prompt '*数据库统计信息收集情况*'
prompt '************************'
col "统计信息收集级别" format a20;
col "是否自动收集?" format a20;
select value "统计信息收集级别",decode(value,'BASIC','NO','YES') "是否自动收集?" from v$parameter where name='statistics_level';
prompt
prompt
prompt '************************'
prompt '*****存在行连接的表*****'
prompt '************************'
select owner,table_name,chain_cnt from dba_tables where chain_cnt>0 order by chain_cnt desc;
prompt                                                              
prompt                                                              
prompt '************************'                                   
prompt '****大于2G未分区的表****'                                   
prompt '************************'                                   
select a.owner,a.segment_name,a.bytes "SIZE(G)" from (select round(sum(bytes)/1024/1024/1024) bytes,segment_name,owner from dba_extents group by segment_name ,owner having round(sum(bytes)/1024/1024/1024)>=2) a,dba_segments b,(select owner,table_name from dba_tables where partitioned='NO') c where a.segment_name=b.segment_name and a.owner=b.owner and b.segment_type='TABLE' and b.owner=c.owner and b.segment_name=c.table_name order by a.bytes desc; 
prompt
prompt
prompt '************************'
prompt '****Level较高的索引*****'
prompt '************************'
select owner,table_name,index_name,blevel from dba_indexes where blevel>2;
prompt
prompt

prompt
prompt
prompt '******************************************'
prompt '*************User defination**************'
prompt '******************************************'
prompt '************************'
prompt '********用户情况********'
prompt '************************'
col username format a19;
col status format a18;
col DEFAULT_TABLESPACE for a20
col temporary_tablespace format a20;
col profile format a15;
col INITIAL_RSRC_CONSUMER_GROUP format a30
select username,replace(account_status,' ','') status,default_tablespace,temporary_tablespace,profile, initial_rsrc_consumer_group from dba_users where account_status='OPEN';
prompt
prompt
prompt '************************'
prompt '***数据库启停权限监控***'
prompt '************************'
col sysdba format a10
col sysoper format a10
select * from v$pwfile_users order by 1;
prompt
prompt
prompt '************************'
prompt '******管理权限监控******'
prompt '************************'
col GRANTEE format a19;
col GRANTED_ROLE format a20;
select a.* from dba_role_privs a ,dba_users b where b.username=a.grantee and b.account_status='OPEN' and a.granted_role in ('DBA','SYSDBA','SYSOPER','EXP_FULL_DATABASE','DELETE_CATALOG_ROLE') order by a.GRANTED_ROLE;
prompt '************************'
prompt '******系统权限监控******'
prompt '************************'
col GRANTEE format a19;
col privilege format a30;
select GRANTEE,replace(PRIVILEGE,' ','.') PRIVILEGE,ADMIN_OPTION from 
(select a.* from dba_sys_privs a,dba_users b where ((a.grantee=b.username and b.account_status='OPEN') ) and (a.privilege like '%ANY%' OR a.PRIVILEGE IN('ALTER SYSTEM','ALTER DATABASE','DROP USER')) and a.grantee not in ('SYS','SYSMAN','SYSTEM')
union
select a.* from dba_sys_privs a,dba_roles c where (a.grantee=c.role ) and (a.privilege like '%ANY%' OR a.PRIVILEGE IN('ALTER SYSTEM','ALTER DATABASE','DROP USER')) and a.grantee not in ('JAVADEBUGPRIV','OLAP_DBA','SCHEDULER_ADMIN','DBA','IMP_FULL_DATABASE','AQ_ADMINISTRATOR_ROLE','EXP_FULL_DATABASE','OEM_MONITOR') )
order by 1,2;

prompt
prompt '************************'
prompt '******角色权限监控******'
prompt '************************'
col GRANTEE format a19;
col GRANTED_ROLE format a20;
select a.* from dba_role_privs a ,dba_users b where b.username=a.grantee and b.account_status='OPEN' and a.granted_role in ('DBA','SYSDBA','SYSOPER','EXP_FULL_DATABASE','DELETE_CATALOG_ROLE') order by a.GRANTED_ROLE;
prompt '************************'
prompt '******系统权限监控******'
prompt '************************'
col GRANTEE format a19;
col privilege format a30;
select GRANTEE,replace(PRIVILEGE,' ','.') PRIVILEGE,ADMIN_OPTION from 
(select a.* from dba_role_privs a,dba_users b where ((a.grantee=b.username and b.account_status='OPEN') ) and (a.privilege like '%ANY%' OR a.PRIVILEGE IN('ALTER SYSTEM','ALTER DATABASE','DROP USER')) and a.grantee not in ('SYS','SYSMAN','SYSTEM')
union
select a.* from dba_role_privs a,dba_roles c where (a.grantee=c.role ) and a.grantee not in ('JAVADEBUGPRIV','OLAP_DBA','SCHEDULER_ADMIN','DBA','IMP_FULL_DATABASE','AQ_ADMINISTRATOR_ROLE','EXP_FULL_DATABASE','OEM_MONITOR') )
order by 1,2;
prompt
prompt
prompt '************************'
prompt '*****数据库运行日志*****'
prompt '*****ls -l|wc -l*****'
prompt '*****ls -ltr|tail*****'
prompt '************************'
prompt '登录到数据库服务器,查看最近几天的以下日志文件:'
select a.value||decode(substr(a.value,1,1),'/','/','\')||b.value||'.log'  from v$parameter a,v$parameter b  where a.name='background_dump_dest' and b.name='db_name';
show parameter dump_dest
show parameter file_dest
select '$ORACLE_HOME/network/log/listener.log' from dual;
select '$ORACLE_HOME/network/log/sqlnet.log' from dual;

prompt
prompt
prompt '*********************************************************************'
prompt '************************系统和数据库的性能***************************'
prompt '*********************************************************************'
prompt '******************************************'
prompt '************操作系统配置和监控************'
prompt '******************************************'
prompt '************************'
prompt '*******磁盘空间*********'
prompt '************************'
prompt '登录到数据库服务器,执行如下命令:'
prompt 'hp unix:  bdf '
prompt 'others :  df -k'


prompt
prompt
prompt '************************'
prompt '*******系统性能*********'
prompt '************************'
prompt '登录到数据库服务器,执行如下命令:'
prompt 'aix    : topas  '
prompt 'hp unix: glance or top or sar -u 30 20 or vmstat 30 20 '
prompt 'solaris: prstat -l or top or sar -u 30 20 or vmstat 30 20 '
prompt 'lunix  : top or sar -u 30 20 or vmstat 30 20 '

prompt
prompt
prompt '******************************************'
prompt '*************数据库配置和监控  awrrpt.sql *************'
prompt '******************************************'

prompt '************************'
prompt '*****数据库负载统计*****'
prompt '************************'
prompt ''
prompt '截取AWR的Load Profile部分'
prompt ''

prompt
prompt
prompt '************************'
prompt '***数据库内存效率统计***'
prompt '************************'
prompt
prompt '截取AWR的Instance Efficiency Percentages (Target 100%)部分'
prompt


prompt
prompt
prompt '************************'
prompt '**等待事件(wait event)**'
prompt '************************'
prompt
prompt
prompt '截取AWR的Top 5 Events部分'
prompt
prompt


prompt '************************'
prompt '*******Top SQLs*********'
prompt '************************'
prompt
prompt '截取AWR的Top SQLs部分'
prompt
prompt



prompt '*********************************************************************'
prompt '************************其他数据库监控情况***************************'
prompt '*********************************************************************'
prompt
prompt '1,数据文件的I/O情况:'
select substr(a.file#,1,2) "#", substr(a.name,1,20) "name",a.status,a.bytes,b.phyrds,b.phywrts from v$datafile a,v$filestat b where a.file#=b.file# order by phywrts;

prompt '2,DB  Block  Buffer  Hit Ratio(Hit Ratio>95为正常,理想值为100)'
select a.value "physical reads",b.value "consistent gets",c.value "db block gets",100-a.value/(b.value+c.value)*100 "Hit Ratio" from v$sysstat a,v$sysstat b,v$sysstat c where a.name='physical reads' and b.name='consistent gets' and c.name='db block gets';

prompt '3,Shared  Pool  Size  Execution Hit  Ratio(Lib hit Ratio>95 为正常,理想值为100)'
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING",(1-sum(reloads)/sum(pins))*100 "Lib hit Ratio"  FROM V$LIBRARYCACHE;

prompt '4,Shared  Pool  Size   Dictionary Hit Ratio (DIC hit Ratio>95 为正常,理想值为100)'
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES",(1-sum(getmisses)/(sum(gets)+sum(getmisses)))*100 "DIC hit Ratio"  FROM V$ROWCACHE;

prompt '5,监控 SGA 中重做日志缓存区的命中率:ratio1<1,ratio2<1为正常,理想值为0 '
prompt '(若ratio1>1或ratio2'
prompt '>1时,应考虑采取措施减少latch的冲突。大多数的redo log buffer latch冲突是在多个CPU情况下,'
prompt '为减少redo allocation latch的冲突,应减少单个进程持有latch的时间,这可以通过减小初始参数'
prompt 'LOG_SMALL_ENTRY_MAX_SIZE以减小redo entry的数目和大小来实现。如果观察到有redo copy latch冲突,'
prompt '可以通过增大LOG_SIMULTANEOUS_COPIES 初始参数来加大latch数目,其缺省值为CPU数目,最大可增大到CPU数目的两倍。)'
col name format a20;
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'); 


prompt '6, 回滚段空间配置'
col segment_name format a20;
select rb.segment_name,rb.INITIAL_EXTENT,rb.NEXT_EXTENT,rb.MIN_EXTENTS,rb.MAX_EXTENTS,rs.optsize,rb.tablespace_name,rs.status,ds.bytes/1024/1024 from dba_rollback_segs rb,v$rollstat rs,dba_segments ds where rb.segment_id=rs.usn and rb.segment_name=ds.segment_name;

prompt '7, 等待事件最多的前五位等待事件'
col event format a30;
select * from (select * from v$system_event where event not like 'SQL%' order by total_waits desc) where rownum<=5;


prompt '8, 磁盘排序严重的SQL'
select sess.username, sql.sql_text, sort1.blocks  from v$session sess, v$sqlarea sql, v$sort_usage sort1 where sess.serial# = sort1.session_num  and sort1.sqladdr = sql.address  and sort1.sqlhash = sql.hash_value and sort1.blocks > 200;

prompt '9,长时间运行的对象'
select target,opname,count(*) from v$session_longops group by target,opname;

prompt '============resume==========='
--select * from v$sysstat;
--select * from v$pgastat;
--select * from v$sesstat;
--select * from v$filestat;
--select * from dba_tablespaces;
--select * from v$waitstat;
--select * from v$osstat;
--select * from v$tempstat;
--select * from v$rollstat;
--select * from v$undostat;
--select * from v$sgastat;
--select * from v$mystat;
prompt '===========看是否有大量 latch free 等待==========='
select sid,event,p1,p1raw from v$session_wait;
prompt '===========如果很多shared pool和library cache,那应该是解析过度造成的。==========='
select addr,latch#,name,gets,spin_gets from v$latch order by spin_gets;
prompt '===========看VERSION_COUNT的数量==========='
select sql_text,VERSION_COUNT,HASH_VALUE,loaded_versions, users_opening ,address,         users_executing,INVALIDATIONS,PARSE_CALLS,OPTIMIZER_MODE,PARSING_USER_ID,PARSING_SCHEMA_ID,ADDRESS
      from v$sqlarea where version_count >1000;
      
prompt 'Look for SQL stmts with more than one parent cursor'
select sql_id, count(*) n
      from v$sql
      where child_number = 0
      group by sql_id
      having count(*) > 1 ; 
prompt 'to see why the cursors are not sharing'      
select *
      from v$sql_shared_cursor
      where sql_id in
      (
      select sql_id from v$sql where child_number = 0
      group by sql_id having count(*) > 1
      ) ;     

show parameter "_sqlexec_progression_cost"
show parameter compatible            
show parameter TIMED_STATISTICS
show parameter SQL_TRACE 
show parameter CURSOR_SHARING
show parameter session_cached_cursors
Trackback

no comment untill now

Add your comment now

切换到手机版