今天是2017年7月27,在远程给客户处理问题时偶然发现,使用dbms_metadata.get_ddl 无法获得索引创建语句,本来很简单的事情,等了很久都没出结果。
给我第一反应是遇到鬼了。
问题描述:
通过sqlplus 登陆数据库,执行“select dbms_metadata.get_ddl('INDEX', 'IDX_ZW_SSDFJL_FXRBS_YWLBDM1', 'GDYX7001') FROM DUAL;” 没有任何结果。
问题分析:
既然没有结果,那么肯定是哪里存在问题了,随即对该语句进行10046 sql trace 。
操作步骤如下:
ALTER SESSION SET timed_statistics=true;
ALTER SESSION SET max_dump_file_size=unlimited ;
ALTER SESSION SET tracefile_identifier='my_trace_session';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
select dbms_metadata.get_ddl('INDEX', 'IDX_ZW_SSDFJL_FXRBS_YWLBDM1', 'GDYX7001') FROM DUAL;
ALTER SESSION SET EVENTS '10046 trace name context off';
找到trc文件,使用tkprof工具,分析trc文件,然后我们看到;
SQL ID: 3v9mgpjqffrj0 Plan Hash: 2962139578
SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('INDEX_T',
'7')), KU$.OBJ_NUM
FROM
SYS.KU$_INDEX_VIEW KU$ WHERE NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0 AND
KU$.SCHEMA_OBJ.NAME='IDX_ZW_SSDFJL_FXRBS_YWLBDM' AND KU$.SCHEMA_OBJ.OWNER_NAME='GDYX7001';
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.28 0.28 0 3892 0 0
Fetch 1 1.94 1.93 0 249677 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 2.22 2.22 0 253569 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
可以知道逻辑读非常大。我尝试采用手动执行该sql,结果可想而知。也正是该问题导致了。
那么这是dbms_metadata 我们无法对sql进行优化,随即翻阅mos发现oracle 11.2.0.3 存在一个bug:Bug 13844935 Queries against KU$_INDEX_VIEW slow under DBMS_METADATA - superseded
提到该问题在11.2.0.4 得到了解决。
该问题尤其对于分区表(众多级联分情况)的索引尤为突出。