Re: gather_plan_stats hint

From: Mladen Gogala <mgogala_at_yahoo.com>
Date: Thu, 14 May 2015 06:14:07 -0400
Message-ID: <5554756F.2070706_at_yahoo.com>

Hi Stefan,
Of course that the OP needs to wait for completion. The principles and the theory of tracing are explained in the most groundbreaking Oracle tuning book ever, "Optimizing Oracle for Performance" by Cary Millsap. I am saying that about the book, after having read all books piblished by Tom Kyte, Jonathan Lewis and Christian Antognini. 10046 is the way to go. That book answers to the question how to tune and why tune in the first place.

On 05/14/2015 04:07 AM, Stefan Koehler wrote:
> The additional run time / SQL execution information (gather_plan_stats or statistics_level) is only published and available after the SQL has been
> executed successfully. Oracle 11g introcuded a feature called "Real-Time SQL Monitoring" (dbms_sqltune.report_sql_monitor), which provides these kind
> of information (and a lot more) while the SQL is running, but you can use "Real-Time SQL Monitoring" only, if you have the diagnostic and tuning pack
> license.
>
> If you dont have these licenses, you may can use DTrace (depending on your OS of course) and Tanel Poder's os_explain
> (http://blog.tanelpoder.com/2009/04/24/tracing-oracle-sql-plan-execution-with-dtrace/) to follow the execution plan "on-the-fly" and check its
> resource usage.
>
> Mladen's suggestion about the 10046 trace may also not work as the execution plan is just dumped after the first or all SQL executions. In addition
> the wait interface also lacks, if your issue is caused by CPU usage (e.g. improper HJ, improper NLJ) and you may have problems to map the object
> waits, if the execution plan is quite complex and references objects multiple times.
>
> If the execution plan is not too complex you can also try to propose a hypothesis about the execution flow with the different binds / literals and
> check the cardinalties manually by querying the tables.
>
> Hope this helps.
>
> Best Regards
> Stefan Koehler
>
> Freelance Oracle performance consultant and researcher
> Homepage:http://www.soocs.de
> Twitter: _at_OracleSK
>
>> >Orlando L<oralrnr_at_gmail.com> hat am 14. Mai 2015 um 05:01 geschrieben:
>> >
>> > All,
>> >
>> > We have a SQL performing badly in prod intermittently in 11g. When it runs ok it finishes fine in seconds. When it is bad it does not finish even
>> >after 30/40 mins or even hours.
>> >
>> > I have to find out what is going on, so I used the gather_plan_stats hint, but I dont see a problem in the estimate vs actual when it runs good.
>> >However, I am not able to see the same thing when the query is slow or hangs. How do I see those values for such cases. TIA.
>> >
>> > Orlando.
> --
> http://www.freelists.org/webpage/oracle-l
>
>

-- 
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com

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

Received on Thu May 14 2015 - 12:14:07 CEST