[20170516]10g分析SYS.X$KTFBUE.txt

--//昨天别人问的问题,就是调用dba_extents很慢,我建议他对X$进行分析.

--//执行如下:exec dbms_stats.gather_fixed_objects_stats(); --//问题依旧.我google,baidu看了一下,发现是一个bug.

Description

This problem is introduced in 10.2.0.4 by the fix for bug 5259025 . DBMS_STATS may fail with ORA-1422 when trying to

gather statistics for X$KTFBUE. eg: exec dbms_stats.gather_table_stats('SYS', 'X$KTFBUE'); ^ ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "SYS.DBMS_STATS", line 13437 ORA-06512: at "SYS.DBMS_STATS", line 13457 ORA-06512: at line 1

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not

confirm that you are encountering this problem. Always consult with Oracle Support for advice.

References

    Bug:7430745 (This link will only work for PUBLISHED bugs)

    Note:245840.1 Information on the sections in this article

SYS@test> select num_rows, last_analyzed from user_tab_statistics where table_name = 'X$KTFBUE';

  NUM_ROWS LAST_ANALYZED ---------- -------------------

--//可以发现这个没分析.现在分析原因.

1.环境:

SYS@test> @ &r/ver1

PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ---------------------------------------------------------------- x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

--//单独分析它.

SYS@test> exec DBMS_STATS.GATHER_TABLE_STATS  ('SYS', 'X$KTFBUE'); BEGIN DBMS_STATS.GATHER_TABLE_STATS  ('SYS', 'X$KTFBUE'); END;

*

ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "SYS.DBMS_STATS", line 13437 ORA-06512: at "SYS.DBMS_STATS", line 13457 ORA-06512: at line 1 --//符合上面bug的描述.

2.跟踪分析看看:

SYS@test> @ &r/10046on 12 old   1: alter session set events '10046 trace name context forever, level &1' new   1: alter session set events '10046 trace name context forever, level 12' Session altered.

SYS@test> exec DBMS_STATS.GATHER_TABLE_STATS  ('SYS',  'X$KTFBUE');

BEGIN DBMS_STATS.GATHER_TABLE_STATS  ('SYS',  'X$KTFBUE'); END;

*

ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "SYS.DBMS_STATS", line 13437 ORA-06512: at "SYS.DBMS_STATS", line 13457 ORA-06512: at line 1

SYS@test> @ &r/10046off

Session altered.

--//检查跟踪文件发现如下:

===================== PARSING IN CURSOR #7 len=59 dep=1 uid=0 oct=3 lid=0 tim=1459858565210839 hv=1204802936 ad='75779df0' SELECT KQFOPTFLAGS FROM SYS.X$KQFOPT WHERE KQFOPTOBJ = :B1 END OF STMT PARSE #7:c=999,e=123,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1459858565210834 BINDS #7: kkscoacd Bind#0   oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00   oacflg=03 fl2=1206001 frm=00 csi=00 siz=24 off=0   kxsbbbfp=2b180ebf8f70  bln=22  avl=06  flg=05   value=4294951517 EXEC #7:c=0,e=104,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1459858565211010 FETCH #7:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,tim=1459858565211065 EXEC #1:c=17996,e=17216,p=0,cr=33,cu=0,mis=0,r=0,dep=0,og=1,tim=1459858565211232 ERROR #1:err=1422 tim=652271886 WAIT #1: nam='SQL*Net break/reset to client' ela= 15 driver id=1650815232 break?=1 p3=0 obj#=-1 tim=1459858565211341 WAIT #1: nam='SQL*Net break/reset to client' ela= 61 driver id=1650815232 break?=0 p3=0 obj#=-1 tim=1459858565211425 WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1459858565211457 *** 2017-05-16 08:39:35.831 WAIT #1: nam='SQL*Net message from client' ela= 4936121 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1459858570147634 =====================

SYS@test> SELECT * FROM SYS.X$KQFOPT WHERE KQFOPTOBJ = 4294951517;

ADDR                   INDX    INST_ID  KQFOPTOBJ KQFOPTFLAGS ---------------- ---------- ---------- ---------- ----------- 00000000058450A8          3          1 4294951517          33 0000000005845168         11          1 4294951517          40

--//这里返回2行,明显存在错误.KQFOPTOBJ来之V$FIXED_TABLE.

SYS@test> select * from V$FIXED_TABLE where name='X$KTFBUE';

NAME                  OBJECT_ID TYPE                                      TABLE_NUM -------------------- ---------- ---------------------------------------- ---------- X$KTFBUE             4294951517 TABLE                                           373

--//找到一个链接blog.sina.com.cn/s/blog_4ea0bbed01010p4g.html,提示要修改包中dbms_stats_internal内容.

--//作者没讲这个包是加密的需要解密.我使用链接www.hellodba.com/reader.php?ID=36&lang=CN.

--//解开后修改如下加入and rownum<2;.(根据前面的sql语句很容易定位)

  FUNCTION GATHER_FXT_STATS_OK(OBJN NUMBER)     RETURN BOOLEAN IS     FLAGS NUMBER;   BEGIN

    BEGIN

      SELECT KQFOPTFLAGS INTO FLAGS       FROM SYS.X$KQFOPT       WHERE KQFOPTOBJ = OBJN and rownum<2;     EXCEPTION       WHEN NO_DATA_FOUND THEN         FLAGS := 0;     END;

    IF (BITAND(FLAGS, 16) = 0) THEN

      RETURN TRUE;     ELSE       RETURN FALSE;     END IF;

  END GATHER_FXT_STATS_OK;

--//因为我的是测试环境我决定看看是否可行.修改第1行如下(先不加密)

CREATE OR REPLACE package body SYS.dbms_stats_internal IS

SYS@test> @ dd.txt

3511  / Package body created.

SYS@test> exec DBMS_STATS.GATHER_TABLE_STATS  ('SYS',  'X$KTFBUE');

PL/SQL procedure successfully completed.

SYS@test> select num_rows, last_analyzed from user_tab_statistics where table_name = 'X$KTFBUE';

  NUM_ROWS LAST_ANALYZED ---------- -------------------       8973 2017-05-16 08:51:28

--//OK现在已经分析了,至于作者讲需要再修改回来,实际上可以不改,因为哪里就是返回1行.多行是错误的,加入条件 rownum<2并没有什么问题.

--//如果要加密回去,执行如下:

$ wrap iname=dd.txt

PL/SQL Wrapper: Release 10.2.0.4.0- 64bit Production on Tue May 16 08:56:03 2017 Copyright (c) 1993, 2004, Oracle.  All rights reserved. Processing dd.txt to dd.plb

--//dd.txt是解密的脚本.

SYS@test> @ dd.plb

Package body created.