以百分比显示会话的oracle CPU使用率 [英] Show oracle CPU usage for sessions as percentage

问题描述

以下脚本返回活动会话的cpu使用情况.结果以秒为单位显示了cpu的使用情况.

The following scripts returns the cpu usage for active sessions. The result shows the cpu usage in seconds.

我需要的是相同报告，其中cpu使用率以百分比表示.最好的方法是什么?

What I need is the same report with cpu usage in percentage. What is the best way to do this?

--
-- Show CPU Usage for Active Sessions
--

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300

COLUMN username FORMAT A30
COLUMN sid FORMAT 999,999,999
COLUMN serial# FORMAT 999,999,999
COLUMN "cpu usage (seconds)"  FORMAT 999,999,999.0000

SELECT
   s.username,
   t.sid,
   s.serial#,
   SUM(VALUE/100) as "cpu usage (seconds)"
FROM
   v$session s,
   v$sesstat t,
   v$statname n
WHERE
   t.STATISTIC# = n.STATISTIC#
AND
   NAME like '%CPU used by this session%'
AND
   t.SID = s.SID
AND
   s.status='ACTIVE'
AND
   s.username is not null
GROUP BY username,t.sid,s.serial#
/

推荐答案

长话短说:您将无法通过单个查询来执行此操作，您将需要编写PL/SQL来按顺序收集有用的数据以获得有用的信息.

Long story short: you won't be able to do it with a single query, you will need to write a PL/SQL to gather useful data in order to obtain useful information.

Oracle具有累计时间"统计信息，这意味着引擎会持续跟踪使用情况.您必须定义分析的开始时间和结束时间.

Oracle has "accumulated time" statistics, this means that the engine keeps a continous track of use. You will have to define a start time and an end time for analysis.

您可以从V $ SYS_TIME_MODEL查询'DB CPU'

You can query 'DB CPU' from V$SYS_TIME_MODEL

select value into t_db_cpu_i
from sys.V_$SYS_TIME_MODEL
where stat_name = 'DB CPU' ;  /* start time */ 
...
select value into t_db_cpu_f
from sys.V_$SYS_TIME_MODEL
where stat_name = 'DB CPU' ; /* end time */

如果只有#1或#8 CPU，CPU统计信息将受到影响.因此，您必须确定引擎使用的CPU数量.

CPU statistics will be affected if you have just #1 CPU or #8 CPUs. So, you will have to determine how many CPUs is your engine using.

您可以从V $ PARAMETER查询'cpu_count'以获得此值.

You can query 'cpu_count' from V$PARAMETER to obtain this value.

select value into t_cpus
from sys.v_$parameter
where name='cpu_count' ;

然后，这很简单:

最大总时间为秒* CPU数，因此，如果您只有#1 CPU，则最大总时间将为"60"，但是如果您有#2 CPU，则最大总时间将为"120" .. #3 CPU将是"180" ..等等.

Maximum total time will be seconds * number of CPUs, so if you have just #1 CPU then maximum total time would be "60" , but if you have #2 CPUs then maximun total time would be "120" .. #3 CPUs will be "180" .. etc. ...

因此，您可以使用sysdate来获取分析期间的开始时间和结束时间:

So you take start time and end time of the analyzed period using sysdate:

t_start := sysdate ;
t_end := sysdate ;

现在您可以计算出以下内容:

And now you compute the following:

seconds_elapsed := (t_end - t_start)*24*60*60 ;
total_time := seconds_elapsed * t_cpus ; 
used_cpu := t_db_cpu_f - t_db_cpu_i ;
secs_cpu := seconds_elapsed/1000000 ;
avgcpu := (secs_cpu/total_time)*100 ;

就是这样，"avgcpu"是您要寻找的值.

And that's it, "avgcpu" is the value you are looking for.

这篇关于以百分比显示会话的oracle CPU使用率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！