OK, disproves my theory. Thanks!
On 11/18/16 07:53, Hameed, Amir wrote:
Only a few have segments created:
INDEX_NAME PARTITION_NAME SEG
------------------------------ ------------------------------ ---
XLA_AE_HEADERS_N5 AP YES
XLA_AE_HEADERS_N5 AR YES
XLA_AE_HEADERS_N5 CE NO
XLA_AE_HEADERS_N5 COREBANK NO
XLA_AE_HEADERS_N5 CST NO
XLA_AE_HEADERS_N5 DPP YES
XLA_AE_HEADERS_N5 FUN NO
XLA_AE_HEADERS_N5 FV NO
XLA_AE_HEADERS_N5 GMF YES
XLA_AE_HEADERS_N5 IGC NO
XLA_AE_HEADERS_N5 IGI NO
XLA_AE_HEADERS_N5 INSTITBANK YES
XLA_AE_HEADERS_N5 LNS NO
XLA_AE_HEADERS_N5 OFA NO
XLA_AE_HEADERS_N5 OKL NO
XLA_AE_HEADERS_N5 OZF NO
XLA_AE_HEADERS_N5 PA NO
XLA_AE_HEADERS_N5 PAY NO
XLA_AE_HEADERS_N5 PN NO
XLA_AE_HEADERS_N5 PO NO
XLA_AE_HEADERS_N5 PSB NO
*From:*Tim Gorman [mailto:tim.evdbt@xxxxxxxxx]
*Sent:* Friday, November 18, 2016 9:51 AM
*To:* Hameed, Amir <Amir.Hameed@xxxxxxxxx>; oracle-l@xxxxxxxxxxxxx
*Subject:* Re: Partitioned index question
Can you check if *all* of the index partitions have segments, please?
On 11/18/16 07:14, Hameed, Amir wrote:
Tim,
Thank you for your reply. This is a LOCAL non-prefixed index. The
table is list-partitioned on “APPLICATION_ID” whereas the index is
created on ("ACCOUNTING_DATE", "LEDGER_ID",
"GL_TRANSFER_STATUS_CODE").
Thanks,
Amir
*From:*Tim Gorman [mailto:tim.evdbt@xxxxxxxxx]
*Sent:* Friday, November 18, 2016 12:14 AM
*To:* Hameed, Amir <Amir.Hameed@xxxxxxxxx>
<mailto:Amir.Hameed@xxxxxxxxx>; oracle-l@xxxxxxxxxxxxx
<mailto:oracle-l@xxxxxxxxxxxxx>
*Subject:* Re: Partitioned index question
Amir,
There are two types of LOCAL partitioned indexes: PREFIXED and
NONPREFIXED. PREFIXED indexes have the partition-key column(s)
leading the list of concatenated columns on the index. NONPREFIXED indexes have a column other than the partition-key
column(s) leading the list.
Queries on PREFIXED indexes always prune perfectly, while queries
on NONPREFIXED might span any and every partition in the index.
Think of a very extreme example, a two-column NONPREFIXED index
where the leading column has only one data value. Consider a query
that filters only only that first column, and for some reason the
CBO allows it to perform a indexed FULL SCAN (instead of a FULL
table scan). Such an execution plan would require that every
partition in the index be materialized with a segment.
Remember: an index is an ordered hierarchical structure, not a
"heap" structure like a table. The rules are bound to be
different for indexes than for tables.
This is all just a guess: you can prove or disprove it.
Could you check to whether that index is PREFIXED or NONPREFIXED,
and then whether *all* partitions in that index have segments?
If the index is NONPREFIXED and all partitions have segments, then
it would bear out this guess. Or at least not disprove it, as
there might be another reason why all partitions have segments.
If the index is PREFIXED or if any partitions have no
corresponding segment, then this guess is likely disproved. Maybe,
maybe not, because it is also possible that these segments don't
materialize until something accesses them.
Please let us know what you find?
Thanks!
-Tim
On 11/17/16 19:36, Hameed, Amir wrote:
Hi,
While investigating a performance problem, I came across
something that didn’t make much sense to me and I wanted to
see if I am hitting a bug or my concept is not clear.
The RDBMS version is 11.2.0.4 and it is an Oracle E-Business
Suite database.
The following is an entry from the AWR taken during the time
when the AWR showed *gc buffer busy acquire* waits.
*Top 10 Foreground Events by Total Wait Time*
*Event*
*Waits*
*Total Wait Time (sec)*
*Wait Avg(ms)*
*% DB time*
*Wait Class*
db file sequential read
6,074,412
17.6K
3
28.4
User I/O
DB CPU
15.5K
24.9
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
