RE: SQL tuning tip
Date: Wed, 17 Oct 2012 10:46:56 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E88562DA1907_at_NADCWPMSGCMS10.hca.corpad.net>
Do this:
1.) trace your session:
alter session set max_dump_file_size=unlimited ;
alter session set tracefile_identifier='&username_';
alter session set events '10046 trace name context forever, level 12';
2.) Add this hint to your query and execute it
/*+ gather_plan_statistics */
3.) Then after the query executes run this:
select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED IOSTATS LAST')) /
4.) Take your tracefile from step1 and process it using orasrp (in windows) found at: www.oracledba.ru/orasrp/
using:
c:\> orasrp --google-charts {trace_file_name} > {tracefile}.html
5.) Open the html file and examine it
This will give you everything you need to [help] understand what's going on.
You can also take a 10053 trace and examing the optimizer calculations if you think it's not costing something correctly.
Also make sure you have collected SYSTEM_stats with NOWORKLOAD and with an INTERVAL to give Oracle all the information it needs to correctly cost the different choices it has available. (as I learned earlier this week or late last week).
Chris
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Fitzjarrell
Sent: Wednesday, October 17, 2012 10:33 AM
To: mohamed houri
Cc: prabhu_adam_at_hotmail.com; oracle-l_at_freelists.org
Subject: Re: SQL tuning tip
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 -- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 17 2012 - 17:46:56 CEST