[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]

Re: Performance issue on large select.

How do Oracle indexes become "unbalanced" or "invalid"?

That is a BIG question. Please bear with me as I give it a shot and feel free to excuse or correct any mistakes (I'm only human and computers are soooo complex now). This will take awhile, so feel free to skip the techno babble to get to the "good stuff". At the risk of ruining what little credibility I have:

<pedagogical techno babble on>

Oracle indexes are (or at least they used to be) "b-tree" structures. Their actual implementation is probably a lot more complex, but a basic b-tree includes nodes and pointers. Each node contains index values and corresponding pointers to "other nodes or data records". B-tree searches are recursive. The search algorithm starts at the root node and moves to other nodes depending on the "search value":

  1. If the "current node" contains a matching index value a corresponding data pointer points to the appropriate data record (possibly the Oracle rowid?) and the search is finished.
  2. If not, then the "search value" will be:
  3. The current node contains a node pointer for each of the conditions (i.e. for n index values, n + 1 node pointers exist). If the appropriate node pointer is null, the search is finished and the "search value" does not exist in the table. If the node pointer is not null, the specified node becomes the "current node" and the search continues with the step #1.
Accessing a b-tree node usually involves a physical disk read (disk cache size can affect this). To be practical, most b-trees have many (i.e. hundreds? thousands?) index values in each node. The actual maximum number for each platform should probably be based on disk read block size. As well, for performance reasons, inserting and deleting b-tree node index values should affect as few nodes as possible. Because of this, insertion algorithms leave many node index values blank and use a node "fill factor" to determine when to split a node into two nodes (each with half of the index values). Deletion algorithms also use the "fill factor" to determine when to combine two nodes into one as index values are removed. Many algorithms also move index values from node to node to optimize average search times.

Search time is directly related to the number of nodes that need to be accessed for each search value. The best average search time happens when all nodes are completely full and the b-tree "depth" (i.e. the maximum number of nodes between the root node and the "last" node in each tree branch) is minimized. Unfortunately, inserting a new index value in a "completely full" tree takes longer (hence the "fill factor"). Also, index value changes (e.g. when table rows are modified) could take a very long time if the tree depth had to be minimized each time. Given all the variables, Oracle algorithms try to give the "best" performance balance for insertions, deletions, updates, and searches.

When a b-tree index is first created, the algorithms generate the "best" balanced tree. The tree becomes more "unbalanced" as data changes are made ... resulting in longer search times. Dropping and recreating Oracle indexes can result in very significant performance gains (orders of magnitude faster).

<pedagogical techno babble off>

"Invalid" indexes are a lot easier to explain. Sometimes Oracle "breaks" (stray radiation from Mars?) and indexes become corrupted. In addition to possible performance problems, I believe corrupt indexes could even cause invalid query results. Oracle has ways to detect corrupt indexes. If I were a DBA, I might even be able to tell you what they are, but I'm not. Sorry ...

As for "preventing this from occurring", that's why DBAs get paid the big bucks! <sarcasm> I don't think there is much you can do with corrupt indexes (other than fix them when you find them), but you can drop and re-create unbalanced indexes periodically. A few months ago, we reduced response time for one query from 60+ seconds to about 15 seconds by recreating one index. There are probably even some tools that improve index performance "on the fly". Some people do this as part of their regular maintenance, others prefer to wait till things get slow. Your choice ...

How's that for filling up my lunch break? Now, aren't you sorry you asked? <grin>

Steve

Tina_Burnett@NAS.ADP.COM wrote:

I have found that dropping and re-creating the index(es) resolves many
performance problems.

So what do you mean by Oracle indexes sometimes become "unbalanced" (or
even "invalid") and is there
a way to prevent this from occurring ?

Tina Burnett
Application Programmer/Analyst
ADP CSS HRizon Custom Programming

(770) 360-3199

--
Steven Calvert
calvert@uleth.ca
University of Lethbridge
(403)329-2071