Oracle Magazine, July/August 2019
Database Developer and DBA ETL use the plan that will yield the quickest result pick the shortest checkout line Returning to the shopping metaphor sometimes things dont work out quite as I expect Ill scan the checkout aisles and see a candidate aisle that has only one person in line Ill race down there with my small basket confident that Ive beaten the system only to find that when I get there that one person has the shopping cart that trumps all other shopping carts Hundreds of items in the cart compounded by many being fresh produce each of which will need to be weighed and priced by the cashier From my perspective this is a disaster I have just two or three things to purchase and now I am going to be stuck waiting for 30 minutes This too is the same difficulty that may befall the database optimizer It may choose an execution plan that appears to be optimal just one lone person in the checkout aisle but when the plan is actually used to execute a SQL statement that plan or parts of it that the optimizer estimated would be quick and efficient turn out to be more costly than expected like the killer cart This metaphor drives the strategy for the SQL tuning database developer If the tuner developer can locate where the optimizer estimates differed significantly from the reality of the SQL execution that is the place to best target the tuning effort ESTIMATES VERSUS REALITY Oracle Database provides an optimizer hint to provide this estimate versus reality information directly to the developer Whereas most optimizer hints instruct the optimizer to use a certain operation within the execution plan the GATHER_ PLAN_ STATISTICS hint differs instructing the database engine to record execution plan statistics when the SQL statement is executed Thus both the optimizer estimates and the runtime actuals are available once the execution completes Displaying these ORACLE MAGAZINE JULY AUGUST 2019 146
You must have JavaScript enabled to view digital editions.
