Examples: Using QryLogFeatureListV - Advanced SQL Engine - Teradata Database

Example: Using QryLogFeatureListV to List Features

The example displays the current list of features being logged:

SELECT featurename FROM DBC.QryLogFeatureListV;

Example: Using QryLogFeatureListV to Generate a Feature Usage Report

The example generates a report on feature usage based on all the DBQLogTbl rows with FeatureUsage bitmap:

select cast(b.FeatureName as char(50)), 
sum(GetBit(a.FeatureUsage,(2047 - b.FeatureBitpos))) as FeatureuseCount 
from DBC.DbqLogTbl a, DBC.QryLogFeatureListV b 
group by b.FeatureName;

Result:

FeatureName                                         FeatureuseCount
--------------------------------------------------  ---------------
Character Partition Primary Index                                 4
Multi Level Partition Primary Index                              52
Increased Partition Level Partition Primary Index                 1
Partition Primary Index                                          65

Example: Using QryLogFeatureListV to Find the Percentage of Requests that Use Block Level Compression

The example finds the percentage of requests in DBQLogTbl that used Block Level Compression:

select cast(b.FeatureName as char(50)), 
cast( cast(NULLIFZERO(sum(GetBit(a.FeatureUsage,(2047 - b.FeatureBitpos)))) 
as FLOAT)/count(a.QueryID)*100 as FLOAT FORMAT '----,---,---,---,--9.999' ) 
as FeatureUsePercent  
from DBC.dbqlogtbl a, DBC.QryLogFeatureListV b group by b.FeatureName 
where b.FeatureName = 'Block Level Compression';

Result:

FeatureName                                FeatureUsePercent
----------------------------------- ------------------------
Block Level Compression                               65.164 

Example: Using QryLogFeatureListV to Find the Usage Percentage of All Requests

The example finds the usage percentage of all requests:

select cast(b.FeatureName as char(50)), 
cast( cast(NULLIFZERO(sum(GetBit(a.FeatureUsage,(2047 - b.FeatureBitpos)))) 
as FLOAT)/count(a.QueryID)*100 as FLOAT FORMAT '----,---,---,---,--9.999' ) as FeatureUsePercent 
from DBC.dbqlogtbl a, DBC.QryLogFeatureListV b group by b.FeatureName order by 2 desc;

Result:

FeatureName                                FeatureUsePercent
----------------------------------- ------------------------
Block Level Compression                               65.164
Primary Index                                         65.164
Hashed Table                                          65.164
[...]  
Unique Hashed Index                                   23.478
Parameterized Query                                   14.876
Partition Level Locking                               12.915
Secondary Index                                        9.575
Teradata Stored Procedure                              9.139   
[...]                               

Example: Using QryLogFeatureListV to Query Native Object Store

The Native Object Store feature is logged using DBQL feature logging and can be queried using the following SQL:

SELECT CAST(b.FeatureName AS CHAR(75)) ,
SUM(GetBit(a.FeatureUsage,(2047 - b.FeatureBitpos))) AS FeatureuseCount
FROM DBC.DbqLogTbl a, DBC.QryLogFeatureListV b
WHERE b.FeatureName='Native Object Store'
GROUP BY b.FeatureName;