X windows for Oracle DBAs

By Brian Fitzgerald

Introduction

This is for DBAs on who want to use Oracle X-windows based tools such as OUI or DBCA. Techniques described here can lead to a smooth installation experience. Beyond just a cookbook, this blog article also demonstrates the principles behind X forwarding. Red Hat Linux on AWS EC2 is presented here as an example platform.

In hurry? Skip to tl;dr, at the end.

X-windows

The X windows system consists of a server, a display, and clients. The X server accepts requests for graphical output from the clients and renders those requests on the display.  In older usage, the server and the client processes usually ran on the same host, a desktop Unix system. In the present context, the X server runs on your Windows PC and displays to your screen. The X server usually listens on TCP port 6000.

In X-windows lingo, applications and utilities are called clients. In the present context, clients run on the Oracle host system, an instance of AWS EC2. Examples of clients include xterm and Oracle Universal Installer (OUI), which run on separate server hardware. Our objective, therefore, is to have  an Oracle installation tool, such as OUI, and connect back to the X server on your PC so you can view and control the graphical user interface (GUI). Paradoxically, the X server does not run on “server” hardware, but on your windows PC. This table should clear up any confusion.

Description X Server X Client
Hardware PC server
Operating system Windows Linux
Has display monitor yes no
X server port (default 6000) binds
listens
accepts connections
connects to
Software Xming xterm, dbca, etc.

The client application connects to the X server via sshd on Linux and via PuTTY on your PC.

AWS EC2

Red Hat Linux on Amazon EC2 is presented as an example platform. AWS EC2 instances are built from an available Amazon Machine Image (AMI). For an Oracle Database installation, be sure to choose an AMI with a supported processor type, such as Intel x86_64 (not ARM), and a supported operating system, such as Red Hat (not Ubuntu or Amazon Linux).

The final step in EC2 launch is to select an existing key pair or create and download a new key pair. A key pair file from AWS has extension .pem.

Linux packages

Linux package installs are required before you can run X clients. In addition, it is helpful to install basic troubleshooting tools.

After you launch a new EC2 instance, run yum update.

# yum -y update

Running yum update may get you a few new tools, such as lsipc and lsmem.

Depending on the AMI, you may find that basic Linux tools are missing. Installing those tools makes troubleshooting easier. For example:

# yum -y install nc lsof unzip strace traceroute

You want to set up a working X windows environment, but before you do, notice three things from the login shell:

[ec2-user@ip-172-31-89-75 ~]$ echo $DISPLAY

[ec2-user@ip-172-31-89-75 ~]$ netstat -ntl | grep :60..
  1. The DISPLAY environment variable is not set
  2. There is no listener in the 6000 range
  3. No file .Xauthority is present

Now install the X windows packages.

# yum -y install motif xorg-x11-utils xterm xauth

xterm here is for testing X windows.

In /etc/ssh/sshd_config, check:

X11Forwarding yes
X11UseLocalhost no

If necessary, edit /etc/ssh/sshd_config and then issue:

service sshd restart

After installing xauth, login using a new PuTTY session. On the first login, the following output will appear:

/usr/bin/xauth: file /home/ec2-user/.Xauthority does not exist

Now recheck:

[ec2-user@ip-172-31-89-75 ~]$ echo $DISPLAY
localhost:10.0
[ec2-user@ip-172-31-89-75 ~]$ netstat -ntl | grep :60..
tcp        0      0 127.0.0.1:6010          0.0.0.0:*               LISTEN
tcp6       0      0 ::1:6010                :::*                    LISTEN
  1. The DISPLAY environment variable is set
  2. There is a new listener in the 6000 range.
  3. A new .Xauthority file exists

The “10” in DISPLAY refers to the offset from 6000. When you set your DISPLAY to localhost:10.0, all X clients will connect to port 6010 by default.

Notice which process is listening;

[root@ip-172-31-88-44 ~]# lsof -Pi :6010
COMMAND  PID     USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
sshd    1351 ec2-user    9u  IPv6  20649      0t0  TCP localhost:6010 (LISTEN)
sshd    1351 ec2-user   10u  IPv4  20650      0t0  TCP localhost:6010 (LISTEN)

The sshd process is listening on port 6010. You are going to run X-client application on the Linux box. The client will connect to port 6010. The sshd process will forward the X-requests to your PC.

X server

There are several MS Windows-based X-servers available, Xming, for example. By default, the X server listens on port 6000. Confirmation:

C:\>netstat -na | findstr :6000 | findstr LISTEN
TCP 0.0.0.0:6000 0.0.0.0:0 LISTENING

X server issues

Can’t load font

A message such as this appears

xterm: cannot load font -misc-fixed-medium-r-semicondensed--13-120-75-75-c-60-iso10646-1

Solution:

If you are using Xming, then install Xming fonts. For example: Xming-fonts-7-7-0-10-setup.exe

xdpyinfo

Behind the scenes, OUI runs xdpyinfo. If xdpyinfo is missing, this message will appear:

ERROR: Unable to verify the graphical display setup. This application requires X display. Make sure that xdpyinfo exist under PATH variable.

To get xdpyinfo, install package xorg-x11-utils. An example of normal OUI output is:

Checking monitor: must be configured to display at least 256 colors.   Actual 16777216    Passed

Warning: No xauth data; using fake authentication data for X11 forwarding.

Symptom:

You run ssh -X and observe a warning message such as:

brian@PQHKEA * xauth $ ssh -X -i ~/.ssh/acme-key.pem ec2-user@3.84.232.118
Warning: No xauth data; using fake authentication data for X11 forwarding.
Last login: Sat Nov 7 18:31:30 2020 from pool-98-113-87-97.nycmny.fios.verizon.net

The message is from the local ssh client, not the remote host. You can confirm this by referring to package https://github.com/openssh/openssh-portable, file clientloop.c.

Fix:

xauth add :${DISPLAY#*:} . `mcookie`

Explanation.

:${DISPLAY#*:} gives you the DISPLAY value without the IP address.

mcookie a 128-bit random hexadecimal number for use with the X authority system

xterm issue

message:

Warning: locale not supported by C library, locale unchanged

Solution:

export LC_ALL=C

X libraries

The manual may instruct you to install certain X windows runtime libraries, or OUI may so instruct you. The library list depends on the Oracle product and the version. Example:

yum -y install libX11 libXau libXi libXtst libXrender

If you have installed xterm, then you already have these dependent libraries.

mwm

mwm is the Motif Window Manager. It lets you move and resize windows. Without a window manager, you may encounter situations where you have a dialog box waiting for input but is inaccessible because it is behind another window. Here is the X root window after dragging three windows to non-overlapping locations.

mwm

The shell commands leading up to this were:

xterm & disown
mwm & disown
unzip -q ~/download/LINUX.X64_193000_db_home.zip
./runInstaller

Network

The X windows display technique described here assumes that you can connect via the secure shell (ssh) from your PC directly to the Oracle server host. The AWS EC2 instance has a Public IP address that is reachable from your PC.

The X windows protocol is a very “chatty” protocol, meaning that clients make a high number of small server requests. Chattiness makes every button press on the GUI respond very slowly. Having a fast network connection is essential. One might give some thought to enabling SSH compression; however, SSH compression does not mitigate the chattiness.

putty.compression

Enabling SSH compression will usually not result in a noticeable improvement in responsiveness.

Running an X windows client across sudo

In a prior section, xterm worked fine from the login shell (ec2-user). In practice, you will sudo to the oracle account to execute runInstaller or dbca. You may also sudo to the grid account to run gridSetup.sh. First, test xterm from the login account:

[ec2-user@ip-172-31-82-194 ~]$ echo $DISPLAY
localhost:11.0
[ec2-user@ip-172-31-82-194 ~]$ xterm
(it works)

One thing you will notice here in this case is that the offset in the DISPLAY variable is 11. The offset is not always 10, and depends on what port is available for X forwarding when you log in.

[ec2-user@ip-172-31-82-194 ~]$ netstat -ntl | grep :60..
tcp        0      0 127.0.0.1:6010          0.0.0.0:*               LISTEN
tcp        0      0 127.0.0.1:6011          0.0.0.0:*               LISTEN
tcp6       0      0 ::1:6010                :::*                    LISTEN
tcp6       0      0 ::1:6011                :::*                    LISTEN

In this example, port 6010 was is in use when I logged in, so sshd started listening on port 6011 and set offset to 11.

If you try to run an X client across a sudo, you will get this error:

[ec2-user@ip-172-31-82-194 ~]$ sudo -i -u oracle
[oracle@ip-172-31-82-194 ~]$ xterm
xterm: Xt error: Can't open display:
xterm: DISPLAY is not set
[oracle@ip-172-31-82-194 ~]$ echo $DISPLAY

The DISPLAY environment setting did not carry over to the sudo shell. You can try setting DISPLAY, but you will get a different error:

[oracle@ip-172-31-82-194 ~]$ export DISPLAY=localhost:11.0
[oracle@ip-172-31-82-194 ~]$ xterm
PuTTY X11 proxy: wrong authorisation protocol attemptedError: Can't open display: localhost:11.0

To fix the authorization error, copy .Xauthority from the login account to the oracle account:

[ec2-user@ip-172-31-82-194 ~]$ sudo cp -up ~/.Xauthority ~oracle/.Xauthority
[ec2-user@ip-172-31-82-194 ~]$ sudo chown oracle:` id -gn oracle ` ~oracle/.Xauthority
[ec2-user@ip-172-31-82-194 ~]$ sudo -i -u oracle
[oracle@ip-172-31-82-194 ~]$ xterm

To automate the copying, I added this code to the login user .bash_profile:

if [ -z "$SUDO_USER" -a -f ~/.Xauthority ] ; then
    for u in oracle grid ; do
        if id $u > /dev/null 2>&1 ; then
            eval hd=~$u
            sudo cp -up ~/.Xauthority $hd/.Xauthority
            sudo chown $u:$(id -gn $u) $hd/.Xauthority
        fi
    done
fi

To automate setting DISPLAY, I added this code to the oracle .bash_profile:

if [ -z "$DISPLAY" ] ; then
    for port in {6010..6019} ; do
        if netstat -ntl | grep -q :$port ; then
            offset=$(( port - 6000 ))
            export DISPLAY=localhost:$offset.0
            break
        fi
    done
fi

To summarize, before sudo, copy .Xauthority from the login account to oracle. After sudo, set the DISPLAY environment variable. Automate those settings in .bash_profile.

puttygen

To connect PuTTY from Windows to AWS EC2 you need to generate a Putty Private Key (PPK) file. Start with the PEM (Privacy Enhanced Mail) file that you downloaded when you created your EC2 instance. Startup PuTTY Key Generator (puttygen). Load the PEM file. Click Save private key. Provide a name. Example:

us-east-1-key.pem
us-east-1-key.ppk

puttygen

X forwarding with PuTTY

To establish a session with PuTTY, navigate the PuTTY screens and make these settings.

Screen Item Example
Session Host Name (or IP address) 3.86.243.42
Connection->Data Auto-login username ec2-user
Connection->SSH->Auth Private key file for authorization C:\Users\Brian Fitzgerald\.aws\us-east-1-key.ppk
Connection->SSH->X11 Enable X11 forwarding checked
Connection->SSH->X11 X display location localhost:0.0

putty.host

You may save your settings for future reuse.

putty.user

ppk.png

The private key file is the ppk file that you converted from the pem file.

putty.x11

X display location refers to the X server on your PC, and is usually localhost:0.0.

A simple command line example

Instead of using PuTTy, you could use ssh. This example uses bash on Windows Subsystem for Linux. The hypothetical hosts are lcl and rem.

Generate your key

lcl $ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/home/bf/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/bf/.ssh/id_rsa.
Your public key has been saved in /home/bf/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:Jb9cfmEZI8r1FNe611gM0dBLYInq+/CXs35NPpSlB88 bf@lcl
The key's randomart image is:
+---[RSA 2048]----+
|            .o*=o|
|           ....++|
|        . o o =+.|
|         * o +o=+|
|        S + . =O+|
|         o + .++E|
|         .+ . +=.|
|         .o  = oo|
|          .ooo+ .|
+----[SHA256]-----+

Copy your key to remote

lcl $ ssh-copy-id rem
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/home/bf/.ssh/id_rsa.pub"
The authenticity of host 'rem (10.130.33.179)' can't be established.
ECDSA key fingerprint is SHA256:Gr8YlEJrgTQdsNMGXOZoU+6ugg4TPyX5B9lCLTE0g/8.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys


bf@rem's password:

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'rem'"
and check to make sure that only the key(s) you wanted were added.

Set your DISPLAY

Startup Xming, then run:

lcl $ export DISPLAY=127.0.0.1:0.0

ssh using the -X flag

lcl $ ssh -X rem
/usr/bin/xauth:  file /home/bf/.Xauthority does not exist
rem $

test

rem $ xdpyinfo | wc
    287    1136    9544

permit your .Xauthority file

rem $ chmod 750 ~
rem $ chmod 640 ~/.Xauthority

sudo to oracle

rem $ sudo su - oracle
[sudo] password for bf:

copy .Xauthority

oracle@rem $ cp -p ~bf/.Xauthority .Xauthority

set your DISPLAY

oracle@rem $ export DISPLAY=10.130.33.179:10.0

test

oracle@rem $ xdpyinfo | wc
    287    1136    9544

Oracle Universal Installer

oracle@rem $ cd /u01/download/oracle/em13400/
oracle@rem $ ./em13400_linux64.bin -J-Djava.io.tmpdir=/u01/tmp

oui.em

Notes

Display

rem $ echo $DISPLAY
10.130.33.179:10.0

Notice :10.0, not :0.0

Ports

rem $ netstat -nat | grep :60
tcp        0      0 0.0.0.0:6010            0.0.0.0:*               LISTEN
tcp6       0      0 :::6010                 :::*

Notice 6010, not 6000

File

rem $ ls -l .Xauthority
-rw-------. 1 bf dba 100 Jul  6 17:41 .Xauthority

X forwarding via bastion host

You can use ssh from the Windows command line to setup X forwarding via a tunnel at the bastion. Identify the following items:

item example
bastion key file us-east-1-key.pem
arbitrary Windows local port 3333
oracle box private IP address 172.31.95.14
ssh port 22
bastion host user brian
bastion public IP address 52.90.61.241

and use them as follows:

ssh -i keyfile -C -N -L localport:oraclebox:22 bastionuser@bastionhost

For example:

C:\>ssh -i us-east-1-key.pem -N -L 3333:172.31.95.14:22 ec2-user@52.90.61.241
  • The bastion key file is the pem file that you downloaded when you created the EC2 instance
  • The local port is a free port on your PC that you will choose arbitrarily.
  • The oracle box is the private IP address or the oracle box that you want to ssh to.
  • Bastion user is your login on the bastion host, i.e. ec2-user.
  • bastionhost is the public IP address of your bastion host.

Next, in a second cmd window:

set DISPLAY=localhost:0.0
ssh -i us-east-1-key.pem -Y -p 3333 ec2-user@localhost
Warning: No xauth data; using fake authentication data for X11 forwarding.
Last login: Tue Nov 19 02:50:02 2019 from ip-172-31-88-110.ec2.internal
Last login: Tue Nov 19 02:50:02 2019 from ip-172-31-88-110.ec2.internal
ec2-user@ip-172-31-95-14 ~$ xterm

The requirements for the bastion host are different from the oracle box.

  • Establishing ssh trust from the bastion to the oracle box is not required.
  • The tunneling session is not responsible for X forwarding. Option -X is not required.
  • Disabling X11Forwarding at the bastion does not disable X forwarding as described here because the X11 forwarding happens at the Oracle host and in PuTTY.
  • The bastion login user does not have to be the same as the oracle box login user.
  • The bastion host does not have to be the same hardware or operating system architecture as the oracle box. Amazon Linux works.
  • In some bastion EC2 systems, no configuration is required. ssh tunneling works out of the box.
  • If the bastion /etc/ssh/sshd_config has “AllowTcpForwarding no”, then ssh tunneling is disabled.

You do not need to log on to the bastion with an interactive shell. After the tunneling session is established, you will not interact with it.

X windows without X11 forwarding

If you have a connection from Linux to the X11 port, usually 6000, on your PC, then you can attempt X windows without X11 forwarding. You are going to set DISPLAY=pc.dnsname.or.ipaddress:6000

Verify port 6000 with:

C:\>netstat -na | findstr LISTEN | findstr :6000
TCP 0.0.0.0:6000 0.0.0.0:0 LISTENING

“0.0.0.0:6000” refers to listening on port 6000 on all IP addresses on all network interfaces. If you see

 TCP 127.0.0.1:6000 0.0.0.0:0 LISTENING

then your X server will not accept connections from outside your PC.

You can find your IP address with:

C:\>ipconfig | findstr Address
Link-local IPv6 Address . . . . . : fe80::f9f0:5830:cd8e:7502%24
IPv4 Address. . . . . . . . . . . : 192.168.1.7
Link-local IPv6 Address . . . . . : fe80::85d7:7339:ff49:e5e6%51
IPv4 Address. . . . . . . . . . . : 192.168.74.113

The addresses shown in this example are private IP addresses. If the oracle box and the PC are not on the same private network, you cannot refer to this address.

To get the NATted IP address you can use netstat:

[ec2-user@ip-172-31-86-22 ~]$ netstat -nat | grep :22 | grep ESTABLISHED
tcp 0 64 172.31.86.22:22 74.101.107.146:49459 ESTABLISHED
tcp 0 0 172.31.86.22:22 74.101.107.146:54036 ESTABLISHED

or last:

[ec2-user@ip-172-31-86-22 ~]$ last -1ai ec2-user
ec2-user pts/1 Tue Aug 27 09:05 still logged in 74.101.107.146

The PC IP address is, therefore, 74.101.107.146. Instead of the IP address, you may refer to DNS name. The DNS name appears when you login.

Using username "ec2-user".
Authenticating with public key "imported-openssh-key"
Last login: Mon Aug 26 15:57:51 2019 from pool-74-101-107-146.nycmny.fios.verizon.net

or from last:

[ec2-user@ip-172-31-86-22 ~]$ last -1a ec2-user
ec2-user pts/1        Tue Aug 27 09:05   still logged in    pool-74-101-107-146.nycmny.fios.verizon.net

Before attempting runInstaller, test connectivity using nc

[ec2-user@ip-172-31-86-22 ~]$ time nc -v 74.101.107.146 6000
Ncat: Version 7.50 ( https://nmap.org/ncat )
Ncat: Connection timed out.

real    0m10.029s
user    0m0.012s
sys     0m0.004s

By default, nc times out in 10 seconds.

If the nc connection times out, then an X-windows client will also time out.

[ec2-user@ip-172-31-86-22 ~]$ export DISPLAY=74.101.107.146:0.0
[ec2-user@ip-172-31-86-22 ~]$ time xterm
xterm: Xt error: Can't open display: 74.101.107.146:0.0

real    2m7.366s
user    0m0.021s
sys     0m0.016s

The TCP timeout depends on tcp_syn_retries

[root@ip-172-31-86-22 ~]# cat /proc/sys/net/ipv4/tcp_syn_retries
6

If tcp_syn_retries = 6, then the TCP timeout works out to 127 seconds, calculated thus:

tcp.timeout

You can monitor the connection in a separate window.

[ec2-user@ip-172-31-86-22 ~]$ netstat -nat | grep :6000
tcp        0      1 172.31.86.22:36310      74.101.107.146:6000     SYN_SENT

SYN_SENT lasting for a long time means that the connection is about to time out. runInstaller will take more than twice as long as xterm does

to time out.

[oracle@ip-172-31-86-22 dbhome_1]$ export DISPLAY=74.101.107.146:0.0
[oracle@ip-172-31-86-22 dbhome_1]$ time ./runInstaller
ERROR: Unable to verify the graphical display setup. This application requires X display. Make sure that xdpyinfo exist under PATH variable.

Can't connect to X11 window server using '74.101.107.146:0.0' as the value of the DISPLAY variable.

real    4m17.524s
user    0m2.835s
sys     0m0.303s

First, xdpyinfo times out:

[ec2-user@ip-172-31-86-22 ~]$ ps -t pts/1
  PID TTY          TIME CMD
 3324 pts/1    00:00:00 bash
11279 pts/1    00:00:00 sudo
11281 pts/1    00:00:00 bash
11311 pts/1    00:00:00 runInstaller
11315 pts/1    00:00:00 xdpyinfo

Then OUI (java) times out:

[ec2-user@ip-172-31-86-22 ~]$ ps -t pts/1
  PID TTY          TIME CMD
 3324 pts/1    00:00:00 bash
11279 pts/1    00:00:00 sudo
11281 pts/1    00:00:00 bash
11311 pts/1    00:00:00 runInstaller
11436 pts/1    00:00:00 perl
11455 pts/1    00:00:01 java

To save time, test your connection using nc, before attempting runInstaller.

dbca issues

dbca NoClassDefFoundError: sun.awt.X11.XToolkit

The DISPLAY environment variable is set, but this messsage appears:

[oracle@ip-172-31-88-246 ~]$ dbca
Exception in thread "main" java.lang.NoClassDefFoundError: Could not initialize class sun.awt.X11.XToolkit
        at java.lang.Class.forName0(Native Method)
        at java.lang.Class.forName(Class.java:264)
        at java.awt.Toolkit$2.run(Toolkit.java:860)
        at java.awt.Toolkit$2.run(Toolkit.java:855)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.awt.Toolkit.getDefaultToolkit(Toolkit.java:854)
        at com.jgoodies.looks.LookUtils.isLowResolution(LookUtils.java:484)
        at com.jgoodies.looks.LookUtils.(LookUtils.java:249)
        at com.jgoodies.looks.plastic.PlasticLookAndFeel.(PlasticLookAndFeel.java:135)
        at java.lang.Class.forName0(Native Method)
        at java.lang.Class.forName(Class.java:348)
        at javax.swing.SwingUtilities.loadSystemClass(SwingUtilities.java:1874)
        at javax.swing.UIManager.setLookAndFeel(UIManager.java:582)
        at oracle.install.commons.util.Application.startup(Application.java:940)
        at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:181)
        at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:198)
        at oracle.assistants.common.base.driver.AssistantApplication.startup(AssistantApplication.java:328)
        at oracle.assistants.dbca.driver.DBConfigurator.startup(DBConfigurator.java:378)
        at oracle.assistants.dbca.driver.DBConfigurator.main(DBConfigurator.java:513)

Cause: The X server is down or not reachable. Troubleshoot as you would an xterm issue, such as:

[oracle@ip-172-31-88-246 ~]$ xterm
xterm: Xt error: Can't open display: localhost:10.0

Client startup step by step

If you follow these steps one at a time and resolve issues as you go, it is less likely that you will encounter difficulty when you try to run an Oracle tool

  • In PuTTY, configure X11 forwarding.
  • Connect PuTTY and log in.
  • Check the DISPLAY environment variable.
  • Test xterm.
  • Test xdpyinfo.
xdpyinfo > /dev/null
  • copy .Xauthority to the oracle account.
  • sudo to oracle.
  • Set the DISPLAY environment variable.
  • Test xterm again.
  • startup mwm.
  • finally, start your Oracle tool. Ex:
./runInstaller

Summary

It is extremely helpful to configure a proper Linux and X windows environment before attempting to run an Oracle tool such as OUI. Commands nc, lsof, strace, traceroute, lsipc, and lsmem were used while writing this blog article. From xorg-x11-utils, xlsclients and xev were used. From motif, mwm was used. xauth is required for X windows. An oracle installation may require unzip and xdpyinfo. Not all needed utilities installed by default. You must install them yourself.

If you Google “x windows forwarding not working” you will find cases where one person’s advice works for one person and not another. Trying random tips and tricks without understanding can lead to your own solutions that don’t work all the time, and enabling unnecessary options. To improve understanding, I have added diagnostic output from netstat and lsof.

Be sure to understand that the X server runs on your PC and listens on port 6000 by default. On the oracle box, sshd listens on port 6000 + an offset, often 10. Setting DISPLAY=localhost:10.0 will lead X clients to connect to the sshd listening on port 6010 on the local host. sshd will forward the X traffic back to the X server on your PC.

It is well to break up the setup and testing into multiple, simple steps, rather than attempting to startup runInstaller in one go.

tl;dr

  • Install needed packages
  • Copy .Xauthority to the oracle account
  • Set the DISPLAY environment variable

 

Notes on histograms

By Brian Fitzgerald

This blog post is a worked example of Oracle histograms. In this case, a frequency histogram is demonstrated in the context of a single table query.

Setup

cr.histodemo.sql

Table ords. Intended column data distribution:

10000 rows
10000 unique ordid
1000 categories, evenly distributed
2 distinct status, skewed
99.99% COMPLETE
0.01% PENDING

One index on each column, ORDID, CATEGORYID, and STATUS.

Not null constraints are there to simplify the demonstration.

whenever oserror exit 1
@ sqlerc.sql
drop table ords purge;
@ sqlerx.sql

create table ords
(
ordid number not null,
categoryid number not null,
status varchar2(10) not null
);

insert into ords ( ordid, categoryid, status )
select
level,
mod( level, 1000 ),
case mod( level, 10000) when 0 then 'PENDING' else 'COMPLETE' end
from dual
connect by level <= 10000;

alter table ords add constraint ords primary key ( ordid );
create index category_idx on ords ( categoryid );
create index status_idx on ords ( status );

quit

output:

[oracle@stormking db12201 stats]$ sqlplus u/u @ cr.histodemo.sql
SQL*Plus: Release 12.2.0.1.0 Production on Sun Feb 11 11:37:31 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Sun Feb 11 2018 00:10:42 -05:00
Connected to:
Oracle Database 12c Enterprise Edition 
Release 12.2.0.1.0 - 64bit Production
Table dropped.
Table created.
10000 rows created.
Table altered.
Index created.
Index created.
Disconnected from Oracle Database 12c Enterprise Edition 
Release 12.2.0.1.0 - 64bit Production

Execution scripts

query.ords.sql

Thwo queries on table ords.

  1. equality predicate on STATUS only
  2. equality predicate on STATUS and CATEGORYID

The 10053 trace shows optimizer’s rationale.

set linesize 100
@ sqlerx.sql
column plan_table_output format a80

@ set.tracefile.identifier.sql status
@ trace.10053.on.sql

select ordid from ords
where status = 'PENDING'
;
select * from table(dbms_xplan.display_cursor( format=>'basic' ));
@ trace.10053.off.sql

@ set.tracefile.identifier.sql cat
@ trace.10053.on.sql

select ordid from ords
where categoryid = 0
and status = 'COMPLETE'
;
select * from table(dbms_xplan.display_cursor( format=>'basic' ));
@ trace.10053.off.sql

rpt.col.usage.sql

Script to show what types of workload has run on the columns. Database monitoring is flushed for the sake of displaying the col_usage report before gathering stats in this demonstration. Gather stats automatically flushes column usagem so alling flush_database_monitoring_info is generally not required.

define ownname=&&1
define tabname=&&2
whenever oserror exit 1
@ sqlerx.sql
@ l32k.p50k.sql

set verify off

set long 2000000000
exec dbms_stats.flush_database_monitoring_info;
column rpt format a160
select dbms_stats.report_col_usage('&&ownname','&&tabname') rpt from dual;

gather.table.stats.sql

Invalidation, in this case, is to force a 10053 trace for the sake of this demonstration. The 10046 trace and tkprof is for checking what queries on ords get run during gather stats. With the exception of no_invalidate, dbms_stats is run with the default options to show how histograms are gathered automatically during routine database maintenance. Estimate_percent defaults to auto_sample_size. Gathering or deleting a histogram can be forced by using option method_opt.

define ownname=&&1
define tabname=&&2
whenever oserror exit 1
@ sqlerx.sql
set verify off

@ set.tracefile.identifier.sql stats
@ trace.10046.on.sql

begin
 dbms_stats.gather_table_stats (
 ownname => '&&ownname',
 tabname => '&&tabname',
 no_invalidate => false
 );
end;
/
prompt done gather stats on &&ownname..&&tabname

@ trace.10046.off.sql
@ tkprof.sql

user.tab.statistics.sql

Script to show the existence or nonexistence of a histogram

define tabname=&&1
whenever oserror exit 1
@ sqlerx.sql
@ l32k.p50k.sql
@ columnformat.sql

select
table_name,
blocks,
num_rows
from user_tab_statistics
where
table_name = '&&tabname';

select
index_name,
leaf_blocks,
num_rows,
distinct_keys
from user_ind_statistics
where table_name = '&&tabname';

select
column_name,
num_distinct,
density,
histogram,
num_buckets
from user_tab_col_statistics
where table_name = '&&tabname';

run.histodemo.sql

The demonstration script

whenever oserror exit 1
@ spoolfile.uniq.sql ords

@ rpt.col.usage.sql U ORDS
@ gather.table.stats.sql U ORDS
@ user.tab.statistics.sql ORDS
@ query.ords.sql

@ spooloff.sql
quit

Execution run 1

Files

ords.db12201.20180211.142321.84.txt

run.histodemo.sql output

db12201_ora_20225_STATS.trc

10043 trace of gather table stats

db12201_ora_20225_STATS.tkp

tkprof of gather table stats

db12201_ora_20225_STATUS.trc

10053 trace of “where status = ‘PENDING'”

db12201_ora_20225_CAT.trc

10053 trace of “where categoryid = 0 and status = ‘COMPLETE'”

Highlights

  • Because table ords is newly created, the column usage report shows no workload
  • Gather stats ran four queries on table ords, namely a full table scan and one per index.
$ grep -i '"U"."ORDS" t' db12201_ora_20225_STATS.tkp
 "U"."ORDS" t /* ACL,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL*/
 "U"."ORDS" t where "ORDID" is not null
 "U"."ORDS" t where "CATEGORYID" is not null
 "U"."ORDS" t where "STATUS" is not null
  • No histogram was gathered
  • ORDID is most selective column, having the highest num_distinct and lowest density
  • CATEGORYID is medium-selective
  • STATUS is not, in general, selective, having only 2 distinct values
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM NUM_BUCKETS
------------------------------ ------------ ---------- -------------------
ORDID 10000 .0001 NONE 1
CATEGORYID 1000 .001 NONE 1
STATUS 2 .5 NONE 1
  • No histogram is present on status. Density is computed:

density = 1 / num_distinct = 1/2 = .5

  • “where status = ‘PENDING'” catches a full table scan
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| ORDS |
----------------------------------
  • “where categoryid = 0 and status = ‘COMPLETE'” catches an index range scan
------------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| ORDS |
| 2 | INDEX RANGE SCAN | CATEGORY_IDX |
-----------------------------------------------------------

Execution run 2

Files

ords.db12201.20180211.142525.84.txt

run.histodemo.sql output for run 2.

db12201_ora_20607_STATS.trc

db12201_ora_20607_STATS.tkp

Gather stats 10046 trace and tkprof.

db12201_ora_20607_STATUS.trc

db12201_ora_20607_CAT.trc

10053 trace again for the queries, “where status” and “where categoryid …
and status”.

Highlights

  • The column usage report shows that equality predicates have run on columns CATEGORYID and STATUS.
COLUMN USAGE REPORT FOR U.ORDS
..............................

1. CATEGORYID : EQ
2. STATUS : EQ
  • This time, gather stats ran six queries on table ords.
$ grep -i '"U"."ORDS" t' db12201_ora_20607_STATS.tkp | wc -l
6
  • gather table stats ran this query on column STATUS:
select 
/*+ no_parallel(t) no_parallel_index(t) dbms_stats 
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) 
no_monitoring xmlindex_sel_idx_tbl 
opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad 
*/
 substrb(dump("STATUS",16,0,64),1,240) val,
 rowidtochar(rowid) rwid 
from "U"."ORDS" t 
where rowid in (
chartorowid('AAAWt1AAHAAAAFjAAA'),
chartorowid('AAAWt1AAHAAAAGCAAv')) 
order by "STATUS"

Clearly, the only purpose is to obtain the two unique STATUS values.

  • Gather stats ran this query on column CATEGORYID:
select 
substrb(dump(val,16,0,64),1,240) ep, 
freq, 
cdn, 
ndv,
(sum(pop) over()) popcnt,
(sum(pop*freq) over()) popfreq,
substrb(dump(max(val) over(),16,0,64),1,240) maxval,
substrb(dump(min(val) over(),16,0,64),1,240) minval
from
 (
select val, 
freq, 
(sum(freq) over()) cdn, 
(count(*) over()) ndv,
(case when freq > ((sum(freq) over())/254) then 1 else 0 end) pop
from (select
  /*+ no_parallel(t) no_parallel_index(t) dbms_stats
    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) 
    no_monitoring xmlindex_sel_idx_tbl 
    opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad
 */
  "CATEGORYID" val, 
  count("CATEGORYID") freq 
from "U"."ORDS" t 
where "CATEGORYID" is not null 
group by "CATEGORYID"
)) order by val

Inference: dbms_stats is collecting information needed to construct a histogram on STATUS and CATEGORYID.

Notice the column name “ep”, which could refer to a histogram endpoint value.

“pop” = 1 if popular, 0 if non-popular.

  • dbms_stats stored a histogram for column status but not for categoryid
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM NUM_BUCKETS
------------------------------ ------------ ---------- ------- -----------
ORDID 10000 .0001 NONE 1
CATEGORYID 1000 .001 NONE 1
STATUS 2 .00005 FREQUENCY 2

Inference: dbms_stats found skew in status but not in categoryid

  • The reported density on column status has changed:
82c84
< STATUS 2 .5 NONE 1
---
> STATUS 2 .00005 FREQUENCY 2

According to 2007 note New Density calculation in 11g by Alberto Dell’Era, density = 0.5 / num_rows for frequency histograms. Dell’Era’s paper refers only to not null values. In other words, for frequency histograms, density, as stored in the catalog, should be

density = .5 / num_values = .5 / ( num_rows – num_nulls )

I have found this to be accurate to within 2.5e-15 for 99% out of the 30,000 frequency histograms in one example database, where the histograms were mostly gathered with Oracle 11.1.0.7 binaries. After gathering statistics with Oracle 12.1.0.2 binaries, dba_tab_col_statistics density is found to agree with formula .5 / ( num_rows – num_nulls ) to within 2.5e-15 for 99.95% of all frequency histograms.This formula is found to apply only for the cases where num_distinct >1. For the remaining 0.05%, the density is within 2% of the formula. All the discrepant densities are IOTs; however, not all IOTs are discrepant, so I must be missing some detail.

In this example, num_values = 10000 – 0 = 10000. Density = .5 / 10000 = 0.00005.

The endpoint values are, roughly, using cre_hist_funcs.sql, function hist_numtochar, by Martin Widlake:

ENDPOINT_NUMBER VAL
--------------- ----------
 9999 COMPLEP
 10000 PENDIN<

Comment: density is used to cost predicates on non-popular values.

“where status = ‘COMPLETE'” remains non-selective.

“where status =’PENDING'” is selective.

Compare run without histogram vs run with histogram

where status =’PENDING’

diff run1/db12201_ora_20225_STATUS.trc run2/db12201_ora_20607_STATUS.trc

Output here: 10053 diff: where status = ‘PENDING’

There several points of interest in the 10053 trace comparison

  • With the histogram, the selectivity of status=’PENDING’ is 1.0000e-4, which is exactly correct.
875,876c877,879
< AvgLen: 9 NDV: 2 Nulls: 0 Density: 0.500000
< Estimated selectivity: 0.500000 , col: #3
---
> AvgLen: 9 NDV: 2 Nulls: 0 Density: 0.000050
> Histogram: Freq #Bkts: 2 UncompBkts: 10000 EndPtVals: 2 ActualVal: no
> Estimated selectivity: 1.0000e-04 , endpoint value predicate, col: #3
  • The plan changes thus:
1016,1021c1028,1034
< -------------------------------------+-----------------------------------+
< | Id | Operation | Name | Rows | Bytes | Cost | Time |
< -------------------------------------+-----------------------------------+
< | 0 | SELECT STATEMENT | | | | 11 | |
< | 1 | TABLE ACCESS FULL | ORDS | 5000 | 63K | 11 | 00:00:01 |
< -------------------------------------+-----------------------------------+
---
> ---------------------------------------------------------+-----------------------------------+
> | Id | Operation | Name | Rows | Bytes | Cost | Time |
> ---------------------------------------------------------+-----------------------------------+
> | 0 | SELECT STATEMENT | | | | 2 | |
> | 1 | TABLE ACCESS BY INDEX ROWID BATCHED | ORDS | 1 | 13 | 2 | 00:00:01 |
> | 2 | INDEX RANGE SCAN | STATUS_IDX| 1 | | 1 | 00:00:01 |
> ---------------------------------------------------------+-----------------------------------+

where categoryid = 0 and status = ‘COMPLETE’

diff run1/db12201_ora_20225_CAT.trc run2/db12201_ora_20607_CAT.trc

output here: 10053 diff: where categoryid and status

  • With a histogram, the estimated cost of using an index to access a popular value is higher. All the more reason not to use STATUS_IDX
 917 Access Path: index (AllEqRange)
 918 Index: STATUS_IDX
919,921c922,924
< resc_io: 30.000000 resc_cpu: 2414493
< ix_sel: 0.500000 ix_sel_with_filters: 0.500000
< Cost: 30.064126 Resp: 30.064126 Degree: 1
---
> resc_io: 60.000000 resc_cpu: 4827696
> ix_sel: 0.999900 ix_sel_with_filters: 0.999900
> Cost: 60.128217 Resp: 60.128217 Degree: 1
  • The cost of CATEGORY_IDX is unchanged.
 904 ****** Costing Index CATEGORY_IDX
 905 SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
 906 SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
 907 Estimated selectivity: 0.001000 , col: #2
 908 Access Path: index (AllEqRange)
 909 Index: CATEGORY_IDX
 910 resc_io: 11.000000 resc_cpu: 83586
 911 ix_sel: 0.001000 ix_sel_with_filters: 0.001000
 912 Cost: 11.002220 Resp: 11.002220 Degree: 1
  • CATEGORY_IDX is more selective and less costly than STATUS_IDX. The optimizer chooses CATEGORY_IDX.

Need for histogram in an application

The need for a histogram depends on the application. In this example, no histogram is needed on columns ORDID and CATEGORYID because the values are evenly distributed. There is no skew.

If the application code base consists of only this query, then no histogram is needed on STATUS:

select ordid from ords
where categoryid = 0
and status = 'COMPLETE'

If the application, additionally, has this query, then a histogram is needed on STATUS.

select ordid from ords
where status = 'PENDING'

What about “where status = ‘COMPLETE'”?

The question sometimes arises, what about a query with just “where status = ‘COMPLETE'”. Such a query would access a high number of blocks and return a high number of rows, and would be quickly identified as a poor performer. It is not clear what business value would be served by such a query. Such queries appear less commonly in practical applications.

What about two plans for the same statement?

Histograms are sometimes touted for their usefulness in finding more than one plan for the same sql. This blog post does not identify any such examples.

The hypothetical situation we are describing is:

  • The SQL is the same.
  • The plans are different.
  • In one case, the predicate is on a popular value and in the other case, a rare value.
  • In the popular case, the query may return a high number of rows, or a plan step may return a high number of rows to its antecedent.
  • In the non-popular case, the query accesses few blocks and returns quickly.
  • The popular and non-popular cases have business value.
  • The slower response time of the popular case is acceptable.

Findings

  • Column usage in predicates, as recorded in col_usage$, leads to analyzing for, but not necessarily storage of, histograms.
  • 10053 trace files show the optimizer’s rationale for choosing its final execution plan.
  • In some cases, a histogram helps the optimizer choose a less costly plan.
  • In some cases, a histogram improves the accuracy of a cost computation, but does not affect the final choice of plan.

10053 diff: group by status

[oracle@stormking db12201 12.2]$ diff run1/db12201_ora_20225_GBY.trc run2/db12201_ora_20607_GBY.trc
1c1
< Trace file /u01/app/oracle/diag/rdbms/db12201/db12201/trace/db12201_ora_20225_GBY.trc
---
> Trace file /u01/app/oracle/diag/rdbms/db12201/db12201/trace/db12201_ora_20607_GBY.trc
12,13c12,13
< Oracle process number: 41
< Unix process pid: 20225, image: oracle@stormking (TNS V1-V3)
---
> Oracle process number: 40
> Unix process pid: 20607, image: oracle@stormking (TNS V1-V3)
16,22c16,22
< *** 2018-02-11T14:23:23.367313-05:00
< *** SESSION ID:(84.19304) 2018-02-11T14:23:23.367313-05:00
< *** CLIENT ID:() 2018-02-11T14:23:23.367313-05:00
< *** SERVICE NAME:(SYS$USERS) 2018-02-11T14:23:23.367313-05:00
< *** MODULE NAME:(SQL*Plus) 2018-02-11T14:23:23.367313-05:00
< *** ACTION NAME:() 2018-02-11T14:23:23.367313-05:00
< *** CLIENT DRIVER:(SQL*PLUS) 2018-02-11T14:23:23.367313-05:00
---
> *** 2018-02-11T14:25:27.002021-05:00
> *** SESSION ID:(84.15782) 2018-02-11T14:25:27.002021-05:00
> *** CLIENT ID:() 2018-02-11T14:25:27.002021-05:00
> *** SERVICE NAME:(SYS$USERS) 2018-02-11T14:25:27.002021-05:00
> *** MODULE NAME:(SQL*Plus) 2018-02-11T14:25:27.002021-05:00
> *** ACTION NAME:() 2018-02-11T14:25:27.002021-05:00
> *** CLIENT DRIVER:(SQL*PLUS) 2018-02-11T14:25:27.002021-05:00
25c25
< *** TRACE CONTINUED FROM FILE /u01/app/oracle/diag/rdbms/db12201/db12201/trace/db12201_ora_20225_CAT.trc ***
---
> *** TRACE CONTINUED FROM FILE /u01/app/oracle/diag/rdbms/db12201/db12201/trace/db12201_ora_20607_CAT.trc ***
27c27
< Registered qb: SEL$1 0xf972ef68 (PARSER)
---
> Registered qb: SEL$1 0x6bfd6f68 (PARSER)
862c862
< Registered qb: SEL$1 0xf94dbf10 (COPY SEL$1)
---
> Registered qb: SEL$1 0x6c4d9b28 (COPY SEL$1)
876c876
< call(in-use=1736, alloc=16344), compile(in-use=130400, alloc=139528), execution(in-use=2936, alloc=4032)
---
> call(in-use=1736, alloc=16344), compile(in-use=130400, alloc=136232), execution(in-use=2936, alloc=4032)
879c879
< call(in-use=1736, alloc=16344), compile(in-use=126272, alloc=139528), execution(in-use=2936, alloc=4032)
---
> call(in-use=1736, alloc=16344), compile(in-use=126272, alloc=136232), execution(in-use=2936, alloc=4032)
883c883
< call(in-use=1736, alloc=16344), compile(in-use=126272, alloc=139528), execution(in-use=2936, alloc=4032)
---
> call(in-use=1736, alloc=16344), compile(in-use=126272, alloc=136232), execution(in-use=2936, alloc=4032)
888c888
< call(in-use=1736, alloc=16344), compile(in-use=126680, alloc=139528), execution(in-use=2936, alloc=4032)
---
> call(in-use=1736, alloc=16344), compile(in-use=126704, alloc=136232), execution(in-use=2936, alloc=4032)
891c891
< call(in-use=1736, alloc=16344), compile(in-use=126800, alloc=139528), execution(in-use=2936, alloc=4032)
---
> call(in-use=1736, alloc=16344), compile(in-use=126824, alloc=136232), execution(in-use=2936, alloc=4032)
897c897
< call(in-use=1736, alloc=16344), compile(in-use=126800, alloc=139528), execution(in-use=2936, alloc=4032)
---
> call(in-use=1736, alloc=16344), compile(in-use=126824, alloc=136232), execution(in-use=2936, alloc=4032)
901c901
< call(in-use=1736, alloc=16344), compile(in-use=127208, alloc=139528), execution(in-use=2936, alloc=4032)
---
> call(in-use=1736, alloc=16344), compile(in-use=127272, alloc=136232), execution(in-use=2936, alloc=4032)
904c904
< call(in-use=1736, alloc=16344), compile(in-use=127328, alloc=139528), execution(in-use=2936, alloc=4032)
---
> call(in-use=1736, alloc=16344), compile(in-use=127392, alloc=136232), execution(in-use=2936, alloc=4032)
911c911
< call(in-use=1736, alloc=16344), compile(in-use=127328, alloc=139528), execution(in-use=2936, alloc=4032)
---
> call(in-use=1736, alloc=16344), compile(in-use=127392, alloc=136232), execution(in-use=2936, alloc=4032)
916c916
< call(in-use=1736, alloc=16344), compile(in-use=127736, alloc=139528), execution(in-use=2936, alloc=4032)
---
> call(in-use=1736, alloc=16344), compile(in-use=127800, alloc=136232), execution(in-use=2936, alloc=4032)
919c919
< call(in-use=1736, alloc=16344), compile(in-use=127856, alloc=139528), execution(in-use=2936, alloc=4032)
---
> call(in-use=1736, alloc=16344), compile(in-use=127920, alloc=136232), execution(in-use=2936, alloc=4032)
927c927
< call(in-use=1736, alloc=16344), compile(in-use=127856, alloc=139528), execution(in-use=2936, alloc=4032)
---
> call(in-use=1736, alloc=16344), compile(in-use=127920, alloc=136232), execution(in-use=2936, alloc=4032)
930c930
< call(in-use=1736, alloc=16344), compile(in-use=128264, alloc=139528), execution(in-use=2936, alloc=4032)
---
> call(in-use=1736, alloc=16344), compile(in-use=128328, alloc=136232), execution(in-use=2936, alloc=4032)
933c933
< call(in-use=1736, alloc=16344), compile(in-use=128424, alloc=139528), execution(in-use=2936, alloc=4032)
---
> call(in-use=1736, alloc=16344), compile(in-use=128448, alloc=136232), execution(in-use=2936, alloc=4032)
949c949
< call(in-use=1736, alloc=16344), compile(in-use=129680, alloc=139528), execution(in-use=2936, alloc=4032)
---
> call(in-use=1736, alloc=16344), compile(in-use=129728, alloc=136232), execution(in-use=2936, alloc=4032)
951c951
< kkoqbc-subheap (create addr=0x7fddf9729810)
---
> kkoqbc-subheap (create addr=0x7f286bfd1810)
991a992,993
> Column (#3):
> NewDensity:0.000050, OldDensity:0.000050 BktCnt:10000.000000, PopBktCnt:9999.000000, PopValCnt:1, NDV:2
993c995,996
< AvgLen: 9 NDV: 2 Nulls: 0 Density: 0.500000
---
> AvgLen: 9 NDV: 2 Nulls: 0 Density: 0.000050
> Histogram: Freq #Bkts: 2 UncompBkts: 10000 EndPtVals: 2 ActualVal: no
1145c1148
< kkoqbc-subheap (delete addr=0x7fddf9729810, in-use=27312, alloc=32840)
---
> kkoqbc-subheap (delete addr=0x7f286bfd1810, in-use=27312, alloc=32840)
1148c1151
< call(in-use=9832, alloc=65656), compile(in-use=133208, alloc=139528), execution(in-use=2936, alloc=4032)
---
> call(in-use=9624, alloc=65656), compile(in-use=135032, alloc=136232), execution(in-use=2936, alloc=4032)
1154c1157
< call(in-use=9832, alloc=65656), compile(in-use=136304, alloc=139528), execution(in-use=2936, alloc=4032)
---
> call(in-use=9624, alloc=65656), compile(in-use=138208, alloc=140376), execution(in-use=2936, alloc=4032)
3080c3083
< SEL$1 0xf972ef68 (PARSER) [FINAL]
---
> SEL$1 0x6bfd6f68 (PARSER) [FINAL]
3083c3086
< call(in-use=12280, alloc=65656), compile(in-use=159680, alloc=218968), execution(in-use=7392, alloc=8088)
---
> call(in-use=12072, alloc=65656), compile(in-use=161568, alloc=223424), execution(in-use=7392, alloc=8088)

PL/SQL “constant”

By Brian Fitzgerald

The procedure-scoped PL/SQL “constant” can have a different value each call. The documentation says as much: “Constants are initialized every time a block or subprogram is entered.”

[oracle@stormking db12201 plsql]$ cat runtime.constant.sql
create or replace procedure pr
(
 p in number,
 o out number
)
is
 c constant number := p;
begin
 o := c;
end pr;
/

variable o number

exec pr ( 1, :o )
print :o

exec pr ( 2, :o )
print :o
quit
[oracle@stormking db12201 plsql]$ sqlplus u/u @ runtime.constant.sql
SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 6 23:28:32 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Tue Feb 06 2018 23:27:25 -05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Procedure created.
PL/SQL procedure successfully completed.
O
----------
 1
PL/SQL procedure successfully completed.
O
----------
 2
Disconnected from Oracle Database 12c Enterprise Edition 
Release 12.2.0.1.0 - 64bit Production

 

Permanently deleting histograms

By Brian Fitzgerald

“A histogram is a special type of column statistic that provides more detailed information about the data distribution in a table column.” Without histograms, the optimizer could underestimate the cost of a table access for a nonselective predicate, and as a result, choose an inefficient index access path.

There are times when the DBA will want to delete histograms, including:

  • Issues with bind peeking.
  • Issues with adaptive cursor sharing.
  • Limit SYSAUX growth.
  • High number of child cursors resulting from adaptive cursor sharing while using a database resident connection pool (DRCP).
  • Plan instability caused by histograms.
  • Troubleshooting any of these.
  • Temporary workaround for any of these.
  • Permanent workaround for any of these.

You can delete a histogram, but that has obvious problems. In the first place, if you delete a histogram, you haven’t prevented it from coming back. You can lock statistics, but locking statistics has its drawbacks. You can only lock statistics at the table level, not the column level. If you table lock stats, other critical statistics, such as num_rows, num_nulls, and num_distinct will not get gathered. The optimizer will not keep up with changes in table population. Finally, to prevent reappearance of histograms, some DBAs decide to disable automatic statistics gathering. I usually do not agree with disabling automatic statistics gathering.

A more flexible way to delete a histogram is to set table preference method_opt to for columns size 1 for the desired column or columns. This blog posting demonstrates how this technique can be scripted.

Scripts for optimizer statistics

gather.table.stats.sql

Gather stats with the default options (no method_opt):

define ownname=&&1
define tabname=&&2
whenever oserror exit 1
@ sqlerx.sql
set verify off
set serveroutput on

declare
 l_fmt clob := q'{Gathered stats on %s.%s with default options.}';
begin
 dbms_stats.gather_table_stats (
 ownname => '&&ownname',
 tabname => '&&tabname'
 );
 out_pkg.printf(l_fmt, '&&ownname', '&&tabname' );
end;
/

Note, at this juncture, that every sql file that reads other sql files (with”@”) needs “whenever oserror exit 1”

dba.tab.col.statistics.sql

Display column statistics and show whether there is a histogram.

define owner=&&1
define tablename=&&2
whenever oserror exit 1
@ l32k.p50k.sql
@ columnformat.sql
set verify off

select column_name, num_distinct, histogram, num_buckets
from dba_tab_col_statistics
where owner = '&&owner'
and table_name = '&&tablename'
order by column_name;

rpt.col.usage.sql

Oracle decides whether to gather a histogram base on workload on skewed columns, as recorded in col_usage$. You can run report_col_usage to check on what workload Oracle has recorded. report_col_usage will not work unless you set long.

define ownname=&&1
define tabname=&&2
whenever oserror exit 1
@ sqlerx.sql
@ l32k.p50k.sql

set verify off

set long 2000000000
exec dbms_stats.flush_database_monitoring_info;
column rpt format a160
select dbms_stats.report_col_usage('&&ownname','&&tabname') rpt from dual;

prefer.delete.histo.sql

The delete histogram script that is the subject of this blog post. The script does not actually delete the histogram. It simply arranges that it will be delete on the next gather stats. You can either delete the histogram right away by calling dbms_stats.delete_column_stats, or you can gather stats with the default options (no method_opt).

define ownname=&&1
define tabname=&&2
define colname=&&3
whenever oserror exit 1
@ sqlerx.sql
set verify off
set serveroutput on

declare
 l_fmt clob := q'{Preference for no histogram on %s.%s is set.}';
begin
 dbms_stats.set_table_prefs (
 ownname => '&&ownname',
 tabname => '&&tabname',
 pname => 'method_opt',
 pvalue => 'for columns size 1 &&colname'
 );
 out_pkg.printf(l_fmt, '&&ownname', '&&tabname' );
end;
/

dba.tab.stat.prefs.sql

Use this script for reporting table stat prefs

define owner=&&1
whenever oserror exit 1
@ sqlerx.sql
@ l32k.p50k.sql
@ columnformat.sql
set verify off

column preference_name format a30
column preference_value format a50

select p.owner,
 p.table_name,
 p.preference_name,
 p.preference_value
from dba_tab_stat_prefs p
where owner = '&&owner'
order by
p.owner,
p.table_name,
p.preference_name;

Script in SQLPATH

Scripts mentioned in this blog post that I keep in a common directory for reuse.

sqlerx.sql

whenever sqlerror exit 1

l32k.p50k.sql

set linesize 32767
set pagesize 50000
set trimspool on

columnformat.sql

A very long, and ever growing, file of catalog column names.

column histogram format a20
column account_status format a20
column column_name format a30
column constraint_name format a30
column consumer_group format a30
etc.

The demo script

demo.delete.histo.sql

whenever oserror exit 1
@ l32k.p50k.sql
@ columnformat.sql
set verify off

drop table t purge;
drop sequence s;
@ sqlerx.sql
create table t
(
k number,
g varchar2(1)
);
create sequence s;

prompt Generate skewed data in column G
insert into T (k, g ) values ( s.nextval, 'M' );
insert into T (k, g ) values ( s.nextval, 'F' );
insert into T (k, g ) values ( s.nextval, 'M' );

commit;

@ gather.table.stats.sql U T
@ dba.tab.col.statistics.sql

prompt generating workload on column G
select count(*) from t where g = 'Z';

prompt checking col_usage$
@ rpt.col.usage.sql U T

@ gather.table.stats.sql U T
@ dba.tab.col.statistics.sql
prompt Yes, there is a histogram.

@ prefer.delete.histo.sql U T G

@ gather.table.stats.sql U T

prompt check existence of histogram on column G
@ dba.tab.col.statistics.sql
prompt The histogram is gone!

Demo script output

[oracle@stormking db12201 stats]$ sqlplus u/u @ demo.delete.histo.sql
SQL*Plus: Release 12.2.0.1.0 Production on Sun Feb 4 20:20:33 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Sun Feb 04 2018 20:16:14 -05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Table dropped.
Sequence dropped.
Table created.
Sequence created.
Generate skewed data in column G
1 row created.
1 row created.
1 row created.
Commit complete.
Gathered stats on U.T with default options.
PL/SQL procedure successfully completed.
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS
------------------------------ ------------ -------------------- -----------
G 2 NONE 1
K 3 NONE 1
generating workload on column G
COUNT(*)
----------
 0
checking col_usage$
PL/SQL procedure successfully completed.
RPT
---------------------------------------------------------------------------
LEGEND:
.......
EQ : Used in single table EQuality predicate
RANGE : Used in single table RANGE predicate
LIKE : Used in single table LIKE predicate
NULL : Used in single table is (not) NULL predicate
EQ_JOIN : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER : Used in single table FILTER predicate
JOIN : Used in JOIN predicate
GROUP_BY : Used in GROUP BY expression
...........................................................................
###########################################################################
COLUMN USAGE REPORT FOR U.T
...........................
1. G : EQ
###########################################################################
Gathered stats on U.T with default options.
PL/SQL procedure successfully completed.
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS
------------------------------ ------------ -------------------- -----------
G 2 FREQUENCY 2
K 3 NONE 1
Yes, there is a histogram.
Preference for no histogram on U.T is set.
PL/SQL procedure successfully completed.
Gathered stats on U.T with default options.
PL/SQL procedure successfully completed.
check existence of histogram on column G
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS
------------------------------ ------------ -------------------- ----------
G 2 NONE 1
K 3 NONE 1
The histogram is gone!
Disconnected from Oracle Database 12c Enterprise Edition 
Release 12.2.0.1.0 - 64bit Production

I have tested this script on 11.2, 12.1, and 12.2.

To summarize, I have deleted the histogram on table U.T, column G. I have done this not directly, by issuing delete_column_stats, but indirectly, by issuing set_table_prefs.

Notes

  1. Gather stats option “force” overrides locked statistics but does not override table preference.
SQL> exec dbms_stats.gather_table_stats(ownname=>'U',tabname=>'T',
force=>true)
PL/SQL procedure successfully completed.
SQL> @ dba.tab.col.statistics.sql U T
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS
------------------------------ ------------ -------------------- -----------
G 2 NONE 1
K 3 NONE 1

The histogram remains deleted even though option “force” was used.

2. Gathering stats with non-default options can re-create the histogram.

SQL> exec dbms_stats.gather_table_stats(ownname=>'U',tabname=>'T',
method_opt=>'for all columns size 254')
PL/SQL procedure successfully completed.
SQL> @ dba.tab.col.statistics.sql U T
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS
------------------------------ ------------ -------------------- -----------
G 2 FREQUENCY 2
K 3 FREQUENCY 3

I want to point out a bad practice here. Routinely gathering histograms on all columns is inadvisable. It is better to start off by gathering stats by using the default options, and then let Oracle decide, based on col_usage$, whether to gather a histogram.

3. The earliest reference I found on this technique of using set_table_prefs to make sure that histograms stay deleted is a 2011 article in Oracle’s Optimizer Blog.

4. Whether table T needs a histogram on column G is a matter unto itself. The answer might be different depending on whether G, M, and F represent “gender”, “male”, and “female” or “granularity”, “medium” and “fine”, whether the distribution of M and F is skewed, whether G appears as a predicate in the application query, whether there is an index on G, and whether a histogram is causing query plan stability issues, or other issues.

5. Others have demonstrated data skew using a high number of rows. I demonstrate skew with only 3 rows.

6. Likewise, others have demonstrated workload by running a high number of statements. I run only one query. Note that a query for a nonexistent value, g = ‘Z’, serves the purpose.

Multiple columns per table

You cannot use script prefer.delete.histo.sql to delete the histogram on more than one column per table. If you try, you get this result.

SQL> @ prefer.delete.histo.sql U T K
Preference for no histogram on U.T is set.
PL/SQL procedure successfully completed.
SQL> @ dba.tab.stat.prefs.sql U
OWNER TABLE_NAME PREFERENCE_NAME PREFERENCE_VALUE
------------------------------ ------------------------------ ------------
U T METHOD_OPT FOR COLUMNS SIZE 1 K

You must state the prefs in a single command, such as:

prefer.delete.histo.sq2

begin
 dbms_stats.set_table_prefs (
 ownname => 'U',
 tabname => 'T',
 pname => 'method_opt',
 pvalue => 'for columns size 1 G size 1 K'
 );
end;
/

Result:

SQL> @ prefer.delete.histo.sq2
PL/SQL procedure successfully completed.
SQL> @ dba.tab.stat.prefs.sql U
OWNER TABLE_NAME PREFERENCE_NAME PREFERENCE_VALUE
------------------------------ ------------------------------ -------------
U T METHOD_OPT FOR COLUMNS SIZE 1 G SIZE 1 K

Use of table prefs in a DBA organization

In a DBA group, members have different opinions based on different experiences. Some DBAs want to lock table stats. Others want to disable the stats gathering job. Sometimes non-DBAs, like developers and data center managers, get into the act with their own opinions.

Using set_table_prefs is offered here as an alternative. dba.tab.stat.prefs.sql can be used to report on tables that have the preference for no histogram set.

The best thing to do is come up with a consensus that is based on principles, current recommendations from Oracle, and rigorous testing.

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:

[code language=”text”]

*.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’

[/code]

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

[code language=”sql”]
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;
[/code]

[code language=”text”]
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.
[/code]

Check that the in-memory column store is populated.

[code language=”sql”]
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
;
[/code]

[code language=”text”]
OWNER SEGMENT_NAME MB MBPOP PCTPOP MBINMEM PRI INMEMORY_COMPRESS POPULATE_
———- —————————— ———- ———- ———- ———- ——– —————– ———
BRIAN TMOD 20608 20608 100 2731 NONE FOR CAPACITY HIGH COMPLETED
[/code]

100% populated!

Let’s try it!

[code language=”sql”]
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
[/code]

[code language=”text”]
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.

[/code]

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.

[code language=”sql”]
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;
[/code]

[code language=”text”]Index altered.

Index altered.

Index altered.

Index altered.

Index altered.

Index altered.

Index altered.

Index altered.

Index altered.
[/code]

[code language=”text”]

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.
[/code]

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!

[code language=”text”]

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
[/code]

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. 🙂