SQL Mini-challenge entry: CTE v2

by Jimmy Brock

Modified data set “curr_job” to return anyone that currently has the job_title of ‘President’ – even if his or her name is not Steven King.

Modify data set “job_hist” to return any employee that has ever had the job_id ‘AD_PRES’.

Buffer gets are now 9.

jbrock@ORA1> set serveroutput off
jbrock@ORA1> alter session set statistics_level=all ;

jbrock@ORA1> col version for a20
jbrock@ORA1> select distinct version from product_component_version;

-------------------- (more...)

SQL Mini-challenge entry: Craig Martin v2

by Craig Martin

My original entry is here. In the comments, Kim Berg Hansen made a great point that there was a flaw in my logic where if there were no President, the correct results wouldn’t be returned. This is an excellent catch, and a perfect example of why all code should be peer reviewed! While looking back through the code to fix this, I found a way to get the buffer count down to (more...)

SQL Mini-challenge entry: Kim Berg Hansen v3

by Kim Berg Hansen

I just keep playing with this inspired by the other great entries :-)

Emmanuel Humblot posted an entry that used as few as 12 buffer gets – very good. His entry also reminded me to remember to look at the constraints of the tables in order to see if some assumptions can safely be made that enables more efficient code – like for example that it can safely be assumed that (more...)

SQL Mini-challenge entry: CTE

by Jimmy Brock

I decided to toss my hat into the ring. Lots of creative solutions, thus far.

I’m using common table expressions, this way I only have to visit the data blocks once.

Got it down to 9 buffer gets.

jbrock@ORA1> set serveroutput off
jbrock@ORA1> alter session set statistics_level=all ;

jbrock@ORA1> col version for a20
jbrock@ORA1> select distinct version from product_component_version;


WITH    emps
      SELECT   location_id

SQL Tuning Health Check (SQLHC)

What is SQL Tuning Health Check? The SQL Tuning Health Check is provided by Oracle (Doc ID 1366133.1) in order to check the environment where the problematic SQL query runs. It checks the statistics, the metadata, initialization parameters and other elements that may influence the performance of the SQL being analyzed. The script generates an [...]

The post SQL Tuning Health Check (SQLHC) appeared first on Oracle DBA Scripts and Articles (Montreal).

Can you justify your data ?

People ask me to justify use of Delphix. I can understand. Delphix is pretty new and often, most of my friends who are DBAs respond with “I can copy a database, so what, I can do it a little faster with Delphix.” Well that’s missing the whole boat. The question won’t be why you should use Delphix but “can you  justify working without Delphix?”

I see Delphix as amazingly positioned at nexus of data (more...)

New Oracle Bug alert (Bug 19384287)

Heads up to all the folks running and above if you're using function-based indexes! There's a new Oracle bug 19384287. I'll fill you in with a complete post over at Toad World.


Oracle introduced this view in version 10. It looks like this in version 11:
SQL> desc dba_feature_usage_statistics
Name                       Null?    Type
-------------------------- -------- ------------------
DBID                       NOT NULL NUMBER
NAME                       NOT NULL VARCHAR2(64)
VERSION                    NOT NULL VARCHAR2(17)
CURRENTLY_USED                      VARCHAR2(5)
FIRST_USAGE_DATE                    DATE
LAST_USAGE_DATE                     DATE
AUX_COUNT                           NUMBER
FEATURE_INFO                        CLOB
LAST_SAMPLE_DATE                    DATE
SAMPLE_INTERVAL                     NUMBER
DESCRIPTION                         VARCHAR2(128)
As its name suggests, it allows you to see (more...)

What is Dephix ? (video presentation)

According to a recent IDC study, on average Delphix

  • pays for itself in 4.3 months
  • 461% ROI over 5 years
  • 96.8% reduction in database storage
  • $50 Million predicted annual benefit for organizations over 75,000 employees

Delphix is used by over 100 of the Fortune 500.

What is Delphix? Why is Delphix important? What problems does Delphix solve in the industry?

Here is a slide deck I put together for KSCOPE :

And here (more...)

keeping my fingers crossed just submitted abstract for RMOUG 2015 Training Days …

The Rocky Mountain Oracle Users Group has been big and organized for a very long time.  I have never been out there ( my bad ) but am hoping to change that situation in 2015.

Abstracts are being accepted for Training Days 2015 ... my first one is in there now thinking about a second submission but my Hotsos 2014 presentation needs some more work/fixing.  Ok lets be honest I need to shrink it (more...)

Watch Oracle DB Session Activity With The Real-Time Session Sampler

Watch Oracle DB Session Activity With My Real-Time Session Sampler

Watching session activity is a great way to diagnose and learn about Oracle Database tuning. There are many approaches to this. I wanted something simple, useful, modifiable, no Oracle licensing
issues and that I could give away. The result is what I call the Oracle Real-Time Session Sampler (OSM: rss.sql).

The tool is simple to use.  Based on a number filtering command line (more...)

Oracle ASM Filter Driver: Advanced Format Fail


In my previous post on the subject of the new ASM Filter Driver (AFD) feature introduced in Oracle’s patchset, I installed the AFD to see how it fulfilled its promise that it “filters out all non-Oracle I/Os which could cause accidental overwrites“. However, because I was ten minutes away from my summer vacation at the point of finishing that post, I didn’t actually get round to writing about what (more...)

Peeling through layers of cache

Nowadays, data in databases is wrapped in may layers of cache: result cache, buffer cache, OS page cache, storage hardware cache… They greatly improve performance, but they also make it less stable and harder to predict. And when I/O performance takes a turn for worse, one has to go through multiple layers of cache trying to understand what went wrong and why. I had such a case not too long ago.

The incident took place (more...)

How to resize grid disks in Exadata with free space in cell disks

This document explains how to resize the grid disks in Exadata (to make them larger), when there is free space in the cell disks. The free space can be anywhere on the cell disks. In other words, the grid disks can be built from and extended with the non-contiguous free space.

Typically, there is no free space in Exadata cell disks, in which case the MOS Doc ID 1465230.1 needs to be followed. But (more...)

12c – Nested tables vs Associative arrays

This was going to the be the immediate follow up to my previous post, but came out and I got all excited about that and forgot to post this one :-)

Anyway, the previous post showed how easy it is to convert between nested tables and associative arrays.  The nice thing in 12c is that this is no longer needed – you can query the associative arrays directly

SQL>  (more...) security grrr…

One of my favourite security "tricks" used to be the following:

SQL> [create|alter] user MY_USER identified by values 'impossible';

Looks odd, but by setting the encrypted value of someone’s password to something that it is impossible to encrypt to, means you’ll never be able to connect as that account.  (Think schema’s owning objects etc).

I hear you ask: "Why not just lock the account?"

Well…in my opinion, that’s a security hole.  Let’s (more...)

Grid/CRS AddNode or runInstaller fails with NullPointerException

Posting this here mostly to archive it, so I can find it later if I ever see this problem again.

Today I was repeatedly getting this error while trying to add a node to a cluster:

(grid)$ $ORACLE_HOME/oui/bin/addNode.sh -silent -noCopy CRS_ADDNODE=true CRS_DHCP_ENABLED=false INVENTORY_LOCATION=/u01/oraInventory ORACLE_HOME=$ORACLE_HOME "CLUSTER_NEW_NODES={new-node}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={new-node-vip}"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 24575 MB    Passed
Oracle Universal Installer, Version Production
Copyright  (more...)

Space used by objects

Calculate the space used by a single object This script will help you calculate the size of a single object : [crayon-53e3d3c3241a4450892579/] Calculate the space used by a whole schema If you want the space used by a whole schema, then here is a variation of the first query : [crayon-53e3d3c3241d6893598998/]

The post Space used by objects appeared first on Oracle DBA Scripts and Articles (Montreal).

Cloning pluggable database with custom snapshot

When cloning pluggable databases Oracle gives you also SNAPSHOT COPY clause to utilize storage system snapshot capabilities to save on storage space. But it works only when datafiles are placed on ACFS, ZFS Appliance over DNFS or Netapp (Oracle Multitenant Pluggable Database Snapshot Cloning: Use Cases and Supported Platforms Doc ID 1597027.1) and you also cannot unplug/drop the source PDB and you also cannot unplug the clone PDB.

Recently I've started to like BTRFS (more...)

My misunderstandings regarding when indexes would not be used

It is normal for bloggers including myself to post about the great things they have done  – however in this case I am blogging about why I was surprised that adding a specific index had the substantial benefits it did have.

The table contains around 32M rows, is not partitioned and is on Linux

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
SELL_UNIT_ID                              NOT NULL VARCHAR2(20)
PRD_CD                                    NOT NULL VARCHAR2(75)
PRD_TYP_CD                                NOT  (more...)