RE: Question re statistics
- From: <krish.hariharan@xxxxxxxxxxxx>
- To: <wjwagman@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Nov 2007 21:35:27 -0700
After having sent this email I was following the thread on "CBO Bug - Tables
with 0 rows". This may have some relevance to your situation, but the
fundamentals outlined below are still relevant for deterministic problem
resolution.
Regards
-Krish
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of krish.hariharan@xxxxxxxxxxxx
Sent: Thursday, November 29, 2007 6:34 PM
To: wjwagman@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Question re statistics
Bill,
In having seen a few of these, I would first try to quantify the three
things
1. What is slow
2. Performance degrades
3. Performance stays up to their standards.
The place to start is to establish a baseline for the specific operation and
to define an exit criteria so that you know when you are done.
For cases such as these I have used event 10046 or equivalent package
commands to get the wait events and the plan for the specific operation
under both circumstances (slow and "fast") and then determine how to
influence the system (optimizer for example) for stability in the execution.
Regards
-Krish
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of William Wagman
Sent: Thursday, November 29, 2007 6:12 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxx
(530) 754-6208
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
