Re: Views being damaged

Michel Keable (mkeable_at_cti.ulaval.ca) wrote:
: I have a very large view on an Oracle set of tables.
 

: After the create view, it works very fast a few times then, all of a sudden,
: there is something that goes wrong somewhere and every use of the view
: generates thousands (read sometimes millions) of recursive calls. The server
: uses 100% of its CPU, every user has a bad response time and, the user sending
: the select never receives any response until the unix process is killed.
 

: We run Oracle 7.1.3 under Sun Solaris. We think that the problem was not
: present with Oracle 7.0.16.
 

: Does somebody ever get that kind of problem?
 

: Michel Keable
: Universite Laval
 

: Michel.Keable_at_sit.ulaval.ca

Could it be due to optimizer_mode? We were running the RULE based optimizer by default:

        optimizer_mode not set in init file - defaults CHOOSE.

        No statistics created on any tables.

Inadvertently somebody created statistics on some tables involved in a view. Selects against this view then exhibited the problems you speak of.

Solved by setting optimizer_mode = RULE in init file.

Hope this of some use.

        Helen

Experienced a similar problem in the following circumstances. Optimizer_mode not set in init file (defaults to CHOOSE)

        optimizer_mode not set in init file thus defaulting to CHOOSE.

        Normal processing via RULE based optimizer Received on Wed Oct 04 1995 - 00:00:00 CET