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 SELECT '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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
