Upgrades

Here’s a link to a truly ambitious document on Metalink (if you’re allowed to log on):

Doc ID 421191.1: Complete checklist for manual upgrades of Oracle databases from any version to any version on any platform

(Actually it only starts at v6 – but I don’t think there are many systems still running v5 and earlier).

Star Transformation – 2

After publishing my note about emulating star transformations when there was something blocking the optimizer’s ability to do them automatically I ended up having an interesting email conversation with Mark Farnham about variations on the theme, concluding with a completely different (and possibly more intuitive) SQL statement to achieve the same aim.

Helpdesk

How to ensure that you never give the wrong answer – as demonstrated by my bank’s telephone help system:

Stage 1 – after calling in through a special high-cost number, of course:

“{long message extolling the virtues of using the bank’s internet system for all your requirements}”

Stage 2 – you wait for the list of options to be recited

“Press 1 for …”
“Press 2 for …”
…
“Press 9 to speak to a member of the helpdesk”

Stage 3 – you decide the only relevant option is to press 9 to speak to a person:

“I’m sorry, I don’t understand what you want.” click, brrrrrrrrrrrrr …

Maxim: If you don’t want to fail, don’t even try.

Star Transformation

A little while ago I published a note explaining how it was possible to find queries which ran faster if you manually de-coupled the index and table accesses. Here’s a further example that came up in discussion on a client site recently. The query looks something like this (at least in concept, although it was a little more complex, with some messy bits around the edges):

Cursor_sharing

For those looking to the next upgrade – here’s an early warning from Oracle:

ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’ setting (Doc ID 1169017.1)

“We recommend that customers discontinue setting cursor_sharing = SIMILAR due to the many problematic situations customers have experienced using it. The ability to set this will be removed in version 12 of the Oracle Database (the settings of EXACT and FORCE will remain available). Instead, we recommend the use of Adaptive Cursor Sharing in 11g.”

The rest of the note contains some interesting information about the behaviour and side effects of this option – which may also help you debug some library cache issues if you’re currently running with this value set in 11g.

Evidence

Here’s a nice example on the OTN database forum of Dom Brooks looking at the evidence.

• The query is slow – what does the trace say.
• There’s “row source execution” line that says we get 71,288 rows before doing a hash unique drops it to 3,429 rows.
• There’s a statement (upper case, bind variables as :Bn) in the trace file that has been executed 71,288 times
• A very large fraction of the trace file time is in the secondary statement
• There’s a user-defined function call in the original select list, before a ‘select distinct’.

Conclusion: the code should probably do a “distinct” in an inline view before calling the function, reducing the number of calls to the function from 71,288 to 3,429.

Footnote: There may be other efficiency steps to consider – I’m always a little suspicious of a query that uses “distinct”: possibly it’s hiding an error in logic, possibly it should be rewritten with an existence subquery somewhere, but sometimes it really is the best strategy. There’s are some unusual statistics names coming from autotrace in the OP’s system – I wonder if he’s installed one of Tanel Poder’s special library hacks.

More CR

Following on from yesterday’s post on consistent reads, I thought I’d make the point that the way you work can make an enormous difference to the amount of work you do. Here’s a silly little demo (in 10.2.0.3):

Consistent Reads

Here’s a quick demo to make a point about consistent reads (prompted by a question on the Oracle-L mailing list):

Deadlock

Here’s an example of a slightly less common data deadlock graph (dumped from 11gR2, in this case):

Rows per block

A recent question on the OTN database forum:

Can any one please point to me a document or a way to calculate the average number of rows per block in oralce 10.2.0.3

One answer would be to collect stats and then approximate as block / avg_row_len – although you have to worry about things like row overheads, the row directory, and block overheads before you can be sure you’ve got it right. On top of this, the average might not be too helpful anyway. So here’s another (not necessarily fast) option that gives you more information about the blocks that have any rows in them (I picked the source$ table from a 10g system because source$ is often good for some extreme behaviour).

Stats collection

From time to time I see people asking how they can check how far their system has got in it’s call to collect stats. The question is often a little ambiguous – they know which call to dbms_stats they’ve used, so they know whether they’re trying to monitor stats collection against a single table (taking advantage of v$session_longops springs to mind) or against an entire schema.

Here’s one simple-minded approach that I whipped up a few years ago – it’s possible you could do better with the latest versions of dbms_stats. Its purpose is simply to give you some way of checking what work dbms_stats needs to do (in this example, for a schema), so that you can check how much has been done and how much remains. The basic method can be modified in various ways to match your tastes.