Friday, June 19, 2009

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.


At July 9, 2009 at 3:46:00 AM PDT, Blogger Tanel Poder said...

Yeah, good list!

Regarding item 6 (optimizer taking the caching factor into account when computing query estimated cost):

The data dictionary has had cache_stats_0$ and cache_stats_1$ base tables since 10.2 I think (or maybe even 10.1), but apparently this feature has not been implemented/tested fully so far.

If you want to play around, set these parameters true on your 11g test instance and let us know the results :)



At July 18, 2009 at 11:13:00 PM PDT, Anonymous Anonymous said...

Isn't your wish list a little late? When will 11gr2 be released?

At September 1, 2009 at 9:30:00 AM PDT, Blogger Filipe Silva said...

At least today you can already download the linux version

At December 15, 2009 at 8:02:00 AM PST, Anonymous Anonymous said...

you have a nice site. thanks for sharing this valuable resources. keep it up. anyway, various kinds of ebooks are available here

At December 28, 2009 at 3:11:00 PM PST, Anonymous Anonymous said...

Bravo, this remarkable idea is necessary just by the way

At February 1, 2010 at 3:47:00 PM PST, Anonymous Anonymous said...

Amiable dispatch and this post helped me alot in my college assignement. Say thank you you on your information.

At February 7, 2010 at 2:14:00 AM PST, Anonymous Anonymous said...

Well I to but I about the list inform should prepare more info then it has.

At March 13, 2010 at 9:09:00 PM PST, Anonymous Anonymous said...

Hello there all, I just registered on this splendid forum and wanted to say howdy! Have a stunning day!

At April 1, 2010 at 8:19:00 AM PDT, Anonymous Anonymous said...

hi everybody

I just wanted to introduce myself to everyone!

Can't wait to get to know you all better!


Thanks again!

At May 8, 2010 at 3:34:00 AM PDT, Anonymous said...

i like this

At October 4, 2010 at 5:32:00 AM PDT, Anonymous Oracle DBA Services said...

You have an interesting topic I think schools should do the basic on the computer courses

At July 29, 2011 at 12:57:00 AM PDT, Anonymous oracle ebs r12 said...

Completely remarkable post. The points that you have added in your wish list are truly amazing. I do agree with all of them and now as the new version has been arrived and I am still in the process to explore it I hope it will do have all these exciting features in it. Thanks for this wonderful post.


Post a Comment

Links to this post:

Create a Link

<< Home