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 ...

2. November 2009

DATE, TIMESTAMP und Zeitzonen in SQL und PL/SQL: Wissenswertes

English title: DATE, TIMESTAMP and time zones in SQL and PLSQL: Things to know - contd ...

Heute setze ich das vor einigen Wochen begonnene Posting zum Thema DATE und TIMESTAMP fort. Wie darin bereits erläutert, unterscheidet sich TIMESTAMP durch zwei Dinge von DATE. Erstens kann TIMESTAMP mit Sekundenbruchteilen umgehen, zweitens werden Zeitzonen unterstützt. Ein Blick in die Dokumentation zeigt nun, dass es TIMESTAMP in drei Varianten gibt:
Today I'd like to continue the blog posting about DATE and TIMESTAMP which I published a few weeks ago. As stated in this posting there are basically two differences between DATE and TIMESTAMP. Firstly, TIMESTAMP supports fractional seconds, secondly TIMESTAMP can handle time zones. The documentation states that there are three variants of TIMESTAMP:
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE
  • TIMESTAMP WITH LOCAL TIME ZONE
Der einfache TIMESTAMP arbeitet ohne Zeitzone (hier besteht der Unterschied zu DATE nur in der Unterstützung von Sekundenbruchteilen). Die anderen beiden Varianten können mit Zeitzonen umgehen. TIMESTAMP WITH TIME ZONE speichert dabei eine explizite Information zur Zeitzone (MEZ, PST, etc.) wogegen TIMESTAMP WITH LOCAL TIME ZONE zur Konvertierung in die "lokale" Zeitzone (die Zeitzone der Datenbanksession) vorgesehen ist (dazu unten mehr). Im letzten Blog-Posting habe ich TIMESTAMP anhand der SYSTIMESTAMP-Funktion vorgestellt. Wenn Ihr wissen möchtet, welche dieser drei Varianten SYSTIMESTAMP nun zurückliefert, könnt Ihr das einfach selbst herausfinden ...
The "simple" TIMESTAMP works without time zones - the only difference between this and a DATE value is the support of fractional seconds. TIMESTAMP WITH TIME ZONE stores the time zone information explicitly whereas TIMESTAMP WITH LOCAL TIME ZONE is suited for datetime conversions to the "local" time zone (the time zone of the database session). In the first blog posting I used the SYSTIMESTAMP function in order to present the TIMESTAMP data type. If you now like to know which of the three TIMESTAMP variants SYSTIMESTAMP returns, just create a view as follows ...
create view systimestamp_type as select systimestamp datum from dual
/

View created.

desc systimestamp_type

 Name                                      Null?    Typ
 ----------------------------------------- -------- ----------------------------

 DATUM                                              TIMESTAMP(6) WITH TIME ZONE
Man sieht, dass SYSTIMESTAMP vom Typ TIMESTAMP WITH TIME ZONE ist. Bei TIMESTAMP könnt Ihr (wie man sieht) auch eine Präzision für die Sekundenbruchteile angeben - der Default ist 6. Die kleinste Einheit in diesem Fall wäre also 0,000001 Sekunden. Diese Präzision kann von Null (0) bis Neun (9) reichen. Wenn Ihr also eine Tabelle mit TIMESTAMP(0) anlegt, habt Ihr effektiv ein DATE.
As you can see: SYSTIMESTAMP returns TIMESTAMP WITH TIME ZONE. And there is another "feature" of TIMESTAMP: You can specify a precision value for the fractional seconds. The default for this is 6 - the smallest unit is then 0.000001 seconds. The range for this goes from zero (0) to nine (9). So if you create a table with a TIMESTAMP(0) column, you have actually a DATE.
TIMESTAMP WITH TIME ZONE bringt nun einige Besonderheiten beim Erzeugen mit TO_TIMESTAMP und umwandeln in VARCHAR2 mit TO_CHAR mit. Zunächst wollen wir das Datum 02.11.2009 14:00 als TIMESTAMP erzeugen, aber nicht in unserer Zeitzone (MEZ), sondern als "kalifornische Zeit". Dazu kann man entweder TO_TIMESTAMP_TZ oder FROM_TZ nutzen.
Time zones must also be taken into account when constructing TIMESTAMP WITH TIME ZONE value or when converting it to VARCHAR2 using TO_CHAR. Let's create a TIMESTAMP value for November, 2nd 2009, 2:00pm - but Pacific Time not CET. You can either use TO_TIMESTAMP_TZ or FROM_TZ for this.
select to_timestamp_tz('2009-11-02 14:00:00 PST', 'YYYY-MM-DD HH24:MI:SS TZR') from dual;

TO_TIMESTAMP_TZ('2009-11-0214:00:00PST','YYYY-MM-DDHH24:MI:SSTZR')
---------------------------------------------------------------------------
02.11.09 14:00:00,000000000 PST

select from_tz(to_timestamp('2009-11-02 14:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'PST') from dual;

FROM_TZ(TO_TIMESTAMP('2009-11-0214:00:00','YYYY-MM-DDHH24:MI:SS'),'PST')
---------------------------------------------------------------------------
02.11.09 14:00:00,000000000 PST
In beiden Fällen kommt ein TIMESTAMP WITH TIME ZONE heraus; die Information "PST" steckt da also explizit drin - und das können wir nun nutzen, um den Wert in unsere Zeitzone umzurechnen. Dazu bietet TO_CHAR zusätzliche Schlüsselwörter an:
Both functions return TIMESTAMP WITH TIME ZONE; the information that this is "pacific time" is now explicity stored within the TIMESTAMP value. So we now can convert this value to other time zones. The TO_CHAR function has addional keywords for that ...
select 
 to_char(
  from_tz(
   to_timestamp('2009-11-02 14:00:00', 'YYYY-MM-DD HH24:MI:SS'), 
   'PST'
  ) at time zone 'CET','DD.MM.YYYY HH24:MI:SS'
 ) as "ZEIT_IN_MEZ" from dual;

ZEIT_IN_MEZ
-------------------
02.11.2009 23:00:00
"Offsets" gehen natürlich auch ...
You can -of course- also work with timezone offsets ...
select 
 to_char(
  from_tz(
   to_timestamp('2009-11-02 14:00:00', 'YYYY-MM-DD HH24:MI:SS'), 
   'PST'
  ) at time zone '+01:00','DD.MM.YYYY HH24:MI:SS'
 ) as "ZEIT_IN_MEZ" from dual;

ZEIT_IN_MEZ
-------------------
02.11.2009 23:00:00
Wenn Ihr in die lokale Zeitzone der Session umrechnen wollt, könnt Ihr den Datentypen TIMESTAMP WITH LOCAL TIME ZONE verwenden; das ist nützlich, wenn mehrere Nutzer in unterschiedlichen Zeitzonen mit dem System arbeiten.
If you like to convert to the "local" time zone (the time zone of the database session), you might use TIMESTAMP WITH LOCAL TIME ZONE. This es particularly useful if your application has to deal with different client time zones.
SQL> alter session set time_zone='CET';

Session altered.

select 
 cast(
  from_tz(
   to_timestamp('2009-11-02 14:00:00', 'YYYY-MM-DD HH24:MI:SS'), 
   'PST'
  ) as TIMESTAMP with local time zone
 ) as "LOCAL_TIME" 
from dual;

LOCAL_TIME
---------------------------------------------------------------------------
02.11.09 23:00:00,000000

Session altered.

SQL> alter session set time_zone='EST';

select 
 cast(
  from_tz(
   to_timestamp('2009-11-02 14:00:00', 'YYYY-MM-DD HH24:MI:SS'), 
   'PST'
  ) as TIMESTAMP with local time zone
 ) as "LOCAL_TIME" 
from dual;

LOCAL_TIME
---------------------------------------------------------------------------
02.11.09 17:00:00,000000
Wenn Ihr nun wissen möchtet, was "PST" nun konkret bedeutet, kann die Funktion TZ_OFFSET weiterhelfen. Sie gibt wieder, wieviele Stunden eine gegebene Zeitzone von UTC (GMT) abweicht.
If you like to know what "PST" means (how many hours), you might use the TZ_OFFSET function. It returns the offset of a given time zone to UTC.
select tz_offset ('PST') from dual;

TZ_OFFS
-------
-07:00
Man kann also sagen, dass man mit TIMESTAMP (und hier: TIMESTAMP WITH TIME ZONE) arbeiten sollte, wenn es an das Speichern von Zeiten geht. Denn die Wahrscheinlichkeit, dass man verschiedene Zeitzonen unterstützen muss, dürfte (für jedwede Anwendung) in Zukunft eher steigen. Arbeitet man dagegen mit DATE, sollte man zumindest alle Zeiten auf eine Zeitzone normalisieren (am besten UTC, also die "Greenwich-Zeit") und dann bei Bedarf umrechnen.
In summary one should work with the TIMESTAMP (and more accurate: TIMESTAMP WITH TIME ZONE) data type when it's about storing datetime values. In the future it will be more and more likely that an application must support multiple time zones - so you should be better off when anticipating this from the very beginning. If you are working with DATE you should at least "normalize" all values to a specific time zones (say: "UTC") and convert them as needed.

Beliebte Postings