透過crontab shell獲取分鐘級別的oracle db time效能指標趨勢變化

測試結論


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> 
</eof
</eof