Using a SQL Statement to retrieve maintenance Information of Automic (UC4) System Tables

[Legacy User]

Running the following SQL Statement against a Automic Database returns maintenance information of the Automic System (MQ) Tables

The returned information from the following statement can be used to help investigate performance issues. This Script delivers the following MQ Table information: 

• Total Space
• Data Space
• Index Space
• Unused Space

Script:

SELECT
TableName, NumRows,
 reservedpages *8192/1024 as TotalSpace,
 pages * 8192/1024 as DataSpace,
 (usedpages-pages)*8192/1024 as IndexSpace,
 (reservedpages-usedpages)*8192/1024 as UnusedSpace

FROM (SELECT
t.[name] as tablename,
avg([rows]) as NumRows,
sum(total_pages) as reservedpages,
sum(used_pages) as usedpages,
sum(
CASE
When it.internal_type IN (202,204) Then 0
When a.type < 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END) as pages
from sys.allocation_units as a Join sys.partitions as p on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
JOIN sys.tables as t on p.object_id=t.object_id WHERE t.name like 'MQ%'
group by t.[name]) as subselect;

Outcome: 

Once the script has been run, the outcome should look something like this: 

https://us.v-cdn.net/5019921/uploads/editor/ci/sjwvf700h7xy.png" width="973">

On a SideNote: 

Large 'UnusedSpace' within the MQ Tables can lead to performance issues.

[Legacy User]

I tested that on one of my locals, and it did find a culprit. :)

Would there be an Oracle version as well for completeness sake? 

[Legacy User]

The database architecture is different between an Oracle database and MS Sqlserver.
But may be the query below helps:

------------------------------------------------------
--USED, FREE, TOTAL SPACE  
------------------------------------------------------
select df2.file_name file_name
     , df.tablespace_name "Tablespace"
     , to_char(totalusedspace,'999G999G999D000','NLS_NUMERIC_CHARACTERS = '',.''') "Used MB"
     , to_char((df.totalspace - tu.totalusedspace),'999G999G999D000','NLS_NUMERIC_CHARACTERS = '',.''') "Free MB"
     , to_char(df.totalspace,'999G999G999D000','NLS_NUMERIC_CHARACTERS = '',.''') "Total MB"
     , round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free"
from dba_data_files df2,
    (select RELATIVE_FNO, tablespace_name,
        round(sum(bytes) / (1024*1024)) TotalSpace
        from dba_data_files
        group by RELATIVE_FNO, tablespace_name) df,
    (select round(sum(bytes)/(1024*1024)) totalusedspace, RELATIVE_FNO, tablespace_name
        from dba_extents
        group by RELATIVE_FNO, tablespace_name) tu
where df.tablespace_name = tu.tablespace_name (+) and df.RELATIVE_FNO = tu.RELATIVE_FNO (+)
    and df.tablespace_name = df2.tablespace_name and df.RELATIVE_FNO = df2.RELATIVE_FNO
order by 1,2
 ;

[Legacy User]

As you can see there are several ways to analyze an Oracle database.
It depends on what exactly you need to know for database maintenance.

Below another query to anaylze tablespaces:
-- Tablespace Basic Information

col Tablespace_name Heading 'Tablespace'
col Megs_Alloc      Heading 'Megs Alloc'
col Megs_Free       Heading 'Megs Free'
col Megs_Used       Heading 'Megs Used'
col Pct_Free        Heading 'Pct Free'
col Pct_Used        Heading 'Pct Used'
col Init_Ext        Heading 'Init Ext'
col Next_Ext        Heading 'Next Ext'
col Min_Ext         Heading 'Min Ext'
col Max_Ext         Heading 'Max Ext'
col Num_Segs        Heading 'Num Segs'
col Num_Exts        Heading 'Num Exts'

select c.tablespace_name,
       round(a.bytes/1048576) Megs_Alloc,
       round(b.bytes/1048576) Megs_Free,
       round((a.bytes-b.bytes)/1048576) Megs_Used,
       round(b.bytes/a.bytes * 100) Pct_Free,
       round((a.bytes-b.bytes)/a.bytes * 100) Pct_Used,
       round(c.initial_extent/1048576) Init_Ext,
       round(c.next_extent/1048576) Next_Ext,
       round(a.minbytes/1048576) Min_Ext,
       round(a.maxbytes/1048576) Max_Ext,
       nvl(d.num_segs,0) Num_segs,
       nvl(d.num_exts,0) Num_Exts
from (select tablespace_name,
             sum(a.bytes) bytes,
             min(a.bytes) minbytes,
             max(a.bytes) maxbytes
      from sys.dba_data_files a
      group by tablespace_name) a,
     (select a.tablespace_name,
             nvl(sum(b.bytes),0) bytes
      from sys.dba_data_files a,
           sys.dba_free_space b
      where a.tablespace_name = b.tablespace_name (+)
        and a.file_id         = b.file_id (+)
      group by a.tablespace_name) b,
      sys.dba_tablespaces c,
      (select tablespace_name,
              count(distinct segment_name) num_segs,
              count(extent_id) num_exts
       from sys.dba_extents
       group by tablespace_name) d
where a.tablespace_name = b.tablespace_name(+)
  and a.tablespace_name = c.tablespace_name
  and a.tablespace_name = d.tablespace_name(+)
order by c.tablespace_name;

[Legacy User]

For a DBA it is good to know if a tablespace run out of space.

Below a query to detect a tablespace free space breakdown:

-- Tablspace Free Space Breakdown

col tablespace_name Heading 'Tablespace Name'
col over_100m       Heading '# > 100M'
col over_25m        Heading '# > 25M'
col over_10m        Heading '# > 10M'
col over_5m         Heading '# > 5M'
col over_1m         Heading '# > 1M'
col over_512k       Heading '# > 512K'
col under_512k      Heading '# <= 512K'

select tablespace_name,
       sum(decode(sign(bytes-1024*1024*100),-1,0,1)) over_100m,
       (sum(decode(sign(bytes-1024*1024*025),-1,0,1)) -
            sum(decode(sign(bytes-1024*1024*100),-1,0,1))) over_25m,
       (sum(decode(sign(bytes-1024*1024*010),-1,0,1)) -
            sum(decode(sign(bytes-1024*1024*25),-1,0,1))) over_10m,
       (sum(decode(sign(bytes-1024*1024*005),-1,0,1)) -
            sum(decode(sign(bytes-1024*1024*10),-1,0,1))) over_5m,
       (sum(decode(sign(bytes-1024*1024*001),-1,0,1)) -
            sum(decode(sign(bytes-1024*1024*5),-1,0,1))) over_1m,
       (sum(decode(sign(bytes-1024*512*001),-1,0,1)) -
            sum(decode(sign(bytes-1024*1024*1),-1,0,1))) over_512k,
      sum(decode(sign(bytes-1024*512*001),-1,1,0)) under_512k
from dba_free_space
group by tablespace_name;

[Legacy User]

Information about tables can be found with this query:

SELECT   ex.owner
       , ex.segment_name
       , ex.partition_name
       , ex.segment_type
       , sx.tablespace_name
       , ex.GB
       , df.file_name
FROM
    (SELECT owner,
            segment_name,
            partition_name,
            segment_type,
            SUM (bytes/1024/1024/1024) GB
      FROM dba_extents
     GROUP BY owner, segment_name, partition_name,segment_type
     ) ex,
     dba_segments sx, dba_data_files df
WHERE ex.segment_name = sx.segment_name
    and sx.tablespace_name = df.tablespace_name
ORDER BY df.file_name, ex.GB desc
;

[Legacy User]

-- This SQL Plus script lists freespace by tablespace

column dummy noprint
column  pct_used format 999.9       heading "%|Used"
column  name    format a16      heading "Tablespace Name"
column  Kbytes   format 999,999,999    heading "KBytes"
column  used    format 999,999,999   heading "Used"
column  free    format 999,999,999  heading "Free"
column  largest    format 999,999,999  heading "Largest"
break   on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report

select nvl(b.tablespace_name,
             nvl(a.tablespace_name,'UNKOWN')) name,
       kbytes_alloc kbytes,
       kbytes_alloc-nvl(kbytes_free,0) used,
       nvl(kbytes_free,0) free,
       round(((kbytes_alloc-nvl(kbytes_free,0))/
                          kbytes_alloc)*100) pct_used,
       nvl(largest,0) largest
from ( select sum(bytes)/1024 Kbytes_free,
              max(bytes)/1024 largest,
              tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024 Kbytes_alloc,
              tablespace_name
       from sys.dba_data_files
       group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by 5 desc,1
;

[Legacy User]

Nevertheless, Oracle provide a powerful tool for analysis purpose:

Oracle Enterprise Manager Documentation

The Oracle Enterprise Manager family of products provides comprehensive solutions for testing, deploying, operating, monitoring, diagnosing, and resolving problems in today's complex IT environments.

http://www.oracle.com/technetwork/oem/enterprise-manager/documentation/index.html