RE: Beaking down SQL performance by wait time

In 10g you can use Active Session History (v$active_session_history / dba_hist_active_sess_history) and sum time_waited by sql_id.

Note that use of these views requires purchase of the "performance pack" or some similarly named additional licensing option. Nevertheless, Grid Control/OEM fetches from these views by default.

Prior to 10g you need to use a third party product like Quest Spotlight or Symantec i3 (formerly Veritas, better known to us all as Precise). Neither of these products publish their agent API, so you must rely on their GUIs.

Oracle 10g still does not allow you to correlate timed statistics (resource counters other than waits) to specific SQL statements. You must devise you own clever tools to accomplish such tasks.

Jeremiah Wilton
ORA-600 Consulting
http://www.ora-600.net

-----Original Message-----

From: fmhabash_at_gmail.com

In Oracle, you can beak down the performance by wait type. This wait is described in terms of count and time and their derivatives. Also, you can aggregate these numbers at DB or session level. How do you aggregate these number at the SQL hash level at least for the total number of executions. I searched in the different views and could not find a way on how you do that.

--

http://www.freelists.org/webpage/oracle-l Received on Thu Feb 22 2007 - 17:40:51 CST