Re: Request that loses indexes

On Nov 14, 10:57 am, Che Averell <aver..._at_dalton-brothers.org> wrote:
> Mark D Powell <Mark.Pow..._at_eds.com> écrit :
>
> > If something is causing one or more of the SQL statements in the
> > procedure to reparse then bind variable peeking can cause behavior
> > like this if the variable values peeked at are not good for the
> > majority of queries.
>
> OK.
>
> > Execution of DDL against any of the tables referenced by procedure
> > would cause SQL to go invalid and have to be reparsed.
>
> There is no DDL in normal run of the database. The only requests that
> are made are select, update, and procedure calls. And the materialized
> views that are refreshed periodicaly.
>
> > The information I asked for in my first response will probably be
> > necessary for anyone to identify the problem.
>
> Yes, thanks for you response. I am currently gathering the information
> asked and will communicate it next week.
>
> Thanks for your help.

Do you have statistics collection jobs running at specific times?

Are there any messages in the alert log?

What are the top waits when it is running ok and when it is running fine?

Does it make a difference if you flush the shared pool when the problem occurs?

jg

--
@home.com is bogus.
I met IRL.  It was the first language that allowed local interactive
prompting and editing in a bar code reader, introduced in 1983.
Interactive Reader Language.  Kinda halfway between assembler and
BASIC, a real PITA.