Search

Top 60 Oracle Blogs

Recent comments

Oracle 18c

Enabled, Accepted, Fixed SQL Plan Baselines

When the documentation is not always clear, I prefer to build a test case to be sure about the behavior in different context and different versions. Here is a test on SQL Plan Management to show which plan is chosen among the different states of SQL Plan Baselines: Enabled, Accepted, Fixed. Thanks to Oracle ACE program, I have some Oracle Cloud credits to quickly provision a database, so I tested that on Oracle 18c.

For this test, I’ve created a table:

create table DEMO as select rownum n from xmltable('1 to 10000');

with 8 indexes:

exec for i in 1..8 loop execute immediate 'create index DEMO'||i||' on DEMO(n,'||i||')'; end loop;

and a procedure to query it several times, setting random costs for the indexes, with only one cheapest:

create or replace procedure runplans(n number) as
dummy number;
begin

18c new Lost Write Protection

There are many layers between the Oracle Database pwrite() calls and the physical sector written on disk: filesystem, logical volume, SAN or NAS, with a lot of smart software running for Virtualisation, Compression, Snapshotting, Synchronisation… Are you sure that the changes you made to your data is actually persisted on disk, completely and without any corruption? In case of bug or crash in the storage layer, it may happen that only part of the changes was written. In the case of crash, Oracle ensures that the datafile headers are written at the end, so that recovery can kick-in after the crash. Then, a partially written block can be detected and restored. With different checksum settings, you can also check block integrity while writing or reading. But that protects only for fractured blocks. What if a block write just did not occur? An old version of the block remains and then is perfectly correct for checksum, RMAN, and DBV.

18c dbms_xplan note about failed SQL Plan Baseline

SQL Plan Baselines is a great feature for plan stability: you capture the plans that you accept. However, if the data model changes and the accepted plans cannot reproduce, the optimizer will come with a new plan. In 18c we have a note from DBMS_XPLAN when the optimization ‘failed to use SQL plan baseline for this statement’.

I create a table, with an index, and run a query on it using this index:

SQL> create table DEMO as select rownum n from xmltable('1 to 1000');
Table DEMO created.
 
SQL> create index DEMO_N on DEMO(n);
Index DEMO_N created.
 
SQL> select * from DEMO where n=1;
 
N
-
1

18c, Cloud First and Cloud Only features: think differently

Remember the times when the Oracle Software features were the same on all platforms? Where Oracle databases could be ported to any relevant platform? Where we were able to try any feature, freely, by downloading the latest release software? Now we need to think differently. Because:

PDB upgrade from 12c to 18c

Oracle 18c is out, in the Oracle Cloud, and the first thing I do with a new version is testing how long it takes to upgrade a previous version PDB by unplug/plug. Faster upgrade should be the benefit of having a slim dictionary where the system objects are reduced to metadata links and data links. However, it looks like upgrading the PDB dictionary still takes the same time as upgrading the CDB$ROOT.

18c Read Only Oracle Home

Capture18c000This is the big new feature of Oracle 18c about database software installation. Something that was needed for decades for the ease of software deployment. Piet de Visser raised this to Oracle a long time ago, and we were talking about that recently when discussing this new excitement to deploy software in Docker containers. Docker containers are by definition immutable images. You need a Read Only Oracle Home, all the immutable files (configuration, logs, database) being in an external volume. Then, to upgrade the software, you just open this volume with an image of the new database version.

Oracle Database Multilingual Engine (MLE)

My ODC appreciation blog post was about Javascript in the database running in the beta of the Oracle Database Multilingual Engine (MLE). Here I’ll detail my first test which is a comparison, in performance, between a package written in Javascript, running in the MLE, and one written and running in PL/SQL.

ODC Appreciation Day : Javascript in the database

Tim Hall has launched the idea to post small blogs this day, from all the Oracle community, about an Oracle feature. I choose one feature that is only released in beta test for the moment: the Multilingual Engine (MLE) which is able to run Javascript stored procedures in the database.

Autonomous Database

Larry Ellison has announced Oracle 18c to be the Autonomous Database, the no-human labor, and self-driven, database. Here is a quick recap of what it is behind the marketing words. My opinion only.

Oracle 18c RU and RUR for pioneers and followers

In a previous post, I explained the announcement of the new release model, with Release Updates and Release Update Revisions replacing Proactive Bundle Patch and PSUs. Here is a description that will help you to plan your patching activity.

Which DBA are you?

There are currently a lot of discussions about the different types of DBAs. When it comes to patching, the DBA type is important.

Some DBAs are very conservative: do not use new features, do not upgrade to ‘.1′ releases, because every change can break anything. At least, maybe just patch to SPU for the security fixes. Some are more confident and apply the PSUs, with more fixes. The problem is that what is not fixed may be subject to a bug one day, and need an urgent one-of patch.