Re: Questions re: tuning

From: Paul Drake <discgolfdba_at_yahoo.com>
Date: Tue, 4 May 2004 17:23:56 -0700 (PDT)
Message-ID: <20040505002356.95551.qmail@web20422.mail.yahoo.com>

Leslie Tierstein <Leslie.Tierstein_at_visionchain.com> wrote:

Hi Leslie.

> (1) Naive (?) question
>
> Is there an "easy" way to tell if a particular index
> is not being used by
> anything in an application

Oracle version info would be helpful.

in 9.2, you can issue
"ALTER INDEX <index_name> MONITORING USAGE;" http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_18a.htm#2113145 there are some pre-requisites involved, I believe.

the view V$OBJECT_USAGE will be populated. http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch3123.htm#1119055

in 8.1.7, one method was to isolate it in its own tablespace. this is not foolproof, as it does not take into account caching, and you have to subtract operations like checkpoint overhead and people running queries against dba_segments for LMTs.

others have suggested a periodic survey of v$bh for blocks of the index, but that is not deterministic.

> (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"?

its a way of creating the table as empty. why one would need to hint a create table statement where there will be no rows is beyond me.

I also would not create such tables under the schema "SYS". Create them elsewhere and put a synonym (public?) in place for it. grant select,insert,delete on it.

> (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

hth.

Paul

Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover

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 - 19:22:02 CDT