Oracle Support

Oracle Database 11g brings industry leading performance, scalability, security and reliability. It provides all-inclusive features to easily manage the demanding transaction processing, business intelligence, and content management applications. Based on these new features, Oracle Database 11g makes it easier for clients to take advantage of the grid architecture. I have presented these to my multiple clients and upgraded their Oracle Database to 11.2.0.4.

Oracle Database 11g Architecture Diagram:

1. Adaptive Cursor Sharing

DBAs are always encouraging developers to use bind variables, but when bind variables are used against columns containing skewed data they sometimes lead to less than optimum execution plans. This is because the optimizer peeks at the bind variable value during the hard parse of the statement, so the value of a bind variable when the statement is first presented to the server can affect every execution of the statement, regardless of the bind variable values.

Oracle 11g uses Adaptive Cursor Sharing to solve this problem by allowing the server to compare the effectiveness of execution plans between executions with different bind variable values. If it notices suboptimal plans, it allows certain bind variable values, or ranges of values, to use alternate execution plans for the same statement. This functionality requires no additional configuration.

BIND_AWARE and NO_BIND_AWARE Hints

From 11.1.0.7 onward it is possible to skip the monitoring that is required to detect bind-sensitive queries by using the BIND_AWARE hint. In the following example, the presence of the hint tells the optimizer that we believe the query is bind-sensitive, so it should use bind-aware cursor sharing from the first execution.

SELECT /*+ BIND_AWARE */ MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;

The hint will only work if the query uses bind variables in WHERE clause predicates referencing columns with histograms.

There is also a NO_BIND_AWARE hint that tells the optimizer to ignore bind-sensitive queries, effectively hiding the query from the adaptive cursor sharing functionality.

Bind-aware cursor sharing has a small overhead associated with it, which is why Oracle use the "adaptive" approach to identifying queries that would benefit from bind-aware cursor sharing. Adding the hint to queries that will not benefit from it is a waste.

2. SQL Plan

In Oracle Database 11g, when an already calculated optimizer plan needs to be updated because of changes in the underlying factors, it does not go into effect immediately. Rather Oracle evaluates the new plan and implements it in only if improves on the one already there. In addition, tools and interfaces are available to see the history of the plans calculated for each query and how they compare

3. Automated Maintenance Tasks

Oracle 11g includes three automated database maintenance tasks:

• Automatic Optimizer Statistics Collection - Gathers stale or missing statistics for all schema objects. The task name is 'auto optimizer stats collection'.
• Automatic Segment Advisor - Identifies segments that could be reorganized to save space. The task name is 'auto space advisor'.
• Automatic SQL Tuning Advisor - Identifies and attempts to tune high load SQL. The task name is 'sql tuning advisor'.

These tasks run during maintenance windows scheduled to open over night. Configuration of the maintenance tasks, their schedules and resource usage is possible using Enterprise Manager or PL/SQL APIs.

The "Automated Maintenance Tasks" screen displays the maintenance window for each task (Server > Automated Maintenance Tasks (link under Scheduler section)). Click the "Configure" button to navigate to the configuration screens.

Note: The DISABLE and ENABLE procedures of the DBMS_AUTO_TASK_ADMIN package

• EXEC DBMS_AUTO_TASK_ADMIN.disable;
• EXEC DBMS_AUTO_TASK_ADMIN.enable;

4. Automatic Memory Management

Oracle has made great strides in simplifying memory management over the last few versions of the database. Oracle 9i automated PGA management by introducing PGA_AGGREGATE_TARGET parameter. Oracle 10g continued this trend by automating SGA management using the SGA_TARGET parameter. Oracle 11g takes this one step further by allowing you to allocate one chunk of memory, which Oracle uses to dynamically manage both the SGA and PGA.

Automatic memory management is configured using two new initialization parameters:

• MEMORY_TARGET: The amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. This parameter is dynamic, so the total amount of memory available to Oracle can be increased or decreased, provided it does not exceed the limit. The default value is "0".
• MEMORY_MAX_TARGET: This defines the maximum size the can be increased to without an instance restart. If the is not specified, it defaults to setting.

When using automatic memory management, the and act as minimum size settings for their respective memory areas. To allow Oracle to take full control of the memory management, these parameters should be set to zero.

5. Automatic SQL Tuning

As part of Automatic SQL Tuning, Oracle 11g automatically runs the SQL Tuning Advisor against high impact SQL statements during maintenance windows. This process involves the following steps:

• AWR statistics are used to compile an ordered list of the SQL statements with the greatest performance impact on the system, where the impact is the sum of the CPU and I/O times for the statement during the past week. The list excludes statements that are inherently less tunable, such as recently (within a month) tuned recursive statements, parallel queries, DML, DDL and SQL statements whose performance problems are caused by concurrency issues.
• The SQL tuning advisor is run against each statement in turn. The outcome may include both SQL profiles and other recommendations.
• Suggested SQL profiles are performance tested, and those that result in at least a threefold improvement are accepted if the parameter is set to , or reported if it is set to .
• The accepted SQL profiles are optionally implemented . Several factors many prevent SQL profiles from being implemented automatically, including stale optimizer statistics of dependent objects. The column of the view indicates if SQL profiles are created manually () or automatically ().

6. AWR Baseline Enhancements

The Automatic Workload Repository (AWR) was introduced in Oracle 10g and included some simple baseline functionality. Creating a baseline allowed a specified range of snapshots to be retained, regardless of the AWR retention policy, and used for performance comparisons. This functionality, and the DBMS_WORKLOAD_REPOSITORY package that manages it, has been extended in Oracle 11g.

7. Case Sensitive Passwords

Case sensitive passwords (and auditing) are a default feature of newly created Oracle 11g databases. The Database Configuration Assistant (DBCA) allows you to revert these settings back to the pre-11g functionality during database creation.

The SEC_CASE_SENSITIVE_LOGON initialization parameter gives control over case sensitive passwords. If existing applications struggle to authenticate against 11g, you can use the ALTER SYSTEM command to turn off this functionality.

SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE

SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

8. Database Replay

The Database Replay functionality of Oracle 11g allows you to capture workloads on a production system and replay them exactly as they happened on a test system. This provides an accurate method to test the impact of a variety of system changes including:

• Database upgrades.
• Operating system upgrades or migrations.
• Configuration changes, such as changes to initialization parameters or conversion from a single node to a RAC environment.
• Hardware changes or migrations.

The capture and replay processes can be configured and initiated using PL/SQL APIs, or Enterprise Manager, both of which are demonstrated in this article. To keep things simple, the examples presented here are performed against two servers (prod-11g and test-11g), both of which run an identical database with a SID of DB11G.

9. DDL With the WAIT Option (DDL_LOCK_TIMEOUT)

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

10. Diagnostics Repository

Includes the trace files, dumps, and core files that are also present in previous releases, plus new types of diagnostic data that enable customers and Oracle Support to identify, investigate, track, and resolve problems quickly and effectively.

11. Direct NFS

Beginning with Release 11g, Oracle Database includes an advanced fault diagnosability infrastructure for collecting and managing diagnostic data. Diagnostic data For improved NFS performance, Oracle recommend using the Direct NFS Client shipped with Oracle 11g. The direct NFS client looks for NFS details in the following locations.

$ORACLE_HOME/dbs/oranfstab

/etc/oranfstab

/etc/mtab

Since we already have our NFS mount point details in the "/etc/fstab", and therefore the "/etc/mtab" file also, there is no need to configure any extra connection details.

12. Fine-Grained Access to Network Services

Oracle allows access to external network services using several PL/SQL APIs (UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP and UTL_INADDR), all of which are implemented using the TCP protocol. In previous versions of the database, access to external services was effectively an on/off switch based on whether a user was granted execute permissions on a specific package or not. Oracle 11g introduces fine grained access to network services using access control lists (ACL) in the XML DB repository, allowing control over which users access which network resources, regardless of package grants.

Access control lists can be created, amended and deleted in the XML DB repository directly using FTP or WebDav. In addition, Oracle provide the DBMS_NETWORK_ACL_ADMIN and DBMS_NETWORK_ACL_UTILITY packages to allow ACL management from PL/SQL. These APIs are the subject of this article

13. Finer Grained Dependency Management

In previous releases, object dependencies were managed at the object level, so altering an object automatically invalidated all dependent objects. Oracle 11g has more granular dependency management, so only changes that directly affect an object will cause an invalidation.

14. Flashback Data Archive

Most flashback features work at the logical level, in that they don't directly allow you to recover an object. They simply give you a view of how the data was in the past, which in turn can be used to recover from logical corruption, such as accidental deletions. Typically, this view of the past is constructed using undo segments, which are retained for a period of time indicated by the UNDO_RETENTION parameter. Once the undo information is lost, the view of the past is lost also. At least that's how it was before Oracle 11g introduced the Flashback Data Archive functionality.

A flashback data archive is essentially an extended store of undo information, allowing some logical flashback operations to extend way back into the past with no difference in how they are coded. An individual flashback archives consists of one or more tablespaces, or parts of tablespace. Each flashback archive has a name, retention period and a quota on each associated tablespace. The database can have multiple flashback data archives, but only a single default archive. When a DML transaction commits an operation on a flashback archive enabled table, the Flashback Data Archiver (FBDA) process stores the pre-image of the rows into a flashback archive, along with metadata of the current rows. The FBDA process is also responsible for managing the data within the flashback archives, such as purging data beyond the retention period.

15. Flashback Transaction

As the name implies, Flashback Transaction allows the changes made by a transaction to be undone, optionally including changes made by dependent transactions.

16. Function Result Cache

The cross-session PL/SQL function result cache provides a simple way to boost the performance of PL/SQL functions by saving the results of function calls for specific combinations of input parameters in the SGA. These results can be reused by any session calling the same function with the same parameters. This can result in a significant performance boost when functions are called for each row in an SQL query, or within a loop in PL/SQL.

Enabling a function to use the function result cache is as simple as adding the RESULT_CACHE clause, and optionally the RELIES_ON clause.

17. Health Monitor

The Health Monitor allows us to perform the integrity checks manually, rather than waiting for the reactive tests to take place. This may help you identify and fix problems before they are ever noticed by users.

Access to Health Monitor is available using the package or Enterprise Manager. The available checks are displayed using the view.

SQL> SELECT name FROM v$hm_check WHERE internal_check='N';
NAME
----------------------------------
DB Structure Integrity Check
Data Block Integrity Check
Redo Integrity Check
Transaction Integrity Check
Undo Segment Integrity Check
Dictionary Integrity Check
6 rows selected.
SQL>

The DBMS_HM.RUN_CHECK procedure is used to run a specific check with the appropriate parameters.

BEGIN
 DBMS_HM.run_check (
 check_name => 'DB Structure Integrity Check',
 run_name => 'my_test_run');
END;
/
PL/SQL procedure successfully completed.

18. 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 INVISIBLEkeyword, and their visibility can be toggled using the ALTER INDEX command.

19. Logminer

In previous versions of Oracle the LogMiner viewer was a separate Java based console, but in Oracle 11g it has been incorporated into Enterprise Manager and integrated with the new Flashback Transaction feature, making it simple to recover transactions that have had an undesirable affect. The logminer functionality is accessed using the "View and Manage Transactions" link on the "Availability" tab.

20. Online Table Redefinition

By default, online table redefinitions no longer invalidate dependent objects (PL/SQL, views, synonyms etc.), provided the redefinition does not logically affect them. An exception to this behavior is triggers, which are associated directly with a table.

21. Partitioning

The partitioning enhancements in Oracle 11g Release 1.

• Extended Composite Partitioning
• Interval Partitioning
• System Partitioning
• Reference Partitioning
• Virtual Column-Based Partitioning
• Single Partition Transportable for Oracle Data Pump
• Partition Advisor
• Enhanced Statistics Collection for Partitioned Objects

22. Query Result Cache

Oracle 11g allows the results of SQL queries to be cached in the SGA and reused to improve performance. 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.

SHOW PARAMETER RESULT_CACHE_MODE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode string MANUAL
SQL>
SELECT /*+ result_cache */ slow_function(id) FROM qrc_tab;

23. Read-Only Tables

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.

24. RMAN

All the RMAN enhancements in Oracle Database 11g Release

• Improved Integration with Data Guard
• Improved Handling of Long-Term Backups
• Archived Redo Log Failover
• Archived Log Deletion Policy Enhancements
• Network-Enabled Database Duplication Without Backups
• Recovery Catalog Enhancements    
  • Virtual Private Catalog
  • IMPORT CATALOG
• Multisection Backups
• Undo Optimization
• Improved Block Media Recovery Performance
• Faster Backup Compression
• Block Change Tracking Support for Standby Databases
• Improved Scripting with RMAN Substitution Variables
• Integration with VSS-Enabled Applications
• Lost Write Detection
• Backup of Read-Only Transportable Tablespaces
• Improved Media Recovery Performance for Databases on SMP Systems

Note: Advice on data recovery, parallel backup of the same file, virtual catalogs for security, duplicate database from backup, undrop a tablespace, and secure backup to the cloud are just a few of the new gems available from RMAN in Oracle Database 11g..

25. Data Recovery Advisor

The Data Recovery Advisor automatically diagnoses corruption or loss of persistent data on disk, determines the appropriate repair options, and executes repairs at the user's request. This reduces the complexity of recovery process, thereby reducing the Mean Time To Recover (MTTR).

26. Scheduler Enhancements

Oracle 10g Release 1 introduced the new Oracle scheduler and Oracle 10g Release 2 enhanced it. Now Oracle 11g adds more functionality to the Oracle scheduler.

• Remote External Jobs    
  • Database Configuration
  • Oracle Scheduler Agent Installation
  • Credentials
  • Creating Remote External Jobs
  • Returning stdout and stderr
  • Disabling Remote External Job Functionality
• Detached Jobs
• Lightweight Jobs
• Scheduler Support for Data Guard
• Oracle Enterprise Manager

27. SecureFiles

The SecureFiles functionality is a complete redesign of the implementation of large object (LOB) storage in Oracle 11g. The original LOB storage, now known as BASICFILE, is still the default storage method, but the SECUREFILE keyword enables the new storage method, which allows encryption for security and space savings using compression and deduplication.

• Initialization Parameters
• Creating SecureFile LOBs    
  • The Basics
  • LOB Deduplication
  • LOB Compression
  • LOB Encryption
  • LOB Cache and Logging
• PL/SQL APIs
• Migrating to SecureFiles

28. SQL Access Advisor

The SQL Access Advisor was introduced in Oracle 10g to make suggestions about additional indexes and materialized views which might improve system performance. Oracle 11g has made two significant changes to the SQL Access Advisor:

1. The advisor now includes advice on partitioning schemes that may improve performance.
2. The original workload manipulation has been deprecated and replaced by SQL tuning sets.

29. SQL Performance Analyzer

The concept of SQL tuning sets, along with the DBMS_SQLTUNE package to manipulate them, was introduced in Oracle 10g as part of the Automatic SQL Tuning functionality. Oracle 11g makes further use of SQL tuning sets with the SQL Performance Analyzer, which compares the performance of the statements in a tuning set before and after a database change. The database change can be as major or minor as you like, such as:

• Database, operating system, or hardware upgrades.
• Database, operating system, or hardware configuration changes.
• Database initialization parameter changes.
• Schema changes, such as adding indexes or materialized views.
• Refreshing optimizer statistics.
• Creating or changing SQL profiles.

Unlike Database Replay, the SQL Performance Analyzer does not try and replicate the workload on the system. It just plugs through each statement gathering performance statistics.

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

Note. Basic table compression is a free feature of the Enterprise Edition database, but OLTP compression requires the Advanced Compression option.

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

31. Temporary Tablespace Enhancements

Oracle 11g has a new view called DBA_TEMP_FREE_SPACE that displays information about temporary tablespace usage.

SQL> SELECT * FROM dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP 56623104 56623104 55574528
SQL>

32. Tablespace Encryption

The Transparent Data Encryption (TDE) feature was introduced in Oracle 10g Database Release 2 to simplify the encryption of data within datafiles, preventing access to it from the operating system. In 11g, Tablespace encryption extends this technology, allowing encryption of the entire contents of a tablespace, rather than having to configure encryption on a column-by-column basis.

33. Database Replay

Explore Database Replay, the new tool that captures SQL statements and lets you replay them at will.

Use Cases

• Database Parameter Change
• OS Upgrades
• Applying Patches
• Debugging
• Object Changes
• Database Upgrades
• Platform Changes
• Conversion to Oracle Real Application Clusters (RAC)

Note: In Database Replay, the entire captured workload is replayed against the database. But what if you don't want to do that? For example, you may be interested in understanding how SQL execution plans and therefore SQL performance might be affected by a change, because they can severely affect application performance and availability. In addition, Database Replay replays only what has been captured; not anything else. You may be interested to know the impact of parameter changes on some SQLs that has not been executed in production yet.

34. Edition-Based Redefinition

It's revolutionary: Patch or update your application's data objects while the application remains in uninterrupted use (in Release 2 only).

Large, mission critical applications built on Oracle Database 11g Release 1 and earlier versions are often unavailable for tens of hours while the application’s database objects are patched or upgraded. Oracle Database 11g Release 2 introduces edition-based redefinition, a revolutionary new capability that allows online application upgrade with uninterrupted availability of the application. When the installation of the upgrade is complete, the pre-upgrade application and the post-upgrade application can be used at the same time. Therefore an existing session can continue to use the pre-upgrade application until its user decides to end it; and all new sessions can use the post-upgrade application. As soon as no sessions are any longer using the pre-upgrade application, it can be retired. In other words, the application as a whole enjoys hot rollover from the pre-upgrade version to the post-upgrade version.

35. PL/SQL Performance Enhacement

Oracle Database 11g introduces a number of nifty new features to improve the performance of PL/SQL code, but the most dramatic ones are native compilation and intra-unit inlining.

Native compilation is not a new feature in itself, but the lack of any preconditions for using it—such as a C compiler install—is indeed new. (Oracle calls this improved feature "Real Native Compilation".) In addition, a new datatype, simple_integer, makes code perform better under native compilation. Intra-unit inlining is an optimization technique applied to the PL/SQL code during compilation time to produce efficient code.

36. Efficient PL/SQL Coding

A trigger that fires several times at different triggering events, ability to force triggers of the same type to follow a sequence, and the new CONTINUE statement are some of the new gems that make PL/SQL programming easier.

Since its inception, PL/SQL has been the language of choice for programming in Oracle Database. Over a period of time, we have seen the language evolve into a comprehensive development platform by virtue of more and more functionality that requires less coding. Oracle Database 11g makes PL/SQL coding yet more efficient for programmers

37. Improved Out-of-Box Auditing

In Oracle Database 11g, two simple changes have been made to provide an even more powerful auditing solution. First, the database parameter audit_trail is now set to DB by default, not NONE, as it was in previous versions. This allows you to turn on auditing on any object, statement, or privilege without recycling the database.

The second change is more statements have been placed under audit by default. Here is the list:

• ALTER SYSTEM
• SYSTEM AUDIT
• CREATE SESSION
• CREATE USER
• ALTER USER
• DROP USER
• ROLE
• CREATE ANY TABLE
• ALTER ANY TABLE
• DROP ANY TABLE
• CREATE PUBLIC DATABASE LINK
• GRANT ANY ROLE
• ALTER DATABASE
• CREATE ANY PROCEDURE
• ALTER ANY PROCEDURE
• DROP ANY PROCEDURE
• ALTER PROFILE
• DROP PROFILE
• GRANT ANY PRIVILEGE
• CREATE ANY LIBRARY
• EXEMPT ACCESS POLICY
• GRANT ANY OBJECT PRIVILEGE
• CREATE ANY JOB
• CREATE EXTERNAL JOB

38. Encryption of Data Pump Dumpfiles

Oracle Database 10g introduced one of the most powerful features for data movement: Data Pump, the successor to the original export/import tool. Apart from being just plain faster, Data Pump provided many benefits such as parallelizing the process and remapping the tablespace. In Oracle Database 11g, it also helps secure dumpfiles via a new parameter called ENCRYPTION.

39. Data Masking

In many organizations staging or QA databases are refreshed from time to time from the production database, allowing developers to run pre-production code against the database prior to going production. However, this procedure is a potential security problem -- when data is refreshed from the production to the QA database to keep them in synch, sensitive data elements may be exposed. To address that risk, many organizations alter sensitive data to some meaningless value in the non-production database, a practice known as data masking. For instance, you may want to update the Social Security Number with a random 9 character number in staging to mask the real SSNs.

But that's the easy part; making the change could be tough—you have to write the SQL script, make sure the process is restartable, take care not to overwhelm the system, and so on. Well, why not let Oracle take care of that while importing the data? In Oracle Database 11g, Data Pump has a new parameter, remap_data, for changing data during the import process.

40. Online Patching

In today's business environment, there is increasing pressure to reduce planned as well as unplanned downtime (still the major variety) for IT systems. Downtime for applying patches and maintaining application objects can now be decreased further. In Oracle Database 10g, although fine-grained dependency tracking makes most changes pretty much online, patches still need to be applied while the database is down.

Oracle Database 11g offers a new feature in this area that is truly revolutionary. Some (but not all) patches in Oracle Database 11g, specifically diagnostic patches, can now be applied online without taking any downtime. When you apply them, they modify the code loaded into the host's memory online without requiring it to shut down

41. SQL Access Advisor

Oracle Database 10g offers an avalanche of helpers—or "advisors"—which help you decide the best course of action. One example is SQL Tuning Advisor, which provides recommendations on query tuning, lengthening the overall optimization process a bit in the process.

But consider this tuning scenario: Say an index will definitely help a query but the query is executed only once. So even though the query will benefit from it, the cost of creating the index will outweigh that benefit. To analyze the scenario in that manner, you would need to know how often the query is accessed and why.

Another advisor—SQL Access Advisor—performs this type of analysis. In addition to analyzing indexes, materialized views, and so on as it does in Oracle Database 10g, in Oracle Database 11g SQL Access Advisor also analyzes tables and queries to identify possible partitioning strategies—a great help when designing optimal schema. In Oracle Database 11g SQL Access Advisor can now provide recommendations with respect to the entire workload, including considering the cost of creation and maintaining access structures

42. Data Guard

Active Data Guard will make your investment in a standby environment worthwhile via real time queries while applying the archived logs, converting the physical standby database to snapshot standby, and a host of new improvements in infrastructure.

Easier Creation of Standby Database

Let's start at the beginning: the creation of a physical standby database. In Oracle Database 11g, that process has become a whole lot easier, with just one RMAN command that does it all. Previously, you could use the Grid Control wizard interface to build a Data Guard setup between two machines. As of this writing, Oracle Enterprise Manager Grid Control 11g is not yet available and the Database Control does not have a wizard for Data Guard. But regardless of your experience in using SQL commands, you will find setting up a Data Guard environment in Oracle Database 11g a breeze.

Active Data Guard

One of the traditional objections to building a Data Guard environment using physical standby database is the passiveness of the standby database. In Oracle Database 10g and below you could open the physical standby database for read-only activities (say, to offload some reporting), but only after stopping the recovery process. With these releases, If Data Guard is a part of your DR solution, you really can't afford to pause the recovery process for a long duration for fear of falling behind, so the physical standby database is essentially useless for any read-only activity.

With Oracle Database 11g, that situation changes: You can open the physical standby database in read-only mode and restart the recovery process. This means you can continue to be in sync with primary but can use the standby for reporting. (As in previous versions, you can take the backup from the standby as well.) Let's see how it is done.

43. Upgrade, RAC, RAC One Node, and Clusterware

For Release 2 only: Enable a single name for the cluster, enable High Availability for a single instance database, place OCR and voting disks on ASM, and explore some miscellaneous HA-related improvements.

44. New Processes

Each new version of Oracle Database brings forth a new set of abbreviations for new processes. Here is the list for Oracle Database 11g:

45. Miscellaneous 11g Release 2 Updates

• Style Security Settings Are by Default (Release 2 Only)
• Audit Trail Purging (Release 2 Only)
• Relocating Audit Trails into a Different Location (Release 2 Only)
• Truly Global Application Contexts (Release 2 Only)
• Listener Password Deprecated (Release 2 Only)
• Fine-grained Security on Directory Objects (Release 2 Only)
• New Audit Options (Release 2 Only)
• Current Session Auditing (Release 2 Only)
• Unified Master Encryption Key (Release 2 Only)
• Audit by Session Change (Release 2 Only)