Claims:
1. A database management system for query execution using hints, the database management system comprises:
a processor;
a memory coupled to the processor for executing a plurality of modules present in the memory, the plurality of modules comprising:
a receiving module configured to receive at least an application schema having database credentials associated with at least one database;
a collection module configured to collect information associated with at least data objects from the database;
a query parser module configured to parse the data objects collected to retrieve at least one query;
a framework configured to:
determine at least one functional and/or performance related issue for at least one hint selected for execution of the query retrieved;
identify at least one hint rule from a set of pre-defined hint rules to repair the functional and/or performance related issues determined in the hint selected for the query; thereby
recommend at least one corrective hint to execute the query based on the hint rule identified to repair the functional and/or performance related issues; and
an executor module configured to execute the query received using the corrective hint recommended.

2. The database management system as claimed in claim 1, wherein the framework is further configured to override the query for execution using at least an auto-database hint correction task based on the corrective hint recommended to execute the query.

3. The database management system as claimed in claim 1, wherein the query parser module is further configured to parse the data objects preferably retrieves the Select and DML statements from the data objects.

4. The database management system as claimed in claim 3, wherein the query parser module is further configured to:
analyze each line number and text/content in the data objects where the hints are given in a function;
utilize the each line number (where hint is used);
parse the each line till next semicolon (;) to retrieve the query.

5. The database management system as claimed in claim 1 and 4, wherein the framework is further configured to determine the functional and/or performance related issues for the hint selected for execution of the query.

6. The database management system as claimed in claim 1, wherein the set of pre-defined hint rules comprises at least a set of functional issues rules and/or at least a set of performance issue rules.

7. The database management system as claimed in claim 6, wherein the set of functional issues rules are configurable and preferably comprises rules for index ascending and descending, parallel hint DML operation and fetching data from same table in parallel, or rules for concurrent use of append hint, or any combination thereof.

8. The database management system as claimed in claim 1, wherein the set of performance issue rules are configurable and preferably comprises rules for append hint used in loop, wrong usage of leading/ordered hint, or hash and nested looping, or wrong alias, or any combination thereof.

9. A method for query execution using hints in a database management system, the method comprising:
receiving at least an application schema having database credentials associated with at least one database;
collecting information associated with at least data objects from the database;
parsing, using a framework, the data objects collected to retrieve at least one query;
determining at least one functional and/or performance related issue for at least one hint selected for execution of the query retrieved;
identifying at least one hint rule from a set of pre-defined hint rules to repair the functional and/or performance related issues determined in the hint selected for the query;
recommending at least one corrective hint to execute the query based on the hint rule identified to repair the functional and/or performance related issues;
executing the query received using the corrective hint recommended.

10. The method as claimed in claim 9, comprises overriding the query for execution using at least an auto-database hint correction task based on the corrective hint recommended.

11. The method as claimed in claim 9, wherein parsing the data objects preferably retrieves the Select and DML statements from the data objects.

12. The method as claimed in claim 9, wherein the parsing comprises:
analyzing each line number and text/content in the data objects where the hints are given in a function;
utilizing the each line number (where hint is used);
parsing the each line till next semicolon (;) to retrieve the query.

13. The method as claimed in claims 9 and 12, wherein upon retrieving the particular query, the method further comprises:
determining the functional and/or performance related issues for the hint selected for execution of the query.

14. The method as claimed in claim 9, wherein the set of pre-defined hint rules comprises at least a set of functional issues rules and/or at least a set of performance issue rules.

15. The method as claimed in claim 9, wherein the set of functional issues rules are configurable and preferably comprises rules for index ascending and descending, parallel hint DML operation and fetching data from same table in parallel, or rules for concurrent use of append hint, or any combination thereof.

16. The method as claimed in claim 9, wherein the set of performance issue rules are configurable and preferably comprises rules for append hint used in loop, wrong usage of leading/ordered hint, or hash and nested looping, or wrong alias, or any combination thereof.

17. A database management system for query execution using hints, the database management system comprises:
a processor;
a memory coupled to the processor for executing a plurality of modules present in the memory, the plurality of modules comprising:
a framework configured to:
determine at least one functional and/or performance related issue for at least one hint selected for execution of at least one query received;
identify at least one hint rule from a set of pre-defined hint rules to repair the functional and/or performance related issues determined in the hint selected for the query; thereby
recommend at least one corrective hint to execute the query based on the hint rule identified to repair the functional and/or performance related issues; and
an executor module configured to execute the query received using the corrective hint recommended.

18. The database management system as claimed in claim 17, wherein the framework is further configured to override the query for execution using at least an auto-database hint correction task based on the corrective hint recommended to execute the query.

19. The database management system as claimed in claim 17 comprises at least a report generation module configured to: generate at least one report, preferably a log, storing at least the functional and/or performance related hint issues determined.

20. The database management system as claimed in claim 19, wherein if report comprises the functional and/or performance related hint issues, the framework configured to repair the functional and/or performance related hint issues based on the log generated.

21. A method for query execution using hints in a database management system, the method comprising:
determining at least one functional and/or performance related issue for at least one hint selected for execution of at least one query received;
identifying at least one hint rule from a set of pre-defined hint rules to repair the functional and/or performance related issues determined in the hint selected for the query;
recommending at least one corrective hint to execute the query based on the hint rule identified to repair the functional and/or performance related issues;
executing the query received using the corrective hint recommended.

22. The method as claimed in claim 21, comprises overriding the query for execution using at least an auto-database hint correction task based on the corrective hint recommended to execute the query.

23. The method as claimed in claim 21, comprises generating at least one report, preferably a log, storing at least the functional and/or performance related hint issues determined.

24. The method as claimed in claim 21, wherein, if report comprises the functional and/or performance related hint issues, repair the functional and/or performance related hint issues based on the log generated.

25. An apparatus for query execution using hints as claimed in claim 1.

26. An apparatus for query execution using hints as claimed in claim 17.

27. An apparatus for query execution using hints in a database management system, configured to perform a method as claimed in claim 9.

28. An apparatus for query execution using hints in a database management system, configured to perform a method as claimed in claim 21.
, Description:TECHNICAL FIELD

The present subject matter described herein, in general, relates to database technologies or database management systems or database queries where database hints have been used wrongly, and more particularly, to systems, methods and apparatuses for binding and rebinding of query execution plans to database queries where hints have been used.

BACKGROUND

As conventionally known, a relational database management systems (DBMSs) database is a collection of information that is organized in a tabular format so that it can easily be accessed, managed, and updated in a number of different ways. Relational database management systems (DBMSs) allow a user to specify queries using high level declarative languages. These queries are specified in a declarative manner, where the query is set forth but no detailed algorithm of how to obtain the results is outlined. The way the relational databases have been used over the years with the increase in the amount of data has made performance as one of the key factors. However, if all database operations are not performed in optimized manner then it will be almost impossible to access large amount of data for applications.

When a database related application performs slowly, there is a probability that, the data access routines queries and the way the data is fetched from that application are not optimized or not written in a best possible manner. With the data increasing on a daily basis a critical challenge involves providing techniques to optimize the DBMS queries which should give a fast response. In order to achieve this all the major databases have come up with their own database optimizer.

Database optimizer is a database optimization tool that maximizes database and application performance by DBMS tuning and determining the best query execution plan for the queries. A query execution plan is an ordered set of steps used to access data in DB. A query optimizer in DBMSs translates the declarative query in a specified algorithm called a query execution plan. An efficient query execution plan is select by query optimizers to evaluate an input query. Selection of the execution plan is performed using a cost model that estimates the resources that are needed for each alternative plan in consideration. The query plan statement displays execution plans chosen by the database optimizer. The query plan result enables to determine whether the optimizer selects a particular execution plan or not. However, the query optimizer does not always produce optimal query execution plans because of many factors primarily because the query optimizer examines available access paths, as well as statistics for the objects accessed by the statement.

To aid the database engine, the DBMSs include (to varying degrees) a mechanism called query hinting. Query hinting allows the database engine to influence the choice of the best query execution plan through query hints. In general, query hints instruct the query optimizer to constrain the search space to a certain subset of execution plans. Broadly there are two ways to constrain the search space to a certain subset of execution plans which can be achieved by specifying an access path for a table (in other words, force the use of an index for a particular table) or by forcing the join order of the plan based on the order of the tables listed in a Structured Query Language (SQL) string. It may be noted that there are certain hints which don’t fall in these two ways which are not provided here to avoid complexity in understanding the details of the hints. The hints are strategies specified for enforcement by the processor on SELECT and data manipulation language (DML) statements. As an application designer a user or the administrator might know information about the data that the optimizer does not know and might be able to choose a more efficient execution plan than the optimizer. These are the situations where the hints are used to instruct the optimizer to use the optimal execution plan.

However, the SQL tuning requires a special expertise which is not available with all the application developers. Further, many times it is experienced that a wrong hint is given by the application designers/developers which can cause functional and/or performance issues. The prior-art provides various solutions to avoid the problem of wrong hint. According to one of the prior-arts, once an issue has been identified that the query plan of a particular query is not working in a proper manner and this particular query is the bottleneck, there might be many reasons why the execution plan of the query is not working according to what was supposed to work.

According to the prior-art techniques, when a query statement is being processed, the database may retrieve data from tables in a variety of ways. The database may scan through all the records in a table (FTS), scan a contiguous subset of the records in a table based on the rowid index, scan a contiguous subset of the entries in a database index, or use a combination of the above strategies in a single scan. However, there may be a possibility that the statistics of the tables and columns is not proper or an access path or a join mechanism is not proper. Further, once the cause for these possibilities is known, one of the solutions is to apply hint for that query and then check manually whether the plan is going proper or not for that query statement, as shown in figure 1.

However, a manual intervention is always required to confirm if the execution plan is going proper or not. Further, due to complexity in the databases, a same program would be checked multiple times manually for each different problems identified. Also, as manual intervention is required and if the changes made during manual intervention are done in hurry without checking the functional/performance impact, this creates a requirement of dependency on a skilled user which is not recommended.

It is a general observation that, often the application designer/database administrator lacks the skill to investigate the root cause of the query performance problems and thereby resulting in inconsistent methods of analysis and solutions to query/ DBMS tuning that are limited by the skill and experience which they posses. If tuning the queries by hints has been decided then the applied solution needs to be verified manually whether it works fine in all other environments i.e. combination of operating system and database version. It is extremely difficult to check the plan of all the problematic queries of an application which may have hundreds of queries. Thus, to determine whether each query is going for the right execution plan for the applied hint is a very tedious and error prone work. Further, there is no framework which is available in the prior-art that can find the performance and the function hint related issues from a complete database application schema. Furthermore, some of the major applications available today in the market and any of the available tools in the prior-art do not have the feature to find the performance and the function related hint issues.

The above-described deficiencies of today's query execution techniques where database hints are used are merely intended to provide an overview of some of the problems of conventional systems, and are not intended to be exhaustive. Other problems with conventional systems and corresponding benefits of the various non-limiting embodiments described herein may become further apparent upon review of the following description.

SUMMARY

This summary is provided to introduce concepts related to system, method and apparatus for identifying functional and performance related issues caused by using wrong database hints, and the same are further described below in the detailed description. This summary is not intended to identify essential features of the claimed subject matter nor is it intended for use in determining or limiting the scope of the claimed subject matter.

A main objective of the present invention is to solve the technical problem as recited above by providing system, method and apparatus for finding any functionality and performance related impacts which can be caused by using database hint in the application schema.

Accordingly, in one implementation, the present invention provides a mechanism to check all the functional and performance related issues for hints, and identifying whether applied hint are correct or not based on the hint rules pre-defined or pre-set and design the mechanism automatically, reporting the issues with the associated detailed information, and fixing the issues.

In one implementation, a database management system for query execution using hints is disclosed. The database management system comprises a processor and a memory coupled to the processor for executing a plurality of modules present in the memory. The plurality of modules comprises a framework, and an executor module. The framework is configured to determine at least one functional and/or performance related issue for at least one hint selected for execution of at least one query received; identify at least one hint rule from a set of pre-defined hint rules to repair the functional and/or performance related issues determined in the hint selected for the query; thereby recommend at least one corrective hint to execute the query based on the hint rule identified to repair the functional and/or performance related issues. The executor module configured to execute the query using the corrective hint recommended by the framework based on the issues found.

In one implementation, a database management system for query execution using hints is disclosed. The database management system comprises a processor, and a memory coupled to the processor for executing a plurality of modules present in the memory. The plurality of modules comprises a receiving module, a collection module, a query parser module, a framework, and an executor module. The receiving module is configured to receive at least an application schema having database credentials associated with at least one database. The collection module is configured to collect information associated with at least data objects from the database. The query parser module is configured to parse the data objects collected to retrieve at least one query. The framework is configured to determine at least one functional and/or performance related issue for at least one hint selected for execution of the query retrieved; identify at least one hint rule from a set of pre-defined hint rules to repair the functional and/or performance related issues determined in the hint selected for the query; thereby recommend at least one corrective hint to execute the query based on the hint rule identified to repair the functional and/or performance related issues. The executor module is configured to execute the query received using the corrective hint recommended.

In one implementation, a method for query execution using hints in a database management system is disclosed. The method comprises receiving at least an application schema having database credentials associated with at least one database; collecting information associated with at least data objects from the database; parsing the data objects collected to retrieve at least one query; determining at least one functional and/or performance related issue for at least one hint selected for execution of the query retrieved; identifying at least one hint rule from a set of pre-defined hint rules to repair the functional and/or performance related issues determined in the hint selected for the query; thereby recommending at least one corrective hint to execute the query based on the hint rule identified to repair the functional and/or performance related issues; and executing the query received using the corrective hint recommended.

All the issues found along with the recommended solution for each issue are stored in a logging table (for example, a T_HINTLOG table) which will preferably have below, but not limited to below, columns:
ID: Generated unique ID
OBJECTNAME: This column represents the name of the function/procedure.
QUERY: This column stores parsed SQL query.
WRONGHINT: This column represents wrong hint used in parsed SQL query.
FINDINGS: This column represents details of wrong hint issue.
RECOMMENDEDSOLUTION: This column represents recommended solution to change the wrong hint.

After finding all hint related issues there is provided an option to auto correct these issues with recommended solution. If user agrees with provided recommended solution then an auto database hint correction (ADHC) task will take hint issues details from log table (T_HINTLOG) and based on object name, query and recommended solution, the ADHC task will auto correct hint issues in respective queries of objects. Task will auto correct all the reported hint issues and it will recompile all the corrected objects.

In one implementation, a method for query execution using hints in a database management system is disclosed. The method comprises receiving at least one query ; determining at least one functional and/or performance related issue for at least one hint selected for execution of the query retrieved; identifying at least one hint rule from a set of pre-defined hint rules to repair the functional and/or performance related issues determined in the hint selected for the query; thereby recommending at least one corrective hint to execute the query based on the hint rule identified to repair the functional and/or performance related issues; and executing the query received using the corrective hint recommended.

In contrast to the prior-art techniques, the present invention by system, method and apparatus finds any functionality and performance related impacts/issues which can be caused by using database hint in the application schema. The present invention is performed in an application database schema, would find all the issues and report all these type of issues. There is no need to check for any query manually if the hint used has caused any functional or performance impact.

Further, in contrast to the prior-art, the present invention automatically find and verify database functional and performance hint related issues and eliminates the probability of human errors.

The various options and preferred embodiments referred to above in relation to the first implementation are also applicable in relation to the other implementations.

BRIEF DESCRIPTION OF THE ACCOMPANYING DRAWINGS

The detailed description is described with reference to the accompanying figures. In the figures, the left-most digit(s) of a reference number identifies the figure in which the reference number first appears. The same numbers are used throughout the drawings to refer like features and components.

Figure 1 illustrates a conventional technique of identifying, fixing, and reporting hint issues available in the database schemas.

Figure 2 illustrates a flowchart for all the functional and performance related issues for hints, in accordance with an embodiment of the present subject matter.

Figure 3 (a), (b), and (c) illustrates a log file generated by the framework for a sample program during identification of issues, an example of the issues available in the query plan for the selected hint for the sample program, and a corrected query plan using the present invention, in accordance with an embodiment of the present subject matter.

Figure 4 illustrates a flow chart for checking all the functional and performance related issues for hints, in accordance with an embodiment of the present subject matter.

Figure 5 illustrates a database management system for query execution using hints, in accordance with an embodiment of the present subject matter.

Figure 6 and Figure 7 illustrates a method for query execution using hints, in accordance with an embodiment of the present subject matter.

Figure 8 illustrates an example showing the difference between the execution plans after an issue is found by the framework and corrected automatically, in accordance with an embodiment of the present subject matter.

It is to be understood that the attached drawings are for purposes of illustrating the concepts of the invention and may not be to scale.

DETAILED DESCRIPTION OF THE PRESENT INVENTION

The following clearly describes the technical solutions in the embodiments of the present invention with reference to the accompanying drawings in the embodiments of the present invention. Apparently, the described embodiments are merely a part rather than all of the embodiments of the present invention. All other embodiments obtained by a person of ordinary skill in the art based on the embodiments of the present invention without creative efforts shall fall within the protection scope of the present invention.

The invention can be implemented in numerous ways, as a process, an apparatus, a system, a composition of matter, a computer readable medium such as a computer readable storage medium or a computer network wherein program instructions are sent over optical or electronic communication links. In this specification, these implementations, or any other form that the invention may take, may be referred to as techniques. In general, the order of the steps of disclosed processes may be altered within the scope of the invention.

A detailed description of one or more embodiments of the invention is provided below along with accompanying figures that illustrate the principles of the invention. The invention is described in connection with such embodiments, but the invention is not limited to any embodiment. The scope of the invention is limited only by the claims and the invention encompasses numerous alternatives, modifications and equivalents. Numerous specific details are set forth in the following description in order to provide a thorough understanding of the invention. These details are provided for the purpose of example and the invention may be practiced according to the claims without some or all of these specific details. For the purpose of clarity, technical material that is known in the technical fields related to the invention has not been described in detail so that the invention is not unnecessarily obscured.

In the following detailed description, numerous specific details are set forth in order to provide a thorough understanding of the invention. However, it will be understood by those skilled in the art that the present invention may be practiced without these specific details. In other instances, well-known methods, procedures, and components, modules, units and/or circuits have not been described in detail so as not to obscure the invention.

Although embodiments of the invention are not limited in this regard, discussions utilizing terms such as, for example, “processing,” “computing,” “calculating,” “determining,” “establishing”, “analyzing”, “checking”, or the like, may refer to operation(s) and/or process(es) of a computer, a computing platform, a computing system, or other electronic computing device, that manipulates and/or transforms data represented as physical (e.g., electronic) quantities within the computer's registers and/or memories into other data similarly represented as physical quantities within the computer's registers and/or memories or other information non-transitory storage medium that may store instructions to perform operations and/or processes.

Although embodiments of the invention are not limited in this regard, the terms “plurality” and “a plurality” as used herein may include, for example, “multiple” or “two or more”. The terms “plurality” or “a plurality” may be used throughout the specification to describe two or more components, devices, elements, units, parameters, or the like. Unless explicitly stated, the method embodiments described herein are not constrained to a particular order or sequence. Additionally, some of the described method embodiments or elements thereof can occur or be performed simultaneously, at the same point in time, or concurrently.

It is a general observation that, often the application designer/database administrator lacks the skill to investigate the root cause of the query performance problems and thereby resulting in inconsistent methods of analysis and solutions to query/ DBMS tuning that are limited by the skill and experience which they possess. Most of the times when the query execution plan are analyzed, the plan may look/work fine in one particular environment i.e., operating system and database version but when the same plan is checked in some other environment with different operating system and database version, the plan may behaves differently. Also, it is extremely difficult to check the plan of all the queries of an application which may have thousands of database programming objects, and each of these programs may have hundreds of queries. Thus, to determine whether each query is going for the right execution plan is a very tedious and error prone work. Further, there is no framework which is available in the prior-art that can find the performance and the function hint related issues from a complete database application schema. Furthermore, some of the major applications available in the market today and any of the available tools in the prior-art do not have the feature to find the performance and the function hint related issues.

In contrast to the prior-art techniques, the present invention by system, method and apparatus finds any functionality and performance related impacts/issues which can be caused by using database hint in the application schema. The present invention is performed in an application database schema, would find all the issues and report all these type of issues. There is no need to check for any query manually if the hint used has caused any functional or performance impact.

Further, in contrast to the prior-art, the present invention automatically find and verify database functional and performance hint related issues and eliminates the probability of human errors.

System, method and apparatus for identifying functional and performance related issues by using database hints are disclosed.

While aspects are described for systems, methods and apparatuses identifying functional and performance related issues by using database hints, the present invention may be implemented in any number of different computing systems, environments, and/or configurations, the embodiments are described in the context of the following exemplary systems, apparatus, and methods.

Henceforth, embodiments of the present disclosure are explained with the help of exemplary diagrams and one or more examples. However, such exemplary diagrams and examples are provided for the illustration purpose for better understanding of the present disclosure and should not be construed as limitation on scope of the present disclosure.

Referring now to figure 2, a flowchart for all the functional and performance related issues for hints is illustrated in accordance with an embodiment of the present subject matter. In one implementation, the present invention, once the database credentials of the application schema is received; the present invention would log into the database.

All the rules defined in the framework of the present invention for functionality and performance may start executing. It may collect the entire database programming objects and may parse each object to get the Select and DML statements. If there is no SQL statement present then it will continue with next database object else if there is/are any SQL query present then it will perform the pre-defined set of hints (as discussed below) to check for each SQL query.

In one implementation, the present invention parses each database programming objects by taking each line number and text where hints are given in a function and using that line number (where hint is used) parsing each line till next semicolon (;) to get the particular SQL query. After getting a particular SQL query each hint rule is applied in that SQL to find potential issue. The logic of each rule is different.

For example : Performance APPEND hint issue, may check if SQL statement is having append hint and then will parse the program using line number to check whether this query is getting used inside loop, if yes then this will be reported as an issue.

In one implementation, the hint check rules are classified in two categories, i.e. functional issues checks and performance issues checks.

In functional hint rules:
1. Index Ascending and descending issue checks: The index_asc hint instructs the optimizer to use the ascending index on a range scan operation. If the statements use an index range scan and the index is ascending, then optimizer scan the index entries in ascending order of their indexed values. If the index_asc hint for a query and the index on which the index_asc hint is used is not a primary key then the expected result may not be correct. In the same manner index_desc hint instructs the optimizer to use the descending index on a range scan operation. If the statements use an index range scan and the index is descending, then optimizer scans the index entries in descending order of their indexed values. If the index_desc hint is used for a query and the index on which the index_desc hint is used as a non primary key index then descending order of indexed value will not be proper Table Data.
2. Append hint issue check: If append hint is used in any function/procedure, then the framework may check whether that function/procedure is getting executed in multiple nodes (e.g., Real application cluster) from any DB job or scheduler. If yes then this will be reported as an issue because using append hint and running in multiple nodes at the same time will cause table locking issue. If no then this will be reported as warning because there might be chance at application level this function/procedure is getting executed in multiple threads.
3. Parallel hint DML operation along with Select and append hint issue:-
a) Parallel DML has been enabled in function code, a dml operation on a table using parallel hint is used and then same table is used to fetch the data using select statement using parallel hint then during compilation time the function code would compile but during runtime execution will get a error
b) While performing an insert into..Select operation using append hint for insert and parallel hint for select statement in a function code would compile but during runtime execution will get a error

In performance hint rules:
1. Append hint issue check: The append hint may tell the optimizer to perform a direct-path insert, which improves the performance of INSERT...SELECT operations but using append hint cause a table fragmentations if the hint is used in loop conditions because as direct-path inserts append data to the end of the table, they constantly increase the table high water mark, even if there is lots of free space within table.
2. Parallel hint issue: The PARALLEL hint instructs the optimizer to use the specified number of concurrent servers for a parallel operation. If PARALLEL hint is getting used and the degree of parallelism specified is more than half of the number of CPUs available then it will impact other operations, which can cause performance issue for On line transaction processing, or OLTP systems.
3. Hash and nested looping issues: The use_hash hint requests a hash join against the specified tables. A hash join is a technique whereby the optimizer loads the rows from the driving table (the smallest table, first after the where clause) into a RAM area and then uses a hashing technique to locate the rows in the larger second table. The use_nl hint instructs the optimizer to join each specified table to another row source with a nested loops join, using the specified table as inner table. Whenever there is a literal value given in a query and for that corresponding column if index exists and hash hint is used then it would report it as an issue. Same is the case of using nesting loop logic, if the literal value is not given and nested loop hint is used then it would report this issue.
4. Wrong alias issues: The use of table aliases means to rename a table in a particular SQL statement. The renaming is a temporary change and the actual table name may not change in the database. During the hint writing time many times it is seen that SQL statement uses an alias for the table, but instead of giving the alias in the hint the table name is given and vice versa. This actually defeats the whole purpose of writing the hint. This goes unnoticed many times until this gets deployed in the production environment causing performance issues.
5. Ordered hint issue: Ordered hint is a directive to compiler to join the table in the order provided in from clause.
Suppose in our where clause we have a very selective filter condition to choose from a table which is present in 3rd position in from clause and in hints we have used ordered, then in this case our query plan will be wrong because of ordered hint.

In order to understand the working of the present invention (framework), a sample program and the associated processing of the sample program is provided below. However, it is to be noted and understood by the ordinary person skilled in the art that the below provided sample program is only for better understanding of the present invention and shall not restrict the protection scope of the invention in any manner.

Sample procedure:
create or replace procedure p_test_proc(str_in_val IN varchar2) as
i_tmp_var number;
str_temp_var1 varchar2(100);
str_temp_var2 varchar2(100);
str_temp_var3 varchar2(100);
str_temp_var4 varchar2(100);
str_temp_var5 varchar2(100);
begin

select /*+index(b ix_table_test1)*/
a.col_2, a.col_3
into str_temp_var1, str_temp_var2
from t_table_test1 a
where a.col_1 = '1212';
select /*+use_hash(a b) full(a) full(b)*/
a.col_3, a.col_4, b.col_4
into str_temp_var1, str_temp_var2, str_temp_var3
from t_table_test1 a, t_table_test2 b
where a.col_1 = b.col_1
and a.col_2 = b.col_3
and a.col_1 = '1234';
select /*+ ordered use_nl(t a b)index(t ix_table_test3) index(a ix_table_test1) index(b ix_table_test2)*/
t.col_2, t.col_3, t.col_4, a.col_3, b.col_4
into str_temp_var1,
str_temp_var2,
str_temp_var3,
str_temp_var4,
str_temp_var5
from t_table_test1 a, t_table_test2 b, t_table_test3 t
where t.col_1 = '111'
and t.col_2 = '2'
and t.col_1 = a.col_1
and t.col_2 = a.col_2
and t.col_3 = b.col_3
and t.col_4 is not null;
execute immediate 'alter session enable parallel dml';
insert into t_table_test2
select /*+ parallel(4)*/
col_1, col_2, col_3, col_4
from t_table_test1
where rownum < 3;
select /*+parallel(4)*/
col_1
into i_tmp_var
from t_table_test2;
begin
for item in 1 .. 10 loop
insert /*+append*/
into t_table_test4
(col_1, col_2, col_3)
select /*+ parallel(a, 4)*/
col_1, col_2, col_3
from t_table_test3 a;
end loop;
end;
end p_test_proc;

Referring now to Figure 3 (a), (b), and (c) a log file generated by the framework for a sample program during identification of issues, an example of the issues identified in the query plan generated for the selected hint during execution of the sample program, a corrected query plan using the present invention, and, is illustrated in accordance with an embodiment of the present subject matter.

Referring now to figure 4, a flow chart for checking all the functional and performance related issues for hints are illustrated in accordance with an embodiment of the present subject matter. The functional and performance related issues may be any of the above issues. If any of the above mentioned checks fails then hint identification framework will log that issue along with SQL and particular object name. This process will continue for each SQL statement of each database programming object.

It is to be noted that, there may be many other rules which may have been defined / pre-defined in the framework of the present invention based on the requirement of the database or database application, but above mentioned are very few rules for illustration purpose and shall not restrict the scope of the present invention.

Referring now to figure 5, a database management system / an apparatus for execution of a query is illustrated, in accordance with an embodiment of the present subject matter. In one implementation, the database management system / an apparatus 500 are disclosed. Although the present subject matter is explained considering that the present invention is implemented in the database management system / the apparatus 500, it may be understood that the present invention may also be implemented in a variety of computing systems, such as a laptop computer, a desktop computer, a notebook, a workstation, a mainframe computer, a server, a network server, and the like. It will be understood that the database management system / the apparatus 500 may be accessed by multiple users, or applications residing on the database system. Examples of the database management system / the apparatus 500 may include, but are not limited to, a portable computer, a personal digital assistant, a handheld node, sensors, routers, gateways and a workstation. The database management system / an apparatus 500 are communicatively coupled to each other and/or other nodes or a nodes or apparatuses to form a network (not shown). Examples of the other nodes or a nodes or apparatuses may include, but are not limited to, a portable computer, a personal digital assistant, a handheld node, sensors, routers, gateways and a workstation.

In one implementation, the network (not shown) may be a wireless network, a wired network or a combination thereof. The network can be implemented as one of the different types of networks, such as GSM, CDMA, LTE, UMTS, intranet, local area network (LAN), wide area network (WAN), the internet, and the like. The network may either be a dedicated network or a shared network. The shared network represents an association of the different types of networks that use a variety of protocols, for example, Hypertext Transfer Protocol (HTTP), Transmission Control Protocol/Internet Protocol (TCP/IP), Wireless Application Protocol (WAP), and the like, to communicate with one another. Further the network may include a variety of network nodes, including routers, bridges, servers, computing nodes, storage nodes, and the like.

The database management system / the apparatus 500 may include a processor 502, an interface 504, and a memory 506. The processor 502 may be implemented as one or more microprocessors, microcomputers, microcontrollers, digital signal processors, central processing units, state machines, logic circuitries, and/or any nodes that manipulate signals based on operational instructions. Among other capabilities, the at least one processor is configured to fetch and execute computer-readable instructions or modules stored in the memory 506.

The interface (I/O interface) 504, may include a variety of software and hardware interfaces, for example, a web interface, a graphical user interface, and the like. The I/O interface may allow the database system, the first node, the second node, and the third node to interact with a user directly. Further, the I/O interface may enable the database management system / the apparatus 500 to communicate with other nodes or nodes, computing nodes, such as web servers and external data servers (not shown). The I/O interface can facilitate multiple communications within a wide variety of networks and protocol types, including wired networks, for example, GSM, CDMA, LAN, cable, etc., and wireless networks, such as WLAN, cellular, or satellite. The I/O interface may include one or more ports for connecting a number of nodes to one another or to another server. The I/O interface may provide interaction between the user and the database management system / the apparatus 500 via, a screen provided for the interface.

The memory 506 may include any computer-readable medium known in the art including, for example, volatile memory, such as static random access memory (SRAM) and dynamic random access memory (DRAM), and/or non-volatile memory, such as read only memory (ROM), erasable programmable ROM, flash memories, hard disks, optical disks, and magnetic tapes. The memory 506 may include plurality of instructions or modules or applications to perform various functionalities. The memory includes routines, programs, objects, components, data structures, etc., which perform particular tasks or implement particular abstract data types.

In one implementation, a database management system / an apparatus 500 for execution of a query using hints is disclosed. The database management system / the apparatus 500 comprise a processor 502, and a memory 506 coupled to the processor 502 for executing a plurality of modules present in the memory 506. The plurality of modules comprises a receiving module 508, a collection module 512, a query parser module 514, a framework 516, and an executor module 518. The receiving module 508 is configured to receive at least an application schema having database credentials associated with at least one database 520. The collection module 512 is configured to collect information associated with at least data objects from the database 520. The query parser module 514 is configured to parse the data objects collected to retrieve at least one query. The framework 514 is configured to determine at least one functional and/or performance related issue for at least one hint selected for execution of the query retrieved; identify at least one hint rule from a set of pre-defined hint rules to repair the functional and/or performance related issues determined in the hint selected for the query; thereby recommend at least one corrective hint to execute the query based on the hint rule identified to repair the functional and/or performance related issues. The executor module 518 configured to execute the query received using the corrective hint recommended.

In one implementation, the query parser module 514 is further configured to parse the data objects preferably retrieves the Select and DML statements from the data objects.

In one implementation, the query parser module 514 is further configured to analyze each line number and text/content in the data objects where the hints are given in a function; utilizing the each line number (where hint is used); parse the each line till next semicolon (;) to retrieve the query, and determine the functional and/or performance related issues for the hint selected for execution of the query.

In one implementation, the set of pre-defined hint rules comprises at least a set of functional issues rules and/or at least a set of performance issue rules. The set of functional issues rules are configurable and preferably comprises rules for index ascending and descending, parallel hint DML operation and fetching data from same table in parallel, or rules for concurrent use of append hint, or any combination thereof. The set of performance issue rules are configurable and preferably comprises rules for append hint used in loop, wrong usage of leading/ordered hint, or hash and nested looping, or wrong alias, or any combination thereof.

In one implementation, a database management system / an apparatus 500 for execution of a query using hints is disclosed. The database management system / the apparatus 500 comprise a processor 502, and a memory 506 coupled to the processor 502 for executing a plurality of modules present in the memory 506. The plurality of modules comprises a framework 516, and an executor module 518. The framework 516 configured to determine at least one functional and/or performance related issue for at least one hint selected for execution of at least one query received; identify at least one hint rule from a set of pre-defined hint rules to repair the functional and/or performance related issues determined in the hint selected for the query; thereby recommend at least one corrective hint to execute the query based on the hint rule identified to repair the functional and/or performance related issues. The executor module 518 configured to execute the query received using the corrective hint recommended.

In one implementation, the database management system / the apparatus 500 further comprises a report generation module 522 configured to generate at least one report, preferably a log, storing at least the functional and/or performance hint related issues determined. Further, if report comprises the functional and/or performance related issues, the framework configured to repair the functional and/or performance hint related issues based on the log generated.

Referring now to figure 6, a method for query execution using hints in a database management system is illustrated, in accordance with an embodiment of the present subject matter. The method may be described in the general context of computer executable instructions. Generally, computer executable instructions can include routines, programs, objects, components, data structures, procedures, modules, functions, etc., that perform particular functions or implement particular abstract data types. The method may also be practiced in a distributed computing environment where functions are performed by remote processing devices that are linked through a communications network. In a distributed computing environment, computer executable instructions may be located in both local and remote computer storage media, including memory storage devices.

The order in which the method is described is not intended to be construed as a limitation, and any number of the described method blocks can be combined in any order to implement the method or alternate methods. Additionally, individual blocks may be deleted from the method without departing from the protection scope of the subject matter described herein. Furthermore, the method can be implemented in any suitable hardware, software, firmware, or combination thereof. However, for ease of explanation, in the embodiments described below, the method may be considered to be implemented in the above described database management system / apparatus 500.

In one implementation, a method for query execution using hints in a database management system is disclosed.

At block 602, at least an application schema having database credentials associated with at least one database is received.

At block 604, information associated with at least data objects from the database is collected.

At block 606, the data objects collected to retrieve at least one query is parsed. During parsing the data objects preferably retrieves the Select and DML statements from the data objects. Further, during parsing, each line number and text/content in the data objects where the hints are given in a function is analyzed. The each line number (where hint is used) is utilized till next semicolon (;) to retrieve the query.

At block 608, at least one functional and/or performance related issues for at least one hint selected for execution of the query is determined.

At block 610, at least one hint rule from a set of pre-defined hint rules is identified to repair functional and/or performance related issues determined. In one implementation, the set of pre-defined hint rules comprises at least a set of functional issues rules and/or at least a set of performance issue rules. The set of functional issues rules are configurable and preferably comprises rules for index ascending and descending, parallel hint DML operation and fetching data from same table in parallel, or rules for concurrent use of append hint, or any combination thereof. The set of performance issue rules are configurable and preferably comprise rules to append the hint used in loop, wrong usage of leading/ordered hint, or hash and nested looping, or wrong alias, or any combination thereof.

At block 612, at least one corrective hint to execute the query based on the hint rule identified to repair the functional and/or performance related issues are recommended. The query plan with the hint repaired may be then overridden using the corrective hint identified

At block 614, the query received for execution is executed using the corrective hint recommended to execute the query.

Referring now to figure 7, a method for query execution using hints in a database management system is disclosed. In one implementation, a method for query execution using hints in a database management system comprises receiving at least one query for execution at step 702; determining at least one functional and/or performance related issue for at least one hint selected for execution of the query received at step 704; identifying at least one hint rule from a set of pre-defined hint rules to repair the functional and/or performance related issues determined in the hint selected for the query at step 706; recommending at least one corrective hint to execute the query based on the hint rule identified to repair the functional and/or performance related issues at step 708; and executing the query received using the corrective hint recommended at step 710.

In the available prior-art, all the hints related issues are required to be checked manually to find the issues. During, manual checking if any hint related issues are missed then only at runtime those issues can be found. Manual fixing/checking of issues depends on the competency of the person working on it. Even after finding all the issues, a need to correct all the issues is manually (manual intervention). Further, after correcting all the issues found, repeatedly a manually check is required to confirm if an new issue related to hint due to the new code modification is introduced.

In contrast to the prior-art technique, the present invention (framework) automatically finds all hints related issues based on the predefined rules. Without running/executing the codes the issues are reported based on the pre-defined set of rules. Further, the present invention provides an automatic database hint correction to auto correct hint issue with recommended solution. After correcting all the issues found, the user may have to just execute the framework task which will report all the issues related to hint due to the new code modification without any manual checking/intervention.

Figure 8 illustrates an example showing the difference between the execution plans after an issue is found by the framework and corrected automatically, in accordance with an embodiment of the present subject matter.

In contrast to the prior-art techniques, the present invention by system, method and apparatus finds any functionality and performance related impacts/issues which can be caused by using database hint in the application schema. The present invention is performed in an application database schema, would find all the issues and report all these type of issues. There is no need to check for any query manually if the hint used has caused any functional or performance impact.

Further, in contrast to the prior-art, the present invention automatically find and verify database functional and performance hint related issues and eliminates the probability of human errors.

Apart from what is discussed above, the present invention has some additional advantages and technical benefits, as provided below:
• The present invention run in the application database schema and would find all the issues and report all these type of issues. Hence, there is no need to check for any query manually if the hint used has caused any functional or performance impact.
• The present invention after fixing all the issues avoids manually checking of whether that issues are fixed properly or not.
• The present invention provides an automated mechanism for finding and verifying database functional and performance hint related issues and eliminates the probability of human errors.
• The present invention provides a mechanism for finding all functional issues which are caused because of using database hints.
• The present invention provides an automation Framework to identify all the functional and performance issues caused.
• The present invention provides a mechanism to identify performance issues which are caused because of using incorrect database hints in application systems.

A person skilled in the art may understand that any known or new algorithms by be used for the implementation of the present invention. However, it is to be noted that, the present invention provides a method to be used during back up operation to achieve the above mentioned benefits and technical advancement irrespective of using any known or new algorithms.

A person of ordinary skill in the art may be aware that in combination with the examples described in the embodiments disclosed in this specification, units and algorithm steps may be implemented by electronic hardware, or a combination of computer software and electronic hardware. Whether the functions are performed by hardware or software depends on the particular applications and design constraint conditions of the technical solution. A person skilled in the art may use different methods to implement the described functions for each particular application, but it should not be considered that the implementation goes beyond the scope of the present invention.

It may be clearly understood by a person skilled in the art that for the purpose of convenient and brief description, for a detailed working process of the foregoing system, apparatus, and unit, reference may be made to a corresponding process in the foregoing method embodiments, and details are not described herein again.

In the several embodiments provided in the present application, it should be understood that the disclosed system, apparatus, and method may be implemented in other manners. For example, the described apparatus embodiment is merely exemplary. For example, the unit division is merely logical function division and may be other division in actual implementation. For example, a plurality of units or components may be combined or integrated into another system, or some features may be ignored or not performed. In addition, the displayed or discussed mutual couplings or direct couplings or communication connections may be implemented through some interfaces. The indirect couplings or communication connections between the apparatuses or units may be implemented in electronic, mechanical, or other forms.

When the functions are implemented in a form of a software functional unit and sold or used as an independent product, the functions may be stored in a computer-readable storage medium. Based on such an understanding, the technical solutions of the present invention essentially, or the part contributing to the prior art, or a part of the technical solutions may be implemented in a form of a software product. The computer software product is stored in a storage medium, and includes several instructions for instructing a computer node (which may be a personal computer, a server, or a network node) to perform all or a part of the steps of the methods described in the embodiment of the present invention. The foregoing storage medium includes: any medium that can store program code, such as a USB flash drive, a removable hard disk, a read-only memory (Read-Only Memory, ROM), a random access memory (Random Access Memory, RAM), a magnetic disk, or an optical disc.

Devices that are in communication with each other need not be in continuous communication with each other, unless expressly specified otherwise. In addition, devices that are in communication with each other may communicate directly or indirectly through one or more intermediaries.

When a single device or article is described herein, it will be readily apparent that more than one device/article (whether or not they cooperate) may be used in place of a single device/article. Similarly, where more than one device or article is described herein (whether or not they cooperate), it will be readily apparent that a single device/article may be used in place of the more than one device or article or a different number of devices/articles may be used instead of the shown number of devices or programs. The functionality and/or the features of a device may be alternatively embodied by one or more other devices which are not explicitly described as having such functionality/features. Thus, other embodiments of the invention need not include the device itself.

Finally, the language used in the specification has been principally selected for readability and instructional purposes, and it may not have been selected to delineate or circumscribe the inventive subject matter. It is therefore intended that the scope of the invention be limited not by this detailed description, but rather by any claims that issue on an application based here on. Accordingly, the disclosure of the embodiments of the invention is intended to be illustrative, but not limiting, of the scope of the invention, which is set forth in the following claims.

With respect to the use of substantially any plural and/or singular terms herein, those having skill in the art can translate from the plural to the singular and/or from the singular to the plural as is appropriate to the context and/or application. The various singular/plural permutations may be expressly set forth herein for sake of clarity.

Although implementations for system, method and apparatus for identifying functional and performance related issues caused by using wrong database hints have been described in language specific to structural features and/or methods, it is to be understood that the appended claims are not necessarily limited to the specific features or methods described. Rather, the specific features and methods are disclosed as examples of implementations of the system, method and apparatus for identifying functional and performance related issues by using database hints.