[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內容.
--//作者沒講這個包是加密的需要解密.我使用連結

--//解開後修改如下加入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並沒有什麼問題.
--//如果要加密回去,執行如下:

--//dd.txt是解密的指令碼.

SYS@test> @ dd.plb
Package body created.

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

相關文章