Plans (query and work)
January 13th, 2006 by Peter Scott
One of our customers has a possible problem with a data discrepancy. Two queries that should return the same results don’t. We should get copies of the queries early next week so we can tear the two apart. We need to analyse whether the results should be the same. And if they should be the same then what is going wrong. As a first step I captured the user’s SQL and got the explain plan. I don’t often see query plans with so much going on: the first few lines tell me it is a star transform, there is materialized view query rewrite as the fact table in the plan is not the original table in the query, three bitmap indexes are filtering the results (only to be expected in a star transform), a bit of partition elimination and for good measure a dose of parallel query. Almost 700 lines of plan; I think a good exercise in analysis for one of my team on Monday morning.
One of my appraisal objectives for the next six months is to recruit a replacement for myself, or at least a part of myself. The part up for grabs is manager of our three Oracle development teams. In a way this is sad in that I become even less hands on than now but there will be a lot of challenge (potentially, I don’t know what yet!) for me in the summer. Cryptically, I have been asked to look out for a new job within the company and apply for it when I see it advertised. I would know which job they mean when I see the advert.
January 13th, 2006 at 11:24 pm
One of my appraisal objectives for the next six months is to recruit a replacement for myself, or at least a part of myself.
Actually that is a sign of a good manager. You are actually accomplishing a few goals here:
a) freeing up some of your time so that you can focus more on higher level stuff (e.g. strategy);
b) provide opportunity for your employees to move up the corporate ladder.
As for hands on, well, it’s tougher when your focus is going to be on the higher level stuff but no reason why you can’t keep on top of things anyhow. Good luck with the cryptic job posting.
January 20th, 2006 at 6:50 pm
One thing you might want to eliminate
is possibility of bug with star transformation.
I ran into a bug myself on 9.2.0.6
and the oracle support told me that
there were a lot of bugs against star transformation in various oracle releases.
Bugs like 4524089, 4502658 and 4920426.
Thus try the queries with star transformation disabled to eliminate that possible cause right away.
Anurag
January 20th, 2006 at 7:42 pm
Anurag, thanks, but in this case it’s the user thinking that two queries are equilivalent, when they are not
I know about some bugs with star transforms and also with parallel query :( We are running this system on 9.2.0.7 and try to keep patching up to date.