Re: Block dump - Uncommitted TXN - Help Urgent

On 9/19/06, Mark W. Farnham <mwf_at_rsiz.com> wrote:
>
> HAVING many many extents is rarely a problem, even with dictionary
> managed tablespaces (with the notable exception of online drop, and
> pathological conditions such as having a huge number of extents each smaller
> than the multiblock read size on tables scanned frequently enough to be a
> problem and infrequently enough to age out of cache.)
>
>
>
> Making extents frequently, on the other hand, could be your entire problem
> here.
>
>
>
> If you create a table with columns like dba_extents plus an as_of_date
> column, and track your dictionary based objects as they change in size
> (perhaps once or twice a day is probably enough), you will be able to
> quickly identify objects which have an inappropriate next size. Now
> rebuilding those objects is usually a time waster unless they exhibit empty
> front (ie. Many empty blocks at the beginning of a table or cluster that is
> significantly scanned before the block that contains the first row or a
> large honeycomb factor that is an artifact of non-recurring past behavior.)
> But changing the next size on the identified quickly extending objects to
> something that will cause an extent allocation about once a day (or less,
> some folks shoot for once a quarter or less) should eliminate the
> possibility that piling up on allocating extents is your problem.
>
>
>
> Now while I usually advocate finding the actual individual problem and
> solving that (see Hotsos, Oak table, etc.), it sounds to me as if in this
> case you have a storm of obfuscation that can be quickly and easily
> eliminated. Then, if the systemic noise was not your entire problem, you
> should return to resolving the actual individual problems by wait analysis.
>
>
>
> Rightsizing, Inc. used to maintain and sell a product called "extmon"
> designed to track object growth as an aid to capacity planning. Since it was
> clear text sql, unauthorized free distribution soon made sales small enough
> to discontinue the product (it was only $500). You can probably build it
> yourself in a few hours and make it fast in a bit longer. Or you can
> probably find it, a derivative, or an independently created similar script
> set laying around the net somewhere.
>
>
>
> Good luck. Just please don't misconstrue this advice as suggesting you
> should spend your life rebuilding objects.
>
>
>
>
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *BN
> *Sent:* Tuesday, September 19, 2006 9:35 AM
> *To:* David Sharples
> *Cc:* Anjo Kolk; _oracle_L_list
> *Subject:* Re: Block dump - Uncommitted TXN - Help Urgent
>
>
>
>
>
> On 9/19/06, *David Sharples* <davidsharples_at_gmail.com> wrote:
>
> why not just look at dba_blockers and dba_waiters - why are you doing
> block dumps?
>
>
>
> Find the blocking session and either kill it or call him and and ask him
> to commit the changes
>
>
>
> On 18/09/06, *BN* <bnsarma_at_gmail.com> wrote:
>
>
>
> On 9/18/06, *Anjo Kolk* <anjo.kolk_at_oraperf.com > wrote:
>
>
> I am sorry to be such a pain, but may be explaining what the problem is
> and what you want to do, will help me and others on this list.
>
> Anjo.
>
>
>
> On 9/18/06, *BN* <bnsarma_at_gmail.com> wrote:
>
> Greetings,
>
>
> Oracle 8.1.7.4
>
> I have taken a Block dump,
>
> Where do I look for missing commit?
>
>
>
>
> Greetings,
>
> Like I said I am able to identify this info from v$lock (request, lmode,
> block) and link it
> to v$session to get the object details.
>
> Initially I was thining that it was a missing "COMMIT" some where in the
> app, Later I saw a pattern.
>
> The Blocker (doing a TXN (DML) spanning many tables) was holding the lock
> a little longer, 10 secs are more. Being a very busy OLTP , this was causing
> others issues.
>
> I wasn't sure where the Blocker loosing his time, Later in the evening , I
> noticed that there were many ST enqueue locks (no, we are not using LMTS -
> For all new tables I am pushing LMTS). I am assuming that this could be an
> issue. Bumped up the next extent size. The tables were large and extents
> were in 1000s.
>
> I am also monitoring v$session_wait. Its intermittent and fast. The users
> complain that they are stuck and if they retry they are back to normal.
>
> I have been sitting on this all day yesterday, took some Level 12 traces
> and fixed some expensive queries , not sure if they helped or not. Those
> querues are running faster now.
>
> Again back to monitoring today ...
>
> Appreciate your ides/thoughts ....
> --
> Regards & Thanks
> BN
>

Greetings,

I was thinking of INITRANS , is there a way to track that from v$lock, I remember once Steve Adams answering some body looking at v$lock looking at Request Column, not sure though

-- 
Regards & Thanks
BN

--
http://www.freelists.org/webpage/oracle-l