Blog

The best minds from Teradata, our partners, and customers blog about relevant topics and features.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

05-24-2010
02:55 PM

- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Email to a Friend
- Printer Friendly Page
- Report Inappropriate Content

05-24-2010
02:55 PM

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 );

22 Comments

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.