Recompiling Busy Package Bodies in a RAC Environment

In short, probably not a good idea.

A package body needed to be recompiled on production. The large package body took about ten seconds to compile in QA. Obviously, nothing else went invalid or needed to be recompiled.

On production, the compile seemed to hang, as it was still going on after 30s. OEM showed a spike in sessions working or waiting, all of which were concurrency waits. Investigating further, these were all “library cache pin” waits. We killed the rebuild session from its terminal, and about two-thirds of the sessions waiting disappeared fairly quickly.

The remaining hundred or so sessions were all on the same node. Each was trying to execute one of two stored procedures from the package we had tried to recompile. I looked in GV$SQL to see if there was anything significantly different about the statements in each instances memory, but this was slightly out of my depth and nothing was immediately obvious to me.

The waiting sessions were made to go away by being manually killed. The available literature on how to “fix” library cache pins generally refers to x$ views to which I didn’t have access. Nor was DBA_BLOCKERS available.

What I would like to know is, what was the correct way of dealing with this? Was a lock or latch held on the package body by one of the sessions? If so, was it for a good reason, or was there a process failure of some sort? Where could I have looked for this information?

Transaction Tables

The section on undo tablespaces includes, “When the first DML operation is run within a transaction, the transaction is bound (assigned) to an undo segment (and therefore to a transaction table) in the current undo tablespace.”

What’s a transaction table? I don’t recall them being mentioned before this point, and there’s no forward link from here either. It’s not in the glossary, and the index only points to a description of how they are recovered, not what they are.

PCTUSED and PCTFREE Parameters

The section on PCTUSED says, “The PCTUSED parameter sets the minimum percentage of a block that can be used for row data plus overhead before new rows are added to the block.”

I think this is wrong, or at least badly expressed. Consider part of the description of figure 2-5, “[A]fter the amount of used space falls below 40%, new rows can again be inserted into this block.” This makes me think that PCTUSED is a maximum, rather than a minimum. The idea is quite hard to express clearly in writing; I think the diagrams are much more useful here.

I always had trouble keeping PCTUSED and PCTFREE apart in my mind, until I read the best sentence in this section, “[U]pdates can reduce the available space of a block to less than PCTFREE, the space reserved for updates but not accesible to inserts.“

Table Directory in Row Header

Apparently part of the header of each data block is the table directory, which contains information about the table that this block forms part of.

Why would Oracle want to write something about tables into every data block? Why not allow this information to remain in the data dictionary or the segment header? Is it here for efficiency purposes?

RAC Redo Recovery

According to 1-23 of the Concepts Manual, “After an instance failure, Oracle automatically performs instance recovery. If one instance in a RAC environment fails, then another instance recovers the redo for the failed instance.”

For this to work, I guess that each instance must keep its redo logs on the shared storage (no use of locally-attached storage, which I believe is possible in 11g). Each instance must also know which log files belong to which other instance.

As far as I can see, there also must be a way of distinguishing how much of the logged changes have already been stored “properly”, and how many remain to be written. This would be true in a RAC or a single-node environment. The first possibility that occurs is that the process of writing changes back to disk is also logged.

Query Isolation

Is query isolation (or transaction isolation) achieved by storing an SCN with the running query (or transaction)? I guess this would be the easiest way to identify which parts of the undo to note and which to ignore when creating a consistent view of the data.

Redo Log Buffer

In this buffer are entries for the redo log that haven’t yet been written to permanent storage. I assume that anything in here stands to be lost if there’s a hardware failure.

Alert Log

What goes into the alert log? What gets left out? Page 1-9 says, “The alert log of a database is a chronological log of messages and errors.” Does that mean that messages from all Oracle processes get written to the log?

Updatability and Analytic Functions

Not really a question, more an annoyance: Oracle won’t correctly determine which columns – if any- in a view are updatable once analytics are involved.

A table will not be regarded as key-preserved by Oracle in a view if analytic functions are invoked on it in a sub-select:

SQL> drop table analytic_test_a;

Table dropped

SQL> drop table analytic_test_b;

Table dropped

SQL> drop view analytic_test_view_1;

View dropped

SQL> drop view analytic_test_view_2;

View dropped

SQL> drop view analytic_test_view_1s;

View dropped

SQL> drop view analytic_test_view_2s;

View dropped

SQL> create table analytic_test_a as
2 select o.object_id, o.object_name, o.created
3 from user_objects o
4 where o.object_type in (‘TABLE’,'VIEW’);

Table created

SQL> alter table ANALYTIC_TEST_A
2 add constraint ANALYTIC_TEST_A_PK primary key (OBJECT_NAME);

Table altered

SQL> create table analytic_test_b as
2 select t.table_name, t.tablespace_name, t.pct_free
3 from all_tables t;

Table created

SQL> create view analytic_test_view_1 as
2 select *
3 from analytic_test_a a,
4 analytic_test_b b
5 where b.table_name = a.object_name;

View created

SQL> create view analytic_test_view_2 as
2 select a.*, b.*, min(OBJECT_ID) over (partition by TABLESPACE_NAME) min_id
3 from analytic_test_a a,
4 analytic_test_b b
5 where b.table_name = a.object_name;

View created

SQL> select *
2 from user_updatable_columns c
3 where lower(c.table_name) = ‘analytic_test_view_1′;

OWNER TABLE_NAME COLUMN_NAME UPDATABLE INSERTABLE DELETABLE
—————————— —————————— —————————— ——— ———- ———
BARTOJ_DEV ANALYTIC_TEST_VIEW_1 OBJECT_ID NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_1 OBJECT_NAME NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_1 CREATED NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_1 TABLE_NAME YES YES YES
BARTOJ_DEV ANALYTIC_TEST_VIEW_1 TABLESPACE_NAME YES YES YES
BARTOJ_DEV ANALYTIC_TEST_VIEW_1 PCT_FREE YES YES YES

6 rows selected

SQL> select *
2 from user_updatable_columns c
3 where lower(c.table_name) = ‘analytic_test_view_2′;

OWNER TABLE_NAME COLUMN_NAME UPDATABLE INSERTABLE DELETABLE
—————————— —————————— —————————— ——— ———- ———
BARTOJ_DEV ANALYTIC_TEST_VIEW_2 OBJECT_ID NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_2 OBJECT_NAME NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_2 CREATED NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_2 TABLE_NAME YES YES YES
BARTOJ_DEV ANALYTIC_TEST_VIEW_2 TABLESPACE_NAME YES YES YES
BARTOJ_DEV ANALYTIC_TEST_VIEW_2 PCT_FREE YES YES YES
BARTOJ_DEV ANALYTIC_TEST_VIEW_2 MIN_ID NO NO NO

7 rows selected

SQL> create view analytic_test_view_1s as
2 select *
3 from analytic_test_view_1;

View created

SQL> select *
2 from user_updatable_columns c
3 where lower(c.table_name) = ‘analytic_test_view_1s’;

OWNER TABLE_NAME COLUMN_NAME UPDATABLE INSERTABLE DELETABLE
—————————— —————————— —————————— ——— ———- ———
BARTOJ_DEV ANALYTIC_TEST_VIEW_1S OBJECT_ID NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_1S OBJECT_NAME NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_1S CREATED NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_1S TABLE_NAME YES YES YES
BARTOJ_DEV ANALYTIC_TEST_VIEW_1S TABLESPACE_NAME YES YES YES
BARTOJ_DEV ANALYTIC_TEST_VIEW_1S PCT_FREE YES YES YES

6 rows selected

SQL> create view analytic_test_view_2s as
2 select *
3 from analytic_test_view_2;

View created

SQL> select *
2 from user_updatable_columns c
3 where lower(c.table_name) = ‘analytic_test_view_2s’;

OWNER TABLE_NAME COLUMN_NAME UPDATABLE INSERTABLE DELETABLE
—————————— —————————— —————————— ——— ———- ———
BARTOJ_DEV ANALYTIC_TEST_VIEW_2S OBJECT_ID NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_2S OBJECT_NAME NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_2S CREATED NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_2S TABLE_NAME NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_2S TABLESPACE_NAME NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_2S PCT_FREE NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_2S MIN_ID NO NO NO

7 rows selected

SQL>

Current Reading: Concepts Manual

I thought I’d start by following Tom Kyte’s advice in Expert Oracle Database Architecture, and read the Concepts manual. It’s the 10.2 version because that’s the dominant version at work. I got a hard copy because I can’t read that much off the screen; the price was an eye-watering £30 for two cheaply bound A4 volumes. Thanks a bunch, Oracle!