[rt-users] Oracle 9 and Build.html performance

[Joop]

Emmanuel Lacour wrote:
> On Tue, Jan 22, 2008 at 01:16:26PM +0100, Joop wrote:
>   
>> Emmanuel Lacour wrote:
>>     
>>> SQL> set autotrace on
>>> SQL> CREATE INDEX Groups3 ON Groups (LOWER('Type'), LOWER('Domain'), 
>>> Instance);
>>> Index created.
>>> SQL>   execute dbms_utility.analyze_schema( 'RT', 'estimate');
>>>  
>>>       
>> Any particular reason to run 'estimate' instead of 'compute' ?
>>     
>
> Yes, I follow the rt README.Oracle ;) and I wasn't aware of "compute" ;)
>   
I tried both and with compute I get better results than estimate. Have a 
look at the docs to see other options of analyze and with atleast Oracle 
10g it is recommended to use DBMS_STATS.GATHER_SCHEMA_STATS or one of 
its relatives. It has the possibility to keep the compute timewise 
within bays. My VM took about 1min to analyze the whole schema and it 
does so each morning at 6:00 am so  noone is bothered by it.

Further my explain plan is different. I have 3 full tablescans twice of 
USERS and once of PRINCIPALS and my cost is around 480 (This is Oracle XE)

The query send by Ruslan is slighly worse then the original cost is 482 
instead of 480.

Joop


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20080122/1b7076e7/attachment.htm>