以百分比形式显示会话的 oracle CPU 使用率
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30616351/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Show oracle CPU usage for sessions as percentage
提问by Postlagerkarte
The following scripts returns the cpu usage for active sessions. The result shows the cpu usage in seconds.
以下脚本返回活动会话的 CPU 使用率。结果以秒为单位显示 cpu 使用情况。
What I need is the same report with cpu usage in percentage. What is the best way to do this?
我需要的是与 cpu 使用率相同的报告。做这个的最好方式是什么？
--
-- 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#
/
回答by Daniel Vukasovich
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.
长话短说：您将无法通过单个查询来完成，您将需要编写 PL/SQL 来收集有用的数据以获得有用的信息。
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.
Oracle 有“累计时间”统计，这意味着引擎会持续跟踪使用情况。您必须定义分析的开始时间和结束时间。
You can query 'DB CPU' from V$SYS_TIME_MODEL
您可以从 V$SYS_TIME_MODEL 查询“DB CPU”
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 */
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.
如果您只有 #1 CPU 或 #8 CPU，CPU 统计数据将受到影响。因此，您必须确定您的引擎使用了多少 CPU。
You can query 'cpu_count' from V$PARAMETER to obtain this value.
您可以从 V$PARAMETER 查询 'cpu_count' 以获取此值。
select value into t_cpus
from sys.v_$parameter
where name='cpu_count' ;
Then, it's quite simple:
然后，这很简单：
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. ...
最大总时间将是秒 * CPU 数，因此如果您只有 #1 个 CPU，则最大总时间将为 "60" ，但如果您有 #2 个 CPU，则最大总时间将为 "120" .. #3 个 CPU将是“180”……等等……
So you take start time and end time of the analyzed period using sysdate:
因此，您使用 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 ;
And that's it, "avgcpu" is the value you are looking for.
就是这样，“avgcpu”就是您正在寻找的值。
