23. November 2009

In ein Datafile "hineingucken": GET_TABLESPACE_MAP

English title: Looking into a datafile: GET_TABLESPACE_MAP

Gerade auf Entwicklermaschinen (bspw. meiner) kommt es immer wieder vor, dass durch das häufige Anlegen und Droppen von Tabellen die Datafiles sehr stark anwachsen. Wenn dann noch ein paar größere Tabellen dabei sind, wird das Datafile so groß, dass ich es nach dem Droppen der großen Tabelle gerne "shrinken" würde. Aber das bekannte ALTER DATABASE DATAFILE '...' RESIZE XM klappt dann nicht, weil sich am Ende des Datafile immer noch Objekte befinden - ich habe dann ein Datafile mit Objekten "an Anfang", "am Ende" und jeder Menge freien Platz in der Mitte. Man könnte die Tabellen am Ende nun mit einem ALTER TABLE ... MOVE verschieben und den Platz so freibekommen - dazu müsste man aber mal wissen, welche das sind.
Sometimes - after dropping and creating tables I am in the situation that I have a huge datafile for my (say: USERS) tablespace which has huge sparse areas. Unfortunately these areas are not at the end of the datafile so I cannot shrink it using ALTER DATABASE DATAFILE ... RESIZE. So I have a datafile (which I want to shrink) but which I cannot shrink because there some some objects at the end. I could move them (of course) but I need to know which objects these are.
Einfache Abfragen auf die DBA_SEGMENTS-Tabellen sind meistens ziemlich unübersichtlich, die Datendateien sind häufig mal was größer - die DBA_SEGMENTS hat demnach für ein Datafile ziemlich viele Einträge. Werkzeuge muss man immer erstmal starten und viele Tablespace Maps finde ich persönlich sehr unübersichtlich, da sie versuchen, alle Details auf einmal anzuzeigen ... Ich habe mir daher mal eine Funktion geschrieben, die mir erlaubt, zunächst einen "groben" Überblick über das File zu bekommen und darüber hinaus die Möglichkeit bietet, in die Details hineinzugehen - quasi ein "Drill Down" ins Datafile. Hier ist nun der SQL und PL/SQL-Code der Funktion GET_TABLESPACE_MAP.
Simple queries on the DBA_SEGMENTS table are most often difficult to read - when there are many objects this view has many rows ... Tools always have to be started first and many tools only provide a view at the very detail level. So I decided to code a little function which allows me to look into a datafile using a free level of detail. I can start with an overview (e.g. 10 percentiles) and then drill down to see more and more details. The final level of detail would be the individual segments. Here is the code:
CREATE OR REPLACE TYPE  "STORAGE_MAP_T" as object(
  owner          varchar2(30),
  segment_name   varchar2(30),
  start_block    number,
  size_bytes     number,
  size_blocks    number,
  alloc_bytes    number,
  alloc_blocks   number,
  segment_count  number
)
/

CREATE OR REPLACE TYPE  "STORAGE_MAP_CT" as table of storage_map_t
/


create or replace function get_tablespace_map (
  p_datafile_name  in varchar2,
  p_percentiles in number,
  p_start_block in number default null,
  p_end_block   in number default null
) return storage_map_ct authid current_user pipelined is
  v_start_block number := null;
  v_end_block   number := null;
  v_ts_blocks   number := null;
  v_ts_blocksize number := null;
  v_ts_name     varchar2(30) := null;
  v_perc_size   number := null;
  v_last_endblock number := null;
begin
  select df.blocks, ts.block_size, ts.tablespace_name
    into v_ts_blocks, v_ts_blocksize, v_ts_name
  from dba_data_files df, dba_tablespaces ts
  where df.tablespace_name = ts.tablespace_name
  and df.file_name = p_datafile_name;
 
  if p_end_block is null then
    v_end_block := v_ts_blocks;
  else
    v_end_block := p_end_block;
  end if;
  if p_start_block is null then
    v_start_block := 0;
  else
    v_start_block := p_start_block;
  end if;
 
  if p_percentiles is not null then
    v_perc_size := round((v_end_block - v_start_block) / p_percentiles);
    for c in (
      with ts as (
        select v_ts_blocksize block_size, v_ts_name tablespace_name from dual
      ), pc as (
        select level - 1 perc from dual connect by level <= p_percentiles
      ), ex as (
        select
          de.owner,
          de.segment_name,
          de.block_id                 start_block_id,
          de.block_id * ts.block_size start_bytes,
          de.block_id + de.blocks     end_block_id,
          nvl(de.blocks, 0)           blocks,
          nvl(de.bytes,0)             bytes,
          pc.perc   percentile
        from (dba_extents de join ts on (ts.tablespace_name = de.tablespace_name and  de.block_id >= v_start_block and de.block_id < v_end_block
  ))
           right outer join pc on (pc.perc = trunc((de.block_id - v_start_block) / v_perc_size))
      )
      select
        percentile,
        v_perc_size * percentile + v_start_block start_block,
        v_perc_size                                    perc_size_blocks,
        sum(bytes)                                     perc_alloc_bytes,
        sum(blocks)                                    perc_alloc_blocks,
        count(segment_name)                            segment_count
      from ex
      group by percentile
    ) loop
      pipe row (
        storage_map_t(
          null,
          c.percentile,
          c.start_block,
          c.perc_size_blocks * v_ts_blocksize,
          c.perc_size_blocks,
          c.perc_alloc_bytes,
          c.perc_alloc_blocks,
          c.segment_count
        )
      );
    end loop;
  else
    v_last_endblock := v_start_block;
    for c in (
      with ts as (
        select v_ts_blocksize block_size, v_ts_name tablespace_name from dual
      )
      select
        de.owner,
        de.segment_name,
        de.block_id                 start_block,
        de.block_id * ts.block_size start_bytes,
        de.block_id + de.blocks     end_block_id,
        nvl(de.blocks, 0)           blocks,
        nvl(de.bytes,0)             bytes
      from dba_extents de join ts on (ts.tablespace_name = de.tablespace_name)
      where de.block_id >= v_start_block and de.block_id < v_end_block
      order by 3
    ) loop
      if v_last_endblock < c.start_block then
        pipe row (
          storage_map_t(
            null,
            '- FREE -',
            v_last_endblock,
            (c.start_block - v_last_endblock) * v_ts_blocksize,
            (c.start_block - v_last_endblock),
            0,
            0,
            0
          )
        );
      end if;
      pipe row (
        storage_map_t(
          c.owner,
          c.segment_name,
          c.start_block,
          c.bytes,
          c.blocks,
          c.bytes,
          c.blocks,
          1
        )
      );
      v_last_endblock := c.start_block + c.blocks;
    end loop;
    if v_last_endblock < v_end_block then
        pipe row (
          storage_map_t(
            null,
            '- FREE -',
            v_last_endblock,
            (v_end_block - v_last_endblock) * v_ts_blocksize,
            (v_end_block - v_last_endblock),
            0,
            0,
            0
          )
        );
     end if;
  end if;
  return;
end;
Die Funktion sieht danm wie folgt aus ...
The function looks as follows ...
FUNCTION get_tablespace_map RETURNS STORAGE_MAP_CT
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_DATAFILE_NAME                VARCHAR2                IN
 P_PERCENTILES                  NUMBER                  IN
 P_START_BLOCK                  NUMBER                  IN     DEFAULT
 P_END_BLOCK                    NUMBER                  IN     DEFAULT
Dabei bedeuten ...
  • P_DATAFILE_NAME: Der Name des Datafiles, logisch
  • P_PERCENTILES: Die Anzahl "Perzentile", die angezeigt werden sollen. Die Funktion teilt den untersuchten Bereich (das ganze Datafile oder den durch die nächsten zwei Parameter festgelegten Bereich) in die hier angegebene Anzahl Perzentile auf und ermittelt für jeden Bereich den Füllgrad in Anzahl Segmente, Bytes und Blocks.
  • P_START_BLOCK und P_END_BLOCK schließlich geben an, welcher Bereich untersucht werden soll. Gibt man hier nichts an, so wird das ganze Datafile untersucht.
The parameters explained
  • P_DATAFILE_NAME: The datafiles' name
  • P_PERCENTILES: The amount of percentiles into which the examined area (full datafile or area between the next two parameters) should be divided into.
  • P_START_BLOCK und P_END_BLOCK (if given) determine the area of the datafile to be examined.
Das kann man nun wie folgt testen ...
Testing as follows ...
select
  segment_name,
  start_block,
  size_bytes / 1048576 size_mb,
  size_blocks,
  alloc_bytes / 1048576 alloc_mb,
  alloc_blocks,
  segment_count
from table(get_tablespace_map('/oracle/u02/orcl/users01.dbf', 10))
order by 1

SEGMENT_NAME                   START_BLOCK       SIZE_MB SIZE_BLOCKS      ALLOC_MB ALLOC_BLOCKS SEGMENT_COUNT
------------------------------ ----------- ------------- ----------- ------------- ------------ -------------
0                                        0      3276,797      419430      3307,000       423296       1645
1                                   419430      3276,797      419430      3250,000       416000        761
2                                   838860      3276,797      419430      3324,000       425472         69
3                                  1258290      3276,797      419430      3240,000       414720         53
4                                  1677720      3276,797      419430      3263,000       417664         53
5                                  2097150      3276,797      419430      3284,000       420352       1278
6                                  2516580      3276,797      419430      3159,063       404360       2630
7                                  2936010      3276,797      419430         0,000            0          0
8                                  3355440      3276,797      419430         0,000            0          0
9                                  3774870      3276,797      419430         0,000            0          0
Man sieht an diesem Beispiel sehr schön, dass eigentlich "gar nichts zu tun ist". Man könnte einfach hingehen und mit ALTER DATABASE DATAFILE ... RESIZE ... das File verkleinern. Der "Start"-Block eines Segments entscheidet über die Zuordnung; wenn ein Objekt also eine Perzentilgrenze überschreitet, wird es dem vorgehenden zugeordnet. Die Spalten ALLOC_MB bzw. ALLOC_BLOCKS können also durchaus größere Werte anzeigen als SIZE_MB bzw. SIZE_BLOCKS. Möchte man nun ein Drill Down bspw. in das 6. Perzentil machen, so gibt man einfach den Start- und End-Block als dritten und vierten Parameter an.
This example shows the case in which there's nothing to do - since the free area is at the end of the datafile. In this case an ALTER DATABASE DATAFILE ... RESIZE ... would do the job. There are some rows where the ALLOC-Columns are greater then the SIZE-Columns. This is because a segemnts' first block determines the percentile to which this object is being added to. If a large objects starts at the very last block of a percentile it's being added to this one even if most of the data is contained in the next percentile. To do a Drill Down we use the third and fourth parameter of the function: To get more detail for the 6th percentile we recall the function as follows.
select
  segment_name,
  start_block,
  size_bytes / 1048576 size_mb,
  size_blocks,
  alloc_bytes / 1048576 alloc_mb,
  alloc_blocks,
  segment_count
from table(get_tablespace_map('/oracle/u02/orcl/users01.dbf', 10, 25165580, 2936010))
order by 1;

SEGMENT_NAME                   START_BLOCK        SIZE_MB SIZE_BLOCKS       ALLOC_MB ALLOC_BLOCKS SEGMENT_COUNT
------------------------------ ----------- -------------- ----------- -------------- ------------ -------------
0                                  2516580        327,680       41943        342,813        43880           158
1                                  2558523        327,680       41943        304,000        38912            97
2                                  2600466        327,680       41943        326,250        41760           620
3                                  2642409        327,680       41943        340,000        43520            40
4                                  2684352        327,680       41943        318,000        40704            31
5                                  2726295        327,680       41943        328,000        41984            20
6                                  2768238        327,680       41943        325,000        41600           781
7                                  2810181        327,680       41943        328,125        42000           365
8                                  2852124        327,680       41943        317,875        40688           421
9                                  2894067        327,680       41943        229,000        29312            97
Übergibt man SQL NULL für die Anzahl der Perzentile, so gibt die Funktion die Details zurück ...
Specifying SQL NULL as the amount of percentiles leads the function to return the very detail information: the individual segments.
select
  segment_name,
  start_block,
  size_bytes / 1048576 size_mb,
  size_blocks,
  alloc_bytes / 1048576 alloc_mb,
  alloc_blocks,
  segment_count
from table(get_tablespace_map('/oracle/u02/orcl/users01.dbf', null,  2700000, 2730000 ))
order by 1

SEGMENT_NAME                   START_BLOCK        SIZE_MB SIZE_BLOCKS       ALLOC_MB ALLOC_BLOCKS SEGMENT_COUNT
------------------------------ ----------- -------------- ----------- -------------- ------------ -------------
- FREE -                           2700000         56,250        7200          0,000            0             0
SPATIALTAB01                       2724608          8,000        1024          8,000         1024             1
SPATIALTAB01                       2728704          8,000        1024          8,000         1024             1
SPATIALTAB01                       2723584          8,000        1024          8,000         1024             1
SPATIALTAB01                       2729728          8,000        1024          8,000         1024             1
SPATIALTAB01                       2725632          8,000        1024          8,000         1024             1
SPATIALTAB01                       2726656          8,000        1024          8,000         1024             1
SPATIALTAB01                       2727680          8,000        1024          8,000         1024             1
MDRT_1D2E3A$                       2715392         64,000        8192         64,000         8192             1
MDRT_1D2E3A$                       2707200         64,000        8192         64,000         8192             1
In Verbindung mit APEX wäre hier eine sehr einfache Navigation - mit Drill Up und Drill Down denkbar. Eine Erweiterung könnte dann so aussehen, dass auf bestimmte Segmente per Klick einfach ein ALTER TABLE MOVE anwendet. Ich selbst konnte das schon einige Male brauchen; vielleicht ist die Funktion ja für den ein oder anderen noch hilfreich.
In combination with APEX this allows a very simple web application. Using kind of Drill Down and Drill Up one could easily navigate through a datafile and gain information about allocated and free areas. This function was sometimes useful to me - perhaps it is also for you ...

Kommentare:

Andreas Dämmrich hat gesagt…

Hallo Carsten,

wollte heute mal ein paar Gigabyte-Tablespaces von uns verschlanken und dabei Deine vielversprechende Funktion hier einsetzen.

Jetzt bekomme ich leider nen ORA-600 :-(

Habe etwas rumprobiert und letztenedes dieses Statement als "Bösewicht" extrahiert (stammt aus der Schleife beginnend in der Funktion, Zeile 34) - hier zum Testen bereits mit den direkten Werten aus dem fraglichen Schema ersetzt:
----------
with ts as (
select 8192 block_size, 'FLOW_1' tablespace_name from dual
), pc as (
select level - 1 perc from dual connect by level <= 10
), ex as (
select
de.owner,
de.segment_name,
de.block_id start_block_id,
de.block_id * ts.block_size start_bytes,
de.block_id + de.blocks end_block_id,
nvl(de.blocks, 0) blocks,
nvl(de.bytes,0) bytes,
pc.perc percentile
from (dba_extents de join ts on (ts.tablespace_name = de.tablespace_name and de.block_id >= 0 and de.block_id < 1587200
))
right outer join pc on (pc.perc = trunc((de.block_id - 0) / 158720))
)
select
percentile,
158720 * percentile + 0 start_block,
158720 perc_size_blocks,
sum(bytes) perc_alloc_bytes,
sum(blocks) perc_alloc_blocks,
count(segment_name) segment_count
from ex
group by percentile;
-------------

Folgendes kommt dabei raus :-(:

-------------
ERROR at line 26:
ORA-00600: internal error code, arguments: [kkqcbydrv:1], [], [], [], [], [],
[], []
-------------

Innerhalb deines ersten Beispiel-Selects als ganze Funktion ausgeführt sieht das entsprechend so aus:

-------------
SQL> select
2 segment_name,
3 start_block,
4 size_bytes / 1048576 size_mb,
5 size_blocks,
6 alloc_bytes / 1048576 alloc_mb,
7 alloc_blocks,
8 segment_count
9 from table(get_tablespace_map('D:\ORACLE\DB\TEST10G\USERS01.DBF', 10))
10 order by 1;
from table(get_tablespace_map('D:\ORACLE\DB\TEST10G\USERS01.DBF', 10))
*
ERROR at line 9:
ORA-00600: internal error code, arguments: [kkqcbydrv:1], [], [], [], [], [],
[], []
ORA-06512: at "MYSCHEMA.GET_TABLESPACE_MAP", line 34
ORA-06512: at line 1
-------------

Hattest Du so nen Fehler schonmal?

Sollte ich das irgendwie als SYS ausführen, weil mir vielleicht irgendwelche Rechte fehlen oder so?
Wir verwenden ne 10.2.0.4'er Enterprise Edition auf Windows Server 2003 (VMWare).

Vielen Dank schonmal für jegliche Hinweise in dieser Sache!

Schöne Grüße,
Andreas

Carsten Czarski hat gesagt…

Hallo Andreas,

den Fehler hatte ich zwar noch nicht ... aber nach etwas Suche habe ich eine Metalink-Note gefunden: 793019.1. Grund dürfte das CONNECT BY in der Query sein.

Die Note schlägt auch Workarounds vor und sagt, welcher Patch eingespielt werden kann.

Viele Grüße

-Carsten

Andreas Dämmrich hat gesagt…

Super Carsten!
Vielen Dank für den (Meta)link :-)
Die Workarounds da (zumindest der alter system, den ich ausprobiert hab) lässt die Selects auf Deine Funktion in unserer 10.2.0.4'er jetzt wunderbar funktionieren.

Herzlichen Dank und schöne Grüße,
Andreas

Beliebte Postings