Home » SQL & PL/SQL » SQL & PL/SQL » Predicting Sort usgae space
Predicting Sort usgae space Mon, 06 August 2007 22:29
 ganeshsv Messages: 51Registered: January 2006 Member
Hi,

Is there any way to calculate the temp space used by sort operation?

SG

Re: Predicting Sort usgae space [message #256848 is a reply to message #256847] Mon, 06 August 2007 22:32
 BlackSwan Messages: 25530Registered: January 2009 Location: SoCal Senior Member
>Is there any way to calculate the temp space used by sort operation?
SUM of length columns time numbers rows returned.
In 10+ years I NEVER worried what this answer might be.
What will you do differently once you have this magical number?
Re: Predicting Sort usgae space [message #256851 is a reply to message #256848] Mon, 06 August 2007 22:44
 ganeshsv Messages: 51Registered: January 2006 Member
Yes I too tried the same. But it is not returning the value. My requirement is one of my jobs fails because of temp space is not available. I need to estimate the required size.

Table Tab contains COlA Varchar2(2) and colB Number(5);
And it has 100000 records.

Select colA from Tab order by ColA;

So expected sort usage is 2*100000 = 2000000 bytes?

Can you clarify this?

SG
Re: Predicting Sort usgae space [message #256852 is a reply to message #256847] Mon, 06 August 2007 22:49
 BlackSwan Messages: 25530Registered: January 2009 Location: SoCal Senior Member
>So expected sort usage is 2*100000 = 2000000 bytes?
Your math is correct, but I suspect you have mis-identified the real problem.
Re: Predicting Sort usgae space [message #256854 is a reply to message #256852] Mon, 06 August 2007 22:53
 ganeshsv Messages: 51Registered: January 2006 Member
I am sorry. Can you please provide some information where I am doing wrong?

SG
Re: Predicting Sort usgae space [message #256855 is a reply to message #256847] Mon, 06 August 2007 22:56
 BlackSwan Messages: 25530Registered: January 2009 Location: SoCal Senior Member
I am NOT a mind reader.
I don't know what you were doing & don't know what error message you got.
I refuse to speculate without any facts being presented.
Re: Predicting Sort usgae space [message #256857 is a reply to message #256855] Mon, 06 August 2007 23:02
 ganeshsv Messages: 51Registered: January 2006 Member
Table Tab contains COlA Varchar2(2) and colB Number(5);
And it has 100000 records.

Select colA from Tab order by ColA;

So expected sort usage is 0.2 MB.

But when I used to calculate the sort usage space by using the following query, it returns 1MB.

SELECT A.tablespace_name TABLESPACE, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v\$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v\$tablespace B, v\$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP BY A.tablespace_name, D.mb_total;

Where my calculation goes wrong?

SG
Re: Predicting Sort usgae space [message #256861 is a reply to message #256847] Mon, 06 August 2007 23:14
 BlackSwan Messages: 25530Registered: January 2009 Location: SoCal Senior Member
>Where my calculation goes wrong?
IMO, you are suffering from Compulsive Tuning Disorder.
While 200,000 is not close to 1,000,000 when it comes to the cost of RAM or disk space, you've wasted more \$\$\$ trying to reconcile the 2 values than the answer is worth.

What will you do differently once you have this magical number?
Re: Predicting Sort usgae space [message #256862 is a reply to message #256857] Mon, 06 August 2007 23:15
 Michel Cadot Messages: 65084Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
What is your tablespace extent definition?
If you want to know what are Oracle stats about sort during a query activate a 10032 trace.

Regards
Michel
Re: Predicting Sort usgae space [message #257215 is a reply to message #256847] Tue, 07 August 2007 15:43
 gintsp Messages: 118Registered: February 2007 Senior Member
It is always worth to include your DB version!
So now I can say if you have 10g then you can get temp space from explain plan.
See example (table should be analyzed of course):

```SQL> create table source as select * from dba_source;

Table created.

Elapsed: 00:00:08.67
SQL> exec dbms_stats.gather_table_stats(user, 'source');

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.37
SQL> set autot traceonly explain
SQL> set lines 100
SQL> select * from source order by owner;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2721796570

-------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |  1158K|   109M|       | 30419   (2)| 00:06:06 |
|   1 |  SORT ORDER BY     |        |  1158K|   109M|   258M| 30419   (2)| 00:06:06 |
|   2 |   TABLE ACCESS FULL| SOURCE |  1158K|   109M|       |  4142   (2)| 00:00:50 |
-------------------------------------------------------------------------------------

SQL> ```

There is new column temp_space in plan_table.

Gints Plivna
http://www.gplivna.eu
 Previous Topic: utl_file help Next Topic: inssuficent priviliges
Goto Forum:

Current Time: Sat Jul 22 19:57:53 CDT 2017

Total time taken to generate the page: 0.07744 seconds