Re: SQL tuning tip
- From: mohamed houri <mohamed.houri@xxxxxxxxx>
- To: prabhu_adam@xxxxxxxxxxx
- Date: Wed, 17 Oct 2012 16:34:37 +0200
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@xxxxxxxxxxx>
> 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
>
>
>
--
Bien Respectueusement
Mohamed Houri
--
//www.freelists.org/webpage/oracle-l
Other related posts:
