Oracle blog

Oracle new feature enhancement requests for 11gr2 and above

Here is my wish list:

1) Ability to create composite partitioned local indexes when the table is not composite partitioned and first level partitioning for index and table is same.

If we have a table range partitioned by TIME_CREATED column and column like "ID" is generated off of a sequence then the local index on "ID" column is prone to index contention as DML concurrency goes up.

Creating reverse key indexes would resolve the contention but would limit our ability to range scans and it also introduces more physical I/O for DML's and select queries.

If we can partition index on "ID" column by TIME_CREATED and do subpartition by hash on "ID", it will not only resolve index contention and would also give us the ability to do the range scans along with less physical I/O for DML's and select queries.



2) Ability to use In memory undo and private redo for short transactions when supplemental logging is enabled.

Right now, when supplemental logging is enabled, in memory undo is siltenly turned off automatically. This provides more scalability to OLTP type of database applications.


3) Adding more intelligence in determining amount of space reserved for ITL entries during index leaf block splits.

Right now, if you have an index on a sequence based column and when sudden surge in DML's occurs, Oracle would reserve as much space as possible for all the concurrent transactions in the form of ITL entries and the same amount of space reserved for ITL slots in future leaf block splits even though the spike in DML's may be one time activity.

It should basically add a timestamp to when the maximum number of ITL slots (and also maintain average number of ITL's used) were used and if it was done more than an hour ago (or a day ago) then reserve
only the space required for average number of ITL's used for future leaf block splits.

This would avoid wasting lot of space when the transaction concurrency spikes are very rare (we should be altering index with more initrans in this case anyways).



4) Often times, DBA's create function based mod index to resolve right most leaf block index contention by dividing index column (say "ID") into few buckets (i.e. create index on (mod(ID,10), ID)). This requires change in application for all the sql queries belong to this table and hence it may force DBA's to create reverse key indexes, which is not the ideal solution for big tables.

Just like the way Oracle is recognizing upper or lower functions, when used on an indexed column, to consider index access path, it'd be better if Oracle can recognize mod function also (i.e. convert "WHERE ID=10" to "WHERE ID=10 and mod(id,10)=0" automatically depending on the way mod function index was used.


5) Ability to define index leaf block split records ratio, right now, Oracle put's last record in the new leaf block and the rest will be left in the current leaf block when the right most leaf block is split due to insertion of maximum key value, and use 50-50 ratio for all other leaf block split operations.

When application is designed to address index contention of sequence based column's by dividing them into several buckets (like mod index explained above) and grow them sequentially in each bucket, then Oracle would be wasting lot of space when 50-50 leaf block splits happen.

By having ability to define leaf block split ratio (not for right most leaf block split) then indexes will be more skinnier (for this used case) and hence improved performance due to the possibility of caching more index blocks.


6) Ability to define index affinity. If we have a table with (account#, phone_number, pin_number, etc) and have indexes on (account#, phone_number) and (account#, pin_number), then if most executed queries use the first index and if we have a query with "where account# = ?" then oracle can use first or second index based on the optimizer statistics.

In this case, even if statistics indicate that the second index is better, just from caching perspective first index is ideal. We need to have ability to alter an particular index to more affinity either by having alter index command (which will be enforced when leading columns of the indexes were used as predicates and more than one index has this.


7) Ability to enforce delayed (or on demand) global index maintenance. With one process trying to sort all the indexed columns from the table partition being altered and deleting them from the index requires significant amount of time and if we have multiple partition level changes to be done in a given partition maintenance window, it can run into peak business time or it can pose other operational issues like what happens if instance is crashed, etc.

Since object_id is part of rowid to be stored in global indexes, Oracle can simply mark an object_id as "INVALID" and when user tries to query the data belong to that object, then either perform the cleanup in those leaf blocks at that time or ignore the entries belong to that object. This way, global index maintenance will be like local indexes, with the only difference of on demand cleanup of global index leaf block entries (and we need to have ability to explicitly perform the cleanup of leaf blocks entries).


8) Include access and filter predicates of v$sql_plan in DBA_HIST_SQL_PLAN; right now Oracle doesn't record those column values in any awr base tables.

9) Record the first snap_id of when a sql statement first executed on the database in awr base table (DBA_HIST_SQLTEXT). This information is recorded in statspack base table STATS$SQLTEXT.LAST_SNAP_ID, but not in any awr tables. This is useful in figuring out what changed when a new application rolls out.


10) Record out of normal entries from ash buffer into awr table (DBA_HIST_ACTIVE_SESS_HISTORY) instead of just picking one sample id on 10 second interval. DBA can define threshold for out of normal activity in terms of number of sessions waiting on any given waitevent, etc. This will be very helpful in doing root cause analysis and for any scalability analysis.


11) Honour "_db_block_max_cr_dba" parameter which has a default value of 6, limiting how many clone copies of one block can exist in buffer cache. I've see it going over 10 in many situation. Cloning a buffer is needed for read consistency purposes, but having more copies of them than 6 is hardly ever a requirement and Oracle should reuse an existing clone
if no other process is pinned it (this by itself resolves the problem). Not doing so can increase cache buffer chains latch contention since the latch is needed to be held longer to figure out the right clone buffer image to use.


12) Record redo usage per sql statement in v$sql. This will be very useful for troubleshooting and root cause analysis.


13) Use direct path reads when parallel query option is used with rowid range scans, i.e. select /*+ rowid(w) parallel(w, 2) */ ... where rowid between :1 and :2". Right now, Oracle used scattered reads to read the blocks into buffer cache, but using direct path reads bypassing buffer cache has it's used cases.

Since Oracle is doing direct path reads for each extent of the segment, it can easily get the extent boundaries from the rowid range and hence should be able to direct path reads.

posted by Saibabu Devabhaktuni @ 12:43 AM