Re: Question re statistics

Hint SQL statements to force them into known/proven execution paths? Use stored outlines for the same purpose?

Finn

On Nov 29, 2007 8:11 PM, William Wagman <wjwagman_at_ucdavis.edu> wrote:

> Greetings,
>
> I have come up against this situation before and have never been quite
> sure how to handle it. Running 10gR2 on RHEL 4 EE. In a home grown
> application there are a series of tables in which records are either
> inserted or deleted such that the number of records in the table is
> constantly changing. It is not a large table, the maximum number of
> records at any one time is 5000 but may vary between 0 and 5000. What
> they are seeing is that as updates to the table proceed performance
> degrades because allegedly statistics become old and unhelpful. So what
> they have done is create a job which runs every few minutes and analyzes
> these tables so the statistics stay fresh and performance stays up to
> their standards. Here is a description of one of the tables in question
> -
>
> SQL> desc org.requests
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> REQUESTOID NOT NULL RAW(16)
> REQUESTGROUPOID NOT NULL RAW(16)
> REQUEST_TYPE NOT NULL CHAR(1)
> REQUEST_PERSONOID NOT NULL RAW(16)
> OBJECT_TABLE_CODE CHAR(2)
> ORIGINAL_OID RAW(16)
> PENDING_OID RAW(16)
> REQUEST_ACTION CHAR(1)
> REQUEST_DATE DATE
>
> I haven't yet traced a session. I've looked at awr reports but am not
> seeing anything helpful there, yet.
>
> So, my question, and I realize it is an open ended one, can someone
> offer suggestions for resolving this issue and things to look at to
> track down the problem. It just strikes me as being very inefficient,
> silly almost, to analyze these tables every 5 minutes but I don't know
> how to approach a solution.
>
> Thanks.
>
> Bill Wagman
> Univ. of California at Davis
> IET Campus Data Center
> wjwagman_at_ucdavis.edu
> (530) 754-6208
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l