Bits and Pieces

This is simply a list of useful (or just plain interesting) links that I have accumulated over time. The criterion for their appearance on the list is simply that when I read them I either decided they were interesting, or that I’d probably want to read them again some time. I’ve finally decided to tidy up the list a bit and post it – but it has a flaw: when I was collecting URLs I didn’t take a note of the date I read something so some of the useful information may have been superceded by newer versions of Oracle or a better workaround to a problem. So if you do follow a link, remember my “Rules of Trust”.

For future links, and with some retrospective additions as I re-read and transfer articles from the currently unpublished part of my list, the date at the end of each line will be the date it was added to the list. In some cases, of course, the date of publication is included in the URL itself. To make it easier to see if there’s anything new since the last time you visited I’ll put the last update date at the top of the page then mark the latest updates with *** as a tag you can search for (obviously I’ll delete the *** from previously marked entries).

Oracle

Column projection and merge overheads – Alexander Anokhin (July 2012)

Surprising benefits of “not null” predicate – Alexander Anokin (Nov 2013)

Rman for beginners (index page) – Norman Dunbar (Nov 2012)

Real-time SQL monitoring – Tim Hall (August 2011)

An intertesting ASH query – John Beresniewicz by way of Doug Burns
Adaptive Thresholds part 1 – Doug Burns (Nov 2012)
Adaptive Thresholds part 2 – Doug Burns (Nov 2012)
Adaptive Thresholds part 3 – Doug Burns (Nov 2012)

Virtual column problem 11.2 – Karen Morton (June 2011)

Dynamic Sampling damaged in 11.2 – Randolf Geist (June 2011)
Empty tables, indexes and Dynamic Sampling (11.2) – Randolf Geist (Feb 2012)

“Sparse” nested loop join costs from 10.1 – Randolf Geist (May 2012)

Cost is time: latest implementation – Randolf Geist (July 2011)

Some edge cases with frequency histograms – Randolf Geist

Logical I/O Part 1 – Randolf Geist
Logical I/O Part 2 – Randolf Geist
Logical I/O Part 3 – Randolf Geist

AIX and the 11g memory_target 

adrci (Automatic Diagnostic Repository Command Interpreter) in 11g – An introduction by Uwe Hesse (June 2011)

Saving Optimizer Stats – Doug Burns

Automatic Stats Collection  failing – 1 – Martin Widlake
Automatic Stats Collection Failing – 2 – Martin Widlake
Automatic Stats Collection Failing – 3 – Martin Widlake

Optimizer default values (10.1) – from the 10g manuals, values used if you haven’t collected stats on a table or index (watch out for distributed queries).
Optimizer Features listed ( X$QKSFM) internally – Julian Dyke

Counting a collection using ODCI as a cardinality fix – Adrian Billington

Partitioned tables with partly unusable indexes in 11.1 (_optimizer_table_expansion) – Christian Antognini

Parallel distribution costs – Christian Antognini

Join Elimination and subquery coalesce – Christian Antognini

Join factorisation a new optimizer features in 11.2, by TimurAkmadeev
Subquery Coalescing a new optimizer features in 11.2, by TimurAkmadeev
Subquery Unnesting bug in 11.2 – Timur Akhmadeev
A closing comment explains the meaning ot “type”  in the 11.2 trace file for CLOSE #N lines – Timur Akhmadeev
Obsolete Cursors – pt.1 – Timur Akhmadeev
Obsolete Cursors – pt.2 – Timur Akhmadeev

An example of a result cache problem caused by the single latch – Dominic Brooks
Overview of the Result Cache – Arup Nanda in Oracle Magazine

Wait event documentation (w.i.p.) – Kyle Hailey

Great discussion (in the comments) on 11g and Mutexes – Tanel Poder

Attaching plans to statements in 11g using SQL Plan Management in 11g, by the Optimizer Development Group

Problems with SQL Baselines – Dominic Brooks

Parallel Execution – by Greg Rahn, includes an important addition to monitoring in 11.1. (Dec 2009)

Parallel problem with ODP.net  – by Doug Burns (Oct 2012) ***

Range Partitioned hash clusters (11.2) – Iggy Fernandez

adaptive direct path reads( 11.2)  things that affect Oracle’s decision to use it, by Alex Fatkulin

The new, better Oradebug in 11g – Tanel Poder’s reference
Oracle Diagnostic Events – Miladin Modrakovic’s Hotsos presentation
A better sql_trace in 11g – Miladin Modrakovic

Hanganalyze – Arup Nanda
Related to Hanganalyze above – Virag Sharma

Latching on the root block of an index – Tanel Poder (from oracle-l)
Contention for index leaf blocks – Tanel Porder (from oracle-l)

Rowsource Operations listed in 11g – Tanel Poder

Automatic Memory Management Internals – Tanel Poder

who has what privileges and how they got them, and the SQL for several other security reports from Pete Finnigan

Bulk binds in streams 11.2 – but it seems to be broken – by Pythian.

Control File Reads – one reason (and changes in behaviour with version), a thread on the OTN database forum
 CF Enqueues and Control file reads – a bug fix reported by Timur Akhmadeev

Linux Large Pages – pt.1 – Kevin Closson
Linux Large Pages – pt.2 – Kevin Closson

Notes on parameter settings for large pages in 11.2.0.2 – OTN (Nov 2012)

Free memory, swap, Oracle and Everything – Video presentation by Chriso Kutrovsky of Pythian (Nov 2012)

Log file sync – Kevin Closson
Log file sync – Riyaz Shamsudeen
Log file sync – Christian Bilien

Some interesting stuff on SGA heap dumps – Riyaz Shamsudeen
More on the SGA and free memory – Riyaz Shamsudeen

Plan Hash Value – the final say ? – Riyaz Shamsudeen
Same plan hash value different plan pt.1 – Randolf Geist
Same plan hash value different plan pt.2 – Randolf Geist

Most popular posts – Arup Nanda’s list of his most frequently read blog posts – includes 10g, 11g, Linux, Security and Exadata series (Nov 2012)

Block Change Tracking – Kyle Hailey, with links to Alex Gorbachev
Block Change Tracking – Alex Gorbachev’s original pdf file

Rolling Cursor Invalidation – Harald van Breederode

Introduction to CollectL monitoring tool – Martin Bach
Collectl home page on Sourceforge

Some useful comments  on database resident connection pooling (DRCP) – an OTN thread

A cute way of setting up Advanced Queues  (AQ) to cause jobs to run on user events – an OTN thread

11.2.0.1 procedure for calculating sql_id from sql_text – check the comments on the post.
The SQL_ID and the hash_value – Tanel Poder

Oracle Database 11g Release 2 RAC On Linux Using VirtualBox – Tim Hall

Oracle Database 11g Release 2 RAC On Linux Using VMware Server 2 – Tim Hall
Oracle 11g Release 1 RAC On RHEL5 Linux Using VMware ESX Server and NFS – Tim Hall

Cloning an Oracle image – Niall Litchfield

A case study about choice of index, starting with tkprof output – a thread on OTN

A discussion regarding a query with “exists” and “not exists” subqueries – a thread on OTN

Some informative comments about OPATCH – a thread on OTN

How to review a test case – a thread on OTN
A reminder about how to test – (also some notes on auto-allocation with mult-file tablespaces) – a thread on OTN

How to ask a tuning question on OTN – Randolf Geist’s template
When your query takes too long –  OTN advice by Van Wijk

Scripts to download Oracle  documentation pdf files – Christian Antognini

Oracle Learning Channel on YouTube

Cost Based Oracle – Fundamentals in Russian – Official publication and sales

How to find interesting Oracle Patents – Timur Akhmadeev

Reference list of published papers on optimisation – Oracle (Nov 2012)

Metalink (Needs an account)

Block Change Tracking – Known Issues MOS doc 2836206.1.

A list of notes on statistics – I haven’t checked all the target locations.
A master list of MOS notes on RAC et. al.  – same comment on targets as above
Lookup tool for ORA-00600 and ORA-07445 errors

Interesting bits about archive_lag_target (10.2)

Comparing Enterprise to Standard Edition (11.2) – from the documentation
Comparing Enterprise to Standard Edition (10.2) – from the documentation

General Interest

Little’s Law (queue theory) – Wikipedia

Beware of “experts” using weasel Words

Bad Science – a TED talk by Ben Goldacre

Expensive SANs never fail ! (1) – by Pythian

Expensive SANs never fail ! (2) – by Pythian

Bubbles in I/O latency (device queues)

Important Statistical Distributions – an introduction by Craig Shallahamer

Bayesian Statistics, an introduction. (Bayesian statistics is a formal way of combining prior beliefs with observed facts into posterior beliefs, and ultimately, informed decisions.)

A useful description of SSD and flash memory.
More on SSD – Gwen Shapira

Some key numbers of hardware performance.

Quantum Electrodynamics – video of Richard Feynmann giving the Douglas Robb Memorial Lectures at the University of Auckland.

A sense of perspective.

The periodic table of elements – Tom Lehrer (with supporting animation)

The Timezone database