Two years of successful use of Edition-Based Redefiniton in Oracle databases
The stored code in the database? Do not tell, in the yard 2017!
This year, the QIWI brand is 10 years old. During this time, more than 130 thousand lines of stored PL / SQL code have accumulated in our main transactional database. On Habré regularly there are articles about how various development teams categorically do not use the stored code in the database, trying to remove unnecessary load from the database and thus reduce the cost of the system. We can discuss this topic for a long time, and this point of view is refuted, for example, in this video .
Which is indisputable - the stored PL / SQL code traditionally had one significant minus: the release of the PL / SQL program required stopping the service, since the compilation of this code should have received an exclusive lock in the database dictionary (the so-called library cache pin) Untimely triggered random recompilation could suspend the entire system. I had to regularly select technical windows for the release of PL / SQL code. Certified screenshots of complaints of our indignant clients who have fallen through such windows are carefully stored in our archives. However, less than 20 years have passed since the creation of PL / SQL, as Oracle, if this is not completely eliminated, it is significantly mitigated.
Welcome to Oracle Edition-Based Redefinition
We will not give detailed code examples 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 inside the database itself. Now applications are able to work with different versions of the same procedures, packages, and views. However, in the database, in addition to the code, there are also data structures in the form of tables, and Oracle had to come up with a way of interversion conversion of both the tables themselves and the data in them.
Immediately make a reservation that our developers use EBR only for views and PL / SQL code, and do not use for tables. The subject area is well studied and the data structures are quite stable. Over the course of the year, the columns in the hot tables were changed or added by force about five times, while the code changes were tens of times more.
application
Our Java application can switch itself to using the new version of PL / SQL code. The current edition can be extracted from the database with such a simple request:
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.
Successful release of a new version of PL / SQL code executes a command of the form
alter database default edition = ED_1180_23185307 and the application, having learned that the edition has changed, at the right moment executes a command of the form
alter session set edition = ED_1180_23185307 It is theoretically possible to roll back the PL / SQL code to the previous version - to do this, run the alter database command with the previous edition installed, and the application should switch to it.
Bugs
The Oracle DBMS inside is extremely complex, so many man-years have been invested in its optimization and development that any new features in its core do not pass painlessly for the rest of the functionality. We are, of course, talking about bugs and patches that fix them. EBR was not an exception at all, but, on the contrary, a significant troublemaker. Let's just say: it is impossible to do without technical support.
Unfortunately, Oracle does not maintain a separate list of patches that fix 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 installing on the OEBS base, and install on your base those ones that are potentially most likely for your application. You can find it on the Oracle Support site in Section 3 of Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes (Doc ID 1594274.1)
Underwater rocks
When working with Oracle Edition-Based Redefinition, we found four drawbacks:
- The limit on the number of editions is 2000. At a rate of 2 releases per week, we will exhaust them in 20 years. We hope that by then Oracle will be able to remove this limitation.
- Flat rather than tree structure editions, 1 parent <–> 1 child. This does not bother us yet.
- 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).
- Specificity in the distribution of rights to versioned code.
I want to dwell on the last point in more detail, since this effect is described extremely poorly.
The fact is that issuing rights to a versioned object, the last time it was changed in any previous edition, copies this object to the current edition, with all the symptoms of recompilation that are already familiar to us and, if not lucky, freezes on the dictionary lock library cache pin . Apparently, this is due to the internal implementation of editioned schemas in the database.
Therefore, the procedure for distributing rights had to be slightly changed: first we find the edition in which the desired object was last modified, we install this edition in our session using the above alter session command , and only after that we issue the necessary rights.
As they say, not bug 26654363, but expected behavior. Well, the workaround is not too labor intensive and in the vast majority of cases you can get along with it.