Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Fwd: Stored Outline Problem

Re: Fwd: Stored Outline Problem

From: M.Godlewski <mcgodlewski_at_yahoo.com>
Date: Wed, 17 Dec 2003 08:29:24 -0800
Message-ID: <F001.005DA2FF.20031217082924@fatcity.com>

Bill,
 
I tried your example on my systems.  On Sun solaris 9.2.0.4 has the same issue of non matching hash values.  Sun Solaris 8.1.7.3 has matching hash_values.

Bill Buchan <wbuchan@uk.intasys.com> wrote:

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


Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
-- 
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).
Received on Wed Dec 17 2003 - 10:29:24 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US