Oracle Veritabanı: Daily Checklist - Günlük Veritabanı Kontrolleri - Tablespace

Günlük olarak database'de neler yapılmalı?

Günlük olarak database'de genel kontroller gerçekleştirilmelidir. Bu genel kontroller içerisinde hem sistemsel veriler kontrol edilmeli, hem database object'lerinin yani tablo, view, index gibi unit'lerin boyutları, durumları kontrol edilmeli, database'in performansı ölçülmeli, backup'ların alınıp alınmadığı gibi kontroller yapılmalıdır.

Bunlar için karışık olarak şimdi belirteceğimiz kontroller yapılabilinir:

1- Tablespace Kontrolleri:

Tablespace kontrollerinde ilk olarak var olan tablespace'lerin kapladığı boyutlarla ilgili query'leri çalıştırır ve bunlarla ilgili rapor almaya çalışırız. Toad kullanlar için bunun bir kısa yolu vardır. Database tab'ı altında Administer>Tablespaces seçeneğini tıklayarak bir grafik elde edebiliriz.

Tablespace kontrollerinin amacı tablespace büyüklükleriyle ilgili bilgi edinmek, büyüme oranlarıyla ilgili öngörüde bulunmak ve eğer tablespace'in büyüme için bir yeri kalıp kalmadığını öğrenmektir.

Bununla birlikte rapor hazırlarken bize kendimizin kullanabileceği sorgular gerekir.

Datafile'larla ilgili gerekli bilgiler için aşağıdaki query'i kullanabiliriz.

select file_name, tablespace_name, bytes, autoextensible,maxbytes from dba_data_files; 

Bu sorguyla tablespace'lerin hepsinin ne kadar yer kapladığını bulabiliriz.

 select tablespace_name, sum(bytes)/1024/1024/1024 from dba_data_files group by tablespace_name ; 

Buradaki sorgu ise en kapsamlı sorgu konumundadır. Buradaki sorguda maksimum kullanılabilicek doluluk oranlarına göre tablespace'ler sıralanır. Şu anki varolan sınırlara göre doluluk oranlarına bakılmak istenirse "order by pct_used" yapılmalıdır.

 SELECT tablespace_name,  
 megs_alloc,  
 megs_free,  
 megs_used,  
 pct_free,  
 pct_used,  
 MAX megs_max,  
 case when MAX=0 then 0 else round(100 * (megs_used / MAX)) end used_pct_of_max  
 FROM (SELECT a.tablespace_name,  
 round(a.bytes_alloc / 1024 / 1024) megs_alloc,  
 round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,  
 round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,  
 round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) pct_free,  
 100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) pct_used,  
 round(maxbytes / 1048576) MAX  
 FROM (SELECT f.tablespace_name,  
 SUM(f.bytes) bytes_alloc,  
 SUM(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes  
 FROM dba_data_files f  
 GROUP BY tablespace_name) a,  
 (SELECT f.tablespace_name, SUM(f.bytes) bytes_free  
 FROM dba_free_space f  
 GROUP BY tablespace_name) b  
 WHERE a.tablespace_name = b.tablespace_name(+)  
 UNION ALL  
 SELECT h.tablespace_name,  
 round(SUM(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,  
 round(SUM((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576) megs_free,  
 round(SUM(nvl(p.bytes_used, 0)) / 1048576) megs_used,  
 round((SUM((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100) pct_free,  
 100 - round((SUM((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100) pct_used,  
 round(SUM(f.maxbytes) / 1048576) MAX  
 FROM sys.v_$temp_space_header h,  
 sys.v_$temp_extent_pool p,  
 dba_temp_files f  
 WHERE p.file_id(+) = h.file_id  
 AND p.tablespace_name(+) = h.tablespace_name  
 AND f.file_id = h.file_id  
 AND f.tablespace_name = h.tablespace_name  GROUP BY h.tablespace_name)  ORDER BY used_pct_of_max DESC;

Yukarıdaki sorguyla aynı işi yapan başka bir sorguda aşağıdaki gibidir.

 SELECT total.tablespace_name,      
     total_space total__mb,  
     round(nvl(total_space-Free_space, 0),2) used__mb,   
     round(nvl(free_space, 0),2) free__mb,  
     round(nvl(total_space-free_space, 0)/total_space*100,2) percent  
 FROM  
  (select tablespace_name, sum(bytes/1024/1024) Free_Space  
    from sys.dba_free_space  
   group by tablespace_name  
  ) Free,  
  (select tablespace_name, sum(bytes/1024/1024) TOTAL_SPACE  
    from dba_data_files b  
   group by tablespace_name  
  ) Total  
 WHERE free.tablespace_name = total.tablespace_name(+)  
 order by percent desc;  

Buradaki sorgumuzda ise database'imizdeki yıllık büyüme miktarını bulabiliriz.

 select to_char(creation_time, 'RRRR MM') "Month",    
 sum(bytes)/1024/1024/1024 "Growth in GB"    
 from sys.v_$datafile    
 where creation_time > SYSDATE-730    
 group by to_char(creation_time, 'RRRR MM')  
 order by "Month";    

Tablespacelerın ay olarak değişimlerinin toplamlarının hesaplanmış olarak görülmesi olarak düşünülebilinir. Bu değişimlerden sonra silinmiş dosyalar göz önüne alınmamıştır. Sadece aylık olarak artışlar belirtilmiştir.

Tablespace'deki Boş Blokların Bulunması:

Aşağıdaki sorguyla veritabanımızda boş olan blokların görülmesini sağlayabiliriz. Bu şekilde bir tablespace'de ne kadar boş alan olduğunu ve burada değişiklik yapılması gerekip gerekmediğini görebiliriz.

 select file_id, block_id first_block, block_id+blocks-1 last_block,   
 segment_name  
    from dba_extents  
    where tablespace_name = 'SYSTEM'  
   union all  
   select file_id, block_id, block_id+blocks-1, 'free'  
    from dba_free_space  
   where tablespace_name = 'SYSTEM'  
   order by file_id, first_block;  

Tablespace'deki Bir Kullanıcıya Ait Tabloların Büyüklüklerinin Bulunması:

Aşağıdaki sorgumuzla kullanıcılarımıza ait tablolardan büyüklüklerinin bulunması ve büyüklüklerine göre gereki tablolarda block kontrolü, bozulmuş blok gibi kontrol işlemlerini yapabilir, çok fazla büyük tablo varsa bu konuda yazılımcı uyarılabilinir. Ayrıca bulunan tablolara göre performans arttırmak için partitionlar ayrılması veya materialized view yaratılması gibi önlemler alabiliriz.

SELECT  
   owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg  
 FROM  
 (SELECT segment_name table_name, owner, bytes  
  FROM dba_segments  
  WHERE segment_type = 'TABLE'  
  UNION ALL  
  SELECT i.table_name, i.owner, s.bytes  
  FROM dba_indexes i, dba_segments s  
  WHERE s.segment_name = i.index_name  
  AND  s.owner = i.owner  
  AND  s.segment_type = 'INDEX'  
  UNION ALL  
  SELECT l.table_name, l.owner, s.bytes  
  FROM dba_lobs l, dba_segments s  
  WHERE s.segment_name = l.segment_name  
  AND  s.owner = l.owner  
  AND  s.segment_type = 'LOBSEGMENT'  
  UNION ALL  
  SELECT l.table_name, l.owner, s.bytes  
  FROM dba_lobs l, dba_segments s  
  WHERE s.segment_name = l.index_name  
  AND  s.owner = l.owner  
  AND  s.segment_type = 'LOBINDEX')  
 WHERE owner in UPPER('&owner')  
 GROUP BY table_name, owner  
 HAVING SUM(bytes)/1024/1024 > 10 --Çok küçük değerleri ölçmek için  
 ORDER BY SUM(bytes) desc  
 ;  

Bütün Database'in Büyüklüğünün Bulunması 

 select  
 ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +  
 ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +  
 ( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +  
 ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"  
 from  
 dual;  

Referans:
http://gavinsoorma.com/2009/06/daily-dba-checklist-to-ensure-database-high-availability/