Home » SQL & PL/SQL » SQL & PL/SQL » Predicting Sort usgae space
Predicting Sort usgae space [message #256847] Mon, 06 August 2007 22:29 Go to next message
ganeshsv
Messages: 51
Registered: January 2006
Member
Hi,

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

Thanks in advance,
SG

Re: Predicting Sort usgae space [message #256848 is a reply to message #256847] Mon, 06 August 2007 22:32 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: 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 Go to previous messageGo to next message
ganeshsv
Messages: 51
Registered: 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?

Thanks in advance,
SG
Re: Predicting Sort usgae space [message #256852 is a reply to message #256847] Mon, 06 August 2007 22:49 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: 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 Go to previous messageGo to next message
ganeshsv
Messages: 51
Registered: January 2006
Member
I am sorry. Can you please provide some information where I am doing wrong?

Thanks in Advance,
SG
Re: Predicting Sort usgae space [message #256855 is a reply to message #256847] Mon, 06 August 2007 22:56 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: 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 Go to previous messageGo to next message
ganeshsv
Messages: 51
Registered: 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?

Thanks in Advance,
SG
Re: Predicting Sort usgae space [message #256861 is a reply to message #256847] Mon, 06 August 2007 23:14 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: 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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your sort_area_size?
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 Go to previous message
gintsp
Messages: 118
Registered: 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 Dec 10 22:26:28 CST 2016

Total time taken to generate the page: 0.08432 seconds