DE102006017076A1 - Automatic optimization of query processing in database systems - Google Patents

Abstract

The invention relates to a system for automatically optimizing query processing in database or database management systems (1) with at least one relational database (2), at least one server (3) and at least one client (4), which is used for data access requests with the server in Communication connection occurs, the database management system (1) having an optimizer (5) that controls the processes of data access requests and uses hints from applications (6) of the client (4) or the server (3) for external control of the optimizer (5) , Furthermore, the present invention relates to a method for optimizing database queries in such a database management system. In order to create a system and a method which allow a considerably faster and easier adaptation of the optimization of database access requests or database queries, the system proposes according to the invention 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 module of a data access request, a hint call is provided, by means of which a hint from the hint table (7) stored in the memory is called for external control of the optimizer (5). With regard to the method, it is proposed that at least one call-up is integrated in the program code of an application, by means of which ...

Description

The The present invention relates to a system and a method to optimize query processing in database management systems with at least one relational database, at least one server and at least one client responsible for data access requests communicates with the server, using the database management system an optimizer, the flows of data access requests controls and hints from client or server applications for external control used by the optimizer.

Appropriate Optimization systems and corresponding procedures for the process of data access requests or database queries are from the Prior art in particular for the management and access of large databases known. Such a system is, for example, the Oracle database system, which is a standard query language (SQL - Standard Query Language) supported and uses an optimizer which controls the processing or the process controls database access requests or database queries, the optimizer as well Hints supported. Hints are instructions to the optimizer, regardless of one of the optimizers otherwise provided sequence of a database query at least in Share a specific, given by the hint flow control make. For example, could a database query a more or less complex combination the data require different tables, with the speed and efficiency of the resulting query results from the Order depends in which respective tables or their data are combined or compared. The optimizer used for the controller the procedure general criteria, such. the relative scope the tables to make a certain order of the combinations. In contrast, the user or programmer of a particular application be known that a Query criterion especially with respect to a specific table is highly selective, but according to the scheme used by the optimizer would have a subordinate ranking. A hint can be used to instruct the optimizer when editing To start the query with the table for which a query criterion is highly selective is, resulting in a significant acceleration in editing the entire data query can. Such Hints are therefore by a programmer or a correspondingly experienced user from his / her special knowledge an application and the tables to which the specific application in the case of database queries can access the program code an application or corresponding database query blocks an application inserted.

There However, both the specific applications and the sought Data containing files or tables in terms of scope and Change content significantly can, is at least from time to time a corresponding adaptation of such Hints required. Likewise new database queries due to changing or new applications or because of new service offerings the development and the Require use of new hints. However, this requires interruptions the expiration of the application, since a change from hints immediately is intervened in the program code.

Furthermore must do this in the case of a variety of different applications regularly and independently for all Applications are repeated, sometimes in different Applications used Hints can be identical.

The appropriate maintenance of applications including embedded therein Hints is relatively time-consuming and costly, especially if an application program is very extensive and simultaneous from a big one Number of users is used, whose work is appropriate for one Maintenance and care of the application program must be interrupted. alternative must have one such care outside usual Operating times are made. However, there are applications in the Run 24-hour operation and in particular also applications that run in globally networked systems, so that an interruption of the operation, and if only for reinstalling or supplementing the hints with patches or the like, in any case consuming and expensive and with inconvenience for the user is connected.

Opposite this The prior art is based on the object of the present invention to provide a system and a method of the type mentioned, which is a much faster and easier adaptation of the optimization allow 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 which calls a hint from the hint table.

Therefore For example, the present invention contemplates that each application requires storage space or storage space for the recording is associated with an associated hint table, and the previously in program code of the application or a building block for database queries embedded, static hints are replaced by hint calls, one closer by calling designated hint from the hint table call.

Thereby will it be possible to vary the hitherto static hints dynamically at any time, by, without the Application is interrupted in any way, the associated hint table is updated. This allows the external control or influence the optimizer by Hints much faster and easier actual conditions and needs customized become.

In the preferred embodiment The invention provides that the call is made by that instead a hint in an application a hint variable is embedded which is defined accordingly and has an identifier or text, through which the hint to be inserted from the table at this point is clearly defined.

Farther is an embodiment of the invention, in which for different applications independently and separated hint tables are assigned. This concludes, however not from one Rear table at the same time two or more different applications is assigned, in particular, if the applications have relatively large similarities have identical or similar database queries to lead.

Farther is expediently a master table provided by Hints as part of a central hint management and maintained, with the respective individual applications assigned Hin tables are subsets of hints from the master table and be selected according to the specific needs of each application. In this way, the multiple becomes identical or similar Development of hints avoided by different persons, since all for a variety of applications in a large, possibly networked System such as in a "Wide Area Network" (large-scale network) needed Hints centrally managed and stored, even if subsets of central hint table each separately assigned to individual applications and stored in the appendices associated memory space.

anyway In particular, the present invention develops its advantages in connection with very large systems, especially database with more than 200 GB and preferably more as a 500GB of storage space with a large amount of data in a corresponding variety of tables or other files are stored. Smaller systems often come without hints, whereby however basically applies that the present invention more useful and effective is, the more extensive and complex the databases and the slower the processors used or the smaller their capacities.

Appropriately, is provided that the Optionally, a hint table can be enabled or disabled can. this makes possible in particular a simple parameter control, by activating or Deactivating a hint from the value of a concrete, variable or adjustable parameter. For example is it according to one preferred variant possible, certain hints only for certain periods to activate.

Furthermore it is useful if a documentation system is provided, which the temporal development the individual hint tables and in particular the master hint table documented and archived.

The The present invention is also related to a corresponding method which is a database management system with at least a relational database, at least one server and at least a client that is responsible for Database queries come into contact with the server, using the database management system an optimizer, the processes of processing database queries Controls and applications from the client or server hints for an external Control used. With regard to such a method, the invention provides that instead a hint in an application or a query building block of a Application a hint call is provided, through which a hint is called from a hint table associated with the application.

For storage of the hint table, the respective application must have corresponding memory space be assigned to. The access rights to hints and the options for changing hints can be assigned differently depending on your needs. In the preferred embodiment of the invention, a central master hint table is used by an administrator who also determines which hint from the master hint table is used and / or activated to generate the individual hint tables associated with the respective applications become. 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 The present invention will be apparent from the following Description of a schematically reproduced with reference to a figure embodiment as well as other concrete examples from a given database environment.

In the single figure can be seen schematically represented a database management system 1 , which among other things an optimizer 5 and which one with a server 3 and a database 2 connected is. The database management system can also be in the server 3 be integrated or comprise all elements shown. The server 3 is in turn with a client 4 If necessary, the server and client can also be identical. An application program 6 or several application programs 6 run on the client 4 or on the server 3 ,

In the case of a database query, in the context of an application 6 from the client 4 to the server 3 becomes the database management system 1 activated, prompting the optimizer 5 The database query is analyzed and interpreted and converted 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. Database 2 is a relational database, here only schematically as a unified block 2 but in fact may consist of a plurality 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 also include memory from client 4 and server 3 includes.

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. The user of the application 6 but may be information about the database or about individual tables of the database 2 known or accessible to the optimizer 5 is not available and does not include in automatic operation in the determination of the control processes. A user or user or administrator of the system may therefore be dependent on the application 6 from hints or instructions in the form of so-called "hints" to the optimizer 5 which override other operations otherwise scheduled or planned by the optimizer, in this way enhance the effect of the optimizer 5 to improve the control of the database query.

However, according to the present invention, the hints are not as such in the application 6 or corresponding blocks that trigger a database query, but the application 6 Rather, it only contains calls to corresponding hints that are in one of the applications 6 associated hint table 7 are stored. When processing the database query and optimizing the corresponding logical processes, the optimizer calls 5 therefore according to the database query of the application 6 received hint calls the corresponding hints from the hint table 7 and integrates them according to the hint specifications into 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 comprises a master hint table 9 creates, maintains and updates from the master hint table the individual hint tables 7 generated. 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 can be part of a larger network, as already above for the database 2 has been described. It goes without saying that the advantages of the present invention in particular in very large databases come into play, for example, include 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 are still two embodiments of the present invention with reference to two examples, being first again the nature of hint processing is explained.

Hint processing

Hints are statements embedded in SQL code for the Oracle Optimizer, the the execution plan of Influence SQL statements. The use of Hints is then mandatory necessary if technical knowledge should flow into the execution of SQL statements. How should the Optimi zer z. For example, you know that an index with the field Error Flag for the Case "yes" highly selective, in the case of "no" but at all is not selective. Hints are still necessary if the optimizer - from which establish always - not the desired / optimal execution plan takes.

Around Dynamic (that is, without program customizations) To change hints is one about master data tables controllable hint administration has been developed. To use this Hintverwaltung need the call points of the database requests in a program uniquely be identifiable (e.g., by unique numbering or paging in program modules with a unique name). This uniqueness is necessary so that from the master data tables of the Hintverwaltung the corresponding hint record can then be pulled, then via a program variable the database request statement can be passed. Exemplary An implementation in the SAP programming language ABAP becomes the Hint use shown below. Here are the database request accesses outsourced in modularized units (function blocks).

If Hints actually needed will be during development, test or productive use. The corresponding maintenance of the hint master data takes place, for example. through the Database administrators or the technical maintainers of the applications. Is for a call to a database request does not return a hint in the hint table entered, causes the insertion the back text% _hints no change of database access.

In order to be able to change hints dynamically (ie without program adjustments), a hint management via master data tables has been developed. To use this hint administration, the following ABAP statements must be implemented in the DB access blocks:

at The development of new DB access blocks is the use of the dynamic hint management duty. The macro is get-hint standard with the select number '00' (3rd transfer parameter) call. This call is found once per function module at the beginning and fills an internal table with all hint texts for this when number and specifically the variable vl_hint_text with the hint text for the default case '00' '01'. More hint text selections for If when need only find out about this when needed Macro get-hint-from-itab (see below) instead.

If Hints actually needed will be during development, test or productive use. The corresponding maintenance of the Hint master data is done by DP / AI. Is for a certain When number no master data entry exists causes the insertion the hint text (% _hint ...) in the select statement no change in DB access.

Example 1 (standard case):

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

If in a select statement different hints in dependence be used by the data constellation, first the macro called get-hint as described above. Depending on the data constellation is then with the macro get-hint-from-itab stating the appropriate Select (1st parameter) and sequential number (2nd parameter) provided the desired hint text.

Example 3: several hints for many Selects within a when number

If within a when number multiple selects (for different or the same Tables), first the macro get-hint as described above called. For each Select is then specified with the macro get-hint-from-itab the corresponding select (1st parameter) and consecutive number (2nd parameter) the desired one Hint text provided.

Claims (10)

System for automatically optimizing request processing in database or database management systems ( 1 ) with 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, the database management system ( 1 ) an optimizer ( 5 ) that controls operations of data access requests and hints from applications ( 6 ) of the client ( 4 ) or the server ( 3 ) for an external control of the optimizer ( 5 ), characterized in that at least one application ( 6 ) Storage 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 means of which a hint is retrieved from the hint table stored in the memory (FIG. 7 ) for the external control of the optimizer ( 5 ) is called. System according to claim 1, characterized in that the hint call a defined in the application or the data access block Variable is. System according to claim 1 or 2, characterized in that ge for different applications separated hint tables ( 7 ) are provided. System according to one of Claims 1 to 3, characterized in that a hint management ( 8th ) associated with a master hint table maintained via the hint management, the hint table ( 7 ) of an application ( 6 ) at least a subset of the master hint table ( 9 ). 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 administration ( 8th ) while an application is running ( 6 ) are changeable. System according to one of claims 1 to 5, characterized that the Hints a hint table individually activated and deactivated are. System according to claim 6, characterized in that the hints via adjustable Parameters can be activated and deactivated, the parameters at least one parameter for a validity period a hint. System according to one of Claims 1 to 7, characterized in that the hint management comprises a documentation system which monitors the development over time of both the master hint table ( 9 ) as well as the hint tables ( 7 ) of applications ( 6 ). Method for optimizing database queries in a database management system according to one of claims 1 to 8, characterized in that in integrated the program code of an application at least call is through which in the case of database queries over the Application for optimizing the processing of the database query Hlnt from an outside stored in the program code, the application associated hint table is called and processed. Method according to claim 9, characterized in that that the Call back via a defined variable variable integrated in the program code takes place.