Bank migration/Row link Learning in Oracle Database (ii) Row migration/Row link detection method

Through the previous introduction we know that the main line link is due to the db_block_size of the database is not large enough for some large fields can not be stored in a block, resulting in a row link. For row links We have nothing to avoid except to increase db_block_size, but since the database was built Db_block_size is immutable (before 9i), we can specify different table spaces for the oracle9i database db_ Block_size, therefore, the production of line links is almost inevitable, there is not much to adjust the place. Row migration is mainly due to the fact that the table's Pctfree parameter settings are too small to allow enough space in the block to accommodate the updated records, resulting in a row migration. It is very necessary to adjust the row migration, because this can adjust and control the cleanup.

How do I detect that there are row migrations and row links in the database? We can generate Chained_rows tables using the Script Utlchain.sql (in the $oracle_home/rdbms/admin directory) provided by the ORACLE database, and then take advantage of analyze table table_name list The CHAINED ROWS into Chained_rows command analyzes the tables one by one and stores the results of the analysis in the Chained_rows table. From the Utlchain.sql script we see the Chained_rows table script, and for the partitioned table, the cluster table is applicable. You can then use a patchwork of statements to generate a script that analyzes the tables you need, and execute the script to put the specific profiling data into the Chained_rows table, such as the following script that analyzes all the tables under a user:

SPOOL List_migation_rows.sql

SET ECHO off

SET HEADING off

SELECT ' ANALYZE TABLE ' | | table_name | | ' LIST CHAINED ROWS into chained_rows; ' From User_tables;

SPOOL off

Then query the Chained_rows table to see how many row links and row migrations are on a particular table.

SELECT table_name, COUNT (*) from chained_rows GROUP by table_name;

Of course, you can also query the ' table fetch continued row ' column in the V$sysstat view to get the current number of row links and row migrations.

SELECT name, value from v$sysstat WHERE name = ' Table fetch continued row ';

You can use the following script to find a table with row links and row migrations, automatically completing all the analysis and statistics.

Accept owner Prompt "Enter the schema name to check for Row chaining (return for all):"

Prompt

Prompt

Accept table Prompt "Enter the table name to check (return for all tables owned by &owner):"

Prompt

Prompt

Set head off serverout on term on feeds off veri off echo off

!clear

Prompt

Declare

V_owner VARCHAR2 (30);

V_table VARCHAR2 (30);

V_chains number;

V_rows number;

V_count number: = 0;

sql_stmt varchar2 (100);

Dynamiccursor INTEGER;

Dummy INTEGER;

Cursor Chains is

Select COUNT (*) from chained_rows;

Cursor Analyze is

Select owner, TABLE_NAME

From Sys.dba_tables

where owner like Upper ('%&owner% ')

and table_name like UPPER ('%&table% ')

ORDER BY TABLE_NAME;

Begin

Dbms_output.enable (64000);

Open analyze;

Fetch analyze into V_owner, v_table;

While Analyze%found loop

Dynamiccursor: = Dbms_sql.open_cursor;

SQL_STMT: = ' Analyze table ' | | v_owner| | '. ' | | v_table| | ' list chained rows into Chained_rows ';

Dbms_sql.parse (Dynamiccursor, sql_stmt, dbms_sql.native);

Dummy: = Dbms_sql.execute (Dynamiccursor);

Dbms_sql.close_cursor (Dynamiccursor);

Open chains;

Fetch chains into v_chains;

if (v_chains!= 0) Then

if (v_count = 0) Then

Dbms_output.put_line (CHR (9) | | CHR (9) | | CHR (9) | | <<<<< Chained Rows Found >>>>> ');

V_count: = 1;

End If;

Dynamiccursor: = Dbms_sql.open_cursor;

Sql_stmt: = ' Select count (*) V_rows ' | | From ' | | v_owner| | '. ' | | v_table;

Dbms_sql.parse (Dynamiccursor, sql_stmt, dbms_sql.native);

Dbms_sql. Define_column (Dynamiccursor, 1, v_rows);

Dummy: = Dbms_sql.execute (Dynamiccursor);

Dummy: = Dbms_sql.fetch_rows (Dynamiccursor);

Dbms_sql. Column_value (Dynamiccursor, 1, v_rows);

Dbms_sql.close_cursor (Dynamiccursor);

Dbms_output.put_line (v_owner| | | v_table);

Dbms_output.put_line (CHR (9) | | ---> has ' | | v_chains| | ' Chained Rows and ' | | v_rows| | ' Num_rows in it! ');

Dynamiccursor: = Dbms_sql.open_cursor;

SQL_STMT: = ' truncate table chained_rows ';

Dbms_sql.parse (Dynamiccursor, sql_stmt, dbms_sql.native);

Dummy: = Dbms_sql.execute (Dynamiccursor);

Dbms_sql.close_cursor (Dynamiccursor);

V_chains: = 0;

End If;

Close chains;

Fetch analyze into V_owner, v_table;

End Loop;

if (v_count = 0) Then

Dbms_output.put_line (' No Chained Rows found in the ' | | v_owner| | ' owned tables! ');

End If;

Close analyze;

End

/

Set feed on head

Prompt

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45555.htm