Oracle Database 11g New Features

SQL Tuning Improvements in Oracle 11G

• SQL Access Advisor can now make recommendations on how partitioning might be applied to existing tables, indexes, and materialized views to improve an application’s performance.
• SQL statements can now tune themselves automatically, this has come up as a revolution to an oracle databases.

The 10g automatic tuning advisor makes tuning suggestions. 11g automatically apply SQL profiles for statements where the suggested profile gives 3-times better performance that the existing statement.

The performance comparisons are done by a new administrative task during a user-specified maintenance window.

• Statistics for the Cost-Based Optimizer (CBO) are now published separately from being gathered.
• Oracle Database 11g now supports retention of historical execution plans for a SQL statement.
  • This means that the CBO can compare a new execution plan against the original plan 
• Statistics can be gathered for multiple columns. Collecting statistics for multiple columns gives the CBO the ability to more accurately select rows based on common multi-column conditions or joins.

Result Caches

Oracle database would provide a method to retain the result set in memory from a complex query. example, a list of all Zip Codes and their corresponding Area names for lookup when processing addresses for new customers.

• The SQL query result cache is an area of memory in the Shared Global Area (SGA) that can retain the result sets that a query generates.
• The PL/SQL function result cache can store the results from a PL/SQL function call.
• The results are immediately available for reuse by any user session.
• For user sessions that connect to the database through an application server, the OCI client cache permits those sessions to simply share the results that are already cached on the application server without having to reissue a query directly against the database.

• 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 SQL and PL/SQL results for super-fast subsequent retrieval.

• SQL> Alter table table_name cache results;

New System/Performance Testing Tools in Oracle 11G

Patching the database’s application code, database patch set, or hardware configuration will affect that database’s performance, which usually meant purchasing a third-party package (e.g. Mercury Interactive’s LoadRunner) to generate a sample workload against the database using the next version of the application code,and then comparing the results against baseline performance for the current application code version.

This can be very useful for oracle regression testing.

Database Replay

Database Replay can capture generated workloads from production systems at the database level. Therefore, it’s no longer necessary to run actual application code to duplicate the load on the database, and this also improves accuracy of the simulated workload because it limits or removes other factors like network latency.

These captured workloads can then be replayed on a quality assurance i.e. UAT database so that the impact of application changes, software patches, and even hardware upgrades can be measured accurately. This feature is especially valuable in detecting performance issues that could potentially cause a production database performance that might go otherwise undetected until the changes have been deployed.

SQL Performance Analyzer (SPA)

The SQL Performance Analyzer enhances the existing Oracle Database 10g SQL tuning components.

The SPA provides the ability to capture a specific SQL workload in a SQL Tuning Set, take a performance baseline before a major database or system change, make the desired change to the system,and then replay the SQL workload against the modified database or configuration.

The before and after performance of the SQL workload can then be compared with just a few clicks of the mouse. The DBA only needs to find any SQL statements that are now performing poorly and tune them via the SQL Tuning Advisor.

Repair Advisors

Oracle 10G introduced some of the database performance advisory framework like Undo Advisor, Segment Advisor, SQL Tuning Advisor, SQL Access Advisor, MTTR Advisor, and the best of all Automatic Database Diagnostic Monitor (ADDM). Oracle Database 11g expands this advisory framework by introducing a new Database Repair Advisor.

The main goal of this new Advisor is to locate root causes of a failure, identify and present options for repairing these root causes, and correct the identified problems with self-healing mechanisms.

Fault Diagnostics in Oracle 11G

Oracle Database 11g adds some of improved fault diagnostics mechanism to make it extremely easy for DBA’s who have little exposure of system/database can easily detect and quickly resolve problems using this advance feature. Here are some of the points mentioned:

Automatic Diagnostic Repository

The Automatic Diagnostic Repository (ADR) is at the heart of Oracle Database 11g’s new fault diagnostic framework. The ADR is a central, file-based repository external to the database itself, and it’s composed of the diagnostic data – alert logs, core dumps, background dumps, and user trace files - collected from individual database components from the first moment that a critical error is detected. When critical errors are detected, Oracle automatically creates an “incident” ticket, notifying the DBA instantly.

Automatic Health Monitoring

When a problem within the database is detected, the new Health Monitor (HM) utility will automatically perform a series of integrity checks to determine if the problem can be traced to corruption within database blocks, redo log blocks, undo segments, or dictionary table blocks. HM can also be fired manually to perform checks against the database’s health on a periodic basis.

Hang Manager

Oracle Database 10g introduced the Hang Analysis tool in Enterprise Manager, and Oracle Database 11g now expands this concept with the Hang Manager.

Incident Packaging Service

If the problem can’t be solved using these tools, it may be time to ask for help from Oracle Support.

The new Incident Packaging Service (IPS) facility provides tools for gathering and packaging all necessary logs that Oracle Support typically needs to resolve a Service Request.

Support Workbench

Though it’s stored outside of the database itself, the ADR can be accessed via either Enterprise Manager or command-line utilities.

Once the ADR has detected and reported a critical problem, the DBA can interrogate the ADR; report on the source of the problem, and in some cases even implement repairs through the Support Workbench, a new facility which comes with Enterprise Manager.

Flashback Enhancements in Oracle 11G

Oracle Database 10g database has come up flashback feature to recover the database with the ability to perform an incomplete recovery of the database with Flashback Database enhancement.

Oracle Database 10g also provided four new logical database recovery features: Flashback Table, Flashback Drop, Flashback Version Query, and Flashback Transaction Query.

Oracle Database 11g expands this new feature by introducing two new Flashback features:

Flashback Data Archive

This new feature offers the ability to retain the reciprocal UNDO information for critical data significantly beyond the point in time that it would be flushed out of the UNDO tablespace.

Therefore, it’s now possible to hold onto these reciprocal transactions essentially indefinitely.

Once this feature is enabled, all retained transaction history can be viewed, and this eliminates the task of creating corresponding history tracking tables for critical transactional tables.

And as you might expect, Oracle Database 11g also provides methods to automatically purge data retained in the Flashback Data Archive once a specified retention period has been exceeded.

Flashback Transaction

This is an extension of the Flashback Transaction Query functionality introduced in Oracle Database 10g, Flashback Transaction allows the DBA to back out of the database one or more transactions.

As well as any corresponding dependent transactions by applying the appropriate UNDO statements for the affected transaction’s to the corresponding affected rows in the database.

Automatic Memory Tuning

In 11g, all memory can be tuned automatically by setting one parameter. Prior to Oracle 11g, the DBA set the sga_target and sga_max_size parameters, allowing Oracle to reallocate RAM within the SGA. The PGA was independent, as governed by the pga_aggregate_target parameter.

Now in Oracle 11g we see the memory_max_target parameter which governs the total maximum RAM for both the PGA and SGA regions and the new memory_target parameter which governs the existing sizes. This allows RAM to be de-allocated from the SGA and transferred to the PGA.

As of Oracle 10g, Automatic Memory Management (AMM) only allows shifting of RAM within the confines of sga_max_size. Now in Oracle 11g, we see this new Automatic Shared Memory Management (ASMM).

The memory_target parameter is dynamic (changeable with "alter system" commands), whereby RAM can be de-allocated from an instances SGA/PGA and re-allocated to another instance.

MEMORY_TARGET specifies the Oracle system-wide usable memory. The database tunes memory to the MEMORY_TARGET value, reducing or enlarging the SGA and PGA as needed. In a text initialization parameter file, if you omit the line for MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, the database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET.

If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero.

After startup, you can then dynamically change MEMORY_TARGET to a non-zero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.

Some of the Administration related features are as follows:

Virtual columns

• Oracle 11g virtual table columns are columns that are actually functions.

For Example you can create table as follows:

SQL>create table t1 (c1 number, c2 number, c3 as (c1+c2) virtual);

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.”

Data Compression

Compression technology that can reduce the amount of storage required for data sets by up to two thirds.

Oracle uses a unique compression algorithm specifically designed to work with relational data. The algorithm works by eliminating duplicate values within a database block, even across multiple columns.

Compressed blocks contain a structure called a symbol table that maintains compression metadata. When a block is compressed, duplicate values are eliminated by first adding a single copy of the duplicate value to the symbol table.

Each duplicate value is then replaced by a short reference to the appropriate entry in the symbol table. Through this innovative design, compressed data is self-contained within the database block as the metadata used to translate compressed data into its original state is contained within the block.

When compared with competing compression algorithms that maintain a global database symbol table, Oracle’s unique approach offers significant performance benefits by not introducing additional I/O when accessing compressed data.

One significant advantage is Oracle’s ability to read compressed blocks directly without having to first uncompress the block. Therefore, there is no measurable performance degradation for accessing compressed data.

In fact, in many cases performance may improve due to the reduction in I/O since Oracle will have to access fewer blocks. Further, you can achieve a more efficient buffer cache by storing more data in the cache without having to increase your memory.

Rman Backups and Datapump exports can also be compressed. Datapump exports can be compressed using the following four options available with compress clause:

1. All.
2. Data-only.
3. Metadata-only.
4. None.

New partitioning capabilities in Oracle 11G

• Partitioning by parent/child references.
• Partitioning by virtual columns.
• More composite partitioning options, including
  • Range/Range.
  • List/Range.
  • List/Hash.
  • List/List.

Interval partitioning, which allows you to automatically create new partitions based on intervals, such as every month or every day.

In previous versions of Oracle, Composite Partitioning, or the use of subpartition, was permitted but the partition/subpartition combinations were limited.

In Oracle Database 11g, these restrictions have been removed to allow for all combinations of Composite Partitions, including the new Interval Partitions.

Partitioning Matrix

Range List Hash Range  YES YES YES List  YES YES YES Interval YES YES YES

Enhancements in Database Security

Passwords can be Case Sensitive from Oracle 11G onwards.

Case Sensitivity

11g will support case sensitive passwords automatically, when a user is created with the familiar syntax, Oracle will automatically store a new type of password verifier (hash value) in the data dictionary table that holds the user information. The same happens when a user or DBA changes an existing password.

Strong Authentication

The verifier is based on 160 bit SHA-1 hashing algorithm, this means that in addition to allow for case sensitivity, the hashing algorithm was changed to create a longer and more secure type of verifier that is accepted generally by the industry and which is employed in several widely used security applications and protocols.

Backwards Compatibility

When a database is migrated, only the hash values based on the legacy password hashing algorithm are available, therefore the passwords will remain case insensitive until they are changed.

It is recommended to change all database passwords soon after the migration to 11g as to take advantage of the added security that the case sensitive passwords and the new SHA-1 hashing provide.

A DBA should change the administrative accounts such as the SYS, SYSTEM etc. immediately and can use a password management policy (Profile) to force end-user password changes within a reasonable time.

Administration

The view DBA_USERS has new column PASSWORD_VERSIONS rendered as follows:

decode(length(u.password),16,'10G ',NULL)||NVL2(u.spare4, '11G ' ,NULL) for example:

SQL> select USERNAME, PASSWORD_VERSIONS from dba_users;

USERNAME PASSWORD

Disabling Password Case Sensitivity

When you create or modify user accounts, by default, passwords are case sensitive. To control the use of case sensitivity in passwords for application-to-password file authentication, set the SEC_CASE_SENSITIVE_LOGON initialization parameter. Only users who have the SYSDBA or SYSOPER privilege can set the SEC_CASE_SENSITIVE_LOGIN parameter. Set it to TRUE to enable case sensitivity or FALSE to disable case sensitivity.

For greater security, Oracle recommends that you enable case sensitivity in passwords. However, if you have compatibility issues with your applications, you can use this parameter to disable password case sensitivity. Examples of application compatibility issues are passwords for your applications being hard-coded to be case insensitive, or different application modules being inconsistent about case sensitivity when sending credentials to start a database session.

Password File

Passwords stored in the password file have also become case sensitive and store the same kind of hash value in addition to the old-style verifier, this is reflected by the ignorecase argument to the orapwd utility, it is 'n' (no) by default so when you create a password file for remote sysdba and sysoper authentication, the SYS password will be case sensitive.

Naturally, when you grant SYSDBA to an existing user, check if this user has the 11G type password listed in DBA_USERS.PASSWORD_VERSIONS otherwise Oracle can only store the old-style verifier for that user in the passwordfile and the password will then not be case sensitive.

If the password of the user that was granted SYSDBA or SYSOPER is changed later, Oracle will still store the new verifier and the password will then become case sensitive.

Reference : http://it.toolbox.com/wiki/index.php/Oracle_11g_New_Features#Hang_Manager

Database Tools - PL/SQL programming

Built-in Functions

•Character Functions

–UPPER(string)  converts text to uppercase

–LOWER  converts text to lowercase

–INITCAP  capitalises first letter of each word

–LENGTH  string length including pads

–LTRIM  trims characters from left

–RTRIM  trims characters from right

–RPAD  pads a string on the right

•Number Functions
            –Trigonometric functions
            –Mathematical functions

•Conversion Functions
            –TO_CHAR converts dates, numbers to varchar2
            –TO_DATE converts text to dates (correct format)
            –TO_NUMBER converts text to numbers

•Number Functions

            –Trigonometric functions
            –Mathematical functions

•Conversion Functions

–TO_CHAR  converts dates, numbers to varchar2

–TO_DATE  converts text to dates (correct format)

–TO_NUMBER converts text to numbers

•Grouping Functions (cf SQL)
            –AVG
            –COUNT
            –SUM

•Miscellaneous
            –object functions
            –SQLCODE
            –SQLERRM
            –UID
            –USER

STORED PROCEDURE

Syntax

CREATE [OR REPLACE] PROCEDURE procedure_name

   (arg1 [IN] datatype, .. arg2 OUT datatype, arg3 INOUT datatype)

AS | IS

  {variables;}

BEGIN

  {statements;}

END;

FUNCTION

Syntax

CREATE [OR REPLACE] FUNCTION function_name

 (arg1 [IN] datatype, .. arg2 OUT datatype,  .. arg4  INOUT datatype )

  RETURN datatype

AS | IS

  {variables;}

BEGIN

  {statements;}

RETURN returnstatement;

END;        

PACKAGE

Encapsulated collection of schema objects (procedures, functions, cursors, variables, constants and exceptions)

Advantages

           •Better organisation of the application (each package has clear, well defined interfaces )
           •Privileges are granted more easily
           •Variables and cursors persist for the session
           •Allow procedure and function overloading
           •Reduce I/O by loading schema objects simultaneously
           •Code re-use

Syntax

  CREATE [OR REPLACE] PACKAGE pack_name AS

  {FUNCTION f_name(arg1…);}

  {PROCEDURE p_name(arg1…);}

  {variables;}

  {cursors;}

  END pack_name;

TRIGGER

Trigger Types and Events
 15 triggers

–4 basic types

  Before Statement

  Before Row

  After Row

  After Statement

–for each of 3 statements

  Insert, Update, Delete

–instead of triggers (Views)

  Insert, Update, Delete

Syntax

CREATE [OR REPLACE] TRIGGER name

    { BEFORE | AFTER }

  {INSERT | UPDATE | DELETE [OF column_name]}

  ON table_name

  [FOR EACH ROW [WHEN [(condition)]]]

       DECLARE

  {declarations;}

       BEGIN

  {code statements;}

       END;

Limitations on Views
–limited updatability
           •distinct not specified
           •every element is a column (not an aggregate function)
           •only the many side of a 1:many
–inserts only into one table
           •must contain all not null columns
           •no violation of integrity constraints

Instead of Triggers

CREATE [OR REPLACE] TRIGGER name

    INSTEAD OF {INSERT | UPDATE | DELETE }  ON view_name

  FOR EACH ROW

         BEGIN

  {statements;}

         END;

eg:-

CREATE OR REPLACE TRIGGER credit

AFTER INSERT ON ord

FOR EACH ROW

DECLARE

  balance number;

BEGIN

  SELECT credit_balance INTO balance FROM customer

  WHERE customer_id = :NEW.customer_id;

  balance := balance - :NEW.total;

  UPDATE customer

  SET credit_balance = balance

  WHERE customer_id = :NEW.customer_id;

END;

CURSOR

eg:-

DECLARE

CURSOR c_emp IS

SELECT * FROM emp

WHERE department = ‘LONDON’;

BEGIN

   FOR emp_record IN c_emp LOOP

      IF emp_record.salary_type = ‘HOURLY’ THEN

  emp_record.salary_rate := emp_record.salary_rate * 1.1;

      ELSEIF emp_record.salary_type = ‘MONTHLY’ THEN

  emp_record.salary_rate := emp_record.salary_rate + 90;

      END IF;

   END LOOP;

   COMMIT;

END;