Hot patching the Schema

We have seen that patching PLSQL is possible in Oracle 11gR2 without impacting a running application and with some careful design, it is cumbersome but possible in older versions of Oracle. Not all application changes are as simple as just a code change - sometimes the underlying database schema needs changed too. So what are the options there?

Well adding new tables is easy, just add them as the old application doesn't use them anyway.

Dropping old tables is easy too, just leave them alone until the application no longer needs them!

Changing existing tables - that is where things get interesting.

Before Oracle 11G - Forget about it!

Internally, Oracle builds up a list of dependencies between objects. Consider the scenario below, where procedure P2 calls procedure P1 which in turn accesses table T1.

Lets create the objects so we can make some changes and see what happens.

SQL10G> create table t1 (c1 varchar2(10));

SQL10G> create or replace procedure P1
as 
begin
  for row in (select c1
              from   t1) loop
    null;
  end loop;
end P1;
/

SQL10G> create or replace procedure P2
as
begin
  P1;
end P2
/

Now, check that all the objects are valid:

SQL10G> select object_name, status
        from user_objects where object_name in ('P1', 'P2');

OBJECT_NAME  STATUS 
------------ -------
P1           VALID  
P2           VALID  

2 rows selected.

So, all the procedures are valid. Now add a column to the table, and see what happens to the procedures:

SQL10G> alter table t1 add c2 varchar2(10);

SQL10G> select object_name, status
        from user_objects where object_name in ('P1', 'P2');

OBJECT_NAME  STATUS 
------------ -------
P1           INVALID
P2           INVALID

The change to the table has invalidated the procedures, meaning that the next time a session attempts to use them, they will need recompiled. While that will happen automatically existing sessions will get dreaded "ORA-04068: existing state of packages has been discarded" error.

In Oracle 10G and below, changing just about any object in a dependency chain will cause all the dependent objects to become invalid. Even if the tables are disguised behind views, and only views are referenced in the procedures, the change to the table will invalidate the view, which will ripple the invalidation on up the chain to the procedures:

SQL10G> create or replace view v1 
        as select c1 from t1;

SQL10G> create or replace procedure P1
        as
    begin
      for row in (select c1 from v1) loop
        null;
          end loop;
    end;
    /

SQL10G> alter procedure p1 compile;

SQL10G> select object_name, status
        from user_objects where object_name in ('P1', 'P2');

OBJECT_NAME  STATUS 
------------ -------
P1           VALID  
P2           VALID 

SQL10G> alter table t1 add c3 varchar2(10);

SQL10G> select object_name, status
        from user_objects where object_name in ('P1', 'P2');

OBJECT_NAME  STATUS 
------------ -------
P1           INVALID
P2           INVALID

There really is nothing you can do, except avoid using PLSQL!

What about Cursors?

Executing a piece of SQL (also known as a cursor) within Oracle is a fairly complicated process, which is well beyond the scope of object invalidation's, but there is one important thing to investigate. In the library cache, Oracle stores a memory structure related to each piece of SQL it has previously executed, or is currently executing, as it is highly likely the application will want to execute the same piece of SQL again later.

Furthermore, in a well designed non PLSQL application (which I will refer to as a Java application from now on) all SQL statements are prepared once, and then executed many times. This prepared 'handle' contains everything Java needs to know to execute the query, and points into the Oracle library cache.

As we know, Oracle contains lists of dependencies between objects, and cursors are no different. So if we change table T1 by adding a column for example, what will happen in the Java application when it attempts to execute the prepared statement that points at an invalidated cursor?

Testing this can be left as an exercise, but luckily enough the Java application will continue unaffected. When the query is next executed, Oracle notices the cursor was invalidated, and provided the change to the table would not make the query invalid (eg dropping or resizing a column referenced in the query), it simply re-validates the cursor and continues processing.

So that means even before Oracle 11G, if you are not using PLSQL in your application, simple schema changes can be performed on line. Well, almost, as adding a column or index to a table still incurs a lock on the table for a very short time.

11gR1 - Less Invalidations

Starting in Oracle 11gR1, Oracle introduced a new feature called 'Fine Grained Dependency Tracking'. The plan behind this feature was to reduce object invalidations whenever possible and hence increase your ability to make schema changes without affecting a live application. The good news is that it works, at least some of the time. Running the previous example on 11gR2:

SQL11G> create table t1 (c1 varchar2(10));

SQL11G> create or replace procedure P1
as 
begin
  for row in (select c1
              from   t1) loop
    null;
  end loop;
end P1;
/

SQL11G> create or replace procedure P2
as
begin
  P1;
end  P2
/

Now, check that all the objects are valid:

SQL11G> select object_name, status
        from user_objects where object_name in ('P1', 'P2');

OBJECT_NAME  STATUS 
------------ -------
P1           VALID  
P2           VALID  

2 rows selected.

So, all the procedures are valid. Now add a column to the table, and see what happens to the procedures:

SQL11G> alter table t1 add c2 varchar2(10);
SQL11G> select object_name, status
        from user_objects where object_name in ('P1', 'P2');

OBJECT_NAME  STATUS 
------------ -------
P1           VALID
P2           VALID

Even after altering the table, the procedures are still valid, so things have certainly changed. In Oracle 11G it is actually possible to add or drop a column from a table without invalidating everything that references that table, provided the referencing object doesn't actually use the affected column.

As usual, there is more to the puzzle. What if the procedure performs a 'select *' on the table? If you alter the procedure listed above and then run the test again, you will see that unfortunately the procedures both become invalid like in Oracle 10G. For me, this isn't a massive surprise, but it isn't too bad as we all know you should never ever have a 'select *' in production code, right?

Now we have a rule of thumb for coding procedures. Best practice already states that:

Avoid using 'select *' in queries as it can easily lead to unexpected bugs if table structures change.

It can also lead to unexpected code invalidations too, so that is another reason to avoid it.

Related to this, insert statements that fail to list the columns being inserted are just like 'select *', ie avoid doing:

SQL11G> insert into t1 values ('v1');

Instead list the columns:

SQL11G> insert into t1 (c1) values ('v1');

It will lead to less bugs, and less invalidations!

Another cause of unexpected PLSQL invalidations is using %ROWTYPE to define variables. In terms of invalidations it behaves just like 'select *', and hence should be avoided if invalidations are a prime concern.

Rules of Thumb

Now we can defined three rules that we should add to PLSQL coding best practices to facilitate on line schema changes in Oracle 11gR1:

• Avoid 'select *' in queries
• In inserts, always list out all the columns
• Avoid using %rowtype in variable definitions.

However, as we will see, with some careful design and the help of Oracle 11gR2, we can relax these rules and still make schema changes on line.

Oracle 11gR2 - Editioning Views

Before exploring Editioning Views, it is worth mentioning something about views. One way to protect the PLSQL application from changes in any referenced tables is to create a view for each table and only reference the view in PLSQL. Provided the view lists all the columns of the table it is created against (ie it is not created with 'select * from table'), even if the PLSQL uses 'select * from view' or '%rowtype', it will not go invalid due to adding or removing a column from a table.

You may then ask, what is the point of Editioning Views, whatever they are?

An Editioning View is used to create a logical projection of a table structure for a particular database Edition. In some respects it is a special view, but it has some key restrictions:

• It must be owned by and in the same schema as the table owner
• The FROM LIST can only contain a single table
• FOR UPDATE, UNION, GROUP BY, HAVING, ORDER BY etc are not allowed
• Only one Editioning View can exist for a given table in a given database Edition.
• The select list must only mention column names and optional aliases. No column can be mentioned more than once and no expression is allowed in the select list.

When you think about it, these restrictions make sense - the Editioning View is designed to behave like the base table, and these restrictions largely apply to creating tables too.

With all these restrictions, there must be a good reason to actually use Edition Views, and there are at least two.

Any statement that can be performed against a table, can be performed against an Edition View - this does not hold for normal views. Consider the following partitioned table:

SQL11G> create table partitioned_t (c1 date, c2 varchar2(10))
partition by range (c1) 
(
  partition p1 values less than to_date('2011-01-01', 'YYYY-MM-DD')),
  partition p2 values less than to_date('2011-02-01', 'YYYY-MM-DD'))
);

SQL11G> create or replace view partitioned_v
as
select * from partitioned_t;

SQL11G> create or replace editioning view partitioned_ev
as
select * from partitioned_t;

SQL11G> select * from partitioned_v partition(P2);  

ORA-14109: partition-extended object names may only be used with tables and editioning views

SQL11G> select * from partitioned_ev partition (P2);

no rows select.

Table style triggers can be created on Editioning Views. This is because Triggers are PLSQL, and they are application and table version specific. In general if Editioning Views are in use, then any triggers should be removed from the base tables and recreated on the Editioning View. It is important that if the triggers are created on the Editioning View, then they should be removed from the table otherwise they will fire twice.

Also important is that the application no longer has privileges to insert into the base table, as it could cause the Edition view to accidentally be skipped, leading to bugs.

One bonus feature of Editioning Views is that even if they are defined like:

SQL11G> create edition view 
as 
select * from T1;

(ie the dreaded select * once again), they are not invalidated by adding a column to the table later, which would happen to traditional views.

What you can never do

If your goal is online upgrades and hence no invalidations, you can never alter the size of an existing column, as doing so will invalidate even an Editioning Trigger.

SQL11G> create table t1 (c1 varchar2(10));

SQL11G> create editioning view t1_ev
as
select c1 from t1;

SQL11G> select object_name, status
from user_objects where object_name in ('T1_EV');    

OBJECT_NAME               STATUS 
------------------------- -------
T1_EV                     VALID  

1 row selected.

SQL11G> alter table t1 modify c1 varchar2(100);

SQL11G> select object_name, status
from user_objects where object_name in ('T1_EV');        

OBJECT_NAME               STATUS 
------------------------- -------
T1_EV                     INVALID

1 row selected.

It probably goes without saying, but dropping a table that is in use will cause a few problems too!

Less Locking in Oracle 11g

As of Oracle 11G, adding a column to a table truly became an online operation - an exclusive lock is no longer required even for a short time as it used to be. Now there really is no impact to on line transactions.

The 'create index online' operation also saw an improvement. Back in Oracle 10g, the create index online statement needed an exclusive lock on the table for a brief amount of time right at the start, which could impact online transactions. This is no longer required. As adding an index to 'patch' running applications is another common problem, this new non-locking functionality is a big plus.

Changing indexes

Index creation can be a time consuming process, and if an upgrade requires the creation of several new indexes, it is highly likely each will be created at different times. This has the potential to affect the currently running version of the application in either a positive or negative way.

Luckily Oracle 11g also introduced the ability to create an index as invisible. Invisible indexes are maintained as usual by Oracle, but the application cannot use them without explicitly altering its session. So adding new indexes seamlessly to an online application is possible and the key is to create them using the 'online' and 'invisible' options, and then alter them to visible when the new Edition is ready to be turned on.

Achieving Online Upgrades

So far there has been quite a lot of information about how to achieve online upgrades, so it is worth summarising it. Assuming you are running Oracle 11gR2 here are the rules of thumb:

• The application should have no privileges on the base tables
• All tables should have an Editioning View created against them that the application must access instead of the table
• All triggers should be moved from the base table to the Editioning Views.
• Despite the protection offered by Editioning Views, select * from table should be avoided, as should inserts that don't list all the columns.
• In general, schema changes can only involve adding columns to existing tables or adding completely new tables.
• You can never drop or alter existing columns which are referenced by an Editioning View that is in use by any live part of the application.
• Indexes required for the upgrade should be created as invisible and altered to visible when the upgrade is complete.
• When PLSQL units need to be changed, they should be added to a new Edition.

There is still one major piece of the puzzle missing. It was mentioned previously that you can never alter an existing column. Sometimes, you have no choice and luckily there is a way to change an existing column, using a little care, clever thinking and some Cross Edition Triggers.