测试结论
1,可以通过crontab以指定时间间隔，比如2分钟或5分钟不停执行，把v$sys_time_model的关于DB TIME性能数据转储到一个自定义创建的临时表中
2,然后通过分析函数就可以实时监听DB TIME的分钟级别的变化趋势了，而AWR则作不到这一点
测试明细
1，数据库版本
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2，DB TIME视图
SQL> desc v$sys_time_model;
Name Null? Type
----------------------------------------- -------- ----------------------------
STAT_ID NUMBER
STAT_NAME VARCHAR2(64)
VALUE NUMBER
3，创建临时中转表
SQL> create table real_db_time(oper_id int,curr_date date,stat_name varchar2(64),value number);
Table created.
4,创建序列
SQL> create sequence seq_db_time start with 1 cache 20000;
Sequence created.
5，创建SHELL脚本
[oracle@mygirl ~]$ pwd
/home/oracle
[oracle@mygirl ~]$ mkdir -p shell_dir
[oracle@mygirl ~]$ cd shell_dir/
[oracle@mygirl shell_dir]$
[oracle@mygirl shell_dir]$ more calc_dbtime.sh
#!/bin/bash
source home/oracle/.bash_profile
sqlplus '/as sysdba'<<EOF
insert into real_db_time(oper_id,curr_date,stat_name,value) select seq_db_time.nextval,sysdate,stat_name,value from v$sys_time_model where stat_id=3649082374;
commit;
exit
EOF
6，授权SHELL脚本
[oracle@mygirl shell_dir]$ chmod u+x calc_dbtime.sh
[oracle@mygirl shell_dir]$
7，执行SHELL脚本
[oracle@mygirl shell_dir]$ ./calc_dbtime.sh
SQL*Plus: Release 11.2.0.4.0 Production on Tue May 23 23:53:35 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> insert into real_db_time(curr_date,stat_name,value) select sysdate,stat_name,value from v
*
ERROR at line 1:
ORA-04044: procedure, function, package, or type is not allowed here
SQL>
Commit complete.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@mygirl shell_dir]$
8，根据上述报错调整SHELL脚本
调整shell脚本如下
[oracle@mygirl shell_dir]$ more calc_dbtime.sh
#!/bin/bash
source /home/oracle/.bash_profile
sqlplus '/as sysdba'<<EOF
insert into real_db_time(curr_date,stat_name,value) select sysdate,stat_name,value from v\$sys_time_model where stat_id=3649082374;
commit;
exit
EOF
9,查询中转临时表数据
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> col value for 9999999999999999999999
SQL> set linesize 300
SQL> select * from real_db_time;
CURR_DATE STAT_NAME VALUE
------------------- ---------------------------------------------------------------- -----------------------
2017-05-24 00:01:23 DB time 674478161534
2017-05-24 00:01:27 DB time 674478172707
10,创建每2分钟调度的CRONTAB任务
crontab 文件中的行由 6 个字段组成，不同字段间用空格或 tab 键分隔。前 5 个字段指定命令要运行的时间
分钟 (0-59)
小时 (0-23)
日期 (1-31)
月份 (1-12)
星期几（0-6，其中 0 代表星期日）
第 6 个字段是一个要在适当时间执行的字符串
/home/oracle/shell_dir/calc_dbtime.sh
[oracle@mygirl shell_dir]$ crontab -e
[oracle@mygirl shell_dir]$ crontab -l
*/1 * * * * /home/oracle/shell_dir/calc_dbtime.sh
11,验证crontab功能是否正常运行
SQL> select count(*) from real_db_time;
COUNT(*)
----------
1
SQL>
SQL>
SQL> host date
Wed May 24 00:15:22 CST 2017
SQL> /
COUNT(*)
----------
3
SQL> /
COUNT(*)
----------
4
OPER_ID CURR_DATE STAT_NAME VALUE
---------- ------------------- ---------------------------------------------------------------- -----------------------
1 2017-05-24 00:09:56 DB time 674478633740
2 2017-05-24 00:15:01 DB time 674478685335
3 2017-05-24 00:16:01 DB time 674478701612
4 2017-05-24 00:17:02 DB time 674478899116
5 2017-05-24 00:18:01 DB time 674478915407
12,开始用oracle swingbench进行600用户压测，查看DB TIME的变化趋势
SQL> host date
Wed May 24 00:21:48 CST 2017
SQL> host date
Wed May 24 00:22:05 CST 2017
SQL> /
OPER_ID CURR_DATE STAT_NAME VALUE
---------- ------------------- ---------------------------------------------------------------- -----------------------
1 2017-05-24 00:09:56 DB time 674478633740
2 2017-05-24 00:15:01 DB time 674478685335
3 2017-05-24 00:16:01 DB time 674478701612
4 2017-05-24 00:17:02 DB time 674478899116
5 2017-05-24 00:18:01 DB time 674478915407
6 2017-05-24 00:19:01 DB time 674478932909
7 2017-05-24 00:20:02 DB time 674581050366
8 2017-05-24 00:22:00 DB time 741845940952
9 2017-05-24 00:22:03 DB time 742655387353
9 rows selected.
SQL> /
OPER_ID CURR_DATE STAT_NAME VALUE
---------- ------------------- ---------------------------------------------------------------- -----------------------
1 2017-05-24 00:09:56 DB time 674478633740
2 2017-05-24 00:15:01 DB time 674478685335
3 2017-05-24 00:16:01 DB time 674478701612
4 2017-05-24 00:17:02 DB time 674478899116
5 2017-05-24 00:18:01 DB time 674478915407
6 2017-05-24 00:19:01 DB time 674478932909
7 2017-05-24 00:20:02 DB time 674581050366
8 2017-05-24 00:22:00 DB time 741845940952
9 2017-05-24 00:22:03 DB time 742655387353
10 2017-05-24 00:23:48 DB time 802831010616
10 rows selected.
OPER_ID CURR_DATE STAT_NAME VALUE
---------- ------------------- ---------------------------------------------------------------- -----------------------
1 2017-05-24 00:09:56 DB time 674478633740
2 2017-05-24 00:15:01 DB time 674478685335
3 2017-05-24 00:16:01 DB time 674478701612
4 2017-05-24 00:17:02 DB time 674478899116
5 2017-05-24 00:18:01 DB time 674478915407
6 2017-05-24 00:19:01 DB time 674478932909
7 2017-05-24 00:20:02 DB time 674581050366
8 2017-05-24 00:22:00 DB time 741845940952
9 2017-05-24 00:22:03 DB time 742655387353
10 2017-05-24 00:23:48 DB time 802831010616
11 2017-05-24 00:24:34 DB time 829847635890
OPER_ID CURR_DATE STAT_NAME VALUE
---------- ------------------- ---------------------------------------------------------------- -----------------------
12 2017-05-24 00:25:24 DB time 857823572185
12 rows selected.
set linesize 300
col stat_name for a10
set pagesize 400
col prev_value for 9999999999999999
select
dhs.snap_id end_snap_id,
round(diff_db_time/60000000) db_time,
end_interval_time end_time
from
(
select
snap_id,
stat_name,
value,
prev_value,
(case when value-prev_value<0 then value
else value-prev_value
end) as diff_db_time
from
(
select
oper_id,
stat_name,
value,
lag(value,1,0) over(order by oper_id) prev_value
from real_db_time
)
) newt,
dba_hist_snapshot dhs
where newt.snap_id=dhs.snap_id and
dhs.instance_number=1
order by end_snap_id;
col curr_dbtime for 999999999999999999999999999
select
oper_id,
curr_date,
prev_date,
value,
prev_value,
(value-prev_value) as curr_dbtime
from
(
select
oper_id,
stat_name,
value,
curr_date,
lag(value,1,0) over(order by oper_id) prev_value,
lag(curr_date) over(order by oper_id) prev_date
from real_db_time
)
order by oper_id;
OPER_ID CURR_DATE PREV_DATE VALUE PREV_VALUE CURR_DBTIME
---------- ------------------- ------------------- ----------------------- ----------------- ----------------------------
1 2017-05-24 00:09:56 674478633740 0 674478633740
2 2017-05-24 00:15:01 2017-05-24 00:09:56 674478685335 674478633740 51595
3 2017-05-24 00:16:01 2017-05-24 00:15:01 674478701612 674478685335 16277
4 2017-05-24 00:17:02 2017-05-24 00:16:01 674478899116 674478701612 197504
5 2017-05-24 00:18:01 2017-05-24 00:17:02 674478915407 674478899116 16291
6 2017-05-24 00:19:01 2017-05-24 00:18:01 674478932909 674478915407 17502
7 2017-05-24 00:20:02 2017-05-24 00:19:01 674581050366 674478932909 102117457
8 2017-05-24 00:22:00 2017-05-24 00:20:02 741845940952 674581050366 67264890586
9 2017-05-24 00:22:03 2017-05-24 00:22:00 742655387353 741845940952 809446401
10 2017-05-24 00:23:48 2017-05-24 00:22:03 802831010616 742655387353 60175623263
11 2017-05-24 00:24:34 2017-05-24 00:23:48 829847635890 802831010616 27016625274
OPER_ID CURR_DATE PREV_DATE VALUE PREV_VALUE CURR_DBTIME
---------- ------------------- ------------------- ----------------------- ----------------- ----------------------------
12 2017-05-24 00:25:24 2017-05-24 00:24:34 857823572185 829847635890 27975936295
13 2017-05-24 00:28:55 2017-05-24 00:25:24 971055971162 857823572185 113232398977
14 2017-05-24 00:31:05 2017-05-24 00:28:55 1040640012939 971055971162 69584041777
15 2017-05-24 00:31:23 2017-05-24 00:31:05 1050617328079 1040640012939 9977315140
16 2017-05-24 00:31:23 2017-05-24 00:31:23 1050617328079 1050617328079 0
17 2017-05-24 00:31:23 2017-05-24 00:31:23 1050617328079 1050617328079 0
18 2017-05-24 00:31:23 2017-05-24 00:31:23 1050617328079 1050617328079 0
19 2017-05-24 00:32:03 2017-05-24 00:31:23 1064985078377 1050617328079 14367750298
20 2017-05-24 00:33:01 2017-05-24 00:32:03 1064985230270 1064985078377 151893
21 2017-05-24 00:34:02 2017-05-24 00:33:01 1064985247381 1064985230270 17111
22 2017-05-24 00:35:01 2017-05-24 00:34:02 1064985301077 1064985247381 53696
OPER_ID CURR_DATE PREV_DATE VALUE PREV_VALUE CURR_DBTIME
---------- ------------------- ------------------- ----------------------- ----------------- ----------------------------
23 2017-05-24 00:36:01 2017-05-24 00:35:01 1064985327201 1064985301077 26124
24 2017-05-24 00:37:02 2017-05-24 00:36:01 1064985363491 1064985327201 36290
25 2017-05-24 00:38:01 2017-05-24 00:37:02 1064985390460 1064985363491 26969
26 2017-05-24 00:39:01 2017-05-24 00:38:01 1064985419625 1064985390460 29165
27 2017-05-24 00:40:02 2017-05-24 00:39:01 1064985454106 1064985419625 34481
27 rows selected.
SQL>
