Re: Partitioned index question

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_at_gmail.com]
> *Sent:* Friday, November 18, 2016 9:51 AM
> *To:* Hameed, Amir <Amir.Hameed_at_xerox.com>; oracle-l_at_freelists.org
> *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_at_gmail.com]
> *Sent:* Friday, November 18, 2016 12:14 AM
> *To:* Hameed, Amir <Amir.Hameed_at_xerox.com>
> <mailto:Amir.Hameed_at_xerox.com>; oracle-l_at_freelists.org
> <mailto:oracle-l_at_freelists.org>
> *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
>

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