Built-in Performance Monitoring in Oracle and SQL Server

Jonathan Lewis on 16 May 2013 with 0 comments

  Watch now While the primary focus of both Oracle and SQL Server is on relational database management, there are a number of tools that come with each software package that are focused around monitoring and investigating the performance of the servers. These monitoring tools vary wildly between the two platforms, yet still have a lot in common because of… Continue Reading →

Compression in Oracle – part 5: Costs of Index Compression

Jonathan Lewis on 27 February 2013 with 2 comments

In the previous article in this series we looked at index compression, and the way in which Oracle stored index data for a compressed index, and we discovered that Oracle keeps two row directories the “main” directory and the “prefix” directory with slightly different structures even though there is still only a single “row heap”. We also examined the way… Continue Reading →

Compression in Oracle – part 4: Basic Index Compression

Jonathan Lewis on 13 February 2013 with 2 comments

In the first three parts (Part 1: Basic Table Compression, Part 2: Read-Only Data, Part 3: OLTP Compression) of this series we examined table compression – both basic and the separately licensed OLTP compression. In this article we move on to index compression which, as we shall see, uses the same “deduplication” techniques as table compression but includes a couple… Continue Reading →

Compression in Oracle – Part 3: OLTP Compression

Jonathan Lewis on 31 January 2013 with 10 comments

In part two of this series we examined the effect of updates on compressed data when using basic compression, and saw how Oracle will do some “decompression” of a row before updating it with the effect that highly compressed rows might easily become so much larger that even a small amount of change could lead to a large number of… Continue Reading →

Compression in Oracle – Part 2: Read-Only Data

Jonathan Lewis on 24 January 2013 with 12 comments

In part one of this series we saw that the mechanics of basic table compression came in to play only for direct path inserts, CTAS, and “alter table move”. We also saw that Oracle would, by default, set the free space percentage (pctfree) for a table to zero if the table were defined as compressed – giving us a hint… Continue Reading →

Compression in Oracle – Part 1: Basic Table Compression

Jonathan Lewis on 15 January 2013 with 16 comments

In this short series on compression in Oracle, we will be looking at the various types of compression used by traditional Oracle systems, this means: (i) basic table compression, (ii) OLTP table compression, and (iii) index compression. I won’t be discussing the hybrid columnar compression (HCC) that appeared with Exadata (though I may write up a mini-series on that topic… Continue Reading →

Statistics in Oracle and SQL Server

Jonathan Lewis on 10 December 2012 with 0 comments

This video is a follow up to the webinar broadcast by Red Gate on 12 December 2012. Please leave a response and ask questions at the bottom of the page. Watch video This webinar features a demonstration of Schema Compare for Oracle. You can download a free 14 day trial here: Download Schema Compare for Oracle. The better the information… Continue Reading →

Temporary Tables in Oracle and SQL Server

Jonathan Lewis on 15 October 2012 with 1 comments

This video is a follow up to the webinar broadcast by Red Gate on 13th September 2012. Please leave a response and ask questions at the bottom of the page. Watch video Summary Temporary tables are often used in applications for processes that are difficult to complete in a single pass. They are widely used in SQL Server development and… Continue Reading →

ANSI Outer 2

Jonathan Lewis on 17 July 2012 with 0 comments

A comment on a recent post of mine pointed me to a question on the OTN SQL and PL/SQL Forum where someone had presented a well-written test case of an odd pattern of behaviour in ANSI SQL. I made a couple of brief comments on the thread, but thought it worth highlighting here as well. The scripts to create the required tables… Continue Reading →

Ch-ch-ch-ch-changes

Jonathan Lewis on 29 May 2012 with 0 comments

For those not familiar with Richard Foote’s extensive blog about indexes (and if you’re not you should be) – the title of this note is a blatant hi-jacking of his preferred naming mechanism. It’s just a short note to remind myself (and my readers) that anything you know about Oracle, and anything published on the Internet – even by Oracle Corp. and… Continue Reading →