Browse Prior Art Database

A New Method and Apparatus of DataBase Optimizer Selection Based on Realtime Testing Competition Feedback

IP.com Disclosure Number: IPCOM000198969D
Publication Date: 2010-Aug-19
Document File: 3 page(s) / 166K

Publishing Venue

The IP.com Prior Art Database

Abstract

We propose a system and method to compete certain N winners generated from underline DB access path cost model calculation and feedback realtime competition results to DB engine for refining APS component decision by combining all kinds of nonlinear complicated optimization computation factors (random I/O cost, CPU cost, list prefetch and sort cost, etc) result in a background testing method.

This text was extracted from a PDF file.
At least one non-text object (such as an image or picture) has been suppressed.
This is the abbreviated version, containing approximately 58% of the total text.

Page 1 of 3

A New Method and Apparatus of DataBase Optimizer Selection Based on Realtime Testing Competition Feedback

Query optimizer is one of the most important components of database management system that determines the most efficient way to execute a query. Nowadays, almost all relational database vendors adopt cost-based optimization as the only approach or the main approach. Cost-based optimizer generates as many access plan candidates as possible and then estimates elapsed time (means cost) for each access plan candidates that consists of "CPU cost" and "I/O cost".

Although cost-based optimizer is widely used by almost all major

                                          relational database vendors, it is still imperfect. One of the reasons that is I/O cost is hard to be estimated accurately. First of all, statistics itself is unable to exactly model data distribution.

                    
1.b , such Index cluster ratio would cause database execution in data page swap in/swap out between memory and disk (Random I/O).

As shown in figure

a). Clustered index scan.

Nonclustered index scan.

Fig 1. A Difficult Scenario for Optimizer Cost Model Estimation involving non-linear factor

In order to avoid Random I/O, optimizer may use a technique "list prefetch". However, "list prefetch" requires sort so introduces some performance overhead. The challenge is that it is not an easy decision to choose between "list prefetch" overhead and Random I/O cost. Similar scenarios would be introduced when table

joining's sort cost and Random I/O

                        cost balance. Last but not least, capability of storage devices could be different so weighting CPU cost and I/O cost is a real challenge in a real production environment.

So the key problems are clear:


How to evaluate realtime Random I/O cost, CPU cost, list prefetch and sort cost, etc. How to combine the underline optimizer cost model and the runtime memory related info?

How to feedback optimizer the realtime 'in memory' factors to refine access path selection?

1


b).

[This page contains 38 pictures or other non-text objects]

Page 2 of 3

Main Idea


Our invention proposes a system and method to compete certain N winners generated from underline DB access path cost model calculation and feedback realtime competition results to DB engine for refining APS component decision by combining all kinds of nonlinear complicated optimization computation factors (random I/O cost, CPU cost, list prefetch and sort cost, etc) result in a background testing method.

Claims
1. The system design and component to build up runtime competition environment by certain winners from underline optimizer cost model algorithm.
2. The syst...