Looking for breakthrough ideas for innovation challenges? Try Patsnap Eureka!

Device and method for optimizing execution plan and based on practice testing

A technology for executing plans and optimizing devices, applied in computing, special data processing applications, instruments, etc., can solve problems such as tight use of database resources, performance degradation of business systems, inaccurate statistical information, etc., to reduce the probability of wrong execution plan selection , the effect of increasing computational overhead and reducing the probability of misjudgment

Active Publication Date: 2012-05-02
INDUSTRIAL AND COMMERCIAL BANK OF CHINA
View PDF2 Cites 31 Cited by
  • Summary
  • Abstract
  • Description
  • Claims
  • Application Information

AI Technical Summary

Problems solved by technology

Since the weight values ​​of various influencing factors preset by CBO in cost estimation cannot truly reflect the actual consumption ratio of various resources, the execution cost estimated by CBO is only a hypothetical cost, which is likely to be the same as the actual execution cost. inconsistent
Moreover, in some business systems with high access loads and extremely complex data environments, data statistics will change drastically, and changes in the data environment (such as adding or deleting indexes, etc.) may lead to inaccurate statistics collected by CBO, resulting in Misjudgment, preferring to choose the execution plan that actually consumes more resources, that is, the "optimal" execution plan considered by CBO is not the "optimal" execution plan in reality
The wrong selection of the execution plan will often lead to tight use of database resources, a sudden drop in the performance of the business system, and a serious impact on the stability of the entire system

Method used

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
View more

Image

Smart Image Click on the blue labels to locate them in the text.
Viewing Examples
Smart Image
  • Device and method for optimizing execution plan and based on practice testing
  • Device and method for optimizing execution plan and based on practice testing
  • Device and method for optimizing execution plan and based on practice testing

Examples

Experimental program
Comparison scheme
Effect test

Embodiment Construction

[0029] In order to make the object, technical solution and advantages of the present invention clearer, the present invention will be described in further detail below in conjunction with specific embodiments and with reference to the accompanying drawings.

[0030]The technical idea of ​​the present invention is based on cost-based execution plan optimization, introduces statistical information of resource consumption during actual execution of the execution plan, and incorporates actual inspection results into the consideration range of execution plan evaluation. When the execution plan changes, the system will still give priority to the historical execution plan with the smallest actual cost that has been tested in practice, but the system will regularly evaluate the new execution plan generated by traditional cost optimization. When the evaluation value of the new plan is less than the old one Execute the actual consumption cost of the plan, the new plan will be used. This...

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to View More

PUM

No PUM Login to View More

Abstract

The invention discloses a device and a method for optimizing an execution plan and based on practice testing. The device comprises a data base device and an optimizing device based on the practice testing, wherein the data base device and the optimizing device are connected with each other. The data base device is used for obtaining data storage of a relational data base, optimizing sentences of a structured query language (SQL), and executing the execution plan. The optimizing device based on the practice testing is used for receiving a traditional optimized execution plan based on cost and output by the data base device and returning the execution plan tested by practice to the data base device. The device and the method for optimizing the execution plan and based on the practice testing are an improved measure of an optimizing method based on cost, can effectively reduce judge error rate of channel bus output (CBO) in complex and versatile data environments without increasing system expenditure and enable the generated execution plan to be the optimal execution plan in reality, thereby improving stability of the system.

Description

technical field [0001] The invention relates to the technical field of data processing of relational databases, in particular to a device and method for optimizing an execution plan based on practice tests. Background technique [0002] When a relational database executes a structured query language (Structured Query Language, SQL) statement, it can obtain data results through a variety of access paths, for example, it can be obtained through index scanning or full table scanning. A path is called an execution plan for the SQL statement. Different execution plans consume different system resources, such as consuming different disk I / O, memory, and CPU. Before executing a SQL statement, a relational database will generate multiple execution plans for the SQL statement, and select the execution plan that consumes the least system resources, which is called the "optimal execution plan". Among them, the module that generates the execution plan and selects the optimal execution...

Claims

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to View More

Application Information

Patent Timeline
no application Login to View More
IPC IPC(8): G06F17/30
Inventor 祁智苗黄涌铭张世荣林瑶
Owner INDUSTRIAL AND COMMERCIAL BANK OF CHINA
Who we serve
  • R&D Engineer
  • R&D Manager
  • IP Professional
Why Patsnap Eureka
  • Industry Leading Data Capabilities
  • Powerful AI technology
  • Patent DNA Extraction
Social media
Patsnap Eureka Blog
Learn More
PatSnap group products

Browse by: Latest US Patents, China's latest patents, Technical Efficacy Thesaurus, Application Domain, Technology Topic, Popular Technical Reports.

© 2024 PatSnap. All rights reserved.Legal|Privacy policy|Modern Slavery Act Transparency Statement|Sitemap|About US| Contact US: help@patsnap.com