System and method to generate optimized SQL access plan based on existing plans (01-Feb-2010)
SQL statements are more and more complex, the access plans are complex also (For example, for some large queries the access plan may contain hundreds of nodes). It’s very hard to manually analyze these plans. Especially during query performance tuning, there will be lots of access plans generated. Now DBAs have to manually analyze and compare these plans, and try to find the best access plan depending on their personal experiences. However this is a very time consuming and error-prone process. So how to efficiently perform the query tuning and how to efficiently find the optimized access plan is one of the major pain points for DBAs. In order to make the query tuning more efficiently, we proposed a novel way to optimize a query’s access plan based on existing access plans. The approach will bring introduce a new approach for query performance tuning and will save massive manual works.
System and method to generate optimized SQL access plan based on existing plans
In this disclosure, we propose a novel method to calculate a locally optimum access plan based on existing plans. The goal of this approach is provide an automatic way for DBAs to find the best access plan for a query, which will greatly save database administrator (DBA)'s efforts during query performance tuning, it will greatly improve such kind of jobs' efficiency and greatly improve the usability of tools which will apply this method.
The major point for this method is to introduce an access path combination system which can automatically generateand validate the best access plan for a query, show in Figure1:
The system contains two main components:
Optimized plan generator: given existing plans, generate the candidate optimized plan.
Plan validator: with the candidate optimized plan as input, validate it in both semantic and executable perspective.
And we also propose a corresponding novel approach which is shown in Figure2:
1
Figure2: The process of generating optimized access plan
The key point is to provide a new way to do query performance tuning. It will greatly save DBAs effort in such kind of jobs and enhance the functionality of tools using this approach.
This disclosure introduced system and correspondingmethod to generate the optimized access plan basedon existing plans. As shown in figure 1, the system contains two
2
main components:
Optimized plan generator: given existing plans, generate the candidate optimized plan.
Plan validator: with the candidate optimized plan as input, validate it in both semantic and executable perspective.
We will introduce the detail of above two components:
Optimized Plan Generator
Step 1: For a set of access plan, using Step 1 to get all better access plans from access plan pair combinations. The lowest cost access plan is the bestexisting access plan.
Step 1: To get an existing better access plan from two access plan.
Input: two access plans
Output: existing best access plan
In order to interpret, we name Access plan 1 A1, its according table set named S1 (T1.1, T1.2… T1.
S2(T2.1, T2.2… T2.
1.1 Initialize all the table set for each access plan with all tables.
1.2 Find the replaceable sub-tree.
1.2.1 Select first table T1.1 from A1. If the access plan A2 has the same table in leaf then they are replaceable sub-tree. Record the two sub trees. Then replace the higher cost sub-tree with the lower cost sub-tree.
1.2.2 Try to using T1.2 from S1 to match S2. Repeatthe same action until to every single table from A1 compare to A2.
A1
3
N).
Similarly, we name Access plan 2 A2 and its according table set
N).
T1.1
A2
4
Find More Documents Like This
Download this Disclosure
Click here if you want to sign-in now.
