CBQT-ORE and its FIRST_ROWS optimization inability

CBQT-ORE and its FIRST_ROWS optimization inability


CBQT: cost based query transformation
ORE: or expansion

Some days ago, I took a look on a customer’s performance issue which was introduced after their upgrade from 12.1 to 19c. Quite fast we could narrow it down to a new optimizer feature „cost based or expansion“.

To get a basic understanding of the feature, I’ll recommend reading Optimizer Transformations: OR Expansion by Oracle’s CBO PM Nigel Bayliss.

As a quick fix we simply disabled the feature by setting „_optimizer_cbqt_or_expansion“ = off and the performance went back to good again.

Afterwards I wanted to understand the root cause and built a model to reproduce the problem.

rem ######################################
rem # set environment                    #
rem ######################################
alter session set statistics_level=ALL;

rem ######################################
rem # prophylactic cleanup               #
rem ######################################
drop table asc_t2;
drop table asc_t1;
drop table asc_t3;

rem ######################################
rem # create testdata                    #
rem ######################################

--will hold 1M rows with a unique ID
create table asc_t1
with gen as
   select rownum dummy from dual connect by level <= 1e4
select rownum id
  from gen, gen
 where rownum <= 1e6;

--will hold 1M rows with 500 distinct values of T1_ID (20000 records per value)
create table asc_t2
select id, mod(id, 500) t1_id, lpad('*', 250, '*') pad
  from asc_t1;

--will hold 1M rows with 500 distinct values of T1_ID (20000 records per value)
create table asc_t3
select id, t1_id, pad
  from asc_t2;

create unique index asc_t1_uk on asc_t1 (id);
create unique index asc_t2_uk on asc_t2(id);
create index asc_t2_i1 on asc_t2(t1_id);
create unique index asc_t3_uk on asc_t3(id);
create index asc_t3_i1 on asc_t3(t1_id);

And this is the query to be examined:

select t1.*
  from asc_t1 t1
 where id between 1 and 10
   and exists (select 1
                 from asc_t2 t2, asc_t3 t3
                where =
                  and (t2.t1_id = or t3.t1_id =

With no hints or further parameters set, this is the plan including some rowsource execution statistics pulled from memory after its execution:
So at first we can see that ORE was used and the query was split into two disjunct union all branches. What’s really interesting for me at a first sight is the presence of a blocking operation in the VW_ORE block (HASH JOIN at ID 4), despite the fact it is called in an exists clause and therefore has the ability to leave after the first row is found.

So for each row we got from ASC_T1 an in-memory hash table was build after scanning the appropriate index on T2 and visiting the table block (IDs 6 and 5).  In total 20.000 rows on 20.078 buffers were read on these operations. After that index ASC_T3_UK was probed against that hash table and here we see the effects of “exists” very cleary:  despite the index contains 1 million entries just 55 rows over all 10 calls needed to be read to find a first match and therefore be able to quit, because the exists clause was fulfilled. The second union-all branch wasn’t called at all.

Let’s look at two more examples.

First: forbid CBQT-ORE from kicking in.

select t1.*
  from asc_t1 t1where id between 1 and 10 
 and exists (select /*+ no_or_expand */1
                          from asc_t2 t2, asc_t3 t3
                        where =
                              and (t2.t1_id = or t3.t1_id =

And its rowsource execution statistics:
It has got a much lesser cost than the ORE plan and much less buffers were visited to execute the query. It gives also the impression that the CBO is now aware that it has to deal with an “exists” clause here, because that part of the plan was optimized to find a first matching row very quickly. E.g low cost for the FTS on ASC_T2 or the absence of blocking operations are indicators for this strategy.

Second: Switch back to LORE (Legacy OR Expansion)

select /*+ opt_param('_optimizer_cbqt_or_expansion', 'off') */ t1.* 
  from asc_t1 t1
 where id between 1 and 10
    and exists (select 1
                             from asc_t2 t2, asc_t3 t3
                          where =
                                and (t2.t1_id = or t3.t1_id =

Again rowsource execution statistics:
This one now shows the lowest cost and least buffers visited overall. It also seems to be aware of the exists clause and adapts a first_rows strategy in the relevant parts of the execution plan! This is basically what I would have expected from the CBQT-ORE.

So we now have a theory that CBQT-ORE loses track that its query block is called in an “exists” clause and provides an execution plan as if it was a standalone query, where it would need to fetch all the rows.

There’s some more evidence to this theory.

1.) The transformed query from the VW_ORE query block shows the same cost and execution plan when it is costed as a standalone query. The correlated value from the outer rowsource was replaced with a bind variable in the following example:

select 0
  from (
             select 1
                 from asc_t3 t3, asc_t2 t2
              where = and t2.t1_id = :b1
            union all
           select 1
               from asc_t3 t3, asc_t2 t2
            where =      
                 and t3.t1_id = :b1
                 and lnnvl (t2.t1_id = :b1)               );

generates this plan, which matches the “VW_ORE_82971ECB” from our first query.

2.) The 10053 traces for both the disabled CBQT-ORE and the old-style OR-Expansion (LORE) show lots of references that a first_rows optimization approach was chosen for the query blocks in question.


On the other hand when looking at the 10053 trace of the first query, interestingly before the ORE checks kicked in, the same plan was already found which was used in query 2, where I explicitly disabled the transformation.

Final cost for query block SEL$2 (#2) – First K Rows Plan:
  Best join order: 1
  Cost: 506.142990  Degree: 1  Card: 2.000000  Bytes: 71964.000000
  Resc: 506.142990  Resc_io: 506.000000  Resc_cpu: 4233492
  Resp: 506.142990  Resp_io: 506.000000  Resc_cpu: 4233492

Later in the same tracefile, the ORE transformation and costing was performed and produced a final cost (8591) which was (way) higher than the formerly calculated plan with a final cost of 506. But however at the end it was not picked and the CBO stayed with the cheapest ORE-plan.

CBQT-ORE does not seem to be aware when its query block resides in an “exists” clause and therefore doesn’t optimize for first rows access patterns. The query block gets optimized as if all rows would be needed to fetch. Additionally in certain scenarios it seems to “forget” if cheaper plans were found during the whole optimization process. If this effects you in a negative way, as a first action the cost based transformation can be turned off and the legacy version used instead. Additionally an SR was raised to tackle this issue.

Further Reading:
Excellent articles on the topic of CBQT Or-Expansion can also be found on the blogs of Patrick Joliffe, Mohamed Houri and Nenad Noveljic

Update 1:
After playing with the same testcase in my 21c lab I noticed the that the problem went away. After quick look into v$system_fix_control I found bug 28414968 “expansion with some constant branches;fkr1 in (NOT)EXISTS subque” which is first available in 19.11 and has to be activated proactively. That plan now gets produced in in 21c and after setting alter session set „_fix_control“=’28414968:3′; in >=19.11
10 buffers less visited compared to the LORE plan, as ID 8 was superfluous in the LORE plan.

Funnily, if I code the union-all instead of the or-predicate myself, I still get the old plan. But that’s one topic for another post I guess.

select id
  from demo.asc_t1 t1
 where >= 1
   and <= 10
   and exists (select 0
                            from demo.asc_t3 t3, demo.asc_t2 t2
                         where =
                               and t2.t1_id = 
                         union all
                         select 1
                             from demo.asc_t3 t3, demo.asc_t2 t2
                          where =
                                and t3.t1_id =
                                and lnnvl (t2.t1_id =

Oracle Active Dataguard and Cloning of PDBs – the easy way

In diesem Artikel werden wir mehrere Features der Datenbank beschreiben.

Active Data Guard, ein kostenpflichtiges Feature um Reporting auf die Standby Seite zu bringen (und tolle Administrationsfeatures beinhaltet, wie z. B. vereinfachtes Cloning (wie im Artikel beschrieben) oder Global Data Services).
Multitenant, ein Feature, das man für Datenbank-Konsolidierung benutzt und neue Möglichkeiten zur Administration anbietet.
PDB Cloning in einem ADG Environment: Clones kann man als eine Art von Backup sehen, wenn man Applikation Upgrades oder irgendwelche invasiven Tätigkeiten durchführt und einen schnellen Fallback haben möchte.
In höheren Versionen gibt es bessere Möglichkeiten, wie Flashback PDB oder PDB PITR.

Dieser Artikel dient zur Verbesserung des Daily Business, um Kunden zufriedener machen zu können. Wenn man schon Lizenzen besitzt, sollte man auch die zur Verfügung stehenden Features benutzen, nicht wahr?

Manche unserer Kunden haben Multitenant und Active Data Guard lizensiert, wobei man Multitenant sehr selten für Cloning oder verschiedene andere Tätigkeiten benutzt. Es ist auch klar, weshalb dies so ist, denn viele Kunden kennen diese Features nicht. Es gehört zu unseren Aufgaben, unseren Kunden diese Features vorzustellen und deren Einsatzgebiete zu erklären. Ein glücklicher Kunde ist der beste Kunde.

Bis vor kurzen war es so, dass wir entweder Restore Points erstellt haben, die in Wahrheit in einem CDB Environment nicht wirklich benutzbar sind, denn man kann nur den ganzen Container zurückspielen oder wir mussten Duplicates (Restore) erstellen, was Zeit beanspruchte und unnötige Downtime verursachte. Da wir gern diese Tätigkeiten optimieren, ist es nötig Kunden zu informieren wie man es in Zukunft machen könnte, damit die Projekte weitergehen und jeder zufrieden ist.
Dieses Dokument beschreibt die Möglichkeiten, die sehr einfach sind, wenig Zeit kosten, nur freien Storage und einen DBA brauchen, der diese Features kennt.

Active Data Guard erlaubt es den Kunden das Reporting auf die Disaster Systeme zu schwenken, damit die Performance der Produktivsysteme, bei performance-lastigen Reports, nicht beeinträchtigt wird. Es ermöglicht uns weiterhin die Dinge zu tun, die schnell sind, Downtimes vermeiden und Service Agreements leicht erfüllen.

Wenn man in einem Active Data Guard Environment eine lokale PDB klont, werden die Standby PDBs automatisch erstellt und in einer sind dazu nur 5 Kommandos notwendig (denn man muss die PSORGER PDB wieder READ WRITE öffnen).
In höheren Versionen kann man einen Hot Clone erstellen, wenn man Local Undo benutzt, das heißt Zero Downtime.


SYS@CDB1 SQL> alter pluggable database psorger close immediate instances=all ;
Pluggable database altered.
SYS@CDB1 SQL> alter pluggable database psorger open read only instances=all ;
Pluggable database altered.
SYS@CDB1 SQL> create pluggable database psorgerc from psorger ;
Pluggable database created.

[oracle@exa2vm01 ~]$ tail -f $al
  Mem# 0: +DATAX6C1/E2CDB1/ONLINELOG/group_22.2191.970881745
  Mem# 1: +RECOX6C1/E2CDB1/ONLINELOG/group_22.9224.970881745
1: +RECOX6C1/E2CDB1/ONLINELOG/group_22.9224.970881745
Thu Sep 10 20:36:27 2020
Recovery created pluggable database PSORGERC
Thu Sep 10 20:36:54 2020
Recovery copied files for tablespace SYSTEM
Recovery successfully copied file +DATAX6C1/E2CDB1/AEFAD6D19935CDF7E0537D001BAC3B4F/DATAFILE/system.2920.1050784587 from +DATAX6C1/E2CDB1/647EDCDA7FB28F8DE0538B001BACEE69/DATAFILE/system.1628.967329427
Datafile 23 added to flashback set
Successfully added datafile 23 to media recovery
Datafile #23: '+DATAX6C1/E2CDB1/AEFAD6D19935CDF7E0537D001BAC3B4F/DATAFILE/system.2920.1050784587'
Thu Sep 10 20:37:23 2020
Recovery copied files for tablespace SYSAUX
Recovery successfully copied file +DATAX6C1/E2CDB1/AEFAD6D19935CDF7E0537D001BAC3B4F/DATAFILE/sysaux.2929.1050784615 from +DATAX6C1/E2CDB1/647EDCDA7FB28F8DE0538B001BACEE69/DATAFILE/sysaux.1629.967329427
Datafile 24 added to flashback set

Wie man sieht reichen 3 Kommandos aus, um eine Pluggable Database zu klonen (EE mit ADG). Eine 100GB Datenbank kann auf einer Exadata in wenigen Minuten geklont werden und somit verfügt man über eine „Point in Time“ Kopie der PDB, die man im Fall eines schnellen „Restores“ wieder benutzen kann. Es handelt sich hierbei nicht um einen Restore, aus Kundensicht sieht es jedoch so aus, als würde man etwas aus der Vergangenheit zurückbringen. Letztendlich wird hierbei nur ein mächtiges Feature der Enterprise Edition von Oracle genutzt.

Natürlich gibt es aber auch Situationen, in denen ein Restore erforderlich wird, da keine Clones erstellt wurden. In diesem Fall ist es am besten einen DUPLICATE vom Backup zu erzeugen. Dies ist einfach umzusetzen, braucht nur wenige Kommandos und ist automatisiert, so dass der DBA wenig Zeit mit der Kommandoeingabe verliert. Mehr Informationen zu diesem Thema folgen in einem weiteren Artikel.

Jetzt werden wir uns aber dem Thema cloning weiter widmen und zeigen in wenigen Kommandos, wie wir aus dem Duplikat die Pluggable Database wieder in den korrekten Container einfügen können.

Das Scenario beinhaltet:
1x Active Data Guard Configuration ( – CDB1 mit mehreren PDBs, PDB1, PDB2, PDB3
1x Duplikat der CDB Point in Time Recovered ( – CDBDUP mit PDB1

Die technischen Schritte um die PDB1 aus der CDBDUP in die CDB1 zu kopieren sind folgende:

1. In der CDB1 löschen wir die PDB1

alter pluggable database pdb1 close immediate instances=all ;
drop pluggable database pdb1 including datafiles ;

2. In der CDBDUP der PDB1 erstellen wir einen Benutzer mit Berechtigungen zum Klonen

CREATE USER remote_clone_user IDENTIFIED BY remote_clone_user;

3. In den tnsnames.ora beider DB Systeme definieren wir einen Connect String der im Scenario benutzt wird. Es ist gut nicht produktive Connect Strings zu benutzen, so weiß man zu 100% wo man sich einloggt


4. In der CDB1 auf der Standby Seite setzen wir einen Parameter damit die PDB automatisch in das Standby Environment kopiert wird.

alter system set standby_pdb_source_file_dblink='pdb1_ps' ;

5. In der CDBDUP setzen wir die PDB1 in READ ONLY Modus (nach Duplicate ist die DB im MOUNT Zustand, in höheren Versionen ist diese Read Only Restriction nicht mehr da, da könnte man dann einen Relocate zB. machen)

alter pluggable database PDB1 open read only ;

6. In der CDB1 erstellen wir einen Database Link zu der PDB1 im CDBDUP

create database link pdb1_ps connect to remote_clone_user identified by remote_clone_user using 'pdb1_ps' ;

7. Wir erstellen einen Klon der PDB in der CDB1 und warten bis es fertig wird. Auf einer Exadata X6-2 dauerte eine 110GB PDB 10 Minuten. Im Background wird der Database Link für den Klon auf die Standby Seite benutzt.

create pluggable database PDB1 from pdb1@pdb1_ps ;

8. Wir öffnen die PDB1 und sind mit der Bereitstellung der PDB1 fertig. Wir öffnen die PDB mit dem Application Service damit es auf den korrekten Instanzen startet (im RAC kann es 1 Node sein oder auch 10)

alter pluggable database PDB1 close immediate instances=all ;
srvctl start service -db CDB1 -pdb pdb1_svc

Somit haben wir die PDB1 wieder in Betrieb genommen und die Welt ist für heute gerettet.
In einem Data Guard Environment geht es leider nicht so einfach, man muss manuell die Datafiles auf die Standby Seite transferieren. Um mehr Informationen darüber zu erfahren bitte den Oracle Note durchlesen.
Using standby_pdb_source_file_dblink and standby_pdb_source_file_directory to Maintain Standby Databases when Performing PDB Remote Clones or Plugins (Doc ID 2274735.1)