Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.hanau.net!newsfeed01.sul.t-online.de!t-online.de!news.csl-gmbh.net!feeder.news-service.com!xlned.com!feeder1.xlned.com!feeder2.cambrium.nl!feeder5.cambrium.nl!feed.tweaknews.nl!63.218.45.10.MISMATCH!nx01.iad01.newshosting.com!newshosting.com!post01.iad01!not-for-mail Date: Fri, 04 May 2007 19:00:02 -0700 From: DA Morgan Organization: Puget Sound Oracle Users Group User-Agent: Thunderbird 1.5.0.10 (Windows/20070221) MIME-Version: 1.0 Newsgroups: comp.databases.oracle.server Subject: Re: sorting and temporary tablespace used history info References: <1178327585.879212.93770@u30g2000hsc.googlegroups.com> In-Reply-To: <1178327585.879212.93770@u30g2000hsc.googlegroups.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Message-ID: <1178330399.803509@bubbleator.drizzle.com> Cache-Post-Path: bubbleator.drizzle.com!unknown@oracle.advtechserv.com X-Cache: nntpcache 3.0.1 (see http://www.nntpcache.org/) Lines: 50 X-Complaints-To: abuse@csolutions.net Xref: news.f.de.plusline.net comp.databases.oracle.server:197464 hippodog2001@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 or 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@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org