Oracle Scratchpad

February 13, 2013

STS, OFE and SPM

Filed under: dbms_xplan,Execution plans,Hints,Oracle,Upgrades — Jonathan Lewis @ 9:19 am GMT 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 GMT 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 GMT 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 GMT 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 GMT 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 GMT 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 GMT 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 GMT 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…)

July 16, 2012

ANSI Outer 2

Filed under: ANSI Standard,CBO,Execution plans,lateral view,Oracle — Jonathan Lewis @ 4:55 pm GMT Jul 16,2012

A comment on a recent post of mine pointed me to a question on the OTN SQL and PL/SQL Forum where someone had presented a well-written test case of an odd pattern of behaviour in ANSI SQL. I made a couple of brief comments on the thread, but thought it worth highlighting here as well. The scripts to create the required tables (plus a few extras) are all available on OTN. If you create only the four tables needed and all their indexes you will need about 1.3GB of space.

The core of the problem is this: there is a three table join which does a hash join involving an index fast full scan on a particular index; when you add a fourth table to the join this fast full scan turns into a full tablescan for no obvious reason. Here are the queries, with the plans that I got when running 10.2.0.3. (My final plan is slightly different from the plan shown on OTN – I have a right outer hash join to the last table where the OP had a nested loop outer – but the difference is not significant). The queries, with their execution plans, are below- the three table join first:
(more…)

July 12, 2012

PK Problem

Filed under: Execution plans,Oracle — Jonathan Lewis @ 4:18 pm GMT Jul 12,2012

I have been saying for a very long time (probably since some time in the last century) that if you want to add a primary key (or unique) constraint to a table where there might be some duplicate data then one of the best strategies for doing so might be to create a non-unique index (with the online option), then add the constraint in the state enable novalidate, and then validate the constraint. For example:

create table t1
as
select
	*
from
	all_objects
;

create index t1_i1 on t1(object_id) online;
-- collect stats
alter table t1 add constraint t1_pk primary key(object_id) enable novalidate;
alter table t1 modify constraint t1_pk validate;

(more…)

June 8, 2012

Unique Fail

Filed under: Bugs,CBO,Execution plans,Oracle,Statistics — Jonathan Lewis @ 5:54 pm GMT Jun 8,2012

As in – how come a unique (or primary key) index is predicted to return more than one row using a unique scan. Here’s and example (running on 10.2.0.3 – but the same type of thing happens on newer versions):
(more…)

June 4, 2012

Subpartition stats

Filed under: CBO,Execution plans,Oracle,Performance,Tuning — Jonathan Lewis @ 7:07 am GMT Jun 4,2012

You might have expected the following query ought to run reasonably efficiently, after all it seems to be targeted very accurately at precisely the few rows of information I’m interested in:

select
	column_name,
	avg_col_len
from
	dba_subpart_col_statistics
where
	owner             = 'TEST_USER'
and 	table_name        = 'TEST_COMP'
and	subpartition_name = 'P_MAX_D'

(more…)

May 24, 2012

Subquery Factoring

Filed under: Execution plans,Hints,Oracle,Subquery Factoring,Tuning — Jonathan Lewis @ 6:37 pm GMT May 24,2012

I have a small collection of postings where I’ve described anomalies or limitations in subquery factoring (the “with subquery”, or Common Table Expression (CTE) to give it the official ANSI name). Here’s another example of Oracle’s code not behaving consistently. You may recognise the basic query from yesterday’s example of logical tuning – so I won’t reprint the code to generate the data sets. This examples in this note were created on 11.2.0.2 – we start with a simple query and its execution plan:
(more…)

March 12, 2012

First_rows hash

Filed under: Bugs,CBO,Execution plans,Oracle — Jonathan Lewis @ 1:49 am GMT Mar 12,2012

Just like my posting on an index hash, this posting is about a problem as well as being about a hash join. The article has its roots in a question posted on the OTN database forum, where a user has shown us the following execution plan:

---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                         |    14 |   896 |    63   (7)| 00:00:01 |
|   1 |  SORT GROUP BY                |                         |    14 |   896 |    63   (7)| 00:00:01 |
|   2 |   NESTED LOOPS                |                         |       |       |            |          |
|   3 |    NESTED LOOPS               |                         |    14 |   896 |    62   (5)| 00:00:01 |
|*  4 |     HASH JOIN                 |                         |    14 |   280 |    48   (7)| 00:00:01 |
|   5 |      VIEW                     | V_SALES_ALL             |   200 |  1800 |     4   (0)| 00:00:01 |
|   6 |       UNION-ALL               |                         |       |       |            |          |
|   7 |        INDEX FAST FULL SCAN   | PRODUCTS_DATES_IDX      |   100 |   900 |     2   (0)| 00:00:01 |
|   8 |        INDEX FAST FULL SCAN   | PRODUCTS_DATES_IDX_HARD |   100 |   900 |     2   (0)| 00:00:01 |
|*  9 |      VIEW                     | index$_join$_003        |  2238 | 24618 |    44   (7)| 00:00:01 |
|* 10 |       HASH JOIN               |                         |       |       |            |          |
|* 11 |        INDEX RANGE SCAN       | PRODUCTS_GF_INDEX2      |  2238 | 24618 |     6   (0)| 00:00:01 |
|  12 |        INDEX FAST FULL SCAN   | PRODUCTS_GF_PK          |  2238 | 24618 |    45   (3)| 00:00:01 |
|* 13 |     INDEX UNIQUE SCAN         | DATES_PK                |     1 |       |     0   (0)| 00:00:01 |
|  14 |    TABLE ACCESS BY INDEX ROWID| DATES                   |     1 |    44 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

(more…)

March 4, 2012

Count(*)

Filed under: Execution plans,Oracle,Performance,trace files — Jonathan Lewis @ 5:41 pm GMT Mar 4,2012

A recent posting on the comp.databases.oracle.server newsgroup pointed me to a rather elderly Ask Tom question (originally posed in July 2004, last updated June 2011) where Tom produced an extraordinary observation. The response times for the following two queries are completely different (on Oracle 9.2 on his data set):

(more…)

« Previous PageNext Page »

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,521 other followers