Archives of the TeradataForum

Message Posted: Fri, 05 Jan 2013 @ 01:17:16 GMT

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

In my environment i have scheduled to run once a week.

Published on Teradata Developer Exchange (developer.teradata.com) Home > Blogs > carrie's blog > If You're Not Collecting Statistics on Your Dictionary Tables, Do It. Now.

If You're Not Collecting Statistics on Your Dictionary Tables, Do It. Now.

By carrie
Created May 24 2010 - 2:55pm

I've mentioned it before, Marcio has blogged about it, customers have brought it up at the Partners Conferences. It's cheap, fast, risk-free, with immediate results. But some of you are still not getting it. Or it could be you're one of the few who truly don't need it.

Either way, I'm going to take this opportunity to pound away a little more on the advantages of collecting statistics on your dictionary tables.

I'm not talking about full collection on all columns of all dictionary tables. For one thing, only the hashed dictionary tables let you do collections. Just to remind you, the non-hashed tables that do not support statistics collection include:

     DBC.Acctg - Resource usage by Account/User
     DBC.ChangedRowJournal - Down-AMP recovery journal
     DBC.DatabaseSpace - Database and table space accounting
     DBC.LocalSessionStatusTable - Last request status by AMP
     DBC.LocalTransactionStatusTable - Last transaction consensus status
     DBC.OrdSysChngTable - AMP recovery journal
     DBC.RecoveryLockTable - Recovery session locks
     DBC.RecoveryPJTable - Permanent journal recovery
     DBC.SavedTransactionStatus - AMP recovery table

You only need to consider collecting on columns where you think it could speed up dictionary access for queries that you often run. Dictionary tables where collected statistics are often useful include:

     DBC.TVM
     DBC.DBase
     DBC.TVFields
     DBC.AccessRights
     DBC.Indexes
     DBC.Profiles
     DBC.Owners
     DBC.Roles
     DBC.RoleGrants
     DBC.UDFInfo

An important thing to remember is that dictionary tables are typically very small compared to your usual production tables. Teradata DBAs have told me that their usual statistics collection time for the dictionary tables is in the 1 minute to 3 minute range. Most of you can afford that.

At one Teradata site, DBQL data showed them that 3K to 5K queries per day were running against their DBC tables. Most of these dictionary queries were coming from their own home-grown applications and from PMON and Teradata Manager.

They discovered that MS-Access, based on how they were using it, appeared to be getting CurrentPerm values from the DBC.TableSize view frequently, apparently to check the size of the data before deciding to bring it back from Teradata.

Some of the more complex views that come with your system may run better when stats have been collected on the underlying base tables. At one site a simple query against the UserRoleRights view in DBC used 40 GB of spool and took several minutes to complete. After they collected stats it used 4 GB of spool and came back in seconds. Sound familiar?

Here's a listing from yet another Teradata site, showing which dictionary table statistics they collect statistics on. Customize your dictionary collections based on what you actually need. DBQL can be your good friend when it comes to better understanding of what your dictionary access actually looks like. You might be surprised!

     COLLECT STATISTICS DBC.udfinfo column ( DatabaseId ,FunctionName );
     COLLECT STATISTICS DBC.udfinfo column DatabaseId ;
     COLLECT STATISTICS DBC.udfinfo index (FunctionID );
     COLLECT STATISTICS DBC.tvm COLUMN (DATABASEID ,TVMNAMEI);
     COLLECT STATISTICS DBC.tvm COLUMN CREATEUID;
     COLLECT STATISTICS DBC.tvm COLUMN LASTALTERUID;
     COLLECT STATISTICS DBC.tvm COLUMN TABLEKIND;
     COLLECT STATISTICS DBC.tvm COLUMN DATABASEID;
     COLLECT STATISTICS DBC.tvm COLUMN TVMID;
     COLLECT STATISTICS DBC.tvm COLUMN TVMNAME;
     COLLECT STATISTICS DBC.Dbase COLUMN DATABASENAMEI;
     COLLECT STATISTICS DBC.Dbase COLUMN DATABASEID;
     COLLECT STATISTICS DBC.DBase COLUMN OWNERNAME;
     COLLECT STATISTICS DBC.DBase COLUMN LASTALTERUID;
     COLLECT STATISTICS DBC.DBase COLUMN (DATABASENAME ,LASTALTERUID);
     COLLECT STATISTICS DBC.DBase COLUMN DATABASENAME;
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN (USERID ,DATABASEID);
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN (TVMID ,DATABASEID,USERID);
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN DATABASEID;
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN (TVMID ,DATABASEID);
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN (TVMID ,USERID);
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN TVMID;
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN USERID;
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN (AccessRight,GrantorID);
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN (AccessRight,CreateUID);
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN (FieldId,AccessRight);
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN FieldId;
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN AccessRight;
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN GrantorID;
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN CreateUID;
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN (DatabaseId,AccessRight);
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN (TVMId,AccessRight);
     COLLECT STATISTICS DBC.OWNERS COLUMN OWNERID;
     COLLECT STATISTICS DBC.OWNERS COLUMN OWNEEID;
     COLLECT STATISTICS DBC.OWNERS COLUMN (OWNEEID ,OWNERID);
     COLLECT STATISTICS DBC.ROLES COLUMN ROLENAMEI;
     COLLECT STATISTICS DBC.ROLES COLUMN ROLEID;
     COLLECT STATISTICS DBC.RoleGrants COLUMN GRANTEEID;
     COLLECT STATISTICS DBC.HOSTS COLUMN LogicalHostId;
     COLLECT STATISTICS DBC.HOSTS INDEX ( HostName );
     COLLECT STATISTICS DBC.TVFIELDS COLUMN TableId;
     COLLECT STATISTICS DBC.TVFIELDS INDEX ( TableId ,FieldId );
     Collect Statistics data dictionary database

     Source URL (retrieved on Jun 21 2010 - 7:23am):

developer.teradata.com/blog/...

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