From oracle-l-bounce@freelists.org Tue Oct 12 18:45:34 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i9CNjYK01210 for ; Tue, 12 Oct 2004 18:45:34 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i9CNjXI01205 for ; Tue, 12 Oct 2004 18:45:34 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EC41472D582; Tue, 12 Oct 2004 18:51:40 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 30224-73; Tue, 12 Oct 2004 18:51:40 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4A28372D564; Tue, 12 Oct 2004 18:51:40 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain Subject: RE: Performance Question - High I/O per Insert Date: Tue, 12 Oct 2004 16:50:01 -0700 Message-ID: <63A70CA32CEF354892F78EADB13D3297030D5D34@seaems005c> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Performance Question - High I/O per Insert Thread-Index: AcSwtHr8j2EQdvIGQyaub/NdXk7DhgAAa4yQ From: "Mark Strickland" To: Cc: , X-OriginalArrivalTime: 12 Oct 2004 23:50:02.0240 (UTC) FILETIME=[30CB9000:01C4B0B6] Content-Transfer-Encoding: 8bit X-archive-position: 10996 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: mstrickland@drugstore.com Precedence: normal Reply-To: mstrickland@drugstore.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Thanks, I'll check that out! ________________________________ From: Graeme.Farmer@mincom.com [mailto:Graeme.Farmer@mincom.com] Sent: Tuesday, October 12, 2004 4:38 PM To: Mark Strickland Cc: oracle-l@freelists.org; oracle-l-bounce@freelists.org Subject: RE: Performance Question - High I/O per Insert Have you tracked the "branch node splits" and "leaf node splits" statistics from v$sesstat for the session(s) performing the inserts? If you have STATSPACK collecting information you should see an upward trend for these stats towards the end of the week but it would be best to target the inserting sessions to be more conclusive in testing your hypothesis. A logoff trigger may be necessary if the sessions are transient. Graeme "Mark Strickland" 12/10/2004 02:41 AM To cc , Subject RE: Performance Question - High I/O per Insert No, no triggers. I believe the high I/O is due to index maintenance. When I remove the indexes or run the load with direct path, the load screams. Thx! Mark ________________________________ From: Graeme.Farmer@mincom.com [mailto:Graeme.Farmer@mincom.com] Sent: Sunday, October 10, 2004 5:52 AM To: Mark Strickland Cc: oracle-l@freelists.org; oracle-l-bounce@freelists.org Subject: RE: Performance Question - High I/O per Insert Any triggers on the table that refer to data found in the current week's partition? This is a classic cause of high I/O per insert. Graeme oracle-l-bounce@freelists.org wrote on 29/09/2004 05:27:28 AM: > This is a follow-up to a question I posed earlier, but I have more info. > A SQL*Loader conventional path load inserts rows into a table > partitioned by week. On the first day of a new week, the load runs as > efficiently as expected, but as the week goes on, the load goes more and > more slowly. There is a single local index on each partition and the > blevel is 2. I'm scratching my head. I realize I'm not providing much > info, but can anyone tell me why loading into a partition might get > slower as the partition fills? Thx! > > > Mark Strickland > > > > ________________________________ > > From: Mark Strickland > Sent: Wednesday, September 22, 2004 12:42 PM > To: oracle-l@freelists.org > Subject: Performance Question - High I/O per Insert > > > > I'm trying to understand why I'm seeing a high number of I/Os for > inserts. Inserts are through SQL*Loader conventional patch. Average > row length is 129 bytes, no longs or blobs. The table has five indexes > and each index has a blevel of 2. According to v$sqlarea, each insert > uses 650 logical I/Os, 69 physical I/Os. I would expect fewer than 20 > I/Os per insert. There are db file sequential read waits on the data > files that make up the index tablespace. That file system also contains > the archived logs. Not surprised at the contention. Can someone point > me in the right direction to understand this? Thx. > > > > Mark Strickland > > Drugstore.com > > Seattle, WA > > > -- > http://www.freelists.org/webpage/oracle-l -- This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please notify the sender and delete the transmission. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. -- This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please notify the sender and delete the transmission. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. -- http://www.freelists.org/webpage/oracle-l