Underestimating space requirements for databases, users, and profiles leads to performance problems. This is because Teradata Database requires free disk cylinders to enable the growth of permanent, temporary, and spool space as well as to enable the growth of permanent journal tables.
These tables cannot share cylinders when you make your permanent, temporary, and spool disk space assignments. For databases with uneven or skewed data distribution, current space assignments may be much higher than normal to accommodate the skewed use. In such cases, global space accounting can help and space assignments can be normal (for example, an average value) because space is managed at a global level. For more information about global space accounting, see About Global Space Accounting.
The best practice for the initial sizing of the disk space required by a database, user, or profile is to make a good estimate of the space these will require when a database, user, or profile is created, and then to modify those assignments at a later time using MODIFY DATABASE, MODIFY USER, or MODIFY PROFILE requests as appropriate.
You should always consider the following disk space requirements when making disk space assignments for databases, users, and profiles.
Teradata Database requires this type of disk space … | For … |
---|---|
spool | materializing volatile tables. |
temporary | materializing global temporary tables. To materialize global temporary tables, temporary space must have enough empty disk cylinders to contain their rows. |
Modifying the Disk Space Currently Assigned to Databases, Users, or Profiles
Modifying the permanent, temporary, and spool space assignments for databases, user, and profiles is a simple operation using MODIFY DATABASE, MODIFY USER, and MODIFY PROFILE requests as appropriate.
- DBC.AllSpaceV[X]
Use this view to report disk space usage, including spool space, for any account, database, table, or user.
The following request reports how the space currently used by the department table is distributed on each AMP.
SELECT DatabaseName, TableName, AMP, CurrentPerm FROM DBC.AllSpaceV WHERE TableName = ’department’ ORDER BY 1,2,3; DatabaseName TableName AMP CurrentPerm ------------ ---------- ---- ------------ test department 1-0 1,024 test department 1-1 512 test department 1-2 1,024 test department 1-3 512 personnel department 1-0 2,048 personnel department 1-1 1,536 personnel department 1-2 1,536 personnel department 1-3 1,536 user1 department 1-0 2,048 user1 department 1-1 1,536 user1 department 1-2 1,536 user1 department 1-3 1,536
The following request reports the values for the MaxPerm and CurrentPerm columns for each table contained by user. Because user only contains one table, employee, the request only returns information about employee and All, where the All “table” represents all of the tables contained by the specified database or user. In this case, All represents all of the tables contained by the user named user.
The MaxPerm value for All is the amount of permanent space defined for user. Because user contains only one table, the number of bytes on each AMP is the same for both All and employee. All of the reported values represent the size of the employee table.
Note that employee returns 0 bytes in the MaxPerm column because tables do not have MaxPerm space. Only databases and users have MaxPerm space, represented by All.
SELECT Vproc, TableName (FORMAT ‘X(20)’), MaxPerm, CurrentPerm FROM DBC.AllSpaceV WHERE DatabaseName = user ORDER BY TableName, Vproc; Vproc TableName MaxPerm CurrentPerm ----- --------- --------- ----------- 0 All 2,621,440 64,000 1 All 2,621,440 64,000 2 All 2,621,440 112,640 3 All 2,621,440 112,640 … … … … 0 employee 0 41,472 1 employee 0 41,472 2 employee 0 40,960 3 employee 0 40,960 … … … …
- DBC.DiskSpaceV[X]
Use this view to report disk space usage, including spool space, for any account, database, or user.
The following request reports the permanent disk space across all AMPs.
SELECT AMP, DatabaseName, CurrentPerm, MaxPerm FROM DBC.DiskSpaceV; AMP DatabaseName CurrentPerm MaxPerm --- ------------ ----------- --------- . . . . . . . . 0-0 stst14 0 125,000 0-0 ud12 0 125,000 1-0 atest 1,536 125,000 1-0 a1 0 247,500 1-0 btest 3,584 5,000 1-0 b2test 49,664 250,000 . . . . . . . . 1-1 atest 1,536 125,000 1-1 a1 0 247,500 1-1 btest 3,584 5,000 1-1 b2test 50,688 250,000 . . . . . . . . 1-2 atest 1,536 125,000
Similarly, you can submit a request like the following to calculate the percentage of space used by a particular database. Note that the request uses a NULLIFZERO specification to avoid a divide by zero exception.
SELECT DatabaseName, 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;
This request reports the following information from DBC.DiskSpaceV.
DataBaseName Sum(MaxPerm) Sum(CurrentPerm) Percent Used ------------ -------------- ----------------- ------- Finance 1,824,999,996 1,796,817,408 98.46% DBC 12,000,000,006 8,887,606,400 73.98% Spool_Reserve 2,067,640,026 321,806,848 15.56% CrashDumps 300,000,000 38,161,408 12.72% SystemFE 1,000,002 70,656 7.07%
- DBC.GlobalDBSpaceV[X][_SZ]Use this view to see global space accounting information, including the following data:
- The space limits for a database or user at the system level
- The sizes of the total space allocated to AMPs in the system
- The total peak allocated space sizes across the system
- The skew limits associated with the different space values
The following request reports unallocated space remaining in the database:
SELECT databasename, allocatedperm, maxperm,maxperm-allocatedperm (TITLE 'UnallocatedSpace'), permskew FROM DBC.GlobalDBSpaceV where databasename='example2';
This request reports the following information:
DatabaseName example2 AllocatedPerm 5,148 MaxPerm 5,200 UnallocatedSpace 52 PermSkew 10,000 The maximum allocated peak spool for a given user: SELECT peakallocatedspool, databasename FROM DBC.GlobalDBSpaceV ORDER BY 1 DESC; *** Query completed. 30 rows found. 2 columns returned. *** Total elapsed time was 1 second. PeakAllocatedSpool DatabaseName -------------------------- ------------------------------------------------ 71,273,111,876 example 71,273,111,876 example2 0 LockLogShredder 0 TDMaps 0 tdwm 0 SysAdmin 0 Default 0 All 0 PUBLIC 0 SYSSPATIAL 0 SYSUIF 0 TDPUSER 0 TD_SYSXML 0 TD_SYSGPL 0 SYSLIB 0 External_AP 0 Crashdumps 0 SYSJDBC 0 TD_SERVER_DB 0 TDStats 0 SYSUDTLIB 0 SYSBAR 0 SystemFe 0 SQLJ 0 Sys_Calendar 0 TD_SYSFNLIB 0 TDQCD 0 EXTUSER 0 dbcmngr 0 DBC
- DBC.TableSizeV[X]
Use this view to report disk space usage excluding spool space, for any account or table.
The following request reports the total disk space currently used by the employee table with its peak space usage.
SELECT SUM(PeakPerm), SUM(CurrentPerm) FROM DBC.TableSizeV WHERE TableName = ’employee’; Sum(PeakPerm) Sum(CurrentPerm) ------------- ---------------- 260,608 260,608
In this case, the 2 sums match, indicating that the disk space currently used by the employee table is also its peak space usage.
The following request reports poorly distributed tables by returning the CurrentPerm values for table_2 and table_2_nusi, the only tables contained by user across all AMPs.
SELECT Vproc, TableName (FORMAT ‘X(20)), CurrentPerm, PeakPerm FROM DBC.TableSizeV WHERE DatabaseName = user ORDER BY TableName, Vproc; Vproc TableName CurrentPerm PeakPerm ----- --------- ----------- -------- 0 table_2 41,472 53,760 1 table_2 41,472 53,760 2 table_2 40,960 52,736 3 table_2 40,960 52,736 4 table_2 40,960 52,760 5 table_2 40,960 52,760 6 table_2 40,960 52,272 7 table_2 40,960 52,272 0 table_2_nupi 22,528 22,528 1 table_2_nupi 22,528 22,528 2 table_2_nupi 71,680 71,680 3 table_2_nupi 71,680 71,680 4 table_2_nupi 9,216 9,216 5 table_2_nupi 9,216 9,216 6 table_2_nupi 59,392 59,392 7 table_2_nupi 59,392 59,392
The report indicates that table_2 is evenly distributed across the AMPs, but that table_2_nusi is not, with its current permanent space clumped into spaces of 9,216 bytes, 22,528 bytes, 59,392 bytes, and 71,680 bytes across 2 AMPs each.
Different Disk Space Views Return Different Results
It is important to understand that because the DBC.AllSpaceV, DBC.DiskSpaceV, and DBC.TableSizeV system views report different information, using superficially identical requests to return information using those views often returns conflicting results. For example, while DBC.AllSpaceV and DBC.DiskSpaceV include information about spool space in their reports, DBC.TableSizeV does not.
Similarly, selecting SUM(CurrentPerm) from each of these views returns different results. For example, requesting SUM(CurrentPerm) from DBC.AllSpaceV returns a sum of All “tables,” representing the database or user total plus the sum of each table in the database or user. Submitting the same request using DBC.TableSizeV returns sums for all tables except for All, and submitting the query using DBC.DiskSpaceV returns sums only for All.
- DBC.AllSpaceV
SELECT MAX(CurrentPerm), SUM(CurrentPerm) FROM DBC.AllSpaceV WHERE DatabaseName = user; Maximum(CurrentPerm) Sum(CurrentPerm) -------------------- ---------------- 112,640 1,308,672
The reported values represent the disk space for all of the tables contained by user plus the disk space for the All table.
- DBC.DiskSpaceV
SELECT MAX(CurrentPerm), SUM(CurrentPerm) FROM DBC.DiskSpaceV WHERE DatabaseName = user; Maximum(CurrentPerm) Sum(CurrentPerm) -------------------- ---------------- 112,640 654,336
The reported values represent the disk space for the All table.
- DBC.TableSizeV
SELECT MAX(CurrentPerm), SUM(CurrentPerm) FROM DBC.TableSizeV WHERE DatabaseName = user; Maximum(CurrentPerm) Sum(CurrentPerm) -------------------- ---------------- 71,680 654,336
The reported values represent the disk space for all of the tables contained by user, but do not include the All table.
Use the following table to determine which system views are appropriate for requesting disk space information for different database objects.
To report disk space information at this level … | You should use this system view … |
---|---|
table | DBC.TableSizeV |
database, user, or profile | DBC.DiskSpaceV |
database or user with global space accounting | DBC.GlobalDBSpaceV |
tables plus database, user, or profile | DBC.AllSpaceV |