Re: gather_plan_stats hint
- From: Stefan Koehler <contact@xxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>, oralrnr@xxxxxxxxx
- Date: Thu, 14 May 2015 10:07:20 +0200 (CEST)
Hi Orlando,
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: @OracleSK
Orlando L <oralrnr@xxxxxxxxx> 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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
