Results 1 to 3 of 3

Thread: consistent_gets

  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: consistent_gets

    We're getting some strange behavior from the following query:

    Code:
    SELECT * 
    FROM na_employee emp
    WHERE emp.ebd_ein = emp.OLD_SSN
    AND NOT EXISTS (SELECT *
                             FROM na_ssn_to_ein t2
                             WHERE t2.SSN = emp.ebd_ein)
    ORDER BY LAST_NAME
    In TOAD, repeated executions of this query returns us all 816 rows, or 0 rows. SQL Plus always returns records.

    In looking into the problem, I noticed that V$SESS_IO returns a value ~5000 for CONSISTENT_GETS when the query is run from SQLPlus, but shows ~25000 when run from TOAD (regardless of whether data is returned).

    Since I can't even find good documentation on the def'n of this value, I don't even know if this is significant at this point. But it's the only thing I've found thus far.

    Could anyone offer some pointers as to where I could go from here?

    -Chuck

  2. #2
    Join Date
    Feb 2005
    Location
    Leesburg, VA
    Posts
    42
    Have you tried doing an explain plan in SQL*Plus yet? I'm guessing on a btree to bitmap conversion (happens a lot in SQL*Plus), but can't tell without that.
    Steven Karam
    Oracle 10g Certified Master
    Web: OrclDBA.com
    Email: steve@orcldba.com

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Do an explain plan in both TOAD (CTRL-e, make sure the toad plan tables are setup) and in sqlplus. If they are the same then what v$sess_IO is showing you is that TOAD has been running some of its own queries in the background (probably to understand the schema its logged into). If the plans are different check what optimizer mode has been set in TOAD.

    To understand your query better do a search on google for tkprof as that will give more useful stats or stick autotrace on in TOAD.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •