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