WO2007115847A1

WO2007115847A1 - Automatic optimisation of request processing in databank systems

Info

Publication number: WO2007115847A1
Application number: PCT/EP2007/051296
Authority: WO
Inventors: Uwe KRÜNER-MARQUIS; Mojgan Kowsari
Original assignee: Lrs Lufthansa Revenue Services Gmbh
Priority date: 2006-04-10
Filing date: 2007-02-09
Publication date: 2007-10-18
Also published as: DE102006017076A1; DE102006017076B4

Abstract

The invention relates to a system for automatic optimisation of request processing in data bank or data bank management systems (1), comprising at least one relational databank (2), at least one server (3) and at least one client (4), in communication connection to the server for data access requests, said databank management system (1), comprising an optimiser (5), controlling the running of data access requests and using hints from client (4) or server (3) applications (6) for external control of the optimiser (5). The invention further relates to a method for optimisation of databank requests in such a databank management system. According to the invention, a system and method may be achieved, permitting an essentially more rapid and simpler matching of the optimisation of databank access requests or databank queries, by means of system provision of memory space to at least one application (6) for a hint table (7) and that in place of a hint in the application or in a component of a data access request, a hint call is provided, by means of which a hint is called from the hint table (7), stored in the memory for external control of the optimiser (5). For teh method, provisionis made for integration in the programme code of an application of at least one hint call, by means of which a hint is called from a hint table, provided for the application and stored outside the programme code and processed by the application on a databank query for optimisation of the databank query.

Description

Automatic optimization of query processing in database systems

The present invention relates to a system and method for optimizing query processing in database management systems having at least one relational database, at least one server, and at least one client in communication with the server for data access requests, wherein the database management system includes an optimizer controls data access requests and uses the hints from client or server applications for external control of the optimizer.

Corresponding optimization systems and corresponding methods for the execution of data access requests or database queries are known from the prior art, in particular for the administration and access of extensive databases. One such system is, for example, the Oracle database system, which supports a standard query language (SQL) and uses an optimizer that controls the processing of database access requests or database queries, and the optimizer also supports hints. Hints are instructions to the optimizer, regardless of one of the optimizer otherwise provided expiration of a database query at least in part to make a certain predetermined by the hint flow control. For example, a database query might require a more or less complex combination of data from different tables, with the speed and efficiency of the resulting query results depending on the order in which corresponding tables or their data are combined or compared. The optimizer uses general criteria to control the flow, such as the relative size of the tables to make a particular order of the combinations. In contrast, the user or programmer of a particular application may be aware that a query criterion is highly selective, especially with respect to a particular table, but that would have a subordinate ranking following the schema used by the optimizer. A hint can tell the optimizer to start processing the query with the table for which a query criterion is highly selective, which can significantly speed up the processing of the entire data query. Such hints are therefore inserted by a programmer or a suitably experienced user from his or her particular knowledge of an application and the tables which the particular application can access in the case of database queries, into the program code of an application or into corresponding database query blocks of an application. However, since both the actual applications and the files or tables containing the data searched for can change significantly in terms of size and content, at least from time to time a corresponding adjustment of such hints is required. Likewise, new database queries may require the development and deployment of new hints due to changing or new applications or new service offerings. However, this requires interruptions of the execution of the application, as is intervened in a change of Hints directly into the program code.

Moreover, in the case of a variety of different applications, this must be repeated regularly and independently for all applications, with some of the hints used in different applications being identical.

The proper maintenance of applications, including the hints embedded therein, is relatively time-consuming and costly, especially when an application program is very large in size and at the same time used by a large number of users whose work must be interrupted for proper maintenance of the application program , Alternatively, such care must be done outside normal operating hours. However, there are applications that run in 24-hour operation, and in particular, applications that run in globally networked systems, so that an interruption of the operation, and only to reinstall or supplement the Hints by patches or the like, In any case, consuming and expensive and associated with inconvenience to the user.

Compared to this prior art, the present invention has the object to provide a system and a method of the type mentioned, which allow a much faster and easier adjustment of the optimization of database access requests or database queries.

This object is achieved by assigning an application memory space for storing hints in a hint table associated with the application, and providing a hint call instead of a hint in the application or building block of the database query application is by which a hint is called from the hint table.

Thus, the present invention contemplates that each application is allocated memory space for receiving an associated hint table, and the static hints previously embedded in program code of the application or a database query block are replaced with hint calls. which invoke a hint from the hint table specified by the call. This makes it possible to dynamically vary the hitherto static hints at any time by updating the associated hint table without interrupting the application in any way. As a result, the external control or influencing of the optimizer by Hints can be much faster and easier adapted to the actual circumstances and needs.

In the preferred embodiment of the invention, it is contemplated that the call is made by embedding a hint variable in an application instead of a hint, which is defined accordingly and has an identifier, or text, by which at that point in the table Hint to be inserted is clearly defined.

Furthermore, an embodiment of the invention is preferred in which independent and separate hint tables are assigned for different applications. However, this does not exclude that a hint table is concurrently assigned to two or more different applications, especially if the applications have relatively similar similarities leading to identical or similar database queries.

Furthermore, it is expedient to provide and maintain a master table of hints as part of a central hint administration, wherein the hint tables assigned to individual applications are subsets of the hints from the master table and are selected according to the specific needs of each application. In this way, the multiple identical or similar evolution of hints by different people is avoided since all are centrally managed for a variety of applications in a large, possibly networked system such as a wide area network are stored, even if subsets of the central hint table are each separately assigned to individual applications and stored in the appendices associated memory space.

In any case, the present invention develops its advantages, especially in connection with very large systems, in particular a database with more than 200 GB and preferably more than 500 GB of storage space with a large number of data stored in a corresponding multiplicity of tables or other files are. Smaller systems often come without hints, but in principle it is the more useful and effective the present invention, the more extensive and complex the databases and the slower the processors used or the smaller their capacity.

Appropriately, it is provided that the Hints a hint table can be selectively activated or deactivated. In particular, this allows a simple parameter control by the activation or deactivation of a hint of the value of a concrete, variable or adjustable - -

dependent parameter. For example, according to a preferred variant it is possible to activate certain hints only for certain periods of time.

In addition, it is expedient if a documentation system is provided which documents and archives the temporal development of the individual hint tables and in particular the master hint table.

The present invention is also directed to a corresponding method comprising a database management system having at least one relational database, at least one server, and at least one client contacting the server for database queries, the database management system having an optimizer of the processing flow controls database queries and uses the client's or server's applications for external control. With regard to such a method, it is provided according to the invention that instead of a hint in an application or a query module of an application, a hint call is provided by which a hint is called from a hint table assigned to the application.

To store the hint table, appropriate memory space must be assigned to the respective application. The access rights to hints and the options for changing hints can be assigned differently depending on requirements. In the preferred embodiment of the invention, a central master hint table is used by an administrator who also determines which hints are used and / or activated from the master hint table to generate the individual hint tables associated with the respective applications , In particular, it is possible in a simple manner that different applications have different phrases and hints in their respective tables, but which can very well overlap in a wide range and in this way corresponding Hints only once must be created centrally, which then several times in different applications or can be used in the same way. If necessary, subordinate operators or administrators may be granted certain access rights to individual hint tables, which allow, for example, certain hints to be enabled or disabled or periods of activation and deactivation of hints. Preferably, however, this is also done centrally by a hint management system from which the sole access to the master hint table exists.

Further advantages, features and embodiments of the present invention will become apparent from the following description of an embodiment shown schematically with reference to a figure as well as further concrete examples from a given database environment. - -

The single FIGURE shows schematically a database management system 1 which, inter alia, has an optimizer 5 and which is connected to a server 3 and a database 2. The database management system can also be integrated into the server 3 or comprise all elements shown. The server 3 is in turn connected to a client 4, whereby the server and client can possibly also be identical. An application program 6 or even several application programs 6 run on the client 4 or else on the server 3.

In the case of a database query, which is directed by an application 6 from the client 4 to the server 3, the database management system 1 is activated, whereupon the optimizer 5 analyzes the database query and interprets and converts it into a concrete sequence of logical operations to the desired data from the database 2 in the shortest possible time and with the least possible effort to extract. The database 2 is a relational database, which is shown here only schematically as a uniform block 2, but which in fact can consist of a large number of individual databases which may be present on a database server (not shown) and implemented locally, but which may also be in the form of distributed memory, which may include memory of client 4 and server 3 under certain circumstances.

The optimizer attempts to control the order of logical operations in such a way that the result of the database query is achieved as economically as possible and in the shortest possible time. However, information about the database or about individual tables of the database 2 may be known or accessible to the user of the application 6, which the optimizer 5 does not have available and does not involve in the definition of the control processes in automatic operation. A user or user or administrator of the system may therefore provide hints or instructions to the optimizer 5 from the application 6, which override other operations otherwise provided or planned by the optimizer, in this way Optimizer 5's effect on database query control.

According to the present invention, however, the hints are not included as such in the application 6 or corresponding building blocks that trigger a database query, but rather the application 6 only contains calls of corresponding hints stored in a hint table 7 associated with the application 6 , When processing the database query and the optimization of the corresponding logical operations, the optimizer 5 therefore calls according to the received with the database query from the application 6 Hin calls the Hints from the hint table 7 and integrates them according to the hint specifications in the Processing or the time or ranking of the searches carried out in the context of the database query. According to the invention, a hint management system is additionally provided which creates, maintains and updates a master H int table 9 and which generates the individual hint tables 7 from the master hint table. It is understood that the application 6 and the hint table 7 in the present case is only an example of a variety of possible applications with associated hint tables, wherein the number of clients and servers in principle can be arbitrary and the entire system part of a larger Network as described above for database 2. It should be understood that the benefits of the present invention will be particularly apparent in very large databases that include, for example, more than 500 gigabytes of memory in a variety of different tables. With very complex and fragmented databases and with a relatively slow-running database management system or server, however, the system can already lead to a noticeable acceleration in the processing of database queries even with smaller memory sizes of eg 100 to 200 gigabytes.

In the following two embodiments of the present invention will be described with reference to two examples, wherein first again the nature of Hint processing will be explained.

Hint processing

Hints are SQL-embedded instructions for the Oracle Optimizer that affect the execution plan of SQL statements. The use of hints is imperative if technical knowledge should flow into the execution of SQL statements. How should the optimizer z. For example, you know that an index with the field Error flag is highly selective for the case "Yes", but not selective at all for the case "No". Hints are still necessary if the optimizer - for whatever reason - does not take the desired / optimal execution plan.

To dynamically (i.e., without program adjustments) change, a Hint management controllable via master data tables has been developed. To use this hint management, the database request invocations must be uniquely identifiable in a program (e.g., by unique numbering or paging in program modules with a unique name). This uniqueness is necessary so that the respective hint data record can be drawn from the master data tables of the hint administration, which can then be transferred to the database request statement via a program variable. Using the example of an implementation in the SAP programming language ABAP, the hint usage is shown below. Here, the database request accesses are outsourced in modularized units (function modules).

^* Start of the definition of the function block FUNCTION TABLE READ TABLE ^* Data declaration a 40-character character variable that takes the hint text DATA: vl_hint_text (40) type c.

^* Call the procedure (ABAP Macro) get-hint for the determination

^* by passing the variables cl_objname vp_sel_num and vp_num the

^* Call office of the

^* Database Request uniquely identified get-hint cl_objname vp_sel_num vp_num vl_hint_text

^* insert dynamically determined hint text in the SELECT statement. This passes the

^* SAP database interface the hint text to the database

^* (here eg ORACLE database system) select ^* from TABLE where ...% _hints oracle vl_hint_text

Whether hints are actually needed is determined during development, in the test or in productive use. The corresponding maintenance of the hint master data takes place, for example. by the database administrators or the technical maintainers of the applications. If no hint is entered in the hint table for a database request call, inserting the back text% _hints does not change the database access.

To dynamically (i.e., without program adjustments) change, a Hint management controllable via master data tables has been developed. To use this hint administration, the following ABAP statements must be implemented in the DB access blocks:

* Declaration of a variable vl_hint_text: DATA: vl_hint_text like / lrs / sθθshint-hint_text.

* Call the macro get-hint get-hint cl_objname vp_whenum '00' vl_hint_text

* Possibly. Calling the macro get-hint-from-itab get-hint-from-itab vl_select_number vl_flow_number vl_hint_text

* Insert hint text in the SELECT

• select ^* from tabname where ...% _hints oracle vl_hint_text

When developing new DB access blocks, the use of dynamic hint management is mandatory. The macro get-hint is called by default with the select number '00' (3rd transfer parameter). This call takes place once per function module and fills an internal table with all hint texts for this when number and especially the variable vl_hint_text with the hint text for the default case '00''01'. Further hint text selections for this when number will only be available via the macro get-hint-from-itab (see below).

Whether hints are actually needed is determined during development, in the test or in productive use. The corresponding maintenance of the Hint master data is done by DP / AI. If there is no master data entry for a particular when number, inserting the hint text (% _hint ...) in the select statement does not change the DB access.

Example 1 (standard case):

FUNCTION / LRS / USTC01_READ_TABLE constants: cl_objname (9) type c value 'USTC01_RT'. data vl_hint_text like / lrs / sθθshint-hint_text.

Get hint text get-hint cl_objname vp_whenum ¹ OO 'vl_hint_text.

^* which select to take? case vp_whenum.

when 1.

select ^* into corresponding fields of table tp_ustcO1 from / irs / sOObustc

% _hints oracle vl_hint_text.

Example 2: 2 hints for a select within a when number.

If different hints have to be used in a select statement depending on the data constellation, the macro get-hint is called first as described above. Depending on the data constellation, the desired hint text is provided with the macro get-hint-from-itab specifying the appropriate select (1st parameter) and consecutive number (2nd parameter).

function / irs / cova01_read_table.

constants: cl_objname (9) type c value 'COVA01_RT'.

data: vl_hint_text like / lrs / sθθshint-hint_text.

^* Default on macro call get-hint cl_objname vp_whenum ¹ OO 'vl_hint_text.

^* which select to take? case vp_whenum.

when δl if vp_sel_bpsaaid_btva> 0.

^* Call macro get-hint-from-itab with serial number 02 get-hint-from-itab ¹ OO '' 02 'vl_hint_text. endif. select ^* from / irs / s00bcova appending table tp_cova01 where custaid = vp_sel_cltaid

% _hints oracle vl_hint_text.

Example 3: Multiple hints for multiple selects within a when number.

If several selects (for different or the same tables) occur within a When number, the macro get-hint is called first as described above. For each Select, the desired hint text is provided with the get-hint-from-itab macro, specifying the appropriate Select (1st parameter) and consecutive number (2nd parameter).

function / irs / agnt91_read_table.

Constant: cl_objname (9) type c value 'AGNT91_RT'.

data: vl_hint_text like / lrs / sθθshint-hint_text.

^* Default on macro of the get-hint cl_objname vp_whenum '00' vl_hint_text.

^* which select to take? case vp_whenum.

When 10. ^* Call macro get-hint-from-itab with select number 00 get-hint-from-itab ¹ OO '' 01 '

(actually not necessary, because vl_hint_text already filled correctly by calling get-hint)

.Select ^* from / LRS / S00SCARR Where ...

% _HINTS ORACLE VL_HINT_TEXT

^* Call macro get-hint-from-itab with select-number 01 get-hint-from-itab '01 '' 01 '(fills vl_hint_text for select number 01, serial number 01.

Select ^* from / LRS / S00SADRS

Where ... (results from select 1)

% _HINTS ORACLE VL_HINT_TEXT

When 1 1.

Claims

PATENT CLAIMS

A system for automatically optimizing request processing in database or database management systems (1) having at least one relational database (2), at least one server (3) and at least one client (4) communicating with the server for data access requests, wherein the database management system (1) comprises an optimizer (5) which controls operations of data access requests and which uses hints from applications (6) of the client (4) or the server (3) for external control of the optimizer (5), characterized that at least one application (6) is allocated memory space for a hint table (7) and that instead of a hint in the application or in a block of a data access request, a hint call is provided, by which a hint is stored from the memory stored in the memory Hint table (7) for external control of the optimizer (5) is called.

2. System according to claim 1, characterized in that the hint call is a variable defined in the application or the data access block.

3. System according to claim 1 or 2, characterized in that separate hint tables (7) are provided for different applications.

4. System according to one of claims 1 to 3, characterized in that a hint management (8) is provided, which is associated with a master hint table, which is maintained via the hint management, wherein the hint table ( 7) an application (6) comprises at least a subset of the master hint table (9).

5. System according to one of claims 1 to 4, characterized in that the hint table (7) an application (6) and / or the master hint table (9) of a hint management (8) during the ongoing operation of an application (6) are changeable.

6. System according to any one of claims 1 to 5, characterized in that the Hints a hint table are individually activated and deactivated.

7. System according to claim 6, characterized in that the hints can be activated and deactivated via adjustable parameters, wherein the parameters comprise at least one parameter for a validity period of a Hints.

8. System according to any one of claims 1 to 7, characterized in that the Hint management has a documentation system which the temporal evolution of both the master hint table (9) and the hint tables (7) of the applications (6 ).

9. A method for optimizing database queries in a database management system according to one of claims 1 to 8, characterized in that in the program code of an application at least call is integrated, through which in the case of database queries on the application for optimizing the processing of the database query a Hlnt from a stored outside the program code, the application assigned

Hint table is called and processed.

10. The method according to claim 9, characterized in that the call is made via a built-in program code, defined Hlntvariable.