Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 13700 invoked from network); 10 Jul 2008 13:50:53 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by air964.startdedicated.com with SMTP; 10 Jul 2008 13:50:53 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 46C5D8C277A; Thu, 10 Jul 2008 14:50:53 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 11245-04; Thu, 10 Jul 2008 14:50:53 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2FBDB8B1D10; Thu, 10 Jul 2008 14:50:51 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 10 Jul 2008 14:48:36 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 395C98C0D21 for ; Thu, 10 Jul 2008 14:48:36 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 10706-06 for ; Thu, 10 Jul 2008 14:48:36 -0400 (EDT) Received: from troll.tpk.net (mail.tpk.NET [216.107.198.11]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EAD6E8C0CBC for ; Thu, 10 Jul 2008 14:48:32 -0400 (EDT) Received: from MWF600XL (216-107-223-2.RightSizing.tpk.static.cust.seg.net [216.107.223.2]) by troll.tpk.net (8.14.2/8.12.11) with ESMTP id m6AImPlE027188; Thu, 10 Jul 2008 14:48:31 -0400 From: "Mark W. Farnham" To: , References: Subject: RE: wierd performance problem Date: Thu, 10 Jul 2008 14:46:08 -0400 Message-ID: <026501c8e2bd$390c1a90$1100a8c0@rsiz.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0266_01C8E29B.B1FA7A90" X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198 In-Reply-To: X-archive-position: 9460 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: mwf@rsiz.com Precedence: normal Reply-to: mwf@rsiz.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------=_NextPart_000_0266_01C8E29B.B1FA7A90 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Dba DBA Sent: Thursday, July 10, 2008 12:11 PM To: oracle-l@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. ------=_NextPart_000_0266_01C8E29B.B1FA7A90 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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

------=_NextPart_000_0266_01C8E29B.B1FA7A90-- -- http://www.freelists.org/webpage/oracle-l