Index Bouncy Scan 4

There’s always another hurdle to overcome. After I’d finished writing up the “index bouncy scan” as an efficient probing mechanism to find the combinations of the first two columns (both declared not null) of a very large index a follow-up question appeared almost immediately: “what if it’s a partitioned index”.

The problem with “typical” partitioned indexes is that the smallest value of the leading column might appear in any of the partitions, and the combination (more...)

Min/Max upgrade

Here’s a nice little optimizer enhancement that appeared in 12.2 to make min/max range scans (and full scans) available in more circumstances. Rather than talk through it, here’s a little demonstration:

rem     Script:         122_minmax.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2018
rem     Purpose:
rem     Last tested
rem           Good path
rem           Bad path

create table pt1 (

Index Bouncy Scan 3

This is a follow-up to a problem I had with yesterday’s example of using recursive CTEs to “bounce” along a multi-column index to pick out the unique set of combinations of the first two columns. Part of the resulting query used a pair of aggregate scalar subqueries in a select list – and Andrew Sayer improved on my query by introducing a “cross apply” (which I simply hadn’t thought of) which the optimizer transformed into (more...)


One of my maxims for Oracle performance is: “Don’t try to be too clever.” Apart from the obvious reason that no-one else may be able to understand how to adjust your code if the requirements change at a future date, there’s always the possibility that an Oracle upgrade will mean some clever trick you implemented will stop working.

While searching for information about a possible Oracle bug recently I noticed the following fix control (more...)

Index Bouncy Scan 2

I wrote a note some time last year about taking advantage of the “index range scan (min/max)” operation in a PL/SQL loop to find the small number distinct values in a large single column index efficiently (for example an index that was not very efficient but existed to avoid the “foreign key locking” problem. The resulting comments included pointers to other articles that showed pure SQL solutions to the same problem using recursive CTEs (“with” (more...)

Oracle SOA Suite 12c: How to deploy a BPEL onto a weblogic server

After you have created your BPEL inside Jdeveloper, you have to add an application server inside jdev:

 And here we go:

Then go to the applications view:

And after the deployment check via Application Server view, if the BPEL version was deployed:

Oracle SOA Suite 12c: Invoking a BPEL process via Jdeveloper

After you deployed a BPEL process to your application server open the application server view in Jdeveloper:
 Open the BPEL inside the Application Server view:
 Do a right click on the BPEL process and select "Test Web Service":
 This will open the HTTP Analyzer:
 Fill in your XML input and click "Send Request". After that you will see:

Very nice are the options via the tab at the bottom:
  • SOAP Strucutre
  • HTTP Content
  • REST Structure
  • (more...)

DBFS Nightmare

How to cleanup DBFS tablespace after removing files at DBFS filesystem (Doc ID 2331565.1)	
High level overview of the DBMS_DBFS_SFS.REORGANIZEFS procedure:
1) Create a NEW tablespace
2) Create a temporary filesystem with dbms_dbfs_sfs.createFilesystem in the new tablespace.
3) Run dbms_dbfs_sfs.reorganizeFS -->>
4) The dbfs data is now in the smaller NEW tablespace.
5) Drop the temporay filesystem with dbms_dbfs_sfs.dropFilesystem
6) The OLD original  (more...)

Multiplex Redo Log

When db_create_online_log_dest_1 is defined, REDO log is not multiplexed which is good for creating STANDBY REDO.

REDO log is created at db_create_online_log_dest_1 ONLY.

However, when creating ONLINE REDO, db_create_online_log_dest_1 should NOT be defined to be multiplexed.

REDO logs are created at db_create_file_dest and db_recovery_file_dest.

[oracle@db-asm-1 sql]$ sqlplus / as sysdba @ logfile.sql

SQL*Plus: Release Production on Thu May 3 05:56:30 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:

Oracle SOA Suite 12c: complete startup procedure including database and application server

After the first reboot i had to work hard to get the complete system up again. So here a list of all commands:

  1. database
    export ORACLE_HOME=/home/oracle/app/oracle/product/12.2.0/dbhome_1
    export PATH=$PATH:$ORACLE_HOME/bin
    export ORACLE_SID=orcl

    $ sqlplus / as sysdba

    SQL*Plus: Release Production on Fri Feb 16 20:53:02 2018

    Copyright (c) 1982, 2016, Oracle.  All rights reserved.

    Connected to an idle instance.

    SQL> startup
    ORACLE instance started.

    Total System Global Area 1560281088 (more...)

Who’s Gathering DB Stats?

There was an incident where statistics were being gathered during prime operating hours causing performance issues.
One DBA already verified GATHER_STATS_JOB has already been configured to not run during critical hours.
Speculation is stats are being gathered manually and how to prove this?

AUTO JOB has OPERATION : gather_database_stats (auto).
MANUAL JOB is not being run by scheduler either; otherwise, there would be JOB_NAME.

Half of the mystery is solve, but where is gather_table_stats running  (more...)

Oracle SOA Suite 12c: Invoking a BPEL process via Enterprise Manager (EM)

After you logged in via http://yourhost:7001/em navigate to "soa-infra":
 Then choose "Deployed Composites":
 There you have to click on your composite (here: "Project1"):
 Choose "Test":
 Go to the bottom of the page and expand the payload:
 There you have  fill in your test string:
 And after a click on "Test Web Service"
The response is shown incl. the response time.

To list the audit trails goto "Home-> Flow instances

And choose "search" on the right side:

Answer: Anything Wrong With Query Performance? (Red Right Hand)

I of course attract a highly clever readership :). As some have commented, for a single table to require 1000+ consistent gets to retrieve 1000 rows implies that each row needs to be accessed from a different block. This in turn implies the Clustering Factor for this index to be relatively bad and the associated […]

Playing with Oracle Database Cloud – Migrating to Cloud using an Oracle Cloud Backup

The idea for this scenario is to learn how to migrate an Oracle Database from on-premises to Oracle Cloud DBaaS by using a previously done backup using Oracle Cloud Backup Services. First, we will create an Oracle Database Cloud Services database

Playing with Oracle Database Cloud Backup Service

This article will show you how easy is to make use of Oracle Cloud to securely store all our on-premises Oracle Database Backups. Note that backups stored in Oracle Cloud can also be used to create new Oracle Database Instances

Oracle 12c SOA Suite: Administration via Enterprise Manager


i want to present some screenshots of the web console of the SOA Suite:
But first the servers have to be started:
cd /home/oracle/Oracle/Middleware/Oracle_Home/user_projects/domains/base_domain/bin
nohup ./ &
nohup ./ &
Now the login is available:

 But there the soa_server1 is still down:

 So let's start this one:
nohup . (more...)

Oracle SOA Suite 12c: Tokens – To avoid substituting values: define global token variables

Inside the Enterprise Manager (URL: http://yourhost:7001/em) you can configure SOA Suite profiles:

Choose "Token Configurations"


To list the tokens click on "Modify Configuration file":

 To add a token click on the green plus:

 Do not forget to save your changes:

To use the bulk append you have to create a file like this one:

Choose your file "text.xml":
 and click on "Append":

Playing with CrashSimulator

Hi everyone,   Today I will show you how CrashSimulator (Shell Script – current version 1.04) works in detail. When unzipping the zip file you will find the following files within it: – For high-resolution ssh emulator screens, it

Improve Data Clustering on Multiple Columns Concurrently (Two Suns in the Sunset)

I’ve had a couple of recent discussions around clustering and how if you attempt to improve the clustering of a table based on a column, you thereby ruin the current clustering that might exist for a different column. The common wisdom being you can only order the data one way and if you change the […]

Linking Oracle GoldenGate Classic Architecture to Oracle GoldenGate Microservices Architecture

In my last post I covered how to connect the microservices architecture to the classic architecture (here). For this post, I want to show you how to connect the GoldenGate Classic Architecture to the new GoldenGate Microservices Architecture.

You many be asking yourself, why do I want to do this? The answer is quite simple. At the current moment and time, there is no upgrade path to move from Classic to Microservices. This (more...)