JL Computer Consultancy

Index of articles on Miscellaneous Items


This index covers all the active items on the site. Eventually it will list only those items that do not fall into an obvious category.

Now available through RSS:  I don’t know how it works, but if you know how to use RSS, then go to http://feeds.feedburner.com/JonathanLewisArticles to get updates to this page through RSS. For further details on how this was set up see Eddie Awad’s article at http://awads.net/wp/2006/04/17/orana-powered-by-google-and-feedburner/

Now blogging at http://jonathanlewis.wordpress.com

Back to Home Page.


Title

Content

Last update

Trouble-shooting

An overview of the strategies for trouble-shooting an Oracle system.

3-Sep-2008

 

 

 

Buffer Flag

Interpreting the x$bh buffer header flag

22-Mar-2008

It’s the data

If you have several options for handling a problem, it’s the data that dictates the sensible choice.

1st Dec 2007

Trouble

A pl/sql package to take snapshots of session-based activity.

2nd Sept 2007

Statistics

Statistics – How precise, How Often

28th Aug 2007

Reporting Workloads

Capturing the workload of reports run by a report server

8th Aug 2007

Dual

The DUAL table – the sys account – and the not so obvious.

16th July 2007

Kiddy Scripts

Think carefully before you use a script from the internet on a production system

14th July 2007

Not Proof

A little lesson in testing

14th July 2007

x$kcbsw

A utility for analyzing logical I/O

18th Jan 2007

Using 10053

An example of bug hunting a problem with hints by using event 10053

18th Jan 2007

Workarea Sizing

Undocumented secrets – or untested fairytales

7th Jan 2007

Function-based indexes

There’s no such thing as a function-based index (Recreated from the Dizwell Wiki).

4th Jan 2007

How many Tables

What’s the maximum number of tables you can use in a single query (Recreated from the Dizwell Wiki).

4th Jan 2007

Pushing Predicates

A note on the hints push_pred and no_push_pred, and what it means to push predicates

7th Dec 2006

Autotuning

A pdf of the Powerpoint slides (2 per page handout format) of a presentation I gave at OpenWorld 2006

30th Oct 2006

Optimizer Enhancements

A pdf of the Powerpoint slides (2 per page handout format) of a presentation I gave at OpenWorld 2006

30th Oct 2006

Trending Statspack

Using analytic functions with statspack data

23rd Sept 2006

Uniqueness

What’s the cost of a unique constraint ?

13th Aug 2006

Statistical problem

Which advice to follow – humour.

13th Aug 2006

Cost

A taste of the detail that goes into boundary conditions for cost based optimisation.

13th Aug 2006

Hinted SQL

A note about hints – and an editor’s stroke of genius

13th Aug 2006

Pipeline Functions

Sometimes analytics may be too expensive

14th July 2006

Hash chains

Notes on the cache buffers chains (Recreated from the Dizwell Wiki)

23rd Jun 2006

Analytics

An article published in the UKOUG magazine – walking through the development of a non-trivial example.

23rd Jun 2006

Statspack

Another quick analysis of a statspack question.

7th Jun 2006

Subquery Factoring

A short article on subquery factoring (Recreated from the Dizwell Wiki)

6th Jun 2006

Trouble-shooting 101

An example of making guesses from a tkprof output.

22nd Apr 2006

Testing PX

Understanding parallelism, plans, and run-time activity

7th Apr 2006

2+2 = 5

Just because you’ve fixed a problem that doesn’t mean you understood what the problem was

7th Apr 2006

MV non-bug

Why test cases are so important, and so helpful

5th Apr 2006

Index usage

A clue to spotting indexes that aren’t used in queries – not that that lets you drop them safely.

1st Apr 2006

Podcast

I had an interesting time a couple of weeks ago, being interviewed through a Skype link by Dbazine.

27th Feb 2006

Review

And just for good measure, here’s Chris Foot’s review for Dbazine of my book

27th Feb 2006

Buffer Handles

An insight into how Oracle avoids cache buffers chains latches (Recreated from the Dizwell Wiki)

31st Jan 2006

Buffer Busy Waits

Causes and Cures of buffer busy waits (Recreated from the Dizwell Wiki)

26th Jan 2006

Meaning of errors

A simple NT utility to emulate the Unix oerr command

24th Jan 2006

Deterministic Functions

Finally, they’ve arrived – (Recreated from the Dizwell Wiki)

23rd Jan 2006

Research

The Snark research mechanism

7th Jan 2007

10gR2 Sorting

Faster sorts – or was it just a hint?

13th Jan 2006

V$rollstat

Create package to take snapshots of rollback segment activity

5th Jan 2006

10g Plan table

High precision cost.

30th Dec 2005

Outline Hacking

Ideas on how to manipulate outlines in 9i and above.

25th Nov 2005

Cache_advice

Some ideas on how cache advice might work

23rd Nov 2005

Log issues

Problems with log file parallel write waits and log file sync waits

14th Sept 2005

Top 5

It’s very easy to jump to unsuitable conclusions if you treat the Statspack “Top 5” too casually

14th Sept 2005

Partitioning Problems

Using partitioned tables in real life is not always straightforward. Previously published on www.dbazine.com

22nd Aug 2005

Rebuilding Indexes

Rebuilding indexes – Why, When, How

30th May 2005

Workload audit

How much work did a session do ?

29th May 2005

Pseudo-Science

Science and pseudo-science – bluffing with jargon.

21st May 2005

Index Efficiency 2

Is an index much bigger than it ought to be?

20th May 2005

Circular References

A method for propagating errors and creating myths

20th May 2005

Criticism

Is criticism unprofessional ?

26th Apr 2005

Proof

Can simple scripts prove anything about performance ?

15th April 2005

Bottlenecks

Sometimes you can fix a problem – and find that nothing really changes very much

5th April 2005

Debating Skills

If you want a sensible debate, shouldn’t you start with a sensible question?

5th April 2005

Rumsfeld

The Rumsfeld Box – observations and understanding (pdf file 42K)

27th Mar 2005

A Reader Writes

A contribution from our Spanish correspondent

20th Mar 2005

Credibility

Are credentials a substitute for credibility ?

15th Mar 2005

Testing

Why test cases are important

13th Mar 2005

A Review

In which we don’t trust - a review of silver bullets

13th Mar 2005

The Scientist

What is an Oracle scientist?

4th Mar 2005

Hidden Benefits

Small enhancements in 10g that could make a big difference. Previously published on www.dbazine.com

26th Feb 2005

Smashing Indexes

A demonstration of how badly b-tree indexes can degenerate. Previously published on www.dbazine.com

26th Feb 2005

Hit Ratios

Hit Ratios have their uses (Humour)

3rd Jan  2005

Constraints

An overview of constraints in three parts. . Previously published on www.dbazine.com

29th Dec 2004

Evolution

The Evolution of Optimization: 8i to 10g.

28th Dec 2004

Extents

One extent really does do less work!

6th Nov 2004

Reputations

Horror, Shock, Gasp !! Howard Rogers says I’m wrong about indexes - what should I do ?

24th Oct 2004

OICA

How tweaking the optimizer_index_cost_adjust parameter can be a bad thing.

24th Oct 2004

System Statistics

System statistics (the most important 9i optimization enhancement) can cause problems

16th Oct 2004

A Reader Writes

A contribution from our New York correspondent

16th Oct 2004

Row-level Security

An introduction to row-level security. Previously published on www.dbazine.com

23rd Sept 2004

CTAS on 10g

10g fills a performance tuning hole

23rd Sept 2004

Rebuilding Indexes

A discussion document on the reasons for rebuilding indexes. Previously published on www.dbazine.com

19th Sept 2004

Index Efficiency

One step in identifying indexes that may need rebuilding or coalescing

30th Aug 2004

Snapshots

An example of a package that takes snapshots of dynamic performance views.

24th Mar 2004

Unbalanced B-trees

Can Oracle B-tree indexes get unbalanced, and need rebuilding as a consequence ? Another myth reviewed. Previously published on www.dbazine.com

26th Sept 2003

Optimiser Costs

Why isn't Oracle using my index - perhaps the commonest question on the Internet. Previously published on www.dbazine.com

26th Sept 2003

Update Costs

Do you realise how much it costs to update a row ? Especially when your application generator updates every column. Previously published on www.dbazine.com This article can also be found in Russian at http://ln.com.ua/~openxs/articles/jlewis10_ru.html

26th Sept 2003

Locally Managed

An examination of the benefits of Locally Managed Tablespaces. Previously published on www.dbazine.com This article can also be found in Russian at http://ln.com.ua/~openxs/articles/jlewis8_ru.html

26th Sept 2003

Bitmap Join Indexes

An introduction to the latest Oracle 9 variant on bitmap indexes - the bitmap join index. Previously published on www.dbazine.com

26th Sept 2003

Star Transforms

An explanation of the bitmap star transformation. Previously published on www.dbazine.com

26th Sept 2003

Stored outines v9 style.

A discussion of the 'correct' way of creating the stored outline you need. Previously published on www.dbazine.com  This article can also be found in Russian at http://ln.com.ua/~openxs/articles/jlewis5_ru.html

26th Sept 2003

Stored Outlines

An introduction to the use of Stored Outlines in Oracle 8 – and a look ahead to Oracle 9. Previously published on www.dbazine.com This article can also be found in Russian at http://ln.com.ua/~openxs/articles/jlewis4_ru.html

26th Sept 2003

Bitmap Indexes

A fairly definitive guide to bitmap indexes (excludes bitmap join indexes). Published on www.dbazine.com This article can also be found in Russian at http://ln.com.ua/~openxs/articles/jlewis3_ru.html

26th Sept 2003

Oracle Trace

An investigation into a different way of tracing. Previous published on www.dbazine.com. This article can also be found in Russian at http://ln.com.ua/~openxs/articles/jlewis1_ru.html

26th Sept 2003

TPMs

An article describing an interesting threat that may arise from third party transaction processing monitors. Published by www.dbazine.com

26th Sept 2003

Bust Bits

A space management oddity when you mix ASSM and bitmap indexes badly.

14th Feb 2003

Where next

A change of marketing direction for Oracle ?

13-Dec-2002

Sort Usage

An Oracle 7 utility to identify who is doing sorting (15K file)

21-Dec-2001

Parameter list

Yet another simple (Oracle 8) dump of the current parameter list

23-Nov-2001

Number format

How do you convert from Oracle's internal number format to decimal

2-Nov-2001

Dimensions

Summary management and Query Rewrite with prebuilt tables (addendum)

26-Oct-2001

Block Cleanout

The three main variations on block cleanout after commits.

17-Jan-2001

Old Wives Tales

Folk lore and fairy tales about Oracle databases

17-Jan-2001

PT problems

A couple of caveats on using partitioned tables ?

7-Jan-2001

Analytic

An introduction to Analytic functions in 8.1.6

21-Dec-2000

Sort Usage

A correction to v$sort_usage in Oracle 8.1.5

24-Nov-2000

Free Space

How much space is actually free in a data segment (update)

13-Nov-2000

My statistics

Seeing the statistics for my own session.

29-Aug-2000

Optimising sorts

An insight into optimiser evolution and sorting (7.3 to 8.1)

29-Aug-2000

Intro to Explain Plan

An introduction to the basic features of EXPLAIN PLAN

30-Jun-2000

Explain Plan 2

Explain plan with parallel queries, partitioned table, and some funnies.

30-Jun-2000

Sequences

All about sequences (7 and 8)

24-Sep-1999

Setting SID

Selecting and setting the Oracle SID (Unix)

7-Sep-1999

Block Dump

How to dump an Oracle 8 block

7-Sep-1999

Losing SQL text

A minor bug in v$sql et. al. that 'loses' SQL text

7-Sep-1999

SDU

Widening the pipeline from client to server

17-Aug-1999

OSOPER

Does anyone ever use it ? (Unix)

17-Aug-1999

Buffer content

What is in the db_block_buffer now (Oracle 8.0) (correction)

04-Aug-1999

Shell Arrays

Passing multiple results from SQL*Plus to the shell (Unix)

04-Aug-1999

PL/SQL arrays v7

Turning a PL/SQL array into an SQL statement (in v7)

22-Jul-1999

What a bind

Binding and Bind variables in dbms_sql

13-Jul-1999

Dynamic pl/sql

If you really want to, you can do dynamic selects in PL/SQL

5-July-1999

Dummy data

Generating numeric lists without using a dummy table

29-Jun-1999

2D partitions

Hash sub-partitions in 8i can give you 2 dimensional partitioning

23-Jun-1999

In lists

One variable interpreted as an IN list (V 8.0)

12-Jun-1999

User / Owner

Executing dynamic sql in packages as User, not Owner in in v7

11-Jun-1999

PL/SQL tables

Turning a PL/SQL table into an SQL statement (v8)

11-Jun-1999

PQ Slaves

A better view definition for v$pq_slave

11-Jun-1999

Scheduling

A demonstraton of managing concurrent proceses under Oracle

3-Jun-1999

Performance Now

A package to take performance snapshots - added t/s I/O and filenames

3-Jun-1999

Concurrency

A mechanism for concurrent execution of lists of tasks

26-May-1999

commitscn

A bizarre undocumented feature

26-May-1999

Am I tracing

A way to find out if your session is running with SQL Trace true

26-May-1999

Big Update

A cunning (Y2K) plan to update most of a very large table efficiently

18-May-1999

Block Dump 7

How to dump an Oracle 7 block.

18-May-1999

Dec to Hex

A decimal to Hexadecimal function in PL/SQL

18-May-1999

PLSQL arrays

Array based dynamic SQL in PL/SQL (8.0)

6-May-1999

Where and When

Order of execution in a WHERE clause with NO indexes

6-May-1999

Flush

An idea on allowing ordinary users selective DBA rights

30-Apr-1999

dbms_sql

A little wrapper for simple dynamic SQL

30-Apr-1999

Cache

How Oracle 7 handles small tables and caching

22-Apr-1999

3 Buffers

Oracle 8's answer to Placido Domingo et. al. (and caching)

22-Apr-1999

Packing a table

A new feature in 7.3.4 for packing a table

07-Apr-1999

PT Defect

When partition views work better than partition tables

01-Apr-1999

Memory Hogs

Spotting memory hungry Oracle processes without connecting

01-Apr-1999

Inline Speed

How in-line views can improve performance dramatically

25-Mar-1999

Logs of performance

Using utl_file to log a series of performance snapshots.

24-Mar-1999

Raw or FS

A study of how the choice can affect Oracle performance

15-Mar-1999

Audit Connect

A very cheap early warning that something is going wrong.

15-Mar-1999

TS Build

Building multiple large tablespaces concurrently

3-Mar-1999

Block size

A simple C program to help you pick an Oracle block size

26-Feb-1999

Old V$

What's been around but ignored in V$ for Oracle 7

26-Feb-1999

What's going on

A non-invasive PL/SQL tool for performance snapshots

24-Feb-1999

PQ Costs

How effective was your last Parallel Query

17-Feb-1999

Bad PQ Slaves

Identifying parallel query slaves that won't die.

17-Feb-1999

Tuning

3 scripts for (almost) total performance monitoring (Pts 1,2 &3)

17-Feb-1999

Buffer size

db_block_buffers can be too big

10-Feb-1999

Extent count

Sometimes fewer extents to an object is a good thing

10-Feb-1999

Wrong Plans

A class of queries that makes Explain Plan go wrong

10-Feb-1999

Deltas

A sample of awk, cut,and paste for low-impact monitoring (UNIX)

10-Feb-1999

New V$s

What's new in the V$ area for Oracle 8 (and a symmetric difference)

3-Feb-1999

PQO (pt 2)

Some real life experience with the Parallel Query Option (7.3)

3-Feb-1999

PQO (pt 1)

An introduction to the Parallel Query Option (7.3)

27-Jan-1999

Strip SQL

A small C program to tidy up the listing from v$sql_text

27-Jan-1999

DW

A data warehouse case study

21-Jan-1999

Waiting for files

An undocumented (X$) object to see where v$waitstat is happening

21-Jan-1999

Trace Pack

An idea for switching sql_trace on selectively in production systems

21-Jan-1999

PVs

An introduction to Partition Views (7.3)

14-Jan-1999

Guilty Party

Who's the sinner and what's (s)he doing right now (Unix only)

14-Jan-1999

V$ Views

V$ Views - a health warning

14-Jan-1999

PT Quirk

A Partition Elimination anomaly in partition tables (and views)

09-Jan-1999

PVs or PTs

A comparison of 7.3 partition views and 8.0 partition tables

09-Jan-1999

Bitmaps

Bitmap indexes in Oracle 7.3.2

09-Jan-1999

smon

What does SMON do.

08-Jan-1999

Explain Plan

The oldest bug in Oracle ?

07-Jan-1999

Explain8

A convenient framework for running explain plan against Oracle 8

07-Jan-1999

Explain7

A convenient framework for running explain plan against Oracle 7.3

07-Jan-1999


Other Index Pages

Monitoring and Tuning

Database Administration

The Physical Database

FAQ

Oracle Features

The Parallel Query Option

The Parallel Server Option

Developer Tricks

Internals

PL/SQL

Miscellaneous

Things to come