How Can I?
|Thursday 30 May, 2013
ll these years I’ve been faking it and no one has ever caught me. This short tutorial will
teach how to fake it too.SELECT owner, object_name
An error I found this morning, while look at a DataPump import, was the following:
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
not alter a fake index
Failing sql is:
ALTER INDEX “ASAP”.”QUEST_SX_IDX8812BA0BC1CC2074CC” NOPARALLEL
Most DBAs, even DBAs with 20+ years of experience have never heard of a fake index and wouldn’t know one if they saw it.
Here’s the history lesson in how they came into being.
Oracle’s ADDM, introduced in version 10.1, gives tuning advice. And one of the hidden techniques it uses to do this is to build
fake indexes, run hidden explain plans, and see if the optimizer will use the index. NOSEGMENT indexes were the indexing trick and
the DBMS_XPLAN package was how the plan was created behind the scenes. And if the index was used and the plan cost low ADDM
recommends that you build the index: Simple.
A real index is a complex thing. It is an index object (IND$ and OBJ$), consists of a segment (SEG$), composed of extents (UET$),
is owned by someone (USER$), the segment is in a tablespace (TS$), that consists of one or more datafiles (FILE$). And I’m just
getting started. Once you issue the DDL command Oracle does lots of latching, recursive SQL, space allocation, a full table scan of
the table, etc. etc. and has to build it which is I/O intensive. Now assume a table with millions of rows and it gets ugly. So what
was Oracle to do? How could it try 10, 20, 50 different index possibilities and report which one was best on a running production
database? The answer was NOSEGMENT indexes, also known as FAKE indexes. Fake in the sense that no index is really built. What
happens is that two different entries are made into the data dictionary sufficient to fool the optimizer into thinking there is an
index which is not actually built. So the index “build” takes 20 milliseconds or so. The optimizer only knows about two things ...
objects and index columns ... so the two inserts are into OBJ$ and INDCOL$.
Now ADDM, after its analysis is supposed to drop the NOSEGMENT indexes it creates. But occasionally, as happened at SF, it fails
to do so for some reason. In the above case it appears the fake index was created by a Quest product so likely the user session
crashed or they ended their session in some other way that aborted the session. Alternatively Quest may not use ADDM since they
wouldn’t know a client’s licensing status and probably wrote their own engine to create, test, and drop them.
So the way you find these things, if you are looking for them is with the following SQL statement. What it does is look for index
objects (OBJ$) that are not in dba_indexes (IND$) ... remember fake index creation only hit INDCOL$.
WHERE object_type = 'INDEX'
SELECT owner, index_name
If you find one it can be dropped like any other index.
Here is my web page on indexes and it includes a NOSEGMENT index demo.
Scroll down to “Virtual/NoSegment”.
|Thursday 18 April, 2013
|"What was God doing before the divine creation? Was he preparing hell
for people who asked such questions?"
|Dr. Stephen Hawking
|Thursday 21 January, 2013
|Just found this going through old pictures: Stanley meets one of his brothers at UKOUG.
|Then went to Facebook and found Kyle Hailey had published this picture from the ClosedWorld 2012
conference. I hope I am never so miserable about being with good friends again. ;-)
|Wednesday 30 January, 2013
|If you have been looking for something funny to start your day ... here's my contribution.
I found this with a google search reviewing lots of crazy speculation about Database 12c which I have been Beta testing for many months.
Here's the line I just love: "It also wasn't clear Sunday whether Oracle will soon launch a beta program for 12c"
Which was published 30 September, 2012 by ComputerWorld.
Click here to read the original
|Tuesday 29 January, 2013
|From time-to-time people try to define terms such as VLDB (Very Large
DataBase) and those definitions change along with our use of technology. I am feeling the need to define older desupport-mode
versions of the Oracle database in some manner so the following table is my key to these versions.
||Version Number - Description
|Currently Supported and Fully Patched
||Version 184.108.40.206 or 12.1.
||All versions equal to or greater than 220.127.116.11
||Versions 18.104.22.168 and 22.214.171.124
||Versions 10.2.0.1 to 10.2.0.5
||Anything Pre 8i
|Friday 25 January, 2013
||Yes We Can
|Friday 18 January, 2013
|Tonight I am sitting here re-reading a book I haven't touched in more than 35 years:
Isaac Asimov's "The Gods Themselves." And I am struck by how appropriate the book's dedication which I repeat here for everyone
that find's this page.
And the hope that the war against folly may someday be won, after all
|Tuesday 8 January, 2013
|Hundreds of thousands of websites are potentially at risk following the discovery of an extremely critical
vulnerability in the Ruby on Rails framework that gives remote attackers the ability to execute malicious code on the underlying servers. Get the details at
|Tuesday 1 January, 2013
Happy New Year ... Best wishes for the new year and beyond.