RE: wierd performance problem

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dba DBA
Sent: Thursday, July 10, 2008 12:11 PM
To: oracle-l_at_freelists.org
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.

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