jump to navigation

Oracle 11g New Features March 31, 2007

Posted by Muhammad Habib in Oracle General.
trackback

Oracle 11g DBA new features

  • Interval partitioning for tables – This is a new 11g partitioning scheme that automatically creates time-based partitions as new data is added. This is a marvelous one ! You can now partition by date, one partition per month for example, with automatic partition creation.

  • New load balancing utilities -There are several new load balancing utilities in 11g (first introduced in 10gr2):

  • Web server load balancing – The web cache component includes Apache extension to load-balance transactions to the least-highly-loaded Oracle HTTP server (OHS).
  • RAC instance load balancing Staring in Oracle 10g release 2, Oracle JDBC and ODP.NET provide connection pool load balancing facilities through integration with the new “load balancing advisory” tool. This replaces the more-cumbersome listener-based load balancing technique.
  • Automated Storage Load balancing – Oracle’s Automatic Storage Management (SAM) now enables a single storage pool to be shared by multiple databases for optimal load balancing. Shared disk storage resources can alternatively be assigned to individual databases and easily moved from one database to another as processing requirements change.
  • Data Guard Load Balancing – Oracle Data Guard allows for load balancing between standby databases.
  • Listener Load Balancing – If advanced features such as load balancing and automatic failover are desired, there are optional sections of the listener.ora file that must be present
  • New table Data Type “simple_integer” – A new 11g datatype dubbed simple_integer is introduced. The simple_integer data type is always NOT NULL, wraps instead of overflows and is faster than PLS_INTEGER.

  • Improved table/index compression – 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.

  • Faster DML triggers – DML triggers are up to 25% faster. This especially impacts row level triggers doing updates against other tables (think Audit trigger).

  • Improved NFS data file management – Kevin Closson has some great notes on Oracle 11g improvement in Networked Attached Storage (NAS). “I’ve already blogged that 11g “might” have an Oracle-provided NFS client. Why is this? It’s because Oracle knows full well that taking dozens of commodity servers and saddling them up with multi-protocol connectivity is a mess.

  • Server-side connection pooling – In 11g server-side connection pooling, an additional layer to the shared server, to enable faster [actually to bypass] session creation. 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.

  • RMAN UNDO bypass – Rman backup can bypass undo. Undo tablespaces are getting huge, but contain lots of useless information. Now rman can bypass those types of tablespace. Great for exporting a tablespace from backup.

  • Capture/replay database workloads – Sounds appealing. You can capture the workload in prod and apply it in development.

  • Scalability Enhancements – 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 – 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.

  • Virtual columns – Oracle 11g virtual table columns are 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. Source: Source:

  • A “super” object-oriented DDL keyword – This is used with OO Oracle when instantiating a derivative type (overloading), to refer to the superclass from whence the class was derived.

  • Oracle 11g XML data storage – Starting in 11g, you can store XML either as a CLOB or a binary data type, adding flexibility. Oracle11g will support query mechanisms for XML including XQuery and SQL XML, emerging standards for querying XML data stored inside tables.

  • Data Guard supports “Flashback Standby”.
  • New Trigger features – A new type of “compound” trigger will have sections for BEFORE, ROW and AFTER processing, very helpful for avoiding errors, and maintaining states between each section.

  • Partitioning – partitioning by logical object and automated partition creation.

  • LOB’s – New high-performance LOB features.

  • Automatic Diagnostic Repository (ADR) – When critical errors are detected, they automatically create an “incident”. Information relating to the incident is automatically captured, the DBA is notified and certain health checks are run automatically. This information can be packaged to be sent to Oracle support (see following).
  • Incident Packaging Service (IPS) – This wraps up all information about an incident, requests further tests and information if necessary, and allows you to send the whole package to Oracle Support.
  • Feature Based Patching – All one-off patches will be classified as to which feature they affect. This allows you to easily identify which patches are necessary for the features you are using. EM will allow you to subscribe to a feature based patching service, so EM automatically scans for available patches for the features you are using.
  • New Oracle11g Advisors – New 11g Oracle Streams Performance Advisor and Partitioning Advisor.
  • Enhanced Read only tables

  • Table trigger firing order – Oracle 11g PL/SQL will you to specify trigger firing order.


Oracle11g High Availability & RAC new features

Oracle continues to enhanced Real Application Clusters in Oracle11g and we see some exciting new features in RAC manageability and enhanced performance:

  • Oracle 11g RAC parallel upgrades – Oracle 11g promises to have a rolling upgrade features whereby RAC database can be upgraded without any downtime. Ellison first promised this feature in 2002, and it is a very challenging and complex 11g new feature.

  • Oracle RAC load balancing advisor – Starting in 10gr2 we see a RAC load balancing advisor utility.

  • ADDM for RAC – Oracle will incorporate RAC into the automatic database diagnostic monitor, for cross-node advisories.

  • Optimized RAC 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.

  • Oracle 11g RAC Grid provisioning – The Oracle grid control provisioning pack allows you to “blow-out” a RAC node without the time-consuming install, using a pre-installed “footprint”. Oracle 11g OEM has have easy server blade installs where a binary footprint is tar’ed to the server blade and configured, without a cumbersome install process.

  • Hot patching – Zero downtime patch application.

  • Standby snapshot – For the purpose of regression testing.

  • Quick Fault Resolution – Automatic capture of diagnostics (dumps) for a fault.


OEM – Enterprise Manager Oracle 11g new features

  • Interfaces to Applications – This note says “Oracle says that extending Enterprise Manager’s capabilities are part of the firm’s promise to seamlessly integrate the spoils of its many acquisitions — including the purchases of Siebel Systems and PeopleSoft Corp. — into a single platform.”

  • OEM Easy de-install – This will uninstall both successful and unsuccessful Oracle installs.

  • Database repair wizard – A GUI to guide beginners through the steps to diagnose and repair Oracle issues.

  • Better OEM Grid tools – Another new Oracle11g feature may be improved RAC and Grid monitoring, especially on the cache fusion interconnect.

  • Enterprise Manager interfaces to foreign applications – This note says “Oracle says that extending Enterprise Manager’s capabilities are part of the firm’s promise to seamlessly integrate the spoils of its many acquisitions — including the purchases of Siebel Systems and PeopleSoft Corp. — into a single platform.”


Oracle 11g programming language support New Features

  • PHP – Improved PHP driver for Oracle.

  • Compilers – Improved native Java & PL/SQL compilers.

  • Oracle 11g XML Enhancements – Oracle 11g will also support Content Repository API for Java Technology (JSR 170). Oracle 11g has XML “duality”, meaning that you can also embed XML directives inside PL/SQL and embed PL/SQL inside XML code. Oracle 11g XML will also support schema-based document Type Definitions (DTD’s), to describe internal structure of the XML document.

  • Scalable Java – The next scalable execution feature is automatic creation of “native” Java code, with just one parameter for each type with an “on/off” value. This apparently provides a 100% performance boost for pure Java code, and a 10%-30% boost for code containing SQL.

  • Improved sequence management – A new features of Oracle 11g will bypass DML (sequence.nextval) and allow normal assignments on sequence values.

  • Intra-unit inlining. In C, you can write a macro that gets inlined when called. Now any stored procedure is eligible for inlining if Oracle thinks it will improve performance. No change to your code is required. Now you have no reason for not making everything a subroutine!


Oracle 11g PL/SQL New Features

  • PL/SQL “continue” keyword – This will allow a C-Like continue in a loop, to bypass any “else” Boolean conditions. A nasty PL/SQL GOTO is no longer required to exit a Boolean within a loop.

  • Disabled state for PL/SQL – Another 11g new feature is a “disabled” state for PL/SQL (as opposed to “enabled” and “invalid” in dba_objects).

  • Easy PL/SQL compiling – Native Compilation no longer requires a C compiler to compile your PL/SQL. Your code goes directly to a shared library.

  • Improved PL/SQL stored procedure invalidation mechanism – A new 11g features will be fine grained dependency tracking, reducing the number of objects which become invalid as a result of DDL.

  • Scalable PL/SQL – 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 code, and a 10%-30% performance boost for code containing SQL.

  • Enhanced PL/SQL warnings – The 11g PL/SQL compiler will issue a warning for a “when others” with no raise.

  • Stored Procedure named notation – Named notation is now supported when calling a stored procedure from SQL.

 


Oracle 11g SQL New Features

  • The /*+result_cache*/ SQL hint – This suggests that the result data will be cached in the data buffers, and not the intermediate data blocks that were accessed to obtain the query results. You can cache both SQL and PL/SQL results for super-fast subsequent retrieval.

  • XML SQL queries – Oracle11g will support query mechanisms for XML including XQuery and SQL XML, emerging standards for querying XML data stored inside tables.

  • SQL Replay – Similar to the previous feature, but this only captures and applies the SQL workload, not total workload.
  • Improved optimizer statistics collection speed – Oracle 11g has improved the dbms_stats performance, allowing for an order of magnitude faster CBO statistics creation
  • SQL execution Plan Management – Oracle 11g SQL will allow you to fix execution plans (explain plan) for specific statements, regardless of statistics or database version changes.
  • Dynamic SQL. DBMS_SQL is here to stay. It’s faster and is being enhanced. DBMS_SQL and NDS can now accept CLOBs (no more 32k limit on NDS). A ref cursor can become a DBMS_SQL cursor and vice versa. DBMS_SQL now supprts user defined types and bulk operations.

  • Fully Automatic SQL Tuning – The 10g automatic tuning advisor makes tuning suggestions in the form of SQL profiles that will improve performance. You can tell 11g to automatically apply SQL profiles for statements where the suggested profile give 3-times better performance that the existing statement. The performance comparisons are done by a new administrative task during a user-specified maintenance window.
  • Improved SQL Access Advisor – The 11g SQL Access Advisor gives partitioning advice, including advice on the new interval partitioning. Interval partitioning is an automated version of range partitioning, where new equally-sized partitions are automatically created when needed. Both range and interval partitions can exist for a single table, and range partitioned tables can be converted to interval partitioned tables.

11g Performance tuning optimization new features

  • Automatic Memory Tuning – Automatic PGA tuning was introduced in Oracle 9i. Automatic SGA tuning was introduced in Oracle 10g. In 11g, all memory can be tuned automatically by setting one parameter. You literally tell Oracle how much memory it has and it determines how much to use for PGA, SGA and OS Processes. Maximum and minimum thresholds can be set.
  • Resource Manager – The 11g Resource Manager can manage I/O, not just CPU. You can set the priority associated with specific files, file types or ASM disk groups.
  • ADDM – The ADDM in 11g can give advice on the whole RAC (database level), not just at the instance level. Directives have been added to ADDM so it can ignore issues you are not concerned about. For example, if you know you need more memory and are sick of being told it, you can ask ADDM not to report those messages anymore.
  • Faster sorting – Starting in 10gr2 we see an improved sort algorithm, “Oracle10gRw introduced a new sort algorithm which is using less memory and CPU resources. A hidden parameter _newsort_enabled = {TRUE|FALSE} governs whether the new sort algorithm will be used.”
  • AWR Baselines – The AWR baselines of 10g have been extended to allow automatic creation of baselines for use in other features. A rolling week baseline is created by default.
  • Adaptive Metric Baselines – Notification thresholds in 10g were based on a fixed point. In 11g, notification thresholds can be associated with a baseline, so the notification thresholds vary throughout the day in line with the baseline.

Oracle 11g security & auditing new features

  • Enhanced Password – Pete Finnigan notes some new Oracle 11g security features “[Oracle 11g] will have case sensitive passwords and also the password algorithm has changed to SHA-1 instead of the old DES based hashing used.”

  • 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. Securefiles are a huge improvement to BLOB data types. Faster, with compression, encryption.

  • Oracle 11g audit vault – Oracle Audit Vault is a new feature that will provide a solution to help customers address the most difficult security problems remaining today, protecting against insider threat and meeting regulatory compliance requirements.

  • Proxy connect for SQL*Plus – New with 10r2 proxy identification in SQL*Plus, the “connect” command has been enhanced to allow for a “proxy”, to aid applications that always connect with the same user ID:

    connect sapr3[scott]/tiger

  • FGAC for UTL_SMTP, UTL_TCP and UTL_HTTP. You can define security on ports and URLs.

  • Fine Grained Dependency Tracking (FGDT). This means that when you add a column to a table, or a cursor to a package spec, you don’t invalidate objects that are dependant on them. Sweet!

  • Database Workload Replay – Oracle “Replay” allows the total database workload to be captured, transferred to a test database created from a backup or standby database, then replayed to test the affects of an upgrade or system change. Currently, they are working to a capture performance overhead of 5%, so you could conceivably capture real production workloads.

Advertisements

Comments»

1. Salih KM - November 16, 2007

it’s a good article… if you can give explain with examples it will be very useful. thanks for the 11g new features.. waiting for more articles from you..

Salih KM

2. rashmi - February 13, 2008

it is a good article.can u send difference between 10g RAC and 11g RAC

3. Rajesh - October 21, 2009

Hi,

Good and detail explanation. What about Active Data Guard?

Rajesh

4. Anup - May 1, 2010

This is good artical

5. Anup - May 1, 2010

This is good article

6. Jack Nicholson - August 5, 2010

Very good detailing work for all new features of Oracle 11g including database and RAC also. Nice work again.

7. Habib - August 6, 2010

THANKS JACK

8. pres - December 3, 2010

I want to express my admiration of your writing skill and ability to make reader to read the while thing to the end..

9. Shadab Alam - March 29, 2011

Thats really good.
if u have some other good article than plz send it to me on
dba.alam@gmail.com

10. CHANDHRA SHEKHAR VADLAMUDI - April 6, 2011

Good Work..
Need some examples and clear details

11. adi - April 14, 2011

Very helpful .Thanks for your efforts

12. shyam sunder - April 19, 2011

Hi Habib,
can u please explain the main difference between 10g Rac and 11g Rac interms of software components,iam a fresher attending the interviews,every one is asking the first question is this.

thank you very much habib,
if u help me in this terms

13. Cheap CAD - October 18, 2012

Hi, I appreciate your speculating as it attracts people’s attention and make this topic discussable.

14. coconut oil uses - April 23, 2013

It is crucial to increase and maintain bacteria levels,
eliminate any yeast infection and parasite infestation, thus help
rid the body of cancer. It’s a great alternative to butter on whole grain breads and wonderful for stirfry. Monolaurin is a compound that effectively prevents viral pathogens protecting our body from infectious bacterias and viruses.

15. banana health - April 25, 2013

The Japanese Banana diet works well, but honestly
it is not what helped me lose over 60 pounds safely in
just a few months. There is no guideline provided as to
what you can and cannot eat for lunch, dinner, and snacks.
Information is power when it comes to weight loss plans and if you want to
know more, the Internet provides a variety of informational
resources that really can’t be compared to.

16. malangkota.go.id - April 27, 2013

Hi i am kavin, its my first occasion to commenting anyplace, when i
read this paragraph i thought i could also create
comment due to this good piece of writing.

17. Mahalia - April 28, 2013

If you have cracked heels, take the peel and rub the inside
on and around the cracks. This means that, you can use almond oil to massage your body whether you have normal, dry or oily skin.
If you are suffering from thin and brittle hair, almond oil can
make your hair grow thicker and stronger over time as well.

18. http://outboundnews.com/ - May 21, 2013

Quality articles is the key to be a focus
for the viewers to go to see the website, that’s what this web page is providing.

19. indigestion and back pain - May 25, 2013

Thanks in favor of sharing such a pleasant thought, paragraph is pleasant, thats why i have read it completely

20. Dungeon Hunter 4 Hack iOS - August 20, 2014

I have read so many articles on the topic of the blogger lovers except this post is really a pleasant paragraph,
keep it up.

21. hair Loss news - September 14, 2014

It is a powerful drug that inhibits the formation of DHT in the system,
eventually to a level that is sufficiently low to encourage
a normal growth cycle. It follows that if we are
to use only the naturally occurring individual units we are then limited
by their normal distribution in the scalp and with larger sessions greater numbers
of each type of unit will be generated. What makes a hair salon really
a good one is the perfect style given to you.

22. work safety plan - November 5, 2014

It’s really a great and useful piece of info. I’m satisfied that you just shared this
useful information with us. Please stay us up to date
like this. Thanks for sharing.

23. online courses in marketing - August 12, 2015

These are one of the few posts that I actually care to comment on. I find this blogger an inspiration and is definitely worth following. I’ve became a subscriber too, so please keep me updated.
online courses in marketing


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: