RE: Partitioned index question
Date: Fri, 18 Nov 2016 14:53:03 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED201ADAA0_at_USA7109MB012.na.xerox.net>
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-lReceived on Fri Nov 18 2016 - 15:53:03 CET