✔ [Oracle] DB Recon

[BaseBallBatBoy]

Hallo!

Ich bin an einem Mengengerüst der Tabellen aus drei Schemen interessiert (Total 444 Tabellen, ~20 GB used space). Wie würdet ihr hierzu vorgehen? Hier mal was ich bislang habe. Ideen oder Erweiterungen dazu?

SELECT 
  t.owner AS schema_name,
  t.table_name,
  t.avg_row_len AS avg_row_len_bytes,
  t.num_rows,
  ROUND(t.avg_row_len * t.num_rows / 1024 / 1024 , 2) AS space_used_mb,
  ROUND(s.bytes / 1024 / 1024 , 2) AS space_allocated_mb,
  ROUND(100/s.bytes * (t.avg_row_len * t.num_rows), 2) AS utilization_percentage,
  DENSE_RANK() OVER (PARTITION BY t.owner ORDER BY t.avg_row_len * t.num_rows DESC) AS schema_space_used_rank,
  DENSE_RANK() OVER (ORDER BY t.avg_row_len * t.num_rows DESC) AS overall_space_used_rank,
  t.last_analyzed
FROM
  dba_tables t
LEFT JOIN
  dba_segments s
ON 
  s.segment_name = t.table_name
AND 
  s.owner = t.owner
WHERE 
  t.owner IN ('mySchemaA','mySchemaB','mySchemaC')
ORDER BY  
  t.owner,
  t.table_name
;

Gruss
BBBB

 

[MPr]

Hallo,

sieht zunächst plausibel aus. Ein paar ungeordnete Ideen dazu:
- Partitionierung: in der aktuellen Form liefert die Query für eine partitionierte Tabelle n Ergebnissätze (mit n = Anzahl der Partitionen); bei Bedarf könnte man da noch gruppieren
- Compression: der Vergleich der Nettodatenmenge (avg_row_len * num_rows) mit der Segmentgröße kann im Fall der Verwendung von table compression merkwürdige Ergebnisse liefern, so dass man diese Information ggf. ins Script integrieren sollte (und unter Umständen auch den PCTFREE-Wert, der ebenfalls zu Überraschungen führen kann)
- IOTs: im Fall von index organized tables muss man schauen, wie man den Zusammenhang von IOT-Index- und IOT-Overflow-Segment darstellt (ähnliches gilt für Cluster, aber die setzt - außer Oracle - kaum jemand ein)
- man könnte noch die Indizes der Tabellen und ihre Größe hinzu joinen

Beim Verknüpfen dieser Informationen ist dann noch zu bedenken, dass komplexere Join-Operationen mit dictionary Tabellen unter Umständen ein Problem für den CBO darstellen können.

Gruß

Martin

 

[BaseBallBatBoy]

Hi!

Ich habe das ganze noch etwas erweitert: Mviews und Indexes. Paritioning und Compression findet in diesem Fall nicht statt. Eine Frage zum pct_free Wert. Der ist bei mir überall 10. Die berechnete utilization_percentage hingegen sieht ganz unterschiedlich aus. Eine Erklärung dazu?

SELECT 
  t.owner AS schema_name,
  t.table_name,
  CASE WHEN mv.mview_name IS NOT NULL THEN 'MVIEW' ELSE s.segment_type END AS type,
  CASE WHEN mc.mview_name IS NOT NULL THEN mc.comments ELSE tc.comments END AS comments,
  t.avg_row_len AS avg_row_len_bytes,
  t.num_rows,
  ROUND(t.avg_row_len * t.num_rows / 1024 / 1024 , 2) AS space_used_mb,
  ROUND(s.bytes / 1024 / 1024 , 2) AS space_allocated_mb,
  NVL(i.num_indexes, 0) AS num_indexes,
  NVL(ROUND(i.indexes_space_used / 1024 / 1024 , 2), 0) AS indexes_space_used_mb,
  ROUND(100/s.bytes * (t.avg_row_len * t.num_rows), 2) AS utilization_percentage,
  DENSE_RANK() OVER (PARTITION BY t.owner ORDER BY t.avg_row_len * t.num_rows DESC) AS space_used_schema_rank,
  DENSE_RANK() OVER (ORDER BY t.avg_row_len * t.num_rows DESC) AS space_used_overall_rank,
  t.last_analyzed
FROM
  dba_tables t
LEFT JOIN
  dba_segments s
ON 
  s.owner = t.owner
AND
  s.segment_name = t.table_name
LEFT JOIN
  dba_tab_comments tc
ON 
  tc.owner = t.owner
AND
  tc.table_name = t.table_name
LEFT JOIN
  dba_mviews mv 
ON
  mv.owner = t.owner
AND
  mv.mview_name = t.table_name
LEFT JOIN
  dba_mview_comments mc
ON
  mc.owner = t.owner
AND
  mc.mview_name = t.table_name
LEFT JOIN
  (
  SELECT 
    ix.owner,
    ix.table_name,
    COUNT(ix.index_name) AS num_indexes,
    SUM(sg.bytes) AS indexes_space_used
  FROM 
    dba_indexes ix
  LEFT JOIN
    dba_segments sg
  ON 
    sg.owner = ix.owner
  AND
    sg.segment_name = ix.index_name
  WHERE 
    ix.owner IN ('mySchemaA','mySchemaB','mySchemaC')
  GROUP BY
    ix.owner,
    ix.table_name
  ) i
ON 
  i.owner = t.owner
AND
  i.table_name = t.table_name
WHERE 
  t.owner IN ('mySchemaA','mySchemaB','mySchemaC')
ORDER BY  
  t.owner,
  t.table_name
;

 

[MPr]

PCTFREE gibt für Tabellen an, wie viel Platz für spätere UPDATE-Operationen bei der Befüllung frei gehalten werden soll. Insofern gibt es keinen zwingenden Zusammenhang mit der tatsächlichen Nettodatenmenge im Segment, die von folgenden DML-Operationen (update, delete) beeinflusst wird. Außerdem spielt auch das Segment-Management eine Rolle (Stichworte: ASSM und MSSM), die Größe von Extents (einheitlich und allmählich wachsend) etc.

Gruß

Martin

 

[BaseBallBatBoy]

Danke für die Info. Das reicht mir soweit aus