RE: (RE): Calculating LIOs

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Fri, 13 Aug 2004 13:27:36 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09660D10@bosmail00.bos.il.pqe>

Try using DBMS_SPACE.UNUSED_SPACE. =20

It should be pretty self-explanatory:

SQL> desc dbms_space
PROCEDURE FREE_BLOCKS

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SEGMENT_OWNER                  VARCHAR2                IN
 SEGMENT_NAME                   VARCHAR2                IN
 SEGMENT_TYPE                   VARCHAR2                IN
 FREELIST_GROUP_ID              NUMBER                  IN
 FREE_BLKS                      NUMBER                  OUT
 SCAN_LIMIT                     NUMBER                  IN     DEFAULT
 PARTITION_NAME                 VARCHAR2                IN     DEFAULT
PROCEDURE SPACE_USAGE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SEGMENT_OWNER                  VARCHAR2                IN
 SEGMENT_NAME                   VARCHAR2                IN
 SEGMENT_TYPE                   VARCHAR2                IN
 UNFORMATTED_BLOCKS             NUMBER                  OUT
 UNFORMATTED_BYTES              NUMBER                  OUT
 FS1_BLOCKS                     NUMBER                  OUT
 FS1_BYTES                      NUMBER                  OUT
 FS2_BLOCKS                     NUMBER                  OUT
 FS2_BYTES                      NUMBER                  OUT
 FS3_BLOCKS                     NUMBER                  OUT
 FS3_BYTES                      NUMBER                  OUT
 FS4_BLOCKS                     NUMBER                  OUT
 FS4_BYTES                      NUMBER                  OUT
 FULL_BLOCKS                    NUMBER                  OUT
 FULL_BYTES                     NUMBER                  OUT
 PARTITION_NAME                 VARCHAR2                IN     DEFAULT
PROCEDURE UNUSED_SPACE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SEGMENT_OWNER                  VARCHAR2                IN
 SEGMENT_NAME                   VARCHAR2                IN
 SEGMENT_TYPE                   VARCHAR2                IN
 TOTAL_BLOCKS                   NUMBER                  OUT
 TOTAL_BYTES                    NUMBER                  OUT
 UNUSED_BLOCKS                  NUMBER                  OUT
 UNUSED_BYTES                   NUMBER                  OUT
 LAST_USED_EXTENT_FILE_ID       NUMBER                  OUT
 LAST_USED_EXTENT_BLOCK_ID      NUMBER                  OUT
 LAST_USED_BLOCK                NUMBER                  OUT
 PARTITION_NAME                 VARCHAR2                IN     DEFAULT

Pass it SEGMENT_OWNER, SEGMENT_NAME, SEGMENT_TYPE, and it will return = TOTAL_BLOCKS and UNUSED_BLOCKS, among other things. = TOTAL_BLOCKS-UNUSED_BLOCKS is the number of blocks below HWM that a full = scan operation will have to read.

-Mark

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of ryan.gaffuri_at_comcast.net
Sent: Friday, August 13, 2004 1:18 PM
To: oracle-l_at_freelists.org
Subject: RE: (RE): Calculating LIOs

How do you tell what the HWM is of indexes? I did not see a blocks = column in DBA_INDEXES?=20
Also, I would assume that an 'index full scan' also reads up to the HWM. = I have not been able to figure out what the difference between an 'index = fast full scan' and an 'index full scan'. I thought it was that a fast = full scan read at your multiblock read count and a full scan read 1 = block at a time. However, some people on here said that a full scan can = read at your multiblock read(however, I have never been able to get this = to happen with several tests)?=20
-------------- Original message --------------=20

> Ryan,=20

>=20

> D'oh, I should have mentioned the HWM (High Water Mark).=20

>=20

> In the case of a FULL TABLE SCAN (or a FAST FULL INDEX SCAN on an =3D=20
> index), Oracle will read up to the HWM. That is, the point at which =
=3D=20
> data is now or has ever been stored. Blocks above the HWM are known to =
=3D=20
> be empty, so Oracle does not read them. If you have a table that =3D=20
> contained 100 million rows, and you delete 80 million of them, the HWM =
=3D=20
> will still be at the point where it was when the table contained 100 =
=3D=20
> million rows. One can lower the HWM by re-organizing the table. In the =
=3D=20

---

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
----------------------------------------------------------------
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
-----------------------------------------------------------------

Received on Fri Aug 13 2004 - 12:23:21 CDT