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
>

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