|
|
|
|
| Re: Predicting Sort usgae space [message #256851 is a reply to message #256848] |
Mon, 06 August 2007 22:44   |
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 #256857 is a reply to message #256855] |
Mon, 06 August 2007 23:02 |
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 #257215 is a reply to message #256847] |
Tue, 07 August 2007 15:43 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
