RE: Re-setting V$tables

I still have to see a customer that has licensed the diag pack.  

As of Mlady proposition to write something that would capture at start of module stats and drop them somewhere, all that is fine but it means you need
to add a trigger on the session or modify the sequence of batches. As production DBA this kind of stuff are excluded. I still have to see a generic method, usable by production DBA that could be applied on running production DB to mimic profiles without adding objects in the DB or modifying the code, all behavior usually forbidden.  

Mladen pointed that he was surprised that the DEV has included the DBMS_INFO. As production DBA I usually have nuts and expect for the future to have nuts as main meal. he was lucky and I don't expect this to happen many time.  

As of now I have reproduced much of AWR sampling and gathering data on v$session_wait and v$sql figures plus provide tools to dig into the data gathered
and give you the text and figures. But I recognize that such method can only answer questions like 'which query takes how much of the DB resources between X and Y where resource is defined as the sample of each second of v$session wait and X and Y are given by the dump of figures from v$sql (usually every 60s). The problem is you can't distribute the loads among TASK since all SQL are melted regardless of their origin.  

But Mladen problem gave me the idea to add the following columns from v$sql to the sampler :  

 MODULE, MODULE_HASH , ACTION, ACTION_HASH, CLIENT_INFO   Next it is easy to query the sample group by one of these value and since you already have timed figures from v$sql you can make the Delta per minute, hours, whatever, drill down to each query, its number of executions and others figures during a given frame time.  

However the problem is that v$sql is usually taken only on a minute basis and such method will miss any short live session where a trigger method will miss nothing.  

For those interested, keep an eye on http://www.smenu.org/smenu_owi.htm Should not be long to implement this. I will make also a stand alone version of the sampler so people crying on the $$ of AWR or version 8i and 9i, can have a diagnostic of the oracle wait interface for free. and last (that's the more painful, update the doc)  

Regards,  

B. Polarski      

From: Niall Litchfield [mailto:niall.litchfield_at_gmail.com] Sent: Wednesday, 11 October, 2006 7:27 AM To: mgogala_at_verizon.net
Cc: Sergey Popov; Mark.Bobak_at_il.proquest.com; oracle-l_at_freelists.org Subject: Re: Re-setting V$tables

it sounds like the sessions are long running and consistent (i.e not pooled). I'd expect that active_session_history would be good enough for you to identify the top modules.

On 10/11/06, Mladen Gogala <mgogala_at_verizon.net> wrote:

--

Niall Litchfield
Oracle DBA
http://www.orawin.info

--

http://www.freelists.org/webpage/oracle-l Received on Wed Oct 11 2006 - 02:15:16 CDT