Archives of the TeradataForum

Message Posted: Sun, 07 Sep 2003 @ 16:59:36 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

We've tied the clearpeakdisk to the monthly job that collects disk utilization statistics. Our capacity planning people have wanted an instantaneous disk-in-use measurement for more than 10 years. (It's now #1 on their list of Teradata deficiencies, up from the #2 spot because per statement/process resource consumption is now available at V2R5 in the SQL log).

Because they wanted the peak space data so badly, we tried running clearpeakdisk hourly for a while. We had to quit for two reasons: 1) overhead was too high (7K users + 17K tvm objects times #vprocs rows for update) and 2) system stability problems (DBC1012/5100M era, but we have *long* memories when the pain level is high).

/dave hough

p.s. Other than instantaneous disk usage, the only reason we had to run clearpeakdisk frequently was to aid the query developers in finding spool usage problems. To support that function, I created this macro to clear the space for a single user:

REPLACE MACRO DBC.ClearUserPeak(databasenamei char(30), databaseid byte(8))
AS  ( ROLLBACK WORK 'Name and ID do not refer to same database.'
      where not exists
       (select databaseid from dbc.dbase
        where databasenamei = :databasenamei and databaseid = :databaseid);
      UPDATE DataBaseSpace
           SET PeakPermSpace = 0, PeakSpoolSpace = 0,
               PeakTempSpace = 0
      where databaseid = :databaseid;);

The databaseid parameter is required because DataBaseSpace is an unhashed table and can't participate in a join to convert from name to id. I usually do:

select trim(databasenamei), databaseid from dbc.dbase where
databasenamei = ;

and paste the results back into an execute statement:

execute DBC.ClearUserPeak('','00123ABC'XB);

	<Prev	Next>		<<First	<Prev	Next>	Last>>