Questions re: tuning

From: Leslie Tierstein <Leslie.Tierstein_at_visionchain.com>
Date: Tue, 4 May 2004 19:56:34 -0400
Message-ID: <MBEKKMMKENFAJFKKNHDOOECLCDAA.Leslie.Tierstein@visionchain.com>

(1) Naive (?) question

Is there an "easy" way to tell if a particular index is not being used by anything in an application? I thought I had heard about one of the monitoring tools that could tell you this, but I might have been hallucinating. (I'm in the process of generating and examining all the execution plans for queries/reports in this database (which I recently inherited), but I wouldn't call that "easy".)

(2) Weirdness (?) observed

I totally don't understand a statement that the optimizer is trying to execute, in creating a temporary table:
(slightly simplified, leaving out some synonyms and owners): (Explain Plan
is courtesy of QuestCentral for Oracle:)

CREATE GLOBAL TEMPORARY TABLE "SYS"."ORA..." ON COMMIT PRESERVE ROWS CACHE AS SELECT /*SEMIJOIN-DRIVER */
"DAY_DT" FROM "DAY" WHERE rownum < 0

What does it mean to select "WHERE rownum < 0"? Why would the optimizer do that? Or is more information needed to answer this?

(The execution plan is history, and the optimizer is now avoiding creating
the temporary tables, but I'm still puzzled by this.)

Thanks for your help,
Leslie

Leslie Tierstein
Senior Consultant
Vision Chain, Inc.
The Standard for Demand Data Networks
phone: 202-261-3549

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue May 04 2004 - 18:51:52 CDT