Home » RDBMS Server » Server Administration » montoring autoallocate tablespaces
montoring autoallocate tablespaces [message #530331] Mon, 07 November 2011 12:22 Go to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
Currently all my tablesapces are "UNIFORM" and I am looking
into going to "AUTOALLOCATE" so space will not be some much
of an issue.

I am using the following code and here is the output


 sqlplus -s / <<-EOT
set pages 50
set line 100
set verify off
set feed off
set trimspool on
break on report
column FREE for 9,999,999 heading "%Free"
column PCT_USED for 9,999 heading "%Used"
column tablespace_name for a18 heading "Tablespace"
column TOT_SIZE for 9,999,999 heading "Size"
column TOT_FREE for 9,999,999 heading "Free"
column TOT_USED for 9,999,999 heading "Used"
column MAX_BYTES for 999,999 heading "Lrgst|Chunk"
column COUNTF     for 9,999 heading "Count"

repheader left "PAGE Threshold Exceeded            Tablespace Free And Used Analysis"
select a.tablespace_name, TOT_SIZE, TOT_FREE, (TOT_SIZE -  TOT_FREE) TOT_USED,
       Round((TOT_FREE/TOT_SIZE)*100) as FREE,
         Round((1-(TOT_FREE/TOT_SIZE))*100) PCT_USED,page_threshold threshold, max_bytes,countf
    from (select tablespace_name, round(sum(bytes)/1048576) TOT_SIZE
            from dba_data_files group by tablespace_name) a,
         (select tablespace_name, round(sum(bytes)/1048576) TOT_FREE
           from dba_free_space group by tablespace_name) b,
       (select tablespace_name,round(max(bytes)/1048576)  MAX_BYTES,count(*)COUNTF
            from dba_free_space group by tablespace_name) c,
       (select tablespace_name from dba_tablespaces where
          contents != 'UNDO') d,
       (select tablespace_name,page_threshold from tsused_thresholds) e
  where a.tablespace_name = b.tablespace_name
 and   b.tablespace_name = c.tablespace_name
 and   c.tablespace_name = d.tablespace_name
 and   c.tablespace_name = e.tablespace_name
 order by pct_used,a.tablespace_name
/
EOT

PAGE Threshold Exceeded            Tablespace Free And Used Analysis
                                                                                    Lrgst
Tablespace               Size       Free       Used      %Free  %Used  THRESHOLD    Chunk  Count
------------------ ---------- ---------- ---------- ---------- ------ ---------- -------- ------
MODELER                   500        499          1        100      0         90      496      2
IE16K                     100         99          1         99      1         90       68      4
DE1M                      550        439        111         80     20         90      417      2
TOOLS                   2,500      1,845        655         74     26         90    1,194    445
USR_100M               44,000     28,600     15,400         65     35         90    2,780    218
USERS                   5,024      3,135      1,889         62     38         90    3,000      3
DE16K                     450        241        209         54     46         90      162      7
IE1M                    1,000        497        503         50     50         90      497      1
SYSAUX                 45,448     22,314     23,134         49     51         80    3,968    580
SYSTEM                  1,331        639        692         48     52         90      639      3
DE10M                  30,504     13,840     16,664         45     55         90    6,260     95
USR_10M                16,000      6,450      9,550         40     60         90    3,190      6
USR_1M                    500        202        298         40     60         90      202      1
IE10M                  21,504      7,330     14,174         34     66         90    2,960     52
PERF_STAT_DATA          1,500        512        988         34     66         90      484      3
IE100M                226,304     61,200    165,104         27     73         90   16,700     92
MODELER_1             193,784     41,910    151,874         22     78         90   27,130      3
DE100M                860,160    167,000    693,160         19     81         90   28,000     49


Does anybody have a scipt that can emulate my above output
and also deal with tablespaces that can autotallocate?

Thanks to all who answer

Report message to a moderator

Re: montoring autoallocate tablespaces [message #530333 is a reply to message #530331] Mon, 07 November 2011 12:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Currently all my tablesapces are "UNIFORM" and
>I am looking into going to "AUTOALLOCATE" so space will not be some much of an issue.

please post SQL & results so that everyone can see exactly what this issue actually is.
what problem are you trying to solve?
Based upon which metric at what value would an independent observer conclude the issue has been resolved?

Report message to a moderator

Re: montoring autoallocate tablespaces [message #530336 is a reply to message #530331] Mon, 07 November 2011 13:03 Go to previous messageGo to next message
John Watson
Messages: 8989
Registered: January 2010
Location: Global Village
Senior Member
Hi -

First, does your query need to do an outer join to dba_free_space? I think that the way it is written now, it will not include any tablespaces which have no free space.
Second, are you sure that dba_free_space.max_bytes is meaningful, given that extents from dropped objects may be fragmenting the free space? I'm not sure, I haven't tested it.
Third, as BS says, why do you think autoallocate is a good thing? With ASM (and therefore Exadata) autoallocate is defintely bad for performance. And even without ASM, the bitmaps will be more efficient with uniform size. Probably hard to prove, though.

Just a few thoughts, which I realize do not in any way answer your question.

John.

[Updated on: Mon, 07 November 2011 13:05]

Report message to a moderator

Re: montoring autoallocate tablespaces [message #530339 is a reply to message #530333] Mon, 07 November 2011 13:40 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
The results are posted... but there is currently no issue as
per my post. I just supplied the output for everybody to see

What I look for is if %used exceeds the threshold. If that is
the case, than I kick out a page.

If I use autoallocate, I believe the way the SQL is writren
it can theoretically hit my threshold or go over it,which will cause a "false postive" since space maybe autoallocate and bring
down the %used.

The main problem I am trying to solve is bring down the HWM
for some objects and freeing up space when rows get deleted.

Or is there a better way to address this issue without exporting/
dropping/importing

Report message to a moderator

Re: montoring autoallocate tablespaces [message #530342 is a reply to message #530339] Mon, 07 November 2011 13:50 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Or is there a better way to address this issue without exporting/dropping/importing

do NOTHING!

If/when old rows are removed, then new rows will eventually consume that space.
If/when you "shrink" tablespace to "reclaim" space from removed rows,
then Oracle has to expand tablespace size as new rows get added.
Lots of movement, without real progress.

Report message to a moderator

Previous Topic: Convert Enterprise Edition DB to Standard Edition
Next Topic: Large trace files generated in udump
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Oct 31 18:21:48 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.