163 积分	0 好友	12 主题

发消息

[SQL调优] 模仿Oracle OEM 展现AAS average active sessions

1^#

发表于 2013-5-20 17:17:05 | 查看: 7386| 回复: 5

终于终于有点搞清楚 oracle中有关OEM图中的AAS是怎么算的。了了心头一件事

感谢刘大给了AAS的概念 :)

代码如下：
创建三个视图 v_zyf_active_session, V_ZYF_ACTIVE_SESSION_ALL, V_ZYF_ACTIVE_SESSION_AAS
创建一个字典表: zyf_active_type

create or replace view v_zyf_active_session
--runs用sample_id相减得出
--其实简单点，可以取60秒.因为group by 是用trunc(sample_time, 'MI')
as
SELECT update_time
,activity
,COUNT(*) num --db_time ,
,max(runs) runs --采样的时间间隔（秒）
FROM (SELECT trunc(sample_time, 'MI') update_time
,nvl(wait_class, 'CPU') activity
,(last_value(sample_id) over(PARTITION BY trunc(sample_time, 'MI') ORDER BY sample_id rows BETWEEN unbounded preceding AND unbounded following) -
first_value(sample_id) over(PARTITION BY trunc(sample_time, 'MI') ORDER BY sample_id rows BETWEEN unbounded preceding AND unbounded following)) runs
FROM gv$active_session_history
WHERE trunc(sample_time, 'MI') > SYSDATE - 4 / 24
AND inst_id = 1)
GROUP BY update_time
,activity
ORDER BY 1
,2
/
创建活动类型字典表
SELECT * FROM zyf_active_type;
数据如下：
NAME ORDERID
CPU 1
Scheduler 2
User I/O 3
System I/O 4
Concurrency 5
Application 6
Commit 7
Configuration 8
Administrative 9
Network 10
Cluster 11
Other 12
创建视图：
功能：把v_zyf_active_session未统计到的活动类型，补上0值。
因为geom_area如果在某个时间点发现没有某一类型的值时，会引用上一个类型的值。图象区间会产生空白
orderid 用来在geom_area中指定排序
CREATE OR REPLACE VIEW V_ZYF_ACTIVE_SESSION_ALL AS
SELECT a."UPDATE_TIME",a."ACTIVITY",a."NUM"
,b.orderid
FROM (SELECT update_time
,NAME activity
,SUM(num_2) num
FROM (SELECT update_time
,NAME
,decode(activity, NAME, num, 0) num_2
,orderid
FROM (SELECT *
FROM v_zyf_active_session
,zyf_active_type))
GROUP BY update_time
,NAME) a
,zyf_active_type b
WHERE a.activity = b.name
ORDER BY 1
,orderid;
/
CREATE OR REPLACE VIEW V_ZYF_ACTIVE_SESSION_AAS AS
SELECT a."UPDATE_TIME"
,a."ACTIVITY"
,round(a."NUM" /runs,2) AVG_NUM
,orderid
FROM (SELECT update_time
,NAME activity
,SUM(num_2) num
,MAX(runs) runs
FROM (SELECT update_time
,NAME
,decode(activity, NAME, num, 0) num_2
,runs
FROM (SELECT *
FROM v_zyf_active_session
,zyf_active_type))
GROUP BY update_time
,NAME) a
,zyf_active_type b
WHERE a.activity = b.name
ORDER BY 1
,orderid
/

复制代码

用R软件的ggplot2包：画出两个图，一个是所有活动会话的activity的等待次数（也可以认为是db_time）
另一个是AAS average active sessions
的代码如下

library(ggplot2)
library(RODBC)
library(scales) # to access breaks/formatting functions
channel <- odbcConnect("***",uid="username",pwd="passwd")
tb_active_session <- sqlQuery(channel,"select * from v_zyf_active_session_all")
tb_active_session_AAS <- sqlQuery(channel,"select * from V_ZYF_ACTIVE_SESSION_AAS")
#所有活动会话数的活动类型的次数展现
#自定义调整图例顺序，
tb_active_session$ACTIVITY=factor(tb_active_session$ACTIVITY,levels=c("Other","Cluster","Network","Administrative","Configuration"
,"Commit","Application","Concurrency","System I/O","User I/O","Scheduler","CPU"))
g<-ggplot(tb_active_session, aes(x=UPDATE_TIME,y=NUM))+ ggtitle("ASH one hour ALL active sessions ACTIVITY")
g+geom_area(aes(fill=ACTIVITY),stat="identity"
,position = "stack",rm=T ) + scale_fill_manual(values=c("#FF6699", "#CCCC99", "#999966","#666666"
, "#663300", "#CC6600", "#CC3300", "#FF0000", "#0099FF", "#0033FF", "#99FF99","#00CC00")) + scale_x_datetime(breaks = date_breaks("10 min"), labels = date_format("%H:%M"))
#AAS 展现
#自定义调整图例顺序，
tb_active_session_AAS$ACTIVITY=factor(tb_active_session_AAS$ACTIVITY,levels=c("Other","Cluster","Network","Administrative","Configuration"
,"Commit","Application","Concurrency","System I/O","User I/O","Scheduler","CPU"))
gAAS<-ggplot(tb_active_session_AAS, aes(x=UPDATE_TIME,y=AVG_NUM))+ ggtitle("ASH one hour AAS average active sessions")
gAAS+geom_area(aes(fill=ACTIVITY),stat="identity"
,position = "stack",rm=T ) + scale_fill_manual(values=c("#FF6699", "#CCCC99", "#999966","#666666"
, "#663300", "#CC6600", "#CC3300", "#FF0000", "#0099FF", "#0033FF", "#99FF99","#00CC00")) + scale_x_datetime(breaks = date_breaks("10 min"), labels = date_format("%H:%M"))