Techniques and mechanisms for managing and analyzing database query activities

• US 9,898,383 B2
• Filed: 10/09/2014
• Issued: 02/20/2018
• Est. Priority Date: 10/09/2013
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Ramaswami, Satish, Srinivasan, Prashant
• Primary Examiner(s): Hershley, Mark E
• Application Number:
  US14/511,097
• Publication Number:
  US 20150100566A1
• Time in Patent Office: 1,230 Days
• Field of Search: 707718
• CPC Class Codes: G06F 11/3409 for performance assessment
• CPC Class Codes: G06F 11/3452 Performance evaluation by statistical analysis
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 2201/80 Database-specific techniques

Abstract

Techniques and mechanisms for analyzing a plurality of database queries within a database environment. A first statistical evaluation of a first query plan is determined for a first database query with at least one computing device within the database environment. A second statistical evaluation for a second query plan is determined for a second database query. The first statistical evaluation and the second statistical evaluation are compared to determine whether the first query plan matches the second query plan with at least one computing device within the database environment. An indication is stored in a repository of the database environment that the first query matches the second query if the first hash value matches the second hash value. A function that provides the first query plan is determined. If the second query plan is provided by the function that provides the first query plan, data objects referenced by the first query plan and the second query plan if the function provides both the first query plan and the second query plan to determine if the first query plan and the second query plan are syntactically different versions of equivalent database queries. Query statistics are stored in the statistical repository if the first query plan and the second query plan are syntactically different versions of equivalent database queries.

Claims

1. A method of analyzing a plurality of database queries within a database environment, the method comprising:

determining a first statistical evaluation of a first query plan for a first database query corresponding to a first database having a first database schema with at least one computing device within the database environment;

determining a second statistical evaluation for a second query plan for a second database query corresponding to a second database having a second database schema with at least one computing device within the database environment;

comparing the first statistical evaluation and the second statistical evaluation to determine whether the first query plan matches the second query plan with at least one computing device within the database environment;

storing an indication in a repository of the database environment that the first query matches the second query if the first statistical evaluation matches the second statistical evaluation;

determining a function that provides the first query plan with at least one computing device within the database environment;

determining if the second query plan is provided by the function that provides the first query plan with at least one computing device within the database environment;

evaluating data objects referenced by the first query plan and the second query plan if the function provides both the first query plan and the second query plan with at least one computing device within the database environment to determine if the first query plan and the second query plan are syntactically different versions of equivalent database queries; and

storing query statistics in the statistical repository of the database environment if the first query plan and the second query plan are syntactically different versions of equivalent database queries.

2. The method of claim 1 further comprising:

determining a procedure call that provides the first query plan;

determining if the second query plan is provided by the procedure call that provides the first query plan;

evaluating data objects referenced by the first query plan and the second query plan if the procedure call provides both the first query plan and the second query plan to determine if the first query plan and the second query plan are syntactically different versions of equivalent database queries.

3. The method of claim 1 wherein the database environment comprises a multitenant database environment to store data for multiple client entities each identified by a tenant identifier (ID) having one of one or more users associated with the tenant ID, wherein users of each of multiple client entities can only access data identified by a tenant ID associated with the respective client entity, and wherein the multitenant database is a hosted database provided by an entity separate from the client entities, and provides on-demand database service to the client entities.

4. The method of claim 1 wherein the first query plan is directed to a first database and the second query plan is directed to a second database.

5. The method of claim 1 wherein the first query plan is directed to a first software version of a database and the second query plan is directed to a second software version of the database.

6. The method of claim 1 wherein the first query plan is directed to a first database having a first schema and the second query plan is directed to a second database having a second schema.

7. The method of claim 1 wherein the first query plan is directed to a first database having a first set of object types and the second query plan is directed to a second database having a second set of object types.

8. A non-transitory computer-readable medium having stored thereon instructions that, when executed by one or more processors, cause the one or more processors to analyze a plurality of database queries within a database environment by:

determining a first statistical evaluation of a first query plan for a first database query corresponding to a first database having a first database schema with at least one computing device within the database environment;

determining a second statistical evaluation for a second query plan for a second database query corresponding to a second database having a second database schema with at least one computing device within the database environment;

comparing the first statistical evaluation and the second statistical evaluation to determine whether the first query plan matches the second query plan with at least one computing device within the database environment;

storing an indication in a repository of the database environment that the first query matches the second query if the first statistical evaluation matches the second statistical evaluation;

determining a function that provides the first query plan with at least one computing device within the database environment;

determining if the second query plan is provided by the function that provides the first query plan with at least one computing device within the database environment;

evaluating data objects referenced by the first query plan and the second query plan if the function provides both the first query plan and the second query plan with at least one computing device within the database environment to determine if the first query plan and the second query plan are syntactically different versions of equivalent database queries; and

storing query statistics in the statistical repository of the database environment if the first query plan and the second query plan are syntactically different versions of equivalent database queries.

9. The non-transitory computer-readable medium of claim 8 further comprising instructions that, when executed by the one or more processors, cause the one or more processors to:

determine a procedure call that provides the first query plan;

determine if the second query plan is provided by the procedure call that provides the first query plan;

evaluate data objects referenced by the first query plan and the second query plan if the procedure call provides both the first query plan and the second query plan to determine if the first query plan and the second query plan are syntactically different versions of equivalent database queries.

10. The non-transitory computer-readable medium of claim 8 wherein the database environment comprises a multitenant database environment to store data for multiple client entities each identified by a tenant identifier (ID) having one of one or more users associated with the tenant ID, wherein users of each of multiple client entities can only access data identified by a tenant ID associated with the respective client entity, and wherein the multitenant database is a hosted database provided by an entity separate from the client entities, and provides on-demand database service to the client entities.

11. The non-transitory computer-readable medium of claim 8 wherein the first query plan is directed to a first database and the second query plan is directed to a second database.

12. The non-transitory computer-readable medium of claim 8 wherein the first query plan is directed to a first software version of a database and the second query plan is directed to a second software version of the database.

13. The non-transitory computer-readable medium of claim 8 wherein the first query plan is directed to a first database having a first schema and the second query plan is directed to a second database having a second schema.

14. The non-transitory computer-readable medium of claim 8 wherein the first query plan is directed to a first database having a first set of object types and the second query plan is directed to a second database having a second set of object types.

15. A database system capable of analyzing a plurality of database queries within a database environment, the database system comprising:

one or more databases;

an intelligent query analysis agent coupled to receive a first statistical evaluation of a first query plan for a first database query corresponding to a first database having a first database schema and a second statistical evaluation for a second query plan for a second database query corresponding to a second database having a second database schema, to compare the first statistical evaluation and the second statistical evaluation to determine whether the first query plan matches the second query plan, to cause to be stored in the one or more databases an indication that the first query matches the second query if the first statistical evaluation matches the second statistical evaluation, to determine a function that provides the first query plan, to determine if the second query plan is provided by the function that provides the first query plan, to evaluate data objects referenced by the first query plan and the second query plan if the function provides both the first query plan and the second query plan to determine if the first query plan and the second query plan are syntactically different versions of equivalent database queries, and to cause to be stored in the database statistics if the first query plan and the second query plan are syntactically different versions of equivalent database queries.

16. The system of claim 15, wherein the intelligent query analysis agent is further configured to determine a procedure call that provides the first query plan, to determine if the second query plan is provided by the procedure call that provides the first query plan, to evaluate data objects referenced by the first query plan and the second query plan if the procedure call provides both the first query plan and the second query plan to determine if the first query plan and the second query plan are syntactically different versions of equivalent database queries.

17. The system of claim 15 wherein the database environment comprises a multitenant database environment to store data for multiple client entities each identified by a tenant identifier (ID) having one of one or more users associated with the tenant ID, wherein users of each of multiple client entities can only access data identified by a tenant ID associated with the respective client entity, and wherein the multitenant database is a hosted database provided by an entity separate from the client entities, and provides on-demand database service to the client entities.

18. The system of claim 15 wherein the first query plan is directed to a first database and the second query plan is directed to a second database.

19. The system of claim 15 wherein the first query plan is directed to a first software version of a database and the second query plan is directed to a second software version of the database.

20. The system of claim 15 wherein the first query plan is directed to a first database having a first schema and the second query plan is directed to a second database having a second schema.

21. The method of claim 15 wherein the first query plan is directed to a first database having a first set of object types and the second query plan is directed to a second database having a second set of object types.