SQL Mini-challenge entry: Kim Berg Hansen v4

by Kim Berg Hansen

Anything goes that is not explicitly disallowed? Okay, then let’s get down to three buffer gets ;-)

As a DBA I grant rights to HR:

grant on commit refresh to hr

And then in HR I create some materialized views and logs:

create materialized view log on employees with rowid, commit scn
   (employee_id, job_id, first_name, last_name, department_id)
   including new values;
create materialized view log on departments with rowid, commit scn
   (department_id,  (more...)

In-memory limitation

I’ve been struggling to find time to have any interaction with the Oracle community for the last couple of months – partly due to workload, partly due to family matters and (okay, I’ll admit it) I really did have a few days’ holiday this month. So making my comeback with a bang – here’s a quick comment about the 12cR2 in-memory feature, and how it didn’t quite live up to my expectation; but it’s also a comment (more...)

Oracle Database 12c In-Memory Feature – Part V. You Can’t Use It If It’s Not “Enabled.” Not Being Able To Use A Feature Is An Important “Feature.”

This is part 5 in a series: Part I, Part II, Part III, Part IV, Part V.


This blog post is the last word on the matter.

Enabled?  It’s About Usage!

You don’t get charged for Oracle feature usage unless you use the feature. So why does Oracle inconsistently use the word enabled when we care about usage? If enabled precedes usage then enabled is a sanctified term. Please read (more...)

Why you Need Delphix for Oracle E-Business

Dear readers,

As I mentioned in my last post, I've been working with an exciting technology called Delphix
for database and ERP applications recently.

With Delphix you can reduce the pain points involved with performing a traditional recovery operation for Oracle and other database platforms. Now when it comes to large ERP systems such as SAP and Oracle E-Business Suite, the challenge of backup and recovery and well as system and application refresh operations and (more...)

Exadata Zone Maps

Just a quick post on a new Exadata feature called Zone Maps. They’re similar to storage indexes on Exadata, but with more control (you can define the columns and how the data is refreshed for example). People have complained for years that storage indexes provided no control mechanisms, but now we have a way to exert our God given rights as DBA’s to control yet another aspect of the database. Here’s a link to the (more...)

SQL Mini-challenge entry: Timo Raitalaakso

by Timo Raitalaakso

Here is my approach to the problem. I will be using the “Anything not explicitly disallowed by the problem statement is also permissible.” approach.

One key to query performance is the correct organization of the rows according to the access path. One way to get performance is to use indexing.

create index ej_idx on hr.employees(job_id,department_id) compress 2;
create index e_idx on hr.employees(first_name,last_name,department_id);
create unique index ejh_idx on hr.employees(employee_id,department_id);

SQL Mini-challenge entry: Craig Martin v3

by Craig Martin

Blasted.. after looking at the solution from Jimmy Brock, I see that adding one more hint to use index fast full scan to get to the Jobs row gets me down to 10 buffers. Great addition! So my newest solution is:

select /*+ ordered index_ffs(j job_id_pk) use_nl(jh) use_nl(e) use_nl(e2) use_nl(e3) use_nl(d) use_nl(l) */  distinct l.location_id, l.city
from dual
    left outer join jobs j
        on j.job_title = 'President'
    left outer  (more...)

OGG-00212, what a frustrating error.

Normally, I don’t mind errors when I’m working with Oracle GoldenGate (OGG); I actually like getting errors, keeps me on my toes and gives me something to solve.  Clients on the other hand do not like errors…LOL.  Solving errors in OGG is normally pretty straight forward with the help of the documentation.  Although today I can almost disagree with the docs.

Today, as I’ve been working on implementing a solution with OGG 11.1.x (more...)

Delphix An exciting new way of managing your data

Dear Readers,

 Recently I have been working with an exciting technology called Delphix.

What is Delphix you might ask? Well Delphix is a data virtualization appliance that provides you with the ability to quickly clone, refresh and provision virtualized copies of database and application environments in record time compared to traditional copy, clone and refresh operations for Oracle and SQL Server as well as for SAP and Oracle EBS application ERP systems.

 In addition we (more...)

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