Re: Fwd: Stored Outline Problem

Additional information:

The problem described below occurs on 9.2.0.4 (Linux).

I have just tried re-producing on 8.1.7.4 (Solaris) and it actually worked
fine there (hash values in ol$ and v$sql matched).

Once again, any input welcome.

Thanks,
- Bill.


>Hi all,
>
>I'm trying to set up stored outlines and am running into the following
>problem (OUTLN tables are initially empty and the shared pool has just
>been flushed):
>
>
>ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE;
>
>SELECT 'THISISMYTEST' FROM DUAL;
>
>ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;
>
>SQL> SELECT HASH_VALUE,SQL_TEXT FROM OUTLN.OL$;
>
>HASH_VALUE SQL_TEXT
>---------- ------------------------------------------------------------
>1024768383 SEL! ECT 'THISISMYTEST' FROM DUAL
>
>SQL> SELECT HASH_VALUE,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE
>'%THISISMYTEST%';
>
>HASH_VALUE SQL_TEXT
>---------- ------------------------------------------------------------
>2081312514 SELECT 'THISISMYTEST' FROM DUAL
>2452395670 SELECT HASH_VALUE,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%
> THISISMYTEST%'
>
>
>Now (as expected) there is now one row in OUTLN.OL$ but the HASH_VALUE !=
>the HASH_VALUE for the given SELECT statement in v$sql. In fact the
>HASH_VALUE doesn't match anything in v$sql.
>
>Obviously (and demonstratively) the outline will not be used if the query
>hashes to a different value that the outline. Am I missing
>something? (CREATE ANY OUTLINE is granted)
>
>
>Thanks for any help,
>- Bill.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bill Buchan
INET: wbuchan@uk.intasys.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: M.Godlewski
  INET: mcgodlewski_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).