The table partitions where there is data in the table partition but the corresponding index partition does not contain any rows.
select table_name , high_value , num_rows , last_analyzed
from dba_tab_partitions
where table_name = '&P_TABLE_NAME' ;
select index_name , high_value , num_rows , last_analyzed
from dba_ind_partitions
where index_name = '&P_INDEX_NAME' ;
If the TAB_PARTITIONS HIGH_VALUE has a NON-ZERO value but the corresponding INDEX_PARTITION has a 0 VALUE (exception is index: XLA_AE_LINES_N1), then there is the corruption in the index statistics.
In this case use the solution below to get the performance issue resolved.
a. Compile the below function(getPartitionHighValue) which is used in the query (step b).
CREATE OR REPLACE function getPartitionHighValue(
p_partition_type in varchar2,
p_owner in varchar2,
p_name in varchar2,
p_partition_name in varchar2) RETURN VARCHAR2 AS
l_cursor integer default dbms_sql.open_cursor;
l_len number;
l_long_val varchar2(4000);
l_long_len number;
l_n number ;
l_table_name varchar2(50);
BEGIN
IF upper(p_partition_type) = 'TABLE' THEN
dbms_sql.parse(l_cursor,
'select high_value_length , high_value from dba_tab_partitions ' ||
'where table_owner = :1 ' ||
'and table_name = :2 ' ||
'and partition_name = :3 ',
dbms_sql.native );
ELSE
dbms_sql.parse(l_cursor,
'select high_value_length , high_value from dba_ind_partitions ' ||
'where index_owner = :1 ' ||
'and index_name = :2 ' ||
'and partition_name = :3 ',
dbms_sql.native );
END IF;
dbms_sql.bind_variable( l_cursor, ':1', p_owner );
dbms_sql.bind_variable( l_cursor, ':2', p_name );
dbms_sql.bind_variable( l_cursor, ':3', p_partition_name );
dbms_sql.define_column(l_cursor, 1 , l_len);
dbms_sql.define_column_long(l_cursor, 2 );
l_n := dbms_sql.execute(l_cursor);
IF (dbms_sql.fetch_rows(l_cursor)>0) THEN
dbms_sql.column_value(l_cursor, 1 , l_len );
dbms_sql.column_value_long(l_cursor, 2, l_len, 0 ,
l_long_val, l_long_len );
END IF;
dbms_sql.close_cursor(l_cursor);
return l_long_val;
END getPartitionHighValue;
/
b.Run the below sql to check the table partitions where there is data in the table partition but the corresponding index partition does not contain any rows. The only exception should be the index: XLA_***
SELECT tab.table_name ,
getPartitionHighValue('TABLE',tab.table_owner, tab.table_name,
tab.partition_name) "TABLE_HIGH_VALUE" ,
ind.index_name ,
getPartitionHighValue('INDEX',ind.index_owner, ind.index_name,
ind.partition_name) "INDEX_HIGH_VALUE" ,
tab.num_rows "TABLE_NUM_ROWS",
ind.num_rows "INDEX_NUM_ROWS"
FROM dba_tab_partitions tab,
dba_indexes i,
dba_ind_partitions ind
WHERE tab.table_name = i.table_name
AND tab.table_owner = i.table_owner
AND i.index_name = ind.index_name
AND i.owner = ind.index_owner
AND tab.table_name IN
('XLA_AE_HEADERS','XLA_AE_LINES',
'XLA_DISTRIBUTION_LINKS','XLA_EVENTS',
'XLA_TRANSACTION_ENTITIES')
AND getPartitionHighValue('TABLE',tab.table_owner,
tab.table_name, tab.partition_name) =
getPartitionHighValue('INDEX',ind.index_owner,
ind.index_name, ind.partition_name)
AND tab.num_rows <> 0
AND ind.num_rows = 0;
Run the below SQL to check the table partitions where there is data in the table partition but the corresponding index partition does not contain any rows.
SELECT tab.table_name ,
getPartitionHighValue('TABLE',tab.table_owner, tab.table_name,
tab.partition_name) "TABLE_HIGH_VALUE" ,
ind.index_name ,
getPartitionHighValue('INDEX',ind.index_owner, ind.index_name,
ind.partition_name) "INDEX_HIGH_VALUE" ,
tab.num_rows "TABLE_NUM_ROWS",
ind.num_rows "INDEX_NUM_ROWS"
FROM dba_tab_partitions tab,
dba_indexes i,
dba_ind_partitions ind
WHERE tab.table_name = i.table_name
AND tab.table_owner = i.table_owner
AND i.index_name = ind.index_name
AND i.owner = ind.index_owner
AND tab.table_name IN
('XLA_AE_HEADERS','XLA_AE_LINES','XLA_DISTRIBUTION_LINKS',
'XLA_EVENTS','XLA_TRANSACTION_ENTITIES')
AND getPartitionHighValue('TABLE',tab.table_owner,
tab.table_name, tab.partition_name) =
getPartitionHighValue('INDEX',ind.index_owner,
ind.index_name, ind.partition_name)
AND tab.num_rows <> 0
AND ind.num_rows = 0;
This time after the patch application, nothing should be shown except for the index XLA_AE_LINES_N1 in the sql.
Should the issue persist, please provide a TKPROF and SQLTXPLAIN output of the program as well the output of the below SQL's:
SELECT * FROM dba_tab_partitions
WHERE table_owner = 'XLA'
AND table_name IN ('XLA_AE_HEADERS','XLA_AE_LINES',
'XLA_DISTRIBUTION_LINKS','XLA_EVENTS',
'XLA_TRANSACTION_ENTITIES')
ORDER BY table_name, partition_position;
SELECT * FROM dba_ind_partitions
WHERE index_owner = 'XLA'
AND (index_name LIKE 'XLA_AE_HEADERS%' OR
index_name LIKE 'XLA_AE_LINES%' OR
index_name LIKE 'XLA_DISTRIBUTION_LINKS%' OR
index_name LIKE 'XLA_EVENTS%' OR
index_name LIKE 'XLA_TRANSACTION_ENTITIES%')
ORDER BY index_name, partition_position;
分享到:
相关推荐
《成功之路:Oracle 11g学习笔记》专为开发人员编写,先引导开发新手熟悉Oracle环境,然后进入数据库开发,并要掌握项目开发中的一些技巧。开发技巧展示是《成功之路:Oracle 11g学习笔记》的一大特色(这一点很值得...
linux下oracle11g安装笔记,希望能有帮助
该文档是李兴华针对oracle 11g 的课堂笔记,是pdf格式文本。。。。
成功之路:Oracle_11g学习笔记17成功之路:Oracle_11g学习笔记17成功之路:Oracle_11g学习笔记17
资源名称:成功之路:Oracle 11g学习笔记内容简介:本书专为开发人员编写,先引导开发新手熟悉Oracle环境,然后进入数据库开发,并要掌握项目开发中的一些技巧。开发技巧展示是本书的一大特色(这一点很值得资深的...
成功之路+Oracle11g学习笔记 非常好的学习oracle 基础的一本书 适合初学者
带目录版,放心下载,方便观看。成功之路Oracle11g学习笔记
oracle 11g performance tuning 英文版
Oracle10g学习笔记,Oracle10g学习笔记Oracle10g学习笔记Oracle10g学习笔记Oracle10g学习笔记Oracle10g学习笔记
Oracle DB 11g Performance Tuning
成功之路Oracle11g学习笔记-赵振平 请下载2个附件
有关oracle数据库基础学习OCP课程的基础051的课程笔记
Oracle 11g Performance Tuning Recipes
Oracle 官方培训文档,11g DB性能调优
Oracle Database 11g Performance Tuning,oracle内部技术文档,正宗
我的Oracle 11g OCP学习笔记,很详细,分享给大家学习!
成功之路 Oracle 11g 中文学习笔记 专为开发人员编写,先引导开发新手熟悉Oracle环境,然后进入数据库开发,并要掌握项目开发的一些技巧。开发技巧展示是本书一大特色(这一点很值得资深的开发人员借鉴)! 本书共24...
Oracle11g备份恢复笔记文档,有详解实例,方便学习。如有差错还请谅解
oracle11g学习笔记一 1 一、以下是sqlplus常用命令 2 一、Oracle用户管理 4 二、权限和角色 4 权限包含系统权限和对象权限 5 角色 5 权限的传递 6 使用profile 管理用户口令 8 (1)帐号锁定 8 (2)给账户解锁 8 (3)...
Oracle 11g学习笔记基础篇(pdf)