Two years of successful use of Edition-Based Redefiniton in Oracle databases

Stored code in the database? Do not tell, it's 2017 in the yard!

This year, the QIWI brand is 10 years old. During this time, our main transaction database has accumulated more than 130 thousand lines of stored PL / SQL code. On Habré, there are regular articles about how different development teams categorically do not use the stored code in the database, trying to remove the excessive load from the database and thus reduce the cost of the system. On this topic, you can discuss for a long time, and this point of view is refuted, for example, in this video .

What is indisputable - the stored PL / SQL code traditionally had one significant drawback: the release of the PL / SQL program required stopping the service, since the compilation process of this code should have received an exclusive lock in the database dictionary (the so-called library cache pin ). Not at the right time running random recompilation could hang the whole system. We had to regularly allocate technical windows for the release of PL / SQL code. Certified screenshots of complaints from our outraged customers who fall into such windows are carefully stored in our archives. However, less than 20 years from the creation of PL / SQL, as Oracle, if it didn’t completely eliminate, then significantly mitigated.

Welcome to Oracle Edition-Based Redefinition

We will not give detailed code samples using Edition-Based Redefinition, but describe several key points of the project for its implementation. With some stretch, this mechanism, which is usually reduced to EBR, can be considered a version control system of database objects within the database itself. Now applications can work with different versions of the same procedures, packages and views. However, in the database, besides the code, there are also data structures in the form of tables, and Oracle had to invent a method of interversion transformation of both the tables themselves and the data in them.

Immediately make a reservation that our developers use EBR only for views (view) and PL / SQL code, and do not use for tables. The subject area is well studied and data structures are quite stable. Over the course of the year, the columns in the hot tables changed or added five times at most, while the code changes were dozens of times larger.
')

application

Our Java application can switch to the use of a new version of PL / SQL code. The current edition can be extracted from the database with such a simple query:

select property_value from database_properties where property_name = 'DEFAULT_EDITION' 

The application stores this value and regularly polls the database to see if it has changed.

A successful release of a new version of PL / SQL code executes a command like

 alter database default edition = ED_1180_23185307 

and the application, having learned that the edition has changed, at the appropriate moment executes a command of the form

 alter session set edition = ED_1180_23185307 

and thereby switches to using the new version of the stored code.

Theoretically, a rollback of PL / SQL code to the previous version is also possible - for this, you need to run the alter database command with the previous edition installed, and the application should switch to it.

Bugs

Oracle's DBMS inside is extremely complex, so many person-years are invested in its optimization and development that any new features in its core are not painless for the rest of the functionality. Of course, we are talking about bugs and patches that eliminate them. EBR was not the exception, but, on the contrary, a significant troublemaker. Let's just say: it's impossible to do without technical support.

Unfortunately, Oracle does not keep a separate list of patches that eliminate EBR-related bugs. However, Oracle is actively using EBR in one of its popular ERP-systems - Oracle E-Business Suite (OEBS). Therefore, you can take the set of patches that Oracle recommends for installation on the OEBS base, and install on your base those that are potentially most likely for your application. You can find it on the Oracle Support site in Section 3 of the Oracle E-Business Suite Release 12.2: Doc ID 1594274.1

Underwater rocks

When working with Oracle Edition-Based Redefinition, we found four drawbacks:

1. The limit on the number of editions is 2,000. With a speed of 2 releases per week, we will exhaust them in 20 years. We hope that by that time Oracle will be able to remove this restriction.
2. Flat, not tree structure editions, 1 parent <-> 1 child. For now, this does not bother us.
3. Non-editioned objects cannot refer to versioned ones (for example, in version 11g, objects such as materialized view are non-editioned and cannot refer to editioned view).
4. Specificity in the distribution of rights to the versioned code.

I would like to dwell on the last point in more detail, since this effect is described extremely poorly.

The fact is that the issuance of rights to the versioned object that last changed in any previous edition copies this object to the current edition, with all the recompilation symptoms we already know and, if unlucky, hangs on the dictionary block library cache pin . Apparently, this is due to the internal implementation of editioned circuits in the database.

Therefore, the rights distribution procedure had to be slightly modified: first, we find the edition in which the desired object was last modified, set this edition in our session using the above described alter session command, and only after that we issue the necessary rights.

As they say, not a bug 26654363, but the expected behavior. Well, the workaround is not too laborious and in most cases you can get along with it.

The result of the project: minus 16 hours of planned downtime per year

99.8% -> 99.98%

PS We are looking for DBA and database developers!