RE: wierd performance problem
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracledbaquestions@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 10 Jul 2008 14:46:08 -0400
Very likely this is the "empty front" problem. Oracle has no mechanism or
concept of low water mark when for one reason or another blocks at the
beginning of a table (or partition) are emptied out or effectively skipped
in a freelists or ASSM allocation for an insert session that is rolled back.
Getting many physical reads to do a table scan to return 1 single stopkey
row is the hallmark symptom of this condition.
To verify and completely describe the texture of the problem, select the
file and block address of the first row returned and compare the results
with the file/block address ranges from dba_extents.
If you verify a substantial, repeated problem, then that is one of the few
remaining legitimate reasons for reloading a segment. (Note avoided
religious war by not attempting to enumerate them. The acid test for any
individual case is establishing that the cost to "re-org" is less than the
projected future cost reduction.)
For a partitioned table just copying the problem partition and doing a
Gorman is probably the cheapest way to fix the problem. Since you're
partitioning by day, it seems unlikely the problem will recur for that
partition, and you *may* also benefit by copying the rows to the new
physical location in a single threaded order that matches your predominant
retrospective access path, if any such access path or ties being at least
25% of queries exist.)
No indexes? holy cow. that seems unlikely to be a good choice, even if the
only index would be a null dropout on a column "I_need_to_be_processed" that
is born not null and gets nulled when the row is processed. But that has
nothing to do with your current problem statement, unless the reason you're
partitioned by day with no indexes is to quickly absorb raw inserts for
transaction post-processing, and you're deleting the row upon completion of
post processing. You'd avoid cost to find unprocessed rows and reduce the
overhead of deletion to the cost of punching the "I_need_to_be_processed"
column to null and the index drop-out adjustment. (nullable datestamp of
insertion time columns have substantial positive side effects, though you
might argue for a column with fewer bytes.)
Regards,
mwf
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Dba DBA
Sent: Thursday, July 10, 2008 12:11 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: wierd performance problem
This only occurs with one table. The table is partitioned by day. It has
about 160 gb.
I do the following:
select count(*)
from mytable
where rownum < 2;
No indexes. Takes 30 seconds. Same thing on other tables the same size take
2 seconds.
I ran a 10046 trace and found that all my wait time is db file scattered
read.
dug deeper. Looked at the raw trace file. I noticed I am doing ALOT of db
file scattered read calls. My individual wait time on each call does not
appear to be considerable.
why would I do so many scattered reads to just get the first record? I am
having alot of performance problems with scanning this table and adding
indexes.
Nothing else is going on. I am on a SAN.
Oracle 10.2.0.3
Redhat 4.5
As I said there is not any other activity.
Other related posts:
