Re: gather_plan_stats hint

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

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