Method and apparatus for enhancing performance of database and environment thereof

• US 8,239,369 B2
• Filed: 03/20/2008
• Issued: 08/07/2012
• Est. Priority Date: 03/20/2008
• Status: Expired due to Fees

• Current Assignee: DBSophic Ltd. (FutureIT, Inc.)
• Original Assignee: DBSophic Ltd. (FutureIT, Inc.)
• Inventors: Levin, Amihai
• Primary Examiner(s): NGUYEN, KIM T
• Application Number:
  US12/051,900
• Publication Number:
  US 20090240711A1
• Time in Patent Office: 1,601 Days
• Field of Search: 707/713, 707/715
• US Class Current: 707/713
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures

Abstract

A method and apparatus for enhancing the performance of an environment comprising a database system. The method and apparatus employ collection stage for collecting data related to substantially all aspects of the system, including hardware, operating system, database, database schema, content and activity. Multiple issues relating to multiple categories are detected within the collected information. Then the detected issues are analyzed by correlating and prioritizing them. The issues that receive the highest priority are preferably those whose correction will yield the most significant improvement on the overall performance of the system. One or more recommendations are then issued for correcting the root issues hindering performance. Preferably, for one or more recommendations, scripts are generated which are then executed manually or automatically.

Claims

1. In a computerized environment comprising at least one computing platform executing a database engine, a method for enhancing the performance of the database engine or the computerized environment, the method comprising:

receiving data related to at least two aspects of the environment;

detecting from the data at least two detection issues associated with at least two detection categories of the system, wherein one of the at least two detection categories is a query code category, the query code category defines issues related to coding of query statements executed by the environment;

correlating the detection issues to determine at least one analysis issue;

analyzing the at least one analysis issue related to at least one analysis category, wherein the at least one analysis category is selected from analysis categories influenced by issues detected in the coding of the query statements; and

issuing at least one recommendation based, in part, on the analysis of the at least analysis issue and the coding of the query statements.

2. The method of claim 1 wherein the at least one recommendation includes at least code re-writes of the query statements executed by the environment.

3. The method of claim 2 further comprising:

generating a script for implementing at least one recommendation; and

executing the script implementing the at least one recommendation.

4. The method of claim 1 further comprising collecting the data related to at least two aspects of the environment.

5. The method of claim 1 wherein the data relates to any at least two aspects selected from the group consisting of:

hardware;

operating system;

database engine;

database meta data;

data stored in the database;

activity;

performance metrics; and

historical trends.

6. The method of claim 5 wherein the database meta data comprises at least one item from the group consisting of:

database schema;

database objects; and

database code.

7. The method of claim 1 wherein at least one another detection category of at least two detection categories are selected from the group consisting of:

database schema;

index schema;

programming objects code;

programming objects usage;

database configuration;

instance configurations;

server configuration;

hardware;

security; and

activity.

8. The method of claim 7 wherein the activity detection category relates to at least one activity from the group consisting of:

retrieving data from a database;

adding data to a database;

updating data within a database; and

deleting data from a database.

9. The method of claim 1 wherein the at least one analysis category is selected from the group consisting of:

hardware, operating system configuration;

instance configuration;

database configuration;

application configuration;

database schema;

index schema;

server side object;

application query coding;

security; and

maintenance.

10. The method of claim 1 further comprising reporting to a user the information related to the detection or to the analysis.

11. The method of claim 1, wherein detecting coding issues of the query statements comprises detecting at least one of non-optimal coded query statements and improper usage of literals in query statements.

12. In a computerized environment comprising at least one computing platform executing a database engine executed by a first computing platform, an apparatus for enhancing the performance of the database engine or the computerized environment, the apparatus comprising:

detection components, the detection components comprising at least one specific detection component for assessing the existence of at least two detection issues associated with at least two detection categories within the environment, according to a detection rule, wherein one of the at least two detection categories is a query code category, the query code category defines issues related to coding of query statements executed by the environment;

analysis components, the analysis components comprising;

at least one specific analysis component for correlating the detection issues to determine at least one analysis issue and analyzing the at least one analysis issue related to at least one analysis category from the at least two detection issues by applying at least one analysis rule, wherein the at least one analysis category is selected from analysis categories influenced by issues detected in the coding of the query statements;

a performance enhancement estimation component for evaluating the performance enhancement of resolving the situation associated with the at least one analysis rule; and

at least one recommendation component for issuing at least one recommendation to resolve the situation based, in part, on the analysis of the at least analysis issue and the coding of the query statements, wherein the at least one specific detection component or the at least one at least one specific analysis component or the performance enhancement estimation component is adapted to be executed by the first computing platform or by a second computing platform, each of the first computing platform and the second computing platform includes a processor coupled to a memory.

13. The apparatus of claim 12 wherein at least one specific detection component comprises at least one component from the group consisting of:

an activity detection component;

an indexing detection component;

a query detection component;

a schema detection component;

a data detection component; and

a programming object detection component.

14. The apparatus of claim 12 wherein the detection components further comprise a rule engine for activating the at least one specific detection component.

15. The apparatus of claim 12 wherein the at least one specific analysis component comprises at least one component from the group consisting of:

database schema analysis component;

indexing schema analysis component;

server side code analysis component;

application code analysis component;

server configuration analysis component;

instance configuration analysis component;

database configuration analysis component;

application configuration analysis component;

hardware analysis component;

security analysis component; and

maintenance analysis component.

16. The apparatus of claim 12 wherein the analysis components further comprise a prioritization estimation component for prioritizing the at least one analysis rule.

17. The apparatus of claim 12 wherein the at least one recommendation includes at least code re-writes of the query statements executed by the environment.

18. The apparatus of claim 17 wherein the recommendation components further comprise a script generation component for generating a script for implementing the at least one recommendation;

and a script execution component for executing the script for implementing the at least one recommendation.

19. The apparatus of claim 12 further comprising collection components for collecting the data from the environment.

20. The apparatus of claim 19 wherein the collection components comprise at least one component from the group consisting of:

database query component;

operating system component;

hardware component;

memory structures component;

registry access component;

trace collection component;

log collection component; and

data aggregator component.

21. The apparatus of claim 12 further comprising a reporting component for reporting to a user at least one result of the detection components or the analysis components.

22. The apparatus of claim 12 wherein the computerized environment comprises at least one client computing platform.

23. The apparatus of claim 12, wherein the detection component further detects coding issues including at least one of non-optimal coded query statements and improper usage of literals in query statements.

24. A computer readable storage medium containing a set of instructions for a general purpose computer, the set of instructions comprising:

receiving data related to at least two aspects of a computerized environment comprising at least one computing platform executing a database engine;

detecting from the data at least two detection issues associated with at least two detection categories of the system, wherein one of the at least two detection categories is a query code category, the query code category defines issues related to coding of query statements executed by the environment;

correlating the detection issues to determine at least one analysis issue;

analyzing the at least one analysis issue related to at least one analysis category, wherein the at least one analysis category is selected from analysis categories influenced by issues detected in the coding of the query statements; and

issuing at least one recommendation based, in part, on the analysis of the at least analysis issue and the coding of the query statements.