Re: SQL tuning tip

From: mohamed houri <mohamed.houri_at_gmail.com>
Date: Wed, 17 Oct 2012 17:08:17 +0200
Message-ID: <CAJu8R6gngJf996ieMsZSC+qMW=7RnbkHAQr_o2YGYBRX5ufrag_at_mail.gmail.com>

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_at_yahoo.com>

> 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_at_gmail.com>
> *To:* prabhu_adam_at_hotmail.com
> *Cc:* oracle-l_at_freelists.org
> *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_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
>
>
>
>
>

-- 
Bien Respectueusement
Mohamed Houri


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

Received on Wed Oct 17 2012 - 17:08:17 CEST