Re: SQL tuning tip
- From: mohamed houri <mohamed.houri@xxxxxxxxx>
- To: David Fitzjarrell <oratune@xxxxxxxxx>
- Date: Wed, 17 Oct 2012 17:08:17 +0200
It is always good to post the oracle version information. But I am still
unable to figure out how the absence of this information could halve the
answer given by a real execution plan of a very simple query taking 300
seconds to complete. There are of course changes in the CBO from version to
version that could explain why a CBO did choose a path here and not there.
But, when confronted to a localized performance problem (a query for
example) I am inclined to start by getting its execution plan first. If
this reveals itself of no help then I will trace this query with the 10046
events to see what is happening behind the scene (SQL recursive calls, VPD
policy,etc...)
Best regards
Mohamed Houri
www.hourim.wordpress.com
2012/10/17 David Fitzjarrell <oratune@xxxxxxxxx>
> 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
>
>
>
>
>
--
Bien Respectueusement
Mohamed Houri
--
//www.freelists.org/webpage/oracle-l
Other related posts:
