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

[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.