Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id 13FB21960FFC for ; Sat, 2 Feb 2013 09:39:18 +0100 (CET) Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTP for ; Sat, 2 Feb 2013 09:39:17 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 229EBF3A287; Sat, 2 Feb 2013 03:39:10 -0500 (EST) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1359794350; bh=q92GJkyVu6SQhfkev/4sgEN50FYOq66dWAEnBAVdjsk=; h=MIME-Version:In-Reply-To:References:Date:Message-ID:Subject:From: To:Cc:Content-type:Content-Transfer-Encoding:Sender:Reply-To: List-help:List-unsubscribe:List-Id:List-subscribe:List-owner: List-post:List-archive; b=VxPba848hk8Tl3pGyaccQmQaUiZW7UXX0tmbMlM3whtEnMscKD6cqeVeXeawfHSdy 24OloRLkyYUFA1Su2+6lo9cBZAkEVTy9EkoY1epY4tjh+Viin3YcCYcEwo+VUTwFm7 eagyW6HdHquCvjqBC1SnubTQMgoBg9OABYB1tyEg= X-Virus-Scanned: Debian amavisd-new at localhost.localdomain 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 WBsm7UZSJnC8; Sat, 2 Feb 2013 03:39:09 -0500 (EST) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7FCF1F3A277; Sat, 2 Feb 2013 03:38:25 -0500 (EST) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1359794347; bh=q92GJkyVu6SQhfkev/4sgEN50FYOq66dWAEnBAVdjsk=; h=MIME-Version:In-Reply-To:References:Date:Message-ID:Subject:From: To:Cc:Content-type:Content-Transfer-Encoding:Sender:Reply-To: List-help:List-unsubscribe:List-Id:List-subscribe:List-owner: List-post:List-archive; b=nAy0rKW/ZIeNTRxOoAe+rWwFQwPmb+D/cFVlzMn/3mrD0wr6lHxYiaaiWcLK/+753 FzD3gemhg1hoS+0blNtu5AxXxlEO7YjBuUzuPAU+hmcRBJX8DRMX+3uxUnHR0+OERK ChBf/9VuNcrCcKrM8UhdteEvgAmTQes0ERywDfJY= Received: with ECARTIS (v1.0.0; list oracle-l); Sat, 02 Feb 2013 03:37:42 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B6C5CF3A275 for ; Sat, 2 Feb 2013 03:37:41 -0500 (EST) 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 cOvtuwI5+zmN for ; Sat, 2 Feb 2013 03:37:41 -0500 (EST) Received: from mail-wi0-f178.google.com (mail-wi0-f178.google.com [209.85.212.178]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 18F38F3A25F for ; Sat, 2 Feb 2013 03:37:39 -0500 (EST) Received: by mail-wi0-f178.google.com with SMTP id o1so1377868wic.17 for ; Sat, 02 Feb 2013 00:37:38 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=mime-version:x-received:sender:in-reply-to:references:date :x-google-sender-auth:message-id:subject:from:to:cc:content-type :x-gm-message-state; bh=Codg8zWvAJ8UYhgm8yKVyu9Y4wsCZNvKIF5WdSUpacQ=; b=JdlD/feDHvfv7uPtTKrj7wEYV0pVHm9gi8klMMrCJNFmZibsZssPbAoV0a4WK34S1F NFF+Y1NWJZ/YQKAdMLeUkg+xBSoS4ilFHMyM3ChGrdwBFWZCtS2vrxUKOi7G2TYQ/dnX gqxkUbPqZPXB3vSvOGD4/LLKASX9PycVFrTZn1eiSCrsp9YltZ8HQCYmN6q1Jhc4Vxnh BasUtu8SfaNFhvw4hABjw5HWfr2zuG4xBeQWlCrPu1rB+E+rtBU5SVr66TmACBc2HHZN iGLB1v4K00uG0qmCRowfJW6m2ekrVqprNWycpbOxn8tZVdKCnPXHfaYSnHCzwPmqmC5T N0Kw== MIME-Version: 1.0 X-Received: by 10.180.73.212 with SMTP id n20mr1800188wiv.11.1359794258772; Sat, 02 Feb 2013 00:37:38 -0800 (PST) Received: by 10.216.42.3 with HTTP; Sat, 2 Feb 2013 00:37:38 -0800 (PST) In-Reply-To: References: Date: Sat, 2 Feb 2013 10:37:38 +0200 X-Google-Sender-Auth: FF3uF19phofrvcWxNywWLvJhQHA Message-ID: Subject: Re: query to find high temp usage From: Tanel Poder To: Josh Collier Cc: "oracle-l@freelists.org" Content-type: text/plain X-Gm-Message-State: ALoCoQlv1yN0tNbEyxWLXDWgAlEr9DKWchqex1Cy41pPLLlghXTY+t1j9HxRy1J07wvwrCZflXQe Content-Transfer-Encoding: 8bit X-archive-position: 46941 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: tanel@tanelpoder.com Precedence: normal Reply-To: tanel@tanelpoder.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 Look into TEMP_SPACE_ALLOCATED (and PGA_ALLOCATED) fields of DBA_HIST_ACTIVE_SESS_HISTORY. Some more performance counter fields (like read & write IO requests & bytes) have been introduced into ASH in Oracle 11.2. These counters are not necessarily tied to the SQL_ID ASH record shows (as the allocation may have been made just a few milliseconds before the ASH sample was taken), but in practice, if a SQL uses a lot of TEMP, its SQL_ID will show up in ASH around the memory allocations too... And starting from 11g, you can use the SQL_PLAN_LINE* fields in ASH to tie the allocation back to the rowsource doing the allocation (but the same sampling-related limitations apply in ASH). If that's not enough, then write a little PL/SQL loop which saves the contents of V$SQL_WORKAREA_ACTIVE every minute, it will give you everything you need ... -- *Tanel Poder* Enkitec (The Exadata Experts) Training | Troubleshooting | Exadata | Voicee App On Fri, Feb 1, 2013 at 7:45 PM, Josh Collier wrote: > I am having a hard time finding queries that consistently use a lot of > temp i/o for sorts and joins. In the AWR. > Anyone have any quick pointers for 11.2.0.2? > > Thanks for your time, > > Josh C. > > -- > http://www.freelists.org/webpage/oracle-l > > > -- http://www.freelists.org/webpage/oracle-l