Re: SQL tuning tip

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Wed, 17 Oct 2012 08:32:44 -0700 (PDT)
Message-ID: <1350487964.20480.YahooMailNeo_at_web121606.mail.ne1.yahoo.com>

Yet you are logged into the problem database with the version information in full view at login.  In this forum posts absent the version information don't provide enough information since, as you noted, the optimizer changes between releases and what would be an acceptable plan for an 8.1.7.4 database will likely not be acceptable in 10.2.0.5 or 11.2.0.3.

David Fitzjarrell

---

From: mohamed houri <mohamed.houri_at_gmail.com> To: David Fitzjarrell <oratune_at_yahoo.com> Cc: "prabhu_adam_at_hotmail.com" <prabhu_adam_at_hotmail.com>; "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Wednesday, October 17, 2012 9:08 AM Subject: Re: SQL tuning tip

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
http://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:32:44 CEST