Oracle Scratchpad

August 10, 2010

Joins – HJ

Filed under: CBO,Execution plans,Performance — Jonathan Lewis @ 6:43 pm BST Aug 10,2010

In the second note on my thesis that “all joins are nested loop joins with different startup costs” I want to look at hash joins, and I’ll start by going back to the execution plan I posted on “Joins – NLJ”. (For a quick reference list of URLs to all three articles in turn, see: Joins.)
(more…)

July 30, 2010

Scalability Conflict

Filed under: Indexing,Infrastructure,Performance,Troubleshooting — Jonathan Lewis @ 5:51 pm BST Jul 30,2010

Here’s an example of how you have to think about conflicts of interest when dealing with problems of scalability. It starts with a request (that I won’t give in detail, and was a little different from the shape I describe below)  from a client for advice on how to make a query go faster.

Basic problem: the query runs about 20 times per second, returning a very small number of rows; it’s basically a very simple “union all” of three query blocks that access the same table in slightly different ways.

(more…)

June 29, 2010

Subquery Factoring (3)

Filed under: CBO,Execution plans,Oracle,Performance,Subquery Factoring,Troubleshooting — Jonathan Lewis @ 6:28 pm BST Jun 29,2010

From time to time I’ve warned people that subquery factoring should be used with a little care if all you’re trying to do is make a query more readable by extracting parts of the SQL into “factored subqueries” (or Common Table Expressions – CTEs – if you want to use the ANSI term for them). In principle, for example, the following two queries should produce the same  execution plan:
(more…)

June 27, 2010

Coalesce

Filed under: Index Rebuilds,Indexing,Infrastructure,Oracle,Performance,redo — Jonathan Lewis @ 6:36 pm BST Jun 27,2010

The following question came up in an email conversation a little while ago:

Are you aware of any problems a large oltp site might have with running index coalesce during production hours, as opposed to doing index rebuilds in a maintenance window?

(more…)

June 2, 2010

Testing

Filed under: Performance,Troubleshooting — Jonathan Lewis @ 7:20 pm BST Jun 2,2010

The old chestnut of “optimal block size” came up on OTN again a few weeks ago, with someone asking for advice on how to do some testing to decide on the optimal block size for a database. The correct answer to this question is you don’t: you assume you are going to use the default size for your platform and then think about whether there are any very specific jobs that your application does that might gain some sort of worthwhile benefit if you used a non-default size.

Nevertheless, the OP came back some time later with a few results which suggested that some of his tests showed that a 4KB block size gave significantly better performance than the same tests using 8KB and 16KB block sizes.

But there’s a problem with the conclusion. If you examine the results carefully, and think about what type of work must happen in the tests, you realise that this particular test was not about the blocksize – it was about the network and the client program. (I haven’t included a link to the posting where I explained this – it’s just a little later in the same thread. This is just to give you the option of working out why the test is wrong before you read my comments about it.)

Update 18th Aug 2010

The investigation continues – with the OP comparing the resultsof using a table with a single 2000 byte column to a table with many columns with an similar total size. Again, though, the anomaly in timing he is chasing seems to be about network traffic time, NOT about database block size.

(I’ve only sent one reply to this thread at the moment, but the OP has been good at supplying extra data in the past, so the discussion may evolve to produce further interesting information.)

May 18, 2010

double trouble

Filed under: Execution plans,Performance,Tuning — Jonathan Lewis @ 7:06 pm BST May 18,2010

In the latest Quiz Night, I asked how you could make a query more efficient by changing a two table join into a three table join – with the clue that my third table was a repeat of the first table. Gary Myers, in comment 4,  provided the type of answer I was looking for. Sometimes it is more efficient to get a small amount of data from a table on a first pass then go back and get the rest of the data on a second pass – especially if the first pass is an ‘index only’ operation.
(more…)

May 15, 2010

Contention

Filed under: humour,Oracle,Performance,Troubleshooting — Jonathan Lewis @ 10:44 am BST May 15,2010

There was a news item in the UK last week about a man in Chideock, Dorset who staged a protest about the volume of heavy traffic that has to come through the village where he lives.

A pedestrian crossing has recently been installed on the road, using traffic-light control. So one morning he decided to cross the road, and then come back again, non-stop, for an hour. Each time he got across the road he pressed the crossing control button to come back. A few vehicles got through on the green light each time, but after just one hour he had caused a four mile tailback of traffic.

Let this be a lesson to Oracle DBAs and developers – even a small job, if repeated very frequently, can create havoc with your system.

May 14, 2010

Quiz Night

Filed under: Oracle,Performance — Jonathan Lewis @ 6:55 pm BST May 14,2010

I have two queries designed to return the same result set in the same order. In outline they look like this (look carefully at the from clauses):

select  ...
from    tableA  t1,
        tableB  t2
where
        t1.filter = ...
and     t2.join = t1.join
and     t2.filter = ...
order by ...


select  ...
from    tableA  t1,
        tableB  t2,
        tableA  t3
where
        t1.filter = ...
and     t2.join = t1.join
and     t2.filter = ...
and     ...  -- to be continued
order by ...

How did I manage to take the first query and make it more efficient by turning it from a two-table join to a three-table join ?

May 7, 2010

SQL*Net compression

Filed under: Infrastructure,Performance,Troubleshooting — Jonathan Lewis @ 7:21 pm BST May 7,2010

Here’s a little demonstration I’ve been meaning to write about for the last few years – it’s very simple: create a table, then query it a couple of times.
(more…)

April 26, 2010

DW Introduction

Filed under: Infrastructure,Oracle,Performance,Tuning — Jonathan Lewis @ 4:28 pm BST Apr 26,2010

Greg Rahn has been writing a short series on “Core Performance Fundamentals of Oracle Data Warehousing”. Here’s his catalogue of the first four or five articles in the series.

April 5, 2010

Failed Login

Filed under: audit,Infrastructure,Oracle,Performance,Troubleshooting — Jonathan Lewis @ 7:59 pm BST Apr 5,2010

Here’s a piece of code I found recently running every half hour on a client site:

SQL_ID = 2trtpvb5jtr53
SELECT
        TO_CHAR(current_timestamp AT TIME ZONE :"SYS_B_0", :"SYS_B_1") AS curr_timestamp,
        COUNT(username) AS failed_count
FROM
        sys.dba_audit_session
WHERE
        returncode != :"SYS_B_2"
AND     TO_CHAR(timestamp, :"SYS_B_3") >= TO_CHAR(current_timestamp - TO_DSINTERVAL(:"SYS_B_4"), :"SYS_B_5")

(more…)

March 25, 2010

Index too big

Filed under: Infrastructure,Oracle,Performance,Troubleshooting — Jonathan Lewis @ 8:50 pm BST Mar 25,2010

I thought I’d posted this a couple of years ago – but maybe it was something I put on the OTN database forum in response to a question. If it was, the same (or similar) question has recently appeared.  “How come my index is so big when there’s no data in the table ?”

(more…)

March 20, 2010

Not KEEPing

Filed under: Infrastructure,Performance,Troubleshooting — Jonathan Lewis @ 9:10 am BST Mar 20,2010

I’ve recently spent some time working with a client to get the maximum benefit from their KEEP pool – I’ll be publishing some interesting demonstrations when I get some time – and thought that some of you would be keen to hear about bug 8897574.

The bug applies to 11.1.0.7 – luckily my client is still on 10.2 – and has the following abstract: KEEP BUFFER POOL DOES NOT WORK.

(more…)

January 12, 2010

force_match

Filed under: Infrastructure,Performance — Jonathan Lewis @ 7:19 pm BST Jan 12,2010

If you have looked at SQL Profiles (see for example Kerry Osborne’s blog) then you may have come across the force_match option for enabling or importing a SQL profile. I received an email recently asking a few questions about this feature. (more…)

January 10, 2010

first_rows_N again

Filed under: CBO,Performance,Tuning — Jonathan Lewis @ 7:02 pm BST Jan 10,2010

If you run a query using first_rows_N optimisation you could run into a massive performance problem in cases where the optimizer thinks the complete result set is quite large when it is actually very small.

If both conditions are true the optimizer may choose a very resource-intensive execution path “expecting” to stop (or at least pause between fetches) after N rows – hoping to give the impression that it can respond very quickly – but find that the query has to run to completion because the N rows simply don’t exist.

(more…)

« Previous PageNext Page »

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,528 other followers