Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from turing.freelists.org (turing.freelists.org [206.53.239.180]) by malta2546.startdedicated.com (Postfix) with ESMTPS id F1F691002C4EC1 for ; Mon, 13 Jan 2020 22:55:05 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 483AA25FBF; Mon, 13 Jan 2020 16:55:04 -0500 (EST) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1578952504; bh=Jx11dVOwc8drI215mUZk+LbIY2IuBc30pEb8hxk5lx4=; h=From:Sender:Sender:From; b=LNn54wPjz05HuKeCvxRRENt6jh8f/C9ewXTnccnsGVamMm5D3EoanjYxIogwBh1eS sYpuUuBjP4qtTWJ7560AQ65zojPFhvp8dKffQfnmJB2vlq+ywcGraccgX6AwlaKnEc c5JIeWkxN30WjMmr91QmEhSHtQwuflrNJjvbyWK8= X-Virus-Scanned: Debian amavisd-new at turing.freelists.org 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 cdxPERJS75aQ; Mon, 13 Jan 2020 16:55:04 -0500 (EST) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6752F25D7C; Mon, 13 Jan 2020 16:54:16 -0500 (EST) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1578952500; bh=Jx11dVOwc8drI215mUZk+LbIY2IuBc30pEb8hxk5lx4=; h=From:Sender:Sender:From; b=AJKYGfadCuCHJpoXg8pR8qW8xGIZxD15gtFu2bICt75sY8VenFWWRbUHNpPnUyKUi HVQXairAt+HFUHXwBAhpXkbetkab0hO/xmiuI0yXC9ehnCAOU/Cms5XZf0oL+AhT0D NX7ILufCcMb2dwnZ9JgymnQ6XQR702dy1XGD3JMU= Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 13 Jan 2020 16:53:31 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4857425C8F for ; Mon, 13 Jan 2020 16:53:31 -0500 (EST) Authentication-Results: turing.freelists.org; dkim=pass (2048-bit key; unprotected) header.d=gmail.com header.i=@gmail.com header.b="q+89HWNF"; dkim-atps=neutral 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 3dnhXUBwJwvR for ; Mon, 13 Jan 2020 16:53:31 -0500 (EST) Received: from mail-qk1-f193.google.com (mail-qk1-f193.google.com [209.85.222.193]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 0EEBE25C8D for ; Mon, 13 Jan 2020 16:53:31 -0500 (EST) Received: by mail-qk1-f193.google.com with SMTP id d71so10188250qkc.0 for ; Mon, 13 Jan 2020 13:53:30 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:to:from:subject:message-id:date:user-agent :mime-version:content-transfer-encoding:content-language; bh=2X6F7VdGX4ykVFo4U7PTe9Csp242HaEPEmtvVQCRkqQ=; b=gXO+CN//I+TxwNqtie7M9LrSGNkOHItGwSAsWYF2YLn3MxzATMnb9ahGNG5u8J+Yix VrpMgRwzP9MJWo27yr46131Her8LLicQPQGBeHXA526suityw+2fzRuM0cu0XTKVkeu7 DHy131Hfo3Wgd7K32DjBbKDwxmkArCtK6/6nBMPlTDMo2mwbno7E5qyYiFjXHxZnpQ8j UoIcQ9xdhRhh/uHUiE0zJG82gMynp4iwUwzEhEqjSF9HJ6VzeboSfDAP+E2VHUTT9KUx laKnPMcn5Y4UVhUviNeRtfbj/XxW0A+JHn+gNbgav0Rwh1iEb54e3DfVr70Ag5Kjjm89 kG1Q== X-Gm-Message-State: APjAAAVFy7i+SUzp27e4jjJ97sep3QbDwYzgxM8+ye2Bjo2oU1/+DfHp eogZ3ZYRQlSL1OEWRWG0VFBNJ1Cm X-Google-Smtp-Source: APXvYqxkUPEpnxhFsXkjrTTob/N27Siika44hI4q0SKQWbrBxX9Xz+lm0xDdRf+ozT32eU1vr0mHiw== X-Received: by 2002:ae9:ef06:: with SMTP id d6mr17887449qkg.402.1578952409683; Mon, 13 Jan 2020 13:53:29 -0800 (PST) Received: from [192.168.80.133] (3ff7b262.cst.lightpath.net. [63.247.178.98]) by smtp.googlemail.com with ESMTPSA id x3sm6455233qts.35.2020.01.13.13.53.29 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 13 Jan 2020 13:53:29 -0800 (PST) To: "oracle-l@freelists.org" From: Mladen Gogala Subject: Strange timing Message-ID: <5b07648c-bb22-1cb6-b5d0-cb0bf1a0e88c@gmail.com> Date: Mon, 13 Jan 2020 16:53:28 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.2.2 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Content-Language: en-US X-archive-position: 75849 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: gogala.mladen@gmail.com Precedence: normal Reply-To: gogala.mladen@gmail.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 I have a large table, let's call it "TEST_TBL" which is partitioned and has 4 global indexes and 2 local indexes.  Local indexes are completely irrelevant for the story.  I executed the following command: ALTER TABLE TEST_TBL TRUNCATE PARTITION PART_X UPDATE GLOBAL INDEXES; According to the manual, this should truncate the table partition PART_X, invalidate and rebuild the global indexes. And now we come to the mystery: According to the above steps, the statement duration should not depend on the partition size. Truncate is a DDL which simply writes high watermark to the beginning of the segment and rebuilding indexes should take about equal time, regardless of the size of the partition being truncated. However, that is not what I see. Truncating a large partition, with 60M rows takes more than 20 times longer than truncating the partition with only 40K rows.  Does anyone have an explanation? I did 10046 trace but I still don't see what the problem is. -- http://www.freelists.org/webpage/oracle-l