Re: sorting and temporary tablespace used history info

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 04 May 2007 19:00:02 -0700
Message-ID: <1178330399.803509@bubbleator.drizzle.com>

hippodog2001_at_yahoo.com wrote:
> Hi,
> Is there any v$ view to find out sorting history, not an active sort
> information...?
>
>
> For e.g.
> In the morning, you see your temporary tablespace has been eaten up.
> you want to find out which SQL_ID performed sort operation (assume
> sort in this case) have eaten up your temporary tablespace during the
> night.
> You can query v$sort_usage, or v$tempseg_usage, those views won't give
> you the sort history information, but only gives you current running
> query sorting information.
>
>
> Shed any light? Thanks for your info...

SELECT sql_id, sorts
FROM GV$SQL
WHERE sorts > 0
ORDER by 2;

===== 6ssrk2dqj7jbx 11088 6ssrk2dqj7jbx 27326 6ssrk2dqj7jbx 34157 aykvshm7zsabd 36309

SQL> SELECT sql_id, temp_space

2 FROM gv$sql_plan
3 WHERE temp_space IS NOT NULL;

SQL_ID TEMP_SPACE
------------- ----------

a2jdutz3uc5w1     435000
fxay0bnpv861n     582000

7yprw22f6516t 4875000
1274csuzzdwnq 4842000

bdf9mrbbxksvw     418000
g48azr9sj7ud2     582000

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri May 04 2007 - 21:00:02 CDT