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

Home -> Community -> Usenet -> c.d.o.server -> Re: Question on stored outlines

Re: Question on stored outlines

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sun, 06 Nov 2005 18:52:10 +0100
Message-ID: <dklfsb$s5k$02$3@news.t-online.com>


Maxim Demenko schrieb:
> dbaplusplus_at_hotmail.com schrieb:
>

>> I am using Oracle 9.2.0.5 on HP UNIX 11i. I am supporting  EMC
>> Documentum's
>> canned application. I am using cost based optimizer. There is a query
>> which is taking 7 hours to run (yes I have updated statistics on all
>> table/indexes - used COMPUTE STATISTICS).
>> I have isolated this query and when I run in sqlplus by adding a hint:
>>  /*+ RULE */, query takes less than a minute.
>>
>> Since I do not have access to Documentum's code, STORED OUTLINES seem
>> like good candidate to add this hint.
>>
>> Based on my reading, to use ORACLE's stored outlines, one has to set
>> a parameter in the session, for example ...
>> ALTER Session SET USE_STORED_OUTLINES=true
>> I do not see USE_STORED_OUTLINES as an init.ora parameter
>>
>> Since Documentum's code cannot be modified how STORED OUTLINES can
>> really be used? Am I missing something? Why all the articles on Stored
>> Outlines claim no code changes are required.
>>
>> Only option seems to me is to use a logon trigger and set this
>> parameter.
>>  Are there any other options, will like to know from the experts.
>>
>>
>> Thanks a lot.
>>

>
> Well, it is clearly documented in SQL Reference:
> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_64a.htm#2064651
>
>
> <quote>
> Specify the schema containing the materialized view log's master table.
> If you omit schema, Oracle assumes the master table is contained in your
> own schema. Oracle creates the materialized view log in the schema of
> its master table. You cannot create a materialized view log for a table
> in the schema of the user SYS.
> </quote>
>
> And materialized view log is obviously needed for fast refresh. However
> according the documentation i would expect an error by attempt to create
> materialized view log as user sys ( at least on the 9.2.0.6 ), but i
> could as well reproduce your test.
> It seems also *you cannot create* should be interpreted as *you should
> not create ... because it will not work anyway...*
>
> Best regards
>
> Maxim

Sorry, it was intended as the reply to another thread.

Best regards

Maxim Received on Sun Nov 06 2005 - 11:52:10 CST

Original text of this message

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