Query tuning in the cloud
Tuning a production database system through the use of a remote mimic. In response to receipt of a query tuning request against a database system, information about that system is obtained and a mimic of the system is set up in a remote system environment. The mimic aims to imitate the database system in all relevant ways with respect to the tuning request. A tuning analysis is then performed on this mimic system such that there is substantially no impact to operations of the original database system. Tuning results are then applied to the original database system. The entire process takes place with little or no human intervention.
1. A computer program product comprising:
a machine readable storage device; and
computer code stored on the machine readable storage device, with the computer code including instructions for causing a processor(s) set to perform operations including the following:
receiving a first query tuning request identifying a first untuned query to be tuned, with the first query being directed toward a first production database operated on a first set of computer hardware, with the first production database including substantive data and catalog data, and with the catalog of the first database including first database schema, first database statistics, system configuration settings, database configuration settings, capture statements from production, data description language (DDL), database object structures, database object relationships, database object states, and historical requests to access database objects,
setting up a first mimic of the first production database on a second set of computer hardware, with the first mimic including at least a portion of the catalog data of the first production database but not the substantive data of the first production database, and
performing query tuning of the first untuned query using the first mimic operating on the second set of computer hardware to obtain a first tuned query, and
outputting the first tuned query;
wherein the setting up the first mimic includes:
determining a plurality of relevant objects with respect to the first untuned query,
selecting a relevant portion of the catalog data of the first production database that relates to the relevant objects of the first untuned query, and
setting up the first mimic to include the relevant portion of the catalog data.
2. The computer program product of claim 1 wherein the computer code further includes instructions for causing the processor(s) set to perform the following operation:
running the first tuned query on the first production databased to obtain first tuned query results based on the substantive data of the first production database.
3. The computer program product of claim 1 wherein the second set of computer hardware includes a web server.
4. The computer program product of claim 1 wherein the performance of query tuning changes catalog data of the first mimic, but does not change catalog data of the first production database.
5. The computer program product of claim 1 wherein the performance of query tuning includes:
creating a plurality of virtual indexes to add to a list of indexes in catalog data of the first mimic;
determining that use of the plurality of virtual indexes would provide better performance than indexes existing in the first production database; and
responsive to the determination that the plurality of virtual indexes would provide better performance, including the plurality of virtual indexes in the first tuned query.
6. A computer system comprising:
a processor(s) set;
a machine readable storage device; and
computer code stored on the machine readable storage device, with the computer code including instructions for causing the processor(s) set to perform operations including the following:
receiving a first query tuning request identifying a first untuned query to be tuned, with the first query being directed toward a first production database operated on a first set of computer hardware, with the first production database including substantive data and catalog data, and with the catalog of the first database including first database schema, first database statistics, system configuration settings, database configuration settings, capture statements from production, data description language (DDL), database object structures, database object relationships, database object states, and historical requests to access database objects,
setting up a first mimic of the first production database on a second set of computer hardware, with the first mimic including at least a portion of the catalog data of the first production database but not the substantive data of the first production database, and
performing query tuning of the first untuned query using the first mimic operating on the second set of computer hardware to obtain a first tuned query, and
outputting the first tuned query;
wherein the setting up the first mimic includes:
determining a plurality of relevant objects with respect to the first untuned query,
selecting a relevant portion of the catalog data of the first production database that relates to the relevant objects of the first untuned query, and
setting up the first mimic to include the relevant portion of the catalog data.
7. The computer system of claim 6 wherein the computer code further includes instructions for causing the processor(s) set to perform the following operation:
running the first tuned query on the first production databased to obtain first tuned query results based on the substantive data of the first production database.
8. The computer system of claim 6 wherein the second set of computer hardware includes a web server.
9. The computer system of claim 6 wherein the performance of query tuning changes catalog data of the first mimic, but does not change catalog data of the first production database.
10. The computer system of claim 6 wherein the performance of query tuning includes:
creating a plurality of virtual indexes to add to a list of indexes in catalog data of the first mimic;
determining that use of the plurality of virtual indexes would provide better performance than indexes existing in the first production database; and
responsive to the determination that the plurality of virtual indexes would provide better performance, including the plurality of virtual indexes in the first tuned query.
11. A computer program product comprising:
a machine readable storage device; and
computer code stored on the machine readable storage device, with the computer code including instructions for causing a processor(s) set to perform operations including the following:
receiving a first query tuning request identifying a first untuned query to be tuned, with the first query being directed toward a first production database operated on a first set of computer hardware, with the first production database including substantive data and metadata, and with the metadata of the first database including logical organization of a plurality of tables of the first production database, physical organization of the plurality of tables, table size of each of the plurality of tables, identities of table columns of each table of the plurality of tables, a plurality of currently compiled indexes for the plurality of tables, query usage statistics and DBMS (database management system) configuration settings, operating system used to operate the first production database and hardware resources included in the first set of computer hardware;
setting up a first mimic of the first production database on a second set of computer hardware, with the first mimic including at least a portion of the metadata of the first production database but not the substantive data of the first production database; and
performing query tuning of the first untuned query using the first mimic operating on the second set of computer hardware to obtain tuning information;
wherein the setting up the first mimic includes:
determining a plurality of relevant objects with respect to the first untuned query,
selecting a relevant portion of metadata of the first production database from catalog data of the first production database that relates to the relevant objects of the first untuned query, and
setting up the first mimic to include the relevant portion of the catalog data.
12. The computer program product of claim 11 wherein the computer code further includes instructions for causing the processor(s) set to perform the following operations:
tuning the first query based, at least in part, on the tuning information to obtain a first tuned query.
13. The computer program product of claim 12 wherein the computer code further includes instructions for causing the processor(s) set to perform the following operations:
running the first tuned query on the first production databased to obtain first tuned query results based on the substantive data of the first production database.
14. The computer program product of claim 11 wherein the performance of query tuning changes metadata of the first mimic, but does not change metadata of the first production database.
15. The computer program product of claim 11 wherein the tuning information includes information indicative of a first additional index to be added to the first production database.
16. The computer program product of claim 11 further comprising the processor(s) set wherein the computer program product is in the form of a computer system.