Shulou(Shulou.com)06/01 Report--

Take a look at the tests done step by step and database performance tuning and record them. [@ more@]

Testing of UNIFORM SIZE

SQL > CREATE TABLESPACE eric DATAFILE

2'/ u01 AUTOEXTEND OFF App SIZE oradata AUTOEXTEND OFF orclUniplic01.dbf' oradata 10m

3 LOGGING

4 PERMANENT

5 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M

6 BLOCKSIZE 8K

7 SEGMENT SPACE MANAGEMENT MANUAL

8 FLASHBACK ON

Tablespace created.

SQL > select tablespace_name,extent_management,allocation_type

2 from dba_tablespaces where tablespace_name='ERIC'

TABLESPACE_NAME EXTENT_MAN ALLOCATIO

ERIC LOCAL UNIFORM

SQL > create table test

2 tablespace eric as

3 select * from dba_users where 1: 0

Table created.

SQL > select extent_id,block_id,blocks

2 from dba_extents where segment_name='TEST' and tablespace_name='ERIC'

EXTENT_ID BLOCK_ID BLOCKS

0 9 128

The creation of the test table starts with a block_id of 9, and the first 8 block are retained by the system. The data blocks 1 and 2 are used to record the information of the data file header, and the data blocks

3. 8 is used to record the bitmap information of the interval.

SQL > alter system dump datafile 9 block min 1 block max 3

System altered.

SQL > SELECT d.VALUE

2 | |'/'

3 | | LOWER (RTRIM (i.INSTANCE, CHR (0)

4 | |'_ ora_'

5 | | p.spid

6 | | '.trc' trace_file_name

7 FROM (SELECT p.spid

8 FROM v$mystat m, v$session s, v$process p

9 WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p

10 (SELECT t.INSTANCE

11 FROM v$thread t, v$parameter v

12 WHERE v.NAME = 'thread'

13 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE)) I

14 (SELECT VALUE

15 FROM v$parameter

16 WHERE NAME = 'user_dump_dest') d

17 /

TRACE_FILE_NAME

/ u01/app/admin/orcl/udump/orcl_ora_3868.trc

According to the generated trace file, we can find that the first two block records the information of the file header.

/ u01/app/admin/orcl/udump/orcl_ora_3868.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = / u01/app/product/10201/

System name: Linux

Node name: rhel131

Release: 2.6.9-42.ELsmp

Version: # 1 SMP Wed Jul 12 23:27:17 EDT 2006

Machine: i686

Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 15

Unix process pid: 3868, image: oracle@rhel131 (TNS V1-V3)

* 2008-10-22 03 Fraser 2215 66

* ACTION NAME: () 2008-10-22 03 purl 22 purl 09.564

* MODULE NAME: (sqlplus@rhel131 (TNS V1-V3)) 2008-10-22 03 purl 22 purl 09.564

* SERVICE NAME: (SYS$USERS) 2008-10-22 03 purl 22 purl 09.564

* SESSION ID: (159.6) 2008-10-22 03 purl 22 purl 09.564

Start dump data blocks tsn: 9 file#: 9 minblk 1 maxblk 3

Block 1 (file header) not dumped: use dump file header command

Buffer tsn: 9 rdba: 0x02400002 (9 Compact 2)

Scn: 0x0000.001398aa seq: 0x02 flg: 0x00 tail: 0x98aa1d02

Frmt: 0x02 chkval: 0x0000 type: 0x1d=KTFB Bitmapped File Space Header

Hex dump of block: st=0, typ_found=1

Dump of memory from 0x0CEB6800 to 0x0CEB8800

CEB6800 0000A21D 02400002 001398AA 00020000 [. @.]

CEB6810 00000000 00000009 00000080 00000500 [.]

CEB6820 00000001 00000000 00000000 00000007 [.]

CEB6830 00000488 00000001 00000008 00000000 [.]

CEB6840 00000000 00000000 00000000 [.]

CEB6850 00000009 00000080 00000000 00000000 [.]

CEB6860 00000000 00000000 00000000 [.]

Repeat 504 times

CEB87F0 00000000 00000000 00000000 98AA1D02 [.]

File Space Header Block:

Header Control:

RelFno: 9, Unit: 128, Size: 1280, Flag: 1

AutoExtend: NO, Increment: 0, MaxSize: 0

Initial Area: 7, Tail: 1160, First: 1, Free: 8

Deallocation scn: 0.0

Header Opcode:

Save: No Pending Op

Buffer tsn: 9 rdba: 0x02400003 (9 Compact 3)

Scn: 0x0000.001398aa seq: 0x01 flg: 0x00 tail: 0x98aa1e01

Frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap

Hex dump of block: st=0, typ_found=1

Dump of memory from 0x0CEB6800 to 0x0CEB8800

CEB6800 0000A21E 02400003 001398AA 00010000 [. @.]

CEB6810 00000000 00000009 00000009 00000000 [.]

CEB6820 00000001 0000F7FF 00000000 00000000 [.]

CEB6830 00000000 00000000 00000001 00000000 [.]

CEB6840 00000000 00000000 00000000 [.]

Repeat 506 times

CEB87F0 00000000 00000000 00000000 98AA1E01 [.]

File Space Bitmap Block:

BitMap Control:

RelFno: 9, BeginBlock: 9, Flag: 0, First: 1, Free: 63487

0100000000000000 0000000000000000 0000000000000000 0000000000000000

0000000000000000 0000000000000000 0000000000000000

0000000000000000 0000000000000000 0000000000000000

Starting from the third block, the information of the bitmap is recorded.

01 is converted from hexadecimal to binary is 0000 0001, and the result obtained by high-order and low-order byte exchange is 1000 0000 (little-Endian for linix platform)

Indicates that an interval is assigned

Let's assign another interval.

SQL > alter table test allocate extent

Table altered.

The bitmap becomes:

Buffer tsn: 9 rdba: 0x02400003 (9 Compact 3)

Scn: 0x0000.00139a4b seq: 0x01 flg: 0x00 tail: 0x9a4b1e01

Frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap

Hex dump of block: st=0, typ_found=1

Dump of memory from 0x0CEB6800 to 0x0CEB8800

CEB6800 0000A21E 02400003 00139A4B 00010000 [. @ .K.]

CEB6810 00000000 00000009 00000009 00000000 [.]

CEB6820 00000002 0000F7FE 00000000 00000000 [.]

CEB6830 00000000 00000000 00000003 00000000 [.]

CEB6840 00000000 00000000 00000000 [.]

Repeat 506 times

CEB87F0 00000000 00000000 00000000 9A4B1E01 [.K.]

File Space Bitmap Block:

BitMap Control:

RelFno: 9, BeginBlock: 9, Flag: 0, First: 2, Free: 63486

0300000000000000 0000000000000000 0000000000000000 0000000000000000

0000000000000000 0000000000000000 0000000000000000

03 stands for 0000 0011, which is 1100 0000 after high-low conversion, indicating that the first two intervals have been allocated.

Create another object in this data file, allocating four intervals

SQL > create table test2 tablespace eric

2 as select * from dba_objects where 1 / 2

Table created.

SQL > alter table test2 allocate extent

Table altered.

SQL > /

Table altered.

SQL > /

Table altered.

SQL > col segment_name for A10

SQL > select segment_name,extent_id,block_id,blocks

2 from dba_extents where file_id=9

SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS

--

TEST 0 9 128

TEST 1 137 128

TEST2 0 265 128

TEST2 1 393 128

TEST2 2 521 128

TEST2 3 649 128

6 rows selected.

Then dump the file header, and the interval bitmap has changed to:

Buffer tsn: 9 rdba: 0x02400003 (9 Compact 3)

Scn: 0x0000.00139d3d seq: 0x01 flg: 0x04 tail: 0x9d3d1e01

Frmt: 0x02 chkval: 0x4d8d type: 0x1e=KTFB Bitmapped File Space Bitmap

Hex dump of block: st=0, typ_found=1

Dump of memory from 0x0CEB6800 to 0x0CEB8800

CEB6800 0000A21E 02400003 00139D3D 04010000 [. @. =.]

CEB6810 00004D8D 00000009 00000009 00000000 [.M.]

CEB6820 00000006 0000F7FA 00000000 00000000 [.]

CEB6830 00000000 00000000 0000003F 00000000 [.?.]

CEB6840 00000000 00000000 00000000 [.]

Repeat 506 times

CEB87F0 00000000 00000000 00000000 9D3D1E01 [. =.]

File Space Bitmap Block:

BitMap Control:

RelFno: 9, BeginBlock: 9, Flag: 0, First: 6, Free: 63482

3F00000000000000 0000000000000000 0000000000000000 0000000000000000

0000000000000000 0000000000000000 0000000000000000

The conversion from 3F to binary is 0011 1111, and the result of high and low bit conversion is 1111 1100, that is, the first six intervals are allocated.

Create another object.

SQL > create table test3 tablespace eric

2 as select * from dba_objects where 1 / 2

Table created.

SQL > select segment_name,extent_id,block_id,blocks

2 from dba_extents where file_id=9

SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS

--

TEST 0 9 128

TEST 1 137 128

TEST2 0 265 128

TEST2 1 393 128

TEST2 2 521 128

TEST2 3 649 128

TEST3 0 777 128

7 rows selected.

The bitmap changes as follows:

/ u01/app/admin/orcl/udump/orcl_ora_3894.trc

File Space Bitmap Block:

BitMap Control:

RelFno: 9, BeginBlock: 9, Flag: 0, First: 7, Free: 63481

7F00000000000000 0000000000000000 0000000000000000 0000000000000000

The conversion of 7F to binary is 0111 1111, and the result of bit conversion is 1111 1110. The first 7 regions have been allocated.

Let's take a look at the drop drop test2 table.

SQL > drop table test2

Table dropped.

SQL > col segment_name for A10

SQL > select segment_name,extent_id,block_id,blocks

2 from dba_extents where file_id=9

SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS

--

TEST 0 9 128

TEST 1 137 128

TEST3 0 777 128

That's weird. Why is it still 7F? It is the same as before drop.

/ u01/app/admin/orcl/udump/orcl_ora_3897.trc

File Space Bitmap Block:

BitMap Control:

RelFno: 9, BeginBlock: 9, Flag: 0, First: 7, Free: 63481

7F00000000000000 0000000000000000 0000000000000000 0000000000000000

The last two trace files are compared below, and no differences are found.

[oracle@rhel131 ~] $diff / u01/app/admin/orcl/udump/orcl_ora_3894.trc

/ u01/app/admin/orcl/udump/orcl_ora_3897.trc

1c1

< /u01/app/admin/orcl/udump/orcl_ora_3894.trc --- >

/ u01/app/admin/orcl/udump/orcl_ora_3897.trc

13c13

< Unix process pid: 3894, image: oracle@rhel131 (TNS V1-V3) --- >

Unix process pid: 3897, image: oracle@rhel131 (TNS V1-V3)

15,19c15,19

< *** 2008-10-22 03:46:04.029 < *** ACTION NAME:() 2008-10-22 03:46:04.028 < *** MODULE NAME:(sqlplus@rhel131 (TNS V1-V3)) 2008-10-22 03:46:04.028 < *** SERVICE NAME:(SYS$USERS) 2008-10-22 03:46:04.028 < *** SESSION ID:(146.56) 2008-10-22 03:46:04.028 --- >

* 2008-10-22 03 Fraser 4715 50.875

> * ACTION NAME: () 2008-10-22 03 purl 47 purl 50.874

> * MODULE NAME: (sqlplus@rhel131 (TNS V1-V3)) 2008-10-22 03 purl 47 purl 50.874

> * SERVICE NAME: (SYS$USERS) 2008-10-22 03 purl 47 purl 50.874

> * SESSION ID: (146.61) 2008-10-22 03 purl 47 purl 50.874

Finally found the cause, it was caused by 10g recyclebin.

In drop test2, there is no information about emptying the Recycle Bin, so it is still recorded in the header that the space has been allocated. Try another table to know why.

SQL > drop table test purge

Table dropped.

Let's take a look at the file from dump.

File Space Bitmap Block:

BitMap Control:

RelFno: 9, BeginBlock: 9, Flag: 0, First: 0, Free: 63483

7C00000000000000 0000000000000000 0000000000000000 0000000000000000

0000000000000000 0000000000000000 0000000000000000

It becomes 7C, the conversion to binary is 0111 1100, and after high and low bit conversion, it becomes 0011 1110.

Come again, purge test2.

SQL > purge table test2

Table purged.

The message from dump is:

File Space Bitmap Block:

BitMap Control:

RelFno: 9, BeginBlock: 9, Flag: 0, First: 0, Free: 63487

4000000000000000 0000000000000000 0000000000000000 0000000000000000

It becomes 40, the conversion to binary is 0100 0000, and the conversion from high to low becomes 0000 0010.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.