[Updated in October 2017]
Oracle was, is and will be probably the first in most of RDBMS innovations. Over the last 35 years Oracle went through successful database release schedule. To help planning the company IT projects I give the below road-map of the latest Oracle Database major releases 11.2 and beyond, showing planned release dates and the duration of their support lives. The release and timing of any platforms are subject to change at any time and at Oracle’s sole discretion. (see the latest status in MOS Doc ID 742060.1 or below as last updated on Oct-2017)
Patching end dates for the latest release/patch levels
|Release||Patching Ends||Notes and Exceptions*|
|18.104.22.168||31-Jul-2021||Extended Support fees waived through July 31, 2019|
|22.214.171.124||31-Aug-2016||Patching has ended for this release.|
Extended Support fees waived until Dec 31, 2018.
Oracle Major releases – Designated by the 2nd place in the version number (e.g. 11.2.0.x) and shown as the wider red bar in the Roadmap.
—> Starting from Oracle 12.2 Oracle uses a new patching concept based on RUs RURs patches.
—> Before Oracle release 12.2 following patching concept applies:
– Oracle Patch sets – Designated by the 4th place in the version number (e.g. 126.96.36.199), and shown by the narrower green bars below their associated major release. Patchsets are released on different platforms over time started usually from Linux.
– Patch Set Updates (PSU) and Critical Patch Updates (CPU) – (not listed separately on the Roadmap) they are released every quarter for supported patch sets and designated by the 5th place in the version number (e.g. 188.8.131.52.2).
Oracle historical release road map
2018: Oracle 18c expected to be released (based on OOW 2017 announcement)
– First fully autonomous database
2017: Oracle 12cR2 (12.2)
2014 – 184.108.40.206
– Oracle Database In-Memory Read more »
I remember when long time ago one database consultant confused my manager saying that our Oracle 9i database had poor performance just taking into account a slow response from dba_segments data dictionary view. That was a nasty trick to blame a DBA and the Oracle database for poor performance at that time. In fact there were a few Oracle bugs related to those performance issues after switching from dictionary to locally managed tablesspaces at that time. Recently I’ve noticed similar performance degradation on Oracle 11gR2 (220.127.116.11 and 18.104.22.168) by querying DBA_SEGMENTS or USER_SEGMENTS data dictionary views involving the columns BYTES, BLOCKS, or EXTENTS. Queries on DBA_TS_QUOTAS or USER_TS_QUOTES on columns BYTES or BLOCKS were also slow.
Even if you personally do not care about these dictionary views they are still very important since they are used by some Oracle internal components and the other database tools including Oracle Enterprise Manager (OEM) Cloud Control and its Database Home Page. Thus, I’ll describe below the problematic of those data dictionary views and the way how to fix their performance issues.
First of all do not wonder why queries against those views often seem to slow. DBA_SEGEMENTS for example is a very complex view that is built on another SYS_DBA_SEGS view. In summary DBA_SEGMENTS view on Oracle 11gR2 consists of the following components:
– 25 columns
– around 110 lines of SQL code
– 3 UNION ALL clauses
– A lot of joins between following tables: sys.user$, sys.ts$, sys.undo$, sys.seg$, sys.file$
This is the second part of my article about Column-Store databases. In the first part Column-Oriented Databases – Old Idea, New wave I was focusing on topics like performance and functionality of Column-Oriented Databases and their comparison to RDBMS, specifically to Oracle database. This time I will continue the comparison of two database camps – Column-Stores vs Row-Stores – in areas of compression, partitioning. I’ll mention also the usage of Column-oriented storage benefits in Oracle products, like for example a new Oracle 12c database In-Memory Option.
Compression of columns vs rows
One of the potential advantages of column-oriented storage is the possibility of good compression. It is important to understand why compressing the data can be advantageous. It is not primarily the pure cost of having enough disk space to cover the physical size of the data that matters – disks are relatively cheap and are getting larger and cheaper at a steady rate. Rather, the potential benefit is when data has to be retrieved from disk as part of processing queries. Good I/O bandwidth is not cheap and techniques, such as compression, that reduce the size of the data that is retrieved from storage can be very advantageous, although there is usually some CPU-cost associated with compressing and uncompressing the data.
Oracle for example provides several major mechanisms for utilizing compression to benefit query processing. One is the row-level objects compression feature; another is Exadata Hybrid Columnar Compression – HCC (see below).
Partitioning VERTICAL vs HORIZONTAL
Column-oriented storage is a form of vertical partitioning of the data. One of the disadvantages of this type of partitioning is Read more »
During the last few years I went through several POC of different Column-Store databases reviewing their functionality, performance and use cases. Usually at the beginning of every exercise I saw the impressive vendor promises of reach functionality, great performance and scalability. Some even said: this is a new trend in database world, even a standard! You do not need RDBMS anymore!
In this type of cases I usually act as conservative database architect. And you know what – that always helped eliminating additional companies’ efforts and frustrations in implementing specialized database solutions. This time I share some experiences in evaluating Column-Store databases. But let start with basics first.
While most commercial RDBMS products store data in some form of row format, some database vendors provide column-oriented storage of data. The supposed advantages of storing the data by column rather than by row include a better ability to compress the data, something that would reduce the need for disk-I/O. The idea of column-based storage is not new and has been used in commercial products from former Sybase and Sand Technology for well over a decade. In reality, each storage format has its own set of advantages and disadvantages and there is no free lunch – only tradeoffs.
The tradeoffs associated with column-based storage include the cost of tracking and eventual reconstruction of the rows to which the column values belong as well as additional complexity for ETL and OLTP processing. While recognizing that each storage format has its pros and cons and that there are scenarios where a column-based format has some merit, it is worth examining whether the column-based format lives up to its recent hype.
Beware of disingenuous benchmark numbers
Yes folks – PERFORMANCE is the main sales factor of the columnar databases!
There are claims that Column-Stores outperform a commercial row-store RDBMS by large factors. I just want to warn you to not rely blindly on magic performance benchmarks the vendors have done, in house themselves. Usually these performance test cases are not similar to the real production database loads, created often for read-only data using database engines that lacks RDBMS features and functionality that would be required in a production system.
A second observation is that the often benchmarks against Column-Stores do not test joins. Read more »
Below is a list of issues (ORA- and PLS- errors) I encountered after upgrading Oracle 11gR2 Database Enterprise Servers to the latest Patchset 22.214.171.124.
The upgrade mostly done:
– from 126.96.36.199.6-8
– to 188.8.131.52.1 (including Patchset Update PSU 1)
– on platforms: HP-UX Itanium, Linux
ORA-01979: missing or invalid password for role
Problem: Roles with passwords are failing after Oracle database Patchset upgrade
Solution: Recreate a role with a new password or without it.
alter role REPORTING_RW not identified;
ORA-01791: not a SELECTed expression
Problem: SQL having different number of columns in DISTINCT and ORDER BY clauses failing after Oracle database upgrade but it was working on previous release before last Patchset 184.108.40.206 since developers happily used an Oracle Bug that was fixed in 220.127.116.11. So the correct behavior is on 18.104.22.168 and not on older versions.
SQL> select distinct sal, empno from scott.emp order by deptno;
select distinct sal, empno from scott.emp order by deptno
ERROR at line 1:
ORA-01791: not a SELECTed expression
Solution: include all ORDER BY columns into Read more »