Automated real-time index management

Abstract

Automated index management requires automatically monitoring the query workload in the DBMS to identify the set of high-load queries used to evaluate new potential indexes. Potential indexes are automatically created, evaluated, and tested without user approval or action, and then made available system-wide for executing end-user queries. Indexes created through automated index management are referred to as automatic indexes in this document.

Description

Technical Field

[0001] This invention relates to the management of indexes in a database management system (DBMS). Background Technology

[0002] In DBMS, indexes are an essential feature for database performance. Despite advancements in alternative performance features such as materialized views and in-memory columnar storage and processing, the importance of indexes remains paramount. Optimal index mix minimizes resource utilization (CPU and I / O) when retrieving relatively small amounts of data from very large tables, and improves application throughput.

[0003] Because of the importance of indexes, managing index mix is ​​a crucial task. Generally, managing index mix involves creating index mixes that provide optimal performance and maintaining that mix to ensure it remains optimal.

[0004] Creating an optimal index mix requires a broad understanding of the database structure, the workload of querying and updating the database, and the internal workings of the DBMS. Furthermore, once created, managing the mix to ensure it provides optimal performance is a demanding task, requiring continuous monitoring of workload, data structure changes, and reassessment of the performance provided by existing indexes and the potential performance of any new indexes added to the mix. Because this task is so demanding, it is often not performed, or performed insufficiently or incompletely, leading to performance degradation by the actual implementation of the index mix.

[0005] This document describes techniques for the automated management of indexes. The methods described in this section are methods that can be sought, but are not necessarily methods that have been previously conceived or sought. Therefore, unless otherwise indicated, none of these methods should be assumed to qualify as prior art simply because they are included in this section. Attached Figure Description

[0006] In the attached diagram:

[0007] Figure 1 This is a diagram depicting a process for automated index management according to an embodiment of the present invention.

[0008] Figure 2 This is a diagram depicting the process for identifying and generating a meta-only automatic index according to an embodiment of the present invention.

[0009] Figure 3A This is a diagram illustrating the process of obtaining compilation qualification for meta-index only according to an embodiment of the present invention.

[0010] Figure 3BThis is a diagram illustrating the process of obtaining compilation qualification for meta-index only according to an embodiment of the present invention.

[0011] Figure 4A This is a diagram depicting a process for test-execute verification according to an embodiment of the present invention.

[0012] Figure 4B The diagram depicts a test execution verification of a process for making automatic indexing available according to an embodiment of the present invention.

[0013] Figure 5 This is a diagram depicting the first-in verification implemented based on a shared cursor pool mechanism according to an embodiment of the present invention.

[0014] Figure 6A This is a diagram depicting activities for managing the use of automatic indexing according to an embodiment of the present invention.

[0015] Figure 6B This is a diagram depicting a process for implementing just-in-time automatic indexing according to an embodiment of the present invention.

[0016] Figure 7A This is a diagram depicting a container database management system according to an embodiment of the present invention.

[0017] Figure 7B This is a diagram depicting a container database management system according to an embodiment of the present invention.

[0018] Figure 8 A method for implementing automated index management in a container database management system according to an embodiment of the present invention is described.

[0019] Figure 9 This is a diagram depicting a computer system that can be used to implement embodiments of the present invention.

[0020] Figure 10 A software system is described that can be used to control the operation of a computer system according to embodiments of the present invention. Detailed Implementation

[0021] In the following description, numerous specific details are set forth for illustrative purposes in order to provide a thorough understanding of the invention. However, it will be apparent, however, that the invention may be practiced without these specific details.

[0022] General Overview

[0023] This paper describes methods for automating index management, referred to herein as automated index management. Automated index management requires automatically monitoring the query workload in the DBMS to identify the high-load query set used to evaluate new potential indexes. Potential indexes are automatically created, evaluated, and tested without user approval or action, ensuring they are not used by database application workloads, and then made available to the system for executing end-user queries. Indexes created through automated index management are referred to herein as automatic indexes.

[0024] Figure 1 This is a flowchart overview of the key operations and activities involved in automated index management. (Refer to...) Figure 1 Operations 105 through 130 are executed iteratively as a loop to produce new automatic indexes. These operations are collectively referred to as automatic index creation. Each iteration of this loop may produce a new automatic index, a change in the state of an automatic index, or the removal of an automatic index that is actually considered unhelpful.

[0025] At 105, queries that form a working set. The working set is formed by monitoring executed queries to determine which are executed more frequently within a threshold time period. If a query has not been added to the working set for at least the threshold time period, then the query is added to the working set.

[0026] At 110, automatic index candidates are identified. According to an embodiment, candidates are identified by monitoring column usage. Candidates can be new indexes that have keys for columns that are frequently used in filtering predicates, join predicates, and other query operations in the working set, such as sorted or grouped operations.

[0027] In section 115, the candidate auto-index is created as a meta-only auto-index. A meta-only auto-index is defined in the database dictionary but is not yet materialized. Not being materialized means that the underlying data used for the index (“index data”) has not yet been generated and stored in the database. An example of index data is key-value pairs organized in the form of a binary tree. For meta-only auto-indexes, index statistics can be collected. By defining auto-indexes in this way, when a query is compiled, one or more candidate execution plans can be generated that use the meta-only auto-index even though it is not materialized.

[0028] Meta-auto-indexes are also created as invisible indexes. Invisible indexes are not considered by the DBMS when compiling queries issued by database applications or end users.

[0029] In step 120, a compiler-only qualification is performed for each query in the working set. In compiler-only qualification, each query in the working set is compiled by the DBMS. When compiling queries in the working set, compilation considers only meta-auto indexes and other existing indexes. Compilation can produce execution plans with and without meta-auto indexes. Generally, the execution plan with the lowest cost is selected. If compilation selects an execution plan using meta-auto indexes for a query, then the meta-auto indexes have already qualified for use for that query. Any auto indexes that have qualified for use for any query in the working set are added to the "compile-qualified set".

[0030] In step 125, only meta-automatic indexes in the compiled set are materialized. Even when materialized, the automatic indexes remain invisible, preventing them from being used in end-user queries. This prevents the use of automatic indexes that could lead to performance degradation before their potential to improve performance is confirmed.

[0031] In section 130, test execution verification is performed on the concrete, compile-qualified set. Test execution verification involves executing queries in the working set to determine whether the use of automatic indexes actually improves query performance. Automatic indexes that improve the performance of any queries in the working set qualify for test execution.

[0032] It's possible that an automatic index might improve the execution of one query in the working set, but degrade the execution of another query in the same set. In this case, the automatic index can be considered qualified for testing, albeit in a limited way, because it is blocked from being used for the execution of said other query. When the use of an automatic index is blocked from the execution of a specific query, this is referred to here as locking down the automatic index for that query.

[0033] In 135, the automatic indexes that qualify for test execution are made "visible," which means that the compilation of queries issued by end users takes the automatic indexes into account and can generate an execution plan that uses the automatic indexes.

[0034] The test execution verification only prevents the use of automatic indexes that degrade query execution for queries that are in the working set. However, once it becomes visible, automatic indexes can degrade query execution for queries that are not in the working set. Therefore, for such queries, a mechanism referred to in this paper as first verification is used to determine whether the use of automatic indexes improves query execution performance. (140)

[0036] In the initial verification, the execution plan is tested for the first one or more times when the automatic index is used on the query to verify whether the query execution has improved or deteriorated. The automatic index is prevented from being used in subsequent compilations of the query. Therefore, the execution plan is tested when a database session compiles a query that initially uses the automatic index. For one or more other database sessions that could compile the query simultaneously or subsequently, the automatic index is prevented from being used in the compilation of that query.

[0037] The execution plan for queries that use automatic indexes is evaluated. If query performance deteriorates, the automatic indexes are blocked for that query, thus preventing other database sessions and users from experiencing performance degradation for that query.

[0038] Monitor automatic indexing. If it is not used, disable automatic indexing. (145)

[0039] Identify and create only meta-automatic indexes

[0040] Figure 2 This is a flowchart depicting the process of identifying meta-only auto-indexes in further detail. Once identified, the process creates the meta-only auto-index.

[0041] Reference Figure 2 High-load queries are selected. (205) High-load queries are identified by examining query history 235. Query history 235 is a repository within the DBMS that tracks query execution, including performance metrics and information on when a query was most recently executed and last selected as a high-load candidate for the working set. Selection criteria may include: (a) the query has at least a certain workload threshold (measured in terms of performance metrics) within the time window; (b) the query is new, i.e., it has not recently been selected for the working set within the threshold time period; and (c) the query's workload has a threshold ranking compared to other recently executed queries. No queries may be selected.

[0042] The information in Query History 235 is generated at least in part by the database process that executes the query within the DBMS. For each query, Query History 235 may include: (a) performance metrics about the execution of the query, including the number and average of buffer accesses, I / O reads, rows processed, CPU processing time, and elapsed execution time; (b) the last execution time; (c) the execution plan used, including which indexes were used and evaluated at compile time; and (d) when the query was last selected for the working set.

[0043] In section 215, a candidate set of automatic indexes is formed by examining the column usage of the queries in the working set. Generally, the resulting indexes are those that have keys corresponding to one or more columns in different groups of the WHERE clause filter predicate (“filter predicate”) and join predicate, and for which indexes are not yet defined in the database dictionary. Automatic indexes can be formed for each group of columns that have columns referenced in the combination of filter predicates or join predicates. Each group of columns can represent a key or composite key used for a potential automatic index.

[0044] Automatic indexes can be created for each distinct set of columns. However, when an automatic index can be used for multiple sets of columns, as illustrated in the example below, only one automatic index may be created. Creating fewer automatic indexes saves storage resources and reduces the processing (CPU and I / O) required to maintain the index when modifying a table indexed by an automatic index.

[0045] To illustrate how a candidate set is formed, the following is provided: Figure 2 The column usage example 240 illustrates this. In column usage example 240, column A is referenced in the filter predicate A=1 in query QA and the predicates A=2&B=2 in query QB. Therefore, queries QA and QB reference column groups (A) and (A,B). An automatic index can be generated for each group, i.e., an automatic index with (A) as the index key and another automatic index with the group (A,B) as the automatic index key (i.e., a composite key). However, an index with the composite key (A,B) can also be used as an access path for predicates on only A, as in query QA. Therefore, only one automatic index with the composite key (A,B) is added to the candidate indexes.

[0046] Automatic indexing can also be created on functions applied to columns. For example, QC includes the predicate UPPER(name) = "SMITH". Automatic indexing of functions that apply the UPPER function to a column name can be added to the candidate list.

[0047] In step 220, Data Definition Language (“DDL”) commands are formed and executed to create invisible, meta-only auto-indexes. DDL commands include parameters and / or clauses specifying the index keys, that the auto-index is auto-index, that it is invisible, and that it is not materialized. The DBMS's database dictionary is updated accordingly to define the indexes marked as invisible and not materialized.

[0048] In section 225, index statistics are generated. Generally, index statistics are generated by performing an "index statistics scan" of the rows in the table, and for each of those rows, examining the column values ​​in the index key. One particularly important index statistic that can be generated is the number of distinct key values ​​for the index key. From the number of distinct key values, other index statistics can be generated. For example, the number of rows in the table, the number of distinct key values, and the size of the key can be used to estimate the number of data blocks required to store the index.

[0049] According to an embodiment, a single index statistics scan is performed on a table for a set of multiple automatic indexes. Each row in the table is read, and the columns of the keys used for the set of automatic indexes are examined to generate index statistics for each automatic index in the set.

[0050] Outside the context of automated index creation, as described herein, in response to DDL commands that request, for example, the creation of indexes on a table and the materialization of those indexes, index statistics are performed separately for each index creation. Therefore, by performing table scans on multiple automated indexes on a table, rather than multiple index statistics scans, a single index statistics scan reduces the use of computer resources (e.g., processing time, I / O) for generating index statistics for multiple indexes.

[0051] Compilation qualification only

[0052] After the meta-only auto-indexes are defined, a compile-only eligibility process is performed to determine which meta-only auto-indexes should be materialized and tested. During compile-only eligibility, queries in the working set are compiled to determine whether any of the meta-only auto-indexes are selected for use in the execution plan generated by the compilation. Figure 3A The process of obtaining compilation qualification only according to an embodiment of the present invention is described.

[0053] Reference Figure 3A For each query in the working set, the query is compiled to generate an execution plan (305). The compilation of the query can utilize index statistics previously generated for the automatic indexes. The query execution plan is examined to determine which automatic indexes are selected for use in the execution plan (310).

[0054] Each auto-index selected for the threshold number of times is added to the list of eligible compilers (320). The threshold number of times can be once or more. For example, if the threshold number of times is twice, then if the execution plans of two or more queries in the working set select an auto-index during the period of eligible compiler only, that auto-index is added to the list of eligible compilers.

[0055] According to an embodiment, during query compilation, a dynamic sampling declaration can be performed to determine the selectivity of any predicate on one or more columns of the key of the automatic index (315). The result of dynamic predicate sampling is used to determine whether to use the automatic index. When a dynamic sampling declaration is used for the automatic index, the generation of index statistics at 225 may occur first. Alternatively, both index statistics on the automatic index and query sampling declaration can be used during query compilation. Dynamic sampling declarations are described in U.S. Patent No. 7,213,012 Optimizer Dynamic Sampling, filed May 9, 2003, as U.S. Patent Application No. 10 / 435,228 by Hakan Jakobsson et al., the entire contents of which are incorporated herein by reference.

[0056] Make automatic indexing specific

[0057] After compilation qualification is completed, the index that has obtained compilation qualification is materialized so that the automatically qualified index can be tested and executed.

[0058] Figure 3B The process of materializing compile-qualified automatic indexes is described. Similar to the single-scan method used to generate index statistical scans, the single-table scan method is used to generate multiple compile-qualified automatic indexes for the table. (See reference...) Figure 3B One or more tables indexed by the qualified index are determined so that a single index creation scan method can be applied. (350)

[0059] For each table, a single index creation scan is performed to generate index data for all compile-qualified automatic indexes on the table (355). Performing the scan may require extracting the key values ​​needed to form index data for all compile-qualified automatic indexes of the table. Some types of indexes do not actually store the key values ​​in the index data, but rather require checking the key values ​​to determine how to create the index. For example, a bitmap index may require setting bits in the bitmap corresponding to rows containing specific key values.

[0060] In addition, additional index statistics can be generated during a single index creation scan. For example, when a B-tree index is created, information such as the depth of the B-tree, the number of non-leaf nodes, and the number of leaf nodes are generated.

[0061] Outside the context of automated index creation, in response to DDL commands, such as requests to create and materialize indexes on a table, an index creation scan is performed separately for each index creation. Therefore, by performing a single index creation scan on multiple indexes on a table, rather than multiple index creation scans, the single index creation scan described herein reduces the resources (e.g., processing time, I / O) required to materialize multiple indexes on the same table.

[0062] Verification of test execution for automatic indexing that has obtained compilation qualifications

[0063] Based on performance metrics estimates from the execution plan, it was determined that compiling the automatic indexes alone could improve query execution performance. Test executions confirmed that the automatic indexes did indeed improve query execution.

[0064] To determine if there has been an improvement in query execution, performance metrics regarding previous executions of the query are needed for comparison purposes. Such information is obtained from query history 235.

[0065] Figure 4A Describe the process used to test the execution verification. (Refer to...) Figure 4A The members (405) of the test execution work set are identified. These members are queries in the work set for which the execution plan was generated using automatic indexes during the compilation qualification period only. Test execution verification is performed on the queries in the test execution work set.

[0066] For each query in the test execution work set, the query is test executed to generate a performance metric. This performance metric is then compared with performance metrics about the query stored in the query history 450 (415). Generally, the performance metric used for comparison is the performance metric about the query from the most recent execution plan in the query history 450.

[0067] Based on the comparison, determine whether the test execution improves or degrades query execution performance (420). If performance degrades, then block any of the automatically qualified indexes used in the test execution for the query (425). For example, the execution time of the test execution for the query may be greater than the execution time of the query in query history 450, indicating degraded query execution performance.

[0068] Query blocking

[0069] According to an embodiment of the invention, "SQLlockdown" is used to lock the query. SQL lockdown is built on top of an SQL management object. The SQL management object influences how the DBMS compiles the query. The compilation of the query can produce a suboptimal execution plan for that query. For example, compilation can produce a suboptimal execution plan for the query that can use the index when another execution plan without using the index would be better.

[0070] To avoid compiling suboptimal execution plans for queries, an SQL management object is generated for the query, and the DBMS uses this SQL management object to influence how the query is compiled. This can be avoided using suboptimal execution plans. SQL management objects can be generated automatically by monitoring query performance. SQL management objects are the foundational structure of SQL profiles. The automated generation of SQL profiles is described in U.S. Patent No. 7,664,730, Method and system for implementing a SQL profile, filed September 7, 2004, as U.S. Patent Application No. 10 / 936,205 by Mohamed Ziaddin et al., the entire contents of which are incorporated herein by reference.

[0071] A key feature of SQL locking is that it enables automated configuration of the DBMS to improve performance at the query-specific level. If an automatically generated SQL lock degrades the performance of a query, the degradation is limited to that query.

[0072] On the other hand, configuration changes (such as creating indexes) have system-wide effects. Adding an index can degrade the performance of many queries. Therefore, it is important that the methods used to automatically create indexes employ measures to prevent this from happening when using an automatic index on a query degrades its performance. Thus, as explained later, SQL blocking is used to prevent the use of automatic indexes that the automatic index management discovers is degrading the performance of a query.

[0073] Make automatic indexing available

[0074] Once the test execution verification is complete, any auto-indexes that have qualified for compilation and have demonstrated through the test execution verification that the execution of a threshold number of queries has been improved can be used for future executions of the queries. Figure 4B The procedure for making automatic indexing available in this manner is shown.

[0075] Reference Figure 4BFor each auto-index that qualifies for compilation, determine whether the auto-index improves query execution for a threshold number of queries. If not, discard the auto-index (455), thereby removing the auto-index from the DBMS's database dictionary and removing the index data of the auto-index from the database. The threshold can be one or more.

[0076] If it is determined that an automatic index improves query execution for a threshold number of queries, then that automatic index is made visible. The query optimizer may then consider using the automatic index for any future execution of queries other than those in the working set.

[0077] First batch of verifications

[0078] The test execution verifies that the automatic index improves the execution of at least one query in the working set. For queries in the working set that the automatic index does not improve, the test execution prevents the automatic index from degrading the execution of that query. However, the automatic index may degrade the execution of queries that are not in the working set. To address this possibility, the query execution performance is verified for that query when the automatic index was initially selected for the query's execution plan. Before the query execution that verifies the query, the query is referred to here as the unverified query.

[0079] To determine whether an automatic index degrades or improves the query execution performance of an unverified query, first-pass verification is used. Generally, after the initial compilation of a query produces an execution plan using an automatic index one or more times, that automatic index is blocked from being used in subsequent compilations of the query. The execution of the query utilizing the automatic index is evaluated to determine whether the query execution has been improved or degraded. If improved, the DBMS subsequently does not block the use of the automatic index for that query. If degraded, the automatic index is blocked for that query. Therefore, first-pass verification limits query execution degradation due to automatic indexes to one or a few executions of the query, while allowing the automatic index to be used to execute queries when it improves query execution.

[0080] While the first validation is being performed, the DBMS can detect that the query execution is running for much longer than the previous execution of the query. In response, the first validation can stop the query execution, lock the automatic indexes, and then recompile and execute the query without the automatic indexes.

[0081] Shared cursor mechanism

[0082] According to an embodiment of the present invention, the DBMS's shared cursor mechanism is used for initial verification. The shared cursor mechanism uses a shared cursor pool. (See also...) Figure 5 Explain the shared cursor mechanism. Figure 5Depicts the first verifications using the shared cursor mechanism.

[0083] Reference Figure 5 , Figure 5 The shared cursor pool 550 is described. The shared cursor pool stores shared cursors that can be accessed by a database session running the DBMS. Cursors are generated during compilation and include the execution plan of the query. A cursor is a data structure that stores the execution plan, information about the query and the compilation of the execution plan (such as an operator tree), and a list of qualified indexes. A qualified index is an index on a table and on a column referenced by the query that is qualified for the query at compile time. An index is qualified for a query when, for example, it can be used to evaluate predicates on a key, and the index is visible and not locked for use in the query.

[0084] When a query is compiled by the query optimizer, the optimizer looks up the query in the shared cursor pool. If a shared cursor exists for the query, the execution plan stored in the shared execution plan is used for the query. Using the execution plan in this way is referred to here as a "soft parse". Otherwise, the query optimizer performs a "hard parse" that generates the execution plan from a "scratch".

[0085] When a hard parse is performed, the shared cursor used for the query is stored in shared cursor pool 550. Any previously shared cursors used for the query that were previously stored in shared cursor pool 550 are removed from shared cursor pool 550. Removing a shared cursor may require marking the shared cursor as invalid to prevent its use. A hard parse can be performed when the query optimizer determines from the list of eligible indexes in the shared cursor used for the query that a new index exists that is not in the list of eligible indexes.

[0086] However, during the initial verification, when a hard parse is performed in response to the detection of a qualified new automatic index, previous shared cursors in shared cursor pool 550 may not be removed so that the shared cursors can be used for a subsequent execution of the query. Shared cursors using automatic indexes (i.e., shared cursors storing the execution plan using the new automatic index) can be added to shared cursor pool 550, but are marked for verification to prevent them from being used for another execution of the query. If the query performance of the new execution plan is verified to be improved, the new shared cursor is not marked, and the previous shared cursor is removed.

[0087] It should also be noted that when an automatic index is created or rebuilt, the shared cursor for queries that reference the table of that automatic index is not removed from the shared cursor pool. Such a shared cursor remains among the shared cursors available for the corresponding query, including during the initial verification period as shown above. When an index that is not an automatic index is created or rebuilt, the shared cursor for queries that reference the table of that index may be removed from the shared cursor pool.

[0088] First verification based on shared cursor

[0089] Figure 5 A diagram illustrating the first verified data using a shared cursor mechanism is provided. (See reference...) Figure 5 The query detects new qualified automatic indexes. New automatic indexes are detected by checking the shared cursor used for the query in the shared cursor pool 550 and determining that the automatic index is not in the list of qualified indexes in the shared cursor (505).

[0090] The query is hard-parsed to generate an execution plan (510) using the automatic index. A new shared cursor and a new execution plan for the query are stored in a shared cursor pool 550 and marked as validating. The query is executed using the new execution plan and the automatic index. Performance metrics (520) are generated by executing the query.

[0091] While the query is being executed using the new execution plan, the query is recompiled for another execution of the query. During compilation, the unverified state of the new shared cursor is detected. (560) In response, the new shared cursor is ignored, and the query is executed using the previous execution plan from the previous shared cursor. (565)

[0092] After the new execution plan is executed, determine whether the execution performance has deteriorated or improved. Retrieve the performance metrics of the previous execution plan and compare them with the performance metrics of the new execution plan.

[0093] If performance degrades, then the automatic index is locked for the query (530). A new shared cursor is removed from the shared cursor pool (535).

[0094] If performance is improved, the previous shared cursor is removed from the shared cursor pool 550. (540) The new shared cursor is demarked as being validated. Subsequent execution of the query can therefore use the new shared cursor, resulting in improved query execution performance.

[0095] When a query is compiled for the first time using a new automatic index and there is no shared cursor for the query, a new shared cursor is constructed and marked as validating. In this case, it can be determined whether the automatic index was ever used for the query by checking query history 235.

[0096] When another database session compiles the same query, the shared cursor marked as being in validation is not used. A new cursor is constructed without using the new automatic index, which can then be shared by other users executing the same query.

[0097] Managing automatic index usage

[0098] Executing automatic index management involves disabling automatic indexes that are not used and / or are not frequently used, or that provide insufficient or no cost-benefit. Such automatic indexes are referred to here as low-value automatic indexes. Automatic indexes can become low-value because, for example, automatically generated SQL blocking over a period of time can prevent their use. Changes in index statistics can also be a reason. Other factors include: changes in the size of the table data, changes in the distribution of index key columns, changes in the application, and changes in the data model (or schema). Disabling automatic indexes requires making them invisible and / or dematerializing them. Furthermore, automatic indexes can be discarded, thus removing them from the DBMS's database dictionary.

[0099] Disabling automatic indexes can save storage space that would otherwise be occupied by automatically indexed index data. Furthermore, disabling automatic indexes reduces the number of indexes considered when compiling queries, which reduces query compilation time and workload. Additionally, disabling automatic indexes reduces the maintenance costs of modifying tables.

[0100] In this embodiment, the deactivated automatic index remains materialized but is not maintained. However, the automatic index is qualified for automated index creation. In this case, for test executions of queries, the materialized index can be incrementally rebuilt to reduce the cost of materializing the automatic index.

[0101] Figure 6A This describes the activities performed as part of managing automatic indexes. (See reference...) Figure 6A Monitor automatic index usage (605). Automatic index usage is monitored by periodically checking query history 235. Based on the checks, it is determined whether the automatic index has not been used within a threshold time period. In response to this determination, the automatic index is deactivated (615).

[0102] For example, the DBMS can examine execution plans using specific automatic indexes in query history 235. Based on this examination, it determines that the most recently executed execution plan has been executed at least a threshold time interval in the past. In response to this determination, the automatic index is deactivated.

[0103] Disabling automatic indexing that provides insufficient benefits

[0104] Monitor the use of automatic indexes in the following ways: the number of queries for which automatic indexes are used, the number and frequency of queries that benefit from automatic indexes, and the net benefit (the difference in processing time with and without automatic indexes across all queries). Rank the automatic indexes using metrics collected through monitoring (620). Rankings can be based on the number of queries using automatic indexes, the number and frequency of queries that benefit from indexes, and the total net benefit across all queries using automatic indexes. Automatic indexes that have the lowest rank and / or insufficient net benefit across all queries for which automatic indexes are used can be deactivated.

[0105] In this embodiment, the estimated benefits of automatic indexing lead to the maintenance of that automatic index. The cost of maintaining the index has three components: (1) storage space; (2) CPU and I / O time for creating the index; and (3) CPU and I / O costs for maintaining the automatic index. The first two components are one-time costs. The maintenance cost of the index is incurred whenever the table is modified. The cost of the index is measured over a predetermined period (e.g., one week) using the following steps:

[0106] (1) When automatic indexing is enabled, create a snapshot at time T1 of a counter for the number of rows that are inserted, deleted and updated in the record table.

[0107] (2) Create another snapshot of the counter after a predetermined time period T2.

[0108] (3) Calculate maintenance costs using the following formula:

[0109] (INSERT_COUNT(T1)-INSERT_COUNT(T0))*INSERT_COST+

[0110] DELETE_COUNT(T1)-DELETE_COUNT(T0))*DELETE_COST+

[0111] UPDATE_COUNT(T1)-UPDATE_COUNT(T0))*UPDATE_COST)

[0112] INSERT_COST, DELETE_COST, and UPDATE_COST are constants that are determined to represent the cost of maintaining an index for inserting, deleting, or updating rows.

[0113] Timely indexing

[0114] Based on the examination of query history 235, it can be determined that the automatic index is used during regularly occurring time windows (“regular activity windows”) (630). For example, an automatic index may be used for queries that run monthly to generate monthly reports. Based on the evaluation of query history 235, the DBMS can determine that the query is always executed within a window period at the beginning of the month. The window period is a regular activity window used for the automatic index.

[0115] In response to the determination that automatic indexes are used only during regular activity windows, automatic indexes are implemented as Just-In-Time (JIT) automatic indexes (635). To implement JIT automatic indexes, the DBMS generates a "JIT schedule" for activating and deactivating JIT automatic indexes. This schedule activates JIT automatic indexes exactly before its regular activity window and deactivates them exactly after the regular activity window.

[0116] Figure 6B Describes the regular scheduling of activation and deactivation of JII automatic indexing. (See also...) Figure 6B In response to the arrival of the JIT schedule at the beginning of the regular active window defined by JIT auto-indexing, determine to activate JIT auto-indexing (650).

[0117] In response to determining that JIT auto-indexing is active, JIT auto-indexing is activated, which requires making JIT auto-indexing materialized and visible (655). As expected, auto-indexing is used during the active window (660).

[0118] In response to reaching the end of the regular active window, disable JIT auto-indexing (665). In response to disabling JIT auto-indexing, disable JIT auto-indexing (670).

[0119] The benefit of JIT automatic indexing is that when it is disabled, JIT automatic indexes are not maintained, thus reducing the cost of modifying tables. JIT indexes are only maintained when they are activated.

[0120] Database as a Service (DBMS) - Containerized DBMS

[0121] In Database as a Service (DBaaS), consumers use database servers or DBMS running within cloud infrastructure. Advantageously, DbaaS providers manage or control the underlying cloud infrastructure, applications, and servers, including one or more database servers. DbaaS can be very cost-effective, saving consumers significant time and overhead.

[0122] A robust platform for implementing DBaaS is the multi-tenant architecture of the containerized DBMS (CDBMS) described below in this paper. To further enhance the effectiveness of DBaaS, automated index management can be provided as a service on the CDBMS.

[0123] Figure 7A An example of a CDBMS on which embodiments of the present invention can be implemented is described. (See also...) Figure 7A The CDBMS700 includes a database server 702 and a container database 704. Examples of database servers and computer systems on which they may run are described in the “Database System Overview” and “Hardware Overview” sections below, but embodiments are not limited to these platforms.

[0124] Figure 7B A more detailed view of the container database 704 according to an embodiment is depicted. The container database 704 contains multiple databases hosted and managed by the database server 702. The databases include a root database 710 and pluggable databases (PDBs) 720 to 740. The root database 710 is used by the database server 702 to globally manage the container database 700 and store metadata, which defines pluggable databases and / or defines common database objects that can be shared by database sessions running against different pluggable databases belonging to the same container database.

[0125] Each pluggable database includes its own database dictionary. PDB 720 includes database dictionary 721, PDB 730 includes database dictionary 731, and PDB 740 includes database dictionary 741. The database dictionary of a pluggable database may be referred to herein as a pluggable database dictionary. Database objects that are not commonly used database objects as defined by the pluggable database dictionary are referred to herein as pluggable database objects. Pluggable database object 722, defined by database dictionary 721, belongs to PDB 720 and is not shared by other PDBs. Similarly, database dictionary 731 defines pluggable database object 732 belonging to PDB 730, and database dictionary 741 defines pluggable database object 742 belonging to PDB 740.

[0126] One feature facilitated by the root database 710 is the definition of pluggable databases within the container database 700. Similar to pluggable databases, the root database 710 includes a database dictionary 711. The database dictionary 711 contains metadata that defines various aspects required for managing the container database 700 and the pluggable databases contained within it, including metadata defining PDB 720, PDB 730, and PDB 740.

[0127] Resource Management

[0128] Automated index management utilizes computer resources. To ensure that automated index management has sufficient computer resources, or avoids undesirable overhead due to excessive resource consumption, the computer resources used by automated index management are managed by the resource management capabilities of CDBMS 700. Through this capability, CDBMS 700 can manage the resources used by the database processes performing automated index management on any of the PDB720, PDB730, and PDB740. (See U.S. Patent Application No. 15 / 249,979 Efficient Hybrid Parallelism, filed August 29, 2016 by TeckHua Lee et al.) z The resource management mechanism for managing resources in a DBMS is described in Application For In-memory Scans, the entire contents of which are incorporated herein by reference. The resource management mechanism for managing resources for a PDB is described in U.S. Patent Application No. xx / xxx,xxx, filed xx / xx / xx by Ravi Shankar Thammaiah et al., the entire contents of which are incorporated herein by reference.

[0129] Resource management for automated index management can be performed using a "shared allocation method." Under this method, a certain quota of computer resources is allocated to a specific PDB, and a portion of that quota is allocated to the background database process that performs automated index creation on the PDB. For example, within CDBMS 700, a PDB 720 is allocated a quota of 12 CPU cores. The background database process performing automated index creation is allocated a quota of one of these 12 CPU cores.

[0130] In the "Deallocation Method," automated index management is performed on individual PDB replicas of the primary PDB. The automated indexes created by this automated index management are then propagated to the primary PDB. Figure 8 It is a diagram depicting the method of deallocation.

[0131] Reference Figure 8PDB 730 is a snapshot database copy PDB 720, and is therefore referred to herein as a snapshot PDB. Initially, when PDB 730 is created as a snapshot database copy, it occupies very little storage. When changes are made to either snapshot PDB 730 or PDB 720, a delta, including the differences between the snapshot database copy and the base database, is created. Specifically, the snapshot database copy includes pointers to data blocks (or ranges of data blocks) in the base database, and when initially created, the snapshot database copy almost entirely consists of pointers to data blocks. The delta may include data blocks storing changes made to the snapshot database copy or the base database. Thus, after the changes are made, the snapshot database copy includes pointers to data blocks in the base database, as well as at least a portion of the delta. Because the pointers and delta together can include far less data than a fully materialized copy of the base database, significant storage savings are achieved when creating a snapshot database copy. Margaret Susairaj et al.’s U.S. Application No. 14 / 313,926 StorageIntegrated Snapshot Cloning For Database, filed June 24, 2014, describes the creation of snapshot database copies in a CDBMS, the entire contents of which are incorporated herein by reference.

[0132] exist Figure 8 In this context, database processes running on PDB 720 are allocated a quota of 12 CPU cores, while database processes running on snapshot PDB 730 are allocated a quota of 1 core. Figure 8 Other quotas of computer resources, such as memory and block storage, not shown, are allocated.

[0133] Background process 805, running against snapshot PDB 730, uses query history from PDB 720 to perform automated index creation, resulting in automatic indexes being created in snapshot PDB 730. Verification through testing indicates that the automatic indexes for queries have been improved and are being created in background process 805 within PDB 720. Initial verification was performed against the database process running against PDB 720.

[0134] To enable automated index creation to run as a recent and therefore accurate copy of PDB 720, snapshot PDB 730 can actually be refreshed periodically. Snapshot PDB 730 is refreshed by creating a new snapshot database copy of snapshot PDB 730; this step can be performed almost instantaneously because the snapshot database copy does not require materializing a copy of the data in the underlying database.

[0135] Database Overview

[0136] The embodiments of the present invention are used in the context of a DBMS. Therefore, a description of an example DBMS is provided.

[0137] Generally, a server (such as a database server) is a combination of integrated software components and an allocation of computing resources (such as memory, nodes, and processing on those nodes for executing the integrated software components), wherein this combination of software and computing resources is dedicated to providing a specific type of functionality on behalf of clients of the server. A database server manages and facilitates access to a specific database, handling client requests to access the database.

[0138] Users interact with the DBMS database server by submitting commands to the database server, instructing it to perform operations on the data stored in the database. A user can be one or more applications running on the client computer interacting with the database server. Multiple users may also be collectively referred to as users in this document.

[0139] A database consists of data and a database dictionary stored on a persistent storage facility, such as a set of hard drives. A database is defined by its own separate database dictionary. The database dictionary contains metadata that defines the database objects contained within the database. In effect, the database dictionary defines the entire database. Database objects include tables, table columns, and tablespaces. A tablespace is a collection of one or more files used to store data for various types of database objects, such as tables. If the data for a database object is stored in a tablespace, the database dictionary maps the database object to one or more tablespaces that hold that database object's data.

[0140] The database dictionary is referenced by the DBMS to determine how to execute database commands submitted to the DBMS. Database commands can access database objects defined by the dictionary.

[0141] Database commands (such as queries) can take the form of database declarations. For a database server to process a database declaration, it must conform to a database language supported by the database server. A non-limiting example of a database language supported by many database servers is SQL, which includes proprietary forms of SQL supported by database servers such as Oracle (e.g., Oracle Database 11g). SQL Data Definition Language (“DDL”) instructions are given to the database server to create or configure database objects, such as tables, views, or complex types. Data Manipulation Language (“DML”) instructions are given to the DBMS to manage data stored within database structures. For example, SELECT, INSERT, UPDATE, and DELETE are common examples of DML instructions found in some SQL implementations. SQL / XML is a common extension of SQL used when manipulating XML data in object-relational databases.

[0142] A multi-node database management system consists of interconnected nodes that share access to the same database. Typically, nodes are interconnected via a network and share access to shared storage to varying degrees, such as shared access to a set of disk drives and the blocks of data stored thereon. Nodes in a multi-node database system can take the form of a group of computers (e.g., workstations, personal computers) interconnected via a network. Alternatively, nodes can be nodes in the form of a mesh of server blades interconnected with other server blades on a rack.

[0143] In a multi-node database system, each node hosts a database server. A server (such as a database server) is a combination of integrated software components and an allocation of computing resources (such as memory, a node, and processing on that node for executing the integrated software components on a processor), the combination of software and computing resources being dedicated to performing specific functions on behalf of one or more clients.

[0144] Resources from multiple nodes in a multi-node database system can be allocated to software running a specific database server. Each combination of software and resource allocation from the nodes is a server referred to herein as a "server instance" or "instance". A database server may include multiple database instances, some or all of which may be running on separate computers (including separate server blades).

[0145] Query optimizer and execution plan

[0146] Generally, the query compiler compiles the query declaration and generates an internal query representation of that declaration. Typically, the internal query representation is an interconnected data structure that represents the various components and structures of the query declaration. The internal query representation can take the form of a node graph, where each interconnected data structure corresponds to a node and the components of the query declaration it represents. The internal representation is usually generated in memory for the query optimizer to evaluate, manipulate, and transform.

[0147] The term "query" is used in this document to refer to any form of query representation, including queries in the form of database declarations or internal query representations. The query optimizer can receive queries from an entity other than the query parser, where the received query is in the form of an internal query representation.

[0148] The query optimizer generates one or more distinct candidate execution plans for a query. The query optimizer evaluates these candidate execution plans to determine which one should be used to compute the query.

[0149] An execution plan can be represented by a graph of interconnected nodes (referred to herein as operators), each operator corresponding to a step in the execution plan, referred to herein as an execution plan operation. The hierarchical structure of the graph (i.e., a directed tree) represents the order in which execution plan operations are performed and how data flows between each of the execution plan operations. Execution plan operations include, for example, table scans, index scans, hash joins, sort-merge joins, nested loop joins, and filtering.

[0150] Compiling a query requires transforming it. Generally, transforming a query involves rewriting it into another query that produces the same results and can be executed more efficiently—that is, a query for which a potentially more efficient and less costly execution plan can be generated. Examples of query transformations include view merging, subquery denesting, filter predicate shifting and pushdown, common subexpression elimination, outer-to-inner join transformation, materialized view rewriting, star schema transformation, and join predicate pushdown. A query is rewritten by manipulating a deep copy of its representation to form a transformed query representation that represents the transformed query. The transformed query is referred to as the transformed query in this paper; the query whose copy is transformed is referred to as the base query.

[0151] Query compilation can also perform more than one transformation for evaluation. Each transformed query produced is called a candidate transformed query or candidate query. The transformed query that is rewritten to produce another transformed query is referred to herein as the base query of that other transformed query. The query initially received by the query optimizer is called the original query.

[0152] The original query optimized by the optimizer and the alternative transformed queries generated for the query are individually referred to as "candidate queries" and are collectively called the "query search space". One or more candidate execution plans generated for each query in the query search space are collectively called the "plan search space".

[0153] Hardware Overview

[0154] According to one embodiment, the techniques described herein are implemented by one or more dedicated computing devices. The dedicated computing device may be hardwired to execute the techniques, or may include digital electronic devices persistently programmed to execute the techniques, such as one or more application-specific integrated circuits (ASICs) or field-programmable gate arrays (FPGAs), or may include one or more general-purpose hardware processors programmed to execute the techniques according to program instructions in firmware, memory, other storage, or a combination thereof. Such a dedicated computing device may also combine custom hardwired logic, ASICs, or FPGAs with custom programming to implement the techniques. The dedicated computing device may be a desktop computer system, a portable computer system, a handheld device, a networking device, or any other device containing hardwired and / or program logic for implementing the techniques.

[0155] For example, Figure 9 This is a block diagram illustrating a computer system 900 on which embodiments of the present invention can be implemented. The computer system 900 includes a bus 902 or other communication mechanism for transmitting information, and a processor 904 coupled to the bus 902 for processing information. The processor 904 may be, for example, a general-purpose microprocessor.

[0156] Computer system 900 also includes main memory 906, such as random access memory (RAM) or other dynamic storage devices, coupled to bus 902 for storing information and instructions to be executed by processor 904. Main memory 906 may also be used to store temporary variables or other intermediate information during the execution of instructions to be executed by processor 904. When such instructions are stored in a non-transitory storage medium accessible to processor 904, computer system 900 becomes a dedicated machine customized to perform the operations specified in the instructions.

[0157] The computer system 900 further includes a read-only memory (ROM) 908 or other static storage device coupled to a bus 902 for storing information and instructions for the processor 904. A storage device 910 (such as a disk, optical disk, or solid-state drive) is provided and coupled to the bus 902 for storing information and instructions.

[0158] Computer system 900 can be coupled via bus 902 to a display 912, such as a cathode ray tube (CRT), for displaying information to the computer user. Input devices 914 (including alphanumeric keys and other keys) are coupled to bus 902 for transmitting information and command selections to processor 904. Another type of user input device is a cursor control 916, such as a mouse, trackball, or arrow keys, for transmitting directional information and command selections to processor 904 and for controlling cursor movement on display 912. This input device typically has two degrees of freedom on two axes (a first axis (e.g., x) and a second axis (e.g., y)), which allows the device to specify a position in a plane.

[0159] Computer system 900 may implement the techniques described herein using custom hardwired logic, one or more ASICs or FPGAs, firmware, and / or program logic combined with the computer system to make computer system 900 a special-purpose machine or to program computer system 900 as a special-purpose machine. According to one embodiment, the techniques herein are executed by computer system 900 in response to processor 904 executing one or more sequences of one or more instructions contained in main memory 906. Such instructions may be read into main memory 906 from another storage medium, such as storage device 910. Execution of the instruction sequence contained in main memory 906 causes processor 904 to perform the processing steps described herein. In alternative embodiments, hardwired circuitry may be used in place of or in combination with software instructions.

[0160] As used herein, the term "storage medium" refers to any non-transitory medium that stores data and / or instructions that enable a machine to operate in a particular manner. Such storage media can include non-volatile media and / or volatile media. Non-volatile media include, for example, optical discs, magnetic disks, or solid-state drives, such as storage device 910. Volatile media include dynamic memory, such as main memory 906. Common forms of storage media include, for example, floppy disks, flexible disks, hard disks, solid-state drives, magnetic tape, or any other magnetic data storage media, CD-ROMs, any other optical data storage media, any physical media with a perforated pattern, RAM, PROM, and EPROM, FLASH-EPROM, NVRAM, any other memory chip or cartridge.

[0161] Storage media differ from transmission media, but can be used in conjunction with transmission media. Transmission media participate in the transfer of information between storage media. For example, transmission media include coaxial cables, copper wires, and optical fibers comprising conductors, such as bus 902. Transmission media can also take the form of sound waves or light waves, such as those generated during radio wave and infrared data communication.

[0162] Various forms of media can involve delivering one or more sequences of one or more instructions to processor 904 for execution. For example, the instructions may initially be carried on a disk or solid-state drive of a remote computer. The remote computer may load the instructions into its dynamic memory and transmit them over a telephone line using a modem. A modem local to computer system 900 may receive data over the telephone line and convert the data into an infrared signal using an infrared transmitter. An infrared detector may receive the data carried in the infrared signal, and appropriate circuitry may place the data on bus 902. Bus 902 delivers the data to main memory 906, from which processor 904 retrieves and executes the instructions. Instructions received by main memory 906 may optionally be stored on storage device 910 before or after execution by processor 904.

[0163] Computer system 900 also includes a communication interface 918 coupled to bus 902. Communication interface 918 provides bidirectional data communication coupling with network link 920, which is connected to local network 922. For example, communication interface 918 may be an Integrated Services Digital Network (ISDN) card, a cable modem, a satellite modem, or a modem providing data communication connectivity to a corresponding type of telephone line. As another example, communication interface 918 may be a local area network (LAN) card providing data communication connectivity to a compatible LAN. A wireless link may also be implemented. In any such implementation, communication interface 918 transmits and receives electrical, electromagnetic, or optical signals carrying digital data streams representing various types of information.

[0164] Network link 920 typically provides data communication with other data devices via one or more networks. For example, network link 920 may provide connectivity to host computer 924 or data devices operated by Internet Service Provider (ISP) 926 via local network 922. ISP 926 then provides data communication services via a global packet data communication network (now often referred to as the "Internet") 928. Both local network 922 and Internet 928 use electrical, electromagnetic, or optical signals that carry digital data streams. Signals through various networks and signals on network link 920 and through communication interface 918 (which transmit digital data back and forth with computer system 900) are examples of transmission media.

[0165] Computer system 900 can send messages and receive data, including program code, via networks (one or more), network links 920, and communication interfaces 918. In the Internet example, server 930 can send request codes for applications via the Internet 928, ISP 926, local network 922, and communication interface 918.

[0166] The received code can be executed by processor 904 when it is received, and / or stored in storage device 910 or other non-volatile storage for later execution.

[0167] In the foregoing description, embodiments of the invention have been described with reference to many specific details that may vary between different implementations. The description and drawings are therefore to be viewed in an illustrative rather than restrictive sense. The unique and exclusive indication of the scope of the invention, and what the applicant intends to mean by the scope of the invention, is the literal and equivalent scope of the set of claims published in this application, published in the specific form of such claims (including any subsequent corrections).

[0168] Software Overview

[0169] Figure 10 This is a block diagram of a basic software system 1000 that can be used to control the operation of a computer system 900. The software system 1000 and its components (including their connections, relationships, and functions) are intended to be exemplary only and are not intended to limit the implementation of the example embodiments (one or more). Other software systems suitable for implementing the example embodiments (one or more) may have different components, including components with different connections, relationships, and functions.

[0170] The software system 1000 is provided to guide the operation of the computer system 900. The software system 1000, which may be stored in main memory (RAM) 906 and on a fixed storage device (e.g., hard disk or flash memory) 910, includes a kernel or operating system (OS) 1010.

[0171] OS1010 manages low-level aspects of computer operations, including managing process execution, memory allocation, file input and output (I / O), and device I / O. One or more applications, designated 1002A, 1002B, 1002C…1002N, can be “loaded” (e.g., transferred from fixed storage device 910 to main memory 906) for execution by software system 1000. Applications or other software intended for use on computer system 900 can also be stored as a set of downloadable computer-executable instructions, for example, for downloading and installing from an internet location (e.g., a web server, app store, or other online service).

[0172] Software system 1000 includes a graphical user interface (GUI) 1015 for receiving user commands and data graphically (e.g., "click" and "touch gestures"). These inputs can then be acted upon by software system 1000 according to instructions from operating system 1010 and / or applications (one or more) 1002. GUI 1015 is also used to display the results of operations from OS 1010 and applications (one or more) 1002, allowing the user to provide additional input or terminate the session (e.g., log out).

[0173] OS1010 can execute directly on the bare hardware 1020 of computer system 900 (e.g., processor(s) 904). Alternatively, a hypervisor or virtual machine monitor (VMM) 1030 can be inserted between the bare hardware 1020 and OS1010. In this configuration, VMM 1030 acts as a software “buffer” or virtualization layer between OS1010 and bare hardware 1020 of computer system 900.

[0174] VMM 1030 instantiates and runs one or more virtual machine instances (“guests”). Each guest includes a “guest” operating system (such as OS 1010) and one or more applications (such as application(one or more) 1002) designed to run on the guest operating system. VMM 1030 presents the guest operating system to the virtual operating platform and manages the execution of the guest operating system.

[0175] In some cases, VMM 1030 can allow a guest operating system to run as if it were running directly on the bare hardware 1020 of computer system 900. In these cases, the same version of the guest operating system configured to run directly on the bare hardware 1020 can also run on VMM 1030 without modification or reconfiguration. In other words, in some situations, VMM 1030 can provide full hardware and CPU virtualization to the guest operating system.

[0176] In other cases, for efficiency, the guest operating system can be specifically designed or configured to run on the VMM 1030. In these cases, the guest operating system is "aware" that it is running on the virtual machine monitor. In other words, in some situations, the VMM 1030 can provide paravirtualization to the guest operating system.

[0177] Computer system processing includes the allocation of hardware processor time and the allocation of (physical and / or virtual) memory. Memory allocation is used to store instructions executed by the hardware processor, to store data generated by the hardware processor in executing said instructions, and / or to store the hardware processor state (e.g., register contents) between allocations of hardware processor time when the computer system is not running. The computer system operates under the control of an operating system and can also operate under the control of other programs executing on the computer system.

[0178] cloud computing

[0179] The term "cloud computing" is generally used in this article to describe a computing model that enables on-demand access to a shared pool of computing resources, such as computer networks, servers, software applications, and services, and allows for the rapid provisioning and release of resources with minimal management effort or service provider interaction.

[0180] Cloud computing environments (sometimes referred to as cloud environments or the cloud itself) can be implemented in various ways to best suit different requirements. For example, in a public cloud environment, the underlying computing infrastructure is owned by the organization that makes its cloud services available to other organizations or the general public. In contrast, private cloud environments are generally intended for use by a single organization or within a single organization. Community clouds are intended to be shared by several organizations within a community, while hybrid clouds include two or more types of clouds (e.g., private, community, or public) bound together by data and application portability.

[0181] Generally, cloud computing models enable some of the responsibilities that were previously handled by an organization's own IT department to be delivered as service layers within the cloud environment for consumers (either within or outside the organization, depending on the public / private nature of the cloud). Depending on the specific implementation, the precise definition of what each cloud service layer provides, or the components or features within each cloud service layer, can vary, but common examples include: Software as a Service (SaaS), where consumers use software applications running on cloud infrastructure, while the SaaS provider manages or controls the underlying cloud infrastructure and applications; Platform as a Service (PaaS), where consumers can use PaaS-supported software programming languages ​​and development tools to develop, deploy, and otherwise control their own applications, while the PaaS provider manages or controls other aspects of the cloud environment (i.e., everything below the runtime execution environment); Infrastructure as a Service (IaaS), where consumers can deploy and run arbitrary software applications and / or provision processing, storage, networking, and other basic computing resources, while the IaaS provider manages or controls the underlying physical cloud infrastructure (i.e., everything below the operating system layer). DBaaS has been described previously.

Claims

1. A method for automated index management performed by a DBMS, comprising: Queries that select a work set; The columns used in the query to monitor the working set; Based on the column usage, select one or more automatic indexes from a certain set, wherein selecting one or more automatic indexes from the certain set includes each automatic index in a set of one or more potential automatic indexes: Each automatic index is materialized in a state where each automatic index is not used for queries that are not in the working set; For queries from the working set, the corresponding test execution set performs test execution verification of each automatic index, wherein the execution plan generated for the queries in the corresponding test execution set uses each automatic index during the compilation qualification period only; If the test execution verification indicates that the use of each automatic index improves query execution performance for at least a threshold number of queries in the corresponding test execution set, then each automatic index is included in one or more automatic indexes in the set. In response to selecting one or more automatic indexes of a certain set, the one or more automatic indexes of the certain set are made available for executing queries that are not in the working set; After making one or more automatic indexes of a certain set available for executing queries that are not in the working set: Compile a specific query that is not in the working set to generate a first execution plan, the first execution plan using a specific automatic index from one or more automatic indexes of the set; The specific query is compiled to generate a second execution plan, wherein the specific automatic index is prevented from being used on the second execution plan; Monitor query execution performance and determine whether the query execution performance of the first execution plan has deteriorated compared to the query execution performance of the second execution plan; If the query execution performance of the first execution plan deteriorates, then for the specific query, the specific automatic index is blocked, thereby preventing the use of the specific automatic index when executing the specific query in other database sessions.

2. The method according to claim 1, wherein, Selecting one or more automatic indexes for a given set includes: Based on the column usage, create a set of meta-auto indexes, wherein the meta-auto indexes are auto indexes defined in the database dictionary but not yet materialized; Compile at least some of the queries in the working set; and Determine the set of automatic indexes that are selected to qualify for compilation by compiling at least some of the queries in the working set.

3. The method according to claim 2, wherein, Before compiling at least some of the queries in the working set, index statistics are generated for the set of meta-auto-indexed only.

4. The method according to claim 3, wherein, At least two of the set of meta-only auto-indexes index a specific table, wherein generating index statistics involves generating the index statistics by performing a single index statistics scan of the specific table to generate index statistics for the at least two meta-only auto-indexes.

5. The method according to claim 2, wherein, Compiling at least some of the queries in the working set includes performing dynamic predicate sampling to determine the selectivity of at least one predicate on one or more columns of the automatically indexed key.

6. The method of claim 2, wherein the set of one or more potential automatic indexes is the set of automatic indexes that have obtained compilation eligibility, and the test execution verification of executing a specific automatic index from the set of one or more potential automatic indexes includes: Compile the query of the corresponding test execution set; Execute queries for the corresponding test execution set to generate performance metrics for executing the queries for the corresponding test execution set; Specifically, determining whether the test execution verification indicates that the use of the specific automatic index improves query execution performance for at least a threshold number of queries is based on the performance metric.

7. The method of claim 6, further comprising, based on the performance metric, blocking the specific auto-index for queries in the query set of the corresponding test execution set.

8. The method according to claim 7, wherein, Blocking the specific automatic index includes generating an SQL profile that prevents the specific automatic index from being used to generate an execution plan.

9. The method according to claim 6, wherein, The set of automatically qualified indexes indexes indexes a specific table, wherein materializing the set of automatically qualified indexes includes performing a single index creation scan of the specific table to materialize multiple elements in the set of automatically qualified indexes.

10. The method according to any one of claims 2-9, wherein, Determining the set of auto-indexes eligible for compilation includes adding an auto-index to the set of auto-indexes eligible for compilation if, during at least some of the periods in which queries of the working set are compiled, an auto-index from the set of meta-auto-indexes has been selected for execution plans at least a threshold number of times.

11. The method according to any one of claims 2-9, wherein, For each of the set of one or more potential automatic indexes, the queries in the corresponding test execution set for each automatic index include those queries that used each automatic index in the execution plan generated as at least some of the queries that compiled the working set.

12. The method of claim 1, further comprising: Monitor the use of one or more automatic indexes for a given set; as well as Based on the use of one or more automatic indexes of the set, at least one of the one or more automatic indexes of the set is deactivated.

13. A computer program product comprising instructions that, when executed, implement the method according to any one of claims 1-12.

14. An apparatus for automated index management, comprising: processor; as well as A memory storing instructions that, when executed by the processor, implement the method according to any one of claims 1-12.

15. A non-transitory storage medium storing instructions that, when executed, implement the method according to any one of claims 1-12.

Images