RE: locking issues

Hi Joe,

You may have a PCTFREE of just 1 or 0 that is causing this, or you may have row lengths such that is it possible to fill the block entirely with a higher PCTFREE. In the latter case it may be better to rebuild with INITRANS 2 rather than increasing PCTFREE. However, in general the problem is an inadequate PCTFREE setting.

Regards,
Steve Adams

http://www.ixora.com.au/
http://www.oreilly.com/catalog/orinternals/
http://www.christianity.net.au/

-----Original Message-----
From: jtesta_at_mail.the-word.net [mailto:jtesta_at_mail.the-word.net]On Behalf Of Joseph S. Testa
Sent: Tuesday, 8 August 2000 9:47
To: Steve Adams
Cc: Multiple recipients of list ORACLE-L Subject: Re: locking issues

Steve, we were thinking that but looking at oracle paper on it, didnt really point that as the issue. out initrans is 1 and maxtrans is 255. so in theory what would the pctfree be that would be causing this as an issue?

thanks, joe
Steve Adams wrote:
>
> Hi Joe,
>
> The fact that you are waiting for a shared TX lock indicates that the
> duhvelopers are right. It is an ITL entry shortage. Look for
inadequate
> PCTFREE settings. This is treated in more detail on page 48 of my
book.
>
> Regards,
> Steve Adams
> http://www.ixora.com.au/
> http://www.oreilly.com/catalog/orinternals/
> http://www.christianity.net.au/
>
> -----Original Message-----
> From: Joseph S. Testa [mailto:teci_at_oracle-dba.com]
> Sent: Tuesday, 8 August 2000 4:55
> To: Multiple recipients of list ORACLE-L
> Subject: locking issues
>
> I'm hitting up against locking(including deadlock) issues. Here is
what
> we've done so far:
>
> using utllockt, dba_waiters, etc all of the normal delivered stuff has
> been used.
>
> I'm sure there is a way to see what the values of bind variables are,
i
> think its an event, but i dont remember.
>
> According to the duhvelopers, its an oracle issue not an app issue(i
> disagree totally), short of me going thru their code line by line(and
i
> want to hurt someone since it has goto statements in a stored proc),
is
> there anything else i can do to determine the sql(on deadlock i get a
> trace file so i can see what is happening).
>
> At first it we thought it had to do with a bitmap index but we
converted
> it to a normal index and it didnt go away.
>
> here is part of the deadlock trace file(we're already ran the event
for
> 60, to get more info).
>
> It appears that there is a share lock issue, anyone willing to take a
> stab at this?
>
> DEADLOCK DETECTED
> Current SQL statement for this session:
> INSERT INTO ATTEMPT_STATE (
> ATTEMPT_STATE_OID,ATTEMPT_OID,ACTUAL_DATE,PLAN_DATE,
> SEQUENCE_NUMBER,NAME ) VALUES ( :b1,:b2,:b3, NULL ,:b4,:b5 )
> ----- PL/SQL Call Stack -----
> object line object
> handle number name
> 82feecd4 296 package body FTC.ATTEMPT_STATE_CHANGE_PKG
> 82ff75f0 1 anonymous block
> The following deadlock is not an ORACLE error. It is a
> deadlock due to user error in the design of an application
> or from issuing incorrect ad-hoc SQL. The following
> information may aid in determining the deadlock:
> Deadlock graph:
> ---------Blocker(s)-------- ---------Waiter(s)

--


> -------

> Resource Name          process session holds waits  process session

> holds waits

> TX-0002001d-0003050c        12      20     X             13      18

> S

> TX-00050022-000302db        13      18     X             12      20

> S

> session 20: DID 0001-000C-00000002      session 18: DID

> 0001-000D-00000002

> session 18: DID 0001-000D-00000002      session 20: DID

> 0001-000C-00000002

> Rows waited on:

> Session 18: no row

> Session 20: no row

>

> thanks, joe

>

> --

> Author: Joseph S. Testa

>   INET: teci_at_oracle-dba.com

>

> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

> San Diego, California        -- Public Internet access / Mailing Lists

> --------------------------------------------------------------------

> To REMOVE yourself from this mailing list, send an E-Mail message

> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> the message BODY, include a line containing: UNSUB ORACLE-L

> (or the name of mailing list you want to be removed from).  You may

> also send the HELP command for other information (like subscribing).