Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sorting and temporary tablespace used history info

Re: sorting and temporary tablespace used history info

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 7 May 2007 21:31:35 +0100
Message-ID: <mJidnbkeIf-VEKLbRVnytgA@bt.com>

<yenkat_muratee_at_yahoo.com> wrote in message news:1178567184.813640.327220_at_o5g2000hsb.googlegroups.com...
>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 -
>
>

You can get some useful information - probably not exactly what you want, though - from v$sql_workarea.

Access by sql_id and child_number (or hash_value and child_number in 9i) to get details of the memory usage for each line of the excution plan (v$sql_plan). Figures include maximum memory usage, number of executions, memory required for optimal and onepass workarea operations, and the number of optimal, onepass and multipass executions.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Received on Mon May 07 2007 - 15:31:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US