Re: Oracle 10053 Trace Data?
Date: Thu, 26 Jun 2008 08:28:17 -0700 (PDT)
Message-ID: <859870.38712.qm@web53909.mail.re2.yahoo.com>
function doHelpTag(helpTag){
var helperFrame;
helperFrame = document.getElementById("helpTag");
//helperFrame.src='f?p=200:99:326930779985062055::NO::P99_HELP_TAG:'+helpTag;
if (helperFrame.contentDocument) {
helperFrame.contentDocument.location.replace('f?p=200:99:326930779985062055::NO::P99_HELP_TAG:'+helpTag); } else {
helperFrame.Document.location.replace('f?p=200:99:326930779985062055::NO::P99_HELP_TAG:'+helpTag); }
}
A script enabled browser is required for this page to function properly.
if (self.location.href != top.location.href){
top.location.href = self.location.href; }
function doHelpTag(helpTag){
var helperFrame;
helperFrame = document.getElementById("helpTag");
//helperFrame.src='f?p=200:99:326930779985062055::NO::P99_HELP_TAG:'+helpTag;
if (helperFrame.contentDocument) {
helperFrame.contentDocument.location.replace('f?p=200:99:326930779985062055::NO::P99_HELP_TAG:'+helpTag); } else {
helperFrame.Document.location.replace('f?p=200:99:326930779985062055::NO::P99_HELP_TAG:'+helpTag); }
}
A script enabled browser is required for this page to function properly.
if (self.location.href != top.location.href){
top.location.href = self.location.href; }
function doHelpTag(helpTag){
var helperFrame;
helperFrame = document.getElementById("helpTag");
//helperFrame.src='f?p=200:99:326930779985062055::NO::P99_HELP_TAG:'+helpTag;
if (helperFrame.contentDocument) {
helperFrame.contentDocument.location.replace('f?p=200:99:326930779985062055::NO::P99_HELP_TAG:'+helpTag); } else {
helperFrame.Document.location.replace('f?p=200:99:326930779985062055::NO::P99_HELP_TAG:'+helpTag); }
}
A script enabled browser is required for this page to function properly.
if (self.location.href != top.location.href){
top.location.href = self.location.href; }
function doHelpTag(helpTag){
var helperFrame;
helperFrame = document.getElementById("helpTag");
//helperFrame.src='f?p=200:99:326930779985062055::NO::P99_HELP_TAG:'+helpTag;
if (helperFrame.contentDocument) {
helperFrame.contentDocument.location.replace('f?p=200:99:326930779985062055::NO::P99_HELP_TAG:'+helpTag); } else {
helperFrame.Document.location.replace('f?p=200:99:326930779985062055::NO::P99_HELP_TAG:'+helpTag); }
}
A script enabled browser is required for this page to function properly.
if (self.location.href != top.location.href){
top.location.href = self.location.href;
}
How about metalink? The first hit on a 10053 query is:
Subject: CASE STUDY: Analyzing 10053 Trace Files
Doc ID: Note:338137.1 Type: REFERENCE
Last Revision Date: 23-JUN-2008 Status: PUBLISHED
Case Study Abstract
This case study uses a tracefile from an actual service request to illustrate a methodology for analyzing 10053 trace files. Please note that event 10053 trace files were designed to assist Oracle developers and support personnel to diagnose optimizer problems and are subject to change with every new patchset or release. Our intent with this case study is not to provide a comprehensive reference to the 10053 trace file, but rather to show how this trace file is used by Oracle engineers. Along the way, we will also gain insight into how the Cost Based Optimizer (CBO) arrives at the cost for a query and, ultimately, how it arrives at the execution plan. It is also important to point out that the algorithms used by the CBO to estimate the cost of a query may change from version to version as well.
Our task here is to analyze the bad plan and determine how the CBO computed the costs that led to the cheaper, but flawed plan. We will compare both 10053 traces at some points, but mainly focus on how the costs were computed for the bad plan. The good plan is basically a shorter version of the bad plan (one in which no indexes were considered).
The reason for examining a 10053 is typically to understand why the CBO made its decisions. The 10053 will help answer the question "Why was my index not used?" or the related, "Why did the CBO choose an FTS?". The 10053 is typically not the best place to start looking for tuning opportunities - the execution plan and TKProf have better information in this regard; the 10053 is used for deeper cause determination.
Click here to read the entire case study.
To view the Case Study Index, click here.
--- On Thu, 6/26/08, Harish Kalra <harish.kumar.kalra_at_gmail.com> wrote:
From: Harish Kalra <harish.kumar.kalra_at_gmail.com>
Subject: Re: Oracle 10053 Trace Data?
To: azitelli_at_gmail.com
Cc: oracle-l_at_freelists.org
Date: Thursday, June 26, 2008, 1:48 AM
Not particulary about 10053 but optimizer development team is also blogging at
http://optimizermagic.blogspot.com
about Oracle CBO and its various insights.
Thanks & Regards
-Harish Kalra
On 6/26/08, Andrew Zitelli <azitelli_at_gmail.com> wrote:
Can anyone recommend any books, technical papers or web sites which discuss SQL tuning using Oracle's 10053 trace data? I know Cary Millsap's new company Method-R offers a week long class on this topic, taught by Karen Morton. Are there any other worthwhile resources discussing this topic? Thanks!
Andy Zitelli
-- Harish Kumar Kalra Oracle Database Consultant http://hkalrain.blogspot.com/ -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 26 2008 - 10:28:17 CDT