Re: Questions re: tuning
- From: Paul Drake <discgolfdba@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 4 May 2004 17:23:56 -0700 (PDT)
--- Leslie Tierstein
<Leslie.Tierstein@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
