数据库当前状况报告

数据库当前状况报告，生成orcl_info_report简化版.txt文件

[ ref: 数据库当前状况脚本 ]

/*
功 能: 数据库当前状况报告，生成orcl_info_report简化版.txt文件。
使用方法: sqlplus /nolog @orcl_info_简化版.sql
Created By xiangshubo on 2003-10-20
email:[e-mail]
Modified By xiangshubo on 2004-04-12
Modified By xiangshubo on 2004-06-22
Modified By xiangshubo on 2004-06-30
Modified By xiangshubo on 2005-06-28
Modified By xiangshubo on 2005-08-08
Modified By xiangshubo on 2005-11-07
Modified By xiangshubo on 2006-06-22简化版
*/

accept dbcon prompt '数据库连接：'
connect &dbcon;
pause 继续吗？

set echo off feed off head off verify off
set trimspool on
set long 500
set linesize 200 pagesize 9999
set serveroutput on size 100000
set numwidth 10

spool orcl_info_report简化版.txt
select 'host='||utl_inaddr.GET_HOST_NAME()||',IP='||utl_inaddr.GET_HOST_ADDRESS()||',DB='||name||
chr(10)||' 数据库当前状况报告' from v$database;

set head on
select to_char(sysdate,'yyyy-mm-dd HH24:MI:SS')||' Reported by orcl_info.sql'
||chr(10)||'(Created By XSB,http://xsb.itpub.net)'
"报告创建时间：" from dual;

PROMPT
PROMPT *********************************************************
PROMPT ***数据库概况信息： ***
PROMPT *********************************************************
PROMPT
PROMPT 数据库产品版本：
column banner format a75 trunc
select * from v$version;
PROMPT
prompt 当前实例状态信息：
column instance_name,startup_time,archiver format a20 trunc
column host_name format a20 trunc
column instance_number format 99
select a.instance_name,to_char(a.startup_time,'YYYY-MM-DD HH24:MI:SS') startup_time,a.archiver,a.host_name,a.instance_number
from v$instance a;

PROMPT
prompt 当前数据库状态信息：
rem for 8i or after
select name,to_char(created,'yyyy-mm-dd hh24:mi:ss') created,log_mode ,open_mode from v$database ;
rem for 8 or before
rem ++ select name,to_char(created,'yyyy-mm-dd hh24:mi:ss') created,log_mode from v$database ;

PROMPT
prompt 连接数信息：
select * from v$license;

PROMPT
prompt 当前DB_job信息：
column what format a20
column LOG_USER format a8
column SCHEMA_USER format a8
column INTERVAL format a15
SELECT to_char(A.JOB,'fm9999') job, A.WHAT, A.LOG_USER, A.SCHEMA_USER, to_char(A.LAST_DATE,'yymmdd hh24:mi') LAST_DATE,
to_char(A.THIS_DATE,'yymmdd hh24:mi') THIS_DATE, to_char(A.NEXT_DATE,'yymmdd hh24:mi') NEXT_DATE, A.BROKEN, A.INTERVAL, to_char(A.FAILURES,'fm99999') fails
FROM DBA_JOBS A;

PROMPT
PROMPT 数据库中等待事件(前10位)的状况：
column event format a30
select * from (select * from V$SYSTEM_EVENT order by 4 desc) where rownum<=10;
prompt

PROMPT 表空间利用率：
SELECT d.tablespace_name, SUM(d.total) total, SUM(d.used) used, round(SUM(d.used) / SUM(d.total) * 100, 2) used_pct --,
--SUM(d.free) free,
--round(SUM(d.free) / SUM(d.total) * 100, 2) free_pct
FROM (SELECT a.file_id file#,
a.file_name,
a.total,
(a.total - nvl(f.free, 0)) used,
nvl(f.free, 0) free,
round((a.total - nvl(f.free, 0)) / a.total * 100, 1) "used%",
round(nvl(f.free, 0) / a.total * 100, 1) "Free%",
a.tablespace_name,
a.autoextensible,
a.status,
a.extent_management
FROM (SELECT tablespace_name, file_name, file_id, round(bytes / 1024 / 1024) total, autoextensible, dba_data_files.status, extent_management
FROM dba_data_files
JOIN dba_tablespaces
USING (tablespace_name)) a,
(SELECT file_id, round(SUM(bytes) / 1024 / 1024) free
FROM dba_free_space
GROUP BY file_id) f
WHERE a.file_id = f.file_id(+)
ORDER BY a.total DESC) d
GROUP BY d.tablespace_name
ORDER BY 4 DESC;

PROMPT
PROMPT 数据文件大小(M)及空间利用率：
column file_name format a20
column tablespace_name format a15
set numwidth 8
select a.file_id file#,a.file_name,
a.total,(a.total-nvl(f.free,0)) used,nvl(f.free,0) free,
round((a.total-nvl(f.free,0))/a.total*100,1) "used%",
round(nvl(f.free,0)/a.total*100,1) "Free%" ,
a.tablespace_name,a.autoextensible,a.status,a.extent_management
from (select tablespace_name,file_name,file_id, round(bytes/1024/1024) total,autoextensible,dba_data_files.status,extent_management
from dba_data_files join dba_tablespaces using (tablespace_name)) a,
(select file_id, round(sum(bytes)/1024/1024) free from dba_free_space group by file_id) f
WHERE a.file_id = f.file_id(+)
order by a.total desc;

PROMPT
PROMPT 临时文件：
column name format a60
SELECT FILE#,NAME,ROUND(BYTES/1024/1024,3) SIZE_M FROM V$TEMPFILE;

PROMPT
PROMPT 控制文件：
select name from v$controlfile;
PROMPT
PROMPT 联机日志文件：
column member format a50
SELECT A.GROUP#, B.MEMBER, A.STATUS, ROUND(A.BYTES / 1024 / 1024) SIZE_M
FROM V$LOG A, V$LOGFILE B
WHERE A.GROUP# = B.GROUP#;

PROMPT
PROMPT 数据库用户及其默认表空间、临时表空间：
column username format a30 trunc
column default_tablespace format a30 trunc
column temporary_tablespace format a30 trunc
select a.username,a.default_tablespace,a.temporary_tablespace
from dba_users a order by a.default_tablespace;

PROMPT
PROMPT 占用空间超过200M的实体(已rollup合计)：
column OWNER format a20 trunc
column SEGMENT_NAME format a25 trunc
column partition_name format a25 trunc
column SEGMENT_TYPE format a25 trunc
SELECT ROUND(SUM(SEG.BYTES / 1024 / 1024), 2) SPACE_M,SEG.OWNER,SEG.SEGMENT_NAME,SEG.partition_name,SEG.SEGMENT_TYPE,SEG.HEADER_FILE
FROM DBA_SEGMENTS SEG
GROUP BY rollup(SEG.OWNER, SEG.SEGMENT_NAME, SEG.partition_name,SEG.SEGMENT_TYPE, SEG.HEADER_FILE )
HAVING SUM(SEG.BYTES) > 1024 * 1024 * 200
ORDER BY 1 DESC ;

PROMPT
PROMPT 请关注以下的SQL语句（set autot trace 观看其执行路径）：
PROMPT
SET HEAD ON
column sql_text format a40
PROMPT IO较多的SQL语句：
select * from (SELECT SQL_TEXT, EXECUTIONS , DISK_READS, BUFFER_GETS,ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run FROM V$SQLAREA
WHERE EXECUTIONS>0 AND BUFFER_GETS > 0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.6
ORDER BY DISK_READS/decode(EXECUTIONS,0,1,EXECUTIONS) desc)
where rownum<=3;
PROMPT 时间较长的SQL语句：
SELECT * FROM (SELECT sql_text, sorts, fetches, executions, disk_reads, buffer_gets, rows_processed, elapsed_time
FROM v$sqlarea t
ORDER BY elapsed_time/decode(EXECUTIONS,0,1,EXECUTIONS) DESC) WHERE ROWNUM<=3;
PROMPT 记录数较多的SQL语句：
SELECT * FROM (SELECT sql_text, sorts, fetches, executions, disk_reads, buffer_gets, rows_processed, elapsed_time
FROM v$sqlarea t
ORDER BY fetches/decode(EXECUTIONS,0,1,EXECUTIONS) DESC) WHERE ROWNUM<=3;
PROMPT 运行次数较多的SQL语句：
SELECT * FROM (SELECT sql_text, sorts, fetches, executions, disk_reads, buffer_gets, rows_processed, elapsed_time
FROM v$sqlarea t
ORDER BY executions DESC) WHERE ROWNUM<=3;

PROMPT
prompt 当前Session信息：
column username format a20 trunc
column osuser format a20 trunc
column machine format a20
column program format a20
set numwidth 8
SELECT A.MACHINE,a.SID,a.USERNAME,a.STATUS,a.OSUSER,a.PROGRAM,a.LOGON_TIME FROM V$SESSION a
WHERE a.TYPE='USER' ORDER BY A.MACHINE;

prompt
prompt 关于数据库内存及命中率信息
prompt SGA空间分配：
set numwidth 10
show sga
prompt
prompt 关于Shared_Pool:
prompt 1.V$LIBRARYCACHE gives the pins/reload ratio for the library cache. The GETHITRATIO
prompt column should be .95 or higher.
select round(gethitratio*100,2) gethitration from v$librarycache where namespace = 'SQL AREA';

prompt 2. The GETMISSES to GETS columns in V$ROWCACHE should have a ratio less than 15% .
select round(sum(GETMISSES)/sum(GETS)*100,2) getmissration from V$ROWCACHE ;
prompt
prompt 判断shared_pool是否过大
select pool,name,round(bytes/1024/1024,2) size_M
from v$sgastat where name = 'free memory';

prompt
prompt 关于DB_BUFFERS :
prompt 3.The buffer cache hit ratio should be 90% or higher.
prompt Hit Ratio = 1 – (physical reads/(db block gets + consistent gets))
SELECT round(1 - (phy.value / (cur.value + con.value)),4)*100 "CACHEHITRATIO"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets' AND con.name = 'consistent gets' AND phy.name = 'physical reads';
prompt Buffer Hit足够大(>95%)表示命中率较高,否则可以调整数据缓冲区的大小

prompt
prompt About Sort_Area_Size:
SELECT a.VALUE "sorts(memory)",b.VALUE "sorts(disk)" FROM v$sysstat a,v$sysstat b
WHERE a.NAME='sorts (memory)' and b.NAME='sorts (disk)';
prompt In-memory Sort数字大(>95%)表示大部分数据在内存中进行排序,否则调整sort_area_size的值，或pga_aggregate_target的值

prompt
prompt About PGA_Size:
SELECT NAME,round(VALUE/1024/1024,2) size_M FROM v$pgastat;
prompt

PROMPT
prompt 关注的初始化参数:
column name format a40 trunc
column value format a70
select name, value from v$parameter where name in ('aq_tm_processes','bitmap_merge_area_size',
'compatible','cpu_count','create_bitmap_area_size','cursor_sharing','db_cache_advice','db_cache_size',
'db_keep_cache_size','db_recycle_cache_size','dispatchers','global_names','hash_area_size',
'hash_join_enabled','java_pool_size','large_pool_size','log_buffer','optimizer_mode',
'pga_aggregate_target','processes','query_rewrite_enabled','sga_max_size','shared_pool_size',
'shared_servers','sort_area_size','timed_statistics','workarea_size_policy');

PROMPT
prompt 当前使用的数据库初始化参数:
select to_char(rownum,'fm999') rn,a.* from (select name, value from v$parameter where value is not null order by name) a;
set head off
SELECT '注意' || A.VALUE || '目录下的alert_' || B.VALUE || '.log文件'
FROM V$PARAMETER A, V$PARAMETER B
WHERE A.NAME = 'background_dump_dest' AND B.NAME = 'db_name';
prompt ************************************
prompt END
prompt ************************************

SPOOL OFF
exit