Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Подсчитать free space для БД >=100 TB  [new]
BigBudda
Guest
Привет всем!

Буду рад, если кто поделится советом/запросом как в Oracle 11.2.0.4
быстро = за пару минут подсчитать размер free space для табличных пространств.
Весь вопрос в том, что размер БД 100 ТБ, и запрос типа

select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space
group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files
group by tablespace_name) b;
4 окт 17, 12:18    [20841648]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитать free space для БД >=100 TB  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 17354
Ты хвастаешься или действительно недоволен?
Попробуй для начала хинт /*+ rule */
4 окт 17, 12:31    [20841718]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитать free space для БД >=100 TB  [new]
dmdmdm
Member

Откуда: Нижний Новгород
Сообщений: 880
При такой БД считать не надо.
Надо просто сразу покупать еще диски.
4 окт 17, 12:43    [20841754]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитать free space для БД >=100 TB  [new]
ORA__SQL
Member

Откуда: Moscow
Сообщений: 1767
BigBudda
быстро = за пару минут подсчитать размер free space для табличных пространств.
Сначала так, а потом считай
create table dba_free_space_tmp as select * from dba_free_space;
create table dba_data_files_tmp as select * from dba_data_files;
 
4 окт 17, 13:05    [20841846]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитать free space для БД >=100 TB  [new]
BigBudda
Guest
Сможете скинуть более шустрый запрос, возможно на других вью?
Запустил с хинтом rule, висит минут 20....
И чем помогут временные таблицы с _temp?
4 окт 17, 13:51    [20842083]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитать free space для БД >=100 TB  [new]
Vadim Lejnin
Member

Откуда:
Сообщений: 5790
BigBudda,
Мессир, Вы посчитайте, сколько записей по сегментам для 100Т базы
12 500 000 000 8k блоков

Производительность будет зависеть также от типов ТС
Для начала посмотрите план запроса, чтобы оценить что oracle думает
p.s. Можно попробовать parallel включить, но не уверен что взлетит на fix view
4 окт 17, 14:16    [20842231]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитать free space для БД >=100 TB  [new]
xtender
Member

Откуда: Мск
Сообщений: 4688
BigBudda
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space
group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files
group by tablespace_name) b;
во-первых где условие джойна потерял?
во-вторых, попробуй так:
+
with 
 ts as (select/*+ materialize */ name,ts#,bitmapped,online$,contents$,blocksize,flags from sys.ts$)
,fi as (select/*+ materialize */ * from sys.file$)
,ktfbfe as (select/*+ materialize */ * from sys.x$ktfbfe)
,free_space(tablespace_name, bytes) as (
   select ts.name, 
          sum(f.length) * ts.blocksize
   from ts, sys.fet$ f
   where ts.ts# = f.ts#
     and ts.bitmapped = 0
   group by ts.name,ts.blocksize
   union all
   select
          ts.name,
          sum(f.ktfbfeblks) * ts.blocksize
   from ts, ktfbfe f
   where ts.ts# = f.ktfbfetsn
     and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
     and bitand(ts.flags, 4503599627370496) <> 4503599627370496
   group by ts.name,ts.blocksize
   union all
   select
          ts.name,
          sum(u.ktfbueblks) * ts.blocksize
   from sys.recyclebin$ rb, ts, sys.x$ktfbue u, fi
   where ts.ts# = rb.ts#
     and rb.ts# = fi.ts#
     and u.ktfbuefno = fi.relfile#
     and u.ktfbuesegtsn = rb.ts#
     and u.ktfbuesegfno = rb.file#
     and u.ktfbuesegbno = rb.block#
     and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
     and bitand(ts.flags, 4503599627370496) <> 4503599627370496
   group by ts.name,ts.blocksize
   union all
   select ts.name,
          sum(u.length) * ts.blocksize
   from ts, sys.uet$ u, fi, sys.recyclebin$ rb
   where ts.ts# = u.ts#
     and u.ts# = fi.ts#
     and u.segfile# = fi.relfile#
     and u.ts# = rb.ts#
     and u.segfile# = rb.file#
     and u.segblock# = rb.block#
     and ts.bitmapped = 0
   group by ts.name,ts.blocksize
   union all
   select
          ts.name,
          sum(f.extent_length_blocks_2K) *2048
   from ts, sys.new_lost_write_extents$ f, fi
   where ts.ts# = f.extent_datafile_tsid
     and f.extent_datafile_tsid = fi.ts#
     and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
     and bitand(ts.flags, 4503599627370496) = 4503599627370496
   group by ts.name
   )
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from free_space
group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;
/

должно быть намного быстрее
4 окт 17, 14:23    [20842266]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитать free space для БД >=100 TB  [new]
BigBudda
Guest
Спасибо за скрипт, но под sys выполнить не могу:
sys.new_lost_write_extents$ не существует
4 окт 17, 14:34    [20842295]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитать free space для БД >=100 TB  [new]
xtender
Member

Откуда: Мск
Сообщений: 4688
BigBudda,

+ для 11.2
with
 ts as (select/*+ materialize */ name,ts#,bitmapped,online$,contents$,blocksize,flags from sys.ts$)
,fi as (select/*+ materialize */ * from sys.file$)
,ktfbfe as (select/*+ materialize */ * from sys.x$ktfbfe)
,free_space(tablespace_name, bytes) as (
      select ts.name, 
             sum(f.length) * ts.blocksize
      from ts, sys.fet$ f
      where ts.ts# = f.ts#
        and ts.bitmapped = 0
      group by ts.name,ts.blocksize
      union all
      select
             ts.name,
             sum(f.ktfbfeblks) * ts.blocksize
      from ts, ktfbfe f
      where ts.ts# = f.ktfbfetsn
        and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
      group by ts.name,ts.blocksize
      union all
      select
             ts.name, 
             sum(u.ktfbueblks) * ts.blocksize
      from sys.recyclebin$ rb, ts, sys.x$ktfbue u, fi
      where ts.ts# = rb.ts#
        and rb.ts# = fi.ts#
        and u.ktfbuefno = fi.relfile#
        and u.ktfbuesegtsn = rb.ts#
        and u.ktfbuesegfno = rb.file#
        and u.ktfbuesegbno = rb.block#
        and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
      group by ts.name,ts.blocksize
      union all
      select ts.name, 
             sum(u.length) * ts.blocksize
      from ts, sys.uet$ u, fi, sys.recyclebin$ rb
      where ts.ts# = u.ts#
        and u.ts# = fi.ts#
        and u.segfile# = fi.relfile#
        and u.ts# = rb.ts#
        and u.segfile# = rb.file#
        and u.segblock# = rb.block#
        and ts.bitmapped = 0
      group by ts.name,ts.blocksize
)
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
      from free_space
      group by tablespace_name
     ) a,
     (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
      from dba_data_files
      group by tablespace_name
     ) b
where a.tablespace_name=b.tablespace_name
4 окт 17, 20:41    [20843533]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитать free space для БД >=100 TB  [new]
xtender
Member

Откуда: Мск
Сообщений: 4688
Кстати, в 11.2 dba_free_space хинтована нестед лупсами. Я б вообще избегал ей пользоваться в 11.2
4 окт 17, 20:42    [20843538]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитать free space для БД >=100 TB  [new]
Промазал
Guest
xtender,
Саян, промазал, для 11.2 with бесполезен...
5 окт 17, 09:33    [20844237]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитать free space для БД >=100 TB  [new]
Опечатался
Guest
Промазал,

При чём тут 11.2?

Заменить
from dba_free_space
на
from free_space
не осилил?
5 окт 17, 09:49    [20844280]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитать free space для БД >=100 TB  [new]
xtender
Member

Откуда: Мск
Сообщений: 4688
Опечатался,

да, спасибо, поправленное:
+ для 11.2
with
 ts as (select/*+ materialize */ name,ts#,bitmapped,online$,contents$,blocksize,flags from sys.ts$)
,fi as (select/*+ materialize */ * from sys.file$)
,ktfbfe as (select/*+ materialize */ * from sys.x$ktfbfe)
,free_space(tablespace_name, bytes) as (
      select ts.name, 
             sum(f.length) * ts.blocksize
      from ts, sys.fet$ f
      where ts.ts# = f.ts#
        and ts.bitmapped = 0
      group by ts.name,ts.blocksize
      union all
      select
             ts.name,
             sum(f.ktfbfeblks) * ts.blocksize
      from ts, ktfbfe f
      where ts.ts# = f.ktfbfetsn
        and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
      group by ts.name,ts.blocksize
      union all
      select
             ts.name, 
             sum(u.ktfbueblks) * ts.blocksize
      from sys.recyclebin$ rb, ts, sys.x$ktfbue u, fi
      where ts.ts# = rb.ts#
        and rb.ts# = fi.ts#
        and u.ktfbuefno = fi.relfile#
        and u.ktfbuesegtsn = rb.ts#
        and u.ktfbuesegfno = rb.file#
        and u.ktfbuesegbno = rb.block#
        and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
      group by ts.name,ts.blocksize
      union all
      select ts.name, 
             sum(u.length) * ts.blocksize
      from ts, sys.uet$ u, fi, sys.recyclebin$ rb
      where ts.ts# = u.ts#
        and u.ts# = fi.ts#
        and u.segfile# = fi.relfile#
        and u.ts# = rb.ts#
        and u.segfile# = rb.file#
        and u.segblock# = rb.block#
        and ts.bitmapped = 0
      group by ts.name,ts.blocksize
)
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
      from free_space
      group by tablespace_name
     ) a,
     (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
      from dba_data_files
      group by tablespace_name
     ) b
where a.tablespace_name=b.tablespace_name;
5 окт 17, 11:10    [20844572]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитать free space для БД >=100 TB  [new]
BigBudda
Guest
Скрипт с free_space выполняется дольше.
5 окт 17, 12:53    [20845112]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитать free space для БД >=100 TB  [new]
alexkir
Member

Откуда:
Сообщений: 20
как насчет dba_tablespace_usage_metrics ?
5 окт 17, 14:02    [20845383]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитать free space для БД >=100 TB  [new]
д0kХ
Guest
У меня есть ИМХО подозрение
что реальных датафайлов на 10 - 20 Тб, а все остальное FRA.
5 окт 17, 18:37    [20846278]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитать free space для БД >=100 TB  [new]
xtender
Member

Откуда: Мск
Сообщений: 4688
BigBudda,

план-то покажи...
5 окт 17, 18:44    [20846294]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитать free space для БД >=100 TB  [new]
xtender
Member

Откуда: Мск
Сообщений: 4688
еще более упрощенный вариант:
+
with 
 fi as (select/*+ materialize */ * from sys.file$)
,used as (select/*+ materialize */ fi.ts#,sum(blocks) blocks from fi group by fi.ts#)
,free as (
   select ts#,sum(blocks) blocks
   from (
      select f.ts#,
             sum(f.length) blocks
      from sys.fet$ f
      group by f.ts#
      union all
      select
             f.ktfbfetsn,
             sum(f.ktfbfeblks)
      from sys.x$ktfbfe f
      group by f.ktfbfetsn
      union all
      select
             rb.ts#, 
             sum(u.ktfbueblks)
      from sys.recyclebin$ rb, sys.x$ktfbue u, fi
      where rb.ts# = fi.ts#
        and u.ktfbuefno = fi.relfile#
        and u.ktfbuesegtsn = rb.ts#
        and u.ktfbuesegfno = rb.file#
        and u.ktfbuesegbno = rb.block#
      group by rb.ts#
      union all
      select u.ts#, 
             sum(u.length)
      from sys.uet$ u, fi, sys.recyclebin$ rb
      where u.ts# = fi.ts#
        and u.segfile# = fi.relfile#
        and u.ts# = rb.ts#
        and u.segfile# = rb.file#
        and u.segblock# = rb.block#
      group by u.ts#
  )
  group by ts#
)
select t.name
      ,free.blocks*blocksize as free_space
      ,used.blocks*blocksize 
from sys.ts$ t,free,used
where t.ts#=free.ts#
  and t.ts#=used.ts#
/
5 окт 17, 19:20    [20846352]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитать free space для БД >=100 TB  [new]
BigBudda
Member

Откуда:
Сообщений: 10
Ответ нашёлся на мой вопрос:

select object_name, reason
from DBA_OUTSTANDING_ALERTS
where message_group='Space'
and message_TYPE='Warning';


Данный запрос отрабатывает за доли секунды, даже для БД в несколько десятков террабайт.
6 окт 17, 16:34    [20848936]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитать free space для БД >=100 TB  [new]
AlexFF__|
Member

Откуда:
Сообщений: 2762
BigBudda
Ответ нашёлся на мой вопрос:

select object_name, reason
from DBA_OUTSTANDING_ALERTS
where message_group='Space'
and message_TYPE='Warning';


Данный запрос отрабатывает за доли секунды, даже для БД в несколько десятков террабайт.

=))))))))) До слез! =)))))))))))
6 окт 17, 16:43    [20848982]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить