Database query profiler

• US 8,122,066 B2
• Filed: 10/14/2008
• Issued: 02/21/2012
• Est. Priority Date: 10/14/2008
• Status: Active Grant

• Current Assignee: Hewlett Packard Enterprise Development LP (Hewlett-Packard Enterprise Company)
• Original Assignee: Hewlett-Packard Development Company, L.P. (HP Inc.)
• Inventors: Castellanos, Maria G., Ramos, Ivo Jose Jimenez
• Primary Examiner(s): Vy, Hung T
• Assistant Examiner(s): Cao, Phuong Thao
• Application Number:
  US12/251,320
• Publication Number:
  US 20100094829A1
• Time in Patent Office: 1,225 Days
• Field of Search: 707/803, 707/713, 707/999.004
• US Class Current: 707/803
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409

Abstract

A query profiling system and associated operating methods implement a database query profiler. The illustrative database system comprises a parser, a query model, a catalog manager, a binder and view rewriter, and a query analyzer. The parser parses statements by checking syntax of a statement according to a structured query language (SQL) grammar and representing the statements in a syntax-oriented internal format. The catalog manager processes data definition SQL statements (DDL) that are transformed to the internal representation; transform the representations of the DDL to database objects abstractions; groups the database objects into a Catalog. The binder and view rewriter take the internal representations obtained by parsing workload queries (data manipulation SQL statements or DML) and rewrite table references by their corresponding View definitions; bind database object references to their actual database objects contained in the catalog. The query analyzer analyzes the queries for relevant attributes and stores the attributes in a profile repository.

Claims

1. A controller-executed method for operating a query profiling system comprising:

profiling database queries in a workload comprising;

receiving a query set and a data definition language (DDL) model;

extracting metadata of the DDL model;

converting data definition elements of the metadata into a catalog of database objects;

parsing queries corresponding to a workload to create a set of instances of query representations;

binding table and column references in a query to a real table, view, or column object contained in the catalog;

analyzing the queries for relevant attributes comprising complexity and number of predicates; and

storing the attributes in a repository.

2. The method according to claim 1 further comprising:

creating the catalog comprising;

acquiring metadata sources that define a workload data schema, the metadata sources including at least one metadata source in a group consisting of user-provided plain text files containing CREATE statements that define tables for a workload, user-provided extensible markup language (XML) files containing CREATE statements embedded in XML tags, and system-loaded metadata exported to an XML file with embedded SQL statements from a data definition language (DDL) model;

reading structured query language (SQL) statements contained in the metadata sources;

parsing the SQL statements;

converting the parsed SQL statements to internal representations;

transforming the internal representations to database objects; and

grouping the database objects in the catalog.

3. The method according to claim 1 further comprising:

parsing queries comprising;

determining whether a query is correctly specified according to a structured query language (SQL) grammar; and

converting the query into an internal format.

4. The method according to claim 1 further comprising:

parsing queries comprising;

checking syntax of a query according to a structured query language (SQL) grammar;

representing the query in a syntax-oriented internal format; and

expressing an internal model in abstractions comprising column references, a structured query language (SQL) statements, expressions, table references, Boolean expressions, and predicates.

5. The method according to claim 1 further comprising:

binding table and column references comprising;

cannonicalizing table references into a catalog.schema.tablename format;

checking a table reference for registration in the catalog;

determining whether the table reference is defined as a view;

substituting the table reference for a table reference defined as a view by using a corresponding select statement;

checking that attribute references are correct using the catalog;

determining whether column references point to a base table column or a derived table column;

binding a column reference directly to the base table column if the column reference points to the base table column; and

following a path of the derived table column to a base table where the column reference originates and substituting all column references contained in predicates if the column reference points to the derived table column.

6. The method according to claim 1 further comprising:

analyzing the queries comprising;

scanning statement representations; and

extracting distinct attributes based on type of data manipulation language (DML).

7. The method according to claim 1 further comprising:

extracting attributes for SELECT statements comprising columns contained in a list of columns contained in a select list, tables contained in a from list, restriction predicates, join predicates, group by columns, order by columns, and subqueries; and

extracting attributes for DELETE, INSERT, and UPDATE statements comprising restriction predicates and subqueries.

8. The method according to claim 1 further comprising:

operating a graphical user interface comprising;

presenting a display screen for setting location of the repository;

receiving a repository location specification from a user;

presenting a display screen for defining a query workload;

receiving a query workload definition from the user;

presenting a display screen for assigning a name to the query workload;

receiving a query workload name from the user;

presenting a display screen for selecting a schema source;

receiving a schema source selection from the user; and

presenting a display screen of a query profile comprising a workload-wide view of a query set based on profiling database queries in the workload.

9. A query profiling system comprising a controller programmed to implement:

a parser that parses by checking syntax of queries according to a structured query language (SQL) grammar and representing the queries in a syntax-oriented internal format;

then compares the queries against metadata extracted from a data definition language (DDL) model in a catalog of database objects;

a binder and view rewriter that rewrites table references with their corresponding view definitions and binds references in a query to objects contained in the catalog;

a query analyzer that analyzes the queries for relevant attributes and stores the attributes in a profile repository; and

a catalog manager that creates the catalog by acquiring metadata sources that define a workload data schema, reading structured query language (SQL) statements contained in the metadata sources; and

wherein said parser parses the SQL statements, converts the parsed SQL statements to query representations, and transforms the query representations to database objects, the catalog manager grouping the database objects in the catalog.

10. A query profiling system comprising a controller programmed to implement:

a parser that parses by checking syntax of queries according to a structured query language (SQL) grammar and representing the queries in a syntax-oriented internal format;

then compares the queries against metadata extracted from a data definition language (DDL) model in a catalog of database objects;

a binder and view rewriter that rewrites table references with their corresponding view definitions and binds references in a query to objects contained in the catalog; and

a query analyzer that analyzes the queries for relevant attributes and stores the attributes in a profile repository;

the binder and view rewriter configured to bind table and column references by cannonicalizing table references into a catalog.schema.tablename format, checking a table reference for registration in the catalog, determining whether the table reference is defined as a view, substituting the table reference by a corresponding select statement for a table reference defined as a view, checking that attribute references are correct using the catalog, determining whether column references point to a base table column or a derived table column, binding a column reference directly to the base table column if the column reference points to the base table column, and following a path of the derived table column to a base table where the column reference originates and substituting all column references contained in predicates if the column reference points to the derived table column.

11. A query profiling system comprising a controller programmed to implement:

a parser that parses by checking syntax of queries according to a structured query language (SQL) grammar and representing the queries in a syntax-oriented internal format;

then compares the queries against metadata extracted from a data definition language (DDL) model in a catalog of database objects;

a binder and view rewriter that rewrites table references with their corresponding view definitions and binds references in a query to objects contained in the catalog; and

a query analyzer that analyzes the queries for relevant attributes and stores the attributes in a profile repository;

the query analyzer configured to analyze the queries by scanning query representations, and extracting distinct attributes based on type of data manipulation language (DML) wherein the query analyzer extracts attributes for SELECT statements comprising columns contained in a list of columns contained in a select list, tables contained in a from list, restriction predicates, join predicates, group by columns, order by columns, and subqueries; and

extracts attributes for DELETE, INSERT, and UPDATE statements comprising restriction predicates and subqueries.

12. A query profiling system comprising a controller programmed to implement:

a parser that parses by checking syntax of queries according to a structured query language (SQL) grammar and representing the queries in a syntax-oriented internal format;

then compares the queries against metadata extracted from a data definition language (DDL) model in a catalog of database objects;

a binder and view rewriter that rewrites table references with their corresponding view definitions and binds references in a query to objects contained in the catalog;

a query analyzer that analyzes the queries for relevant attributes and stores the attributes in a profile repository; and

a graphical user interface presents a display screen for setting location of the profile repository, receives a repository location specification from a user, presents a display screen for defining a query workload, receives a query workload definition from the user, presents a display screen for assigning a name to the query workload, receives a query workload name from the user, presents a display screen for selecting a schema source, receives a schema source selection from the user, and presents a display screen of a query profile comprising a workload-wide view of a query set based on profiling of database queries in the workload.

13. A query profiling system comprising a controller programmed to implement:

a parser that parses by checking syntax of queries according to a structured query language (SQL) grammar and representing the queries in a syntax-oriented internal format;

then compares the queries against metadata extracted from a data definition language (DDL) model in a catalog of database objects;

a binder and view rewriter that rewrites table references with their corresponding view definitions and binds references in a query to objects contained in the catalog;

a query analyzer that analyzes the queries for relevant attributes and stores the attributes in a profile repository; and

an article of manufacture comprising;

a controller-usable medium having a computer readable program code embodied in a controller for profiling database queries in a workload further comprising;

code causing the controller to receive a query set and a data definition language (DDL) model;

code causing the controller to extract metadata of the DDL model;

code causing the controller to convert data definition elements of the metadata into a catalog of database objects;

code causing the controller to parse queries corresponding to a workload to create a set of instances of query representations;

code causing the controller to bind table and column references in a query to a real table, view, or column object contained in the catalog;

code causing the controller to analyze the queries for relevant attributes; and

code causing the controller to store the attributes in a repository.