Oracle 查询DB的负载情况

最新推荐文章于 2022-08-14 11:14:59 发布

cixiaobi8104

最新推荐文章于 2022-08-14 11:14:59 发布

阅读量799

点赞数

 
             链接： 
             http://blog.itpub.net/28602568/viewspace-1467897/ 
           
 标题： Oracle 查询DB的负载情况  

             作者： 
            ｌōττéｒｙ©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

参考博客浏览

整理后sql：

SELECT *

FROM ( SELECT A.INSTANCE_NUMBER,

A.SNAP_ID,

B.BEGIN_INTERVAL_TIME + 0 BEGIN_TIME,

B.END_INTERVAL_TIME + 0 END_TIME,

ROUND ( VALUE - LAG ( VALUE , 1 , '0' )

OVER ( ORDER BY A.INSTANCE_NUMBER, A.SNAP_ID)) "DB TIME"

FROM ( SELECT B.SNAP_ID,

INSTANCE_NUMBER,

SUM ( VALUE ) / 1000000 / 60 VALUE

FROM DBA_HIST_SYS_TIME_MODEL B

WHERE B.DBID = ( SELECT DBID FROM V$DATABASE)

AND UPPER (B.STAT_NAME) IN UPPER (( 'DB TIME' ))

GROUP BY B.SNAP_ID, INSTANCE_NUMBER) A,

DBA_HIST_SNAPSHOT B

WHERE A.SNAP_ID = B.SNAP_ID

AND B.DBID = ( SELECT DBID FROM V$DATABASE)

AND B.INSTANCE_NUMBER = A.INSTANCE_NUMBER)

WHERE TO_CHAR(BEGIN_TIME, 'YYYY-MM-DD' ) = TO_CHAR( SYSDATE , 'YYYY-MM-DD' )

ORDER BY BEGIN_TIME;

SQL 结果：

INSTANCE_NUMBER	SNAP_ID	BEGIN_TIME	END_TIME	DB TIME
2	7307	2015/3/20 0:00	2015/3/20 1:00	13
1	7308	2015/3/20 0:59	2015/3/20 1:59	220
2	7308	2015/3/20 1:00	2015/3/20 2:00	2
1	7309	2015/3/20 1:59	2015/3/20 2:59	62
2	7309	2015/3/20 2:00	2015/3/20 3:00	0
1	7310	2015/3/20 2:59	2015/3/20 3:59	61
2	7310	2015/3/20 3:00	2015/3/20 4:00	0
1	7311	2015/3/20 3:59	2015/3/20 4:59	62
2	7311	2015/3/20 4:00	2015/3/20 5:00	0
1	7312	2015/3/20 4:59	2015/3/20 5:59	62
2	7312	2015/3/20 5:00	2015/3/20 6:00	0
1	7313	2015/3/20 5:59	2015/3/20 6:59	86
2	7313	2015/3/20 6:00	2015/3/20 7:00	0
1	7314	2015/3/20 6:59	2015/3/20 7:59	128
2	7314	2015/3/20 7:00	2015/3/20 8:00	4
1	7315	2015/3/20 7:59	2015/3/20 8:59	212
2	7315	2015/3/20 8:00	2015/3/20 9:00	52
1	7316	2015/3/20 8:59	2015/3/20 9:59	238
2	7316	2015/3/20 9:00	2015/3/20 10:00	220
1	7317	2015/3/20 9:59	2015/3/20 10:59	328
2	7317	2015/3/20 10:00	2015/3/20 11:00	441
1	7318	2015/3/20 10:59	2015/3/20 11:59	455
2	7318	2015/3/20 11:00	2015/3/20 12:00	492
1	7319	2015/3/20 11:59	2015/3/20 12:59	371
2	7319	2015/3/20 12:00	2015/3/20 13:00	392
1	7320	2015/3/20 12:59	2015/3/20 13:59	466
2	7320	2015/3/20 13:00	2015/3/20 14:00	442
1	7321	2015/3/20 13:59	2015/3/20 14:59	492
2	7321	2015/3/20 14:00	2015/3/20 15:00	424
1	7322	2015/3/20 14:59	2015/3/20 15:59	457
2	7322	2015/3/20 15:00	2015/3/20 16:00	362
1	7323	2015/3/20 15:59	2015/3/20 16:59	536
2	7323	2015/3/20 16:00	2015/3/20 17:00	418
1	7324	2015/3/20 16:59	2015/3/20 17:59	545
2	7324	2015/3/20 17:00	2015/3/20 18:00	399
1	7325	2015/3/20 17:59	2015/3/20 18:59	332
2	7325	2015/3/20 18:00	2015/3/20 19:00	271
1	7326	2015/3/20 18:59	2015/3/20 19:59	169
2	7326	2015/3/20 19:00	2015/3/20 20:00	112
1	7327	2015/3/20 19:59	2015/3/20 20:59	121
2	7327	2015/3/20 20:00	2015/3/20 21:00	60