Re: SQLs consuming high TEMP space...

If you are using 9i, then you get some clues from v$sql_workarea - columns max_tempseg_size and last_tempseg_size give you an idea of space used, xxx_executions give you an idea of how many times space was needed and how it was used.

If you are using 10g, then the column direct_writes in v$sql is a strong clue about use of temp for sort and hash spilling.

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

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

• Original Message ----- From: "vikram singh" <vikramsingh120_at_gmail.com> To: <oracle-l_at_freelists.org> Sent: Tuesday, August 15, 2006 8:29 AM Subject: SQLs consuming high TEMP space...

> Hi All,
>
> What is the better way of finding out the Top-N SQLs that are consuming
> high TEMP space...?
>
> Thanks...
>

No virus found in this incoming message. Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.10.10/418 - Release Date: 14/08/2006

--
http://www.freelists.org/webpage/oracle-l