Oracle Scratchpad

August 13, 2013

NVL()

Filed under: CBO,Conditional SQL,Execution plans,NULL,Oracle — Jonathan Lewis @ 7:14 am BST Aug 13,2013

Here’s a little detail about how the optimizer can handle the nvl() function that I hadn’t noticed before (and it goes back to at least 8i). This is running on 11.2.0.3, and table t1 is just all_objects where rownum <= 20000:

(more…)

August 5, 2013

Bloom Filter

Filed under: 12c,CBO,Execution plans,Oracle — Jonathan Lewis @ 9:22 pm BST Aug 5,2013

I’ve posted this note as a quick way of passing on an example prompted by a twitter conversation with Timur and Maria about Bloom filters:

The Bloom filter (capital B because it’s named after a person) is not supposed to appear in Oracle plans unless the query is executing in parallel but here’s an example which seems to use a serial Bloom filter.  Running in 11.2.0.3 and 12.1.0.1 (the results shown are the latter – the numbers are slightly different between versions):

(more…)

July 25, 2013

Parallel to Serial

Filed under: Execution plans,Oracle,Parallel Execution — Jonathan Lewis @ 5:21 pm BST Jul 25,2013

Here’s a little problem that came up on the Oracle-L listserver today:

I’m trying to write a query which reads the corresponding partition of the fact, extracts the list of join keys, materialises this result set, and finally joins the necessary dimensions. The key thing I’m trying to do is to run the initial query on the fact in parallel and then the rest of the query serially.

The full requirement, if you follow the link, may seem a little puzzling but there’s no point in second-guessing every question that people ask – there’s usually a reason for doing something in a particular way – so I just rattled off the first thing that came to mind, which was this:  when you include rownum in a parallel query Oracle has to serialise to generate the rownum – so create an inline view which does the parallel but adds a rownum to the select list, then join to the inline view. The plan should include a VIEW operator holding the parallel bit, and then you can hint as necessary to make the subsequent activity serial.

(more…)

June 28, 2013

Illogical Tuning

Filed under: CBO,Execution plans,Oracle,subqueries — Jonathan Lewis @ 6:55 pm BST Jun 28,2013

The title is a bit of a joke, really. It’s mirroring a title I used a little over a year ago “Logical Tuning” and reflects my surprise that a silly little trick that I tried actually worked.

If you don’t want to read the original article, here’s a quick précis – I started with the first query, which the optimizer executed as a filter subquery, and rewrote it as the second query, which the optimizer executed as two anti-joins (reducing the execution time from 95 seconds to 27 seconds):

(more…)

June 7, 2013

Same Plan

Filed under: CBO,Execution plans,Oracle,Tuning — Jonathan Lewis @ 5:11 pm BST Jun 7,2013

An interesting little problem appeared on the Oracle-L mailing list earlier on this week – a query ran fairly quickly when statistics hadn’t been collected on the tables, but then ran rather slowly after stats collection even though the plan hadn’t changed, and the tkprof results were there to prove the point. Here are the two outputs (edited slightly for width – the original showed three sets of row stats, the 1st, avg and max, but since the query had only been run once the three columns showed the same results in each case):

(more…)

June 6, 2013

Parallel DML

Filed under: Execution plans,Oracle,Parallel Execution — Jonathan Lewis @ 8:06 am BST Jun 6,2013

Parallel DML is not enabled by default, and it’s easy to forget this and think you’ve made an operation “as parallel as possible” by declaring objects parallel or putting in hints for parallelism.

A recent question on OTN asked about speeding up a  materialized view refresh that seemed to be spending a lot of its time waiting on “PX Deq Credit: send blkd”. The manuals describe this as an “idle event”; but that’s not always true. The OP had supplied the output from tkprof for one of the sessions showing the “insert as select” that was the (complete) refresh and it was clear that the select was running in parallel, but the insert wasn’t – and that’s one case in which the “PX Deq Credit: send blkd” is arguably an “idle” wait (with a timeout of 2 seconds). It’s possible that the refresh could go faster if the OP enabled parallel DML.

(more…)

May 13, 2013

Parse Time

Filed under: Execution plans,Oracle,Performance,Troubleshooting — Jonathan Lewis @ 6:59 pm BST May 13,2013

Dominic Brooks published a note recently about some very nasty SQL – originally thinking that it was displaying a run-time problem due to the extreme number of copies of the lnnvl() function the optimizer had produced. In fact it turned out to be a parse-time problem rather than a run-time problem, but when I first read Dominic’s note I was sufficiently surprised that I decided to try modelling the query.

(more…)

February 13, 2013

STS, OFE and SPM

Filed under: dbms_xplan,Execution plans,Hints,Oracle,Upgrades — Jonathan Lewis @ 9:19 am BST Feb 13,2013

That’s SQL Tuning Sets, optimizer_features_enable, and SQL Plan Management.

There’s a recent post on OTN describing an issue when using SQL Tuning Sets to enforce plan stability when upgrading from 10.2.0.3 to 11.2.0.3 – it doesn’t always work. Here’s a very simple model to demonstrate the type of thing that can happen (the tables are cloned from a completely different demo, so don’t ask why I picked the data they hold):

(more…)

January 28, 2013

Losing it

Filed under: Execution plans,Oracle,Troubleshooting — Jonathan Lewis @ 6:08 pm BST Jan 28,2013

The example I gave last week showing how a SORT operation in an execution plan might include the work of resolving function calls in your SQL and might, therefore, be reporting much higher resource utilisation than expected reminded me of some problems I’ve had with gaps in execution plans in the past. So I thought I’d give a little demonstration of the way in which the completeness of execution plans can develop over time.

(more…)

January 25, 2013

Sorting

Filed under: Execution plans,Oracle,Troubleshooting — Jonathan Lewis @ 5:45 pm BST Jan 25,2013

Here’s a little quirk of execution plans that came up recently on the Oak Table network. If you call a function in a query, and do some sorting with the results, where does the work of calling the function get reported in the execution plan if you trace the query or look at the in-memory rowsource execution stats. Let’s take a look at a simple example:

(more…)

December 7, 2012

Update Error

Filed under: Execution plans,Oracle,subqueries,Troubleshooting — Jonathan Lewis @ 5:10 pm BST Dec 7,2012

When doing updates with statements that use correlated subqueries, it’s important to make sure that your brackets (parentheses) are in the right place. Here are two statements that look fairly similar but have remarkably different results – and sometimes people don’t realise how different the statements are:

(more…)

November 21, 2012

Plan Order

Filed under: Execution plans,Oracle,subqueries — Jonathan Lewis @ 6:53 pm BST Nov 21,2012

The previous post reminded me of another (fairly special) case where the order of operations in an execution plan seems to be wrong according to the “traditional” strategy for reading execution plans. Here’s a simple select statement with its execution plan to demonstrate the point:

(more…)

November 19, 2012

Plan timing

Filed under: Execution plans,Oracle,subqueries — Jonathan Lewis @ 9:59 pm BST Nov 19,2012

I’ve copied the following question up from a recent comment because it’s a useful generic type of question, and I think I can answer it in the few minutes I have to spare.

Hi Jonathan. I have a query plan where I cannot explain how time adds up. I did the ALTER SESSION trick but it changed nothing. I ran this, and got the following plan. I have two questions (I appoligize for not being able to format this code and plan but I saw not formatting buttons on the insert box).

1) @ step #8 in the plan, the query jumps to 3 and 1/2 minutes. This step says VIEW but gives no indication of what it did that actually took 3 and 1/2 minutes. Can you explain or give me some idea how to find out what is being done on this line that takes that long. Especially with so few rows.

2) the total elapsed time of the query was 3 minutes 45 seconds. This jives with step #1 that says 3:44.54. But if A-TIME is supposed to be among other things, the sum of child steps, then how to we explain step #2. Step #2 says 3 minutes 3 minutes 44 seconds. But the sum of its child steps (#3 and #8) is7 minutes and 25 seconds. Did this query do these steps “in parallel” so to speak? Or is there something really off in the numbers and if so how to I prove it?

This is not something that is super pressing but I sure would like to be able to explain the differences to people. It is making it somewhat harder to get people to believe I know what I am doing when I cannot such obvious issues as these two things.

Thanks, Kevin Meade

Fortunately Kevin included enough information to allow me to understand what was going on. In this case that means: “Here’s the query and the execution plan pulled from memory – showing the execution statistics”. As follows:

(more…)

October 19, 2012

count(*)

Filed under: Execution plans,Oracle,Performance — Jonathan Lewis @ 6:48 pm BST Oct 19,2012

I came across a nice bit of code on OTN recently that someone had written several years ago (in 2007, in fact)  to demonstrate the different ways in which the optimizer would handle “select count({something}) from table;”. If you want to copy and repeat the test code, you may need to adjust it slightly – it references a type vc2s, which I changed to dbms_stats.chararray, and it references a plan table called toad_plan_table, which I replaced with references to the standard plan_table (getting rid of the truncate as I did so).

The code simply executes a call to explain plan for different statements, then extracts the projection information for the “sort aggregate” line of the execution plan (examining the operation and options columns to do so). The thing that particularly surprised me was that there was a difference between the following two queries:
(more…)

August 9, 2012

Debugging

Filed under: Bugs,Execution plans,Oracle,subqueries — Jonathan Lewis @ 7:04 pm BST Aug 9,2012

One of my recent assignments involved a company that had run into some performance problems after upgrading from 10.2.0.3 to 11.2.0.2. We had spent half an hour on the phone discussing the system before I had arrived, and I’d made a couple of suggestions that had solved most of their problems before I got on site – but they still wanted me to come in and give them some specific ideas about why the critical part of the solution had helped.

The most critical piece of advice I had given them (after listening very carefully to their description of the system) was to get rid of ALL the histograms they had on their system, and then watch very carefully for any signs that they might need to re-introduce a handful of histograms over the next few weeks.

One of their critical queries completed in less that 2 seconds when histograms were removed, but took 33 seconds to complete when histograms were in place. With their permission, the following notes record my investigation of this puzzle, the underlying Oracle bug (possibly not yet documented) that caused it, and the optimum workaround that was available to them.
(more…)

« Previous PageNext Page »

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,877 other followers