Re: SQL tuning tip
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: "mohamed.houri@xxxxxxxxx" <mohamed.houri@xxxxxxxxx>, "prabhu_adam@xxxxxxxxxxx" <prabhu_adam@xxxxxxxxxxx>
- Date: Wed, 17 Oct 2012 07:46:11 -0700 (PDT)
Without the Oracle version information seeing the plan only provides half, if
that, of the information needed. I neglected to ask for that in my response,
so I'm correcting that now.
Post the Oracle version you're using, along with relevant snippets of the 10046
trace you're surely going to generate.
David Fitzjarrell
________________________________
From: mohamed houri <mohamed.houri@xxxxxxxxx>
To: prabhu_adam@xxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Sent: Wednesday, October 17, 2012 8:34 AM
Subject: Re: SQL tuning tip
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
http://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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
