Jonathan Lewis is a well-known figure in the Oracle world with more than 30 years’ experience using the software. He has published three books about Oracle – the most recent being “Oracle Core” published by Apress Nov 2011 – and contributed to three others. He has posted more than 1,400 articles on his blog and has spent a lot of his time contributing to newsgroups, forums, and User Group magazines and events around the world.
Jonathan has been self-employed for most of his time in the IT industry. For the last 25 years he has specialised in short-term assignments – typically of a design, review, or troubleshooting nature – often spending no more than two or three days on a client site to address problems. After visiting more than 50 countries (and more than a dozen US states) to talk about or troubleshoot Oracle systems, he has finally gone into near-retirement, though still writes articles and does a little consulting over the internet.
Despite the differences in the software, he finds that the fundamental principles of solving performance issues don't really seem to change as you move from Oracle to SQL Server.
His blog can be found at http://jonathanlewis.wordpress.com.
In part 1 of this series we discussed the reasons why we might want to create some histograms to help the optimizer deal with skewed data distribution. We used an example of a simple status column in an orders table to demonstrate principles, talked about frequency histograms in particular, and highlighted a couple of problems associated with histograms. In part… Read more
In this short series on histograms we will be looking at the reasons why we need histograms and the methods Oracle has to create them. We will examine the costs of creating them and the potential they introduce for undesirable overheads and then we will review their potential for giving you stability problems in your execution plans. This overview w… Read more
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… Read more
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 cou… Read more
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… Read more
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… Read more
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… Read more
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… Read more
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… Read more
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… Read more
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… Read more
The following video and set of resources are from the webinar broadcast by Red Gate on 7 June 2012. Oracle and SQL Server may both share a common language, but certain things are handled quite differently. Jonathan Lewis (OakTable Network, Oracle Ace Director) is used to seeing heap tables (almost) everywhere, but Grant Fritchey (Microsoft SQL Server MVP) is used… Read more
From time to time a request for a query to “show the current SQL” for each session appears on the internet. It’s not really difficult to write something suitable, but this particular request is an interesting demonstration of how much inertia there is in the Oracle world. Here’s a recent suggestion of what the query should look like: select sess.sid,… Read more
Oracle’s optimizer has many strategies for acquiring the data you want with the minimum of work. Some of these strategies are really quite subtle and sophisticated – think of the Star Transformation, for example, which uses a two-phase approach to dimension tables; or the Index Join which does hash joins between indexes to avoid visiting tables. Despite all the clever things… Read more
Jonathan Lewis is an Oracle expert who is recording his exploration of SQL Server in a series of articles. In this fourth part, he turns his attention to clustered indexes, and the unusual requirement SQL Server seems to have for the regular rebuilding of indexes. … Read more
We soon learn, in SQL Server, that heaps are a bad thing, without necessarily understanding how or why. Jonathan Lewis is an Oracle expert who doesn't like to take such strictures for granted, especially when they don't apply to Oracle. Jonathan discovers much about how SQL Server places data, and concludes from his experiments that heaps perform badly in SQL Server because you cannot specify a fill factor for them.… Read more
A well-known Oracle expert records faithfully his struggles with the unfamiliar : SQL Server. He now sets himself the task of creating a table with a million rows of random data. As one might expect, it is the lack of familiarity with the workarounds and tricks of SQL Server that trips him up. His journey brings us fresh insights, and a glimpse at the alternative-universe of Oracle.… Read more
When a SQL expert moves from Oracle to SQL Server, he can spot obvious strengths and weaknesses in the product that are too familiar to be apparent to the SQL Server DBA. Jonathan Lewis is one such expert: In this article he records his train of thought whilst investigating the mechanics of the SQL Server database engine. The result makes interesting reading.… Read more
Sometimes, it is a great idea to push away the keyboard when tackling the problems of an ill-performing, complex, query, and take up pencil and paper instead. By drawing a diagram to show of all the tables involved, the joins, the volume of data involved, and the indexes, you'll see more easily the relative efficiency of the possible paths that your query could take through the tables.… Read more