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

Search Discussions

  • Paul Drake at May 4, 2004 at 7:22 pm

    Leslie Tierstein 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 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
    -----------------------------------------------------------------
  • Jacques Kilchoer at May 4, 2004 at 7:57 pm
    Another method for 8.1.7:=20
    Create a database trigger to store query outlines, then query the
    outline tables to see if the index has been used. (Tom Kyte, expert
    one-on-one Oracle)=20

    -----Original Message-----
    Paul Drake

    Leslie Tierstein
    wrote:
    (1) Naive (?) question
    =20
    Is there an "easy" way to tell if a particular index
    is not being used by
    anything in an application
    ...

    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.

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMay 4, '04 at 6:51p
activeMay 4, '04 at 7:57p
posts3
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase