[20151201]統計分析與GRD.txt
[20151201]統計分析與GRD.txt
--上午測試了手工實現資源掌控。下午看看那個物件出現REMASTER_CNT次數最多。
1.環境:
SYS@xxxx1> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@xxxx1> select * from (select * from GV$GCSPFMASTER_INFO order by REMASTER_CNT desc) where rownum<=10 ;
INST_ID FILE_ID DATA_OBJECT_ID GC_MASTERIN CURRENT_MASTER PREVIOUS_MASTER REMASTER_CNT
---------- ---------- -------------- ----------- -------------- --------------- ------------
1 0 94701 Affinity 1 0 30
1 0 93703 Affinity 0 1 29
2 0 94701 Affinity 1 0 29
2 0 93703 Affinity 0 1 28
1 0 97086 Affinity 0 1 15
2 0 97086 Affinity 0 1 14
1 0 97164 Affinity 1 0 12
1 0 92546 Affinity 0 1 11
1 0 97163 Affinity 0 1 11
1 0 97160 Affinity 0 1 11
10 rows selected.
--可以發現最多的集中在DATA_OBJECT_ID=94701,93703.
2.相關分析:
SYS@xxxx1> column owner noprint
SYS@xxxx1> column SUBOBJECT_NAME noprint
SYS@xxxx1> select * from dba_objects where data_object_id in (94701,93703);
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
-------------------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
EMR_YZB 93703 93703 TABLE 2014-10-29 09:59:34 2014-12-26 11:51:42 2014-10-29:09:59:36 VALID N N N 1
MS_CF01 94701 94701 TABLE 2014-10-29 11:12:18 2015-08-13 09:54:13 2015-03-16:19:03:50 VALID N N N 1
--我有執行如下：
SYS@xxxx1> SELECT * FROM dba_objects WHERE data_object_id IN (94701 ,93703 ,94701 ,93703 ,97086 ,97086 ,97164 ,92546 ,97163 ,97160) ;
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
-------------------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- -------------
EMR_BL_BLXG 92546 92546 TABLE 2014-10-28 21:27:51 2014-11-03 10:14:23 2014-10-28:21:27:51 VALID N N N 1
EMR_YZB 93703 93703 TABLE 2014-10-29 09:59:34 2014-12-26 11:51:42 2014-10-29:09:59:36 VALID N N N 1
MS_CF01 94701 94701 TABLE 2014-10-29 11:12:18 2015-08-13 09:54:13 2015-03-16:19:03:50 VALID N N N 1
ZY_BQYZ 97086 97086 TABLE 2014-10-29 11:35:06 2015-09-18 11:52:24 2015-03-12:17:24:26 VALID N N N 1
IDX_ZY_FYMX_FYRQ 97160 97160 INDEX 2014-10-29 11:53:29 2014-10-29 11:53:29 2014-10-29:11:53:29 VALID N N N 4
I_ZY_FYMX_JFRQ 97163 97163 INDEX 2014-10-29 11:56:14 2014-10-29 11:56:14 2014-10-29:11:56:14 VALID N N N 4
I_ZY_FYMX_ZYH_JSCS 97164 97164 INDEX 2014-10-29 11:57:19 2014-10-29 11:57:19 2014-10-29:11:57:19 VALID N N N 4
7 rows selected.
--發現基本都是大表(除了幾個索引)，為什麼呢？我們的應用分割很好，訪問這些表基本在例項1.
--集中分析DATA_OBJECT_ID=94701的情況。
SELECT inst_id
,policy_event
,data_object_id
,target_instance_number
,event_date, substr(event_date,12,5) hhmm,to_char(to_date(event_date,'mm/dd/yyyy hh24:mi:ss'),'d') week
FROM gv$policy_history
WHERE data_object_id =94701
ORDER BY data_object_id, event_date desc ,inst_id;
INST_ID POLICY_EVENT DATA_OBJECT_ID TARGET_INSTANCE_NUMBER EVENT_DATE HHMM W
---------- -------------------- -------------- ---------------------- -------------------- ---------- -
1 push_affinity 94701 2 12/01/2015 07:56:09 07:56 3
2 push_affinity 94701 1 11/12/2015 08:19:38 08:19 5
1 push_affinity 94701 2 11/10/2015 22:07:01 22:07 3
2 push_affinity 94701 1 11/02/2015 16:01:07 16:01 2
1 push_affinity 94701 2 10/28/2015 22:02:47 22:02 4
2 push_affinity 94701 1 10/04/2015 17:27:41 17:27 1
1 push_affinity 94701 2 10/01/2015 22:01:49 22:01 5
2 push_affinity 94701 1 09/21/2015 08:10:32 08:10 2
1 push_affinity 94701 2 09/19/2015 14:06:48 14:06 7
2 push_affinity 94701 1 08/14/2015 16:43:47 16:43 6
1 push_affinity 94701 2 08/11/2015 22:08:32 22:08 3
2 push_affinity 94701 1 08/11/2015 11:27:34 11:27 3
1 push_affinity 94701 2 08/07/2015 09:48:58 09:48 6
2 push_affinity 94701 1 08/04/2015 01:11:50 01:11 3
1 push_affinity 94701 2 08/03/2015 17:51:03 17:51 2
2 push_affinity 94701 1 07/27/2015 18:05:17 18:05 2
1 push_affinity 94701 2 07/22/2015 22:04:03 22:04 4
2 push_affinity 94701 1 07/21/2015 08:20:44 08:20 3
1 push_affinity 94701 2 07/10/2015 22:06:27 22:06 6
2 push_affinity 94701 1 07/05/2015 08:54:24 08:54 1
1 push_affinity 94701 2 06/30/2015 22:04:45 22:04 3
2 push_affinity 94701 1 06/26/2015 09:23:37 09:23 6
1 push_affinity 94701 2 06/24/2015 11:38:43 11:38 4
2 push_affinity 94701 1 06/09/2015 11:31:54 11:31 3
1 push_affinity 94701 2 06/05/2015 22:03:50 22:03 6
2 push_affinity 94701 1 04/27/2015 06:00:58 06:00 2
1 push_affinity 94701 2 04/14/2015 22:03:01 22:03 3
2 push_affinity 94701 1 04/09/2015 06:00:00 06:00 5
1 push_affinity 94701 2 04/03/2015 22:07:49 22:07 6
29 rows selected.
--注意看時間,單獨查詢target_instance_number=2的情況：
SELECT inst_id
,policy_event
,data_object_id
,target_instance_number
,event_date, substr(event_date,12,5) hhmm,to_char(to_date(event_date,'mm/dd/yyyy hh24:mi:ss'),'d') week
FROM gv$policy_history
WHERE data_object_id =94701 and target_instance_number=2
ORDER BY data_object_id, event_date desc ,inst_id;
INST_ID POLICY_EVENT DATA_OBJECT_ID TARGET_INSTANCE_NUMBER EVENT_DATE HHMM W
---------- -------------------- -------------- ---------------------- -------------------- ---------- -
1 push_affinity 94701 2 12/01/2015 07:56:09 07:56 3
1 push_affinity 94701 2 11/10/2015 22:07:01 22:07 3
1 push_affinity 94701 2 10/28/2015 22:02:47 22:02 4
1 push_affinity 94701 2 10/01/2015 22:01:49 22:01 5
1 push_affinity 94701 2 09/19/2015 14:06:48 14:06 7
1 push_affinity 94701 2 08/11/2015 22:08:32 22:08 3
1 push_affinity 94701 2 08/07/2015 09:48:58 09:48 6
1 push_affinity 94701 2 08/03/2015 17:51:03 17:51 2
1 push_affinity 94701 2 07/22/2015 22:04:03 22:04 4
1 push_affinity 94701 2 07/10/2015 22:06:27 22:06 6
1 push_affinity 94701 2 06/30/2015 22:04:45 22:04 3
1 push_affinity 94701 2 06/24/2015 11:38:43 11:38 4
1 push_affinity 94701 2 06/05/2015 22:03:50 22:03 6
1 push_affinity 94701 2 04/14/2015 22:03:01 22:03 3
1 push_affinity 94701 2 04/03/2015 22:07:49 22:07 6
15 rows selected.
--可以發現一個規律，出現的時間22：XX居多，也就是因為我們的系統例項2負載輕，晚上10點正好是分析統計資訊的時間，也就是大部分分析我估計都是例項2進行.
--如何確定這個時間正好分析這些表呢？
SYS@xxxx1> select * from DBA_TAB_STATS_HISTORY where table_name='MS_CF01';
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME STATS_UPDATE_TIME
---------- ------------------------------ ------------------------------ ----------------------------
MS_CF01 2015-11-10 22:03:29.943929
MS_CF01 2015-11-23 22:01:40.346709
--儲存的資訊僅僅1個月。還是充分說明問題，看日期2015-11-10 22點基本對上。
3.再看看data_object_id = 93703的情況：
SELECT inst_id
,policy_event
,data_object_id
,target_instance_number
,event_date, substr(event_date,12,5) hhmm,to_char(to_date(event_date,'mm/dd/yyyy hh24:mi:ss'),'d') week
FROM gv$policy_history
WHERE data_object_id =93703 --and target_instance_number=2
ORDER BY data_object_id, event_date desc ,inst_id;
INST_ID POLICY_EVENT DATA_OBJECT_ID TARGET_INSTANCE_NUMBER EVENT_DATE HHMM W
---------- -------------------- -------------- ---------------------- -------------------- ---------- -
2 push_affinity 93703 1 11/10/2015 08:46:03 08:46 3
1 push_affinity 93703 2 11/09/2015 22:05:26 22:05 2
2 push_affinity 93703 1 10/11/2015 20:42:17 20:42 1
1 push_affinity 93703 2 10/10/2015 14:09:43 14:09 7
2 push_affinity 93703 1 08/08/2015 17:31:41 17:31 7
1 push_affinity 93703 2 08/08/2015 14:11:31 14:11 7
2 push_affinity 93703 1 07/27/2015 08:54:40 08:54 2
1 push_affinity 93703 2 07/25/2015 10:09:57 10:09 7
2 push_affinity 93703 1 07/11/2015 02:16:45 02:16 7
1 push_affinity 93703 2 07/10/2015 22:06:27 22:06 6
2 push_affinity 93703 1 06/27/2015 02:25:33 02:25 7
1 push_affinity 93703 2 06/26/2015 22:04:53 22:04 6
2 push_affinity 93703 1 06/12/2015 02:17:44 02:17 6
1 push_affinity 93703 2 06/11/2015 22:07:12 22:07 5
2 push_affinity 93703 1 05/29/2015 02:15:33 02:15 6
1 push_affinity 93703 2 05/28/2015 22:04:54 22:04 5
2 push_affinity 93703 1 05/15/2015 02:23:01 02:23 6
1 push_affinity 93703 2 05/14/2015 22:02:25 22:02 5
2 push_affinity 93703 1 04/18/2015 00:29:28 00:29 7
1 push_affinity 93703 2 04/17/2015 22:09:15 22:09 6
2 push_affinity 93703 1 04/05/2015 02:20:45 02:20 1
1 push_affinity 93703 2 04/04/2015 22:10:27 22:10 7
2 push_affinity 93703 1 01/29/2015 10:31:22 10:31 5
1 push_affinity 93703 2 01/28/2015 22:10:01 22:10 4
2 push_affinity 93703 1 01/19/2015 15:17:53 15:17 2
1 push_affinity 93703 2 01/16/2015 22:02:11 22:02 6
2 push_affinity 93703 1 01/06/2015 16:26:02 16:26 3
1 push_affinity 93703 2 01/05/2015 22:04:08 22:04 2
28 rows selected.
SELECT inst_id
,policy_event
,data_object_id
,target_instance_number
,event_date, substr(event_date,12,5) hhmm,to_char(to_date(event_date,'mm/dd/yyyy hh24:mi:ss'),'d') week
FROM gv$policy_history
WHERE data_object_id =93703 and target_instance_number=2
ORDER BY data_object_id, event_date desc ,inst_id;
INST_ID POLICY_EVENT DATA_OBJECT_ID TARGET_INSTANCE_NUMBER EVENT_DATE HHMM W
---------- -------------------- -------------- ---------------------- -------------------- ---------- -
1 push_affinity 93703 2 11/09/2015 22:05:26 22:05 2
1 push_affinity 93703 2 10/10/2015 14:09:43 14:09 7
1 push_affinity 93703 2 08/08/2015 14:11:31 14:11 7
1 push_affinity 93703 2 07/25/2015 10:09:57 10:09 7
1 push_affinity 93703 2 07/10/2015 22:06:27 22:06 6
1 push_affinity 93703 2 06/26/2015 22:04:53 22:04 6
1 push_affinity 93703 2 06/11/2015 22:07:12 22:07 5
1 push_affinity 93703 2 05/28/2015 22:04:54 22:04 5
1 push_affinity 93703 2 05/14/2015 22:02:25 22:02 5
1 push_affinity 93703 2 04/17/2015 22:09:15 22:09 6
1 push_affinity 93703 2 04/04/2015 22:10:27 22:10 7
1 push_affinity 93703 2 01/28/2015 22:10:01 22:10 4
1 push_affinity 93703 2 01/16/2015 22:02:11 22:02 6
1 push_affinity 93703 2 01/05/2015 22:04:08 22:04 2
14 rows selected.
--這個更明顯，3個不是22點的都是星期7，也可能是統計分析時間。因為window_name=> 'SUNDAY_WINDOW'定義如下：
BEGIN
SYS.DBMS_SCHEDULER.CREATE_WINDOW
(
window_name => 'SUNDAY_WINDOW'
,start_date => NULL
,repeat_interval => 'freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0'
,end_date => NULL
,resource_plan => 'DEFAULT_MAINTENANCE_PLAN'
,duration => to_dsInterval('+000 20:00:00')
,window_priority => 'LOW'
,comments => 'Sunday window for maintenance tasks'
);
SYS.DBMS_SCHEDULER.ENABLE
(name => 'SYS.SUNDAY_WINDOW');
END;
/
-- 6點開始，持續20小時。
SYS@xxxx1> select * from DBA_TAB_STATS_HISTORY where table_name='EMR_YZB';
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME STATS_UPDATE_TIME
---------- ------------------------------ ------------------------------ --------------------------
EMR_YZB 2015-11-09 22:27:18.527761
EMR_YZB 2015-11-25 22:24:24.204653
--不過這個有點奇怪的是，切換髮生在統計分析之前(2015-11-09 22:27:18) .另外2015-11-25並沒有出現remaster，
--不過檢查awr報表，可以這個時段是例項1上執行了表統計分析,摘要如下：
Elapsed Time (s) Executions Elapsed Time per Exec (s) %Total %CPU %IO SQL Id SQL Module SQL Text
----------------- ---------- ------------- ------------- ------ ----- ------------------- -------------- ---------------------------------
1,467.15 1 1,467.15 47.85 45.57 60.77 b6usrg82hwsa3 DBMS_SCHEDULER call dbms_stats.gather_databas...
1,417.34 1 1,417.34 46.23 44.89 61.96 8ygw5pf8y8xcj DBMS_SCHEDULER /* SQL Analyze(1) */ select /...
Segments by Logical Reads DB/Inst: DBCN/dbcn1 Snaps: 9437-9438
-> Total Logical Reads: 74,972,654
-> Captured Segments account for 91.7% of Total
Tablespace Subobject Obj. Logical
Owner Name Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
XXXXXX_XXX XXXXXX_XXX EMR_YZB TABLE 30,072,144 40.11
XXXXXX_XXX XXXXXX_XXX MS_YYHY TABLE 12,294,768 16.40
XXXXXX_XXX XXXXXX_XXX CP_YZMX TABLE 4,580,368 6.11
XXXXXX_XXX XXXXXX_XXX YS_ZY_HZYQ TABLE 3,003,152 4.01
XXXXXX_XXX XXXXXX_XXX ZY_FYMX TABLE 2,754,160 3.67
------------------------------------------------------
--並沒有出現remaster。
4.當然以上的分析非常的牽強。
SELECT SUBSTR (event_date, 12, 2) hhmm, target_instance_number, COUNT (*)
FROM gv$policy_history
GROUP BY SUBSTR (event_date, 12, 2), target_instance_number
ORDER BY 1;
HHMM TARGET_INSTANCE_NUMBER COUNT(*)
---- ---------------------- ----------
00 1 3
01 1 3
02 1 13
02 2 1
03 1 14
03 2 1
04 1 3
05 1 1
06 1 2
06 2 4
07 1 2
07 2 5
08 1 36
08 2 7
09 1 16
09 2 7
10 1 30
10 2 12
11 1 8
11 2 7
12 1 8
12 2 3
14 1 11
14 2 19
15 1 16
15 2 2
16 1 26
16 2 4
17 1 20
17 2 5
18 1 19
18 2 10
19 1 1
19 2 2
20 1 3
22 1 109
22 2 244
23 1 2
38 rows selected.
--從以上結果也可以說明22點集中出現次數最多。
--基本還是可以得出一個結論，當一些大表分析在晚上22點分析時，對於重新掌控物件影響很大。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1851145/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 微服務治理與統計分析微服務
- 如何進行系統分析與設計
- R——助力大資料統計與分析大資料
- LevelDB系統結構與設計思路分析
- 系統分析與設計-Lesson8-Homework
- 數學-概率與統計-數理統計-總結(四)：方差分析及迴歸分析
- 線上消費行為統計與分析系統設計和實現
- 多元統計分析01：多元統計分析基礎
- 網站日誌統計案例分析與實現網站
- Hive（統計分析）Hive
- 軟體設計雜談（一）--需求分析與系統設計 (轉)
- 熱詞統計分析
- matlab統計分析Matlab
- ORACLE的統計分析Oracle
- 分析函式——統計函式
- 電商系統商品資料表設計分析與總結
- 片上系統晶片設計與靜態時序分析晶片
- 電商系統中的商品模型的分析與設計—續模型
- 基於UML的資訊系統分析與設計 (轉)
- Python文字統計與分析從基礎到進階Python
- [20151201]Manual Remastering of ObjectsREMASTObject
- 機器學習之統計分析(2)機器學習
- 機器學習之統計分析(1)機器學習
- oracle 統計分析函式Oracle函式
- 量表設計與分析實戰
- 【一文秒懂】電商系統商品模組初建分析與設計
- 2015年北京市演出市場統計與分析
- ORACLE表統計資訊與列統計資訊、索引統計資訊Oracle索引
- ORACLE表統計資訊與列統計資訊Oracle
- IT系統的業務模型分析與系統建模模型
- 空間統計（二）分析模式 A模式
- 古典密碼的統計分析密碼
- 大量STATSPACK資料統計分析
- 密碼統計分析工具pipal密碼
- 統計分析注意事項1
- 燈；及陣列統計分析陣列
- oracle之autotrace統計資訊分析Oracle
- 分析函式之排名統計函式