SQL>@/home/newvers/product/92/rdbms/admin/spauto
--四.使statspack自动收集系统状况
alert system set job_queue_processes = 10;
alert system set job_queue_processes = 10 scope=both;
alter system set Timed_statistics=true;
[orapaid@bj37 admin]$ cat spauto.sql
Rem
Rem $Header: spauto.sql 16-feb-00.16:49:37 cdialeri Exp $
Rem
Rem spauto.sql
Rem
Rem Copyright (c) Oracle Corporation 1999, 2000. All Rights Reserved.
Rem
Rem NAME
Rem spauto.sql
Rem
Rem DESCRIPTION
Rem SQL*PLUS command file to automate the collection of STATPACK
Rem statistics.
Rem
Rem NOTES
Rem Should be run as the STATSPACK owner, PERFSTAT.
Rem Requires job_queue_processes init.ora parameter to be
Rem set to a number >0 before automatic statistics gathering
Rem will run.
Rem
Rem MODIFIED (MM/DD/YY)
Rem cdialeri 02/16/00 - 1191805
Rem cdialeri 12/06/99 - 1059172, 1103031
Rem cdialeri 08/13/99 - Created
Rem
spool spauto.lis
--
-- Schedule a snapshot to be run on this instance every hour, on the hour
variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
commit;
end;
/
prompt
prompt Job number for automated statistics collection for this instance
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt Note that this job number is needed when modifying or removing
prompt the job:
print jobno
prompt
prompt Job queue process
prompt ~~~~~~~~~~~~~~~~~
prompt Below is the current setting of the job_queue_processes init.ora
prompt parameter - the value for this parameter must be greater
prompt than 0 to use automatic statistics gathering:
show parameter job_queue_processes
prompt
prompt
prompt Next scheduled run
prompt ~~~~~~~~~~~~~~~~~~
prompt The next scheduled run for this job is:
select job, next_date, next_sec
from user_jobs
where job = :jobno;
spool off;
--五.生成分析报告
调用@/home/orapaid/product/92/rdbms/admin/spreport生成
此过程中要输入开始快照 和 终止快照 编号
--六 移除定时任务
SQL>execute dbms_job.remove('job_id');
--七 删除历史数据
删除stats$snapshot表中数据 ,其他表中的数据会相应的级连删除
oracle提供了用于 truncate 这些统计信息表的 脚本
[orapaid@bj37 admin]$ cat sptrunc.sql
Rem
Rem $Header: sptrunc.sql 19-feb-2002.11:36:28 vbarrier Exp $
Rem
Rem sptrunc.sql
Rem
Rem Copyright (c) 2000, 2002, Oracle Corporation. All rights reserved.
Rem
Rem NAME
Rem sptrunc.sql - STATSPACK - Truncate tables
Rem
Rem DESCRIPTION
Rem Truncates data in Statspack tables
Rem
Rem NOTES
Rem Should be run as STATSPACK user, PERFSTAT.
Rem
Rem The following tables should NOT be truncated
Rem STATS$LEVEL_DESCRIPTION
Rem STATS$IDLE_EVENT
Rem STATS$STATSPACK_PARAMETER
Rem
Rem MODIFIED (MM/DD/YY)
Rem vbarrier 03/05/02 - Segment Statistics
Rem cdialeri 04/13/01 - 9.0
Rem cdialeri 09/12/00 - sp_1404195
Rem cdialeri 04/11/00 - 1261813
Rem cdialeri 03/15/00 - Created
Rem
undefine anystring
set showmode off echo off;
whenever sqlerror exit;
spool sptrunc.lis
/* ------------------------------------------------------------------------- */
prompt
prompt Warning
prompt ~~~~~~~
prompt Running sptrunc.sql removes ALL data from Statspack tables. You may
prompt wish to export the data before continuing.
prompt
prompt
prompt About to Truncate Statspack Tables
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt If you would like to continue, press
prompt
prompt
prompt &return Entered - starting truncate operation
truncate table STATS$FILESTATXS;
truncate table STATS$TEMPSTATXS;
truncate table STATS$LATCH;
truncate table STATS$LATCH_CHILDREN;
truncate table STATS$LATCH_MISSES_SUMMARY;
truncate table STATS$LATCH_PARENT;
truncate table STATS$LIBRARYCACHE;
truncate table STATS$BUFFER_POOL_STATISTICS;
truncate table STATS$ROLLSTAT;
truncate table STATS$ROWCACHE_SUMMARY;
truncate table STATS$SGA;
truncate table STATS$SGASTAT;
truncate table STATS$SYSSTAT;
truncate table STATS$SESSTAT;
truncate table STATS$SYSTEM_EVENT;
truncate table STATS$SESSION_EVENT;
truncate table STATS$BG_EVENT_SUMMARY;
truncate table STATS$WAITSTAT;
truncate table STATS$ENQUEUE_STAT;
truncate table STATS$SQL_SUMMARY;
truncate table STATS$SQL_STATISTICS;
truncate table STATS$SQLTEXT;
truncate table STATS$PARAMETER;
truncate table STATS$RESOURCE_LIMIT;
truncate table STATS$DLM_MISC;
truncate table STATS$UNDOSTAT;
truncate table STATS$SQL_PLAN;
truncate table STATS$SQL_PLAN_USAGE;
truncate table STATS$SEG_STAT;
truncate table STATS$SEG_STAT_OBJ;
truncate table STATS$DB_CACHE_ADVICE;
truncate table STATS$PGASTAT;
truncate table STATS$INSTANCE_RECOVERY;
delete from STATS$SNAPSHOT;
delete from STATS$DATABASE_INSTANCE;
commit;
Rem This is required to allow further snapshots to work without
Rem recreating package or restarting the instance
alter package statspack compile;
prompt
prompt Truncate operation complete
prompt
/* ------------------------------------------------------------------------- */
spool off;
whenever sqlerror continue;
set echo on;
[orapaid@bj37 admin]$
--九调整statspack的收集门限
SQL>execute statspack.snap(i_snap_level=>0 ,i_modify_parameter=>'true');
SQL>execute statspack.snap(i_snap_level=>10);
SQL>execute statspack.snap(i_snap_level=>5);
--通过下列语句修改门限的默认值
SQL>execute statspack.modify_statspack_parameter(i_buffer_gets_th=>100000,i_disk_reads_th=>100000);
--10 整理分析报告
--11用shell 生成性能分析报告(也可以手工生成)
ORACLE_SID=$ORACLE_SID
EXPORT ORACLE_SID
ORACLE_HOME='cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
EXPORT PATH
echo "please enter the number of seconds between snapshots."
read elapsed
$ORACLE_HOME/bin/sqlplus -s perfstat/perfstat
sleep $elapsed
$ORACLE_HOME/bin/sqlplus -s perfstat/perfstat
select name,snap_id,to_char(start_time,'yyyymmdd:hh24:mi:ss')
from
stats\$snapshot ,v\$database
where snap_id>(select max(snap_id)-2 from stats\$snapshot);
--stats$sql_statistics 视图
--这个视图用于统计 系统SQL的总开销以及SQL被重用的百分比特别有用
set lines 80;
set pages 999;
column mydate heading 'Yr.Mo Dy Hr' format a16;
column c1 heading 'Tot SQL' format 999,999,999;
column c2 heading 'SINGLE USE SQL' format 999,999;
column c3 heading 'Percent re_used SQL' format 999,999;
column c4 heading 'TOTAL SQL RAM' format 999,999,999;
break on mydate skip 2;
select to_char(snap_time,'yyyy-mm-dd hh24) mydate,
total_sql c1,
single_use_sql c2,
(single_use_sql/total_sql)*100 c3,
total_sql_mem c4
from stats$sql_statistics sq,
stats$snapshot sn
where sn.snap_id=sq.snap_id;
--oracle 8i stats$sqltext用来删除stats_sql_summary表相关的巨大存储开销.
--oracle 9i stats$sqltext(只存储sql源代码)
select * from stats$sql_text
--stats$latch_misses_summary 记录了oracle数据库的锁存失败
select * from stats$latch_misses_summary
--生成锁存报告
select sn.snap_time ,sl.parent_name,sl.where_in_code,sum(nwfail_count) sum_nwfail, sum(sleep_count) sum_sleep
from stats$latch_misses_summary sl ,stats$snapshot sn
where sn.snap_id=sl.snap_id group by sn.snap_time,sl.parent_name,sl.where_in_code ;
--stats$sql_summary 表
--sql统计汇总是statspack 工具中最重要的表之一.
--sql调整经常可以极大的影响oracle系统的性能
--stats$parameter
select * from stats$parameter where name like '%optimizer_mode%'
****************************************************************
STATSPACK系统表
stats$rollstat
stats$latch
select * from stats$latch_children
select * from stats$librarycache(库高速缓存表项的命中率都要保持在90%以上 否则要对SGA oracle 共享池 进行调整)
select * from stats$waitstat where wait_count>0
判断对象是否不正确的存储参数设置 ,最好的途径之一就是观察自由表等待
如果自由表等待非常高,就说明所拥有的表存在竞争性的inert或者update任务
这些表没有定义足够的自由表
select * from stats$enqueue_stat
select * from stats$enqueue_stat where failed_req#>0
分析stats$enqueue_stat 表的时候 很重要的一点就是要记住队列等待是oracle处理的正常部分
select * from stats$sysstat
select * from stats$sesstat
select * from v$statname
select * from stats$sgastat
***************************************************************************
statspack事务表
stats$buffer_pool_statistics
select * from stats$buffer_pool_statistics
这个表是缓冲池效率的一般度量
select * from stats$filestatxs
stats$filestatxs 是关于oracle 调整的最重要的表之一
包括oracle数据文件的详细信息,包括读入IO数量 ,写入IO数量 以及处理过程经历的等待争用
I/O子系统负载平衡
找到"热点"文件以及热点表
找到数据库读入和写入活动的峰值事件
****************************************************
statspack事件表
select * from stats$system_event
select * from stats$session_event
select * from stats$idle_event
select * from stats$bg_event_summary
stats$bg_event_summary 汇总了所有数据库实例的后台事件
和stats$system_event
******************************************************************************************************************
--扩展statspack 收集服务器统计
--每隔2秒 共收集5次
[newvers@bj37 newvers]$ vmstat 2 5
procs memory swap io system cpu
r b w swpd free buff cache si so bi bo in cs us sy id
0 0 0 118136 49908 98268 1668004 0 0 1 0 0 0 0 0 1
0 0 0 118136 51472 98276 1668012 0 0 4 88 177 790 2 0 97
0 0 0 118136 51468 98276 1668012 0 0 0 24 116 39 0 0 100
0 0 0 118136 49608 98276 1668092 0 0 30 138 286 1144 4 1 95
0 0 0 118136 51340 98276 1668116 0 0 12 92 183 263 0 0 100
r 运行队列 当这个值超过服务器上cpu的数量 就会存在cpu瓶颈
pi 是页换入 页换入操作表示服务器出现了内存短缺
us 是用户cpu
sy 是系统cpu
id 是空闲
wa 是等待
所有的cpu值(us+sy+id+wa)总是等于100%
--使用vmstat 识别cpu瓶颈
linux 查看cpu数量
/proc/目录下有常见的系统信息
cat /proc/cpuinfo|grep processor|wc -l
ibm aix 和hp_ux
lsdev -C|grep Processor|wc -l
Solaris中显示cpu的数量
psrinfo -v|grep "Status of processor"|wc -l
--vmstat 识别频繁使用的cpu
要是us与sy的和逼近100 就表示cpu在满负荷运行
唯一能指出cpu瓶颈的度量是运行队列"r"的值
超过了cpu的数量
识别RAM 内存瓶颈
hp/ux 显示内存大小
dmesg
显示 dec-unix 内存大小
uerf -r 300|grep -i mem
aix 上显示内存大小
第一步 : lsdev -C|grep mem
第二步 : lsattr -El mem0
显示sqlaris上内存大小
prtconf|grep -i mem
--使用top工具显示RAM
top -d 2
每隔2秒刷新一次
--hp和solaris可是用glance查看内存
RAM内存和交换磁盘
因为交换(pi)会花费大量的时间从交换磁盘上将内存段复制回RAM,所以他会减慢服务器的速度,在
oracle数据库服务器上,解决页换入问题的方案:
更小的SGA
更多的RAM
减少RAM需求(减少对程序全局区的(PGA)内存的需求来减少数据库服务器的RAM消耗.)
--在aix中检测潜在的I/O瓶颈
wa列表示现在等待外部os服务的cpu百分比 高不一定就是有I/O瓶颈
获取vmstat信息的脚本
connect perfstat/perfstat
drop table stats$vmstat
create table stats$vmstat
(
start_date date,
duration number,
server_name varchar2(20) ,
runque_waits number,
page_in number,
page_out number,
user_cpu number,
system_cpu number,
idle_cpu number,
wait_cpu number
)
tablespace perfstat
storage(initial 10m
next 1m
pctincrease 0);
--linux操作系统的VMSTAT 获取工具脚本
1.必须将ORACLE_HOME设置到你的目录:
ORACLE_HOME=/home/newvers/product/92
2.必须在sqlplus命令中设置ORACLE_SID
$ORACLE_HOME/bin/sqlplus
[email=perfstat/perfstat@prdyp
3.必须通过设置SAMPLE_TIME 改变采样时间:
SAMPLE_TIME=300
--get_vmstat.ksh
ORACLE_HOME=/home/newvers/product/92
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
SERVER_NAME= uname -a|awk '{print $2}'
typeset -u SERVER_NAME
export SERVER_NAME
SAMPLE_TIME=300
while true
do
vmstat ${SAMPLE_TIME} 2>/tmp/msg$$
# run vmstat and direct the output into the Oracle table
cat /tmp/msg$$|sed 1,3d| awk '{printf("%s %s %s %s %s %s\n",$1,$8,$9,$14,$15,$16)}' |while read RUNQUE PAGE_IN PAGE_OUT USER_CPU SYSTEM_CPU IDLE_CPU
do
$ORACLE_HOME/bin/sqlplus -a
[email=perfstat/perfstat@prdyp
rm /tmp/msg$$
--服务器任务负载平衡
确定cpu峰值时间,避免在峰值时间执行批处理程序和对cpu使用过高的程序(尽量将其转移到夜晚执行)
--rpt_top_sql.sql
--查找10点和下午3点最消耗cpu的sql_text
select
to_char(snap_time,'yyyy-mm-dd hh24'),
substr(sql_text,1,50)
from
stats$sql_summary a,
stats$snapshot sn
where
a.snap_id=sn.snap_id
and
to_char(snap_time,'hh24')=10
or
to_char(snap_time,'hh24')=15
order by
rows_processed desc ; --按行处理排序
select object_type mytype,
object_name myname ,
blocks,
count(1) buffers,
avg(tch) avg_touches
from
sys.x$bh a,
dba_objects b,
dba_segments s
where
a.obj=b.object_id
and
b.object_name=s.segment_name
and
b.owner not in('SYS','SYSTEM')
GROUP BY object_name,object_type,
blocks,obj
having avg(tch)>5
and count(1)>20;
识别出热点对象后,可以决定将对象隔离放入keep池中
作为一般的规则,应该有足够的RAM存储可以用于整个表或者索引
列如,如果希望为keep池增加页表,就需要给init.ora的buffer_pool_keep 参数增加104个数据块
--调整 recycle 池
在recycle池放置对象的目标是将全表搜索频率的大表进行分离,为了找到进行全表搜索的大表,我们必须求助于从
access.sql中获得的全表搜索报告:
access_recycle_syntax.sql
select
'alter table '||p.owner||'.'||p.name||' storage (buffer_pool recyle);'
from
dba_tables t,
dba_segments s,
sqltemp s,
(select distinct
statement_id stid,
object_owner owner,
object_name name
from
plan_table
where
operation='TABLE ACCESS'
and
options='FULL') p
where
s.addr||':'||TO_CHAR(s.hashval)=p.stid
and
t.table_name=p.name
and
t.owner=p.owner
and t.buffer_pool'RECYCLE'
having s.blocks>1000
group by
p.owner,p.name,t.num_rows,s.blocks
order by
sum(s.executions) desc;
--给表分配recycle池
alter table user.table_name storage(buffer_pool recycle);
注意:在将任何表加入到RECYCLE池之前,DBA都应该抽取sql源代码,并且验证这个查询是否获取超过了表中行的40%
--高级recycle池调整
下列查询使用了x$bh.tch来识别具有一次缓冲区接触计数,但是总量超过了整个缓存的5%的数据缓存中的对象
,这些数据段是潜在的在recycle缓冲池中放置的候选对象,因为他们可能会让不会重用的数据块占用大量的缓存空间
select object_type mytype,
object_name myname,
blocks,
count(1) buffers,
100*(count(1)/totsize) pct_cache
from
sys.x$bh a,
dba_objects b,
dba_segments s,
()
--取消跟踪功能
alter system set trace_enabled=false;
--STATISTICS_LEVEL
The STATISTICS_LEVEL parameter was introduced in Oracle9i Release 2 (9.2) to control all major statistics collections or advisories in the database. The level of the setting affects the number of statistics and advisories that are enabled:
BASIC: No advisories or statistics are collected.
TYPICAL: The following advisories or statistics are collected:
Buffer cache advisory
MTTR advisory
Shared Pool sizing advisory
Segment level statistics
PGA target advisory
Timed statistics
ALL: All of TYPICAL, plus the following:
Timed operating system statistics
Row source execution statistics
The parameter is dynamic and can be altered using:
ALTER SYSTEM SET statistics_level=basic;
ALTER SYSTEM SET statistics_level=typical;
ALTER SYSTEM SET statistics_level=all;
Current settings for parameters can be shown using:
SHOW PARAMETER statistics_level
SHOW PARAMETER timed_statistics
Oracle can only manage statistic collections and advisories whose parameter setting is undefined in the spfile.
By default the TIMED_STATISTICS parameter is set to TRUE so this must be reset for it to be controled by the statistics level,
along with any other conflicting parameters:
ALTER SYSTEM RESET timed_statistics scope=spfile sid='*';
This setting will not take effect until the database is restarted.
At this point the affect of the statistics level can be shown using the following query:
COLUMN statistics_name FORMAT A30 HEADING "Statistics Name"
COLUMN session_status FORMAT A10 HEADING "Session|Status"
COLUMN system_status FORMAT A10 HEADING "System|Status"
COLUMN activation_level FORMAT A10 HEADING "Activation|Level"
COLUMN session_settable FORMAT A10 HEADING "Session|Settable"
SELECT statistics_name,
session_status,
system_status,
activation_level,
session_settable
FROM v$statistics_level
ORDER BY statistics_name;
A comparison between the levels can be shown as follows:
SQL> ALTER SYSTEM SET statistics_level=basic;
System altered.
SQL> SELECT statistics_name,
2 session_status,
3 system_status,
4 activation_level,
5 session_settable
6 FROM v$statistics_level
7 ORDER BY statistics_name;
Session System Activation Session
Statistics Name Status Status Level Settable
------------------------------ ---------- ---------- ---------- ----------
Buffer Cache Advice DISABLED DISABLED TYPICAL NO
MTTR Advice DISABLED DISABLED TYPICAL NO
PGA Advice DISABLED DISABLED TYPICAL NO
Plan Execution Statistics DISABLED DISABLED ALL YES
Segment Level Statistics DISABLED DISABLED TYPICAL NO
Shared Pool Advice DISABLED DISABLED TYPICAL NO
Timed OS Statistics DISABLED DISABLED ALL YES
Timed Statistics DISABLED DISABLED TYPICAL YES
8 rows selected.
SQL> ALTER SYSTEM SET statistics_level=typical;
System altered.
SQL> SELECT statistics_name,
2 session_status,
3 system_status,
4 activation_level,
5 session_settable
6 FROM v$statistics_level
7 ORDER BY statistics_name;
Session System Activation Session
Statistics Name Status Status Level Settable
------------------------------ ---------- ---------- ---------- ----------
Buffer Cache Advice ENABLED ENABLED TYPICAL NO
MTTR Advice ENABLED ENABLED TYPICAL NO
PGA Advice ENABLED ENABLED TYPICAL NO
Plan Execution Statistics DISABLED DISABLED ALL YES
Segment Level Statistics ENABLED ENABLED TYPICAL NO
Shared Pool Advice ENABLED ENABLED TYPICAL NO
Timed OS Statistics DISABLED DISABLED ALL YES
Timed Statistics ENABLED ENABLED TYPICAL YES
8 rows selected.
SQL> ALTER SYSTEM SET statistics_level=all;
System altered.
SQL> SELECT statistics_name,
2 session_status,
3 system_status,
4 activation_level,
5 session_settable
6 FROM v$statistics_level
7 ORDER BY statistics_name;
Session System Activation Session
Statistics Name Status Status Level Settable
------------------------------ ---------- ---------- ---------- ----------
Buffer Cache Advice ENABLED ENABLED TYPICAL NO
MTTR Advice ENABLED ENABLED TYPICAL NO
PGA Advice ENABLED ENABLED TYPICAL NO
Plan Execution Statistics ENABLED ENABLED ALL YES
Segment Level Statistics ENABLED ENABLED TYPICAL NO
Shared Pool Advice ENABLED ENABLED TYPICAL NO
Timed OS Statistics ENABLED ENABLED ALL YES
Timed Statistics ENABLED ENABLED TYPICAL YES
8 rows selected.
SQL>
Hope this helps. Regards Tim...
--内存调整
select * from v$sga;
--调整前SGA
NAME VALUE
-------------------- ----------
Fixed Size 452184
Variable Size 402653184
Database Buffers 251658240
Redo Buffers 667648
select * from v$sgastat;
POOL NAME BYTES
----------- -------------------------- ----------
fixed_sga 452184
buffer_cache 251658240
log_buffer 656384
shared pool errors 8940
shared pool enqueue 171860
shared pool KGK heap 3756
shared pool KQR M PO 1393788
shared pool KQR S PO 177272
shared pool KQR S SO 5120
shared pool sessions 410040
shared pool sql area 61446860
POOL NAME BYTES
----------- -------------------------- ----------
shared pool 1M buffer 2098176
shared pool KGLS heap 2613480
shared pool PX subheap 19684
shared pool parameters 39012
shared pool free memory 125812664
shared pool PL/SQL DIANA 3445584
shared pool FileOpenBlock 695504
shared pool PL/SQL MPCODE 637644
shared pool PL/SQL PPCODE 48400
shared pool PL/SQL SOURCE 14344
shared pool library cache 19376952
POOL NAME BYTES
----------- -------------------------- ----------
shared pool miscellaneous 8639216
shared pool PLS non-lib hp 2068
shared pool joxs heap init 4220
shared pool table definiti 2632
shared pool trigger defini 1128
shared pool trigger inform 528
shared pool trigger source 624
shared pool Checkpoint queue 564608
shared pool VIRTUAL CIRCUITS 265160
shared pool dictionary cache 1614976
shared pool KSXR receive buffers 1032500
POOL NAME BYTES
----------- -------------------------- ----------
shared pool character set object 432136
shared pool FileIdentificatonBlock 319452
shared pool message pool freequeue 833032
shared pool KSXR pending messages que 840636
shared pool event statistics per sess 1908760
shared pool fixed allocation callback 268
large pool free memory 83886080
java pool free memory 83886080
41 rows selected.
--UGA的大小,UGA主要包含一下部分的内存设置
show parameters area_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 1048576
sort_area_size integer 524288
workarea_size_policy string AUTO
--计算数据缓冲区命中率
select value from v$sysstat where name='physical reads' 4383475
select * from v$sysstat where name='physical reads direct' 3834798
select * from v$sysstat where name='physical reads direct (lob)' 374616
select * from v$sysstat where name like 'consistent gets' 1198738167
select * from v$sysstat where name like 'db block gets' 53472785
x=physical reads direct+physical reads direct (lob)
100-(physical reads-x)/(consistent gets+db block gets-x)*100
100-(4383475-3834798-374616)/(1198738167+53472785-3834798-374616)*100
--共享池的命中率
select sum(pinhits)/sum(pins)*100 "hit radio" from v$librarycache;
--关于排序部分
select name,value from v$sysstat where name like '%sort%';
select sorts(disk)/(sorts (memory)+sorts(disk)) from dual
select 0/(17038425+0) from dual
--关于log_buffer
select name,value from v$sysstat
where name in('redo entries','redo buffer allocation retries');
redo buffer allocation retries/redo entries >1% 考虑增加log_buffer
--其他视图
v$db_cache_advice、v$pga_target_advice、v$java_pool_advice 和 v$db_shared_pool_advice