Oracle Open World Day 4 - 11g Performance & Scalability Features

Mark Rittman

• 3 min read

An early night for me for once, as I'm back now from the Oracle Press event and catching up with the last few presentations of the day. In the end I went to the Server Technology Beta Program event, then the Oracle Press event, and I got a chance to spend some time with Arup Nanda and Kirtikumar Deshpande (the author of one of my favourite Oracle books), Edward Whalen, Bryn Llewellyn, Sam Alapati, Mark Van de Weil and Hermann Baer.

Bryn is responsible for PL/SQL and the new Editioning feature in 11g, Arup, Sam, Edward and Kirtikumar are of course famous for their DBA work and writing, whilst Mark and Hermann work in server technology development with Mark responsible for Change Data Capture and Hermann being Principal Product Manager for data warehousing. I was originally in two minds about going to the two events, but I'm glad I did now as it was a good chance to discuss this week's announcements and talk through some of the things I've been up to on the data warehouse side with Mark and Hermann. Well worth going along for.

Anyway, apart from the enterprise support for Linux announcement earlier in the day, the big event for my was Juan Loaiza's "Next Generation Oracle Database Performance & Scalability: A Sneak Peek". Like the other preview sessions, nothing's promised and this is just an indication of what might be in 11g; however, here's the list of features and some comments by myself:

  • The features in 11g focused on scalability and performance can be grouped into four areas: Scalable execution, scalable storage, scalable availability and scalable management
  • Scalable execution consists of a number of features, the first of which is query results caching; this feature automatically caches the results of an SQL query as opposed to the data blocks normally cached by the buffer cache, and works both client (OCI) and server side - this was described as "buffer cache taken to the next level". The DBA sets the size of the results cache and turns the feature on at a table level with the command "alter table DEPT cache results", the per-process cache is shared across multiple session and at the client level, is available with all 11g OCI-based clients.
  • The next scalable execution feature is automatic creation of "native" PL/SQL and Java code, with just one parameter for each type with an "on/off" value. This apparently provides a 100% performance boost for pure PL/SQL and Java code, and a 10%-30% boost for code containing SQL.
  • Server-side connection pooling allows multiple Oracle clients to share a server-side pool of sessions (USERIDs must match). Clients can connect and disconnect (think PHP applications) at will without the cost of creating a new server session - shared server removes the process creation cost but not the session creation cost.
  • Order of magnitude faster optimizer stats creation
  • Optimized cache fusion protocols - moves on from the general cache fusion protocols in 10g to deal with specific scenarios where the protocols could be further optimized
  • Segment compression now works for all DML, not just direct-path loads, so you can create tables compressed and use them for regular OLTP work. Also supports column add/drop.
  • Oracle SecureFiles - replacement for LOBs that are faster than Unix files to read/write. Lots of potential benefit for OLAP analytic workspaces, as the LOBs used to hold AWs have historically been slower to write to than the old Express .db files.
  • Interval partitioning, a partitioning scheme that automatically creates time-based partitions as new data is added.
  • Virtual columns, columns that are actually functions ("create table t1 (c1 number, c2 number, c3 as (c1+c2) virtual"), and similarly, virtual indexes that are based on functions
  • REF partitioning, allowing you to partition a table based on the partition scheme of another. Allows you to partition an order_items table based off of the order_date column in an orders table.
  • "Fully Automatic SQL Tuning", ADDM for RAC, Streams Performance Advisor, Partitioning Advisor
That's as much as I managed to write down. Off for a bath now, then an early night as I've got an 8.30am start for tomorrow's all-day BI CAB meeting. Now that one's subject to an NDA so I can't blog on that, but I'll hopefully catch up with a few familiar faces and trade a few war stories.

Sign up for more like this.

Enter your email
Subscribe