Oracle Versions

Topics Covered

1. Oracle 10g vs 11g
2. Version Numbering
3. 11g new features
4. 10g new features
5. Oracle vs SQL Server (release dates and features)

Oracle 10g vs 11g

Oracle DB are object-RDMS developed and distributed by Oracle Corporation. A major objective of each upgraded version is to increase performance and scalability over the older version. 

• 2009 - 11gR2 - Database File System (DBFS), Intelligent data placement
• 2007 - 11g   - Cube Organized Materialized Views, Database Replay
• 2004 - 10g   - Flashback Query, Data Pump, Automatic Storage Management, Backup Compression.
  

Oracle 10g	Oracle 11g
2004: Oracle 10g is released	2007: Oracle 11g is released
Primarily it provided grid computing by provision of CPUs and data. To this end, Oracle Enterprise Manager (OEM) gave a powerful grid control mechanism. This version provided enhancements for advanced extensions such as 1.     Oracle RAC (Real Application Clusters) 2.     Oracle Data Guard and Oracle Streams	2 new security features in 11g 1. better password-based authentication with mixed case passwords 2. encryption on tablespace-level and enhancements for data pump encryption and compression.
10g brought about automation of most administration tasks by introducing many self-managing features like 1.     automated DB diagnostic monitor 2.     automated shared memory tuning 3.     automated storage management 4.     automated disk based backup and recovery	11g provides invisible indexes, virtual columns and table partitioning ability to redefine tables which have materialized view logs whilst online. more simplified, improved and automated memory management better ability to diagnose faults through inbuilt infrastructure to prevent, detect, and diagnose help resolve critical DB errors low DB performance issues better performance release 2 has been geared for newer operating systems such as Windows 7, Server 2008 and latest versions of Linux, Unix, Solaris, etc.

1. 11g continued the use of different editions used in 10g which are Enterprise Edition (EE), Standard Edition (SE), Standard Edition One (SE1), Express Edition (EX) and Oracle DB Lite for mobile devices.
2. All in all, 11g is a good upgrade from 10g with many positive enhancements on an evolving technology.
3. The technical documentation which was good in 10g has become even better in 11g, a significant benefit for the DBAs, who depend on it daily.
4. It is common for organizations not to utilize the full features of an Oracle DB. Therefore, the benefits of an upgraded version must be properly utilized for the organization to reduce their cost of ownership, downtime and increase performance, which 11g can deliver.

Version Numbering

• Oracle DB 11g Release 2:    11.2.0.1 – 11.2.0.3      (patchset as of September 2011)
• Oracle DB 11g Release 1:    11.1.0.6 – 11.1.0.7      (patchset as of September 2008)
• Oracle DB 10g Release 2:    10.2.0.1 – 10.2.0.5     (patchset as of April 2010)
  
• Oracle DB 10g Release 1:    10.1.0.2 – 10.1.0.5     (patchset as of February 2006)
• Oracle9i DB Release 2:          9.2.0.1 – 9.2.0.8       (patchset as of April 2007)
• Oracle9i DB Release 1:          9.0.1.0 – 9.0.1.5       (patchset as of December 2003) 

Version-numbering syntax within each release follows the pattern:
                   major.maintenance.application-server.component-specific.platform-specific

 Example:- "11.2.0.3 for 64-bit Windows" means:

• 11th major version of Oracle, maintenance level 2, Oracle Application Server (OracleAS) 0, level 3 for Windows 64-bit.

11g new features

Cost-Based Optimizer Enhancements

1.   The cost bases of various SQL execution steps and internal transformations are now determined in a more accurate manner.

2.   Enhancements to the optimizer's internal costing mechanisms improve the accuracy of execution plan generation, thus generating more optimal plans.

PL/SQL Improvements Oracle DB 11g adds these new features:

PL/SQL Function Result Cache

1. New in Oracle DB 11g is the ability to mark a PL/SQL function to indicate that its result should be cached to allow lookup, rather than recalculation, on the next access when the same parameter values are called.
2. This function result cache saves significant space and time.
3. Oracle does this transparently using the input value as the lookup key.
4. The cache is system-wide so that all distinct sessions invoking the function benefit.
5. If the result for a given set of values changes, you can use constructs to invalidate the cache entry so that it is properly recalculated on the next access.
6. This feature is especially useful when the function returns a value that is calculated from data selected from schema-level tables.
7. For such uses, the invalidation constructs are simple and declarative.
8. Concurrent, multi-user applications that use this feature experience better response times.
9. Applications that implement a session-private scheme consume significantly less memory by using this feature and, therefore, experience improved scalability.

Query Result Cache

1. A separate shared memory pool is now used for storing and retrieving cached results.
2. Query retrieval from the query result cache is faster than rerunning the query.
3. Frequently executed queries will see performance improvements when using the query result cache.
4. The new query result cache enables explicit caching of results in DB memory.
5. Subsequent queries using the cached results will experience significant performance improvements.

Allow Sequences in PL/SQL Expressions

1.  In the previous release of Oracle DB, when a PL/SQL program needed to get a value from syntax, it used SQL. For example:

DECLARE n NUMBER;

BEGIN

SELECT Seq.Nextval INTO n FROM Dual;

END;

2.  This posed a usability problem for PL/SQL programmers and causes runtime performance and scalability problems for the application particularly because it requires the use of a cursor.

3.  In Oracle 11g, it is now possible to simply use Seq.Nextval in a PL/SQL expression. For example:

DECLARE n NUMBER := Seq.Nextval;

BEGIN

...

4.  The same is possible for the CURRVAL pseudocolumn. This feature brings improved usability for the PL/SQL programmer and improved runtime performance and scalability.

Enhancements to the Regular Expression Built-Ins

1. There is new syntax to access the nth subexpression in REGEXP_SUBSTR and REGEXP_INSTR.
2. This is useful if, for example, the matching string is always surrounded by tags like and but you want to extract just what lies between these tags.
3. REGEXP_SUBSTR returns the subexpression. REGEXP_INSTR returns the position.
4. The new built-in REGEXP_COUNT returns the number of times the pattern is matched in the input string.
5. This new functionality is available both in SQL and in PL/SQL.
6. The benefit is increased functionality for the regular expression built-ins.
7. Earlier applications that needed this functionality had to write relatively elaborate application code which is no longer the case.

Functional Completeness of Dynamic SQL for PL/SQL

1. In Oracle DB 11g, native dynamic SQL now supports statements bigger than 32K characters by allowing a CLOB argument. Also included with this feature:

DBMS_SQL.PARSE() gains a CLOB overload

REF CURSOR can be converted to a DBMS_SQL cursor and vice versa to support interoperability

    DBMS_SQL supports the full range of data types (including collections and object types)

    DBMS_SQL allows bulk binds using user-define collection types

2. The features for executing dynamic SQL from PL/SQL had some restrictions in Oracle DB 10g.

3. DBMS_SQL was needed for Method 4 scenarios but it could not handle the full range of data types and its cursor representation was not usable by a client to the DB.

4. Native dynamic SQL was more convenient for non-Method 4 scenarios, but it did not support statements bigger than 32K.

5. Oracle DB 11g removes these and other restrictions to make the support of dynamic SQL from PL/SQL functionally complete.

 

Named and Mixed Notation for User-Defined PL/SQL Subprogram in a SQL Statement

1.   Consider a schema-level function f() declared as:

FUNCTION f(

p1 IN INTEGER := 1,

p2 IN INTEGER := 2,

...

pn IN INTEGER := 99)

RETURN INTEGER

2.  Beginning in this release, it is now possible to invoke the function in a SQL statement. For example, named notation syntax is:

SELECT f(pn=>3, p2=>2, p1=>1) FROM dual

Or, mixed notation is:

SELECT f(1, pn=>3) FROM dual

3.    In previous releases, attempting named or mixed notation resulted in an error.

4.  The benefit is improved usability when a PL/SQL function, with many default parameters, is invoked in a SQL statement and only one or a small number of the actual parameters need to differ from their default values.

New Compound Trigger Type

1. A compound trigger has a section for each of the BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW, and AFTER STATEMENT timing points.
2.  All of these sections can access a common PL/SQL state.
3. The common state is created when the firing statement starts and destroyed when it completes, even when the firing statement causes an error.
4. A compound trigger can be used to speed performance in an auditing scenario where audit rows can be accumulated in the EACH ROW section and bulk inserted to the audit table in the AFTER STATEMENT section.
5. A similar approach is sometimes required to avoid the mutating table error. The alternative to using compound triggers is to model the common state with an ancillary package.
6. This approach is both cumbersome to program and subject to memory leak when the triggering statement causes an error and the AFTER STATEMENT trigger does not fire.
7. This feature brings improved usability for the PL/SQL programmer and improved runtime performance and scalability.

PL/Scope

1. PL/Scope allows you to browse PL/SQL source code analogously to the way that Cscope (see http://cscope.sourceforge.net/) allows you to browse C source code.
2. You can search for and display all types of definitions, declarations, assignments and references in the PL/SQL source code.
3. The PL/SQL compiler can optionally derive the metadata needed to support PL/Scope and store it in the DB catalog. The metadata takes into account the nuances of the language, including scoping and overloading.
4. You can generate reports (especially hyperlinked HTML reports) with supplied report generators.
5. This feature is exposed through Oracle SQL Developer's interactive PL/SQL development environment.
6. PL/Scope supports increased developer productivity, especially for those who need to maintain someone else's code.

PL/SQL CONTINUE Statement

1. Many programming languages allow transfer of control either out of the loop entirely or within a loop back to a new iteration.
2. In previous releases, PL/SQL only allowed transfer of control out of the loop using the EXIT statement.
3. In Oracle DB 11.1, the new CONTINUE statement implements the ability to transfer control within a loop back to a new iteration.
4. Many algorithms require the loop control that this new CONTINUE statement now provides.
5. With this explicit feature, you are no longer required to code several lines of PL/SQL and maintain commonly-adopted style rules.

PL/SQL Hierarchical Profiler

1. PL/SQL Hierarchical Profiler identifies hot spots and performance tuning opportunities in PL/SQL DB applications. It reports the dynamic execution program profile organized by subprogram calls.
2. SQL and PL/SQL execution times are accounted for separately.
3. PL/SQL Hierarchical Profiler requires no special source or compile time preparation.
4. Start and stop procedures in the new package, DBMS_HPROF, control the recording of raw PL/SQL Hierarchical Profiler data.
5. Then other subprograms in this package are used to transform the raw data into schema-level tables to allow the generation of reports.
6.  For example, hyperlinked XML or HTML reports.
7. This feature is part of Oracle SQL Developer's interactive PL/SQL development environment.
8. Programmer productivity and program performance are both enhanced because PL/SQL Hierarchical Profiler directs tuning effort towards those PL/SQL functions that will benefit most.

PL/SQL Inlining Optimization

1. Modern PL/SQL software includes many procedures frequently called in various programs.
2. The power of the PL/SQL optimizing compiler is increased by incorporating the inlining optimization (replacement of a procedure call with a copy of the procedure body).
3. The revised code executes more quickly because call overhead has been eliminated and because further optimizations are possible when the compiler knows the actual values with which the inlined procedure is called.
4. Performance gains can be substantial.
5. The compiler does this optimization automatically.
6. However, you may control the optimization in detail when necessary.
7. Many PL/SQL programs execute faster, as a direct consequence of upgrade, simply by recompiling existing units.

10g new features

Improved Aggregation Performance

The benefit is faster execution of SQL operations containing aggregations.

SQL improvements in Oracle DB 10.2 include:

1. Performance improvements for collection operators.
2. Added support for accessing remote LOBs.
3. Usability and performance enhancements to Expression Filter.
4. Perl compatibility in Regular Expression.
5. Rules Manager for Expression Filter can manage and evaluate an unlimited number of rules, provide efficient synchronization of multiple events and threads, and support a single management environment for rules and application data.

     Rules Manager

1. Rules Manager is a new feature of Oracle 10.2.
2. It enables developers to create applications that process and respond to events of any complexity using rules and policies defined in the DB.
3. It can evaluate events using data from the application and from DB tables.
4. It stores intermediate results to quickly evaluate the next event in a long running composite event (an event made up of two or more simple events).
5. Rules are defined using XML and SQL and can have complex conditions using conjunctions and disjunctions, and specify a set of events, time, and non-occurrence of events with or without a deadline.
6. Event policies control how rules are processed and the duration of an event.
7. Rules can trigger actions that are user-defined procedures running inside an Oracle DB or actions in another application.
8. The benefit of this feature is that rules that are managed in Oracle DB keep pace with changing business conditions and are always up-to-date; rules are easily changed with SQL and are not included in your application or loaded into a memory-based rules repository.
9. Rules can be evaluated efficiently with the complete business context stored in your Oracle DB and data provided by your application.
10.  Event response is flexible; rules can trigger actions in Oracle DB or your application.

Additional advantages of Oracle DB over other approaches include:

1. Manageability, by storing rules and event policies with your application data.
2. Performance, by evaluating rules and coordinating multiple events and application threads with the full capabilities of Oracle DB.
3. Scalability, by evaluating sets of rules of any size.

Regular Expression Enhancements

1. In Oracle 10.2, Regular Expression is enhanced for Perl compatibility.
2. Support for the common Perl REGEXP extensions that are not included and do not conflict with the POSIX standard are added.
3. Oracle's SQL Regular Expression provides a simple yet powerful mechanism for rapidly describing patterns and greatly simplifies the way in which you search, extract, format, and otherwise manipulate text in the DB.

Collection Operator Performance Improvements

1.  Collection operators operate on collections of data objects.

2.  For Oracle DB 10.2, the EQUALITY and MEMBER operators for collections have been improved.

3.  These enhancements have improved the performance for collection operators.

Distributed Large Objects (LOB) Support

1.     Distributed LOBs support provides easy-to-use and efficient support for accessing unstructured data in a distributed environment.

2.     The data interface for LOBs can now INSERT, UPDATE, and SELECT LOBs across dblinks.

3.     The benefit of this new support is the ability to access remote LOBs.

Expression Filter Enhancements

1.   Expression Filter usability and performance enhancements include:

1.   Providing extensible optimizer support for the EVALUATE operator.

2.   Allowing users to specify default values for a subset of attributes in an attribute set.

3.   Allowing users to specify name spaces in stored XPath expressions.

4.   Indexing XPath range predicates.

5.  Allowing users to collect statistics on a set of XPath expressions stored in a table to create an optimal Expression Filter index structure.

2.  Expression Filter features for Oracle DB 10g Release 2 include better usability and improved performance. You can specify default values for expression attributes and specify name spaces in XPath expressions.

PL/SQL Improvements Oracle DB 10.2 adds these new features:

1.  Conditional compilation to allow automatic selection of source code according to the version of Oracle DB.

2.  A new Web Gateway to allow the Oracle instance itself to respond directly to an HTTP request with a response generated by a stored procedure.

3.   A package to support matrix math.

PL/SQL Conditional Compilation

1. Conditional compilation enables PL/SQL programmers to use new language features in a PL/SQL compilation unit without sacrificing the ability to compile the unit in an older DB version that does not support these features.
2. Other typical uses include embedding conditionally compiled debugging and tracing support code in PL/SQL programs.
3. The benefit is that one body of source code can be deployed in a range of Oracle DB versions to take advantage of new features where they are available and to use fallback approaches where they are not.
4. Debugging and tracing code can be left in place without penalizing the size of the executable or runtime efficiency. Such code can be easily activated by a simple recompilation.
5. This provides a feature in PL/SQL that programmers who use other languages have come to expect.

Obfuscation of Dynamically Generated PL/SQL Source Code

1. The existing DBMS_DDL supplied package is enhanced with two new subprograms.
2. The command-line WRAP utility takes a plain text CREATE or REPLACE command that specifies creation of a PL/SQL unit and returns a CREATE or REPLACE command where the text of the PL/SQL unit has been obfuscated.
3. The obfuscated representation is identical to what is produced if the same CREATE or REPLACE command is processed with the command-line WRAP utility.
4. The CREATE_WRAPPED command is a shortcut for and has the same semantics as the EXECUTE IMMEDIATE DBMS_DDL.WRAP (input) command.
5. This feature allows the text of a PL/SQL unit that is created dynamically to be obfuscated.
6. This is critical when an installed product generates new PL/SQL units, for example as part of a customization process, and when the generated code embodies methods that the product vendor considers to be valuable intellectual property.
7. The command-line WRAP utility, available in Oracle DB 10.1 and earlier, allowed the code that a vendor shipped to be obfuscated. This new feature allows custom code generated by shipped vendor code to be obfuscated.

Relaxation of Line Length and Overall Limits for the DBMS_OUTPUT PL/SQL Package

1. The declaration of the package type DBMS_OUTPUT.CHARARR is enhanced to support the increased maximum of 32,767 bytes.
2. Previously, the VARCHAR2 constraint was 255 bytes.
3. This determines the maximum line size that can be written by the DBMS_OUTPUT.PUT and DBMS_OUTPUT.PUT_LINE procedures.
4. The procedure DBMS_OUTPUT.ENABLE (buffer_size integer defaults to 20000) has a new allowed value of NULL for the buffer_size.
5. In this case, NULL means "unlimited." SQL*Plus, through the SET SERVEROUTPUT ON command, enables you to take advantage of the new "unlimited" meaning.
6. PL/SQL programmers frequently use DBMS_OUTPUT and, in Oracle DB 10.1 and earlier, were constrained by the 255 byte limit.
7. When using SQL*Plus, most programmers are regularly caught by the small default overall limit and sometimes by the current 1,000,000 maximum overall limit. In Release 2, the line length limit is increased to 32,767 bytes and the overall limit is removed altogether.

UTL_NLA PL/SQL Package for Matrix Math

1. C and Fortran APIs for matrix math are freely available. Moreover, efficient platform-specific implementations have been written for many of the most popular platforms.
2. The new UTL_NLA package exposes a matrix PL/SQL data type and wrapper PL/SQL subprograms for two of the most popular of these C APIs; namely BLAS and LAPACK.
3. To do matrix manipulation in PL/SQL in Oracle DB 10g Release 1 and earlier, it was necessary to invent a matrix representation based on PL/SQL native data types and then write matrix manipulation routines from scratch.
4. This required substantial programming effort and the performance of the resulting implementation was limited.
5. Oracle DB 10.2, through the new UTL_NLA package, removes the programming effort and delivers a fast implementation.

     Comparison of release dates and features

Oracle G stands for GRID I stands for INTERNET 2009 Oracle 11g R2 - Database File System (DBFS), Intelligent data placement 2007 Oracle 11g - Cube Organized Materialized Views, Database Replay 2004 Oracle10g - Flashback Query, Data Pump, Automatic Storage Management, Backup Compression. 2002 Oracle9i Release 2 - Native XML DB. 2000 Oracle9i Release 1 - RAC and Advanced Analytic Service (business intelligence), Fine-grained auditing. 2000 Oracle8i Release 2 - Oracle tools integrated in middle tier: 9i Application Server available. 1999 First RDBMS on Linux. 1998 Oracle8i - Java support, SQLJ, XML and Oracle interMedia. 1997 Version 8 - First web DB, object-oriented development and multimedia, Binary datatypes, Table Partitioning. Very Large DB (VLDB) features. 1995 Version 7 for 64-bit RDBMS 1994 Version 7 for PC. 1992 Version 7 - for UNIX, distributed transactions, stored procedures, triggers, declarative referential integrity, User-Defined Functions, Cost based optimizer, Varchar2 datatype, ANSI SQL92, Parallel operations including query, load, and index creation. 1989 Version 6.2 - Symmetric cluster access using Oracle Parallel Server.1988 Version 6 - Oracle Financial Applications built on relational DB. 1986 Version 5 - First distributed DB, first true client/server DB, VAX-cluster support, and distributed queries. Row Level Locking. 1984 Version 4 - First portable toolset; first RDBMS for IBM PC, introduced read consistency, was ported to multiple platforms, first interoperability between PC and server. 1982 Version 3 - First portable DB (mainframes, minicomputers, and PC); first RDBMS to support SMP 1980 Version 2 - First commercial SQL DB (PDP11/VAX) 1979 Version 1 - Not commercially released.

SQL Server 2008: SQL Server 2008 R2 - PowerPivot for SharePoint, Multi-Server Administration, Master Data Services (audit data as it changes over time) 2008: SQL Server 2008 - Backup Compression, Data compression, Resource Governor, Transparent Data Encryption, Fine-grained auditing.      2005: SQL Server 2005 - Mirroring, Failover clustering, DB Snapshots, dedicated admin connection (Startup restrict) , Online index rebuild, Online DB restore, Support for 64-bit, granular permissions, encryption, native XML data type 2003: SQL Server 2000 Enterprise edition 64 bit - Itanium 2 support. 2000: SQL Server 2000 - XML and Data Mining, also improved DTS and data analysis tools. User-Defined Functions, Indexed Views, INSTEAD OF and AFTER Triggers, Cascading Referential Integrity Constraints, Multiple Instances. 1998: SQL Server 7.0 - Data Transformation Services, OLAP Services and Microsoft Repository. Row Level Locking. 1996: SQL Server 6.5 - distributed data and transactions, OLE-based management framework, ANSI SQL 92 1993: Renamed as SQL Server 4.2 (Windows NT) 1992: SQL Server beta released. Sybase codebase, DB triggers and stored procedures, roll-forward and roll-back transaction recovery, Client support for Macintosh (Microsoft Windows)