Why is so much data being written into DBQL?

Pankaj Chahar

6 years ago

There are thousands of queries submitted per minute and it is too much data.

But, To avoid the performance impact on the database due to these logged queries,We need to be pro-active and choosy about the data we want to log.

So we save the space, resources,performance and etc etc.

Note : DBQL can only be improvised but can’t be ignored.Its a pillar for Admins as well,helps in disasters 😀

But,if space and other issues are concerened, below tips can help you some:

1. DBQLRules need to be modified.

Example:

select UserName, accountstring, summaryflag from dbc.dbqlrules;

UserName      Account    Summary

------------  ---------  -------

USER                      F

WEB_TACTICAL              F

All                       F

select UserName, accountstring, summaryflag from dbc.dbqlrules;

UserName Account Summary

------------ --------- -------

USER F

WEB_TACTICAL F

All F

Suggested modifications:

Detail logging of non-tactical work

BEGIN QUERY LOGGING with SQL, OBJECTS, UTILITYINFO LIMITSQLTEXT=0 ON ALL;

1	BEGIN QUERY LOGGING with SQL, OBJECTS, UTILITYINFO LIMITSQLTEXT=0 ON ALL;

Summary threshold logging for tactical work by account name

BEGIN QUERY LOGGING with SQL, OBJECTS LIMIT THRESHOLD = 100 CPUTIME ON ALL ACCOUNT = '$M00WXYZ&D&H';

1	BEGIN QUERY LOGGING with SQL, OBJECTS LIMIT THRESHOLD = 100 CPUTIME ON ALL ACCOUNT = '$M00WXYZ&D&H';

Summary threshold logging for the Viewpoint user

BEGIN QUERY LOGGING with SQL, OBJECTS LIMIT THRESHOLD = 100 CPUTIME ON VIEWPOINT;

1	BEGIN QUERY LOGGING with SQL, OBJECTS LIMIT THRESHOLD = 100 CPUTIME ON VIEWPOINT;

2. Change TASM Logging Interval from 600 to 60 in Workload Designer General > Other > Intervals for logging better suited to tactical work

Untill next 🙂

Thanks & Regards,

Pankaj Chahar

Pankajchahar052@gmail.com

+91-8802350184