Online Application Maintenance - Oracle 11g

Often DBAs are often forced to perform object reorganization, also calledonline application maintenance, while the database is in use. Oracle’s online table reorganization feature lets you modify a tables structure while users continue to use it online. Oracle lets you perform both in-place object reorganization where you modify database objects without making an intermediate copy of the objects and copy-based reorganization where you first make changes to intermediate objects.Online table and index reorganizations use the copy-based approach.

Oracle Database 11g offers better online redefinition cap abilities that go a long way to reducing the headaches we all had to endure during most redefinition projects.The following sections summarize the new online redefinition capabilities.

Support for New Objects

Oracle Database 11g provides support for redefining more types of objects online.

  • You can redefine tables with materialized views and view logs.
  • You can also redefine tables consisting of triggers with the follows or preceding clause. These two new trigger clauses establish an order ing dependency between multiple triggers on a table.

Enhanced Online Index Creation and Rebuild

It is usually much faster to rebuild an index online rather than to drop and re-create it.Even though Oracle has provided an online index creation and rebuilding feature for quite a while, the results of trying to rebuild an index online were un predict able in a real-life production environment.Although Oracle claimed that the online create/rebuild process applied exclusive locks on the underlying table only during the beginning and ending of the index creation/rebuild process for “brief” periods of time, it was common to experience a considerable slowdown in the execution of DML statements by the rest of the users in the database.

In Oracle Database 11g,Oracle finally provides a real online index creation and rebuild feature by making it unnecessary to apply an exclusive lock (X) on the underlying tables.Instead, the database will use shared exclusive locks (SX) on the objects, thus enabling DML to continue uninterrupted while the DDL is executing.The changing of the locking strategy applies to the following types of statements, among others.

  • create index online
  • rebuild index online
  • create materialized view log

None of the commands listed here requires exclusive locks in Oracle Database 11g.

Enhanced Default Column Value Functionality

When you add a new column to a table,that column initially has null values. You can add a column with a not null constraint only if the table has new rows or if you supply values for the new column by specifying the default clause.If you specify the default clause while adding the column, however,Oracle immediately issues an update for each row with the default value for the new column. During this row update, the database applies an exclusive lock on the table, thus limiting access to other users.

In Oracle Data base 11g,when you add a column with a default value, Oracle doesn’t immediately update the rows of the table. Let’s use a simple example to drive home the value of this important new feature.

First, let’s add a new column called BONUS to the employees table:

Before the Oracle Database 11g release,the database would let you add the column without a problem, but there was a price to pay. Since the table isn’t empty, Oracle applies an exclusive lock on the table to update all rows in the table with the default value for the new column you just added.If the table had a large number of rows, this meant that the database generated a huge amount of undo and redo logs,and in general, theperformance of the database was affected.In Oracle Database 11g, anydefault values you specify for a column you specify as not null are stored in the data dictionary instead of the table.This, of course,means the addition of a new column is almost instantaneous, thus eliminating a major hurdle for DBAs in adding new columns to tables.All the addition of the new column involves is updating the data dictionary once, with a single value denoting the default value of the new column.This is true regardless of the size of the table.In addition, you have the big advantage of not using any space for adding the default values for the new column.

Online Reorganization of Materialized Views

Until now, if a table contained materialized views, that table was ineligible for an online table reorganization, unless you were prepared to drop and re-create dependent materialized views and materialized view logs for those tables.

In Oracle Database 11g, you can perform an online reorganization of tables with materialized views and materialized view logs.You simply clone the materialized view logs on the interim table, with the procedureDBMS_REDEFINITION.COPY_TABLE_DEPENDENTS, just as you would other objects dependent on the table being reorganized, such as triggers and indexes.After redefining a table with a materialized view log, you must make sure you completely refresh the materialized views at the end of the table reorganization, since the ROWID logs are invalidated during the table reorganization.

Minimal Invalidation of Dependent Objects

In previous versions of the Oracle data base,during an online redefinition, the database automatically invalidated all dependent views and PL/SQLpackages.In Oracle Database 11g, all dependent objects aren’t automatically invalidated any longer.The database marks as invalid only those dependent views, synonyms, andother objects that are logically affected.If an object that’s referenced has identical column names and types following the online redefinition, its status continues to remain valid.Previously,for example,if you dropped a column from a table that you wereredefining online,all procedures and views that depended on the table were invalidated,even when it was not logically required.Under the new concept of a fine-grained dependency management, object dependencies are tracked only at the level of an element within each unit.Now, Oracle invalidates only the subset of proce dures and views that actually use the dropped column.Note that the new object-invalidating behavior doesn't affect triggers, because all triggers on a table that’s being redefined online are invalidated as before.

Oracle Database 11g uses more precise dependency metadata than in previous releases, called fine-grained dependencies.In earlier releases, object dependency metadata was recorded with the precision of the entire object; for example,view V depends on table T.If the view V depended on just the first three columns of the table T and you added a new column to table T, the view would be invalidated, even though the new column didn’t logically affect it.In Oracle Database 11g,the view V will not be invalidated when a change in an object doesn’t directly affect the view.For example,a column addition such as the one explained here won’t result in the invalidation of the view.

Oracle also uses similar fine-grained dependency management for PL/SQL objects such as procedures and functions.In current releases, if you add a new element or change an element in a PL/SQL package, you invalidate all the procedures in that package. In Oracle Database 11g, a new program element invalidates other proce dures or functions only if that object has a specific dependency on the changed or new element.

Fine-grained dependency management leads to an overall reduction in the invalidation of objects following changes to objects, and you don’t need to configure anything to avail of this new feature.You’ll have enhanced application availability especially when you’re upgrading applications.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 11g Topics