tag:blogger.com,1999:blog-4096575.post1046696820064379670..comments2024-03-19T03:20:26.726-04:00Comments on The Arup Nanda Blog: A Tool to Enable Stats Collection for Future Sessions for Application ProfilingArup Nandahttp://www.blogger.com/profile/03392706779349258765noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-4096575.post-69162075058908675482012-06-05T02:59:50.991-04:002012-06-05T02:59:50.991-04:00You made some good points here. I did a search on ...You made some good points here. I did a search on the topic and found most people agree with your blog. ThanksKyle Heathhttp://www.diskdoctors.com/florida-lab.aspnoreply@blogger.comtag:blogger.com,1999:blog-4096575.post-42232483058956753572010-09-28T23:26:43.349-04:002010-09-28T23:26:43.349-04:00@Flado - you are right about the resource consumpt...@Flado - you are right about the resource consumption of statspack. That's precisely why I discounted it.<br /><br />However, your point about Statspack being "official" is valid. Some organizations may balk at a third party tool, especially one that is free; but not at something that is standard in Oracle.<br /><br />Now that you have both approaches, you may decode the one that best fits your need.Arup Nandahttps://www.blogger.com/profile/03392706779349258765noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-25068392937969455062010-09-28T08:19:15.698-04:002010-09-28T08:19:15.698-04:00Hi Arup,
Wouldn't it be easier to just use St...Hi Arup,<br /><br />Wouldn't it be easier to just use Statspack for the collection? The post-logon trigger would simply call<br />statspack.snap(<br /> i_session_id=>sys_context('USERENV','SID'),<br />i_ucomment=>'START or betrer SID', i_snap_level=>0)<br />instead of the INSERT-SELECT.<br />The pre-logoff trigger would make the same call with a different comment value to enable matching of snapshots made by the same session.<br /><br />Advantages I see over your approach:<br />1. Statspack is supplied by and supported by Oracle which makes it easier for the production DBA or consultant to convince management to install it if not already installed.<br />2. Amount of data captured is easily and extensively configurable (snapshot levels, thresholds)<br />3. Reporting tools are available from Oracle (spreport.sql, sprepsql.sql) and many third parties, since the schema is well-known.<br />4. It is easy to manually take intermediate snapshots while a session is running to help concentrating on parts of the app processing.<br /><br />Drawbacks I see:<br />1. Increased disk space usage as Statspack captures much more data even at level 0.<br />2. Increased resource usage, especially for applications with short-lived sessions or prone to cause logon storms.<br />The second drawback is very significant, but when used sparingly, this method provides far greater flexibility.<br /><br />Cheers!<br />FladoFladohttps://www.blogger.com/profile/04901763101139511192noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-76906713006230110842010-09-14T09:37:13.049-04:002010-09-14T09:37:13.049-04:00@Flado - yes, the accumilators wil lbe all zero at...@Flado - yes, the accumilators wil lbe all zero at the very beginning of the session. What if there are some special recursive process that happens before the session gets completely established, such as VPD, application contexts, etc; or some other trigger that establishes some other variables? These will acculmulate the values. In normal application profiling you *may* want to ignore these "noises". That's where the post-login trigger comes handy.<br /><br />If you would rather not ignore these recursive processes increasing the stats, by all means ignore the front end stats and assume 0. No harm done. But the presence of the front end stats provide flexibility.Arup Nandahttps://www.blogger.com/profile/03392706779349258765noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-65835722779877290122010-09-14T09:33:32.095-04:002010-09-14T09:33:32.095-04:00@Balaji - yes, they can be normalized into two tab...@Balaji - yes, they can be normalized into two tables. I didn't deliberately do it because the repeating elements are too few (about 15); and keeping them in the same table helps doing some serious filtering, group by operations. Normalizing into two tables means always joining, requiring an index and so on.Arup Nandahttps://www.blogger.com/profile/03392706779349258765noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-10636772258956441512010-09-14T05:05:58.070-04:002010-09-14T05:05:58.070-04:00Why do you capture the stats at logon? All the acc...Why do you capture the stats at logon? All the accumulators (e.g., "redo size") in v$sesstat start from 0 anyway; diffing the absolute values (e.g., "workarea memory allocated") makes no sense as they can go up and down during the life of the session.<br />One capture just before logoff (perhaps capturing v$session_event as well) should be all that's needed.<br /><br />Cheers,<br />FladoFladohttps://www.blogger.com/profile/04901763101139511192noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-50745370463617512402010-09-13T11:43:51.328-04:002010-09-13T11:43:51.328-04:00Hi Arup,
Thanks for the tool , could we normali...Hi Arup,<br /><br /> Thanks for the tool , could we normalize the recstats table to two tables so that the environment variables need not be stored for each stat value.<br /><br />Thanks,<br />BalajiBalaji Ramachandranhttps://www.blogger.com/profile/02942522596997332393noreply@blogger.com