Automatic query offloading to the standby database

Abstract

Embodiments allow a primary database system ("primary") to automatically detect queries directed to the primary database that can be offloaded to a standby database system ("standby"), and then automatically offload queries that meet the offload requirements to the standby. The primary identifies queries that meet the offload requirements based on the record of the latest database object within each standby that replicates the primary database. The record of the latest standby object is updated based on the heartbeat message from each standby, and the timestamp of the transaction on the primary is committed. The evaluation of whether the query meets the offload requirements is triggered by one or more trigger conditions, which include: including an offload optimizer hint in the query, an estimated running time of the query, etc. Embodiments also allow offloading queries for specific changed data in the primary database to access uncommitted changes made to specific data in the primary database on the standby database.

Description

[0001] CROSS-REFERENCE TO RELATED APPLICATIONS

[0002] This application is related to U.S. Application No. 15 / 885,281, filed on January 31, 2018, entitled “Method And System For Supporting Data Consistency On An Active Standby Database After DML Redirection To APrimary Database,” the entire contents of which are incorporated herein by reference as if fully set forth herein. Technical Field

[0003] The present invention involves automatic detection of queries initiated on a primary database and offloading the queries to a replica of the database without losing the correctness of the query results. Background Art

[0004] The approaches described in this section are approaches that could be pursued, but are not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.

[0005] In case of data corruption or system failure at the primary database, one or more physical or logical copies of the primary database can be maintained as separate databases called standby databases. Thus, if the primary database fails, a failover to the standby database can be performed. Typically, the primary and standby databases are maintained in separate database systems that are remotely connected. For example, Figure 1 Describes multiple database systems that are communicatively coupled. Figure 1 , primary database system 110 maintains primary database 132 , and standby database system 140 maintains standby database 162 .

[0006] The primary database system and the standby database system can each correspond to any of a number of different types of database systems, such as a clustered database system (as depicted for standby database system 140), a single-server database system (as depicted for primary database system 110), and / or a multi-tenant database system (as depicted in FIG. Figure 1 (not shown). Figure 1 In the example of , primary database system 110 includes database server computing device 120 and persistent storage 130. Database server computing device 120 runs database server instance 122, which is a collection of processing and memory that interacts with primary database 132. Database server instance 122 implements the server-side functionality of primary database system 110.

[0007] The standby database system 140 includes database server computing devices 150A-N and persistent storage 160 (which may or may not be distributed between the persistent storage devices of the database server computing devices 150A-N). The label "N" in "150N" and any other reference numerals herein indicates that any number of elements, items, or sets of items may be present or used in an embodiment, as in "1 to n items". The database server computing devices 150A-N correspond to cluster nodes that each run one of the database server instances 152A-N. Each of the instances 152A-N is a collection of processing and memory that interacts with the standby database 162. The instances 152A-N collectively implement the server-side functionality of the standby database system 140. The standby database server computing devices 150A-N can communicate with each other via an interconnect (not shown).

[0008] In addition to residing on persistent storage 130 and persistent storage 160, such as Figure 1 As depicted in , the primary database 132 and the backup database 162 may each reside in a volatile storage device (such as a volatile memory running on any one of the devices 120 and / or devices 150A-N). In addition, the primary database 132 and the backup database 162 may each be stored on a virtual disk and / or a set of physical disks.

[0009] Maintaining consistency between databases

[0010] Maintaining consistency between the primary and standby databases involves replicating changes to the primary database on the standby database. A standby database that maintains a physical copy of the primary database will replicate the primary database on a block-by-block level. Therefore, the system change number (SCN) in the physical standby database represents the same database state as the corresponding SCN of the primary database being replicated. Figure 2 is a block diagram depicting a method for maintaining consistency between a primary database and a standby database, where the standby database is a physical copy of the primary database.

[0011] refer to Figure 2, transaction(s) 202 implement one or more changes to primary database 132. Primary database system 110 records the one or more changes in change records 206A-N and sends these change records to standby database system 140 for replication. Example change records include redo records or redo information (as described in U.S. Patent Application No. 11 / 818,975 filed on January 29, 2007; U.S. Patent Application No. 12 / 871,805 filed on August 30, 2010; U.S. Patent Application No. 13 / 161,315 filed on June 15, 2011; and U.S. Patent Application No. 14 / 337,179 filed on July 21, 2014), each of which is incorporated herein by reference in its entirety.

[0012] The transaction(s) 202 implement one or more changes to the master database 132 based on one or more instructions processed as a single logical operation. Multiple data manipulation language (DML) operations (such as structured query language commands "INSERT", "UPDATE", and "DELETE") and queries on the data may also be processed as a single transaction. Any changes implemented in the database data by a particular transaction are only visible to the transaction itself before the transaction is committed, and are then preserved (and made generally visible) when the transaction commits. The transaction(s) 202 that fail to commit may undergo a "rollback" operation that restores the previous version of the data.

[0013] When changes are implemented at the master database 132, the current system change number (SCN) of the master database is incremented. The SCN of the master database represents a logical time corresponding to a particular state of the master database. For example, when a particular transaction begins, the current SCN of the master database is "1". When a particular transaction makes a particular change, the current SCN of the master database advances to "2". When the particular transaction commits, the current SCN of the master database advances to "3".

[0014] Execute queries against the standby database

[0015] A user can choose to initiate queries on a primary database system that maintains a primary database or on a standby database system that maintains a standby database that replicates (physically or logically) the primary database. In general, queries issued directly to the primary database system must be fulfilled by the primary database system, although the standby database system has any available bandwidth for query execution. Thus, the computing and query capabilities of the standby database system cannot be automatically used to offload any portion of the workload running on the primary database system.

[0016] A user may initiate a query on the standby database system 140 by connecting to the database server instance 152A and issuing database commands directly to the database server instance. As another example, a user may initiate a query to the standby database system 140 by connecting to the primary database system 110 (which maintains a database link to the standby database system 140) and submitting database commands (which are explicitly directed through the database link) to the standby database system.

[0017] For illustration, the primary database system 110 maintains a database link to the standby database system 140, where the name that uniquely identifies the standby database system 140 in the primary database system 110 is standby_system_140. Creating such a database link automatically establishes a new session on the standby database system 140, where all database commands issued through the database link will run. Thus, a user can issue the following query on the standby database system 140 by submitting the query to the primary database server instance 122, where the Employee@standby_system_140 referenced by the query represents the copy of the Employee table in the standby database 162:

[0018] SELECT*FROM Employee@standby_system_140

[0019] WHERE department_id = 30;

[0020] When the query is submitted to instance 122, the query is sent to standby database system 140 and runs on the copy of the employee table in standby database 162 within the session established for the database link by standby database system 140. Standby database system 140 returns the query results to primary database system 110 via the previously established database link, and primary database system 110 then returns those results to the user who initiated the query.

[0021] Read-only transactions can be executed against a standby database instead of the corresponding primary database, freeing up primary database resources for other tasks. However, because the standby database system replicates data from the primary database, read-write transactions should generally not be initiated in the standby database system. Therefore, the workload of read-write transactions falls on the primary database system.

[0022] In addition, because the standby database replicates the changes that have been implemented on the primary database, the standby database usually lags behind its primary database. In other words, the state of the standby database continues to catch up with the state of the primary database. The lag of the standby database hinders the ability to unload queries initiated in the primary database to the standby database. For example, if the current SCN of the primary database is at 100, the latest readable SCN of the standby database may be at 75, and if the current SCN of the primary database advances to 120, the latest readable SCN of the standby database may advance to 100.

[0023] Advantageously, at the primary database level, queries that may be redirected to the standby database are automatically detected and then the detected queries are automatically offloaded to the standby database system so as to run the offloaded queries as quickly as possible and conserve resources of the primary database system. BRIEF DESCRIPTION OF THE DRAWINGS

[0024] In the attached picture:

[0025] Figure 1 Depicted are example primary and standby database systems upon which embodiments may be implemented.

[0026] Figure 2 is a block diagram depicting a method for maintaining consistency among multiple databases.

[0027] Figure 3 A flow chart depicting a primary database system automatically determining whether to offload a particular query directed to the primary database to a target standby database system, and then automatically redirecting the query to the target standby database system without human intervention.

[0028] Figure 4 Depicts an example request to perform a query.

[0029] Figures 5A-5C Depicted are various states of an example last commit table that lists the last commit time of database objects and also indicates which database objects are up to date within each standby database system.

[0030] Figure 6 Depicted are sessions run by a primary database system and a standby database system, respectively, having one or more identical session parameters.

[0031] Figure 7 is a block diagram of a computer system upon which an embodiment may be implemented.

[0032] Figure 8 A block diagram of a basic software system that can be used to control the operation of a computer system. DETAILED DESCRIPTION

[0033] In the following description, for the purpose of explanation, many specific details are set forth in order to provide a thorough understanding of the present invention. However, it will be apparent that the present invention can be practiced without these specific details. In other examples, well-known structures and devices are shown in block diagram form to avoid unnecessarily obscuring the present invention.

[0034] General Overview

[0035] Embodiments allow a primary database system to automatically detect queries that can be offloaded to a standby database (from both read-only transactions and read-write transactions directed to the primary database) without manual intervention, and then automatically offload the detected queries to a target standby database system. Specifically, the standby database system maintains a standby database that is separate from a primary database maintained by a corresponding primary database system, but the standby database replicates data in the primary database.

[0036] According to one or more embodiments, the primary database system determines whether a given query is offload eligible (i.e., eligible to be offloaded to an available standby database system) based on a most recently committed object that lists the most recently committed time of database objects and also indicates which database objects are most recent within each standby database system. According to embodiments, the most recently committed object can be any stored or in-memory data structure, including database tables, lists, etc.

[0037] According to an embodiment, the primary database system maintains a most recent commit table that records the SCN at which changes to database objects in the primary database were committed. According to an embodiment, for each database object, the most recent commit table also includes a flag set indicating whether the copy of the corresponding database object on each of the one or more standby database systems includes the most recent changes made to the database object (i.e., the standby database object is "up to date"). Utilizing such a most recent commit table generally allows the primary database system to offload queries that meet the offload requirement in less time than the time required to wait for the current SCN of the target standby database system to catch up with the query SCN of the query.

[0038] According to an embodiment, the primary database evaluates whether a query meets the offload requirements based on detecting one or more trigger conditions that automatically trigger the evaluation. Example trigger conditions include detecting the presence of an offload optimizer hint in the query, determining that the expected run time of the query is above a threshold, and determining that the query is read from a read-mostly database object. In this embodiment, the overhead of evaluating whether the query meets the offload requirements is not incurred for each query. The trigger conditions are configured so that the query for which the trigger condition is detected is more likely to be executed efficiently in the standby database system than in the primary database system.

[0039] The ability of the primary database system to automatically determine which queries meet the requirements for being offloaded to the standby database system eliminates the need for an administrator to specifically determine which queries will be most efficiently directed to the standby database system rather than to the primary database system. Such determinations are prone to error, which can result in inefficient utilization of primary and standby database system resources. In contrast, according to an embodiment, the primary database system is able to automatically identify queries that meet the offload requirements for offloading to the standby database system based, at least in part, on information related to the current state of one or more standby database systems and the current state of the primary database system. Thus, automatic offloading of particular queries optimally utilizes the processing capabilities of the primary and standby database systems based on the current states of the primary and standby database systems.

[0040] After determining that at least one standby database system is an offload candidate for a particular query for an unmodified database object based on the most recent commit table, the primary database system automatically offloads the query to a target standby database system (i.e., one of the at least one offload candidate standby systems) based on the at least one standby database system being up-to-date with respect to the database objects involved in the particular query. The target standby database system runs the query on its standby database and returns the query results to the primary database system. In turn, the primary database system returns the results to the user. In this manner, executing the query at the standby database system is transparent to the user.

[0041] According to one or more embodiments, the primary database system 110 determines whether to offload a query in a read-write transaction that reads from data that has been changed by a previous DML statement in the read-write transaction. In this case, any standby database system that replicates the primary database is an offload candidate. An embodiment allows queries running on a target standby database system to access changes made to changed data in the primary database. Specifically, an embodiment enables session parameters of a session running an offload query on a standby database system to have the same values ​​as corresponding session parameters of a session running a read-write transaction on the primary database system. These common session parameter values ​​allow offloaded queries to access changes made by uncommitted read-write transactions that have been propagated to the standby database.

[0042] Thus, the evaluation of query offload eligibility is not limited to queries that read from unmodified database objects. Resource-intensive queries against database objects in the primary database that have uncommitted changes can be offloaded to the standby database system and still return the same results as if the query had been run on the primary database. The ability to offload queries that read from database objects that have changed in the primary database can more evenly utilize the available standby database system processing power, thereby increasing the efficiency of the primary and standby systems as a whole. In addition, each offloaded query is completed faster on an individual basis than if the query had been run on the overloaded primary database system as originally directed.

[0043] According to an embodiment, if only one standby database system is determined to be an offload candidate for a given query, then that one standby database system is automatically identified as the target standby database system. However, if the primary database system determines that multiple standby databases are offload candidates for receiving offload for the given query, then the primary database system identifies the target standby database system to which to offload the query among the multiple offload candidate databases.

[0044] According to an embodiment, the primary database system identifies a target standby database system from a plurality of offload candidate standby database systems based on information about the plurality of standby database systems, such as a current SCN of each system, performance statistics of each system, and / or the contents of one or more caches of each system. For example, the primary database system selects a target standby database system for a given query based on a target system that is estimated to run the query in the shortest amount of time or that is estimated to terminate running the query fastest among the plurality of offload candidates. Because the primary database system automatically selects a target standby system for a given query based on the statistics of the offload candidates, the primary database system efficiently preserves a workload balance between the plurality of standby database systems and ensures that a given query is run as efficiently and quickly as possible.

[0045] Determines whether a query is evaluated for eligibility for uninstallation

[0046] Figure 3 A flowchart 300 is depicted for a primary database system to automatically determine whether to offload a specific query directed to the primary database to a target standby database system, and then automatically redirect the query to the target standby database system without manual intervention. Specifically, at step 302 of the flowchart 300, the first database system maintains a primary database including a data set. For example, Figure 1 The master database system 110 maintains a master database 132 that includes a particular data set, such as an employee table that stores information about employees of a particular company in rows and columns.

[0047] At step 304, a query for a data set in a master database is received at the first database system. For example, Figure 4 Depicts an example request to perform a query. Figure 4 4, an application initiates a connection to a database server instance 122 (of the primary database system 110) that maintains the primary database 132. The application submits a request 400 to the database server instance 122 via the connection to run a query 402 that reads from the employee table in the primary database 132. The query 402 corresponds to a query timestamp 404 when the query 402 is to be executed. For example, the query timestamp 404 is the SCN of the primary database system 110, which represents a particular state of the data in the primary database 132 on which the query is to be executed. In addition, the query 402 involves (i.e., runs on) one or more database objects within the primary database 132, each of which has been updated within the standby database 162 to a certain SCN of the primary database 132. Depending on the speed at which change records are applied to the standby database, the SCN to which the objects involved in the query within the standby database 162 are updated generally runs after the query SCN 404 of the query 402.

[0048] At step 306, the first database system automatically determines whether to automatically offload the query to a second database system that maintains a standby database that replicates the primary database. According to an embodiment, the primary database system 110 evaluates the offload eligibility of each query, i.e., queries that meet the offload requirements are determined to be ready to be immediately offloaded to an available standby database system.

[0049] According to another embodiment, when the primary database system 110 detects one or more trigger conditions for a received query, a determination of whether the query meets the offload requirement is triggered. Example trigger conditions include detecting the presence of an offload optimizer hint in the query, determining that the expected run time of the query is above a threshold, and determining that the query reads from a read-dominant database object.

[0050] Trigger condition: Uninstall optimizer prompt

[0051] According to an embodiment, the evaluation of offload eligibility of a query is triggered at least in part by an offload optimizer hint included in query 402. To illustrate, the following is an example of query 402 that includes an offload optimizer hint:

[0052] SELECT / *+REDIRECT_TO_STANDBY* / *

[0053] FROM Employee

[0054] WHERE department_id = 30;

[0055] According to one or more embodiments, including an offload optimizer hint in a query triggers primary database system 110 to evaluate whether to offload the query to a standby database, as described in further detail below.

[0056] Trigger condition: query execution time in the primary database

[0057] According to an embodiment, in response to determining that the estimated runtime of a query within primary database 132 exceeds a predetermined query runtime threshold, primary database system 110 is triggered to evaluate whether to offload the query to a standby database.

[0058] For example, the query optimizer of the primary database system 110 estimates the running time of the query 402 based on one or more optimizer statistics of the primary database system 110, which statistics include one or more of the following: the CPU level of the computing device 120, the amount of volatile memory available at the computing device 120, the latency of accessing the persistent storage device 130 storing the primary database 132, the complexity of the query, information about the cache contents of the primary database system, the size of the target database object of the query in the primary database, and the like.

[0059] According to an embodiment, the predetermined query runtime threshold is a duration fixed by an administrator (ie, a tunable parameter). According to another embodiment, the predetermined query runtime threshold is a duration automatically identified by primary database system 110 based on performance statistics of primary database system 110 .

[0060] Trigger condition: Read-oriented objects

[0061] According to an embodiment, in response to determining that a query involves a read-primarily object, the primary database system 110 is triggered to evaluate whether to offload the query to a standby database. A read-primarily object is a database object for which, among all commands issued to the object, reads far outnumber writes to it, e.g., by more than a threshold percentage of read commands. For example, the primary database system 110 determines that an object involved in a particular query is a read-primarily object based on optimizer statistics for the particular query. Additionally, for purposes of the embodiment, read-only database objects are considered to be read-primarily objects. Using the fact that queries are run on read-primarily objects efficiently utilizes system resources because, given that read-primarily objects are rarely modified, the read-primarily objects are likely to be up-to-date in one or more standby databases.

[0062] Returning to the description of the embodiment of step 306 of flowchart 300 explaining the trigger condition, when query 402 is received at primary database system 110, system 110 determines from the optimizer statistics of the query that the expected run time of the query exceeds the threshold amount of time. In response to identifying the trigger condition, primary database system evaluates whether query 402 meets the offload requirements.

[0063] Determine whether to offload queries based on the most recently submitted table of master database objects

[0064] Once primary database system 110 identifies one or more triggering conditions for query 402, primary database system 110 determines whether to offload the query to a standby database system, such as standby database system 140, or in other words, system 110 determines whether query 402 meets the offload requirements. Specifically, returning to the discussion of step 306 of flowchart 300, according to one or more embodiments, primary database system 110 automatically determines whether to offload query 402 (issued by primary database 132) to standby database system 140 based at least in part on a recent submission table maintained by primary database system 110.

[0065] This article describes the query 402 in the context of Figure 3 300, the query 402 is run only on database objects that have not been modified by any DML statement in the transaction containing the query 402. Other embodiments are described below in which the query 402 is run on one or more database objects that have been modified by one or more previous DML statements in the transaction containing the query 402. In addition, this document describes the standby database system in the context of maintaining a physical copy of the primary database. Figure 3 300. Additionally, although embodiments are described in the context of a recently committed table, recently committed information may be stored in any format within the primary database system 110, and according to one or more embodiments, recently committed information is not limited to a table format.

[0066] Maintaining the most recent submissions table

[0067] According to an embodiment, primary database system 110 maintains a most recently committed table that lists identifiers of database objects in primary database 132 and their most recently committed SCNs within primary database system 110. The most recently committed table also includes information related to the state of various database objects within one or more standby database systems (including standby database system 140) of primary database system 110. According to one or more embodiments, primary database system 110 maintains such most recently committed database objects in a dictionary of primary database 132 and / or in volatile memory of computing device 120.

[0068] To illustrate, Figures 5A-5C Depicted is a most recent commit table 500 that primary database system 110 creates and maintains for the purpose of determining whether to offload a given query, according to one or more embodiments. Most recent commit table 500 includes a flag for each database object listed in table 500 within primary database 132 for each corresponding standby database system that replicates primary database 132. Figure 5A In the example of the most recent commit table 500 , three standby database systems (represented by columns 520 , 530 , and 540 ) replicate the primary database 132 .

[0069] For clarity, the labels of columns 520, 530, and 540 include the current SCN of each corresponding standby database in parentheses. Figure 5A As shown in FIG. 1 , the current SCN of standby database 1 is 210, the current SCN of standby database 2 is 200, and the current SCN of standby database 3 is 170. To explain Figure 5A For example rows in table 500 of , the employee table's most recent committed SCN is 190, and standby databases 1 and 2 (current SCNs are 210 and 200, respectively) are up-to-date with respect to the employee table. Standby database 3, with a current SCN of 170, is not up-to-date with respect to the employee table.

[0070] To maintain the most recent commit table 500, the primary database system 110 records the commit SCN of a transaction in response to a transaction commit within the primary database 132 and for each database object updated or created by the transaction, as depicted in column 510 of the most recent commit table 500. For example, when the most recent commit table 500 is in Figure 5A In the state before the state depicted in FIG. 1 , a specific transaction that updates the employee table and the SSN table in the master database 132 is committed at SCN 190. In response to the commit of the specific transaction, such as Figure 5A As shown in column 510 of the most recent commit table 500 depicted in , the primary database system 110 updates the commit SCN of the employee table and the SSN table to the commit SCN of the transaction (ie, 190).

[0071] Each standby database system of the primary database system 110 sends a heartbeat message to the primary database system 110. A heartbeat message is an automatically generated message that is periodically sent from a standby database that maintains a copy of the primary database to the primary database system, and includes statistical information indicating the current state of the standby system. The heartbeat message includes the current SCN of the standby database system that originated the message.

[0072] After receiving a heartbeat message from a given standby database system, the primary database system 110 updates the most recent commit table 500 based on the current SCN of the given standby database system. Specifically, the primary database system 110 sets any flags associated with database objects having a commit SCN for the given standby database system that is at most the current SCN of the given standby database system. In this manner, the most recent commit table 500 tracks the database objects that are most recent relative to the corresponding database objects in the primary database 132 within each standby database that replicates the primary database 132.

[0073] To illustrate, when a recent submission of Form 500 such as Figure 5A As depicted in FIG, the transaction that updates the employee table and the SSN table commits at SCN 220 within the primary database 132. In response to the transaction committing, the primary database system 110 updates the most recent commit table 500 to reflect the most recent commit time (SCN 220) for the employee table and the SSN table, as shown in FIG. Figure 5B In addition, primary database system 110 clears all flags for the employee table and SSN table within table 500 to reflect that no standby database is up to date with respect to the changes made by the transaction (because none of the standby databases have been updated to SCN 220).

[0074] To further illustrate, when a recent Form 500 submission is Figure 5B As shown in FIG. 1 , the primary database system 110 receives a heartbeat message from the standby database system 3, which indicates that the current SCN of the standby database 3 is 220. In response to receiving the message, Figure 5C As depicted in FIG. 5 , the primary database system 110 updates the most recent commit table 500 to update the flags in column 540 for standby database 3 to set all flags for database objects that are less than or equal to the current SCN of standby database 3. Because all of the depicted database objects, except for the Times table, have a commit SCN of 220 or less, the primary database system 110 sets all flags in column 540 except for the unset flag for the Times table. In this way, the most recent commit table 500 tracks the latest status of each standby database on an object-by-object level.

[0075] Use the most recent submission table to determine whether to offload queries

[0076] The master database system 110 is based at least in part on Figure 5A 402 to automatically determine whether to offload the query 402 (issued by the primary database 132) to the standby database system 140. Specifically, when the most recently submitted table 500 is in Figure 5A, primary database system 110 receives query 402 at SCN 250 that reads from the Employees table and the Jobs table in primary database 132 .

[0077] In response to receiving query 402, primary database system 110 looks for at least one trigger condition for query 402 to trigger a determination of whether the query qualifies for offload. According to this example, query 402 includes an offload optimizer hint, and thus, primary database system 110 proceeds to determine whether query 402 qualifies for offload by determining whether any standby database system that replicates primary database 132 is up-to-date with respect to the database objects of query 402. Figure 5A , the most recently committed table 500 indicates that only standby database 1 is up to date for both the employee table and the work table. Therefore, primary database system 110 determines that query 402 qualifies for offloading to standby database 1 (e.g., maintained by standby database system 140), where standby database 1 is the “target” standby database for query 402.

[0078] As another example, a particular query for which offload eligibility analysis has been triggered operates only on a table that is a read-dominant table in primary database 132 and is not up-to-date in any standby database that replicates primary database 132. According to an embodiment, because there is no standby database that is up-to-date for all database objects involved in the particular query, primary database system 110 automatically runs the query on primary database 132 without offloading the query to a standby database system.

[0079] Deferred query offloading

[0080] According to another embodiment, when primary database system 110 determines that there is no standby database that is up-to-date with respect to all database objects involved in a particular query that meets the offload requirement, primary database system 110 determines whether to delay offloading the particular query until at least one standby system becomes up-to-date with respect to all database objects involved in the particular query based on detecting one or more conditions indicating a guaranteed delay.

[0081] For example, a condition indicating a guaranteed delay includes primary database system 110 detecting that an expected run time for a query exceeds a threshold amount of run time. If a given query that qualifies for offload would run for a long time on primary database 132 in the absence of an up-to-date standby, then performing a delayed query offload is advantageous to ensure that primary database system 110 is not required to dedicate a significant amount of resource bandwidth to a single query. In this example, all standby database systems that replicate primary database 132 are considered offload candidates for a given query.

[0082] As another example of indicating a condition that warrants delayed offloading of a given offload-eligible query, primary database system 110 determines that at least one standby database system is within a threshold amount of lag time of the latest of all database objects involved in a given offload-eligible query. For illustration, the threshold amount of lag time is 10 SCNs, and the last heartbeat message for a given standby database system indicates that the current SCN of the standby database system is within 10 SCNs of the most recently committed SCN of the most recently committed database object involved in the query (as indicated in most recently committed table 500). In this case, any standby database system within the threshold amount of lag time is considered an offload candidate for the given query.

[0083] According to an embodiment, after determining to delay a particular query, the primary database system 110 registers the delayed query in a delayed query list and also makes a copy of the most recently committed table 500 using the most recently committed SCN of the objects involved in the delayed query starting from the time the query was received. The copy of the most recently committed table includes a corresponding column for each standby database system that is a candidate for offloading the particular delayed query. As described above in conjunction with the primary copy of the most recently committed table 500, all copies of the most recently committed table associated with the delayed query are updated based on information in the heartbeat messages from the standby database systems included in the respective copies.

[0084] The primary database system 110 determines that a given deferred query in the list meets the offload requirement after detecting that the target standby database system in the associated copy of the most recently committed table has become up-to-date with respect to the SCN listed in the copy of the table (recorded in table 500 at the SCN of the query) for all database objects involved in the query. The query that has undergone the deferred offload operation is offloaded to the target standby database system after the target standby database becomes up-to-date with respect to all database objects involved in the deferred query. Delaying the query in a transaction that also includes one or more statements following the query also delays subsequent statements until the query is completed.

[0085] Identifying a target standby database system for query offloading from multiple candidate standby systems

[0086] After primary database system 110 determines that query 402 meets the requirements to be automatically offloaded to the standby database system. In the event that multiple standby databases are offload candidates for query 402, primary database system 110 automatically identifies a target standby database system to which primary database system 110 will offload the query among the multiple offload candidates. Specifically, primary database system 110 identifies a target standby database to run the query that meets the offload requirements based on which candidate standby database system has the shortest estimated running time for the query and / or has the earliest estimated termination time for executing the query.

[0087] Many aspects can inform the estimated running time or estimated termination time for a query within a given standby database system. With respect to information related to the contents of the standby database system's cache, when the database system executes a query, if the query results exist in the cache maintained by the standby system, then the system will return the cached results without re-executing the query, which speeds up the execution of the query. Additionally, the presence of indexes in a given standby database system that can be included in an execution plan for a query generated by the system can speed up the execution of queries in that system.

[0088] According to an embodiment, primary database system 110 retrieves an estimated running time and an estimated termination time for a query by submitting the query to candidate standby database systems with a request to evaluate the query without running the query, wherein each candidate standby database system returns the estimated running time and the estimated termination time for the query to system 110.

[0089] To illustrate, primary database system 110 determines that standby database system 140 and another standby database system that maintains a copy of primary database 132 are both offload candidates for a given query. Primary database system 110 selects standby database system 140 as the target standby database system for the given query based on one of the following example scenarios:

[0090] • For a given query, the standby database system 140 has a shorter estimated run time than the second standby database system, and is estimated to complete the given query faster than the second standby database system.

[0091] • Although the second standby database system has a shorter estimated run time for the given query, the standby database system 140 is estimated to complete the given query faster than the second standby database system.

[0092] Although the second standby database system is estimated to complete a given query faster than standby database system 140, standby database system 140 has a shorter estimated run time for a given query than the second standby database system.

[0093] Automatically offload queries to a target standby database system

[0094] At step 308, in response to determining to offload the query to the second database system, the first database system automatically offloads the query by submitting a request to the second database system to run the query on a copy of the data set in the standby database. For example, primary database system 110 determines that query 402 meets the offload requirements and identifies standby database system 140 as the target standby database system for query 402. When standby database system 140 is up-to-date with all database objects involved in query 402, primary database system 110 submits a request modeled after request 400 to standby database system 140 to run query 402 on standby database 162.

[0095] According to an embodiment, primary database system 110 initiates and / or maintains a database link to standby database system 140. Standby database system 140 maintains a session for connecting with primary database system 110. Thus, to submit query 402 to standby database system 140, primary database system 110 submits a request to execute the query through the database link. When standby database system 140 receives the request to execute query 402 through the database link, standby database system 140 runs the query in the session associated with the database link.

[0096] According to an embodiment, primary database system 110 sends a timestamp to standby database system 140 indicating the state of the database on which query 402 is to be run. For example, system 110 sets one or more session parameters within a session maintained in system 140 for connection to primary database system 110 to indicate a flashback state of the database for the most advanced SCN of all most recently committed SCNs of objects involved in query 140. Standby database system 140 runs query 402 on the state of standby database 162 that corresponds to the flashback state SCN in the session parameters of the session running query 402.

[0097] At step 310, the results of the query are received from the second database system. For example, in response to receiving a request to run query 402, standby database system 140 retrieves data that satisfies the query from standby database 162. After completing execution of query 402, standby database system 140 returns the query results to primary database system 110, for example, via a database link. Primary database system 110 receives the results from standby database system 140, and thus, does not run query 402 itself.

[0098] At step 312, the first database system returns the query result received from the standby database to the user as the result of the query. For example, primary database system 110 returns the result of query 402 received from standby database system 140 to the application that initiated request 400 and via the session established for the application on primary database system 110. Therefore, it is transparent to the user that primary database system 110 offloads query 402 to standby database system 140.

[0099] Unload queries that run on at least one database object that contains uncommitted changes

[0100] According to one or more embodiments, primary database system 110 determines to offload a query to standby database system 140, wherein the query is part of a read-write transaction that has made one or more uncommitted changes to a data set in primary database 132 on which the query is executed. For example, primary database system 110 determines to offload the query from the read-write transaction based on detecting at least one of the trigger conditions described in detail above.

[0101] Due to the ACID properties of transactions within a database system, changes made to a data set are not visible in any session other than the one running the transaction until the transaction is committed. Figure 6 As depicted, transaction 620 includes at least one DML statement 622 that changes data in the employee table in master database 132. Transaction 620 also includes query 624 that runs after DML statement 622 and reads from the employee table modified by DML statement 622.

[0102] When transaction 620 makes uncommitted changes to primary database 132, primary database system 110 sends a change record that transfers those changes to standby database system 140, which has an associated SCN. Standby database system 140 promptly applies the uncommitted changes to standby database 162 (where the uncommitted changes are committed in standby database 162 only after receiving a change record indicating that the transaction has been committed). The current SCN of standby database 162 reflects these uncommitted changes because these changes have an associated SCN.

[0103] According to one or more embodiments, primary database system 110 determines offload query 624 based on the most recent commit table 500 that tracks the most recent SCN of each standby database system. Specifically, (as with queries that are run only on database objects that have no uncommitted changes) system 110 determines whether query 624 meets the offload requirement based on whether the database objects involved in query 624 are up-to-date. For each database object involved in query 624 that has not been modified by a previous DML statement, system 110 determines whether the object is up-to-date based on table 500 as described in detail above.

[0104] According to an embodiment, for each database object involved in the query 624 that has been modified by a previous DML statement, the primary database system 110 determines whether the corresponding modified object is up-to-date in the given standby database based on whether the standby database has at least reached the SCN of the latest DML statement in the transaction 620 that modified the corresponding object. According to another embodiment, the system 110 determines whether the object involved in the query 624 that has been modified by a previous DML statement is up-to-date in the given standby database by comparing the current SCN of the standby database with the SCN of the latest DML statement in the transaction 620 that made any changes to the object involved in the query 624.

[0105] Continuing with the above example, primary database system 110 selects standby database system 140 as the target standby database system for query 624 based on the standby database system 140 being up-to-date with respect to the database objects involved in query 624. To ensure that the results of query 624 run on standby database 162 are the same as the results that would be retrieved if query 624 were run on primary database 132, query 624 run on standby database system 140 should have access to any uncommitted changes made by transaction 620 prior to query 624.

[0106] On the primary database system 110, the transaction 620 runs within a session 610, which has at least a set of session parameters 612 including both transaction identification information and session information. Specifically, the session parameters 612 include one or more of the following: an identifier of the transaction 620, an undo block address of the transaction 620, and an SCN at which the transaction 620 is initiated within the primary database system 110, etc.

[0107] Before offloading query 624 to standby database system 140, primary database system 110 establishes a new session 630 on standby database system 140. According to an embodiment, because standby database system 140 maintains a physical copy of primary database 132, primary database system 110 establishes session 630 using the user information and credentials for session 610 that have been propagated to standby database system 140 without explicitly establishing a database link.

[0108] Because session 630 is established by primary database system 110 rather than by the application, primary database system 110 can set session parameters for session 630 as needed. Thus, primary database system 110 populates new session 630 on standby database system 140 using the same user credentials and information as used for session 610 and the same values ​​for session parameters 612 including transaction identification information. For example, primary database system 110 communicates with standby database system 140 via a previously established connection and using RPC to set the values ​​of session parameters 612 for session 630 to the values ​​of corresponding session parameters for session 610.

[0109] Because the target standby database system 140 is up to date with respect to query 624, all uncommitted changes made by transaction 620 (such as changes made by DML 622 prior to the query SCN) have already been applied to standby database 162. Because session 630 running query 624 on standby database system 140 has the same value for session parameter 612 as session 610 running transaction 620 on primary database system 110, query 624 can access the same uncommitted changes in standby database 162 as if the query were actually running on primary database 132. Specifically, because session 630 has the same value for session parameter 612 as session 610, query 624 running within session 630 has the same enabled rules and the same privileges as transaction 620 running in session 610.

[0110] In this way, session 630 on standby database system 140 becomes a proxy session for session 610, assuming that any database command running within session 630 can access the same uncommitted changes that the command would have access to if it were running in session 610.

[0111] Offloading queries to a logical standby database

[0112] Embodiments are described above in the context of standby database 162 being a physical copy of primary database 132, where standby database system 140 replicates primary database 132 by applying change records received from primary database system 110 to standby database 162, which record changes made to data in primary database 132. However, according to one or more embodiments, standby database 162 is a logical copy of primary database 132, where standby database system 140 replicates primary database 132 by executing the same database commands on standby database 162 as primary database system 110 executes on primary database 132. A logical standby database system maintains a unique SCN, which prevents primary database system 110 from comparing its own SCN with the SCN of the logical standby database to determine whether and when to offload a given query.

[0113] According to an embodiment, the primary database system 110 determines whether to offload a query that is part of a read-only transaction to the logical standby database system based on the versions of database objects in the primary database and the logical standby database. In such an embodiment, both the primary database system 110 and the logical standby database system record version identifiers for database objects, where the same version number for database objects across the two systems indicates the same commit state of the object. Therefore, in order to determine whether and when to offload a query to the logical standby database system, the primary database system 110 checks the version of the target object maintained in the logical standby database for the query to determine whether the version of the object in the standby is the expected version for a given query. The primary database system 110 offloads the query to the logical standby database system only if the version of the target object of the query is the same as the version of the object at the query SCN in the primary database 132.

[0114] According to another embodiment, a watermark SCN is determined for each logical standby database that logically replicates primary database 132. The watermark SCN for a given logical standby is the SCN (relative to primary database system 110) at which the logical standby database coincides with primary database 132.

[0115] For example, each time the logical standby database system successfully commits a given transaction, the logical standby database system reports (e.g., via a heartbeat message) an identifier of the transaction to the primary database system 110, which identifies the commit status of the transaction within the logical standby database. The primary database system 110 tracks the SCN of the primary database 132 at which each transaction is committed on the primary database system 110. The primary database system 110 determines that the watermark SCN of the logical standby database is the latest SCN of the primary database 132 at which all transactions committed within the primary database 132 have been committed in the logical standby database.

[0116] According to another example, each logical standby database system maintains a watermark SCN for itself, which the logical standby database system provides to the primary system 110 in a heartbeat message. Specifically, the primary database system 110 sends the SCN at which a given transaction is committed to each logical standby database system. The logical standby database stores a record of the commit SCN of each transaction. The logical standby database periodically sends a heartbeat message to the primary database system 110, which includes the latest SCN indicating the last consecutive transaction committed by the logical standby database system. As a simple example, the logical standby database system has received information that the primary database system 110 has committed each of the following transactions (T1-T12) at the associated SCN. At the time of a given heartbeat message, the logical standby database system has committed T1-T9 and T11. Thus, the given heartbeat message includes SCN "9" as the watermark SCN because that is the latest SCN at which the primary database and the logical standby database have the same state.

[0117] The primary database system 110 updates the most recently committed table 500 using the watermark SCN sent by the logical standby database system in the same manner as above for the physical standby database system. Therefore, queries that operate on unmodified database objects received at the primary database system 110 can be offloaded to the logical standby database system in the same manner as such queries are offloaded to the physical standby database system. However, because the logical standby database system is not associated with a watermark SCN that indicates whether uncommitted changes have been applied to database objects within the logical standby database, queries for database objects that include uncommitted changes are not offloaded to the logical standby database system. Thus, queries offloaded to the logical standby database will produce the same data set as queries executed on the primary database.

[0118] Change records and data blocks

[0119] As described above, change records 206A-N specify one or more changes performed on primary database 132 by transaction(s) 202. Primary database system 110 streams change records 206A-N to one or more standby database systems (including standby database system 140) that physically replicate primary database 132. In some example embodiments, change records 206A-N may include data block addresses 208A-N. A data block is an atomic unit of data that a database server can request to read from and write to relative to a storage device (e.g., in a block-mode disk storage device) storing table data. To illustrate, to retrieve a row from a storage device, the data block containing the row is read into a cache (such as a cache residing in a disk cache). Figure 1A cache in a volatile memory of computing device 120 or one of computing devices 150A-N) and a cached copy of the stored data block is checked to access the row.

[0120] Data blocks are used by the DBMS to store one or more database rows or a portion of a row (including one or more columns of a row). When a row is read from persistent storage, the data block containing the row is copied to a data block buffer in the RAM and / or main memory of the database server. A data block may correspond to a predetermined number of bytes of physical storage space and is referred to as an atom because it is generally the smallest unit of database data that a database server can request from persistent storage. For example, a DBMS may store data in data blocks that each correspond to 2KB of disk space. When a database server looks up a row stored in a data block, it can only read the row from persistent storage by reading in the entire data block.

[0121] Each change record includes a data block address, which may indicate the location of a particular data block in the primary database system 110 and / or the standby database system 140. The location may be the relative location of the particular data block in the primary database where the change occurred. Because the standby database is a copy of the primary database, the location may also be the relative location of the particular data block in the standby database where the change is to occur.

[0122] In some example embodiments described below, the standby database system 140 includes application slaves 210A-N that apply changes indicated in change records 206A-N to corresponding data blocks in the standby database. The standby database system 140 can distribute change records 206A-N between application slaves 210A-N based on data block addresses 208A-N. For example, any change records to be applied in the data block address range of 100 to 500 are distributed to application slave 210A, and any change records to be applied in the data block address range of 501 to 700 are distributed to application slave 210B. In some example embodiments described below, change records 206A-N can be distributed between application slaves 210A-N based on a hash of the data block addresses 208A-N.

[0123] Database Overview

[0124] Embodiments of the present invention are used in the context of a database management system (DBMS). Accordingly, a description of an example DBMS is provided.

[0125] 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 the nodes for executing the integrated software components), where the combination of software and computing resources is dedicated to providing a specific type of functionality on behalf of the server's clients. A database server manages and facilitates access to a specific database, processing client requests to access the database.

[0126] A user interacts with a database server of a DBMS by submitting commands to the database server that cause the database server to perform operations on the data stored in the database. A user may be one or more applications running on a client computer that interacts with the database server. Multiple users may also be collectively referred to as users in this article.

[0127] A database includes a database dictionary and data stored on a persistent storage mechanism such as a set of hard disks. A database is defined by its own separate database dictionary. The database dictionary includes metadata that defines the database objects contained in the database. In effect, the database dictionary defines the entirety of the database. Database objects include tables, table columns, and tablespaces. A tablespace is a set 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 the data for the database object.

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

[0129] Database commands can be in the form of database statements. In order for a database server to process database statements, the database statements must conform to the database language supported by the database server. A non-limiting example of a database language supported by many database servers is SQL, including proprietary forms of SQL supported by database servers such as Oracle (e.g., Oracle database 11g). SQL data definition language ("DDL") instructions are issued to a database server to create or configure database objects, such as tables, views, or complex types. Data manipulation language ("DML") instructions are issued to a DBMS to manage data stored in a database structure. For example, select (SELECT), insert (INSERT), update (UPDATE), and delete (DELETE) are examples of DML instructions common in some SQL implementations. SQL / XML is a common extension of SQL used when manipulating XML data in an object-relational database.

[0130] A single-node database system (such as system 110) includes a single node running a database server instance that accesses and manages a database. A multi-node database management system (such as system 140) consists of interconnected nodes that share access to the same database. Typically, the nodes are interconnected via a network and share, to varying degrees, access to shared storage, such as a set of disk drives and the data blocks stored thereon (in a Figure 1 The nodes in a multi-node database system may be in the form of a group of computers (e.g., workstations, personal computers) interconnected via a network. Alternatively, the nodes may be nodes of a grid consisting of nodes in the form of server blades interconnected with other server blades on a rack.

[0131] Each node in a multi-node database system 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, nodes, and processing on the nodes for executing the integrated software components on processors) dedicated to performing specific functions on behalf of one or more clients.

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

[0133] Query Processing

[0134] A query is an expression, command, or set of commands that, when executed, causes a server to perform one or more operations on a data set. A query may specify (one or more) source data objects, such as (one or more) tables, (one or more) columns, (one or more) views, or (one or more) snapshots, from which (one or more) result sets are to be determined. For example, (one or more) source data objects may appear in the FROM clause of a structured query language ("SQL") query. SQL is a well-known example language for querying database objects. As used herein, the term "query" is used to refer to any form of representing a query, including queries in the form of database statements and any data structure for internal query representation. The term "table" refers to any source object that is referenced or defined by a query and represents a set of rows, such as a database table, view, or inline query block (such as an inline view or subquery).

[0135] Queries may perform operations on data from source data objects on a row-by-row basis as the object(s) are loaded, or on the entire source data object(s) after the object(s) have been loaded. Result sets generated by some operations may be made available to other operations(s), and in this manner, result sets may be filtered or narrowed based on some criteria, and / or joined or combined with other result sets(s) and / or other source data objects(s).

[0136] Generally, a query analyzer receives a query statement and generates an internal query representation of the query statement. Typically, the internal query representation is a set of interconnected data structures that represent various components and structures of the query statement.

[0137] The internal query representation may be in the form of a node graph, with each interconnected data structure corresponding to a node and a component of the represented query statement. The internal representation is typically generated in memory for evaluation, manipulation, and transformation.

[0138] Query Optimization

[0139] As used herein, a query is considered to be "transformed" when it is (a) rewritten from a first expression or representation to a second expression or representation, (b) received in a manner that specifies or indicates a first set of operations (such as a first expression, representation, or execution plan) and executed using a second set of operations (such as operations specified or indicated by a second expression, representation, or execution plan), or (c) received in a manner that specifies or indicates a first set of operations and is planned for execution using a second set of operations.

[0140] Two queries or execution plans are semantically equivalent to one another when they would produce equivalent result sets if executed, even if the two queries or execution plans aggregate the result sets differently. An execution of a query is semantically equivalent to a query or execution plan if the execution of the query produces a result set that is equivalent to the result set that the query or execution plan would produce if executed.

[0141] The query optimizer may optimize a query by transforming the query. Generally, transforming a query involves rewriting the query into another query that produces the same result and can potentially be executed more efficiently, i.e., a potentially more efficient and / or lower cost execution plan may be generated for the query. The query may be rewritten by manipulating any internal representation of the query (including any copy thereof) to form a transformed query or a transformed query representation. Alternatively and / or additionally, the query may be rewritten by generating different but semantically equivalent database statements. According to an embodiment, the query optimizer of the primary database system 110 determines to offload the query to the standby database system 140 based on determining that the execution plan involving offloading the query is estimated to be more efficient than an alternative execution plan that does not involve offloading the query to the standby database system 140 but running the query on the primary database 132.

[0142] Replace or expand

[0143] According to one or more embodiments, one or more of the functions attributed to any process described herein may be performed by any other logical or physical entity according to any one or more embodiments. In an embodiment, each of the techniques and / or functions described herein is performed automatically and may be implemented using one or more computer programs, other software elements, and / or digital logic in any of a general purpose computer or a special purpose computer while performing data retrieval, transformation, and storage operations involving interacting with and transforming the physical state of computer memory.

[0144] Hardware Overview

[0145] According to one embodiment, the technology described herein is implemented by one or more special-purpose computing devices. The special-purpose computing device can be hard-wired to perform the technology, or can include digital electronic devices (such as one or more application-specific integrated circuits (ASICs) or field programmable gate arrays (FPGAs) that are permanently programmed to perform the technology), or can include one or more general-purpose hardware processors that are programmed to perform the technology according to program instructions in firmware, memory, other storage devices, or combinations. Such special-purpose computing devices can also combine custom hard-wired logic, ASICs, or FPGAs with custom programming to implement the technology. The special-purpose computing device can be a desktop computer system, a portable computer system, a handheld device, a networking device, or any other device that combines hard-wiring and / or program logic to implement these technologies.

[0146] For example, Figure 7700 is a block diagram illustrating a computer system 700 upon which embodiments of the present invention may be implemented. Computer system 700 includes a bus 702 or other communication mechanism for communicating information, and a hardware processor 704 coupled to bus 702 for processing information. Hardware processor 704 may be, for example, a general purpose microprocessor.

[0147] The computer system 700 also includes a main memory 706, such as a random access memory (RAM) or other dynamic storage device, coupled to the bus 702 for storing information and instructions to be executed by the processor 704. The main memory 706 may also be used to store temporary variables or other intermediate information during the execution of instructions executed by the processor 704. These instructions, when stored in a non-transitory storage medium accessible to the processor 704, make the computer system 700 a special-purpose machine customized to perform the operations specified in the instructions.

[0148] Computer system 700 also includes a read only memory (ROM) 708 or other static storage device coupled to bus 702 for storing static information and instructions for processor 704. A storage device 710, such as a magnetic disk, optical disk, or solid state drive, is provided and coupled to bus 702 for storing information and instructions.

[0149] The computer system 700 may be coupled to a display 712, such as a cathode ray tube (CRT), via the bus 702 for displaying information to a computer user. An input device 714, including alphanumeric and other keys, is coupled to the bus 702 for communicating information and command selections to the processor 704. Another type of user input device is a cursor control 716, such as a mouse, trackball, or cursor direction keys, for communicating direction information and command selections to the processor 704 and for controlling cursor movement on the display 712. The input device typically has two degrees of freedom in 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.

[0150] Computer system 700 may implement the techniques described herein using custom hardwired logic, one or more ASICs or FPGAs, firmware, and / or program logic that is combined with a computer system to make or program computer system 700 into a special purpose machine. According to one embodiment, computer system 700 performs the techniques described herein in response to processor 704 executing one or more sequences of one or more instructions contained in main memory 706. Such instructions may be read into main memory 706 from another storage medium, such as storage device 710. Execution of the sequences of instructions contained in main memory 706 causes processor 704 to perform the process steps described herein. In alternative embodiments, hardwired circuitry may be used in place of or in combination with software instructions.

[0151] As used herein, the term "storage medium" refers to any non-temporary medium that stores data and / or instructions that cause a machine to operate in a particular manner. Such storage media may include non-volatile media and / or volatile media. Non-volatile media (such as storage device 710) include, for example, optical disks, magnetic disks, or solid-state drives. Volatile media (such as main memory 706) include dynamic memory. Common forms of storage media include, for example, floppy disks, flexible disks, hard disks, solid-state drives, magnetic tapes or any other magnetic data storage media, CD-ROMs, any other optical data storage media, any physical media with hole patterns, RAMs, PROMs and EPROMs, FLASH-EPROMs, NVRAMs, any other memory chips, or cassette tapes.

[0152] Storage media are distinct 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 (including the wires comprising bus 702) include coaxial cables, copper wires, and optical fibers. Transmission media can also take the form of sound or light waves, such as those generated during radio wave and infrared data communications.

[0153] Carrying one or more sequences of one or more instructions to the processor 704 for execution may involve various forms of media. 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 send the instructions over a telephone line using a modem. A modem local to the computer system 700 may receive the data on 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 the bus 702. The bus 702 carries the data to the main memory 706, from which the processor 704 retrieves and executes the instructions. The instructions received by the main memory 706 may optionally be stored on the storage device 710 before or after execution by the processor 704.

[0154] Computer system 700 also includes a communication interface 718 coupled to bus 702. Communication interface 718 provides a two-way data communication coupling to a network link 720, which is connected to a local network 722. For example, communication interface 718 can be an integrated services digital network (ISDN) card, a cable modem, a satellite modem, or a modem that provides a data communication connection with a corresponding type of telephone line. As another example, communication interface 718 can be a local area network (LAN) card to provide a data communication connection with a compatible LAN. Wireless links can also be implemented. In any such implementation, communication interface 718 sends and receives electrical signals, electromagnetic signals, or optical signals that carry digital data streams representing various types of information.

[0155] The network link 720 typically provides data communication to other data devices through one or more networks. For example, the network link 720 can provide a connection to a host computer 724 or to data equipment operated by an Internet Service Provider (ISP) 726 through a local network 722. The ISP 726 in turn provides data communication services through a global packet data communication network (now commonly referred to as the "Internet" 728). Both the local network 722 and the Internet 728 use electrical, electromagnetic, or optical signals that carry digital data streams. The signals through the various networks and the signals on the network link 720 and through the communication interface 718 (which carry digital data to and from the computer system 700) are example forms of transmission media.

[0156] Computer system 700 can send messages and receive data, including program code, through the network(s), network link 720, and communication interface 718. In the Internet example, server 730 can send the requested code for an application program through Internet 728, ISP 726, local network 722, and communication interface 718.

[0157] The received code may be executed by processor 704 as it is received, and / or stored in storage device 710 or other non-volatile storage for later execution.

[0158] Software Overview

[0159] Figure 8 8 is a block diagram of a basic software system 800 that may be used to control the operation of computer system 700. Software system 800 and its components, including their connections, relationships, and functions, are meant to be exemplary only and are not meant to limit implementation of the example embodiment(s). Other software systems suitable for implementing the example embodiment(s) may have different components, including components with different connections, relationships, and functions.

[0160] Software system 800 is provided for directing the operation of computer system 700. Software system 800, which may be stored on system memory (RAM) 706 and fixed storage (eg, hard disk or flash memory) 710, includes a kernel or operating system (OS) 810.

[0161] The OS 810 manages low-level aspects of computer operation, including managing the execution of processes, memory allocation, file input and output (I / O), and device I / O. One or more applications, represented as 802A, 802B, 802C ... 802N, may be "loaded" (e.g., transferred from fixed storage 710 into memory 706) for execution by the system 800. Applications or other software intended for use on the computer system 700 may also be stored as a downloadable set of computer-executable instructions, for example, for downloading and installation from an Internet location (e.g., a Web server, app store, or other online service).

[0162] The software system 800 includes a graphical user interface (GUI) 815 for receiving user commands and data in a graphical (e.g., "click" or "touch gesture") manner. In turn, these inputs can be acted upon by the system 800 according to instructions from the operating system 810 and / or (one or more) applications 802. The GUI 815 also serves to display the results of the operations from the OS 810 and (one or more) applications 802, and the user can then supply additional input or terminate the session (e.g., log off).

[0163] The OS 810 may execute directly on the bare hardware 820 (e.g., the processor(s) 704) of the computer system 700. Alternatively, a hypervisor or virtual machine monitor (VMM) 830 may be inserted between the bare hardware 820 and the OS 810. In this configuration, the VMM 830 acts as a software "buffer" or virtualization layer between the OS 810 and the bare hardware 820 of the computer system 700.

[0164] VMM 830 instantiates and runs one or more virtual machine instances ("guest machines"). Each guest machine includes a "guest" operating system (such as OS 810), and one or more applications (such as (one or more) applications 802) designed to execute on the guest operating system. VMM 830 presents a virtual operating platform to the guest operating system and manages the execution of the guest operating system.

[0165] In some instances, VMM 830 can allow a guest operating system to run as if it were running directly on bare hardware 820 of computer system 700. In these instances, the same version of the guest operating system that is configured to execute directly on bare hardware 820 can also execute on VMM 830 without modification or reconfiguration. In other words, VMM 830 can, in some instances, provide full hardware and CPU virtualization to the guest operating system.

[0166] In other instances, the guest operating system may be specifically designed or configured for efficiency to execute on the VMM 830. In these instances, the guest operating system is "aware" that it is executing on a virtual machine monitor. In other words, the VMM 830 may provide paravirtualization to the guest operating system in some instances.

[0167] A computer system process includes an allocation of hardware processor time, and an allocation of memory (physical and / or virtual): the allocation of memory is used to store instructions executed by the hardware processor, to store data generated by the execution of instructions by the hardware processor, and / or to store hardware processor state (e.g., the contents of registers) between allocations of hardware processor time when the computer system process is not running. A computer system process runs under the control of an operating system, and may run under the control of other programs executing on the computer system.

[0168] cloud computing

[0169] The term "cloud computing" is used generally herein 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 resources to be rapidly provisioned and released with minimal management effort or service provider interaction.

[0170] A cloud computing environment (sometimes referred to as a cloud environment or just a cloud) can be implemented in a variety of different ways to best suit different requirements. For example, in a public cloud environment, the underlying computing infrastructure is owned by an organization that makes its cloud services available to other organizations or the public. In contrast, a private cloud environment is generally used only by or within a single organization. A community cloud is intended to be shared by several organizations within a community; while a hybrid cloud includes two or more types of clouds (e.g., private, community, or public) bound together by data and application portability.

[0171] In general, the cloud computing model enables some of those responsibilities that may have previously been provided by an organization's own information technology department to be delivered instead as a service layer within the cloud environment for use by consumers (either inside or outside the organization, depending on the public / private nature of the cloud). Depending on the specific implementation, the precise definition of the components or features provided by or within each cloud service layer can vary, but common examples include: Software as a Service (SaaS), in which consumers use software applications running on the cloud infrastructure, while the SaaS provider manages or controls the underlying cloud infrastructure and applications. Platform as a Service (PaaS), in which consumers can use software programming languages ​​and development tools supported by the PaaS provider 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 under the runtime execution environment). Infrastructure as a Service (IaaS), in which consumers can deploy and run arbitrary software applications, and / or provide processes, storage, networks, 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). Database as a Service (DBaaS), in which the consumer uses a database server or database management system running on a cloud infrastructure, while the DbaaS provider manages or controls the underlying cloud infrastructure, applications, and servers, including one or more database servers.

[0172] The above basic computer hardware and software and cloud computing environment are presented for the purpose of illustrating the basic underlying computer components that can be used to implement the (one or more) example embodiments. However, the (one or more) example embodiments are not necessarily limited to any particular computing environment or computing device configuration. Instead, according to the present disclosure, the (one or more) example embodiments can be implemented in any type of system architecture or processing environment that a person skilled in the art will understand as being capable of supporting the features and functions of the (one or more) example embodiments presented herein.

[0173] In the foregoing specification, embodiments of the present invention have been described with reference to numerous specific details, which may vary from implementation to implementation. Accordingly, the description and drawings are to be regarded in an illustrative rather than a restrictive sense. The sole and exclusive indicator of the scope of the invention, and what the applicants intend as the scope of the invention, is the literal and equivalent range of the set of claims issuing from this application in the specific form in which such claims are issued, including any subsequent corrections.

Claims

1. A method for automatic query offloading to a standby database, comprising: The first database system maintains a master database including data sets; receiving, at a first database system, a query for a data set in a master database; wherein the query is performed on one or more query database objects in the master database; The first database system automatically determines whether to offload the query to a second database system, the second database system maintaining a standby database replicating the primary database, which includes at least: determining to perform a delayed offload of a query based at least in part on determining that at least one of the one or more query database objects is not up to date; After a delay, determining that the one or more query database objects are up to date; and determining to offload the query to a second database system based on determining that the one or more query database objects are up to date; In response to determining to offload the query to the second database system, the first database system automatically offloads the query by submitting a request to the second database system to run the query on the copy of the data set in the standby database; receiving results of the query from a second database system; and The first database system returns the query result received from the second database system as a result of the query.

2. The method according to claim 1, further comprising: The first database system detects a commit of a transaction on the primary database at a timestamp; wherein the transaction modifies one or more database objects in a primary database; In response to detecting a commit of the transaction on the primary database: recording the timestamp in a most recently committed object as a most recently committed timestamp for each of the one or more database objects; Wherein the first database system automatically determines whether to offload the query to the second database system based at least in part on the most recently submitted object.

3. The method according to claim 2, further comprising: The first database system receives a message from the second database system indicating a current timestamp of the second database system; In response to receiving the message, the first database system automatically sets a flag for the second database system within a most recently committed object for each database object that does not have a flag set for the second database system and is associated with a most recently committed timestamp that is no greater than a current timestamp for the second database system.

4. The method according to claim 3, wherein: Determining that the one or more query database objects are up to date includes: determining whether each flag for the second database system and associated with each of the one or more query database objects is set within the most recently submitted object, The one or more query database objects are determined to be up to date based on: determining that each flag for the second database system and associated with each of the one or more query database objects is set within a most recently submitted object, automatically identifying the query as meeting the offload requirement.

5. The method according to claim 3, wherein: Determining that at least one of the one or more query database objects is not up to date comprises: determining whether each flag for the second database system and associated with each of the one or more query database objects is set within the most recently submitted object, Wherein determining that at least one of the one or more query database objects is not up to date is based on determining that at least one flag for the second database system and associated with at least one of the one or more query database objects is not set within a most recently committed object.

6. The method of claim 1 , wherein determining to perform a lazy offload of the query is based at least in part on determining one or more of: the runtime estimate of the query exceeds a threshold amount of runtime; or The current timestamp of the second database system is within a threshold amount of time from a most recent commit time associated with the one or more query database objects.

7. The method according to claim 3, further comprising: receiving, at the first database system, a second query for a data set in the primary database; wherein said second query is conducted on a second one or more query database objects in the primary database; The first database system automatically determines whether to offload the second query to one or more standby database systems including the second database system based at least in part on the most recently committed object, including performing the following operations for each of the one or more standby database systems: for each of the standby database systems, determining that at least one flag associated with a query database object of the second one or more query database objects is not set within a most recently committed object; In response to determining that each of the one or more standby database systems is associated with at least one unset flag within a most recently committed object, automatically running the second query on the primary database.

8. The method of claim 3, wherein the message indicating a current timestamp of the second database system is a heartbeat message from the second database system.

9. The method according to claim 2, further comprising: In response to detecting a commit of the transaction on the primary database: clearing one or more flags for standby associated with the one or more database objects within a most recently committed object.

10. The method according to claim 1, further comprising: The first database system detects at least one of one or more conditions that triggers an evaluation of the query as meeting an offload requirement, wherein the one or more conditions include: The presence of an unload optimizer hint in the query, the identification of one or more read-primarily objects in the query, and a runtime estimate of the query exceeding a threshold amount of runtime; Wherein, in response to the first database system identifying at least one of the one or more conditions, the first database system automatically determines whether to offload the query.

11. The method according to claim 1, wherein: A standby database is a physical copy of the primary database; The query is part of a transaction initiated on the first database system; The transaction includes one or more data manipulation language (DML) operations configured to make one or more changes to a data set prior to the query; and The method further comprises: executing the one or more DML operations to make the one or more changes to the data set to produce an adjusted data set in the primary database before automatically submitting to the second database system a request to run the query on the copy of the data set in the standby database; wherein the query is configured to be run on the adjusted data set; Therein, before the transaction is committed, a request to run the query on the copy of the data set in the standby database is automatically submitted to the second database system.

12. The method of claim 11, further comprising, before the transaction is committed and after executing the one or more DML operations that make the one or more changes to the data set to produce an adjusted data set in the primary database: The first database system sends one or more change records to the second database system, the change records reflecting the one or more changes to the data set in the primary database; wherein the second database system applies the one or more change records to the copy of the data set in the standby database to produce an adjusted copy of the data set in the standby database; Wherein the result of the query received from the second database system is based on the copy of the adjusted data set in the standby database.

13. The method according to claim 12, further comprising: The first database system automatically establishes a first session on the second database system; Make one or more session parameters of the first session have the values ​​of one or more corresponding session parameters of the second session on the first database system running the transaction; The second database system runs the query within the first session on the copy of the adjusted data set based at least in part on the one or more session parameters of the first session having values ​​of one or more corresponding session parameters of the second session.

14. One or more non-transitory computer readable media storing one or more instruction sequences which, when executed by one or more processors, cause execution of the method of any one of claims 1 to 13.

15. An apparatus for automatic query offloading to a standby database, comprising: one or more processors; as well as A memory coupled to the one or more processors and comprising instructions stored thereon, which, when executed by the one or more processors, cause the method of any one of claims 1 to 13 to be performed.

16. A computer program product comprising instructions which, when executed by a computer, cause the computer to perform the method of any one of claims 1 to 13.

Images