| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Lock Monitor Question
A copy of this was sent to markp28665_at_aol.com (MarkP28665) (if that email address didn't require changing) On 28 Jan 1998 23:46:15 GMT, you wrote:
>From: tkyte_at_us.oracle.com (Thomas Kyte) >>
>While replying to original post on lock question stated that >>
>Oracle maintains row level locks right with the data. In order to find if a
>row is locked, we goto the row. <<
>
>Thomas, I believe what you say and I am glad to know this, but can you tell the
>newsgroup how Oracle handles row locks for rows contained in a dirty buffer
>pool block that gets flushed to disk to make room in the pool before it is
>commited? Does the rdbms have to re-read and re-write the block again to
>remove the row level locks or does it transfer them to memory? Are they stored
>in the initrans work area or taken from free space?
>
They are part of the transaction list that is stored on the block. initrans is used to control how big this structure is for every block (1 for data blocks, 2 for index blocks by default). this structure can grow to be MAXTRANS entries in size given that sufficient free space is on the block.
This information lives on the block and might be flushed to disk. When you commit, Oracle does not try to clean out this information if it was flushed (7.3 added a fast cleanout feature which cleans most blocks cached but not flushed blocks and some other cases). The next person who goes after the block cleans it out. This is called delayed block cleanout. In earlier releases, you had no control over how this was handled. In 7.3, they added the DELAYED_LOGGING_BLOCK_CLEANOUTS init.ora parameter.
Here is a description of it from the server reference:
<quote>
When Oracle commits a transaction, each block that the transaction
changed is not immediately marked with the commit time. This is done
later, upon demand––when the block is read or updated. This is called
block cleanout.
When block cleanout is done during an update to a current block, the cleanout changes and the redo records are piggybacked with those of the update. In previous releases, when block cleanout was needed during a read to a current block, extra cleanout redo records were generated and the block was dirtied. This has been changed. As of release 7.3, when a transaction commits, all blocks changed by the transaction are cleaned out immediately. This cleanout performed at commit time is a “fast version†which does not generate redo log records (delayed logging) and does not repin the block. Most blocks will be cleaned out in this way, with the exception of blocks changed by long running transactions.
During queries, therefore, the data block’s transaction information is normally up–to–date and the frequency of needing block cleanout is much reduced. Regular block cleanouts are still needed when querying a block where the transactions are still truly active, or when querying a block which was not cleaned out during commit.
</quote>
>Also, the original poster and othera may be interested to know that starting
>with ver 7.2 the v$session table has 4 columns that contain the rowid that a
>session is locked on. The numbers are in decimal if my memory is correct and
>have to be converted to hex to match the results of a select rowid. Maybe
>someone out there can generate the code to translate the decimal to hex
>representation. I would like to try, but keep having other people ask me to do
>work for them..
>
Ok, if you install my hexdec package (below, converts from lots of bases to lots of other bases) you can run the following query in 7.2 and up
select sid, serial#, a.username,
'select * from ' ||
b.owner || '.' || b.object_name ||
' where rowid = ''' ||
lpad(to_hex(a.ROW_WAIT_BLOCK#),8,'0') || '.' ||
lpad(to_hex(a.ROW_WAIT_ROW#),4,'0') || '.' ||
lpad(to_hex(a.ROW_WAIT_FILE#),4,'0') || '''' "Is Waiting on Row"
from v$session a, all_objects b
It'll generate output like:
SID SERIAL# USERNAME Is Waiting on Row
---------- ---------- --------------- ------------------------------------
7 451 SCOTT select * from SCOTT.EMP where rowid =
'0000044A.0008.0004'
this shows that SCOTT (identified by 7,451 -- you can use this in an alter system kill session '7,451'; command) is blocked on the EMP table waiting for the row identified by the rowid in the query.
>Please carbon me on post if possible.
>
>Mark Powell -- Oracle 7 Certified DBA
>- The only advice that counts is the advice that you follow so follow your own
>advice -
create or replace function to_base( p_dec in number, p_base in number )
return varchar2
is
l_str varchar2(255) default NULL; l_num number default p_dec; l_hex varchar2(16) default '0123456789ABCDEF'; begin if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then raise PROGRAM_ERROR; end if; loop l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str; l_num := trunc( l_num/p_base ); exit when ( l_num = 0 ); end loop; return l_str;
create or replace function to_dec
( p_str in varchar2,
p_from_base in number default 16 ) return number
is
l_num number default 0; l_hex varchar2(16) default '0123456789ABCDEF'; begin for i in 1 .. length(p_str) loop l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1; end loop; return l_num;
create or replace function to_hex( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 16 );
end to_hex;
/
create or replace function to_bin( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 2 );
end to_bin;
/
create or replace function to_oct( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 8 );
end to_oct;
/
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jan 29 1998 - 00:00:00 CST
 |
 |