Guenadi N Jilevski's Oracle BLOG


New features in 11g for DBA

New features in 11g for DBA

This post will discuss about new features of oracle database 11g administration. Every Oracle database major release comes with new features and step by step some new features are discussed here briefly that comes with Oracle database 11g.

1)Improved Automatic Memory Management:
In 10g in case of automatic memory management we knew that if SGA_TARGET is set then all dynamic SGA components will be automatically managed and if PGA_AGGREGATE_TARGET is set then pga components will be automatically managed. But starting with oracle 11g both pga and sga memory can be managed automatically by setting only MEMORY_TARGET parameter. However you can still assign minimum sizes individually for the SGA and instance PGA.

2)New fault diagnosability infrastructure:
The fault diagnosability infrastructure are for easy diagnosis for any critical problems. After a problem has been occurred it gather necessary information against the problem and then an Incident Packaging Service packages all diagnostic data for a problem into a zip file so that you can transmit that to Oracle Support and thus reduce time to gather information about the problems.

3)Invisible Indexes:
Now in 11g you can make an index as invisible state instead of dropping it or mark them unusable. It is very effective in order to test performance of a query to see whether removing index will improve the performance. By default an invisible index is ignored by an optimizer but is maintained during DML statements. You can change the initialization parameter to cause the optimizer to use invisible indexes.

4)Virtual Columns:
In tables you can now include virtual columns. In fact the value of a virtual column in a row is derived by evaluating an expression. The expression can include columns from the same table, constants, SQL functions, or even user-defined PL/SQL functions. You can create index and do partition on the virtual columns.

5)Enhanced Security:
In 11g now by default your password is case sensitive. So you can use mixed case password. Though by editing initialization parameter you can make password case-insensitive.

6)Database resident connection pooling:
Database resident connection pooling provides a connection pool in the database server for typical Web application usage scenarios where the application acquires a database connection, works on it for a relatively short duration, and then releases it. Database resident connection pooling significantly reduces database resources needed to support a large number of client connections and thereby boosting the scalability of both middle-tier and database tiers.

7)Tablespace level Encryption:
You can now encrypt a tablespace that contains sensitive data. Tablespace encryption is completely transparent to your application and when you encrypt a tablespace, all tablespace blocks are encrypted also.

8)Invalidation of dependent schema objects are greatly reduced:
Before 11g if we make any changes in the main object then dependent object become invalid. In 11g invalidation of dependent schema objects in response to changes in the objects they depend upon is greatly reduced and thus increasing application availability during maintenance, upgrades, and online table redefinition.

9)Enhanced automated maintenance task infrastructure:
Though in Oracle 10g you had automatic optimizer statistics gathering, Automatic Segment Advisor, and Automatic SQL Tuning Advisor. In 11g you can now exercise finer control over automated maintenance task scheduling and those task ran automatically.

10)Transparent data encryption enhanced:
Now in 11g using the transparent data encryption feature you can now encrypt SecureFile LOBs.

11)Table compression now supported in OLTP environments:
On compressed tables you can now run DML operations and adding or dropping columns of a compressed tables.

12)Query result cache in sga:
Now query results can be cached in sga memory in the result cache. The database can then use cached results to answer future executions of that query. It is also true for query fragments. As because retrieving results from the result cache is faster than rerunning a query, frequently run queries experience a significant performance improvement when their results are cached.

13)Default automatic undo management mode:
In 10g if you create database manually then the parameter UNDO_MANAGEMENT became MANUAL as by default UNDO_MANAGEMENT is manual in oracle 10g. However if you created database by dbca it makes the parameter AUTO. In 11g by default UNDO_MANAGEMENT is set to AUTO. A null value for the UNDO_MANAGEMENT initialization parameter now defaults to automatic undo management.

14)Enhancements to Oracle Database Resource Manager:

15)Enhancements to Oracle Scheduler:

16)Enhanced online index creation and rebuild:
Before oracle 11g we can do online index creation and rebuild operation but it required a DML-blocking lock at the beginning and at the end of the rebuild for a short period of time. The DML blocking lock can lock other DMLs and thus performance problem can arise while doing online index creation and rebuild operation. In 11g this lock is no longer required, making these online index operations fully transparent.

17)Tables with materialized view logs can now be redefine online:
Prior to 11g online redefinition of a table that have materialized view logs were not possible. But in 11g tables with materialized view logs can now be redefined online. Materialized view logs are now considered one of the dependent objects. So they can be copied to interim table using DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS package procedure.

18)Facility to make a table read only mode:
Now to avoid any update or insert or delete on a table you don’t need to make whole tablespace in read only mode. In 11g with ALTER TABLE statement you can place a single table into read only mode.

19)DDL commands can wait for locks:
Prior to 11g if you have any DML operation running against the table then you can’t do DDL commands on the table and instantly ORA-54 message would return as demonstrated in ORA-54 Resource Busy. But in 11g you can now set a single initialization parameter, DDL_LOCK_TIMEOUT, to specify how long a DDL command waits for the exclusive locks that it requires on internal structures before it fails. So we might not need wait for prompting further command to test whether we can run DDL commands. Oracle will check and if no DML operations DDL commands will be performed if it get resource free within DDL_LOCK_TIMEOUT time.

20)Enhancements to initialization parameter management:
There are several enhancement in the initialization parameters.

Upon startup, values of initialization parameters are written to the alert log in
such a way as to make it easy to copy and paste them to create a new PFILE.

The name and path of the PFILE or SPFILE used to start the instance is written
to the alert log so we need not to search which one is used to startup database.

February 2, 2008 Posted by | oracle | Leave a comment