dba_tableUsageImpact
Analyze table and view usage by specified users to identify resource-intensive operations. Use database and user inputs to assess and optimize Teradata database performance.
Instructions
Measure the usage of a table and views by users, this is helpful to understand what user and tables are driving most resource usage at any point in time.
Arguments: database_name - database name to analyze user_name - user name to analyze
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| database_name | No | ||
| user_name | No |
Implementation Reference
- The handler function that implements the core logic for the dba_tableUsageImpact tool. It constructs and executes a SQL query on DBC.DBQLObjTbl and DBC.DBQLogTbl to measure table and view usage by users, calculating query counts, percentages, usage frequency categories (High/Medium/Low), and days since first/last query.def handle_dba_tableUsageImpact(conn: TeradataConnection, database_name: str | None = None, user_name: str | None = None, *args, **kwargs): """ Measure the usage of a table and views by users, this is helpful to understand what user and tables are driving most resource usage at any point in time. Arguments: database_name - database name to analyze user_name - user name to analyze """ logger.debug(f"Tool: handle_dba_tableUsageImpact: Args: database_name: {database_name}, user_name: {user_name}") database_name_filter = f"AND objectdatabasename = '{database_name}'" if database_name else "" user_name_filter = f"AND username = '{user_name}'" if user_name else "" table_usage_sql=""" LOCKING ROW for ACCESS sel DatabaseName ,TableName ,UserName ,Weight as "QueryCount" ,100*"Weight" / sum("Weight") over(partition by 1) PercentTotal ,case when PercentTotal >=10 then 'High' when PercentTotal >=5 then 'Medium' else 'Low' end (char(6)) usage_freq ,FirstQueryDaysAgo ,LastQueryDaysAgo from ( SELECT TRIM(QTU1.TableName) AS "TableName" , TRIM(QTU1.DatabaseName) AS "DatabaseName" ,UserName as "UserName" ,max((current_timestamp - CollectTimeStamp) day(4)) as "FirstQueryDaysAgo" ,min((current_timestamp - CollectTimeStamp) day(4)) as "LastQueryDaysAgo" , COUNT(DISTINCT QTU1.QueryID) as "Weight" FROM ( SELECT objectdatabasename AS DatabaseName , ObjectTableName AS TableName , ob.QueryId FROM DBC.DBQLObjTbl ob /* uncomment for DBC */ WHERE Objecttype in ('Tab', 'Viw') {database_name_filter} AND ObjectTableName IS NOT NULL AND ObjectColumnName IS NULL -- AND LogDate BETWEEN '2017-01-01' AND '2017-08-01' /* uncomment for PDCR */ -- AND LogDate BETWEEN current_date - 90 AND current_date - 1 /* uncomment for PDCR */ GROUP BY 1,2,3 ) AS QTU1 INNER JOIN DBC.DBQLogTbl QU /* uncomment for DBC */ ON QTU1.QueryID=QU.QueryID AND (QU.AMPCPUTime + QU.ParserCPUTime) > 0 {user_name_filter} GROUP BY 1,2, 3 ) a order by PercentTotal desc qualify PercentTotal>0 ; """ logger.debug(f"Tool: handle_dba_tableUsageImpact: table_usage_sql: {table_usage_sql}") with conn.cursor() as cur: logger.debug("Database version information requested.") rows = cur.execute(table_usage_sql.format(database_name_filter=database_name_filter, user_name_filter=user_name_filter)) data = rows_to_json(cur.description, rows.fetchall()) if len(data): info=f'This data contains the list of tables most frequently queried objects in database schema {database_name}' else: info=f'No tables have recently been queried in the database schema {database_name}.' metadata = { "tool_name": "handle_dba_tableUsageImpact", "database": database_name, "table_count": len(data), "comment": info, "rows": len(data) } logger.debug(f"Tool: handle_dba_tableUsageImpact: metadata: {metadata}") return create_response(data, metadata)