I am having the same issue as well, But these queries that you
provided were given you cummulative sorts usage for the same sqls, It
returns you a query that have high total sort..but which won't return
you the problematic query precisely (especailly true if you have a hot
system, many sorts run many times)..
Is that any way to find out the highest sorting when run it once?
e.g.
SELECT sql_id, sorts
FROM V$SQL
WHERE sorts > 0
ORDER by 2;
fnbxsy7h5rc68 130392
41xapjhsg3qfb 172537
9s20uc50fb23j 337054 <----
0h6b2sajwb74n 718724 <---
SQLPLUS >select sql_text as text from v$sqltext where sql_id =
'&sqlid' order by piece asc
Enter value for sqlid: 0h6b2sajwb74n
TEXT
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with grantee#=:1 and privilege
#>0
SQLPLUS>r
1* select sql_text as text from v$sqltext where sql_id = '&sqlid'
order by piece asc
Enter value for sqlid: 9s20uc50fb23j
TEXT
SELECT INSTANCE_NAME, STARTUP_TIME, HOST_NAME FROM V$INSTANCE
'On May 4, 7:00 pm, DA Morgan <damor..._at_psoug.org> wrote:
> hippodog2..._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
>
> 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
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -
Received on Mon May 07 2007 - 14:46:24 CDT
