Shulou(Shulou.com)05/31 Report--

This article mainly explains the "oracle table optimization method tutorial", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and study the "oracle table optimization method tutorial"!

1. Set up the experimental table

Create table t_pctfree

(

Id number

Name varchar2 (2000)

Name1 varchar2 (2000)

Name2 varchar2 (2000)

Name3 varchar2 (2000)

Name4 varchar2 (2000)

)

Tablespace users

Pctfree 10

Table created.

2. To add data, write only id first, and other values are null.

SQL > insert into t_pctfree (id) values (2)

1 row created.

SQL > commit

Commit complete.

3. Use dump to view data blocks and make sure that data with an id of 2 exists only in one block.

SQL > select dbms_rowid.rowid_block_number (rowid) block_id from t_pctfree where id=2

BLOCK_ID

-

47547

SQL > alter system dump datafile 5 block 51031

System altered.

SQL > oradebug setmypid

Statement processed.

SQL > oradebug tracefile_name

/ u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc

[oracle@localhost trace] $more / u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc

Tab 0, row 0, @ 0x1f92

Tl: 2 fb:-- HDFL-- lb: 0x2

Tab 0, row 1, @ 0x1f8c

Tl: 6 fb:-- HmurFLmuri-lb: 0x0 cc: 1

Col 0: [2] c1 03

Tab 0, row 2, @ 0x1f86

SQL > var n number

SQL > exec dbms_stats.convert_raw_value ('c103jewelry Magazine n)

PL/SQL procedure successfully completed.

SQL > print: n

N

-

two

4. Modify the data with an id of 2 so that the remaining space in the current block cannot accommodate the modified data.

SQL > update scott.t_pctfree set name=dbms_random.string ('Upright, 2000) where id=2

1 row updated.

SQL > commit

Commit complete.

SQL > alter system switch logfile

System altered.

SQL > /

System altered.

SQL > /

System altered.

.

5. View the status of the modified block

SQL > select dbms_rowid.rowid_block_number (rowid) block_id from t_pctfree where id=2

BLOCK_ID

-

47547

SQL > alter system dump datafile 5 block 51031

System altered.

SQL > oradebug setmypid

Statement processed.

SQL > oradebug tracefile_name

/ u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc

[oracle@localhost trace] $more / u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc

Tab 0, row 1, @ 0xdec

Tl: 9 fb:-- H-lb: 0x1 cc: 0

Nrid: 0x0140c75b.0

6. It is found that row migration has been generated, and the source block only leaves the address of the migration block. Check the information of the migration target block through nrid.

SQL > select dbms_utility.DATA_BLOCK_ADDRESS_FILE (to_number ('0140c75b)) file#,dbms_utility.DATA_BLOCK_ADDRESS_BLOCK (to_number (' 0140c75b)) block# from dual

FILE# BLOCK#

--

551035

SQL > alter system dump datafile 5 block 51035

System altered.

SQL > oradebug setmypid

Statement processed.

SQL > oradebug tracefile_name

/ u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc

[oracle@localhost trace] $more / u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc

Tab 0, row 0, @ 0x17a1

Tl: 2015 fb:-FL-- lb: 0x1 cc: 2

Hrid: 0x0140b9bb.1

Col 0: [2] c1 03

Col 1: [2000]

5a 52 57 58 53 54 45 4a 50 4e 56 43 4c 55 4e 4e 4d 47 59 49 51 50 44 41 41

4b 4d 47 56 52 49 5a 51 55 47 54 54 5a 51 41 49 5a 55 57 43 58 46 42 54 55

7. You can see that the target block of migration is only hrid, but there is no nrid, which means that there is only row migration and no line link.

Thank you for your reading, the above is the content of the "oracle optimization method tutorial", after the study of this article, I believe you have a deeper understanding of the oracle optimization method tutorial, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.