Archives of the TeradataForum

Message Posted: Tue, 20 Jun 2006 @ 17:37:04 GMT


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

Subj:		Re: Questions about Query Logs

From:		Dong, Charles

Thank you for your explanation. You may be familiar with the implementation. You did show your name.

As a user, I want to know how to retrieve query log information from the query log tables. I have not had any problems with Oracle or Informix. If we can know the relationship among these query log tables and/or views, it should not be difficult to get information from the system. I understand performance is very important, and logging uses a lot of system resources. But data integrity is more important, and it cannot be compromised.

I checked our system again using Teradata Administrator again this morning. And I try to use more accurate information acquired from the system. Ex., QryLog is a view not a table, etc. The followings are the tests I did.

(1) View QryLog has a CollectTimeStamp column. I was wrong in my previous email.

(2) View QryLog and QryLogSQL are both based on Table dbc.DBQLogTbl.

A. Show Definition of Table DBQLogTbl produces

     ProcId Decmal(5, 0) NOT NULL
     CollectTimeStamp TimeStamp(0) NOT NULL
     QueryID Integer NOT NUL

     PRIMARY INDEX (ProcID, CollectTimeStamp)  ========> NOT UNIQUE

B. The description for QryLog shows "Each row in the QryLog contains DBQL default information for A QUERY".

The description for QryLogSQL shows "The QryLogSQL view contains the SQL of A QUERY".

     Run:  select count(*)
              from dbc.DBQLogTbl
              where CollectTimeStamp > TimeStamp '2006-06-13 09:59:59';

           Returned 3437


     Run:  select count(*)
              from dbc.QryLog
              where CollectTimeStamp > TimeStamp '2006-06-13 09:59:59';

           Returned 3437


     Run:  select count(*)
              from dbc.QryLogSQL
              where CollectTimeStamp > TimeStamp '2006-06-13 09:59:59';

           Returned 3370

Why they do not match? Some (67) SQLs are missing! Yesterday I checked the system, "Next Event: 6/20/2006 6:05:31 PM". CollectTime Stamp is with 7 days. And detail retention is 7, summary detention 180. I noticed that the interval for data collection is 5 minutes (From QryLogSQL.CollectTimeStamp. I do not know how to set up and display the value of interval for collection). I did the 3 SQLs within an interval. Our system is idle except that I am using it and some light system activities.

C. Show Definitions of View QryLog and QryLogSQL give

Both of them are based on Table DBQLogTbl ONLY;

     ProcID decimal(5, 0) = DBQLogTbl.ProcID mod 100000
     CollectTimeStamp
     QueryID decimal(18, 0) = 0 OR (DBQLogTbl.ProcID / 100000 * 10000000000 + DBQLogTbl.QueryID)

The description of ProcID for both views: "(Primary Key) ID of the Processing Engine from which the query was executed".

The description of CollectTimeStamp for both views: "(Primary Key) Time and date when the DBQLog cache was allocated".

The description of QueryID for both views: "(Foreign Key) Unique value for this ProcID.

As it states, the combination of ProcID and ColectTimeStamp is a Primary Key. They must be unique.

Run:

     select ProcID, CollectTimeStamp, count(*)
     >From dbc.qryLog
     Where CollectTimeStamp > TimeStamp '2006-06-13 09:59:59'
     Group by ProcID, CollectTimeStamp
     Having count(*) > 1
     Order by ProcID, count(*), CollectTimeStamp;

Returned two ProcID values, the max count is 72, min 2.

Run:

     select ProcID, CollectTimeStamp, count(*)
     >From dbc.qryLogSQL
     Where CollectTimeStamp > TimeStamp '2006-06-13 09:59:59'
     Group by ProcID, CollectTimeStamp
     Having count(*) > 1
     Order by ProcID, count(*), CollectTimeStamp;

Returned two ProcID values, the max count is 176, min 2.

ProcID and CollectTimeStamp is NOT PRIMARY KEY. Does Teradata have a different definition of PK? A logical view does not have physical data presence in the system, and PI or PK cannot be used here. Unique constraint can work. But it is not unique.

D. As it states, QuerID is unique for this ProcID. Then, the combination of ProcID and QueryID should be unique.

Run:

     Select ProcID, QueryID, count(*)
     >From dbc.QryLog
     Where CollectTimeStamp > TimeStamp '2006-06-13 09:59:59'
     Group by ProcID, QueryID
     Order by ProcID, count(*) desc, QueryID;

It returned 3453 rows all with count = 1.

Run:

     Select ProcID, QueryID, count(*)
     >From dbc.QryLog
     Where CollectTimeStamp > TimeStamp '2006-06-13 09:59:59'
     Group by ProcID, QueryID
     Order by ProcID, count(*) desc, QueryID;

It returned 3386 rows all with count = 1.

So far, so good. The description is correct. But some (67) SQLs are missing as Results from B shows.

Run:

     Select a.ProcID, a.QueryID, a.StartTime, a.CollectTimeStamp
     >From dbc.QryLog a
     Where a.CollectTimeStamp > TimeStamp '2006-06-13 09:59:59'
           not exists
             (select *
              from dbc.QryLogSQL b
              where a.ProcID = b.ProcID and
                    a.QueryID = b.QueryID and
                    b.CollectTimeStamp > TimeStamp '2006-06-13 09:59:59')
     Order by a.ProcID, a.CollectTimeStamp, a.QueryID;

It returned 191 rows. One ProcID has 19 rows, the other 172.

I switched the position of the two views in the above query.

Run:

     Select a.ProcID, a.QueryID, a.CollectTimeStamp
     >From dbc.QryLogSQL a
     Where a.CollectTimeStamp > TimeStamp '2006-06-13 09:59:59'
           not exists
             (select *
              from dbc.QryLog b
              where a.ProcID = b.ProcID and
                    a.QueryID = b.QueryID and
                    b.CollectTimeStamp > TimeStamp '2006-06-13 09:59:59')
     Order by a.ProcID, a.CollectTimeStamp, a.QueryID;

It returned 124 rows. One ProcID has 62 rows, the other 63.

E. ProcID and QueryID are derived from Table dbc.DBQLogTbl. The two columns should have matching values in both views. D shows their values are unique for each of them. So, the two views should have one to one relationship.

Run:

     Select a.ProcID, a.QueryID, count(*)
     >From dbc.QryLog a join dbc.QryLogSQL b
            on a.ProcID = b.ProcID and
               a.QueryID = b.QueryID
     Where a.CollectTimeStamp > TimeStamp '2006-06-13 09:59:59'
     Group by a.ProcID, a.QueryID
     Order by count(*), a.ProcID, a.QueryID;

It returned 917 rows with count = 2, and 2458 rows with count = 1. It failed the test.

F. ProcID and CollectTimeStamp are both derived from Tabl dbc.DBQLogTbl. And their combination is the primary keys of the two views.

Run:

     Select count(*)
     >From dbc.QryLog a join dbc.QryLogSQL
            on a.ProcID = b.ProcID and
               a.CollectTimeStamp = b.CollectTimeStamp
     Where a.CollectTimeStamp > TimeStamp '2006-06-13 09:59:59';

It returned 0. How can it happed?!

Donna, I have, here, provided the detailed tests and their results. I would be very happy if you can explain what has happened to our system, or point out what I did wrong or our system should be configured in a proper way, or provide detailed information about the relationship among these QryLog* views and how to join them ... I am not a Teradata expert, but I have experience in SQL and RDBMS. All the Teradata tools use the tables and views with SQLs. I prefer use both and tools SQLs as a DBA instead of only tools when you have questions about the database. DBAs should have the expertise to write SQLs when necessary, and Teradata should provide the relationship for DBA userss. You have to know it is data or tools or none of them that cause problems.

I hope some experts can do some tests in their systems to see if they can reproduce similar results.

I appreciate all help from the forum.

Charles


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

Attachments

Library

Quick Reference