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

Abstract

System for the automatic optimization of query processing in database systems (1), with at least one relational database (2), at least one server (3) and at least one client (4) that communicates with the server for data access requests, the database system (1 ) has 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), at least one application (6) Memory space is allocated 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 for external control of the optimizer (5), characterized in that the hints of a hint table can be activated individually via adjustable parameters and can be deactivated, the parameters comprising at least one parameter for a validity period of a hint.

Description

The present invention relates to a system and a method for optimizing query processing in database management systems with at least one relational database, at least one server and at least one client that communicates with the server for data access requests, the database management system having an optimizer that processes 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 to extensive databases. Such a system is, for example, the Oracle database system, which supports a standard query language (SQL - Standard Query Language) and uses an optimizer, which controls the processing or execution of database access requests or database queries, the optimizer also supporting Hints. Hints are instructions to the optimizer, regardless of a sequence of a database query otherwise provided by the optimizer, at least in part to carry out a specific sequence control predetermined by the hint. For example, a database query could require a more or less complex combination of the data from different tables, the speed and efficiency of the resulting query results depending on the order in which corresponding tables or their data are combined or compared with one another. The optimizer uses general criteria to control the process, e.g. the relative size of the tables to make a certain order of combinations. On the other hand, the user or programmer of a certain application can be aware that a query criterion is highly selective precisely with regard to a very specific table, but which would have a subordinate ranking according to the scheme used by the optimizer. A hint can instruct the optimizer to start with the processing of the query with the table for which a query criterion is highly selective, which can lead to a considerable acceleration in the processing of the entire data query. Such hints are therefore inserted into the program code of an application or into corresponding database query modules of an application by a programmer or a correspondingly experienced user from his or her special knowledge of an application and the tables which the special application can access in the case of database queries.

However, since both the specific applications and the files or tables containing the searched data can change considerably with regard to scope and content, a corresponding adjustment of such hints is necessary at least from time to time. Likewise, new database queries due to changing or new applications or due to new service offerings may require the development and use of new hints. However, this necessitates interruptions in the course of the application, since if the Hints change, the program code is immediately interfered with.

In addition, in the case of a large number of different applications, this must be repeated regularly and independently of one another for all applications, and in some cases the hints used in different applications can be identical.

Appropriate maintenance of applications, including the hints embedded therein, is relatively time-consuming and costly, especially when an application program is very large and is used by a large number of users at the same time, whose work must be interrupted for appropriate maintenance and care of the application program . Alternatively, such maintenance must take place outside of normal operating hours. However, there are applications that run in 24-hour operation and in particular also applications that run in globally networked systems, so that the operation is interrupted, if only for reinstalling or supplementing the Hints with patches or the like, in each Case is complex and costly and inconvenient for the user.

From the US 2004/0019587 A1 a method for request processing in a database management system is known, the request processing being based on an access plan in order to extract the desired data from a storage device in accordance with the request. The access plan takes into account links with hints that are generated by identifying the request.

The US 2003/0236782 A1 describes a fine-grained control mechanism based on strategy functions linked to database objects. The strategy function is interrupted when a Inquiry regarding a data object is received, whereby the strategy function returns optimizing hints with which the inquiry is supplemented.

Compared to this prior art, the object of the present invention is to create a system and a method of the type mentioned at the outset, which allow a much faster and easier adaptation of the optimization of database access requests or database queries.

This object is achieved in that an application is or is allocated memory space for storing hints in a hint table assigned to the application, and in that a hint call is provided in the application or a component of the application for a database query instead of a hints is by which a hint is called from the hint table.

Accordingly, the present invention provides that each application is allocated storage space for the storage of an associated hint table, and the static hints previously embedded in program code of the application or a module for database queries are replaced by hint calls, which one call the hint specified by the call from the hint table.

This makes it possible to dynamically vary the previously 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 adapted to the actual conditions and needs much more quickly and easily.

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

Furthermore, an embodiment of the invention is preferred in which hint tables are assigned independently and separately for different applications. However, this does not exclude that a back table is assigned to two or more different applications at the same time, especially if the applications have relatively large similarities which lead to identical or similar database queries.

Furthermore, a master table of hints is expediently provided and maintained within the framework of a central hint administration, the hint tables assigned to individual applications being subsets of the hints from the master table and being selected in accordance with the special requirements of each application. In this way, the multiple identical or similar development of hints by different people is avoided, since all hints required for a large number of applications in a large, possibly networked system, for example in a “wide area network”, are centrally managed and are stored, even if subsets of the central hint table are each separately allocated to individual applications and stored in the memory space allocated to the applications.

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 which are stored in a corresponding plurality of tables or other files. Smaller systems often get by without hints, although the basic principle is that the more extensive and complex the databases and the slower the processors used or the lower their capacities, the more useful and effective the present invention can be used.

It is expediently provided that the hints of a hint table can optionally be activated or deactivated. This enables, in particular, simple parameter control, in that the activation or deactivation of a rear is made dependent on the value of a specific, variable or adjustable parameter. For example, according to a preferred variant, it is possible to activate certain hints only for certain periods.

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

The present invention is also directed to a corresponding method which has a database management system with at least one relational database, at least one server and at least one client which comes into contact with the server for database queries, the database management system having an optimizer which processes the processing controls database queries and uses hints from client or server applications for external control. With regard to such a method, the invention provides that instead of a hint in an application or a query block of an application, a hint call is provided, by means of which a hint is called from a hint table assigned to the application.

Corresponding memory space must be allocated to the respective application in order to save the hint table. The access rights to hints and the options for changing hints can be assigned differently as required. In the preferred embodiment of the invention, a central master hint table is used by an administrator who also specifies which hints from the master hint table are used and / or activated to generate the individual hint tables assigned to the respective applications . It is in particular possible in a simple manner that different applications have different sentences and hints in their respective tables, but they can very well overlap in large areas and in this way corresponding hints only have to be created centrally once, which then occurs several times in different applications or can be used in the same way. If necessary, subordinate operators or administrators can be granted certain access rights to individual hint tables, which make it possible, for example, to activate or deactivate certain hints or to define periods for the activation and deactivation of hints. However, this is also preferably carried out centrally from a hint management system from which there is sole access to the master hint table.

Further advantages, features and embodiments of the present invention will become clear on the basis of the following description of an exemplary embodiment shown schematically on the basis of a figure and on the basis of further concrete examples from a given database environment.

A database management system is shown schematically in the single figure 1 which, among other things, an optimizer 5 and which with a server 3rd and a database 2nd connected is. The database management system can also be in the server 3rd be integrated or include all elements shown. The server 3rd is again with a client 4th connected, where server and client may also be identical. An application program 6 or several application programs 6 run on the client 4th or on the server 3rd .

In the case of a database query that is part of an application 6 from the client 4th to the server 3rd is directed, the database management system 1 activated, whereupon the optimizer 5 the database query is analyzed and interpreted and converted into a concrete sequence of logical operations to get the desired data from the database 2nd to extract in the shortest possible time and with as little effort as possible. Database 2nd is a relational database, shown here only schematically as a single block 2nd is shown, but which can actually consist of a large number of individual databases, which can be present on a database server (not shown) and implemented locally, but which can also be in the form of distributed memory, which may also be memory from the client 4th and server 3rd includes.

The optimizer tries to control the sequence 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 in the database 2nd known or accessible by the optimizer 5 is not available and is not included in the definition of control processes in automatic mode. A user or user or administrator of the system can therefore use the application 6 from notes or instructions in the form of so-called “hints” to the optimizer 5 which take precedence over other operations otherwise provided or planned by the optimizer, in order in this way to have the effect of the optimizer 5 to improve the control of the database query.

According to the present invention, however, the hints are not used as such 6 or corresponding modules that trigger a database query, but the application 6 Rather, it only contains calls to corresponding hints in one of the application 6 assigned hint table 7 are filed. The optimizer calls when processing the database query and optimizing the corresponding logical processes 5 therefore corresponding to the one with the database query from the application 6 received hint calls the corresponding hints from the hint table 7 and integrates them in accordance with the hint specifications into the processing or the time or ranking of the searches performed as part of the database query.

According to the invention, a hint management system is additionally provided, which has a master hint table 9 creates, maintains and updates and which of 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 for a large number of possible applications with assigned hint tables, the number of clients and servers also being arbitrary in principle and the entire system being part of a larger network, just like the database above 2nd has been described. It is understood that the advantages of the present invention are particularly evident in the case of very large databases, which include, for example, more than 500 gigabytes of memory in a large number of different tables. With very complex and fragmented databases and with a relatively slow-working database management system or server, the system can lead to a noticeable acceleration in the processing of database queries even with smaller memory sizes of, for example, 100 to 200 gigabytes.

Two exemplary embodiments of the present invention are described below with the aid of two examples, the essence of the hint processing being explained again first.

Hint processing

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

In order to be able to change Hints dynamically (i.e. without program adjustments), a hint management system that can be controlled via master data tables has been developed. To use this back-up management, the call points of the database requirements must be clearly identifiable in a program (e.g. by unique numbering or outsourcing in program modules with a clearly assigned name). This uniqueness is necessary so that the respective back data record can be drawn from the master data tables of the back office, which can then be transferred to the database request statement via a program variable. Using an implementation in the SAP programming language ABAP is shown below. The database request accesses are outsourced to modularized units (function modules).

• * Start of the definition of the function block FUNCTION TABLE_READ_TABLE
• * Data declaration of a 40-character character variable that takes up the hint text DATA: vl_hint_text (40) type c.
• * Call the procedure (ABAP Macro) get-hint for the transmission
• * by passing the variables cl_objname vp_sel_num and vp_num the
• * Call point of the
• * Database requirement clearly identified get-hint cl_objname vp_sel_num vp_num vl_hint_text
• * Include dynamically determined hint text in the SELECT statement. Thereby passes the
• * SAP database interface the hint text to the database
• * (here e.g. ORACLE database system) select * from TABLE where ...% _hints oracle vl_hint_text

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

• • Deklaration einer Variablen vl_hint_text:
  • DATA: vl_hint_text like /Irs/s00shint-hint_text.
• • Aufruf des Makros get-hint get-hint cl_objname vp_whenum ‚00‘ vl_hint_text
• • Evtl. Aufruf des Macros get-hint-from-itab get-hint-from-itab vl_select_nummer vl_lauf_nummer vl_hint_text
• • Hint-Text im SELECT einbauen
  • • select * from tabname where ... %_hints oracle vl_hint_text

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

• • Declaration of a variable vl_hint_text:
  • DATA: vl_hint_text like / Irs / s00shint-hint_text.
• • Call the macro get-hint get-hint cl_objname vp_whenum '00' vl_hint_text
• • Possibly. Call of the macro get-hint-from-itab get-hint-from-itab vl_select_number vl_lauf_number vl_hint_text
• • Include hint text in the SELECT
  • • select * from tabname where ...% _hints oracle vl_hint_text

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

Whether hints are actually needed is determined during development, testing or in productive use. The corresponding maintenance of the master data is done by DP / AI. If there is no master data entry for a certain 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 / s00shint-hint_text.
 Get back text
 get-hint cl_objname vp_whenum '00' vl_hint_text.
 * which select to take?
 case vp_whenum.



 when 1.
 select * into corresponding fields of table tp_ustc01
       from / lrs / s00bustc
    % _hints oracle vl_hint_text.
    

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

 function /lrs/cova01_read_table.
 constants:
  cl_objname(9) type c value ‚COVA01_RT‘.
  data : vl_hint_text like /lrs/s00shint-hint_text.
  * Standardaufruf des Makros
 get-hint cl_objname vp_whenum ‚00‘ vl_hint_text.
 * welchen Select nehmen ?
 case vp_whenum.

 when 81.
 if vp_sel_bpsaaid_btva > 0.
 * Makro get-hint-from-itab aufrufen mit laufender Nummer 02
       get-hint-from-itab ‚00‘ ‚02‘ vl_hint_text.
 endif.
    select * from /lrs/s00bcova appending table tp_cova01
          where custaid = vp_sel_cltaid
 %_hints oracle vl_hint_text.

If different hints must be used in a select statement depending on the data constellation, the macro get-hint is first called as described above. Depending on the data constellation, the macro get-hint-from-itab is then specified with the corresponding select ( 1 . Parameters) and serial number ( 2nd . Parameters) the desired hint text is provided.

 function / lrs / cova01_read_table.
 constants:
  cl_objname (9) type c value 'COVA01_RT'.
  data: vl_hint_text like / lrs / s00shint-hint_text.
  * Standard call of the macro
 get-hint cl_objname vp_whenum '00' vl_hint_text.
 * which select to take?
 case vp_whenum.

 when 81.
 if vp_sel_bpsaaid_btva> 0.
 * Call macro get-hint-from-itab with consecutive number 02
       get-hint-from-itab '00''02' vl_hint_text.
 endif.
    select * from / lrs / 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.

 function /lrs/agnt91_read_table.
 constants:
  cl_objname(9) type c value ‚AGNT91_RT‘.

 data : vl_hint_text like /lrs/s00shint-hint_text.

 * Standardaufruf des Makros
 get-hint cl_objname vp_whenum ‚00‘ vl_hint_text.
 * welchen Select nehmen ?
 case vp_whenum.

 When 10.
 * Makro get-hint-from-itab aufrufen mit Select-Nummer 00
 get-hint-from-itab ‚00‘ ‚01‘
 (eigentlich nicht notwendig, da vl_hint_text schon durch Aufruf get-hint richtig gefüllt)
 .Select * from /LRS/S00SCARRR
 Where ...
       %_HINTS ORACLE VL_HINT_TEXT
 * Makro get-hint-from-itab aufrufen mit Select-Nummer 01
 get-hint-from-itab ‚01‘ ‚01‘ (füllt vl_hint_text für Select-Nummer 01, laufende Nummer 01.
 Select * from /LRS/S00SADRS
        Where ... (Ergebnisse aus select 1)

       %_HINTS ORACLE VL_HINT_TEXT
 When 11.

If several Selects (for different or the same tables) occur within a When number, the macro get-hint is called up as described above. For each select, the macro get-hint-from-itab is then used, specifying the corresponding select ( 1 . Parameters) and serial number ( 2nd . Parameters) the desired hint text is provided.

 function / lrs / agnt91_read_table.
 constants:
  cl_objname (9) type c value 'AGNT91_RT'.

 data: vl_hint_text like / lrs / s00shint-hint_text.

 * Standard call of the macro
 get-hint cl_objname vp_whenum '00' vl_hint_text.
 * which select to take?
 case vp_whenum.

 When 10.
 * Call the macro get-hint-from-itab with select number 00
 get-hint-from-itab '00''01'
 (actually not necessary, since vl_hint_text is already filled correctly by calling get-hint)
 .Select * from / LRS / S00SCARRR
 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, consecutive number 01.
 Select * from / LRS / S00SADRS
        Where ... (results from select 1)

       % _HINTS ORACLE VL_HINT_TEXT
 When 11. 

Claims (8)

System for the automatic optimization of query processing in database systems (1), with at least one relational database (2), at least one server (3) and at least one client (4) that communicates with the server for data access requests, the database system (1 ) has an optimizer (5) that controls the processes of data access requests and the hints out Applications (6) of the client (4) or the server (3) are used for external control of the optimizer (5), at least one application (6) being allocated memory space for a hint table (7) and that instead of a hint in a hint call is provided for the application or in a module of a data access request, by means of which a hint from the hint table (7) stored in the memory is called for external control of the optimizer (5), characterized in that the hints of a hint Table can be activated and deactivated individually via adjustable parameters, the parameters comprising at least one parameter for a validity period of a hint. System according to Claim 1 , characterized in that the hint call is a variable defined in the application or the data access block. System according to Claim 1 or 2nd , characterized in that separate hint tables (7) are provided for different applications. System according to one of the Claims 1 to 3rd , characterized in that a hint management (8) is provided, which is assigned a master hint table which is maintained via the hint management, the hint table (7) of an application (6) at least a subset the master hint table (9). System according to one of the Claims 1 to 4th , characterized in that the hint table (7) an application (6) and / or the master hint table (9) of a hint management (8) can be changed while an application (6) is in operation. System according to one of the Claims 1 to 5 , characterized in that the hint administration has a documentation system which documents the temporal development of both the master hint table (9) and the hint tables (7) of the applications (6). Method for optimizing database queries in a database management system according to one of the Claims 1 to 6 , characterized in that at least one call-up is integrated in the program code of an application, by means of which, in the case of database queries via the application for optimizing the processing of the database query, a hint is called up and processed from a hint table stored outside the program code and assigned to the application. Procedure according to Claim 7 , characterized in that the call is made via a defined rear variable integrated in the program code.

Images