Re: How do commits release row level locks?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 17 Feb 2004 10:06:15 -0000
Message-ID: <016a01c3f53d$ae199c30$6702a8c0@Primary>

I think Arup has been doing a lot of work with RAC recently, and the article assumes that you may have multiple free list group blocks.

Moreover, if you have a default DBCA install of 9.2, then your tablespace is probably ASSM (as mentioned in Arup's last paragraph), and can't have multiple freelist group blocks.

The first potential data block will be immediately after the segment header block; but there is a problem with ASSM because the first batch of blocks at the start of the first extent may not be the first block formatted.

For ASSM the first extent blocks will usually be:

    Level 1 bitmap
    Level 2 bitmap
    Segment header block

Available data blocks.

For large extent sizes, the may be more than one Level 1 bitmap block before the level 2 bitmap block.

I tend to look at dba_segments for the location of the segment header block (header_file, header_block), then look at dba_extents, in order for the start of the actual extents. (file_id, block_id).

Historically, file_id, block_id of extent ZERO used to match header_file, header_block of dba_segmentss; but with ASSM, this is no longer true.

To find out where your data blocks are on a SMALL test, you need to dump the first block of the first extent to find out which blocks in the extent are formatted. In general, though, for a large, busy, table, all the space in everything but the last extent has probably been formatted and used.

My general approach to picking a few blocks are random is to do

select file_id, block_id + 4 from dba_extents where ... This usually picks one data block from each extent.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

Next public appearances:
March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___February
____UK___June

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message ----- From: "Naveen, Nahata (IE10)" <Naveen.Nahata_at_honeywell.com> To: <oracle-l_at_freelists.org> Sent: Tuesday, February 17, 2004 8:40 AM Subject: RE: How do commits release row level locks?

Thanx Jonathan, Anjo and Jesper!

I have an additional question. I'm referring to the article by Arup: http://www.dbazine.com/nanda3.html

In that he explains the way to identify the first "Data Block" in the file by dumping the "Header Block". These are the steps he lists:

Find the Header Block using the query "SELECT Header_File, Header Block FROM Dba_Segments WHERE ....."
Dump the Header Block
Find the section similar to the following: Extent Control Header
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 10
Find the real no. of blocks for the segment from DBA_SEGMENTS via "SELECT Blocks FROM Dba_Segments WHERE ..... "
Subtract the value of #blocks (Step 3) from the value obtained via Step 4
Step 5 gives the no. of Header Blocks.

In my case, the result of Step 3 and Step 4 is the same. How do I find out the no. of Header Blocks?

9.2.0.3 On Solaris (The tablespace in question is LMT with Uniform Extents, Segment Space Management = Auto)

Regards
Naveen

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Please see the official ORACLE-L FAQ: http://www.orafaq.com

Received on Tue Feb 17 2004 - 04:06:15 CST