Jonathan Lewis's picture


In a recent discussion in the comments of a blog item by Charles Hooper, I made a comment about how hard it is to be accurate (and unambiguous) when talking about the space needed for a rowid. So I thought I’d try to list all the possible options for rowids that you might find in [...]

Jonathan Lewis's picture

SQL*Net compression

Here’s a little demonstration I’ve been meaning to write about for the last few years – it’s very simple: create a table, then query it a few times. execute dbms_random.seed(0) create table t1 as with generator as ( select --+ materialize rownum id from all_objects where rownum <= 3000 ) select lpad(dbms_random.string('U',2),40,'X') v1 from generator [...]

Jonathan Lewis's picture

Row count 2

Following my notes about counting rows in blocks, someone emailed me to ask how the query I’d published would deal with migrated rows. Remember, a migrated row is one that has been moved in its entirety to a different block because an update to the row resulted in the row needing more space than was [...]

Jonathan Lewis's picture


For anyone doing complicated things with RAC, there’s a useful little note on Fairlie Rego’s blog about the improved options for handling and reporting dynamic remastering of objects.

Jonathan Lewis's picture

Failed Login

Here’s a piece of code I found recently running every half hour on a client site:

SQL_ID = 2trtpvb5jtr53
TO_CHAR(current_timestamp AT TIME ZONE :"SYS_B_0", :"SYS_B_1") AS curr_timestamp,
COUNT(username) AS failed_count

Jonathan Lewis's picture

Analyze This – 2

If you’ve run the scripts from Analyze This, I hope you found that the query gave you two different execution plans. This is my output from the test (with a little cosmetic tidying):

Using Analyze

Execution Plan
Plan hash value: 1838229974

| Id | Operation | Name | [...]

Jonathan Lewis's picture

heap block compress

In a recent note showing how an index could become much larger than the underlying table because of the different ways that Oracle handles deletion from table and index blocks, I pointed out that Oracle would have to pick a moment to replace rows marked for deletion with a stub, or place-holder, showing where the [...]

Jonathan Lewis's picture

Index too big

I thought I’d posted this a couple of years ago – but maybe it was something I put on the OTN database forum in response to a question. If it was, the same (or similar) question has recently appeared.  “How come my index is so big when there’s no data in the table ?”
Of course, [...]

To prevent automated spam submissions leave this field empty.
Syndicate content