Asynchronous commit (New Feature in Oracle 10g)

When a transaction updates the database, it generates a redo entry corresponding to this update. Oracle Database buffers this redo in memory until the completion of the transaction. When the transaction commits, the log writer (LGWR) process writes redo for the commit, along with the accumulated redo of all changes in the transaction, to disk. By default Oracle Database writes the redo to disk before the call returns to the client. This behavior introduces latency in the commit because the application must wait for the redo to be persisted on disk.
The full syntax of the new WRITE clause is:

COMMIT [WRITE [IMMEDIATE | BATCH] [WAIT | NOWAIT] ]


You can even change the default behavior of COMMIT at the instance level, so that the default behavior of a COMMIT statement is not COMMIT WRITE IMMEDIATE WAIT.
A new initialization parameter, COMMIT_WRITE, lets you set default commit immediacy to IMMEDIATE or BATCH, and default commit wait to WAIT or NOWAIT:


COMMIT_WRITE='{IMMEDIATE | BATCH}, {WAIT | NOWAIT}'


In Above syntax NOWAIT and BATCH options are for Asynchronous commit, means it will not write data from redo logs to log file when commit is fired. By Using this option I/O speed of the operation will be removed by sacrificing Durability of ACID behavior of Oracle Database.


An asynchronous commit returns before the data has actually been written to disk, so if the database crashes before some buffered redo is written to the logs, or a file I/O problem prevents the log writer from writing buffered redo to a non-multiplexed log, then the commit is lost.

By using above options, we can allow LGWR to consider itself
       - when to write to disk and
       - when control is returned to the client.
By specifying WAIT or NOWAIT you can influence when LGWR returns control to the client.

By specifying IMMEDIATE or BATCH you can influence when LGWR will write to disk.


COMMIT WRITE IMMEDIATE WAIT:

• The database by default will write commit records to disk for every commit and control is returned to the client after the writing is completed.
• It is default option for COMMIT.

COMMIT WRITE IMMEDIATE NOWAIT:

• At this time database commit behavior will be changed to IMMEDIATE NOWAIT.
• At this time LGWR will write to the redo logs directly after every commit. It will not wait until the writing is completed before it sends the next writing process to the redo logs
• The client will have control before the writing is completed.

COMMIT WRITE BATCH [WAIT | NOWAIT] :

• Oracle buffers the redo information.
• The log writer will write this redo information to disk, but in its own time. Thus, several I/Os can be "batched". It will store database changes as they occur and store in pre allocated files.

I am taking Example over here.
SQL> DECLARE
 2    SD DATE;
 3  BEGIN
 4   FOR I IN 1..150000 LOOP
 5    UPDATE EMP SET counter = counter + 1 WHERE EMPno=7499;
 6    commit;
 7   END LOOP;
 8  END;
 9  / 
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:15.02
SQL> DECLARE
 2    SD DATE;
 3  BEGIN
 4   FOR I IN 1..150000 LOOP
 5    UPDATE EMP SET counter = counter + 1 WHERE EMPno=7499;
 6    commit write batch nowait;
 7   END LOOP;
 8  END;
 9  / 
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:14.09
SQL> DECLARE
 2    SD DATE;
 3  BEGIN
 4   FOR I IN 1..150000 LOOP
 5    UPDATE EMP SET counter = counter + 1 WHERE EMPno=7499;
 6    commit write immediate wait;
 7   END LOOP;
 8  END;
 9  / 
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:02:20.06

11g New Features Part -1

Today I will cover some of the new features of Oracle 11g for developers.

1. The new REGEXP_COUNT function returns the number of times the search pattern appears in source string.

2. SIMPLE_INTEGER Datatype

The SIMPLE_INTEGER datatype is a subtype of the PLS_INTEGER datatype and can dramatically increase the speed of integer arithmetic in natively compiled code, but only shows marginal performance improvements in interpreted code. The following procedure compares the performance of the SIMPLE_INTEGER and PLS_INTEGER datatypes.

3. CONTINUE Statement

The CONTINUE statement jumps out of the current loop interation and starts the next one. It can be used on its own, or as part of a CONTINUE WHEN statement, as shown below.

4. Sequences in PL/SQL Expressions:

In 11g , You can use 11g sequnece in Expression, means you don't need to use SQL statement to fetch NEXTVAL or CURRVAL count.

Prior to 11g , to fetch sequence value you need to write sql statement like

Select test_seq.nextval into val_seq from dual;

But in 11g , you can use

val_seq := test_seq.nextval;

5. Dynamic SQL Enhancements

Native dynamic SQL and the DBMS_SQL package now support dynamic SQL statements larger than 32 KB. The EXECUTE IMMEDIATE statement, OPEN-FOR statement and DBMS_SQL.PARSE procedure all accept SQL statements in the form of CLOBs.

You can use DBMS_SQL.TO_REFCURSOR procedure to convert DBMS_SQL cursor id to REF CURSOR.

You can use  DBMS_SQL.TO_CURSOR_NUMBER function converts a REF CURSOR into a DBMS_SQL cursor ID.

6. Automatic Subprogram Inlining:

Automatic subprogram inlining can reduce the overheads associated with calling subprograms, whilst leaving your original source code in its normal modular state. This is done by replacing the subprogram calls with a copy of the code in the subprogram at compile time.

The process of subprogram inlining is controlled by the PLSQL_OPTIMIZE_LEVEL parameter and the INLINE pragma. When PLSQL_OPTIMIZE_LEVEL=2 (the default), the INLINE pragma determines whether the following statement or declaration should be inlined or not.

When PLSQL_OPTIMIZE_LEVEL=3, the optimizer may inline code automatically. In this case the INLINE pragma can turn it off inlining for a statement, or increase the likelihood that the optimizer will choose to inline a statement.

7. PL-SQL Scope

PL/Scope is a tool that gathers information about user defined identifiers at compile time. Collection of PL/Scope data is controlled by the PLSCOPE_SETTINGS parameter, which has a default setting of "IDENTIFIERS:NONE". Switch this value to "IDENTIFIERS:ALL" to enable collection.

The data is stored in the SYSAUX tablespace, so the current space used for PL/Scope data can be displayed with the following query.

The PL/Scope data is available from the %_IDENTIFIERS views.

8. PL/SQL Native Compiler Generates Native Code Directly:

In Oracle 11g, PL/SQL native compilation requires no C compiler, no DBA intervention and is fully supported in a RAC environment. By setting the PLSQL_CODE_TYPE to a value of NATIVE, rather than the default value of INTERPRETED, code is compiled directly to machine code and stored in the SYSTEM tablespace. When the code is called,it is loaded into shared memory, making it accessible for all sessions in that instance. The %_PLSQL_OBJECT_SETTINGS views include the current PLSQL_CODE_TYPE setting for each PL/SQL object.

9. PL/SQL Function Result Cache:

A function result cache can save significant space and time. Each time a result-cached function is invoked with different parameter values; those parameters and their result are stored in the cache. Subsequently, when the same function is invoked with the same parameter values, the result is retrieved from the cache, instead of being recomputed.

To use it, use the RESULT_CACHE clause in each PL/SQL function whose results you want cached. Because the function result cache is stored in a shared global area (SGA), it is available to any session that runs your application.

If you convert your application to PL/SQL function result caching, your application will use more SGA, but significantly less total system memory.

10. Named and Mixed Notation in PL/SQL Subprogram Invocations

Before Release 11.1, a SQL statement that invoked a PL/SQL subprogram had to specify the actual parameters in positional notation. As of Release 11.1, named and mixed notations are also allowed. This improves usability when a SQL statement invokes a PL/SQL subprogram that has many defaulted parameters, and few of the actual parameters must differ from their default values.

11. Compound Triggers:

A compound trigger is a Database Manipulation Language (DML) trigger that can fire at more than one timing point.

The body of a compound trigger supports a common PL/SQL state that the code for all of its sections can access. The common state is established when the triggering statement starts and destroyed when the triggering statement completes, even when the triggering statement causes an error.

12. PL/SQL Hierarchical Profiler

The PL/SQL hierarchical profiler reports the dynamic execution profile of your PL/SQL program, organized by subprogram calls. It accounts for SQL and PL/SQL execution times separately. Each subprogram-level summary in the dynamic execution profile includes information such as number of calls to the subprogram, time spent in the subprogram itself, time spent in the subprogram's subtree (that is, in its descendent subprograms), and detailed parent-children information.

You can browse the generated HTML reports in any browser. The browser's navigational capabilities, combined with well chosen links, provide a powerful way to analyze performance of large applications, improve application performance, and lower development costs.

13. DDL With the WAIT Option

DDL commands require exclusive locks on internal structures. If these locks are not available the commands return with an "ORA-00054: resource busy" error message. This can be especially frustrating when trying to modify objects that are accessed frequently. To get round this Oracle 11g includes the DDL_LOCK_TIMEOUT parameter, which can be set at instance or session level using the ALTER SYSTEM and ALTER SESSION commands respectively.

The DDL_LOCK_TIMEOUT parameter indicates the number of seconds a DDL command should wait for the locks to become available before throwing the resource busy error message. The default value is zero. To see it in action, create a new table and insert a row, but don't commit the insert.

ALTER SESSION SET ddl_lock_timeout=30;
ALTER TABLE lock_tab ADD ( description  VARCHAR2(50));
The session will wait for 30 seconds before failing.

14. Invisible Indexes :

Oracle 11g allows indexes to be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level. Indexes can be created as invisible by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command.

   CREATE INDEX index_name ON table_name(column_name) INVISIBLE;

   ALTER INDEX index_name INVISIBLE;

   ALTER INDEX index_name VISIBLE;

15. Query Result Cache:

Oracle 11g allows the results of SQL queries to be cached in the SGA and reused to improve performance. Set up the following schema objects to see how the SQL query cache works.

Adding the RESULT_CACHE hint to the query tells the server to attempt to retrieve the information from the result cache. If the information is not present, it will cache the results of the query provided there is enough room in the result cache. Since we have no cached results, we would expect the first run to take approximately five seconds, but subsequent runs to be much quicker.

The default action of the result cache is controlled by the RESULT_CACHE_MODE parameter. When it is set to MANUAL, the RESULT_CACHE hint must be used for a query to access the result cache.

If we set the RESULT_CACHE_MODE parameter to FORCE, the result cache is used by default, but we can bypass it using the NO_RESULT_CACHE hint.

16. Table Compression Enhancements:

       

Table compression was introduced in Oracle 9i as a space saving feature for data warehousing projects. In 11g it is now considered a mainstream feature that is acceptable for OLTP databases. In addition to saving storage space, compression can result in increased I/O performance and reduced memory use in the buffer cache. These advantages do come at a cost, since compression incurs a CPU overhead, so it won't be of benefit to everyone.

The compression clause can be specified at the tablespace, table or partition level with the following options:

-   NOCOMPRESS - The table or partition is not compressed. This is the default action when no       compression clause is specified.

- COMPRESS - This option is considered suitable for data warehouse systems. Compression is   enabled on the table or partition during direct-path inserts only.

- COMPRESS FOR DIRECT_LOAD OPERATIONS - This option has the same affect as the simple   COMPRESS keyword.

- COMPRESS FOR ALL OPERATIONS - This option is considered suitable for OLTP systems. As the name implies, this option enables compression for all operations, including regular DML statements. This option requires the COMPATIBLE initialization parameter to be set to 11.1.0 or higher. In 11gR2,this option has been renamed to COMPRESS FOR OLTP and the original name has been deprecated.

The restrictions associated with table compression include:

• Compressed tables can only have columns added or dropped if the COMPRESS FOR ALL           OPERATIONS option was used.
• Compressed tables must not have more than 255 columns.
• Compression is not applied to lob segments.
• Table compression is only valid for heap organized tables, not index organized tables.
• The compression clause cannot be applied to hash or hash-list partitions. Instead, they must  inherit their compression settings from the tablespace, table or partition settings.
• Table compression cannot be specified for external or clustered tables.

17. Read-Only Tables in Oracle Database 11g Release 1

In previous Oracle releases, tables could be made to appear read-only to other users by only granting the SELECT object privilege to them, but the tables remained read-write for the owner. Oracle 11g allows tables to be marked as read-only using the ALTER TABLE command.

 ALTER TABLE table_name READ ONLY;

        ALTER TABLE table_name READ WRITE;

18. Fine-Grained Dependencies for Triggers

Oracle Database 11g Release 1 (11.1) brought both fine-grained dependency tracking and the new possibility that a trigger might be a dependency parent by virtue of the new FOLLOWS keyword.

In release 11.1, dependents on triggers did not have fine-grained dependency. In release 11.2, this fine-grained dependence exists. (Release 11.2 also provides the new PRECEDES keyword which also allows trigger-upon-trigger dependencies.)

19. IGNORE_ROW_ON_DUPKEY_INDEX Hint for INSERT Statement

With INSERT INTO TARGET...SELECT...FROM SOURCE, a unique key for some to-be-inserted rows may collide with existing rows. The IGNORE_ROW_ON_DUPKEY_INDEX allows the collisions to be silently ignored and the non-colliding rows to be inserted. A PL/SQL program could achieve the same effect by first selecting the source rows and by then inserting them one-by-one into the target in a block that has a null handler for the DUP_VAL_ON_INDEX exception. However, the PL/SQL approach would take effort to program and is much slower than the single SQL statement that this hint allows.

This hint improves performance and ease-of-programming when implementing an online application upgrade script using edition-based redefinition.

20. Analytic Functions 2.0

New and enhanced analytical functions are introduced in this release. A new ordered aggregate, LISTAGG, concatenates the values of the measure column. The new analytic window function NTH_VALUE (a generalization of existing FIRST_VALUE and LAST_VALUE functions) gives users the functionality of retrieving an arbitrary (or nth) record in a window.

The LAG and LEAD functions are enhanced with the IGNORE NULLS option.

The new and enhanced SQL analytical functions allow more complex analysis in the database, using (simpler) SQL specification and providing better performance.

DIFFRENCE BETWEEN PLS-INTEGER, BINARY_INTEGER AND SIMPLE_INTEGER

DIFF.BETWEEN PLS-INTEGER, BINARY_INTEGER AND SIMPLE_INTEGER

PLS_INTEGER:

• PLS_INTEGER stores data  in the hardware arithmetic format. So It is faster then NUMBER and its subtypes.
• PLS_INTEGER requires less storage.
• PLS_INTEGER  has range of -2,147,483,648 through 2,147,483,647, represented in 32 bits.
• Use PLS_INTEGER  when more calculations are in use.
• A calculation with two PLS_INTEGER values that overflows the PLS_INTEGER range raises an overflow exception, even if you assign the result to a NUMBER data typ
• PLS_INTEGER and its subtypes can be implicitly converted to these data types:

• CHAR

-  VARCHAR2

• NUMBER
• LONG

SIMPLE_INTEGER:

• It is new feature of the 11g.
• It has same range as PLS_INTEGER and NOT NULL constraint.
• We can not pass null value to procedure , if the procedure have parameter as SIMPLE_INTEGER.
• We can declare SIMPLE_INTEGER with null values in declarative section.
• If you know that a variable will never have the value NULL or need overflow checking, declare it as SIMPLE_INTEGER rather than PLS_INTEGER. Without the overhead of checking for nullness and overflow, SIMPLE_INTEGER performs significantly better than PLS_INTEGER.

For Example:
        The speed improvements are a result of two fundamental differences between the two         datatypes. First, SIMPLE_INTEGER and PLS_INTEGER have the same range (-2,147,483,648 through 2,147,483,647), but SIMPLE_INTEGER wraps round when it exceeds its bounds, rather than throwing an error like PLS_INTEGER.

SET SERVEROUTPUT ON

DECLARE

 l_simple_integer SIMPLE_INTEGER := 2147483645;

BEGIN

 FOR i IN 1 .. 4 LOOP

   l_simple_integer := l_simple_integer + 1;

   DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_simple_integer, 'S9999999999'));

 END LOOP;

 FOR i IN 1 .. 4 LOOP

   l_simple_integer := l_simple_integer - 1;

   DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_simple_integer, 'S9999999999'));

 END LOOP;

END;

/

+2147483646

+2147483647

-2147483648

-2147483647

-2147483648

+2147483647

+2147483646

+2147483645

BINARY_INTEGER:

• The BINARY_INTEGER datatype is used for declaring signed integer variables.
• BINARY_INTEGER variables are stored in binary format, which takes less space.
• Calculations on binary integers can also run slightly faster because the values are already in a binary format.

Native Dynamic SQL vs DBMS_SQL

Native Dynamic SQL	DBMS_SQL
Easy to use and concise.	Often long-winded and awkward.
PL/SQL interpreter has built in support for Native Dynamic SQL so it is more efficient than DBMS_SQL.	DBMS_SQL uses a Procedural API so it is generally slower than Native Dynamic SQL.
Supports user defined types.	Does not support user defined types.
Supports FETCH INTO record types	Does not support FETCH INTO record types
Not supported in client site code.	Supported in client side code.
Does not support DESCRIBE_COLUMNS	Supports DESCRIBE_COLUMNS
Does not support bulk Dynamic SQL, but it can be faked by placing all statements in a PL/SQL block.	Supports bulk Dynamic SQL.
Only supports Single row Updates/Deletes with RETURNING clause.	Supports Single and Multiple row Updates/Deletes with RETURNING clause.
Does not support SQL statements bigger than 32K	Does support SQL statements bigger than 32K
Parse required for every execution	Parse once, execute many possible

PRAGMA DIRECTIVES

PRAGMA DIRECTIVES:
Pragma compiler directives instructs compiler with certain information. It is Processed at compile time not at run time. The directive restricts member subprograms to query or modify database tables and packaged variables.


PRAGMA directive should be in declarative section of the PL-SQL block.
As of release 11.2 we have 5 PRAGMA directives:


1) PRAGMA AUTONOMOUS_TRANSACTION
2) PRAGMA SERIALLY_REUSABLE
3) PRAGMA RESTRICT_REFRENCES
4) PRAGMA EXCEPTION_INIT
5) PRAGMA INLINE


We will see fundamental use of each Pragma:

PRAGMA AUTONOMOUS_TRANSACTION :
This directive instructs the compiler to treat current PL-SQL block as independent from the mail transaction from which it is called. This means that any changes made to the database in the autonomous transaction are independent of the main transaction and are either committed or rolled back without affecting the main transaction.


Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. You can log events, increment retry counters, and so on, even if the main transaction rolls back.


Autonomous triggers (Trigger with Autonomous transaction directive) can contain commit or rollback as well as DDL statements.


If you try to exit an active autonomous transaction without committing or rolling back, Oracle raises an exception. If the exception goes unhandled, or if the transaction ends because of some other unhandled exception, the transaction is rolled back.


Changes made by an autonomous transaction become visible to other transactions when the autonomous transaction commits. The changes also become visible to the main transaction when it resumes, but only if its isolation level is set to READ COMMITTED (the default). If you set the isolation level of the main transaction to SERIALIZABLE, changes made by its autonomous transactions are not visible to the main transaction when it resumes.
In the main transaction, rolling back to a savepoint located before the call to the autonomous subprogram does not roll back the autonomous transaction. Remember, autonomous transactions are fully independent of the main transaction.


If an autonomous transaction attempts to access a resource held by the main transaction (which cannot resume until the autonomous routine exits), a deadlock can occur. Oracle raises an exception in the autonomous transaction, which is rolled back if the exception goes unhandled.

PRAGMA SERIALLY_REUSABLE:


sing this Pragma, State of any package will be lost after its call to server. So It will be helpful to reduce memory consumption in long running process.The state information for this package is not kept in Memory per user but It is kept in SGA for reuses.


The pool is kept in SGA memory so that the work area of a package can be reused across users who have requests for the same package. In this scheme, the maximum number of work areas needed for a package is only as many as there are concurrent users of the package, which is typically much fewer than the total number of logged on users. The user of "serially reusable" packages does increase the shared-pool requirements slightly, but this is more than offset by the decrease in the per-user memory requirements. 

Further, Oracle ages out work areas not in use when it needs to reclaim shared pool memory.


PRAGMA RESTRICT_REFERENCES:

This directive instructs compiler that Pl-SQL block should not read or write table or package variables.


If user defined functions are used to any extent it is advisable to encapsulate them within a package and designate this package as "pure", that is, the functions in the package do not modify the database. The "purity" of the package is denoted with the PL/SQL pragma: RESTRICT_REFERENCES. This pragma has one mandatory argument and three optional arguments. The optional arguments define the level of the "purity" of the function.


You can declare the pragma RESTRICT_REFERENCES only in a package spec or object type spec. You can specify up to four constraints (RNDS, RNPS, WNDS, WNPS) To call the function from parallel queries, you must specify all four constraints. No constraint implies another. For example, WNPS does not imply RNPS.


When you specify TRUST, the function body is not checked for violations of the constraints listed in the pragma. The function is trusted not to violate them.


If you specify DEFAULT instead of a function name, the pragma applies to all functions in the package spec or object type spec (including, in the latter case, the system-defined constructor). You can still declare the pragma for individual functions. Such pragmas override the default pragma.


A RESTRICT_REFERENCES pragma can apply to only one function declaration. So, a pragma that references the name of overloaded functions always applies to the nearest foregoing function declaration.


RNDS  
Asserts that the subprogram reads no database state (does not query database tables).

WNDS
Asserts that the subprogram writes no database state (does not modify tables).

RNPS
Asserts that the subprogram reads no package state (does not reference the values of packaged variables)
You cannot specify RNPS if the subprogram invokes the SQLCODE or SQLERRM function.

WNPS
Asserts that the subprogram writes no package state (does not change the values of packaged variables).
You cannot specify WNPS if the subprogram invokes the SQLCODE or SQLERRM function.

TRUST
Asserts that the subprogram can be trusted not to violate one or more rules.
When you specify TRUST, the subprogram body is not checked for violations of the constraints listed in the pragma. The subprogram is trusted not to violate them. Skipping these checks can improve performance. TRUST is needed for functions written in C or Java that are invoked from PL/SQL, since PL/SQL cannot verify them at run time.

Usage Notes:
A RESTRICT_REFERENCES pragma can appear only in a package specification or object type specification. Typically, this pragma is specified for functions. If a function calls procedures, specify the pragma for those procedures also.
To invoke a subprogram from parallel queries, you must specify all four constraints—RNDS, WNDS, RNPS, and WNPS. No constraint implies another.
Example:
CREATE PACKAGE test_sr AS
  FUNCTION test_function RETURN BOOLEAN;
  PRAGMA RESTRICT_REFERENCES (test_function, WNDS, RNPS);
END loans;
/

PRAGMA EXCEPTION_INIT:


This Pragma will associate used defined exception with Oracle Error number. You can catch user defined exception in exception handler.
PRAGMA EXCEPTION_INIT(exception_name, ORACLE_error_number);
where exception_name is the name of a previously declared exception.
DECLARE
insufficient_privileges EXCEPTION;
PRAGMA EXCEPTION_INIT(insufficient_privileges, -1031);
---
----
BEGIN
…
EXCEPTION
WHEN insufficient_privileges THEN
— handle the error
…
END;

PRAGMA INLINE :

The INLINE pragma specifys that a subprogram call is, or is not, to be inlined. Inlining replaces a subprogram call (to a subprogram in the same program unit) with a copy of the called subprogram.

To reiterate, with subprogram inlining, Oracle will re-write our code to replace a call or calls to a subprogram with the executable code of the subprogram itself.

when the INLINE pragma immediately precedes one of these statements, the pragma affects every call to the specified subprogram in that statement

• Assignment
• Call
• Conditional
• CASE
• CONTINUE WHEN
• EXECUTE IMMEDIATE
• EXIT WHEN
• LOOP
• RETURN

The INLINE pragma does not affect statements that are not in the preceding list.