set verify off feedback off serveroutput on size 1000000 pages
10000
set trimout on trimspool on linesize 2000
set heading on
col OBJECT_NAME for a30
col event for a30
col script for a100
col aux_name for a20
col member for a50
col 是否安装 for a30
col segment_name for a30
col segment_type for a15
col owner for a15
col 用户名 for a15
col username for a15
col 表空间名 for a10
col default_tablespace for a15
col temporary_tablespace for a15
col file_name for a50
col 约束类型 for a10
col 自动扩展 for a8
col id for 99
col account_status for a20
col group# for 99
col archived for a8
col PARAMETER for a40
col 选件名称 for a40
COL 参数名称 FOR A30
col 参数值 for a55
spool D:\whx123abc@163.com.txt
PROMPT
PROMPT 一、主机信息
--Windows NTsysteminfo
--host top
--host vmstat 4 5
--host iostat
-- HP如果装了glance建议使用glance，IBM如果装了svmon建议使用svmon
--主机名
--$ uname -a
--主机型号
--HP# model
--内核版本
--HP# getconf KERNEL_BITS
--AIX# /usr/sbin/bootinfo -K
--Solaris# isainfo -kv
--操作系统版本
--AIX$ oslevel -r
--HP, Linux, Solaris$ uname -a
--Tru64$ /usr/sbin/sizer -v
--磁盘信息
--HP$ bdf
--others$ df -k
--物理内存
--AIX$ /usr/sbin/lsattr -E -l sys0 -a realmem, lsattr -El
mem0
--Linux$ /usr/sbin/dmesg | grep "Physical:"
--HP$ grep MemTotal /proc/meminfo
--Solaris$ /usr/sbin/prtconf | grep "Memory size"
--Tru64$ /bin/vmstat -P | grep "Total Physical Memory"
--内存利用率
--$ vmstat 20 5
--交换区大小
--AIX $ /usr/sbin/lsps -a
--HP$ /usr/sbin/swapinfo -a, swapinfo -m
--Linux, Tru64$ /sbin/swapon -s
--Solaris$ /usr/sbin/swap -1
--CPU 信息
--Solaris# psrinfo -v|grep "Status of processor"|wc -l
--AIX# lsdev -Cc processor, lsattr -El proc0
--其他# lsdev -C|grep Process|wc -l
--CPU利用率
--Solaris $ sar -u -M 20 5
--Others$ sar -u 20 5
--网络情况
--AIX$ netstat -in, netstat -rn
create or replace procedure whx123_print_table( p_query in
varchar2 )
AUTHID CURRENT_USER
is
l_theCursor  integer default dbms_sql.open_cursor;
l_columnValue  varchar2(4000);
l_status
integer;
l_descTbl
dbms_sql.desc_tab;
l_colCnt
number;
begin
execute
immediate
'alter
session set
nls_date_format=''yyyy-mm-dd
hh24:mi:ss'' ';
dbms_sql.parse(  l_theCursor,
p_query, dbms_sql.native );
dbms_sql.describe_columns
(
l_theCursor, l_colCnt, l_descTbl );
for i in
1 .. l_colCnt loop
dbms_sql.define_column
(l_theCursor, i,
l_columnValue, 4000);
end
loop;
l_status
:= dbms_sql.execute(l_theCursor);
while (
dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
for i in 1 .. l_colCnt
loop
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ': '
||
l_columnValue );
end loop;
dbms_output.put_line(
'-----------------' );
end
loop;
execute
immediate
'alter session set
nls_date_format=''dd-MON-rr'' ';
exception
when
others then
execute immediate
'alter
session set nls_date_format=''dd-MON-rr'' ';
raise;
end;
/
PROMPT
PROMPT 二、数据库版本信息
set heading off
select * from v$version;
set heading on
PROMPT
PROMPT 三、数据库信息
exec whx123_print_table('select * from v$database')
PROMPT
PROMPT 四、数据库实例信息
exec whx123_print_table('select * from v$instance')
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'
;
PROMPT
PROMPT 五、数据库选件信息
SELECT PARAMETER "选件名称",VALUE "是否安装"  FROM
V$OPTION ;
col 参数值 for a55
PROMPT
PROMPT 六、数据库非缺省初始化参数
SELECT name "参数名称",value "参数值" FROM V$PARAMETER where
ISDEFAULT='FALSE' order by name;
col value for a30
COL NAME FOR A60
PROMPT
PROMPT 七、控制文件
SELECT * FROM V$controlfile;
PROMPT
PROMPT 八、日志组信息
col member for a30
select
a.thread#,
a.group#,
b.member,
b.status,
a.bytes/1024/1024
from v$Log a,
v$logfile b
where
a.group#=b.group#
order by thread#
/
PROMPT
PROMPT 九、数据文件
SELECT FILE_ID ID,FILE_NAME,TABLESPACE_NAME
"表空间名",BYTES,autoextensible "自动扩展"
FROM dba_data_files order by
tablespace_name;
PROMPT
PROMPT 十、临时文件
SELECT FILE_ID ID,FILE_NAME,TABLESPACE_NAME
"表空间名",BYTES,autoextensible "自动扩展"
FROM dba_temp_files;
PROMPT
PROMPT 十一、表空间信息
set heading on
col "表空间名" for a20
col "表空间使用率%" heading '使用率' for 999
col "表空间总量" heading '总量' for 999999999999.00
col "剩余空间大小M" heading '剩余' for 999999999999.00
col "管理模式" heading '管理模式' for a10
select
a.tablespace_name "表空间名",a.total "表空间总量",
b.free "剩余空间大小M" ,
ceil((1-b.free/a.total)*100)
"表空间使用率%",
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=b.tablespace_name
and a.tablespace_name=c.tablespace_name
order by
(1-b.free/a.total)*100 desc
/
prompt 十二、表空间空闲块情况
prompt -----查找到很高的值，使用合并命令仍有大量空闲块时，考虑转化为本地管理
select tablespace_name
,count(*) cnt
from
dba_free_space
group by tablespace_name
order by cnt desc
/
PROMPT 十三、回滚段空间配置
col segment_name heading 'Segment' for a8
col tablespace_name heading 'TBS' for a5
col INITIAL_EXTENT heading 'Initial' for 9999999999
col NEXT_EXTENT  heading
'Next'  for 9999999999
col Min_extents  heading
'Min'  for 99
col Max_extentsheading 'Max'for 99999
col ds.bytes/1024/1024 heading 'Bytes(M)' for 9999.00
col status for a6
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
PROMPT 十四、NLS参数设置
SELECT * FROM NLS_Database_Parameters;
PROMPT
PROMPT 十五、数据库用户安全性
set pages 100 lines 132
column  ACCOUNT_STATUS heading 'status' format
a5
column usernameheading 'User'format a10
column default_tablespaceheading 'Default'format a12
column temporary_tablespaceheading 'Temporary'format a12
column granted_roleheading 'Granted Roles'format a27
col  profile for a8
clear breaks
break on username on account_status on default_tablespace on
temporary_tablespace on profile
select username,
ACCOUNT_STATUS,
default_tablespace,
temporary_tablespace,
granted_role
from dba_users u,dba_role_privs r
where u.username = r.grantee
order by username
/
PROMPT 十六、最近三个月日志归档频繁度
select to_char(first_time,'yyyy-mm-dd'),thread#,count(*)
from v$log_history
where first_time>sysdate-90
group by to_char(first_time,'yyyy-mm-dd'),thread#
having count(*)>20
order by thread#
/
PROMPT
PROMPT 十七、失效对象
SELECT OWNER "用户名",OBJECT_NAME,OBJECT_TYPE,CREATED,LAST_DDL_TIME
FROM DBA_OBJECTS WHERE STATUS = 'INVALID'
ORDER BY OWNER,OBJECT_NAME;
PROMPT
PROMPT 十八、禁用的触发器
SELECT owner "用户名",trigger_name,status FROM dba_triggers where
status = 'DISABLED';
PROMPT
PROMPT 十九、禁用的约束
SELECT owner
"用户名",constraint_name,decode(constraint_type,'P','主键','F','外键',constraint_type)
"约束类型",status
FROM dba_constraints where
status = 'DISABLED';
PROMPT
PROMPT 二十、无效的索引
SELECT owner "用户名",index_name,status FROM dba_indexes where
status = 'UNUSABLE';
prompt 二十一、表和索引在同一个空间的对象
col owner for a12
col tname for a26
col iname for a26
col tbsname for a15
clear breaks
break on owner on tbsname on tname
select a.owner,
a.tablespace_name tbsname ,
a.table_name tname,
b.index_name iname
from
dba_tables a
,dba_indexes b
where
a.tablespace_name=b.tablespace_name
and
b.table_name=a.table_name
and
a.owner=b.owner
and
b.owner NOT in ('SYS','SYSTEM')
order  by owner
/
PROMPT
PROMPT 二十二、需要绑定变量的SQL(自数据库启动以来)
drop table t1 ;
create table whx123_t1 as select sql_text from v$sqlarea;
alter table whx123_t1 add sql_text_wo_constants
varchar2(1000);
create or replace function
whx123_remove_constants( p_query in varchar2 ) return
varchar2
as
l_query
long;
l_char
varchar2(2);
l_in_quotes boolean default FALSE;
begin
for i in
1 .. length( p_query )
loop
l_char :=
substr(p_query,i,1);
if ( l_char = '''' and
l_in_quotes )
then
l_in_quotes := FALSE;
elsif ( l_char = '''' and NOT
l_in_quotes )
then
l_in_quotes := TRUE;
l_query := l_query || '''#';
end if;
if ( NOT l_in_quotes )
then
l_query := l_query || l_char;
end if;
end
loop;
l_query
:= translate( l_query, '0123456789', '@@@@@@@@@@' );
for i in
0 .. 8 loop
l_query := replace( l_query,
lpad('@',10-i,'@'), '@' );
l_query := replace( l_query,
lpad(' ',10-i,' '), ' ' );
end
loop;
return
upper(l_query);
end;
/
update whx123_t1 set sql_text_wo_constants =
whx123_remove_constants(sql_text);
commit;
COL sql_text_wo_constants FOR A60
COL sql_text FOR A60
select count(*) "硬解析次数",sql_text_wo_constants
from whx123_t1
group by sql_text_wo_constants
having count(*) > 100
order by 1 desc;
PROMPT 二十三 与应用相关
PROMPT
PROMPT 1、最消耗内存的十个SQL
select * from (
select
trunc(t.BUFFER_GETS/t.EXECUTIONS) "buffer/exes",
t.EXECUTIONS "执行次数",t.SQL_TEXT
from
v$sql t  where t.EXECUTIONS > 0
order by 1 desc )
where rownum <= 10;
PROMPT
PROMPT 2、TOP 5 等待
col event for a28
select EVENT,TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED,
AVERAGE_WAIT  from (
select * from v$system_event where event not like
'%rdbms%' and event not like '%message%' and
event not like 'SQL*Net%' order by total_waits desc
)
where rownum <=5 ;
PROMPT  3、磁盘读最多得SQL
prompt disk_reads一般要小于300才好，这里这样的语句太多了所以设置的高
select
st.sql_text
from
v$sql s,
v$sqlarea st
where
s.address=st.address
and
s.hash_value=st.hash_value
and s.disk_reads >
300
order by s.disk_reads
asc
/
PROMPT  4、磁盘排序最严重的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
order by sort1.blocks asc
/
PROMPT
PROMPT 5、SGA命中率
PROMPT cache hit要高于90%--library要高于95%--rowcache要高于85%
select * from
(SELECT 1 - (phy.value-phyd.value)/( (cur.value +
con.value-phyd.value)) "数据缓冲区命中率"
FROM v$sysstat cur,
v$sysstat con, v$sysstat phy,v$sysstat phyd
WHERE cur.name = 'db block
gets' AND con.name = 'consistent gets'
AND
phy.name = 'physical reads' and phyd.NAME = 'physical reads direct'
),
(select sum(pinhits)/sum(pins) "SQL共享池命中率" from v$librarycache)
,
(select 1-SUM(GETMISSES)/SUM(GETS) "数据字典命中率" from
V$ROWCACHE)
;
prompt 6、内存中排序
select a.value "Disk Sorts",
b.value "Memory Sorts",
round((100 * b.value) /
decode((a.value + b.value), 0, 1, (a.value +
b.value)),
2) "Pct Memory Sorts"
from v$sysstat a, v$sysstat
b
where a.name = 'sorts (disk)'
and b.name = 'sorts
(memory)'
/
PROMPT
PROMPT 二十四、数据库大小估算
select nvl(t.owner,'合计：') "用户名",
to_char(sum(bytes),'999,999,999,999,999')
"占用的空间(字节)"
from dba_segments
t
group by rollup(t.owner)
order by 2  ;
PROMPT 如果使用逻辑导出(exp备份)，其大小约等于合计大小。
PROMPT
PROMPT 二十五、意见和建议
COL 下列文件建议取消自动扩展 FOR A60
select file_name "下列文件建议取消自动扩展",BYTES "文件大小"
from
(SELECT file_name,BYTES,autoextensible FROM
dba_data_files
UNION ALL
SELECT file_name,BYTES,autoextensible FROM DBA_TEMP_FILES)
t where
ORDER BY BYTES DESC
;
set heading off
select '参数timed_statistics建议设置为true。当前值为FALSE。' from v$parameter
where name = 'timed_statistics' and value = 'FALSE';
select '参数optimizer_index_caching建议设置为90。当前值为0。' from
v$parameter where name = 'optimizer_index_caching' and value =
'0';
select '参数optimizer_index_cost_adj建议设置为10～50。当前值为100。' from
v$parameter where name = 'optimizer_index_cost_adj' and value =
'100';
select '参数db_file_multiblock_read_count建议设置小一些。当前值为：'||value
from v$parameter
where name = 'db_file_multiblock_read_count'
and value > '16';
select '在Oracle9i中，建议PGA的管理方式为AUTO。' from v$parameter
where name = 'workarea_size_policy' and value <>
'AUTO';
select '在Oracle9i中，建议UNDO的管理方式为AUTO' from v$parameter
where name = 'undo_management' and value <> 'AUTO';
set heading on
select username "默认表空间不应为SYSTEM的用户" from dba_users
where default_tablespace = 'SYSTEM' and
username not in ('SYS', 'OUTLN','SYSTEM');
select username "临时表空间不应为SYSTEM的用户" from dba_users where
temporary_tablespace = 'SYSTEM';
select username "临时表空间不应为PERFANET的用户" from dba_users where
temporary_tablespace in
(select tablespace_name  from
dba_tablespaces where contents = 'PERMANENT' );
PROMPT
PROMPT 未使用的索引(8i及以下使用，需sys用户运行)
select distinct b.owner, b.segment_name
from x$bh a, dba_extents
b
where
b.file_id=a.dbarfil
and a.dbablk between
b.block_id
and
b.block_id+blocks-1
and
segment_type='INDEX'
and b.owner not in
('SYS','SYSTEM')
and not exists (select
instr(BANNER,'9i') from v$version WHERE ROWNUM<=1);
PROMPT
PROMPT 系统表空间中非SYS的对象
select OWNER,SEGMENT_NAME,SEGMENT_TYPE--,
--decode(segment_type,
--'TABLE','alter table
'||OWNER||'.'||SEGMENT_NAME||' MOVE TABLESPACE
&'||'TABLESPACE;' ,
--'INDEX','alter index
'||OWNER||'.'||SEGMENT_NAME||' REBUILD TABLESPACE
&'||'TABLESPACE NOLOGGING;',
--null) SCRIPT
from dba_segments t
where t.tablespace_name = 'SYSTEM'
AND  OWNER
NOT IN ('SYS','OUTLN','SYSTEM','WMSYS');
drop function whx123_remove_constants ;
drop table whx123_t1;
drop procedure whx123_print_table;
PROMPT
PROMPT
spool off
--ed &report_name