# US 2020/0004860 A1 · Database Performance Analysis Based on a Random Archive

> Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt

- Number: US 2020/0004860 A1
- Document ID: US20200004860A1
- Status: Published application
- Kind: A1
- Application number: 16/024,868
- Filed: 2018-07-01
- Published: 2020-01-02
- Earliest claimed: 2018-07-01
- Assignees: Oracle International Corporation
- Applicants: Oracle International Corporation
- Inventors: Arvind Kumar Maheshwari; Uri Shaft; Karl Dias; Vishwanath Karra; Stephen Wexler; Anil Kumar Kothuri
- Web page: https://patentgrep.com/patent/US20200004860A1

## CPC

- G06F 16/24542: {Plan optimisation}
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)

## Abstract

Techniques for analyzing an execution of a query statement based on a random archive are disclosed. A plurality of query statements that are executed during a particular time period are identified. A random sampling function is executed to randomly select a set of query statements from the plurality of query statements. Execution plans and/or performance metrics associated with each execution of the randomly-selected query statements are stored into a random archive. Responsive to determining that a performance metric for a current execution of a particular query statement does not satisfy a performance criteria, information associated with the particular query statement from the random archive is analyzed. A model plan characteristic associated with an execution of the particular query statement stored in the random archive is determined. An execution plan associated with the model plan characteristic is determined for another execution of the particular query statement.

## Claims

### Claim 1

A non-transitory computer readable medium comprising instructions which, when executed by one or more hardware processors, cause performance of operations comprising:
identifying a plurality of query statements that are executed at least once within a particular time period;
executing a random sampling function to randomly select a set of query statements from the plurality of query statements;
storing a plan characteristic associated with a first execution plan for a first execution of a particular query statement of the set of query statements, wherein the first execution of the particular query statement is performed during the particular time period;
determining that a first performance metric associated with a second execution of the particular query statement does not meet a performance criteria;
responsive at least to determining that the first performance metric associated with the second execution of the particular query statement does not meet the performance criteria:
storing a second execution plan for a third execution of the particular query statement, the second execution plan being associated with the plan characteristic that is associated with the first execution plan for the first execution of the particular query statement.

### Claim 2 (depends on 1)

The medium of claim 1, wherein the random sampling function is executed as the plurality of query statements are being identified.

### Claim 3 (depends on 1)

The medium of claim 1, wherein storing the plan characteristic associated with the first execution plan is responsive to the particular query statement being selected from the plurality of query statements by the random sampling function.

### Claim 4 (depends on 1)

The medium of claim 1, wherein the second execution of the particular query statement is performed after the particular time period.

### Claim 5 (depends on 1)

The medium of claim 1, wherein:
the operations further comprise:
determining a second performance metric associated with the first execution of the particular query statement;
determining that the second performance metric is better than the first performance metric; and
storing the second execution plan for the third execution of the particular query statement is further responsive to determining that the second performance metric is better than the first performance metric.

### Claim 6 (depends on 1)

The medium of claim 1, wherein:
the operations further comprise:
determining a second performance metric associated with the first execution of the particular query statement;
determining that the second performance metric is above a model criteria; and
storing the second execution plan for the third execution of the particular query statement is further responsive to determining that the second performance metric is above the model criteria.

### Claim 7 (depends on 1)

The medium of claim 1, wherein:
the operations further comprise:
identifying a first set of executions of the particular query statement, wherein the first set of executions are performed during the particular time period using the first execution plan;
identifying a second set of executions of the particular query statement, wherein the second set of executions are performed during the particular time period using a third execution plan;
determining that a second performance metric associated with the first set of executions is better than a third performance metric associated with the second set of executions; and
storing the second execution plan for the third execution of the particular query statement is further responsive to determining that the second performance metric associated with the first set of executions is better than the third performance metric associated with the second set of executions.

### Claim 8 (depends on 1)

The medium of claim 1, wherein the operations further comprise:
determining a first number of query statements, in the plurality of query statements, that are executed at least once within the particular time period;
determining a second number of query statements to randomly select from the plurality of query statements based on the first number of query statements.

### Claim 9 (depends on 1)

The medium of claim 1, wherein the plan characteristic associated with the first execution plan comprises at least one of:
(a) whether an index is used;
(b) a type of an index that is used;
(c) an identity of a table that is used;
(d) a type of join that is performed on a set of tables;
(e) a sequence of steps that are performed;
(f) a field that is used to access an entry in a table;
(g) a complete set of steps that are included in the first execution plan.

### Claim 10 (depends on 1)

The medium of claim 1, wherein the plan characteristic associated with the first execution plan is stored into a random archive.

### Claim 11

A system, comprising:
at least one device including a hardware processor; and
the system being configured to perform operations comprising:
identifying a plurality of query statements that are executed at least once within a particular time period;
executing a random sampling function to randomly select a set of query statements from the plurality of query statements;
storing a plan characteristic associated with a first execution plan for a first execution of a particular query statement of the set of query statements, wherein the first execution of the particular query statement is performed during the particular time period;
determining that a first performance metric associated with a second execution of the particular query statement does not meet a performance criteria;
responsive at least to determining that the first performance metric associated with the second execution of the particular query statement does not meet the performance criteria:
storing a second execution plan for a third execution of the particular query statement, the second execution plan being associated with the plan characteristic that is associated with the first execution plan for the first execution of the particular query statement.

### Claim 12 (depends on 11)

The system of claim 11, wherein the random sampling function is executed as the plurality of query statements are being identified.

### Claim 13 (depends on 11)

The system of claim 11, wherein storing the plan characteristic associated with the first execution plan is responsive to the particular query statement being selected from the plurality of query statements by the random sampling function.

### Claim 14 (depends on 11)

The system of claim 11, wherein the second execution of the particular query statement is performed after the particular time period.

### Claim 15 (depends on 11)

The system of claim 11, wherein:
the operations further comprise:
determining a second performance metric associated with the first execution of the particular query statement;
determining that the second performance metric is better than the first performance metric; and
storing the second execution plan for the third execution of the particular query statement is further responsive to determining that the second performance metric is better than the first performance metric.

### Claim 16

A method, comprising:
identifying a plurality of query statements that are executed at least once within a particular time period;
executing a random sampling function to randomly select a set of query statements from the plurality of query statements;
storing a plan characteristic associated with a first execution plan for a first execution of a particular query statement of the set of query statements, wherein the first execution of the particular query statement is performed during the particular time period;
determining that a first performance metric associated with a second execution of the particular query statement does not meet a performance criteria;
responsive at least to determining that the first performance metric associated with the second execution of the particular query statement does not meet the performance criteria:
storing a second execution plan for a third execution of the particular query statement, the second execution plan being associated with the plan characteristic that is associated with the first execution plan for the first execution of the particular query statement;
wherein the method is executed by at least one device including a hardware processor.

### Claim 17 (depends on 16)

The method of claim 16, wherein the random sampling function is executed as the plurality of query statements are being identified.

### Claim 18 (depends on 16)

The method of claim 16, wherein storing the plan characteristic associated with the first execution plan is responsive to the particular query statement being selected from the plurality of query statements by the random sampling function.

### Claim 19 (depends on 16)

The method of claim 16, wherein the second execution of the particular query statement is performed after the particular time period.

### Claim 20 (depends on 16)

The method of claim 16, further comprising:
determining a second performance metric associated with the first execution of the particular query statement;
determining that the second performance metric is above a model criteria;
wherein storing the second execution plan for the third execution of the particular query statement is further responsive to determining that the second performance metric is above the model criteria.
