Re: SQL tuning tip

From: mohamed houri <mohamed.houri_at_gmail.com>
Date: Wed, 17 Oct 2012 16:34:37 +0200
Message-ID: <CAJu8R6jUHCt-obDccDsvUzTyU=kHnGe=Rqkt-R9F5DFjUuFGdg_at_mail.gmail.com>

Start first by posting the corresponding execution plan with its predicate part.
You could use *select * from table(dbms_xplan.display_cursor (null,null,'ALLSTATS LAST'));
*
to pull the execution plan from the memory together with the estimations (E-Rows) done by the CBO.

Best regards
Mohamed Houri
www.hourim.wordpress.com

2012/10/17 Prabhu Krishnaswamy <prabhu_adam_at_hotmail.com>

> 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
>
>
>

-- 
Bien Respectueusement
Mohamed Houri


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

Received on Wed Oct 17 2012 - 16:34:37 CEST