Connect By

If you have to deal with hierarchical queries (or “connect by” queries, as they’re commonly known) you might be interested in this note on the OTN Forum where someone has a problem with repeated executions of the same query (same inputs, same results, same execution plan) operating at wildly different speeds with amazing changes in workload.

Ultimately the solution was the same as for the more obvious question: “why does my sort sometimes take much longer than usual?”  – shortage of memory (in this case a very low setting for the pga_aggregate_target).

This example is an interesting variation, though, as it demonstrates how the availability of resources can make Oracle choose to execute different parts of an execution plan – giving another reason for the “conditional plan” strategy I’ve described in the past.

There’s an interesting post on hierarchical queries on Christian Antognini’s blog (which I’ve also referenced from the OTN posting)

Explain VIEW

A brief note on reading execution plans.

------------------------------------------------------------
| Id  | Operation                     | Name    | Time     |
------------------------------------------------------------
|   0 | SELECT STATEMENT              |         | 00:00:17 |
|*  1 |  HASH JOIN                    |         | 00:00:17 |
|   2 |   VIEW                        | VW_SQ_1 | 00:00:01 |
|   3 |    HASH GROUP BY              |         | 00:00:01 |
|   4 |     TABLE ACCESS FULL         | EMP     | 00:00:01 |
|   5 |   VIEW                        |         | 00:00:17 |
|   6 |    TABLE ACCESS BY INDEX ROWID| EMP     | 00:00:17 |
|   7 |     INDEX FULL SCAN           | E_D     | 00:00:01 |
------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPT_NO"="OUTER"."DEPT_NO")
       filter("OUTER"."SAL">"VW_COL_1")

Glossary

When writing about Oracle, people often use expressions whose meanings aren’t necessarily well-known to the less experienced user, so I’ve started building a glossary of commonly used Oracle terms. It’s going to take some time to build and it will keep growing as time passes. To make it easy to get to the page, I’ve added a special link near the top of the options column (in the “Special Links” list).

If you have any suggestions for terms feel free to add them in the comments. Please stick to one term per comment so that I can delete the comment after I’ve added the term to the glossary. As a guideline, you should be able to fit the term into a sentence like: “what is an X” rather than “how does X work.”

Tablespaces

This could nearly be one for the “ancient history” series, because it starts with a quote from that marvellous book “Practical Oracle 8i”, where I wrote in Chapter 8:

Tablespace names are never removed from the data dictionary (cluster SYS.C_TS#), and by default each tablespace entry occupies a minimum of one block in this cluster, even if it is a locally managed tablespace rather than a dictionary-managed tablespace. 

Since SMON scans TS$ every five minutes you need to avoid letting this table get too large.Your options are (1) hack the SQL.BSQ table that creates the data dictionary to reduce the cluster size in the C_TS# cluster – but make sure you get official approval from Oracle Support first, and (2) recycle tablespace names as much as possible and avoid using ‘time-related’ tablespaces names, especially if you are taking advantage of transportable tablespaces .

A few days ago, I got an email from Tom Kyte, because someone had written to AskTom asking him to throw more light on this comment.

Clean it up

There is some confusion about the expression “clean” in Oracle circles, so I thought I’d write a short note to explain the different ways in which the word may be applied to Oracle blocks. There are five terms to consider:

1. clean
2. commit cleanout
3. block cleanout
4. delayed block cleanout
5. delayed logging block cleanout

Undocumented Hints

The bits of Oracle which aren’t documented always seem to be the bits that are hard to resist, so I thought I’d make a brief comment on undocumented hints.

Of course, you should not take advantage of any undocumented feature without first getting approval from Oracle support, but some hints seem to me to fall into a special category where you are more likely to get that approval – and here are my thoughts on why.

Consistent Gets – 2

How would you describe “db  block gets” and “consistent gets” in a couple of short paragraphs ? Having asked the question, I suppose I ought to offer my version of the answer up for scrutiny and dissection.

Before supplying my descriptions, I’d like to point out that there are (at least) two other mechanisms that Oracle uses to visit data blocks in the buffer cache: re-visiting pinned buffers, and doing a fast cleanout on commit. These buffer visits can be seen in the statistics: “buffer is pinned count” and “commit cleanouts successfully completed”. (The statistic “commit cleanouts” tells you how many times Oracle tried to do a commit cleanout and there are various statistics to explain the failures.)

Quiz Night

I was reading a blog earlier on this evening that was talking about the definitions of “consistent gets” and “db block gets”. The point of the article was that it’s very hard to find good descriptions of exactly what these two terms mean.

I’ll add the link to the blog item (and give my definitions) in a few days time but, in the interim, does anyone want to try their hand at defining these two very common terms in a couple of short paragraphs.

[Updated: 11th June] Just a brief note to tantalize a little more, did you know that :

•  You can visit blocks in the buffer cache without recording a consistent get or a db block get.
• You can CHANGE the current version of a block without recording a db block get (or even a consistent get – and without generating undo or redo).
• Consistent gets can create versions of a block that have NEVER, EVER, existed at any point in time (or, much the same thing, at any SCN).

[Updated: 12th June] I’ve published my definitions in a separate note. But this is the link to the blog that started this thought running.

Book content

Rob Freeman raised an interesting topic on Oracle-L a couple of weeks ago with the following:

My question is, what constitutes Oracle Book Writing mal-practice (and I pray I’ve never committed it).  Certainly mistakes crop up in books all the time, I’m as guilty as any writer of this. This chapter I’m reading though, in an effort to get the reader to doing something quickly, does not lay any foundation, skips critical steps and actually prompts them to do what I consider some very dangerous things.

The posting didn’t really generate a lot of discussion – which is a shame – and my privileges to write to Oracle-L lapsed some time ago, so I’m writing my response to Rob’s observations here.

PGA leaks

Here’s a simple script that I created a short time ago while investigating a memory problem on a client site. The purpose of writing the script was, as always, to strip the client’s code back to a bare minimum in an attempt to work out the root cause of a problem. (Warning: if you want to run this script, your Oracle shadow process will grab about 1GB of PGA RAM )

Online Rebuild

Here’s a little oddity that may be waiting to catch you out – but only if you like to create indexes with very long keys.

create table t1(
	v1	varchar2(4000),
	v2	varchar2(2387),
	v3	varchar2(100)
);

create index t1_i1 on t1(v1, v2);

alter index t1_i1 rebuild;
alter index t1_i1 rebuild online;

My key value is at the limit for an 8KB block size in Oracle 9i and later – which is roughly 80% of (block size - 190 bytes). In earlier versions of Oracle the limit was roughly half that (i.e. 40% rather than 80%). 

Health Check

Someone recently posted a question on the OTN Database General forum with the title like “Health Check on Oracle database” but gave no really solid context to indicate the type, purpose, or frequency of activity that they had in mind.

I can think of three or four possible interpretations for what he was saying – and this made me wonder what possible interpretations other people might have.

I won’t link to the specific thread just yet – someone has already asked “What do you mean by an Oracle health check” and if the OP comes up with a response I wouldn’t want to give too much of a lead to other people before they come up with their own interpretations.

So the question is this: in the complete absence of any sensible context, if you overheard a stranger talking about an Orace health check, what sort of activity do you think they might have in mind ?

Cache usage

There’s a thread on OTN that started life with buffer busy waits,  multiple db writer processes and multiple block sizes and then started to get interesting.

It’s worth reading if you’re interested in some of the internal mechanisms of how Oracle handles the buffer cache, working data sets, and LRU chains with their respective latches.

It even includes a couple of queries against dynamic performance views (or rather the X$ equivalents): x$kcbwbpd (buffer pools) and x$kcbwds (working data sets)

Shared Server

Original Note: 3rd June 2009

It’s not uncommon to hear people describing a database or instance as “being shared server”, or “being dedicated server” – and I sometimes get the impression that some DBAs think that enabling shared servers (or multi-threaded servers – MTS – as the feature used to be known) makes it impossible for connections to use dedicated servers.

So, for reference, here’s a short thread on the OTN database forum that makes a couple of useful points about shared servers (with a particular reference to the way the feature is used in 11g).

Fixed Stats

There was a question about the notorious dba_extents view on the comp.databases.oracle.server newsgroup a little while ago.

This is a view that has been popularly abused over the years despite warnings such as a note of mine in 2006 and even back as far as 2001 this one from Connor McDonald.

There are many reasons why this is a nasty view – and the number of reasons has grown as Oracle has evolved – but David FitzJarell has highlighted an important point in one of his blog postings that was worth a mention. So here it is.