Home » RDBMS Server » Performance Tuning » When I should increase the size of datafile,tablespace,redolog files as well as sort area size (Oracle 9i,Windows XP/2000)
When I should increase the size of datafile,tablespace,redolog files as well as sort area size [message #307204] Tue, 18 March 2008 02:25 Go to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
When I should increase the size of datfile,tablespace,redolog files as well as sort area size
Re: When I should increase the size of datafile,tablespace,redolog files as well as sort area size [message #307212 is a reply to message #307204] Tue, 18 March 2008 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't rate your own question before there is any useful answer.

Now my answer is: when it is needed.

Now you can add 5 stars.

Regards
Michel

[Updated on: Tue, 18 March 2008 02:41]

Report message to a moderator

icon2.gif  Re: When I should increase the size of datafile,tablespace,redolog files as well as sort area size [message #307217 is a reply to message #307212] Tue, 18 March 2008 02:47 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
The answer you have mentioned as when it is needed.But now my question is that Which query we should fire to know whether the size of tablespace is full,the size of datafile is huge enough.How I should come to know now I wanted to increase the size of tablespace.

[Updated on: Tue, 18 March 2008 03:02]

Report message to a moderator

Re: When I should increase the size of datafile,tablespace,redolog files as well as sort area size [message #308474 is a reply to message #307217] Mon, 24 March 2008 08:05 Go to previous messageGo to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
Hi,

For SortArea Size parameter you can obtain Disk Sort Ratio and
if value retruned by following query is more than 5%, you can think of increasing SortArea Size parameter
  SELECT (d.value/m.value) * 100
  INTO   v_value
  FROM   v$sysstat d,
         v$sysstat m
  WHERE  d.name = 'sorts (disk)'
  AND    m.name  = 'sorts (memory)';


For datafiles (tablespace) you can following query which will show you freespace and also free %ge in each tablespace. If %ge falls below 15% you can plan for resizing datafiles.

break on report
compute sum of free on report
compute sum of used on report
compute sum of totsz on report
column "(%)" format 999
set feedback off;
set pages 24
select
     a.tablespace_name, sum(round(a.bytes/(1024*1024))) totsz,  sum(used) used, 
     sum(nvl(free,0)) free, 
     round((sum(nvl(free,0))/sum(round(a.bytes/(1024*1024))))*100) "(%)"
from 
    dba_data_files a, 
    (select file_id,round(sum(bytes)/(1024*1024)) used
     from dba_extents group by file_id) b,
    (select tablespace_name,file_id,round(sum(bytes)/(1024*1024))  free
    from dba_free_space group by tablespace_name, file_id) c
where
    a.file_id=b.file_id and a.file_id=c.file_id(+) 
group
    by a.tablespace_name;
set feedback on;

Regards,

MSMallya
Re: When I should increase the size of datafile,tablespace,redolog files as well as sort area size [message #308478 is a reply to message #308474] Mon, 24 March 2008 08:27 Go to previous message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Thanks a lot
Previous Topic: BAD performing query, OR Clause
Next Topic: Performance of a query
Goto Forum:
  


Current Time: Mon Dec 05 02:31:34 CST 2016

Total time taken to generate the page: 0.04078 seconds