ORACLE常用性能监控SQL【一】（下）

2023-05-22 147

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： ORACLE常用性能监控SQL【一】（下）

监控 SGA 中字典缓冲区的命中率

select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio", 
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio" 
from v$rowcache 
where gets+getmisses <>0 
group by parameter, gets, getmisses ;

监控 SGA **享缓存区的命中率，应该小于1%

select sum(pins) "Total Pins", sum(reloads) "Total Reloads", 
sum(reloads)/sum(pins) *100 libcache 
from v$librarycache;

监控 SGA 中重做日志缓存区的命中率，应该小于1%

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');

监控内存和硬盘的排序比率，最好使它小于 .10

SELECT name, value 
FROM v$sysstat 
WHERE name IN ('sorts (memory)', 'sorts (disk)') ;

监控字典缓冲区

SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES" 
FROM V$ROWCACHE ;

非系统用户建在SYSTEM表空间中的表

SELECT owner,table_name 
FROM DBA_TABLES
WHERE tablespace_name in('SYSTEM','USER_DATA') AND 
      owner NOT IN('SYSTEM','SYS','OUTLN', 'ORDSYS','MDSYS','SCOTT', 'HOSTEAC');

性能最差的SQL

SELECT * FROM ( SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text 
                FROM v$sqlarea 
                ORDER BY disk_reads DESC) 
WHERE ROWNUM<100;

读磁盘数超100次的sql

select * from sys.v_$sqlarea where disk_reads>100;

查找消耗资源比较的sql语句

  Select se.username,
          se.sid,
          su.extents,
          su.blocks * to_number(rtrim(p.value)) as Space,
          tablespace,
          segtype,
          sql_text
     from v$sort_usage su, v$parameter p, v$session se, v$sql s
    where p.name = 'db_block_size'
      and su.session_addr = se.saddr
      and s.hash_value = su.sqlhash
      and s.address = su.sqladdr
    order by se.username, se.sid;

最频繁执行的sql

select * from sys.v_$sqlarea where executions>100;

查询使用CPU多的用户session

select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value 
from v$session a,v$process b,v$sesstat c 
where c.statistic#=12 and 
      c.sid=a.sid and 
      a.paddr=b.addr 
order by value desc;

当前每个会话使用的对象数

SELECT a.sid,s.terminal,s.program,count(a.sid) 
FROM V$ACCESS a,V$SESSION s
WHERE a.owner <> 'SYS'AND s.sid = a.sid 
GROUP BY a.sid,s.terminal,s.program
ORDER BY count(a.sid) ;

查看数据库库对象

SELECT owner, object_type, status, COUNT(*) count#
  FROM all_objects
 GROUP BY owner, object_type, status;

查看数据库的版本

SELECT version 
FROM product_component_version 
WHERE substr(product, 1, 6) = 'Oracle';

查看数据库的创建日期和归档方式

SELECT created, log_mode, log_mode FROM v$database;

检查角色和权限设置

根据用户名进行授权的对象级特权

select b.owner || '.' || b.table_name obj,
       b.privilege what_granted,
       b.grantable,
       a.username
  from sys.dba_users a, sys.dba_tab_privs b
 where a.username = b.grantee
 order by 1, 2, 3;

根据被授权人进行授权的对象级特权

Select owner || '.' || table_name obj,
       privilege what_granted,
       grantable,
       grantee
  from sys.dba_tab_privs
 where not exists (select 'x' from sys.dba_users where username = grantee)
 order by 1, 2, 3;

根据用户名进行授予的系统级特权

select b.privilege what_granted, b.admin_option, a.username
  from sys.dba_users a, sys.dba_sys_privs b
 where a.username = b.grantee
 order by 1, 2;

根据被授权人进行授予的系统级特权

select privilege what_granted, admin_option, grantee
  from sys.dba_sys_privs
 where not exists (select 'x' from sys.dba_users where username = grantee)
 order by 1, 2;

根据用户名授予的角色

select b.granted_role ||
       decode(admin_option, 'YES', ' (With Admin Option)', null) what_granted,
       a.username
  from sys.dba_users a, sys.dba_role_privs b
 where a.username = b.grantee
 order by 1;

根据被授权人授予的角色

select granted_role ||
       decode(admin_option, 'YES', ' (With Admin Option)', null) what_granted,
       grantee
  from sys.dba_role_privs
 where not exists (select 'x' from sys.dba_users where username = grantee)
 order by 1;

用户名及已被授予的相应权限

select a.username,
       b.granted_role ||
       decode(admin_option, 'YES', ' (With Admin Option)', null) what_granted
  from sys.dba_users a, sys.dba_role_privs b
 where a.username = b.grantee
UNION
select a.username,
       b.privilege ||
       decode(admin_option, 'YES', ' (With Admin Option)', null) what_granted
  from sys.dba_users a, sys.dba_sys_privs b
 where a.username = b.grantee
UNION
select a.username,
       b.table_name || '-' || b.privilege ||
       decode(grantable, 'YES', ' (With Grant Option)', null) what_granted
  from sys.dba_users a, sys.dba_tab_privs b
 where a.username = b.grantee
 order by 1;

查询用户名及相应的配置文件、默认的表空间和临时表空间

Select username, profile, default_tablespace, temporary_tablespace, created
  from sys.dba_users
 order by username;

等待事件V$视图

在Oracle 10g中V$SESSION_WAIT中的所有等待事件列现在都在V$SESSION中。因此，确保查询等待信息的 V$SESSION，因为它是一个更快的视图。V$ACTIVE_SESSION_HISTORY (ASH)将许多重要统计数据合并为一个视图或一个报表(ASH报表)。

马上该谁等待–查询`V$SESSION_WAIT / V$SESSION`

select event,
       sum(decode(wait_time, 0, 1, 0)) "Waiting Now",
       sum(decode(wait_time, 0, 0, 1)) "Previous Waits",
       count(*) "Total"
  from v$session_wait
 group by event
 order by count(*);

马上该谁等待；SPECIFIC Waits–查询V$SESSION_WAIT

SELECT /*+ ordered */
 sid, event, owner, segment_name, segment_type, p1, p2, p3
  FROM v$session_wait sw, dba_extents de
 WHERE de.file_id = sw.p1
   AND sw.p2 between de.block_id and de.block_id + de.blocks - 1
   AND (event = 'buffer busy waits' OR event = 'write complete waits')
   AND p1 IS NOT null
 ORDER BY event, sid;

马上该谁等待；SPECIFIC Waits–查询V$SESSION_WAIT

SELECT /*+ ordered */
 sid, event, owner, segment_name, segment_type, p1, p2, p3
  FROM v$session_wait sw, dba_extents de
 WHERE de.file_id = sw.p1
   AND sw.p2 between de.block_id and de.block_id + de.blocks - 1
   AND (event = 'buffer busy waits' OR event = 'write complete waits')
   AND p1 IS NOT null
 ORDER BY event, sid;

谁在等待 - 最后10 个等待数–查询V$SESSION_WAIT_HISTORY

SELECT /*+ ordered */
 sid, event, owner, segment_name, segment_type, p1, p2, p3
  FROM v$session_wait sw, dba_extents de
 WHERE de.file_id = sw.p1
   AND sw.p2 between de.block_id and de.block_id + de.blocks - 1
   AND (event = 'buffer busy waits' OR event = 'write complete waits')
   AND p1 IS NOT null
 ORDER BY event, sid;

查找P1, P2, P3代表什么–查询 V$EVENT_NAME

select event#, name, parameter1 p1, parameter2 p2, parameter3 p3
  from v$event_name
 where name in ('buffer busy waits', 'write complete waits');

会话开始后的所有等待数–查询 V$SESSION_EVENT

select sid, event, total_waits, time_waited, event_id
  from v$session_event
 where time_waited > 0
 order by time_waited;

类的所有会话等待数–查询V$SESSION_WAIT_CLASS

select sid, wait_class, total_waits from  v$session_wait_class

系统启动后的所有等待数–查询V$SYSTEM_EVENT

select event, total_waits, time_waited, event_id
  from v$system_event
 where time_waited > 0
 order by time_waited;

类的系统等待数–查询V$SYSTEM_WAIT_CLASS

select wait_class, total_waits
  from v$system_wait_class
 order by total_waits desc;

类的系统等待数–查询V$ACTIVE_SESSION_HISTORY

–In the query below, the highest count session is leader in non-idle wait events.

select session_id, count(1)
  from v$active_session_history
 group by session_id
 order by 2;

–In the query below, find the SQL for the leader in non-idle wait events.

select c.sql_id, a.sql_text
  from v$sql a,
       (select sql_id, count(1)
          from v$active_session_history b
         where sql_id is not null
         group by sql_id order by 2 desc) c
 where rownum <= 5
 order by rownum;

自动工作量仓库(AWR) 的基本信息

自动工作量仓库(AWR)在默认情况下，仓库用小时填充，保留期是7天。

AWR使用多少空间

SQL> Select occupant_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants where occupant_name like '%AWR%';
OCCUPANT_NAME       OCCUPANT_DESC            SPACE_USAGE_KBYTES
----------------- ---------------------------------- ------------------
SM/AWR        Server Manageability - Automatic Workload Repository             215616
SQL>

系统上最原始的AWR信息是什么？

SQL> select dbms_stats.get_stats_history_availability from dual;
GET_STATS_HISTORY_AVAILABILITY
-------------------------------------------------------------
20-OCT-16 12.04.49.088829000 AM -04:00

什么是AWR信息的保留期？

SQL>  select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
                         31

将AWR信息的保留期更改为15天？

SQL> EXEC dbms_stats.alter_stats_history_retention(15);
PL/SQL 过程已成功完成。

获取生成的trace文件

开启SQL跟踪后，会生成一个trace文件，通过初始化参数user_dump_dest配置其所在目录，该参数的值可以通过下面方法获取到：

select name, value from v$parameter where name = 'user_dump_dest'

trace文件的名字是独立于版本和平台的，在大部分常见的平台下，命名结构如下：

{instance name}_{process name}_{process id}.trc

1）instance name

初始化参数instance_name的小写值。通过v$instance视图的instance_name列可以得到这个值。

2）process name

产生跟踪文件进程的名字的小写值。对于专有服务器进程，使用ora，对于共享服务器进程，可以通过v$diapatcher或v$shared_server视图的name列获得。对于并行从属进程，可以通过v$px_process视图server_name列获得，对于其他多数后台进程来说，可以通过v$bgprocess视图的name列获得。

3）process id

操作系统层面的进程标记。这个值可以通过v$process视图的spid列获取。

根据这些信息，可以通过下面的方式获取trace文件名：

select s.SID,
       s.SERVER,
       lower(case
               when s.SERVER in ('DEDICATED', 'SHARED') then
                i.INSTANCE_NAME || '_' || nvl(pp.SERVER_NAME, nvl(ss.NAME, 'ora')) || '_' ||
                p.SPID || '.trc'
               else
                null
             end) as trace_file_name
  from v$instance      i,
       v$session       s,
       v$process       p,
       v$px_process    pp,
       v$shared_server ss
 where s.PADDR = p.ADDR
   and s.SID = pp.SID(+)
   and s.PADDR = ss.PADDR(+)
   and s.TYPE = 'USER'
   and s.SID = 'your sid'
 order by s.SID

将上面的’your sid’替换为你的session的sid就可以查出指定session生成的trace文件的名字，session的sid在v$session视图中得到，或者直接查询当前session的sid：

select userenv('sid') from dual
或者
select  sid  from v$mystat a where rownum=1 ;

将路径（user_dump_dest）和文件名结合在一起，我们就得到了trace文件的完整路径。

而在Oracel 11g中，查询当前会话生成的trace文件则非常简单：

select value from v$diag_info where name = 'Default Trace File'

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

ORACLE常用性能监控SQL【一】（下）

监控 SGA 中字典缓冲区的命中率

监控 SGA **享缓存区的命中率，应该小于1%

监控 SGA 中重做日志缓存区的命中率，应该小于1%

监控内存和硬盘的排序比率，最好使它小于 .10

监控字典缓冲区

非系统用户建在SYSTEM表空间中的表

性能最差的SQL

读磁盘数超100次的sql

查找消耗资源比较的sql语句

最频繁执行的sql

查询使用CPU多的用户session

当前每个会话使用的对象数

查看数据库库对象

查看数据库的版本

查看数据库的创建日期和归档方式

检查角色和权限设置

根据用户名进行授权的对象级特权

根据被授权人进行授权的对象级特权

根据用户名进行授予的系统级特权

根据被授权人进行授予的系统级特权

根据用户名授予的角色

根据被授权人授予的角色

用户名及已被授予的相应权限

查询用户名及相应的配置文件、默认的表空间和临时表空间

等待事件V$视图

马上该谁等待–查询V$SESSION_WAIT / V$SESSION

马上该谁等待；SPECIFIC Waits–查询V$SESSION_WAIT

马上该谁等待；SPECIFIC Waits–查询V$SESSION_WAIT

谁在等待 - 最后10 个等待数–查询V$SESSION_WAIT_HISTORY

查找P1, P2, P3代表什么–查询 V$EVENT_NAME

会话开始后的所有等待数–查询 V$SESSION_EVENT

类的所有会话等待数–查询V$SESSION_WAIT_CLASS

系统启动后的所有等待数–查询V$SYSTEM_EVENT

类的系统等待数–查询V$SYSTEM_WAIT_CLASS

类的系统等待数–查询V$ACTIVE_SESSION_HISTORY

自动工作量仓库(AWR) 的基本信息

获取生成的trace文件

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

马上该谁等待–查询`V$SESSION_WAIT / V$SESSION`