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

SYS@test> @ &r/10046off

--//檢查跟蹤檔案發現如下:

=====================

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.

PL/SQL procedure successfully completed.

      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