Real time sql monitoring – bulk bound statement

Moving through some observations of an ongoing investigation tackling the easiest ones first.

Previously I observed the slow execution of a recursive delete of statistics history but coming not from MMON but from a number of application processes which should have only been calculating stats for their own partitions.

Statement was sql id 9v9n97qj8z1dg:

delete /*+ dynamic_sampling(4) */
from sys.wri$_optstat_histhead_history
where savtime < :1
and rownum <= NVL(:2, rownum);

From the real time sql (more...)


Yesterday I mentioned issues with a recursive delete operation on statistics history.

This is a quick illustration of the last points I made on that post regarding the lack of a COUNT STOPKEY optimisation because of the use of NVL.

COUNT STOPKEY is an optimisation which allows processing to stop once the target number of rows has been reached.

For example:

create table t1 as select * from dba_objects;

alter session set statistics_level = all;


10 Cool things about the COMPARE_SYNC package

@thatjeffsmith recently recommended an article about making your blog more popular. The article said "lists of 10 things" were great ways to get more readers. Hey, if that's all it takes... STEW_COMPARE_SYNC is a package that generates SQL to compare data or synchronize tables. Here are 10 good reasons to use it.

2014 Annual SQL Championship

Steven Feuerstein runs a great site at PL/SQL Challenge that is just another way for developers to stay up to date with their knowledge of PL/SQL, SQL and database design with a bit of fun.

PL/SQL championships are held quarterly, but the less frequent SQL and Database Design quizzes are currently held annually. Thanks to persistence and maybe a bit of experience, I was eligible to compete in both.

Unfortunately do to a timing issue, (more...)

Easy quiz: rownum < NaN

As you know, NaN is a “Not a Number”.
How do you think, what would be the result of the following query? (0f/0 == NaN)

select count(*) cnt from dual where rownum < 0f/0;

Spoiler:: Answer SelectShow

Ok, when you know the result, try to guess what will return this query:
select count(*) cnt  (more...)

To N or Not to N, is SYS_OP_C2C the Question; Oracle NVARCHAR Slow?

February 8, 2015 I was recently contacted about an odd performance issue with a software product named TDM, where certain SQL statements submitted to an Oracle 9.2.x database required roughly 30 seconds to execute when a nearly instantaneous response was expected.  The person who reported the issue to me provided a sample SQL statement that […]

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 35: Robust v/s optimal query execution plans

A subset of all possible execution plans can be described as robust. While such solutions are not always quite optimum, they are almost always close to optimum in real-world queries, and they have desirable characteristics, such as predictability and low likelihood of errors during execution.(read more)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 34: SQL Aaargh!

The biggest problem with SQL query optimization is that semantically equivalent SQL queries are not guaranteed to perform equally well. When I encounter problems like this, I sympathize with the folks who got frustrated with RDBMS performance and created NoSQL.(read more)

Way Too Invisible Columns

Oracle Database 12c introduced "invisible columns": they are only visible when you name them explicitly in the SELECT list. Unfortunately, they seem to be even more invisible when you access them through a database link! Here are some surprising results from SELECT and MERGE statements.

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 33: The mother of all SQL antipatterns?

The mother of all SQL “anti-patterns” is the ad-hoc query. Here, the end-user of the application can specify any combination of parameters—for example, the job_id, the dept_id, or a combination of both—and the data is retrieved using a catchall query.(read more)

Call vs Exec

Just a reference to a really simple difference between CALL and EXEC.
I thought I had mentioned this before but couldn’t find it so…

EXEC/EXECUTE is a SQL*Plus command which wraps the proc call in an anonymous BEGIN … END; block.

CALL is a SQL command hence it is limited to SQL data types and there are other restrictions which the documentation sums up pretty well.

Because CALL is SQL, there is on key behavioural (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 32: Pretzels created by someone experimenting with hallucinogens?

In an interview for the NoCOUG Journal, Steven Feuerstein said: “Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens.” As if to prove Steven correct, NoCOUG held three international SQL challenges. The winning entries were mind-boggling indeed.(read more)

MERGE and invisible columns = invisible documentation?

Oracle 12c introduced "invisible columns" to help us add columns to tables without breaking existing applications. The documentation explains how they work with SELECT and INSERT, but not MERGE. Here's what happened when I tried MERGE.

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 31: How to Avoid a Salted Banana

In the February 2015 issue of the NoCOUG Journal, Lothar Flatz introduces the term “salted banana” for a certain kind of problem in SQL tuning.(read more)

Share your interesting SQL or PL/SQL experience on stage, on camera, or in print

If you have had interesting experiences with SQL or PL/SQL in your career, here’s your opportunity to share them on stage, on camera, or in print. Send your stories to and we’ll forward them to the SQL evangelists team headed up by Steven Feuerstein. You may be selected to tell your stories on stage at the YesSQL! presentation at the winter conference next Tuesday or your stories may be recorded on camera or (more...)

STEW_COMPARE_SYNC: Introducing the package

After many blog posts about comparing and synchronizing tables, I have united all the techniques I presented in one place. The STEW_COMPARE_SYNC package generates SQL for
  • Comparing tables, views and queries, both local and remote.
  • Synchronizing, or applying changes to target tables from either source tables or "Change Data Capture" input.

Compare and Sync without Primary Keys

I have written a lot about comparing and synchronizing tables. My examples always had both primary keys and non-key columns, so I could do updates along with inserts and deletes. What about the other tables? Here's a technique that works for them.

Oracle ANSI SQL and the WITH syntax

SQL language has been around for so long, that some people feel it never changes. This is obviously not true – SQL is a dynamic language that changes all the time: some changes come from the vendors themselves but often this is the result of the standard changing and Oracle adopting the new standard in a later version.

Let’s start with a short history lesson. As we all know, SQL is not a unique language (more...)

How to avoid a salted banana! (SQL tuning)‏

Dear NoCOUG members and friends,

You’re going to enjoy the new issue of the NoCOUG Journal. Click here to download it.

  • Lothar Flatz talks about the biggest issue in SQL tuning: the “salted banana.” We predict that the phrase “salted banana” will become as widely known in the Oracle community as “compulsive tuning disorder.” Click here to go directly to Lothar’s article.
  • Janis Griffin—a.k.a. the Looney Tuner—explains why the Oracle (more...)

MySQL Locking

Have you ever wondered if MySQL does Row Level Locking? Or wondered why are you getting the error:

ERROR 1205 (HY000) : Lock wait timeout exceeded; try restarting transaction

You get the error because your allocated time to hold a DML lock in a transaction exceeds the set limit. Usually the default limit to hold a DML row lock, set by innodb_lock_wait_timeout db parameter, is 50 seconds. If your transaction doesn't commit/rollback within 50 seconds (more...)