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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Question re statistics

RE: Question re statistics

From: <oracle-l-bounce_at_freelists.org>
Date: Fri, 30 Nov 2007 08:28:46 -0800
Message-ID: <FE043305B38A0F448F3924429D650C2A056695E0@VEXBE2.ex.ad3.ucdavis.edu>


Thanks for all for the suggestions, I have something to play with now. Thank you.

Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman_at_ucdavis.edu
(530) 754-6208

-----Original Message-----
From: Kerber, Andrew W. [mailto:Andrew.Kerber_at_umb.com] Sent: Friday, November 30, 2007 5:10 AM
To: William Wagman; oracle-l_at_freelists.org Subject: RE: Question re statistics

You might try getting a good set of statistics for the largest size the table is likely to be, storing them in a stat table so you have a copy, then locking the statistics on the table of concern
(dbms_stats.lock_table_stats).

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of William Wagman Sent: Thursday, November 29, 2007 7:12 PM To: oracle-l_at_freelists.org
Subject: Question re statistics

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



------------------------------------------------------------------------
------
NOTICE:  This electronic mail message and any attached files are
confidential.  The information is exclusively for the use of the
individual or entity intended as the recipient.  If you are not the
intended recipient, any use, copying, printing, reviewing, retention,
disclosure, distribution or forwarding of the message or any attached
file is not authorized and is strictly prohibited.  If you have received
this electronic mail message in error, please advise the sender by reply
electronic mail immediately and permanently delete the original
transmission, any attachments and any copies of this message from your
computer system. Thank you.

========================================================================
======

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 30 2007 - 10:28:46 CST

Original text of this message

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