Saturday, 26 November 2016

Teradata Disk Space Queries

Teradata Disk Space Queries
1) To find AMP usage and CPU utilization on TD server.

  You could query DBC.Tablesize, DBC.ProfileInfo, DBC.AllRoleRights, and  
  DBC.RoleInfo and others. The query should return on the AMP Usage and CPU  
  Utilization The following query is dbc.ampusage                 

  Select Account Name, UserName, sum(diskio),sum(cputime)
from    dbc.ampusage
  Group by 1,2;

2) To find the information at the user & account level. Space Usage. (Total Disk Space  On DBC)

   The following query is dbc.disksspace 
    Select sum (currentperm) c1, sum (maxperm) c2, c2-c1 
from    dbc.diskspace;
   (
    Or)
   Select sum(maxperm) 
from    DBC.DiskSpace;      (Total disk space in the entire System (DBC))
   SELECT SUM (MaxPerm) 
FROM    DBC.DiskSpaceV;
   (
    Or)
   Select sum(MaxPerm) 
from    DBC.DiskSpace 
where    DatabaseName = 'DBC';

3) To find table size in particular DB on TD
 
   Select database name, table name, SUM (current perm) 
FROM    DBC.Tablesize   
   WHERE database name = <DATABASE name> 
GROUP BY 1,2; 

4) To find DB size on TD

   Select database name, SUM (maxperm), SUM (current perm) 
FROM    dbc.diskspace 
   WHERE database name =<DATABASE name> 
GROUP BY 1;

5) To get all the same spaces for one or more Databases or Users on TD
   Select DATABASENAME, SUM (MAXPERM), SUM (CURRENTPERM),
  SUM (MAXPERM)-SUM (CURRENTPERM) ,SUM(MAXSPOOL) 
FROM    DBC.DISKSPACE   
   GROUP BY 1 
ORDER BY 2; 

6)  To find Occupied and Empty Permanent Space and Assigned Spool Space. If you 
   Filter using WHERE Database name = 'xxxx'

     Select DATABASENAME, SUM (MAXPERM),SUM(CURRENTPERM),SUM(MAXPERM)-
   SUM (CURRENTPERM), SUM (MAXSPOOL FROM DBC.DISKSPACE 
GROUP BY 1   
   ORDER BY 2; 
 
   (
    Or
   
   Select * 
from    dbc.databases 
where    database name='Your database name';

7)  To find total disk space in GB on TD server

    Select sum(maxperm)/ 1024/ 1024/ 1024 "MaxPerm in GB", sum(currentperm)/   
    1024/ 1024/ 1024 "CurrrentPerm in GB", sum(maxspool)/1024/1024/1024  
    "maxspool in GB", (sum(maxperm)/ 1024/ 1024/ 1024) - (sum(currentperm)/  
    1024/ 1024/ 1024 ) " SpaceLeft in GB",(sum(currentperm) / (sum(maxperm))  
    *100) "% Used" 
from    dbc.diskspace;
 
    Select sum(maxperm)/ 1024/ 1024 "MaxPerm in MB", sum(currentperm)/ 1024/   
    1024 "CurrrentPerm in MB", sum(maxspool)/1024/1024 "maxspool in MB",
    (sum(maxperm)/ 1024/ 1024) - (sum(currentperm)/ 1024/ 1024 ) "   
    SpaceLeft in MB",(sum(currentperm) / (sum(maxperm)) *100) "% Used" 
from     
    dbc.diskspace;

8) To find Percent of disk space free on TD server

SELECT (((SUM (MAXPERM) - (SUM(CURRENTPERM)) - SUM(CURRENTSPOOL)) /  
 NULLIFZERO (SUM (MAXPERM))) * 100) (TITLE'% FREE', FORMAT'ZZ9.99') 
FROM     
 DBC.DISKSPACE;
 
 SELECT DatabaseName CAST (SUM (MaxPerm) AS FORMAT 'zzz,zzz,zz9')
 ,CAST (SUM (CurrentPerm) AS FORMAT 'zzz,zzz,zz9'),CAST (((SUM (CurrentPerm))/
 NULLIFZERO (SUM(MaxPerm)) * 100) AS FORMAT 'zz9.99%') AS "% Used"
 FROM  DBC.DiskSpace 
GROUP BY  1 
ORDER BY  4 DESC ;

9) To find Percent of disk available for spool on TD server

SELECT (((SUM (MAXPERM) - SUM (CURRENTPERM)) / NULLIFZERO (SUM   
 (MAXPERM))) * 100) (TITLE'% AVAIL FOR SPOOL', FORMAT'ZZ9.99') 
FROM      
 DBC.DiskSpaceV;

10) To find Disk space for a given database on TD server
SELECT SUM (MAXPERM) 
FROM    DBC.DiskSpaceV 
WHERE    DATABASENAME='XXXX';

11) To find Disk space for a given table on TD server
SELECT SUM (CURRENTPERM) 
FROM    DBC.TablesizeV 
WHERE    DATABASENAME='XXX'  
    AND TABLENAME = 'XXXX';

12) To check database space you could run the following query
 Select database name, sum (current perm), sum (maxperm) 
from    dbc.diskspace 
group  By 1;


13)  To find out which child database have a free perm 

Select database name, sum (current perm), sum (maxperm), sum (maxperm) - 
  Sum (current perm) 
from    dbc.diskspace 
Group by 1;

14) To find MaxPerm, Current perm and Peak perm on dbc disks pace

  Select sum (current Perm), sum (peak Perm), sum (MaxPerm) 
from    dbc.DiskSpace;

15) Disk Space currently in use on TD Server

    SELECT SUM (CurrentPerm), SUM (MaxPerm),((SUM(currentperm) /  
    NULLIFZERO (SUM (maxperm)) * 100))(TITLE '%MaxPerm', FORMAT 'zz9.99')
    FROM DBC.DiskSpaceV;

16) Percent of space used by each database in the system on TD Server

SELECT Databasename (format 'X (12)'), SUM (maxperm), SUM(currentperm),
((SUM (currentperm))/NULLIFZERO (SUM (maxperm)) * 100)(FORMAT 'zz9.99%',
        TITLE 'Percent // Used') 
FROM    DBC.DiskSpaceV 
GROUP BY 1 
ORDER BY 4 DESC 
WITH    SUM (currentperm), SUM (maxperm);

17) Users who are running out of PERM space

SELECT Databasename (format 'X(12)'),SUM(maxperm),SUM(currentperm),
   ((SUM (currentperm))/NULLIFZERO (SUM (maxperm)) * 100)(format 'zz9.99%',
   TITLE 'Percent // Used') 
FROM    DBC.DiskSpaceV 
GROUP BY 1 
HAVING       
   (SUM (currentPerm) / NULLIFZERO (SUM (maxperm))) > 0.9 
ORDER BY 4 DESC;

18) Users using lot of spool on TD Server

SELECT databasename,SUM(peakspool) 
FROM    DBC.DiskSpaceV
   GROUP BY 1 
HAVING    SUM (peakspool) > 5000000000 
ORDER BY 2 DESC;

   You can change the value 500000000 to whatever value is appropriate for your site.
   Some sites with more space may have higher tolerance for higher spool usage and    
   Spool limits.

19) To Shows macro space used

CREATE MACRO superspace as (
SELECT    databasename (char (30)), SUM(maxperm), SUM (currentperm) 
FROM    DBC.DiskSpaceV 
GROUP BY databasename 
ORDER BY   Databasename 
WITH    SUM (maxperm), sum (currentperm););

20) To shows TableSizeV View

   The DBC.TableSizeV view provides AMP information about disk space usage at the    table level.Optionally use viewnameVX for information on only those tables that the    requesting user owns or has SELECT privileges on.

  SELECT tablename (TITLE 'Table'),currentperm (TITLE 'CurPerm'),
        vproc (TITLE 'Amp') 
FROM    DBC.tablesizeV 
WHERE    databasename='xxx' 
    AND tablename = 'xxxx' 
ORDER BY 2 DESC; 


21) To finding Skewed Tables on TD Server

    SELECT vproc AS AMP, TableName (FORMAT 'X (20)'), CurrentPerm 
FROM     
    DBC.TableSizeV 
WHERE    DatabaseName = 'USER' 
ORDER BY Table Name, AMP ;

22) Warning about AllSpaceV View

     SELECT DatabaseName,Sum(CurrentPerm) 
FROM    DBC.AllSpaceV 
GROUP BY 1    
   Having sum (currentperm) > 0 
ORDER BY 2 desc;
 
   SELECT DatabaseName,Sum(CurrentPerm) 
FROM    DBC.AllSpaceV 
where     
   databasename='DBC' 
GROUP BY 1 
having    sum(currentperm) > 0 
ORDER BY 2 desc;
 
   SELECT DatabaseName,Sum(CurrentPerm) 
FROM    DBC.DiskSpaceV 
GROUP BY 1  
   Having sum (currentperm) > 0 
ORDER BY 2 desc;

23) To find TableSizeV to report the PERM of tables in a particular database and 
   DiskSpaceV to report the PERM of the database

   SELECT MAX (CurrentPerm), SUM(CurrentPerm) 
FROM    DBC.TableSizeV
   WHERE DatabaseName = 'xxxx';
  
   DiskSpaceV reports MAX (CurrentPerm) 
    and SUM (CurrentPerm) at the database level:
 
   SELECT MAX (CurrentPerm), SUM(CurrentPerm) 
FROM    DBC.DiskSpaceV
   WHERE DatabaseName = 'xxxxx' ;

24) DBC.LogOnOffV View

 The following query returns any failed logon attempts during the last seven days

SELECT LogDate,LogTime,UserName (FORMAT 'X(10)'),Event 
FROM    DBC.LogOnOffV
 WHERE Event NOT LIKE ('%Logo%') 
    AND LogDate GT DATE  7 
ORDER BY LogDate
 LogTime;

25) To find the Max PermSpace on TD Server by using DataBase id 

Select sum (maxpermspace),
from    DBC.DataBaseSpace 
where    Databaseid='00007408'XB;
 
  ( '00007408'XB is Database id for the database)

26) 
    Select sum ( currentperm) 
from    DBC.TableSize 
where    databasename='xxx' ;
 
    Select max (currentperm) 
from    DBC.TableSize 
group by vproc 
where    
    databasename='xxx' ;

    (In above query Max Of current permspace of each amp is less than the   
     Maxpermspace/80(80 is number of amps)

    Amp is full only when it reaches =26528972=2122317760.00(Max Perm Space)/80

    Amp number which is occupied highest space is 50 I.e. 10132992.00 Bytes

27) To find Max PermSpace and Current PermSpace on TD Server  

     Select max (currentperm), max (maxperm) 
from    dbc.diskspace 
where     
   databasename='xxx';

28) To find Current PermSpace,Effective Space of TableSize and DataBaseName on TD  
    Server by using HASHMAP

   SELECT SUM (CURRENTPERM) ACTUALSPACE, MAX (CURRENTPERM)*(HASHAMP()+1
   EFFECTIVESPACE 
FROM    DBC.TABLESIZE 
WHERE    DATABASENAME = 'mydb' 
    AND  
   TABLENAME ='mytable';
 
   SELECT SUM(CURRENTPERM)/(1024*1024) ACTUALSPACE, 
   (MAX(CURRENTPERM)*(HASHAMP()+1))/(1024*1024) EFFECTIVESPACE
   FROM DBC.TABLESIZE 
WHERE    DATABASENAME = 'DB_NAME' 
    AND TABLENAME =   
   'TABLE_NAME';

29)  To use the below query and you can modify according to your needs

    Select databasename,cast(sum(maxperm)/1024/1024/1024 as decimal(7,
        2))  
    max_perm,cast(sum(currentperm)/1024/1024/1024 as decimal(7,
        2)) 
    current_perm,cast(sum(maxspool)/1024/1024/1024 as decimal(7,
        2)) 
    max_spool,cast(sum(currentspool)/1024/1024/1024 as decimal(7,
        2)) current_spool   
    from dbc.diskspace 
where    databasename in (
select    databasename 
from      
    dbc.databases 
where    dbkind = 'D' 
    and databasename in ('STG_DEV', 'STG_TST_TMP'
    'WRK_STG_DEV') ) 
group by databasename 
order by max_perm desc;

29) Find MAX(CurrentPerm) and SUM(CurrentPerm) in DBC

     select max(CurrentPerm),sum(CurrentPerm) 
from    DBC.DiskSpace

30) DBC.DiskSpace

It provides information about disk space usage (including spool) for any database or   
Account.

SELECT DatabaseName CAST (SUM (MaxPerm) AS FORMAT 'zzz, zzz, zz9')
, CAST (SUM (CurrentPerm) AS FORMAT 'zzz, zzz,zz9'),CAST (((SUM (CurrentPerm))/
NULLIFZERO (SUM (MaxPerm)) * 100) AS FORMAT 'zz9.99 %') AS "% Used"
FROM   DBC.DiskSpace 
GROUP BY   1 
ORDER BY   4 DESC;

31)  DBC.TableSize

  It provides information about disk space usage (excluding spool) for any database, table or account.
  SELECT  Vproc ,CAST (TableName AS FORMAT 'X(20)') ,CurrentPerm,
        PeakPerm
   FROM   DBC.TableSize 
WHERE    DatabaseName = USER 
ORDER BY  TableName, Vproc ;

32) DBC.AllSpace

  It provides information about disk space usage (including spool) for any database,    
  table,or account.
  SELECT  Vproc,CAST (TableName AS  FORMAT 'X(20)') ,MaxPerm,
        CurrentPerm
    FROM   DBC.AllSpace 
WHERE    DatabaseName = USER   
    ORDER BY   TableName, Vproc ;

33) You can play with SQL to SUM up the database to get total count of database or you   
   Can simply use the 
     view diskspace;
 (to get the totals across the whole database)

34) Data Skew Check




1) First check to see if the primary index is ok for tables system-wide:







SEL (MAX(CurrentPerm) - MIN(CurrentPerm)) * 100/(NULLIF(MIN(currentperm),
        0))
(NAMED variance)(FORMAT 'zzzzz9.99%'),MAX(CurrentPerm)(TITLE 'Max')
(FORMAT 'zzz,zzz,zzz,999'),MIN(currentperm)(TITLE 'Min')(FORMAT 'zzz,zzz,zzz,999'),
        TRIM(DatabaseName)||'.'||TableName (NAMED Tables)
FROM DBC.tablesizeGROUP BY DatabaseName, TableNameHAVING SUM(CurrentPerm) > 1000000 
    AND variance > 1000 
WHERE    DatabaseName NOT IN ('CrashDumps','DBC')
ORDER BY Tables;


2) If this is not the case, try to identify the job that might be responsible for the concentration of IO and cpu by checking spool usage by vproc:







//* DBC.DISKSPACE - Get AMP Number with skew for data & Peak Spool - Include % distribution efficiency */
.export file diskskew.out
.set defaults
.set separator 1
.set width 110
 
SELECT A.databasename (Format 'x(20)') (Title 'DiskSpace//DB Name')
, A.accountName (Format 'x(20)') (Title 'Acct Name'), A.vproc (Format 'zzzz9') (Title 'AMP //Number'),
        A.currentperm / (1024**2) (Format 'zz,zz9.999') (Title '//CurrPerm')
, DT.avgCurperm / (1024**2) (Format 'zz,zz9.999') (Title 'Avg//CurrPerm')
, A.currentperm / DT.avgCurperm (Format 'zzz9.99') (Title 'CurrPerm//Skew')
, A.peakspool / (1024**2) (Format 'zz,zz9.999') (Title '//PeakSpool'),
        DT.avgPeakspool / (1024**2) (Format 'zz,zz9.999') (Title 'Avg//PeakSpool'),
        A.peakspool / DT.avgPeakspool (Format 'zzz9.99') (Title 'PeakSpool//Skew')
FROM    DBC.Diskspace A,(
SELECT    databasename (Format 'x(12)') (Title 'DiskSpace//DB Name') ,
        accountName (Format 'x(12)') (Title 'Acct Name'), AVG(currentperm) (Format 'z,zzz,zz9') (Title 'CurrPerm//MBytes')
, AVG(peakspool) (Format 'z,zzz,zz9') (Title 'PeakSpool//MBytes') 
FROM    DBC.Diskspace
GROUP BY 1, 2 ) DT (databasename, accountName, avgCurperm, avgPeakspool)
WHERE    A.databasename = DT.databasename 
    AND A.accountName = DT.accountName
    AND (A.currentperm / DT.avgCurperm >= 1.30 
    OR A.peakspool / DT.avgPeakspool >= 1.30)
ORDER BY 3, 1, 2;
.set defaults
 
 
/* DBC.AMPUSAGE - Get CPU Usage & Logical Disk I/Os by Users with more than 100,000 cpu seconds */
 
.export file ampusage.out
.set defaults
.set width 110
SELECT A.accountName (Format 'x(18)') (Title 'AMPUsage//Acct Name'),
        A.username (Format 'x(22)') (Title 'User Name'), SUM(A.CPUTime) (Format 'zzz,zzz,zz9') (Title 'CPUtime'),
        SUM(A.DiskIO) (Format 'zzz,zzz,zzz,zz9') (Title 'DiskIO'), AVG(A.CPUTime) (Format 'zzz,zzz,zz9') (Title 'AvgCPUtime'),
        MAX(A.CPUTime) (Format 'zzz,zzz,zz9') (Title 'MAXCPUtime')
, MAX(A.CPUTime)/nullifzero(AVG(A.CPUTime) )(Format 'zz9.99') (Title 'CPU//Skew//Effect')
FROM DBC.AMPUsage A 
GROUP BY 1, 2 
HAVING    SUM(CPUTime) > 1e5 
ORDER BY 3 desc, 1, 2;
 
.set defaults
.set width 80
 
/* DBC.AMPUSAGE - Get CPU Usage & Logical Disk I/Os by User with skewing greater than 30% over average*/
 
.export file ampusageskew.out
.set defaults
.set width 94
SELECT A.accountName (Format 'x(18)') (Title 'AMPusage//Acct Name'),
        A.username (Format 'x(12)') (Title 'User Name'), A.vproc (Format '99999') (Title 'Vproc'),
        A.CPUTime (Format 'zz,zzz,zz9') (Title 'CPUtime'), DT.AvgCPUTime (Format 'zz,zzz,zz9') (Title 'AvgCPUtime'),
        A.CPUTime/NULLIFZERO(DT.AvgCPUTime)(Format 'zz9.99')(Title 'Ratio//to Avg')(Named CpuRatio),
        A.DiskIO (Format 'zzz,zzz,zzz,zz9') (Title 'AvgDiskIO'), A.DiskIO/NULLIFZERO(DT.avgDiskIO) (Format 'z9.99') (Title 'Ratio//to Avg')
FROM    DBC.AMPUsage A,(
SELECT    accountName, username, AVG(CPUTime), SUM(CPUTime)
, AVG(DiskIO), SUM(DiskIO) 
FROM    DBC.AMPUsageGROUP BY 1, 2
HAVING    SUM(CPUTime) > 1e4) DT (accountName, username, avgCPUtime,
        sumCPUtime, avgDiskIO, sumDiskIO)
WHERE A.accountname = DT.accountName 
    AND A.username = DT.username
    AND CpuRatio > 1.30 
ORDER BY 5, 1, 2, 3;

3) Once you have identified the application, check the tables involved to see if secondary indexes are skewed or if the access to the tables is causing a concentration on the clique...







/* The following query will provide the distribution by amp for a given index or column. */
 
select  hashamp (hashbucket(hashrow(index 
    or column))),count(*) 
from    database.table
group by 1 
order by 2 desc;








/* The following query will provide the number of collisions  for row hash. */







/* The following query will provide the number of collisions  for row hash. */
 
select hashrow(index 
    or column), count(*) 
from    database.table 
group by 1 
order by 1 
having    count(*) > 10;


35) To find out I/O and CPU Usage from this data Dictionary Table DBC.AMPUSAGE
   
      SELECT ACCOUNTNAME, USERNAME,SUM(CPUTIME) AS CPU,SUM (DISKIO)
   AS DISKIO 
FROM    DBC.AMPUSAGE 
GROUP BY 1,2 
ORDER BY 3 DESC;

36) Find the total space in your system

       Select sum (maxperm) 
from    dbc.diskspace;


37) How to find CYLINDERS
   Go to - >Teradata Manager ->Select MENU -> Investigate then “Space Usage”, then   
   “Cylinders by vproc “. it tells you the number of available cylinders. Now, divide the   
   Total space by the number of cylinders to get the size of a cylinder.

38)    SELECT databasename ,SUM ((((MaxPerm) / 1024) /1024) / 1024) AS   
     Maximum_Space_GB ,SUM ((((CurrentPerm) / 1024) /1024) / 1024) AS  
     Current_Space_Used_GB,((100-Percent_Used)*Maximum_Space_GB)/100 AS  
     Free_Space_GB,(Current_Space_Used_GB / NULLIFZERO (Maximum_Space_GB) *  
     100 ) (FORMAT 'zz9.99%' , TITLE 'Percent // Used') AS Percent_Used,
     (Free_Space_GB / NULLIFZERO (Maximum_Space_GB)) 
FROM           DBC.DISKSPACE  
     Group By 1 
ORDER       BY 5 DESC;

Here is SQL to show you a user's max spool (amount allocated) and peak spool (highest amount used):
SELECT DATABASENAME (Title 'User'),sum(MaxSpool) (format '---,---,---,---,--9')(char(19))(title 'max Spool'),
        sum(PeakSpool) (format '---,---,---,---,--9')(char(19))(title 'Peak Spool'),
        sum(Maxtemp) (format '---,---,---,---,--9')(char(19))(title 'max temp'),
        sum(PeakTemp) (format '---,---,---,---,--9')(char(19))(title 'Peak Temp') 
FROM    DBC.DISKSPACE
WHERE DatabaseName = 'DBNAME' 
ORDER BY 1 
GROUP BY 1;

Here is SQL to give you information including spool and temporary space allocations for a user:

SELECT A.DatabaseName (CHAR(20)), A.OwnerName (CHAR(20)), A.AccountName (CHAR(20)),
        B.DefaultDatabase (CHAR(20)), (A.SpoolSpace / 1000000000) (NAMED Spool_In_gig),
        (A.TempSpace / 1000000000) (NAMED Temp_In_gig)
FROM DBC.DATABASES  A, DBC.DBASE  B 
WHERE    A.DatabaseName = B.DatabaseNamei
    AND A.DatabaseName = 'Joe_User' 
ORDER BY 1;

39) Deleting DBQL tables should not cause any problem.

Two steps for a cleanup job could be:

(1)  Insert selects the rows to a private database if you want to keep some history. Consider using (empty) staging tables for optimized insert-select.


(2)  Delete DBC.DBQL* tables (except for the RuleTbl and RuleCountTbl which anyways you can't). If you are deleting based on date then use collectiontimestamp on all but DBQLogTbl. We use Start Time to delete from DBQLogTbl as the collectiontimestamp is way off in this table (may be a bug)

43) DBC.DiskSpace (Useful)
It provides information about disk space usage (including spool) for any database or account.

SELECT  DatabaseName,CAST (SUM (MaxPerm) AS FORMAT 'zzz,zzz,zz9'),
        CAST (SUM (CurrentPerm) AS FORMAT 'zzz,zzz,zz9'),CAST (((SUM (CurrentPerm))/ NULLIFZERO (SUM(MaxPerm)) * 100) AS FORMAT 'zz9.99%') AS "% Used" 
FROM      DBC.DiskSpace 
GROUP BY   1
ORDER BY   4 DESC;

44)  DBC.TableSize
It provides information about disk space usage (excluding spool) for any database, table or account
SELECT   Vproc,CAST (TableName  AS FORMAT 'X(20)') ,CurrentPerm,
        PeakPerm 
FROM      DBC.TableSize 
WHERE    DatabaseName = USER 
ORDER BY  TableName, Vproc ;

45) DBC.AllSpace
It provides information about disk space usage (including spool) for any database, table, or account.
 SELECT  Vproc,CAST (TableName AS  FORMAT 'X(20)') ,MaxPerm,CurrentPerm 
FROM      DBC.AllSpace 
WHERE    DatabaseName = USER 
ORDER BY   TableName, Vproc 



Credits:

No comments:

Post a Comment