Thanks Jonathan.
I am attaching the DDL scripts for the table and the N5 index. The index is
LOCAL non-prefixed. This is a standard Oracle E-Business Suite table.
Thanks
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Jonathan Lewis
Sent: Friday, November 18, 2016 6:53 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Partitioned index question
I think my first check, in the absence of the original script to create the
objects, would be to execute:
set long 200000
select dbms_metadata.get_ddl('TABLE','whatever it was', 'schema name') from
dual; select dbms_metadata.get_ddl('INDEX','whatever it was', 'schema name')
from dual;
I can imagine someone playing silly games with list partitions for the table
and range-partitioned global indexes for the index that could result in some
odd naming effects. In the absence of better information I might also spend a
little time thinking about one could confuse the issue with index organized
tables.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Tim Gorman <tim.evdbt@xxxxxxxxx>
Sent: 18 November 2016 05:13:35
To: Amir.Hameed@xxxxxxxxx; 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
SQL> dba_tab_partitions where table_name='XLA_AE_HEADERS' and
SQL> 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
SQL> dba_ind_partitions where index_name='XLA_AE_HEADERS_N5' and
SQL> 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
SQL> 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
--
//www.freelists.org/webpage/oracle-l
Attachment:
XLA_AE_HEADERS_N5.sql
Description: XLA_AE_HEADERS_N5.sql
Attachment:
XLA_AE_HEADERS.sql
Description: XLA_AE_HEADERS.sql
