数据库性能监视操作

数据库性能监视操作

1、以system用户身份登陆或使用sqlplus '/as sysdba'登陆数据库
2、检查PGA内存使用情况
   SQL> select name,value from v$pgastat;
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
aggregate PGA target parameter                                     25165824
aggregate PGA auto target                                          17464320
global memory bound                                                 1257472
total PGA inuse                                                     5761024
total PGA allocated                                                10747904
maximum PGA allocated                                              34441216
total freeable PGA memory                                                 0
PGA memory freed back to OS                                        11534336
total PGA used for auto workareas                                         0
maximum PGA used for auto workareas                                   57344
total PGA used for manual workareas                                       0
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
maximum PGA used for manual workareas                                     0
over allocation count                                                     0
bytes processed                                                    18631680
extra bytes read/written                                                  0
cache hit percentage                                                    100
其中aggregate PGA target parameter是个定值，在init文件中配置好的，正常情况下，这个值是最大的
其余参数值都没有它大，如果发现比它大的值，比如maximum PGA allocated的值比较大，那么数据库库可能
负载比较大，或者是内存不合理。

另外：
total PGA inuse是正在使用的PGA内存，这个参数的值应该小于aggregate PGA target parameter参数值
total PGA allocated是已经分配的PGA内存，这个值也应该小于aggregate PGA target parameter参数值

2、检查数据库的内存排序情况
SQL> select name,value from v$sysstat where name in('sorts(memory)','sorts(disk)');
NAME                                                              VALUE
------------------------------------------------------------------------------

----------
sorts(memory)                                                       98722473
sorts(disk)                                                           1
硬盘排序的树木要远远小于内存排序，否则内存结构还不够优化

3、检查session数目
检查系统最大session数目
SQL> show parameter session;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
logmnr_max_persistent_sessions       integer     1
mts_sessions                         integer     165
session_cached_cursors               integer     0
session_max_open_files               integer     10
sessions                             integer     170
shared_server_sessions               integer     165
检查当前的session数目
SQL> select count(*)from v$session;
 
  COUNT(*)
----------
         7
正常情况下，当前的session数目应该小于系统配置的最大session数，如果接近或超过最大session数，数据库负载就很大了，存在风险

4、检查Workarea的使用情况
SQL> select name profile,cnt,decode(total,0,0,round(cnt*100/total)) percentage from(select name,value cnt,(sum(value) over ())total from v$sysstat where name like 'workarea exec%');
 
PROFILE                                                                 CNT
---------------------------------------------------------------- ----------
PERCENTAGE
----------
workarea executions - optimal                                          1657
       100
 
workarea executions - onepass                                             0
         0
 
workarea executions - multipass                                           0
         0
关注输出的Percentage值，optimal对应的percentage一般情况下应该在98%~100%，如果这个值低于98%，那么
数据库系统可能需要优化