Re: SQL tuning tip

A gentle observation...

You have asked for help in this forum. Respondents (who cared enough to respond promptly rather than just deleting your message) have requested more information in order to help you. Yet in your next response you choose to argue and to not provide the information?

Please be assured that this list best helps those who help themselves. Think about it.

Good luck,

-Tim

On 10/17/2012 9:08 AM, mohamed houri wrote:
> 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
>>
>>
>>
>>
>>
>

-- 
Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal     => PO Box 352151, Westminster CO 80035
website    => http://www.EvDBT.com/
email      => Tim_at_EvDBT.com
mobile     => +1-303-885-4526
fax        => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...

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