RE: SQL tuning tip
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <prabhu_adam@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 17 Oct 2012 13:14:12 -0400
First, I notice you've got a count and some scalars, so that query won't run
without a group by, so you're providing us some "looks like" query. That's
okay, but you might try to give a query that at least would pass the parser.
Second, we don't know your release and whether you have star transformations
enabled.
Full tracing would give you some information, but let's presume you're at
least up to a release where
--+ gather_plan_statistics
is implemented.
The results of:
set linesize 140 pagesize 40 null ~;
set time on;
set timing on;
select
--+ gather_plan_statistics
dim_a.key1, dim_b.key2,count(f.key1)
from
dimension_a dim_a,
dimension_b dim_b,
fact f
where dim_a.key1 = f.key1
and dim_b.key2 = f.key2
and dim_a.key1 in ('val1','val2','val3','val4')
group by dim_a.key1,dim_b.key2
order by dim_a.key1,dim_b.key2;
select * from table(dbms_xplan.display_cursor(format=>'COST ALLSTATS
LAST'));
Should tell you a lot about what is going on. IF you intend to be getting a
star transformation AND you are not getting one, I would directly check the
manual matching your release that corresponds to Oracle Data Warehousing
Guide e16579.pdf for pages 20-8, 20-9, succinct summary to see whether there
is an obvious reason why you're not getting a star transformation.
Of course too, I'm guessing that key1 is some sort of literal and it might
be numeric. Adjust your predicates as appropriate.
I'd suggest that at least knowing the plan is worthwhile before analysis of
a full on trace, and since we already know it takes about 300 seconds, it
seems reasonable overhead to gather the actuals together with the cost
estimates so that your leaping off point before digging into the details of
the costs in the trace is whether you have a reasonable plan and whether
that is because of a disconnect between the estimated costs and actual costs
or not.
Often this approach reveals some silly error (wrong types, broken indexes,
misleadingly inaccurate statistics or missing statistics) that obviously has
an easy correction. If it is not obvious, but it is clearly not the plan you
are expecting, then you'll want to figure out why. Jonathan Lewis
(Cost-Based Oracle Fundamentals) and Chris Antognini (Troubleshooting Oracle
Performance) have written what I consider the most useful books on getting
the plans you want, and recent papers and talks by Maria Colgan (@SQLmaria)
complete that picture.
If there is a structural reason why you cannot get a star transformation,
you might consider reading my paper about Spiny Starfish, or JL's blog
(Oracle Scratchpad: Star Transformation and Star Transformation - 2). Tim
Gorman has also written about configuring and implementing a datawarehouse
with partitioning and a "virtuous cycle" which I consider fundamental to
deciding whether your situation calls for partitioning and the maintenance
of bitmap indexes to facilitate star transformations and possibly bitmap
joins. An outstanding, if possibly a bit dated, 15 pager "Your
Transformation is in the Stars" by Joel Goodman on the basics of getting
star transformations is easily found. I don't know whether that is an update
since 2009 for that paper.) Doug Burns and Tom Kyte should be on your
research list as well.
Now, if you've got a good lookin' plan, whether a star transformation or
just a more traditional plan that dramatically filters down the rowids you
need (rowid spines and spiny starfish), then examining a trace is what will
tell you where the time is going. As far as I'm concerned, heading to Cary
Millsap's company website gives the best advice about that:
www.method-r.com.
(None of these folks pay me to say they are good.)
Good luck!
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Prabhu Krishnaswamy
Sent: Wednesday, October 17, 2012 10:16 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: SQL tuning tip
Lists,
We have a simple query and takes 300 seconds to run which drives us crazy to
know where the bottleneck is...
Here is the scenario:
Dimension A - Has 1000 rows
Dimension B has 5000 rows
Fact F has 30 million records
Dimension A & B has BITMAP indexes on Key column
SELECT DIM_A.KEY1, DIM_B.KEY2, COUNT(F.KEY1) FROM DIMENSION A, DIMENSION B,
FACT F WHERE A.KEY1 IN (VAL 1, VAL2 , VAL3, VAL4) AND A.KEY1 = F.KEY1 AND
B.KEY2 = F.KEY2
Will the following work?
1) USE_NL hint instead of USE_HASH hint
2) Whether dropping and recreating the same table (including all
partitions)?
3) Any new feature/concept that might help?
Any insight is highly appreciable.
Thank you
Prabhu
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
