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) */
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.
create table t1 as select * from dba_objects;
alter session set statistics_level = all;
@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...
is a package that generates SQL to compare data or synchronize tables. Here are 10 good reasons to use it.
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
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;
Ok, when you know the result, try to guess what will return this query:
select count(*) cnt (more...)
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 […]
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 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
Oracle Database 12c introduced "invisible columns": they are only visible when you name them explicitly in the
list. Unfortunately, they seem to be even more
invisible when you access them through a database link! Here are some surprising results from
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
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...)
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
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.
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
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 email@example.com 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...)
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.
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.
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...)
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...)