Feed aggregator

How to solve the conflict: Need logging including SQLCODE & SQLERRM, while controlling side effects at the same time

Tom Kyte - Sun, 2017-04-16 13:06
Here are two closely related questions regarding SQLCODE, SQLERRM, PRAGMA_RESTRICT_REFERENCES and its deprecation, and the need for a logging framework. We developed a logging framework in PL/SQL which allows us to use simple statement like log.in...
Categories: DBA Blogs

How to connect and make dashboard using SQLite database

Nilesh Jethwa - Sun, 2017-04-16 12:25

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite's code is free as it is in the public domain so it can be used for any purpose, commercial or private. SQLite is the most widely deployed database in the world various applications, including several high-profile projects.

 SQLite dashboard visually summarizes all the important metrics you have selected to track, to give you a quick-and- easy overview of where everything stands. With real-time SQLite SQL reporting reporting, it's a live view of exactly how your marketing campaign is performing.

SQLcl on Bash on Ubuntu on Windows

Yann Neuhaus - Sun, 2017-04-16 11:54

I’m running my laptop on Windows, which may sound weird, but Linux is unfortunately not an option when you exchange Microsoft Word documents, manage your e-mails and calendar with Outlook and present with Powerpoint using dual screen (I want to share on the beamer only the slides or demo screen, not my whole desktop). However, I have 3 ways to enjoy GNU/Linux: Cygwin to operate on my laptop, VirtualBox to run Linux hosts, and Cloud services when free trials are available.

Now that Windows 10 has a Linux subsystem, I’ll try it to see if I still need Cygwin.
In a summary, I’ll still use Cygwin, but may prefer this Linux subsystem to run SQLcl, the SQL Developer command line, from my laptop.

Bash on Ubuntu on Windows

In this post I’ll detail what I had to setup to get the following:
Bash on Windows 10 is available for several months, but with no interaction with the Windows system except accessing to the filesystems. I didn’t try that. This month, Microsoft has released a new update, called ‘Creator Update’ for whatever reason.

Creator Update

You will probably have no choice to update to ‘Creator Update’ soon but for the moment you have to download Windows10Upgrade9252.exe from https://www.microsoft.com/en-us/software-download/windows10

Windows Subsystem for Linux

You enable the feature from Control Panel -> Programs and Features -> Turn Windows features on and off:

This requires a reboot. Windows is not yet an OS where you can install or enable features without closing everything. But at least in Windows 10 the reboot is very fast.

Developer mode

This is a beta feature and requires to enable developer mode:

You do that on the Setup -> Update and Security -> For developers:



Now, when you run it (type Bash in the start menu) it installs a subset of Ubuntu (downloaded from the web):
It asks for a user and password. You will need the password to sudo to root.
You are in Windows/System32 here, which is ugly, so better exit and run again ‘Bash on Ubuntu on Windows’.


All my customization (.bash_profile .bashrc .vimrc .tmux.conf .ssh/config … ) is in my cygwin environment and I want to share it for the time I’ll run both Cygwin and Bash on Ubuntu on Windows. For this, I sudo and change the entry in /etc/passwd to have my home where I have my cygwin.home:


Here are the mount points I have on Cygwin
$ mount
C:/cygwin64/bin on /usr/bin type ntfs (binary,auto)
C:/cygwin64/lib on /usr/lib type ntfs (binary,auto)
C:/cygwin64 on / type ntfs (binary,auto)
C: on /cygdrive/c type ntfs (binary,posix=0,user,noumount,auto)
D: on /cygdrive/d type ntfs (binary,posix=0,user,noumount,auto)

My C: and D: windows drives are mounted in /cygdrive

Here are the mounts I have on the Windows Subsystem for Linux:
root@dell-fpa:/mnt# mount
rootfs on / type lxfs (rw,noatime)
data on /data type lxfs (rw,noatime)
cache on /cache type lxfs (rw,noatime)
mnt on /mnt type lxfs (rw,noatime)
sysfs on /sys type sysfs (rw,nosuid,nodev,noexec,noatime)
proc on /proc type proc (rw,nosuid,nodev,noexec,noatime)
none on /dev type tmpfs (rw,noatime,mode=755)
devpts on /dev/pts type devpts (rw,nosuid,noexec,noatime)
none on /run type tmpfs (rw,nosuid,noexec,noatime,mode=755)
none on /run/lock type tmpfs (rw,nosuid,nodev,noexec,noatime)
none on /run/shm type tmpfs (rw,nosuid,nodev,noatime)
none on /run/user type tmpfs (rw,nosuid,nodev,noexec,noatime,mode=755)
C: on /mnt/c type drvfs (rw,noatime)
D: on /mnt/d type drvfs (rw,noatime)
root on /root type lxfs (rw,noatime)
home on /home type lxfs (rw,noatime)
binfmt_misc on /proc/sys/fs/binfmt_misc type binfmt_misc (rw,noatime)

Because I have scripts and configuration files that mention /cygdrive, I’ve created symbolic links for them:

fpa@dell-fpa:/mnt$ sudo su
[sudo] password for fpa:
root@dell-fpa:/mnt# mkdir /cygdrive
root@dell-fpa:/# ln -s /mnt/c /cygdrive/c
root@dell-fpa:/# ln -s /mnt/d /cygdrive/D


The first thin I do from my bash shell is to ssh to other hosts:

fpa@dell-fpa:/mnt/c/Users/fpa$ ssh
Bad owner or permissions on /mnt/d/Dropbox/cygwin-home//.ssh/config

Ok, permissions of .ssh was set from cygwin, let’s try it from Bash On Ubuntu on Linux:

fpa@dell-fpa:/mnt/c/Users/fpa$ chmod 644 /mnt/d/Dropbox/cygwin-home//.ssh/config
fpa@dell-fpa:/mnt/c/Users/fpa$ ls -ld /mnt/d/Dropbox/cygwin-home//.ssh/config
-rw-rw-rw- 1 root root 5181 Mar 5 16:56 /mnt/d/Dropbox/cygwin-home//.ssh/config

This is not what I want. With 644 I expect -rw-r–r–

Let’s try 444:

fpa@dell-fpa:/mnt/c/Users/fpa$ chmod 444 /mnt/d/Dropbox/cygwin-home//.ssh/config
fpa@dell-fpa:/mnt/c/Users/fpa$ ls -ld /mnt/d/Dropbox/cygwin-home//.ssh/config
-r--r--r-- 1 root root 5181 Mar 5 16:56 /mnt/d/Dropbox/cygwin-home//.ssh/config
fpa@dell-fpa:/mnt/c/Users/fpa$ ssh
Last login: Sun Apr 16 15:18:07 2017 from

Ok, this works but there’s a problem. It seems that the Bash On Ubuntu on Linux doesn’t allow to set permissions differently for user, group and others.


The second thing I do from bash in my laptop is to connect to databases with SQLcl. For Cygwin I had an alias that run the sql.bat script because Cygwin can run .bat files. When I run SQLcl from Cygwin, I run the Windows JDK. This doesn’t work in Bash on Ubuntu on Windows because we are in a Linux subsystem. But we don’t need to because SQLcl can be run directly from the sql bash script, calling the Linux JDK from the Linux subsystem. There’s only one thing to do: download the Linux JDK and set JAVA_HOME to the directory.

In my .bashrc I have the following to set the ‘sql’ alias depending on which environment I am

if [[ $(uname -a) =~ CYGWIN ]] then
alias sql='/cygdrive/D/Soft/sqlcl/bin/sql.bat'
alias sql='JAVA_HOME=/mnt/d/Soft/jdk1.8.0-Linux /cygdrive/D/Soft/sqlcl/bin/sql'

What I observe here is that it is much faster (or less slower…) to start the JVM from the Linux subsystem.
Here 4 seconds to start SQLcl, connect and exit:

fpa@dell-fpa:/tmp$ time sql sys/oracle@// as sysdba <<
real 0m4.684s
user 0m3.750s
sys 0m2.484s
fpa@dell-fpa:/tmp$ uname -a
Linux dell-fpa 4.4.0-43-Microsoft #1-Microsoft Wed Dec 31 14:42:53 PST 2014 x86_64 x86_64 x86_64 GNU/Linux

Here the same from Windows (Cygwin to time – but it’s running on Windows):

$ time sql sys/oracle@// as sysdba <<
Disconnected from Oracle Database 12c Enterprise Edition Release - 64bit Production
real 0m16.261s
user 0m0.000s
sys 0m0.015s
fpa@dell-fpa ~
$ uname -a
CYGWIN_NT-10.0 dell-fpa 2.7.0(0.306/5/3) 2017-02-12 13:18 x86_64 Cygwin

So what?

The Linux subsystem on Windows is not yet ready. The only thing I proved here is that it is faster to start a Java application from Linux, but for this I always have a VirtualBox VM started on my laptop, and this is where it is faster to run it, and have a real Linux system.


Cet article SQLcl on Bash on Ubuntu on Windows est apparu en premier sur Blog dbi services.

12cR1 RAC Posts -- 8i : Switchback from SingleInstance to RAC

Hemant K Chitale - Sun, 2017-04-16 10:59
Earlier this week (10-April), I had done a Switchover from the 2node RAC database to a SingleInstance database.

It is time now to Switchback from SingleInstance to RAC.

First, I check the status of the two databases :

On STBY (the current Primary) :

SQL> select open_mode, database_role from v$database;

-------------------- ----------------

SQL> select instance_name, host_name from v$instance;



Next, I check on the RAC database instance RAC1 (the current Standby) :

SQL> select open_mode, database_role from v$database;

-------------------- ----------------

SQL> select instance_name, status, host_name from gv$instance;

---------------- ------------



I also confirm that only one of the two RAC instances is doing recovery (in 12.1 we have only 1 instance in RAC doing recovery) by verifying the (automatic) message in the alert log for RAC1 :

Sun Apr 16 23:05:50 2017
Attempt to start background Managed Standby Recovery process (RAC1)
Starting background process MRP0
Sun Apr 16 23:05:50 2017
MRP0 started with pid=52, OS id=16739
Sun Apr 16 23:05:50 2017
MRP0: Background Managed Standby Recovery process started (RAC1)
Sun Apr 16 23:05:55 2017
Started logmerger process
Sun Apr 16 23:05:56 2017
Managed Standby Recovery starting Real Time Apply
Sun Apr 16 23:06:10 2017
Parallel Media Recovery started with 2 slaves

Now, I add some data to the PDB Pluggable Database currently running in STBY :

[oracle@oem132 ~]$ sqlplus hemant/hemant@PDB

SQL*Plus: Release Production on Sun Apr 16 23:29:37 2017

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

Last Successful login time: Mon Apr 10 2017 23:43:30 +08:00

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

SQL> select count(*) from my_data;


SQL> create table my_new_table as select * from my_data;

Table created.


I am now ready to switchover from STBY to RAC. However, I have a problem because I started the RAC database (and , therefore, DataGuard Broker) before I  started the SingleInstance node and database.  I have drcRAC1.log and drcRAC2.log both reporting :

04/16/2017 23:05:38
Failed to connect to remote database stby. Error is ORA-12543
Failed to send message to site stby. Error code is ORA-12543.
database rac unable to contact primary database for version check; status ORA-12543
completing bootstrap of this database

The fix is to have the Standby RAC database started *after* the singleInstance Primary and verify that Managed Recovery is restarted in RAC1 :

[oracle@collabn1 ~]$ srvctl stop database -d RAC
[oracle@collabn1 ~]$ srvctl start database -d RAC

Sun Apr 16 23:43:58 2017
Attempt to start background Managed Standby Recovery process (RAC1)
Starting background process MRP0
Sun Apr 16 23:43:59 2017
MRP0 started with pid=53, OS id=2033
Sun Apr 16 23:43:59 2017
MRP0: Background Managed Standby Recovery process started (RAC1)
Sun Apr 16 23:44:03 2017
RFS[2]: Assigned to RFS process (PID:1922)
RFS[2]: Selected log 5 for thread 1 sequence 76 dbid 2519807290 branch 931825279
Sun Apr 16 23:44:04 2017
Started logmerger process
Sun Apr 16 23:44:07 2017
Managed Standby Recovery starting Real Time Apply
RFS[1]: Selected log 7 for thread 1 sequence 78 dbid 2519807290 branch 931825279
Sun Apr 16 23:44:20 2017
Archived Log entry 144 added for thread 1 sequence 77 ID 0x96bd0722 dest 1:
Sun Apr 16 23:44:25 2017
Archived Log entry 145 added for thread 1 sequence 76 ID 0x96bd0722 dest 1:
Sun Apr 16 23:44:27 2017
Parallel Media Recovery started with 2 slaves
Sun Apr 16 23:44:27 2017
Waiting for all non-current ORLs to be archived...
Sun Apr 16 23:44:27 2017
All non-current ORLs have been archived.

Now, recheck the configuration from the SingleInstance node and then  Switchover to RAC :

[oracle@oem132 ~]$ dgmgrl
DGMGRL for Linux: Version - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/racattack@STBY
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - rac

Protection Mode: MaxPerformance
stby - Primary database
rac - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 16 seconds ago)

DGMGRL> switchover to rac;
Performing switchover NOW, please wait...
Operation requires a connection to instance "RAC1" on database "rac"
Connecting to instance "RAC1"...
Connected as SYSDBA.
New primary database "rac" is opening...
Operation requires start up of instance "STBY" on database "stby"
Starting instance "STBY"...
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
start up and mount instance "STBY" of database "stby"


The ORA-12514 error here is acceptable. I only need to startup STBY manually.

DGMGRL> exit
[oracle@oem132 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release Production on Sun Apr 16 23:51:25 2017

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 2929936 bytes
Variable Size 289409776 bytes
Database Buffers 541065216 bytes
Redo Buffers 5455872 bytes
SQL> alter database mount standby database;

Database altered.

SQL> select open_mode, database_role from v$database;

-------------------- ----------------

SQL> select instance_name, status, host_name from v$instance;

---------------- ------------


STBY has now reverted to being a Standby.

Let me check the RAC database instances

[oracle@collabn1 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release Production on Sun Apr 16 23:55:28 2017

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

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

SQL> select open_mode, database_role from v$database;

-------------------- ----------------

SQL> select instance_name, status, host_name from gv$instance;

---------------- ------------



Yes, RAC is now Primary with both instances OPEN.

Let me (now on RAC) verify the new table created and populated when STBY was the Primary. 

SQL> alter pluggable database pdb open;
alter pluggable database pdb open
ERROR at line 1:
ORA-65019: pluggable database PDB already open

SQL> alter session set container=PDB;

Session altered.

SQL> select table_name from dba_tables where owner = 'HEMANT';


SQL> select count(*) from hemant.my_new_table;



Yes, the data in the Pluggable Database PDB has also come over to RAC.

So, on 10-April, I did a Switchover from RAC (2nodes) to STBY (SingleNode).  Today, I have done a Switchover from STBY to RAC.

Categories: DBA Blogs

Importance of Dashboards and Data visualization for Energy and Utility

Nilesh Jethwa - Sat, 2017-04-15 09:25

Time-and-again, analytics has been useful in people’s lives, whether it’s in higher education, marketing, the health sector, or in energy and utility services. The last one, however, is still in a major upheaval because of the introduction of the latest technologies to measure utility and provide more accurate energy readings. But if used properly, the data from analytics can prove to be reliable sources of insights for the proposal of new energy utilization schemes and gaps in the supply and demand chain.

The benefits and use of analytics for energy and utility do not entirely differ from those in the marketing, higher education, and public health service. Data taken from the analytics reveal a lot about consumer behavior, which is then an important facet in businesses and public service.

For example, energy-providing companies can pool together the data from surveys and smart devices to define how consumers use energy, and how much energy should cost in a specific area.

Instead of hiring manpower to do this analysis for your company, you may rely on an employee or two to encode or track the data. The rest is for the automated system to process.

Read more at http://www.infocaptor.com/dashboard/benefits-of-dashboards-and-data-visualization-for-energy-and-utility

How to stabilize, improve and tune Oracle EBS Payroll and Retropay process

Syed Jaffar - Sat, 2017-04-15 05:45
Visited few customers off late to review the performance issues pertaining to their Oracle EBS Payroll and Retro-pay processes. Not sure if many are aware of the tools Oracle has to analyze and improve any Oracle EBS modules, including Payroll and Retro-pay. To get proactive with Oracle EBS, refer the following note:

  • Get Proactive with Oracle E-Business Suite - Product Support Analyzer Index (Doc ID 1545562.1)

I must say, after running through the analyzers (Retro and Payroll), and implementing the suggestions, significant performance is achieved without making any change to the queries. I would strongly recommend to run the analyzers on different modules on Oracle EBS to get proactive and achieve performance improvements and stability. Below is the Payroll analyzer report screen shot, explains the findings and recommendations:


Few good MOS notes to stabilize, improve and tune the Retro-pay and Payroll processes on Oracle EBS environment:

  • EBS Payroll RetroPay Analyzer (Doc ID 1512437.1)
  • EBS Database Parameter Settings Analyzer (Doc ID 1953468.1)
  • EBS Payroll Analyzer (Doc ID 1631780.1)
  • EBS HRMS Payroll - RetroPay Advisor (Doc ID 1482827.1)
  • RetroPay Analyzer Tool FAQ (Doc ID 1568129.1)

Oracle Business Solution Lead Summit: Win, Win, Win With OAUX

Usable Apps - Sat, 2017-04-15 02:00

The Oracle Business Solution Lead (BSL) Summit was held at the Oracle Thames Valley Park, UK offices on February 6–9, 2017.

Oracle Applications User Experience (OAUX) Group Vice President Jeremy Ashley (@jrwashleyand OAUX HCM Senior Director Aylin Uysal (@aylinuysal) delivered a UX Futurist Keynote presentation, including a strategic overview of the Oracle Applications Cloud UX approach, the visual evolution for SaaS UX, wearables, conversational computing interaction, and insights into how machine learning will move things faster towards that vision of a more human way of working.

The event brought together application solution consultants from across EMEA and APAC who lead our large-scale, strategic, multi-pillar opportunities; all keen to hear about and leverage the OAUX message and share their know-how for winning more, bigger, and faster big-bet deals. Attendees learned about impactful approaches and skills to engage opportunities, how those big-bet deals and adoption can be accelerated, and participated in fun, interactive activities along the way.

Jeremy and Aylin at BSL Summit

Jeremy Ashley and Aylin Uysal deliver the OAUX message

Jeremy and Aylin were supported onsite at TVP by Ana Tomescu (@annatomescu), OAUX Cloud UX PM based in Bucharest.

TTS unable to modify a file during IMPDP execution on 12c PDB

Tom Kyte - Sat, 2017-04-15 00:26
Iwas trying to perfrom IMPDP tts for a set of tablespaces .While import other tablespaces are getting created but the IMPDP is failing in between when it is trying to read the below file and saying unable to modify .. My understanding is that the tab...
Categories: DBA Blogs

Converting NVARCHAR to DATE - ora-01858 to_date regexp_substr

Tom Kyte - Sat, 2017-04-15 00:26
I have a query where I"m trying to return an id by filtering on a nvarchar2(2000) column. If I hard code one of the filter conditions, the query returns the desired output, but when using a subquery, I get the ORA-01858 error: This query returns ...
Categories: DBA Blogs

TABLESPACE Consumption for BLOB

Tom Kyte - Sat, 2017-04-15 00:26
Hello Tom, We configured Table with BLOB Column with seperate TableSpace. <code> CREATE TABLE EV_LOG ( "ID" FLOAT(63), "MESSAGE_ID" VARCHAR2(128 BYTE), "EVENT_TYPE" VARCHAR2(128 BYTE) CONSTRAINT "SYS_C0014415" NOT NU...
Categories: DBA Blogs

DDL Trigger to mimic same changes to other tables

Tom Kyte - Sat, 2017-04-15 00:26
Hi Tom, I was trying to write a DDL trigger that will apply the same DDL to other tables when 1 particular table is bieng altered. That trigger is working fine when I add column without any NOT NULL constraint on column but gives error when try...
Categories: DBA Blogs

How to track deleted records data in a file

Tom Kyte - Sat, 2017-04-15 00:26
Hi Tom, we have some exercise to delete records in > 300 tables depend on few requirements. We are using dynamic sql delete statement iterating through tables list. Is there any chance can I track what data is deleting from back-end, I want inse...
Categories: DBA Blogs

We're All In This Thing Together

Greg Pavlik - Fri, 2017-04-14 17:26
This song pretty much summarizes everything I've learned to be true about life after nearly five decades of living...

Well my friend, well I see your face so clear
Little bit tired, a little worn through the years
You sound nervous, you seem alone
I hardly recognize your voice on the telephone

In between I remember
Just before we wound up broken down
We'd drive out to the edge of the highway
Follow that lonesome dead-end roadside sound

We're all in this thing together
Walkin' the line between faith and fear
This life don't last forever
When you cry I taste the salt in your tears

Well my friend let's put this thing together
And walk the path that worn out feet have trod
If you wanted we can go home forever
Give up your jaded ways, spell your name to God

We're all in this thing together
Walkin' the line between faith and fear
This life don't last forever
When you cry I taste the salt in your tears

All we are is a picture in a mirror
Fancy shoes to grace our feet
All that there is is a slow road to freedom
Heaven above and the devil beneath

We're all in this thing together
Walkin' the line between faith and fear
This life don't last forever
When you cry I taste the salt in your tears

Creating dashboard with SQL Server Database

Nilesh Jethwa - Fri, 2017-04-14 11:40

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications?which may run either on the same computer or on another computer across a network (including the Internet).

InfoCaptor works with SQL Server using jTDS jdbc driver. http://jtds.sourceforge.net/ 
Some more information on connection issues with SQL server http://www.infocaptor.com/dashboard/jdbc-with-sqlserver-connection-refused-connect

Read more at http://www.infocaptor.com/ice-database-connect-dashboard-to-sqlserver-sql

Automate and expedite bulk loading into Windchill.

Data migration is the least attractive part of a PDM/PLM project.  Take a look at our latest infographic to learn how to speed up bulk loading data from Creo, Autodesk Inventor and AutoCAD, SolidWorks, Documents, WTParts and more into Windchill PDMLink and Pro/INTRALINK.

More information can also be found in our previous posts:

Approaches to Consider for Your Organization’s Windchill Consolidation Project

Consider Your Options for SolidWorks to Windchill Data Migrations


The post Automate and expedite bulk loading into Windchill. appeared first on Fishbowl Solutions.

Categories: Fusion Middleware, Other

Touchpad tuning on linux: touchpad-indicator...

Dietrich Schroff - Fri, 2017-04-14 10:21
I encoutered some problems with my touchpad on ubuntu:
While typing some text, the touchpad signalled the mouse some strange moves. Sometimes it marked parts of the text and the next key erased the highlighted text...

First idea was to edit the xorg.conf or something else, but i found a less difficult was via touchpad-indicator.

It can be installed via
apt-get install touchpad-indicatorAlittle problem is, that you can not run it via cli with just calling touchpad-indicator, because ubuntu installs this program into /opt:

Here you can choose "disable touchpad on typing" and you are done...

Character selectivity

Jonathan Lewis - Fri, 2017-04-14 06:40

A recent OTN posting asked how the optimizer dealt with “like” predicates for character types quoting the DDL and a query that I had published some time ago in a presentation I had done with Kyle Hailey. I thought that I had already given a detailed answer somewhere on my blog (or even in the presentation) but found that I couldn’t track down the necessary working, so here’s a repeat of the question and a full explanation of the working.

The query is very simple, and the optimizer’s arithmetic takes an “obvious” strategy in the arithmetic. Here’s the sample query, with the equiavalent query that we can use to do the calculation:

select * from t1 where alpha_06 like 'mm%';

select * from t1 where alpha_06 >= 'mm' and alpha_06 < 'mn';

Ignoring the possible pain of the EBCDIC character set and multi-byte national-language character sets with “strange” collation orders, it should be reasonably easy to see that ‘mn’ is the first string in alphabetical order that fails to match ‘mm%’. With that thought in mind we can apply the standard arithmetic for range-based predicates assuming, to stick with the easy example, that there are no histograms involved. For a range closed at one end and and open at the other the selectivity is:

( ( 'mn' - 'mm') / (high_value - low_value) ) + 1/num_distinct

The tricky bits, of course, are how you subtract ‘mm’ from ‘mn’ and how you use the values stored in the low_value and high_value columns of view user_tab_cols. So let’s generate the orginal data set and see where we go (running on 12c, and eliminating redundant bits from the original presentation):

rem     Script:         selectivity_like_char.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Sep 2013

execute dbms_random.seed(0)

create table t1 nologging as
with generator as (
        select rownum id
        from dual
        connect by rownum <= 1000
        cast(dbms_random.string('l',6) as char(6))      alpha_06
        rownum <= 1e6 -- > comment to avoid WordPress formatting issue

execute dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1')

column low_value  format a32
column high_value format a32

        table_name = 'T1'
order by

select min(alpha_06), max(alpha_06) from t1;

set autotrace traceonly explain

        alpha_06 like 'mm%'

set autotrace off

It will probably take a couple of minutes to generate the data – it’s 1M random strings, lower-case, 6 characters fixed – and will take up about 12MB of space. Here are the results from the stats and min/max queries, with the execution plan for the query we are testing:

-------------------- ------------ ---------- -------------------------- --------------------------
ALPHA_06                  1000000    .000001 616161616E72               7A7A7A78747A

------ ------
aaaanr zzzxtz

Execution Plan
Plan hash value: 3617692013

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT  |      |   157 |  1099 |   265  (20)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   157 |  1099 |   265  (20)| 00:00:01 |

Predicate Information (identified by operation id):
   1 - filter("ALPHA_06" LIKE 'mm%')

Given that there are power(26,6) = 308,915,776 different combinations available for lower-case strings of 6 charactgers it’s not too surprising that Oracle generated 1M different strings, nor is it particularly surprising that the lowest value string started with ‘aaa’ and the highest with ‘zzz’.

So how do we get 157 as the cardinality for the query or, to put it another way, how do we get 0.000157 as the selectivity of the predicate. We need to refer to a note I wrote a few years ago to help us on our way (with a little caveat due to a change that appeared in – what number would Oracle use to represent ‘mm’ and the other three strings we need to work with ?

According to the rules supplied (and adjusted in later versions) we have to:

  1. pad the strings with ASCII nulls (zeros) up to 15 bytes
  2. treat the results as a hexadecimal number and convert to decimal
  3. round off the last 21 decimal digits

We can model this in SQL with a statement like:

SQL> column dec_value format 999,999,999,999,999,999,999,999,999,999,999,999
SQL> select round(to_number(utl_raw.cast_to_raw(rpad('aaaanr',15,chr(0))),'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'),-21) dec_val from dual;


1 row selected.

As an alternative, or possibly a cross-check, I created a table with a varchar2(6) column, inserted the four values I was interested in and created a histogram of 4 buckets on the column (there’s a suitable little demo at this URL) and got the following endpoint values:

ENDPOINT_NUMBER                                   ENDPOINT_VALUE
--------------- ------------------------------------------------
              1  505,627,904,294,763,000,000,000,000,000,000,000
              2  568,171,140,227,094,000,000,000,000,000,000,000
              3  568,191,422,636,698,000,000,000,000,000,000,000
              4  635,944,373,827,734,000,000,000,000,000,000,000

Once we’ve got these numbers we can slot them into the standard formula (not forgetting the 1/1,000,000 for the closed end of the predicate) – and to save typing I’m going to factor out 10^21 across the board in the division:

Selectivity = (568,191,422,636,698 – 568,171,140,227,094) / (635,944,373,827,734 – 505,627,904,294,763) + 1/1,000,000

Selectivity = 20,282,409,604 / 130,316,469,532,971 + 1/1,000,000

Selectivity = 0.00015564 + 0.000001 = 0.00015664

From which the cardinality = (selectivity * num_rows) = 156.64, which rounds up to 157. Q.E.D.

Plan Hash Values Changed For the Same SQL ID || How to determine why ?

Tom Kyte - Fri, 2017-04-14 06:06
Hello Team, I have a particular sql query which runs on a daily basis for around 40 mins on an average. Today it has been running since 4:50 BST and has not completed at all. I analysed what went wrong today and have detailed out what I could :...
Categories: DBA Blogs

the confusion about db_domain

Tom Kyte - Fri, 2017-04-14 06:06
Hi: Today I read the link below about the db_domain https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams052.htm#REFRN10036 it is said that Range of values of this parameter can not be null IS that right? because my datab...
Categories: DBA Blogs

RMAN - "report unrecoverable" command not listing the expected tablespace

Tom Kyte - Fri, 2017-04-14 06:06
I have added some rows to a table TAB_1 with NOLOGGING option after taking an RMAN backup. The tablespace TBS_1 is used for the table. When I tried to find the unrecoverable tablespaces using the command "REPORT UNRECOVERABLE", the expected tablespac...
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator