RE: SQL tuning tip

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 17 Oct 2012 13:14:12 -0400
Message-ID: <021701cdac8a$d4f717c0$7ee54740$_at_rsiz.com>

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 (_at_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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Prabhu Krishnaswamy
Sent: Wednesday, October 17, 2012 10:16 AM To: oracle-l_at_freelists.org
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

--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 17 2012 - 19:14:12 CEST