八、如何鉴别行链接和行迁移
-
-
- SELECT count(e) FROM row_mig_chain_demo;
-
- COUNT(E)
-
- 1
-
- SELECT a.name, b.value
- FROM v$statname a, v$mystat b
- WHERE a.statistic# = b.statistic#
- AND lower(a.name) = 'table fetch continued row';
-
- NAME VALUE
-
- table fetch continued row 5
-
-
-
-
- ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;
-
- SELECT chain_cnt
- FROM user_tables
- WHERE table_name = 'ROW_MIG_CHAIN_DEMO';
-
- CHAIN_CNT
-
- 3
-
-
-
-
-
-
-
- sqlplus system/<password>
-
- SELECT 'Chained or Migrated Rows = '||value
- FROM v$sysstat
- WHERE name = 'table fetch continued row';
-
- Chained or Migrated Rows = 31637
-
-
-
-
-
-
-
-
-
-
-
-
-
- SELECT name,value FROM v$sysstat WHERE name like '%table%';
-
- NAME VALUE
-
- table scans (short tables) 124338
- table scans (long tables) 1485
- table scans (rowid ranges) 0
- table scans (cache partitions) 10
- table scans (direct read) 0
- table scan rows gotten 20164484
- table scan blocks gotten 1658293
- table fetch by rowid 1883112
- table fetch continued row 31637
- table lookup prefetch client count 0
|
九、一个表上链接的行是多少?
-
- ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;
-
- SELECT chain_cnt,
- round(chain_cnt/num_rows*100,2) pct_chained,
- avg_row_len, pct_free , pct_used
- FROM user_tables
- WHERE table_name = 'ROW_MIG_CHAIN_DEMO';
-
- CHAIN_CNT PCT_CHAINED AVG_ROW_LEN PCT_FREE PCT_USED
-
- 3 100 3691 10 40
-
|
十、列出链接行
当使用analyze table中的list chained rows子句能够列出一个表上的链接行。该命令的结果是将所有的链接上存储到一个由list chained rows子句显示指定的表中。 这些结构有助于决定是否将来有足够的空间实现行更新。
创建CHAINED_ROWS 表
创建一个用于存储analyze ... list chained rows命令结果的表,可以执行位于$ORACLE_HOME/rdbms/admin目录下的UTLCHAIN.SQL或UTLCHN1.SQL 脚本。
这个脚本会在当前schema下创建一个名为chained_rows的表
create table CHAINED_ROWS ( owner_name varchar2(30), table_name varchar2(30), cluster_name varchar2(30), partition_name varchar2(30), subpartition_name varchar2(30), head_rowid rowid, analyze_timestamp date ); |
当chained_rows表创建后,可以使用analyze table命令来指向该表作为输出。