Re: SQL tuning tip
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: "prabhu_adam@xxxxxxxxxxx" <prabhu_adam@xxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 17 Oct 2012 07:29:00 -0700 (PDT)
If you don't trace the session you probably won't know where that bottleneck
is; a 10046 trace at level 8 or 12 should provide a good place to start your
investigation. As Tim Gorman said in another thread: "Don't guess. Trace it."
David Fitzjarrell
________________________________
From: Prabhu Krishnaswamy <prabhu_adam@xxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Sent: Wednesday, October 17, 2012 8:16 AM
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:
