Re: SQL tuning tip

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Wed, 17 Oct 2012 07:29:00 -0700 (PDT)
Message-ID: <1350484140.606.YahooMailNeo_at_web121604.mail.ne1.yahoo.com>

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_at_hotmail.com> To: oracle-l_at_freelists.org
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                        
--

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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Oct 17 2012 - 16:29:00 CEST