Partitioned index question

gc buffer busy acquire

4,086,094

15.1K

4

24.4

Cluster

gc cr block busy

2,102,205

8634.9

4

13.9

Cluster

direct path read

130,665

1375.9

11

2.2

User I/O

Disk file operations I/O

157,121

1212.6

8

2.0

User I/O

db file parallel read

70,297

571.3

8

.9

User I/O

log file sync

53,280

488.6

9

.8

Commit

gc cr disk read

1,615,316

325.4

0

.5

Cluster

utl_file I/O

1,945,047

278.5

0

.4

User I/O

Segments by Global Cache Buffer Busy

• % of Capture shows % of GC Buffer Busy for each top segment compared
• with GC Buffer Busy for all segments captured by the Snapshot Owner

Tablespace Name

Object Name

Subobject Name

Obj. Type

GC Buffer Busy

% of Capture

XLA APPS_TS_TX_IDX XLA_AE_HEADERS_N5 GMF INDEX PARTITION 4,012,420

94.77

XLA APPS_TS_TX_DATA XLA_AE_HEADERS CST TABLE PARTITION 82,466

1.95

APPLSYS APPS_TS_TX_DATA FND_CONCURRENT_REQUESTS TABLE 38,117

0.90

APPLSYS APPS_TS_TX_IDX WF_NOTIFICATIONS_PK INDEX 19,776

0.47

APPLSYS APPS_TS_TX_IDX FND_CONCURRENT_REQUESTS_N9 INDEX 9,940

0.23

Most of the Global Cache Buffer Busy waits are coming from index XLA_AE_HEADERS_N5, partition GMF and this is where it got interesting for me. The DBA_SEGMENTS view shows that there was no segment associated with the GMF partition of table XLA_AE_HEADERS, which is what XLA_AE_HEADERS_N5 is created on. It was also confirmed by the query on DBA_TAB_PARTITIONS:

SQL> select table_name, partition_name, segment_created from dba_tab_partitions where table_name='XLA_AE_HEADERS' and partition_name='GMF' ;

TABLE_NAME                     PARTITION_NAME                 SEGM
------------------------------ ------------------------------ ----
XLA_AE_HEADERS                 GMF                            NO

However, the query against DBA_IND_PARTITION shows that XLA_AE_HEADERS_N5 partition has a segment associated with it: SQL> select index_name, partition_name, segment_created from dba_ind_partitions where index_name='XLA_AE_HEADERS_N5' and partition_name='GMF' ;

INDEX_NAME                     PARTITION_NAME                 SEG
------------------------------ ------------------------------ ---
XLA_AE_HEADERS_N5              GMF                            YES

SQL> select segment_name, partition_name, bytes from dba_segments where segment_name='XLA_AE_HEADERS_N5' and partition_name='GMF';

SEGMENT_NAME                   PARTITION_NAME                           BYTES
------------------------------ ------------------------------ ---------------
XLA_AE_HEADERS_N5              GMF                              2,566,914,048

The index is LOCAL. So, the question is, is it possible for a LOCAL partitioned index to have a segment when the table partition that it is created on does not have a segment associated with it?

Thanks,
Amir

--
http://www.freelists.org/webpage/oracle-l