Can someone please explain the below query in detail?

Database
Enthusiast

Can someone please explain the below query in detail?

Please expalin in detail how exactly we are getting total size of the DB and the free space in the DB.

Query used to find the free space.

SELECT DATABASENAME,SUM(MAXPERM)/1073741824 AS TOTAL_SPACE, 

SUM(CURRENTPERM)/1073741824 AS USED_SPACE, (TOTAL_SPACE - USED_SPACE) AS FREE_SPACE

FROM DBC.DISKSPACE WHERE DATABASENAME ='XXXX'

GROUP BY 1

What is MAXPERM, CURRENTPERM ?

Please expalin in detail how exactly we are getting the skew factor of the table in the below query.

Query used to find the skew factor of a table.

SELECT TSIZE.DATABASENAME

,TSIZE.TABLENAME

,SUM(TSIZE.CURRENTPERM) AS CURRENTPERM

,SUM(TSIZE.PEAKPERM) AS PEAKPERM,

(100 - (AVG(TSIZE.CURRENTPERM)/MAX(TSIZE.CURRENTPERM)*100)) AS SKEWFACTOR

FROM DBC.TABLESIZE TSIZE

JOIN DBC.TABLES TDEF

ON TSIZE.DATABASENAME = TDEF.DATABASENAME

AND TSIZE.TABLENAME = TDEF.TABLENAME

AND TSIZE.DATABASENAME='YYYY'

GROUP BY 1,2,3,4

ORDER BY SKEWFACTOR DESC

 What is PEAKPERM?

Thanks in advance :)

3 REPLIES
Enthusiast

Re: Can someone please explain the below query in detail?

You will get lot of interesting information in Teradata Administration manual chapter 4 Space. here is extract from chapter which tells about peek, current,max

CURRENTPERM The total number of bytes (including table headers) currently allocated

to existing data tables, index tables and subtables, stored procedures,

triggers, and permanent journals residing in a particular database/user.

This value is maintained on each AMP.

 

MAXPERM The maximum number of bytes available for storage of all (current and

future) data tables, index tables and subtables, stored procedures,

triggers, and permanent journals owned by a particular database/user.

Note: For each database or user reported, the number of permanent

bytes is divided by the number of AMPs in the configuration. The result

is recorded on each AMP and may not be exceeded on that AMP. A user

may own several objects, as long as the combined storage requirements

are within the MaxPerm limit set for the user on each AMP.

 

PEAKPERM The largest number of bytes ever used to store data in a user or database

since the last reset of this value to zero.

This value is maintained on each AMP.

To reset the PeakPerm value to zero, use the DBC.ClearPeakDisk macro

HTH

Harpreet

Enthusiast

Re: Can someone please explain the below query in detail?

Thanks for the info :)

Teradata Employee

Re: Can someone please explain the below query in detail?

Can you get historic peakperm figures before reset?