[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点分析时，对于重新掌控对象影响很大。

时间： 2024-08-21 20:49:28

[20151201]统计分析与GRD.txt的相关文章

[20160516]统计分析参数method_opt.txt

[20160516]统计分析参数method_opt.txt --统计分析参数method_opt我个人感觉是最能折腾人的参数.我自己曾经在这个参数上栽过跟头,通过例子来说明看看. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ------------------------------

[20151203]关于grd对性能影响.txt

[20151203]关于grd对性能影响.txt --前几天写了1篇,统计分析对grd的影响,提到一些大表在晚上分析后会出现资源重新分配,参考链接 --blog.itpub.net/267265/viewspace-1851145/ --我们的生产系统业务并不是很忙,今天做一点"危险"的测试,让另外1个实例掌控某些另外实例经常访问的对象. --先看看没有切换的awr报表(昨天的10-11点)rac部分: RAC Statistics  DB/Inst: xxxx/xxxx1  Snap

[20151212]审计统计分析.txt

[20151212]审计统计分析.txt --昨天看了一个帖子,连接如下: http://www.itpub.net/thread-2048266-1-1.html --对方在脚本有如下语句:    EXECUTE IMMEDIATE          'begin dbms_stats.set_index_stats(ownname  => '''       || SOWNER       || ''',indname  => '''       || SIDXNAME       ||

搜索引擎蜘蛛爬行统计分析

一.搜索引擎蜘蛛爬行统计分析的必要性: 蜘蛛顺利爬行网页是网页被搜索引擎收录的前提条件,搜索引擎是否已爬行了网站,爬行了哪些页面以及返回哪些信息给蜘蛛,掌握了这些情况我们才能对网站进行有针对性的优化改良,因此查看蜘蛛爬行日志是一件非常重要但却非常痛苦的工作,尤其是SEOER及新站站长来说.比如从网上看到蜘蛛爬行某个页面后如果返回200 0 64 ,则表示此网页极有可能要被搜索引擎删除,HEAD 请求返回404也表示要删除网页,如果我们能根据日志及时发现此现象就能根据实际情况及时做出调整.又如,蜘

【基于WPF+OneNote+Oracle的中文图片识别系统阶段总结】之篇三：批量处理后的txt文件入库处理

篇三:批量处理后的txt文件入库处理 [开篇概述]:本文继上述文章再做深度剖析,本篇主要介绍txt入库(oracle)和wpf分页,在此篇涉及的页面设计前篇已经细述不再概说.里面涉及到oracle 64位系统安装处理问题,以前oracle的表,序列,触发器,自增种子等基本操作.外加文件操作和利用用户控件分页等技术.大致效果图如下,其次粘贴完整代码,其后就一一概说本文核心要点和难点突破,最后梳理技术知识点,使其一则保持完整性,二则梳理整理知识点便于随时取用.(本篇暂做效果图如下,至于最终效果图和审

[20161216]关于library cache lock.txt

[20161216]关于library cache lock.txt --这几天一直在关注这个链接,http://www.itpub.net/thread-2073170-1-1.html --就是library cache lock导致挂死业务,一般引起这个问题编译包,而应用正好在使用执行这个包,以及11g口令大小写导致无法登录的问题, --我自己很久以前也遇到过一些,那时的系统是10g,http://www.itpub.net/thread-1842463-1-1.html,但是只要分析某个

[20151202]表统计信息stale百分比.txt

[20151202]表统计信息stale百分比.txt --昨天被别人问及一个问题缺省如果某个表修改信息超过10%,oracle即认为这个表需要重新统计分析. --这个百分比如何计算的,实际上只要自己仔细观察就可以确定oracle如何算的. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ --------------

[20151214]显示统计分析的最大与最小值

[20151214]显示统计分析的最大与最小值.txt --昨天看了一个链接: http://www.pythian.com/blog/oracle-internal-datatype-storage/ col low_value format a20 col high_value format a20 col table_name format a10 head 'TABLE' col data_type format a20 col column_name format a6 head 'C

[20151226]统计信息的保存时间.txt

[20151226]统计信息的保存时间.txt --前一阵子写了一篇审计统计分析的文章 --链接:http://blog.itpub.net/267265/viewspace-1870823/ --里面提到:视图记录了分析的历史记录DBA_TAB_STATS_HISTORY;,我提到保存1个月. --可以查询SYS.OPTSTAT_HIST_CONTROL$视图. SYS@test> select sname,sval1,sval2 from SYS.OPTSTAT_HIST_CONTROL$