11.2.0.3.2中的direct path write temp和ora-1652错误

huagan0523

DB版本:
ACTION_TIME                    ID         ACTION               VERSION    COMMENTS
------------------------------ ---------- -------------------- ---------- ----------------------------------------
14-JUL-12 10.48.02.423325 AM   2          APPLY                11.2.0.3   PSU 11.2.0.3.2
14-JUL-12 12.06.26.890505 PM   2          APPLY                11.2.0.3   PSU 11.2.0.3.2

OS版本：REDHAT 5.8 (x86-64bit)
DB是单机。

每天凌晨0点-2点半左右，监控会出现大量ora-1652的告警，经查证，都是以下等待事件和SQL造成,下面的temp字段是每条SQL消耗的temp大小。

--direct path write temp事件信息
select to_char(a.SAMPLE_TIME, 'yyyy-mm-dd hh24:mi:ss') sample_time,
       c.username,
       a.SESSION_ID,
       a.SESSION_SERIAL#,
       a.instance_number,a.program,a.module,a.blocking_session,a.temp_space_allocated/1024/1024 temp,
       a.event,
       b.SQL_TEXT
  from DBA_HIST_ACTIVE_SESS_HISTORY a, DBA_HIST_SQLTEXT b, dba_users c
where a.SQL_ID = b.SQL_ID and a.sample_time between to_date('2013-6-16 00:00:01','yyyy-mm-dd hh24:mi:ss')
and to_date('2013-6-16 03:30:01','yyyy-mm-dd hh24:mi:ss')
   and a.user_id = c.user_id
   and a.instance_number = 1
   --and snap_id between 8881 and 8882
   and a.event like '%temp%';

sample_time         username SESSION_ID SESSION_SERIAL#  instance_number program                  module        blocking_session temp  event                  SQL_id
-----------         -------- ---------- ---------------  --------------- -------                  ------------  ---------------- ----- ---------------------  --------
2013-06-16 00:50:20        SYS             13                29033                   1                     oracle@cnsz081021 (M000)        MMON_SLAVE                                 9564         direct path write temp        8m2sw3z7kt6ph
2013-06-16 00:49:40        SYS             13                29033                   1                     oracle@cnsz081021 (M000)        MMON_SLAVE                                 7239         direct path write temp        8m2sw3z7kt6ph
2013-06-16 00:49:20        SYS             13                29033                   1                     oracle@cnsz081021 (M000)        MMON_SLAVE                                 6032         direct path write temp        8m2sw3z7kt6ph
2013-06-16 00:49:00        SYS             13                29033                   1                     oracle@cnsz081021 (M000)        MMON_SLAVE                                 4892         direct path write temp        8m2sw3z7kt6ph
2013-06-16 00:48:50        SYS             13                29033                   1                     oracle@cnsz081021 (M000)        MMON_SLAVE                                 4310         direct path write temp        8m2sw3z7kt6ph
2013-06-16 00:47:50        SYS             13                29033                   1                     oracle@cnsz081021 (M000)        MMON_SLAVE                                 1041         direct path write temp        8m2sw3z7kt6ph
2013-06-16 00:47:40        SYS             13                29033                   1                     oracle@cnsz081021 (M000)        MMON_SLAVE                                 475         direct path write temp        8m2sw3z7kt6ph
2013-06-16 00:47:30        SYS             13                29033                   1                     oracle@cnsz081021 (M000)        MMON_SLAVE                                 9556         direct path write temp        2d1p0p5k3f8fu
2013-06-16 00:47:20        SYS             13                29033                   1                     oracle@cnsz081021 (M000)        MMON_SLAVE                                 8980         direct path write temp        2d1p0p5k3f8fu
2013-06-16 00:46:10        SYS             13                29033                   1                     oracle@cnsz081021 (M000)        MMON_SLAVE                                 4957         direct path write temp        2d1p0p5k3f8fu
2013-06-16 00:45:40        SYS             13                29033                   1                     oracle@cnsz081021 (M000)        MMON_SLAVE                                 3376         direct path write temp        2d1p0p5k3f8fu
2013-06-16 00:45:10        SYS             13                29033                   1                     oracle@cnsz081021 (M000)        MMON_SLAVE                                 1732         direct path write temp        2d1p0p5k3f8fu
2013-06-16 00:44:49        SYS             13                29033                   1                     oracle@cnsz081021 (M000)        MMON_SLAVE                                 521         direct path write temp        2d1p0p5k3f8fu
2013-06-16 00:44:39        SYS             13                29033                   1                     oracle@cnsz081021 (M000)        MMON_SLAVE                                 40           direct path write temp        2d1p0p5k3f8fu
2013-06-16 02:03:00        DBMGR           26                21513                   1                     oracle@cnsz081021 (J001)        DBMS_SCHEDULER                             4343         direct path write temp        7jdjp940pj0z0
2013-06-16 02:02:00        DBMGR           26                21513                   1                     oracle@cnsz081021 (J001)        DBMS_SCHEDULER                             306         direct path write temp        7jdjp940pj0z0
2013-06-16 02:01:50        DBMGR           26                21513                   1                     oracle@cnsz081021 (J001)        DBMS_SCHEDULER                             3475         direct path write temp        7jdjp940pj0z0
2013-06-16 02:01:40        DBMGR           26                21513                   1                     oracle@cnsz081021 (J001)        DBMS_SCHEDULER                             3127         direct path write temp        7jdjp940pj0z0
2013-06-16 02:01:20        DBMGR           26                21513                   1                     oracle@cnsz081021 (J001)        DBMS_SCHEDULER                             1736         direct path write temp        7jdjp940pj0z0
2013-06-16 02:01:10        DBMGR           26                21513                   1                     oracle@cnsz081021 (J001)        DBMS_SCHEDULER                             1038         direct path write temp        7jdjp940pj0z0
2013-06-16 02:01:00        DBMGR           26                21513                   1                     oracle@cnsz081021 (J001)        DBMS_SCHEDULER                             517         direct path write temp        7jdjp940pj0z0
2013-06-16 02:00:50        DBMGR           26                21513                   1                     oracle@cnsz081021 (J001)        DBMS_SCHEDULER                             1941         direct path write temp        7jdjp940pj0z0
2013-06-16 02:00:30        DBMGR           26                21513                   1                     oracle@cnsz081021 (J001)        DBMS_SCHEDULER                             562         direct path write temp        7jdjp940pj0z0
2013-06-16 02:00:20        DBMGR           26                21513                   1                     oracle@cnsz081021 (J001)        DBMS_SCHEDULER                             943         direct path write temp        7jdjp940pj0z0

SQL
8m2sw3z7kt6ph：
select p, NULL, NULL from (select count(*) p from v$rman_status  where operation = 'BACKUP COPYROLLFORWARD');  

2d1p0p5k3f8fu:
select p, NULL, NULL from (select count(*) p from v$rman_status  where operation = 'BLOCK MEDIA RECOVERY');

7jdjp940pj0z0:
/* SQL Analyze(26,1) */ SELECT /*+RULE*/ TO_CHAR(STAMP) || '_' || TO_CHAR(RECID) BACKUP_ID, SESSION_STAMP, SESSION_RECID,
START_TIME,END_TIME, STATUS BACKUP_STATUS,OBJECT_TYPE BACKUP_TYPE                                                                                       
FROM SYS.V_$RMAN_STATUS  WHERE OPERATION='BACKUP'                                                                        
AND (END_TIME > SYSDATE-:1 /1000/60/60/24 or (END_TIME is null and START_TIME > SYSDATE-:2 /1000/60/60/24));

huagan0523

执行SQL2d1p0p5k3f8fu可以重现ora-1652，其执行计划如下:
SQL_ID  2d1p0p5k3f8fu, child number 0
-------------------------------------
select p, NULL, NULL from (select count(*) p from v$rman_status  where
operation = 'BLOCK MEDIA RECOVERY')

Plan hash value: 532935667

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |     6 (100)|          |
|   1 |  VIEW                        |           |     1 |    13 |     6 (100)| 00:00:01 |
|   2 |   SORT AGGREGATE             |           |     1 |   125 |            |          |
|*  3 |    FILTER                    |           |       |       |            |          |
|*  4 |     HASH JOIN OUTER          |           |     2 |   250 |     6 (100)| 00:00:01 |
|   5 |      MERGE JOIN CARTESIAN    |           |     2 |   136 |     5 (100)| 00:00:01 |
|   6 |       MERGE JOIN CARTESIAN   |           |     1 |    52 |     5 (100)| 00:00:01 |
|   7 |        VIEW                  |           |     1 |    26 |     3 (100)| 00:00:01 |
|   8 |         HASH GROUP BY        |           |     1 |    75 |     3 (100)| 00:00:01 |
|*  9 |          HASH JOIN OUTER     |           |     2 |   150 |     2 (100)| 00:00:01 |
|  10 |           FIXED TABLE FULL   | X$KCCRSR  |     2 |    20 |     0   (0)|          |
|  11 |           VIEW               |           |     1 |    65 |     1 (100)| 00:00:01 |
|  12 |            HASH GROUP BY     |           |     1 |    65 |     1 (100)| 00:00:01 |
|  13 |             FIXED TABLE FULL | X$KSFQP   |     1 |    65 |            |          |
|  14 |        BUFFER SORT           |           |     1 |    26 |     5 (100)| 00:00:01 |
|  15 |         VIEW                 |           |     1 |    26 |     3 (100)| 00:00:01 |
|  16 |          HASH UNIQUE         |           |     1 |    46 |     3 (100)| 00:00:01 |
|* 17 |           HASH JOIN OUTER    |           |     2 |    92 |     2 (100)| 00:00:01 |
|  18 |            FIXED TABLE FULL  | X$KCCRSR  |     2 |    20 |     0   (0)|          |
|  19 |            VIEW              |           |     1 |    36 |     1 (100)| 00:00:01 |
|  20 |             WINDOW SORT      |           |     1 |    49 |     1 (100)| 00:00:01 |
|* 21 |              FIXED TABLE FULL| X$KSFQP   |     1 |    49 |            |          |
|  22 |       BUFFER SORT            |           |     2 |    32 |     3 (100)| 00:00:01 |
|  23 |        FIXED TABLE FULL      | X$KCCRSR  |     2 |    32 |     0   (0)|          |
|* 24 |      FIXED TABLE FULL        | X$KRBMRST |     1 |    57 |            |          |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter((NVL(UPPER("OPER_KRBMRST"),UPPER("R2"."RSROP"))='BLOCK MEDIA
              RECOVERY' AND "HH"."RECID"=NVL("ID_KRBMRST","R2"."RSRRID") AND
              "HH"."STAMP"=NVL("STAMP_KRBMRST","R2"."RSRTST") AND
              "ODEV"."RECID"=NVL("ID_KRBMRST","R2"."RSRRID") AND
              "ODEV"."STAMP"=NVL("STAMP_KRBMRST","R2"."RSRTST")))
   4 - access("R2"."RSRRID"="ID_KRBMRST" AND "R2"."RSRTST"="STAMP_KRBMRST")
   9 - access("R"."RSRRID"="RS"."RMAN_STATUS_RECID" AND
              "R"."RSRTST"="RS"."RMAN_STATUS_STAMP")
  17 - access("R"."RSRRID"="RS"."RMAN_STATUS_RECID" AND
              "R"."RSRTST"="RS"."RMAN_STATUS_STAMP")
  21 - filter("TYPE"=2)
  24 - filter(("STATUS_KRBMRST"=1 OR "STATUS_KRBMRST"=9 OR "STATUS_KRBMRST"=17 OR
              "STATUS_KRBMRST"=25))

经分析，这些X$的基表的统计信息过旧，导致执行计划出现笛卡尔积，临时对这几个基表进行统计信息的收集，目前已临时解决了问题，不过现在领导要找出根源，即到底是什么job或者是11G 的什么新特性造成每天在定时执行这些任务，而导致问题的出现，如果是新特性或是什么job，能否直接禁用掉。

查询资料，发现11G中有个“11G新特性-控制文件延迟自动备份 ”的新特性，由于出问题的SQL都涉及到v$rman_status，怀疑还是某个和备份的新特性相关，请大牛们帮忙查证下原因。

psufnxk2000

select p, NULL, NULL from (select count(*) p from v$rman_status  where
operation = 'BLOCK MEDIA RECOVERY')   

这个sql,无论如何 ，执行结果 都是一样的。   要这个结果的意义是什么呢？？

huagan0523

我帖子的意义关键不在于讨论这个SQL，关键在于找出运行这些SQL的程序，这种SQL很明显是oracle内部调度的。说白了，我就是要知道到底是什么内部定时任务在跑，这任务应该和MMON_SLAVE进程以及DBMGR用户的某个任务有关，这种内部调度是11G新特性，还是什么？

huagan0523

由于在diag下没有找到任何相关trace文件，因此未上传附件

Stone

可以查看这个时间段是否有Rman的备份，很有可能和这个有关系。
另外请参考 “Rman uses a lot Of Temporary Segments ORA-1652: Unable To Extend Temp Segment [ID 357765.1]”

Cause

SQL> select count(*) from v$rman_status ;
select count(*) from v$rman_status
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 640 in tablespace TEMP

Incorrect statistics on FIXED objects
which resulted in an ineffective execution plan, needed much sort-space.
Multiple waitevents on 'direct path write temp'

Solution

To implement the solution, please execute the following steps:

SQL> exec dbms_stats.gather_fixed_objects_stats;

Good luck.

huagan0523

感谢Stone,我开了SR，回复的结果也是叫我
exec dbms_stats.gather_fixed_objects_stats;
FIXED objects是内存表，FIXED objects的统计信息不由自动收集统计信息的JOB来维护，这一点我知道，那么间隔多久收集一次FIXED objects的统计信息呢？

Stone

huagan0523 发表于 2013-6-21 23:52
感谢Stone,我开了SR，回复的结果也是叫我
exec dbms_stats.gather_fixed_objects_stats;
FIXED objects是内 ...

Oracle有一篇文章，解释了一下相关的东西：

https://blogs.oracle.com/optimizer/entry/fixed_objects_statistics_and_why

另外下面这篇文章写得比较详细，而且比较有条理，可以参考。

http://dba-tips.blogspot.com/2012/03/gathering-fixed-objects-statistics.html

Gathering Fixed Objects Statistics


What are the fixed objects:

Fixed objects are the x$ tables and their indexes.

Why we must gather statistics on fixed objects:

If the statistics are not gathered on fixed objects, the Optimizer will use predefined default values for the
statistics. These defaults may lead to inaccurate execution plans.

Does Oracle gather statistics on fixed objects:

Statistics on fixed objects are not being gathered automatically nor within gathering database stats procedure.

When we should gather statistics on fixed objects:

-After a major database or application upgrade.
-After implementing a new module.
-After changing the database configuration. e.g. changing the size of memory pools (sga,pga,..).
-Poor performance/Hang encountered while querying dynamic views e.g. V$ views.
-This task should be done only a few times per year.

Note:
-It's recommended to Gather the fixed object stats during peak hours (system is busy) or after the peak hours but the sessions are still connected (even if they idle), to guarantee that the fixed object tables been populated and the statistics well represent the DB activity.
-Performance degradation may be experienced while the statistics are gathering.
-Having no statistics is better than having a non representive statistics.

How to gather stats on fixed objects:

Firstly Check the last analyzed date:

select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KGLDP';


OWNER          TABLE_NAME        LAST_ANAL
------------------------------ ------------------------------      ---------
SYS                     X$KGLDP         20-MAR-12


Secondly Export the current fixed stats in a table: (in case you need to revert back)

exec dbms_stats.create_stat_table('OWNER','STATS_TABLE_NAME','TABLESPACE_NAME');
exec dbms_stats.export_fixed_objects_stats(stattab=>'STATS_TABLE_NAME',statown=>'OWNER');

Thirdly Gather fixed objects stats:

exec dbms_stats.gather_fixed_objects_stats;


In case of reverting to the old statistics:
In case you experianced a bad performance on fixed tables after gathering the new statistics:

exec dbms_stats.delete_fixed_objects_stats();
exec DBMS_STATS.import_fixed_objects_stats(stattab =>’STATS_TABLE_NAME’,STATOWN =>'OWNER');

Good luck

huagan0523

OK，3Q again