oracle 行链接的模拟及消除示例

最新推荐文章于 2024-10-14 22:42:36 发布
最新推荐文章于 2024-10-14 22:42:36 发布
阅读量115 收藏
点赞数
文章标签： 数据库
Migration和Chaining

    1）如果一行的数据太大以至一个单独的block容纳不下，会产生两种现象：
    A、Chaining：行数据太大以至一个空block容纳不下，oracle会将这一行的数据存放在一个或多个block 组成的block chain中，insert、update都可能导致这个问题，在某些情况下row chaining是不能避免的。
    B、Migration：一次update操作可能导致行数据增大，以至它所在的block容纳不下，oracle server会去寻找一个有足够自由空间容纳整行数据的block，如果这样的block存在，oracle server把整行移到新的block，在原位置保存一个指向新存放位置的镜像行，镜像行的rowid和原来的rowid一致。
    Chaining、Migration的弊端：insert、update的性能降低，索引查询增加了IO次数。

    2）检测migration和chaining：
    Analyize table table_name compute statistics；
    Select num_rows,chain_cnt from dba_tables where table_name=’...’;
    查询镜像行：
    Analyize table table_name list chained rows；
    Select owner_name,table_name,head_rowid from chained_rows where table_name=’...’;
    产生Migration的原因可能是由于PCTFREE设置的太低以至没有保留足够的空间用于更新。
    可以通过增加PCTFREE的值避免行镜像产生。

    3）消除镜像行的步骤：
    运行analyize table ... list chained rows;
    复制镜像行到另一个表tmp；
    从源表中删除这些行；
    从tmp中将这些行插回到源表中。
    脚本：
    /* Get the name of the table with migrated rows */
    accept table_name prompt ’Enter the name of the table with migrated rows: ’
    /* Clean up from last execution */
    set echo off
    drop table migrated_rows;
    drop table chained_rows;
    /* Create the CHAINED_ROWS table */
    @?/rdbms/admin/utlchain
    set echo on
    spool fix_mig
    /* List the chained & migrated rows */
    analyze table &table_name list chained rows;
    /* Copy the chained/migrated rows to another table */
    create table migrated_rows as
    select orig.* from &table_name orig, chained_rows cr
    where orig.rowid = cr.head_rowid
    and cr.table_name = upper(’&table_name’);
    /* Delete the chained/migrated rows from the original table */
    delete from &table_name
    where rowid in ( select head_rowid from chained_rows );
    /* Copy the chained/migrated rows back into the original table */
    insert into &table_name select * from migrated_rows;
    spool off
    使用这个脚本时，必须将涉及到的外键约束去掉。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
                                                                            示例
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

SYS@primary/2011-04-25 12:03:34>create table table_2 tablespace assm  as select rownum id,mod(rownum,20) t1,mod(rownum,1000) t2,trunc(dbms_random.normal*10000)+5432 as t3,dbms_random.string('U',50) as pad from dual connect by level<=100000;

Table created.

Elapsed: 00:00:18.02

SYS@primary/2011-04-25 12:04:32>set linesize 60
SYS@primary/2011-04-25 12:04:37>desc table_2
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 ID                                     NUMBER
 T1                                     NUMBER
 T2                                     NUMBER
 T3                                     NUMBER
 PAD                                    VARCHAR2(4000)

set linesize 220
col owner for a8
col table_name for a8 heading "NAME"
col num_rows for 999999
col blocks for 999999
col empty_blocks for 99999 heading "EMPTY"
col avg_row_len for 99999
col row_movement for a8
col cache for a5
col chain_cnt for 99999
col last_analyzed for a20
col partitioned for a5 heading "PART"
select owner,table_name,num_rows,blocks,avg_row_len,empty_blocks,
chain_cnt,last_analyzed,row_movement,cache,partitioned
from dba_tables
where table_name = upper('&tablename')
  5  /
Enter value for tablename: table_2

OWNER    NAME        ROWS  BLOCKS AVG_ROW_LEN  EMPTY CHAIN_CNT LAST_ANALYZED        ROW_MOVE CACHE PART
-------- -------- ------- ------- ----------- ------ --------- -------------------- -------- ----- -----
SYS      TABLE_2   100000    1033          70    119         0 2011-04-25 12:05:05  DISABLED     N NO

1 row selected.

Elapsed: 00:00:00.03


set linesize 200
col owner for a10
col segment_name for a15
col segment_type for a10
col tablespace_name for a10
col file_id for 9999
col extent_id for 9999
col block_id for 9999999
col blocks for 99999
select owner,segment_name,segment_type,
tablespace_name,file_id,extent_id,block_id,bytes,blocks
from dba_extents
where  segment_name=upper('&seg_name')
  5  /
Enter value for seg_name: table_2

OWNER      SEGMENT_NAME    SEGMENT_TY TABLESPACE FILE_ID EXTENT_ID BLOCK_ID      BYTES BLOCKS
---------- --------------- ---------- ---------- ------- --------- -------- ---------- ------
SYS        TABLE_2         TABLE      ASSM             6         0     1104      65536      8
SYS        TABLE_2         TABLE      ASSM             6         1     1112      65536      8
SYS        TABLE_2         TABLE      ASSM             6         2     1120      65536      8
SYS        TABLE_2         TABLE      ASSM             6         3     1128      65536      8
SYS        TABLE_2         TABLE      ASSM             6         4     1136      65536      8
SYS        TABLE_2         TABLE      ASSM             6         5     1144      65536      8
SYS        TABLE_2         TABLE      ASSM             6         6     1152      65536      8
SYS        TABLE_2         TABLE      ASSM             6         7     1160      65536      8
SYS        TABLE_2         TABLE      ASSM             6         8     1168      65536      8
SYS        TABLE_2         TABLE      ASSM             6         9     1176      65536      8
SYS        TABLE_2         TABLE      ASSM             6        10     1184      65536      8
SYS        TABLE_2         TABLE      ASSM             6        11     1192      65536      8
SYS        TABLE_2         TABLE      ASSM             6        12     1200      65536      8
SYS        TABLE_2         TABLE      ASSM             6        13     1208      65536      8
SYS        TABLE_2         TABLE      ASSM             6        14     1216      65536      8
SYS        TABLE_2         TABLE      ASSM             6        15     1224      65536      8
SYS        TABLE_2         TABLE      ASSM             6        16     1280    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        17     1408    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        18     1536    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        19     1664    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        20     1792    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        21     1920    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        22     2048    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        23     2176    1048576    128

24 rows selected.


SYS@primary/2011-04-25 12:15:23>desc chained_rows
ERROR:
ORA-04043: object chained_rows does not exist


SYS@primary/2011-04-25 12:16:16>@$ORACLE_HOME/rdbms/admin/utlchain.sql

Table created.

Elapsed: 00:00:00.04

SYS@primary/2011-04-25 12:17:19>analyze table table_2 list chained rows;

Table analyzed.

Elapsed: 00:00:00.01
SYS@primary/2011-04-25 12:18:20>select * from chained_rows where table_name=upper('table_2');

no rows selected

Elapsed: 00:00:00.00

SYS@primary/2011-04-25 12:20:09>create index ind__table_2_pad on table_2 (pad);

Index created.

Elapsed: 00:00:01.23

SYS@primary/2011-04-25 12:20:16>select count(*) from table_2 where id between 10000 and 20000 and pad like 'KA%';

  COUNT(*)
----------
        11

1 row selected.

Elapsed: 00:00:00.04

SYS@primary/2011-04-25 12:23:39>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7ut6n7azbaddb, child number 0
-------------------------------------
select count(*) from table_2 where id between 10000 and 20000 and pad
like 'KA%'

Plan hash value: 2092335737

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |       |       |    20 (100)|          |
|   1 |  SORT AGGREGATE              |                  |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TABLE_2          |    11 |   594 |    20   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND__TABLE_2_PAD |    17 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("ID"<=20000 AND "ID">=10000))
   3 - access("PAD" LIKE 'KA%')
       filter("PAD" LIKE 'KA%')


SYS@primary/2011-04-25 12:23:41>select /*+ full(table_2)*/ count(*) from table_2 where id between 10000 and 20000 and pad like 'KA%';

  COUNT(*)
----------
        11

1 row selected.

Elapsed: 00:00:00.04
SYS@primary/2011-04-25 12:24:53>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  23bsz4wjw3hjs, child number 0
-------------------------------------
select /*+ full(table_2)*/ count(*) from table_2 where id between 10000
and 20000 and pad like 'KA%'

Plan hash value: 1055150476

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       |   284 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| TABLE_2 |    11 |   594 |   284   (1)| 00:00:04 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("ID"<=20000 AND "PAD" LIKE 'KA%' AND "ID">=10000))

SYS@primary/2011-04-25 12:24:56>update table_2 set pad=dbms_random.string('U',300) where id between 10000 and 20000 ;

10001 rows updated.

Elapsed: 00:00:12.36

SYS@primary/2011-04-25 12:27:55>analyze table table_2 compute statistics;

Table analyzed.

Elapsed: 00:00:01.94
set linesize 220
col owner for a8
col table_name for a8 heading "NAME"
col num_rows for 999999
col blocks for 999999
col empty_blocks for 99999 heading "EMPTY"
col avg_row_len for 99999
col row_movement for a8
col cache for a5
col chain_cnt for 99999
col last_analyzed for a20
col partitioned for a5 heading "PART"
select owner,table_name,num_rows,blocks,avg_row_len,empty_blocks,
chain_cnt,last_analyzed,row_movement,cache,partitioned
from dba_tables
where table_name = upper('&tablename')
 /
Enter value for tablename: table_2

OWNER    NAME        ROWS  BLOCKS AVG_ROW_LEN  EMPTY CHAIN_CNT LAST_ANALYZED        ROW_MOVE CACHE PART
-------- -------- ------- ------- ----------- ------ --------- -------------------- -------- ----- -----
SYS      TABLE_2   100000    1530          96      6      7783 2011-04-25 12:28:36  DISABLED     N NO

1 row selected.

Elapsed: 00:00:00.00

set linesize 200
col owner for a10
col segment_name for a15
col segment_type for a10
col tablespace_name for a10
col file_id for 9999
col extent_id for 9999
col block_id for 9999999
col blocks for 99999
select owner,segment_name,segment_type,
tablespace_name,file_id,extent_id,block_id,bytes,blocks
from dba_extents
where  segment_name=upper('&seg_name')
 /
Enter value for seg_name: table_2

OWNER      SEGMENT_NAME    SEGMENT_TY TABLESPACE FILE_ID EXTENT_ID BLOCK_ID      BYTES BLOCKS
---------- --------------- ---------- ---------- ------- --------- -------- ---------- ------
SYS        TABLE_2         TABLE      ASSM             6         0     1104      65536      8
SYS        TABLE_2         TABLE      ASSM             6         1     1112      65536      8
SYS        TABLE_2         TABLE      ASSM             6         2     1120      65536      8
SYS        TABLE_2         TABLE      ASSM             6         3     1128      65536      8
SYS        TABLE_2         TABLE      ASSM             6         4     1136      65536      8
SYS        TABLE_2         TABLE      ASSM             6         5     1144      65536      8
SYS        TABLE_2         TABLE      ASSM             6         6     1152      65536      8
SYS        TABLE_2         TABLE      ASSM             6         7     1160      65536      8
SYS        TABLE_2         TABLE      ASSM             6         8     1168      65536      8
SYS        TABLE_2         TABLE      ASSM             6         9     1176      65536      8
SYS        TABLE_2         TABLE      ASSM             6        10     1184      65536      8
SYS        TABLE_2         TABLE      ASSM             6        11     1192      65536      8
SYS        TABLE_2         TABLE      ASSM             6        12     1200      65536      8
SYS        TABLE_2         TABLE      ASSM             6        13     1208      65536      8
SYS        TABLE_2         TABLE      ASSM             6        14     1216      65536      8
SYS        TABLE_2         TABLE      ASSM             6        15     1224      65536      8
SYS        TABLE_2         TABLE      ASSM             6        16     1280    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        17     1408    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        18     1536    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        19     1664    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        20     1792    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        21     1920    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        22     2048    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        23     2176    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        24     2304    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        25     2432    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        26     2560    1048576    128

27 rows selected.

Elapsed: 00:00:00.01


set linesize 150
undefine table_name
col owner_name for a8
col table_name for a15
col head_rowid for a25
col timestamp for a25
select owner_name,table_name,
head_rowid,analyze_timestamp from chained_rows where table_name
=upper('&table_name')
  4  /
Enter value for table_name: table_2


SYS      TABLE_2         AAAOoeAAGAAAAVTABR        2011-04-25 12:31:28
SYS      TABLE_2         AAAOoeAAGAAAAVTABT        2011-04-25 12:31:28
SYS      TABLE_2         AAAOoeAAGAAAAVTABU        2011-04-25 12:31:28
SYS      TABLE_2         AAAOoeAAGAAAAVTABV        2011-04-25 12:31:28
SYS      TABLE_2         AAAOoeAAGAAAAVTABW        2011-04-25 12:31:28
SYS      TABLE_2         AAAOoeAAGAAAAVTABY        2011-04-25 12:31:28
SYS      TABLE_2         AAAOoeAAGAAAAVTABZ        2011-04-25 12:31:28
SYS      TABLE_2         AAAOoeAAGAAAAVTABa        2011-04-25 12:31:28
SYS      TABLE_2         AAAOoeAAGAAAAVTABb        2011-04-25 12:31:28
SYS      TABLE_2         AAAOoeAAGAAAAVTABd        2011-04-25 12:31:28
SYS      TABLE_2         AAAOoeAAGAAAAVTABe        2011-04-25 12:31:28

7783 rows selected.

Elapsed: 00:00:01.18

SYS@primary/2011-04-25 12:38:35>select count(*) from table_2 where id between 10000 and 20000 and pad like 'KA%';

  COUNT(*)
----------
        19

1 row selected.

Elapsed: 00:00:00.04
SYS@primary/2011-04-25 12:39:20>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7ut6n7azbaddb, child number 0
-------------------------------------
select count(*) from table_2 where id between 10000 and 20000 and pad
like 'KA%'

Plan hash value: 2092335737

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |       |       |    20 (100)|          |
|   1 |  SORT AGGREGATE              |                  |     1 |    80 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TABLE_2          |    19 |  1520 |    20   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND__TABLE_2_PAD |    17 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("ID"<=20000 AND "ID">=10000))
   3 - access("PAD" LIKE 'KA%')
       filter("PAD" LIKE 'KA%')

SYS@primary/2011-04-25 12:39:32>select /*+ full(table_2)*/ count(*) from table_2 where id between 10000 and 20000 and pad like 'KA%';

  COUNT(*)
----------
        19

1 row selected.

Elapsed: 00:00:00.07
SYS@primary/2011-04-25 12:43:50>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  23bsz4wjw3hjs, child number 0
-------------------------------------
select /*+ full(table_2)*/ count(*) from table_2 where id between 10000
and 20000 and pad like 'KA%'

Plan hash value: 1055150476

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       |   418 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |    80 |            |          |
|*  2 |   TABLE ACCESS FULL| TABLE_2 |    19 |  1520 |   418   (1)| 00:00:06 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("ID"<=20000 AND "PAD" LIKE 'KA%' AND "ID">=10000))


SYS@primary/2011-04-25 12:43:53>create table migration as select table_2.*  from table_2 ,chained_rows where table_2.rowid=chained_rows.head_rowid;

Table created.

Elapsed: 00:00:00.43
SYS@primary/2011-04-25 12:51:43>select count(*) from migration;

  COUNT(*)
----------
      7783

1 row selected.

Elapsed: 00:00:00.01


SYS@primary/2011-04-25 12:55:13>delete from  table_2 where rowid in( select head_rowid from chained_rows);

7783 rows deleted.

Elapsed: 00:00:01.15

SYS@primary/2011-04-25 12:57:54>analyze table table_2 list chained rows;

Table analyzed.

Elapsed: 00:00:00.05
select owner,table_name,num_rows,blocks,avg_row_len,empty_blocks,
chain_cnt,last_analyzed,row_movement,cache,partitioned
from dba_tables
where table_name = upper('&tablename')
  5   /
Enter value for tablename: table_2

OWNER    NAME               ROWS BLOCKS AVG_ROW_LEN  EMPTY CHAIN_CNT LAST_ANALYZED        ROW_MOVE CACHE PART
-------- --------------- ------- ------ ----------- ------ --------- -------------------- -------- ----- -----
SYS      TABLE_2          100000   1530          96      6      7783 2011-04-25 12:28:36  DISABLED     N NO

1 row selected.

Elapsed: 00:00:00.00

SYS@primary/2011-04-25 12:58:29>select count(*) from table_2;

  COUNT(*)
----------
     92217

1 row selected.

Elapsed: 00:00:00.01

SYS@primary/2011-04-25 12:59:03>insert into table_2 select * from migration;

7783 rows created.

Elapsed: 00:00:01.00
SYS@primary/2011-04-25 12:59:58>select count(*) from table_2;

  COUNT(*)
----------
    100000

1 row selected.

Elapsed: 00:00:00.01
SYS@primary/2011-04-25 13:00:02>commit;

Commit complete.

Elapsed: 00:00:00.00

SYS@primary/2011-04-25 13:00:54>analyze table table_2 list chained rows;

Table analyzed.

Elapsed: 00:00:00.05
SYS@primary/2011-04-25 13:01:16>analyze table table_2 compute statistics;

Table analyzed.

Elapsed: 00:00:01.82
select owner,table_name,num_rows,blocks,avg_row_len,empty_blocks,
chain_cnt,last_analyzed,row_movement,cache,partitioned
from dba_tables
  4  where table_name = upper('&tablename')
  5  /
Enter value for tablename: table_2

OWNER    NAME               ROWS BLOCKS AVG_ROW_LEN  EMPTY CHAIN_CNT LAST_ANALYZED        ROW_MOVE CACHE PART
-------- --------------- ------- ------ ----------- ------ --------- -------------------- -------- ----- -----
SYS      TABLE_2          100000   1530          96      6         0 2011-04-25 13:01:26  DISABLED     N NO

1 row selected.

Elapsed: 00:00:00.04

SYS@primary/2011-04-25 13:03:09>select count(*) from table_2 where id between 10000 and 20000 and pad like 'KA%';

  COUNT(*)
----------
        19

1 row selected.

Elapsed: 00:00:00.01
SYS@primary/2011-04-25 13:03:31>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7ut6n7azbaddb, child number 0
-------------------------------------
select count(*) from table_2 where id between 10000 and 20000 and pad
like 'KA%'

Plan hash value: 2092335737

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |       |       |    20 (100)|          |
|   1 |  SORT AGGREGATE              |                  |     1 |    80 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TABLE_2          |    19 |  1520 |    20   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND__TABLE_2_PAD |    17 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("ID"<=20000 AND "ID">=10000))
   3 - access("PAD" LIKE 'KA%')
       filter("PAD" LIKE 'KA%')


来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24890594/viewspace-693652/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24890594/viewspace-693652/

cpfengyo322322
关注
Oracle面试题
golove666的博客
04-03 2102
Oracle高频面试题。
关于行链接和行迁移和消除
cndsws0715的博客
03-26 166
关于行链接和行迁移和几种消除方法(看书笔录)[@more@]Oracle中行迁移和行链接的清除及检测一、行迁移和行链接的介绍 在实际的工作中我们经常会碰到一些Oracle数据库性能较低的问题，当然，引起Oracle数据库性能较低...
模拟Oracle行迁移和行链接
weixin_30735391的博客
03-19 99
行链接消除方法创建大的block块------------------ 参考tom kyte的例子----------------------------------------------创建4k blocksize的表空间SQL> alter system set db_4k_cache_size=1m scope=both; System altered.SQL> create ...
Oracle中行迁移和行链接的清除及检测
duanbeibei的专栏
05-28 1736
原文链接： http://9host.cn/oracle/200742218400513097.html 一、行迁移和行链接的介绍 　 在实际的工作中我们经常会碰到一些Oracle数据库性能较低的问题，当然，引起Oracle数据库性能较低的原因是多方面的，我们能够通过一些正确的设计和诊断来尽量的避免一些Oracle数据库性能不好，Row Migration (行迁移) & Row Ch
oracle行链接的检测和处理
csh1951914的博客
08-12 212
oracle行链接的检测和处理http://space.itpub.net/?uid-26890727-action-viewspace-itemid-721389上一篇 / 下一篇 2012-04-17 09:47:09 ...
oracle中的行迁移,Oracle 行迁移 & 行链接的检测与消除
weixin_30066287的博客
04-05 280
什么是行迁移 & 行链接?# 以下描述来自Oracle 11gR2 Document1. 行链接The row is too large to fit into one data block when it is first inserted.In row chaining, Oracle Database stores the data for the row in ...
oracle行迁移实验,Oracle 行迁移 & 行链接的检测与消除
weixin_35414475的博客
04-03 94
什么是行迁移 & 行链接?# 以下描述来自Oracle 11gR2 Document1. 行链接The row is too large to fit into one data block when it is first inserted.In row chaining, Oracle Database stores the data for the row in ...
Oracle 行迁移 & 行链接的检测与消除
weixin_33853794的博客
08-15 79
什么是行迁移 & 行链接?# 以下描述来自Oracle 11gR2 Document1. 行链接The row is too large to fit into one data block when it is first inserted.In row chaining, Oracle Database stores the data for ...
Oracle数据库死锁分析与解决：从现象到根源，彻底消除死锁
[Oracle数据库死锁分析与解决：从现象到根源，彻底消除死锁](https://img-blog.csdnimg.cn/img_convert/467e3840e150f4d16859a3487f0f7ce3.png) # 1. Oracle数据库死锁概述** 死锁是一种数据库系统中常见的并发控制...
Oracle数据库连接超时问题剖析与解决：深入理解超时机制
Oracle数据库连接超时问题是指数据库客户端在指定时间内无法与数据库服务器建立连接或执行操作时，数据库服务器会自动断开连接。该问题会影响应用程序的可用性和性能，给用户带来不便。 **连接超时类型** Oracle...
Oracle数据库连接池性能测试：评估连接池性能，优化配置，提升数据库并发处理能力
![Oracle数据库连接池性能测试：评估连接池性能，优化配置，...在Oracle数据库中，连接池通过JDBC（Java数据库连接）API实现。 连接池的主要好处包括： * **减少数据库连接开销：**建立和关闭数据库连接是昂贵的操
Oracle数据库连接池优化秘籍：提升性能和稳定性，打造高效稳定的数据库连接池
Oracle数据库连接池概述** 数据库连接池是一种管理数据库连接的机制，它在应用程序和数据库之间充当一个缓冲区。通过使用连接池，应用程序可以预先建立并存储一定数量的数据库连接，以备后续使用。当应用程序需要...
oracle数据库归档日志丢失,丢失数据文件的恢复 归档和非归档日志模式下
weixin_42351189的博客
04-14 1279
在Oracle数据库中，构成SYSTEM表空间和当前活动的撤销表空间(由UNDO_TABLESPACE参数指定)的数据文件被认为是"关键的"。这类文件受损将导致实例立即终止。而且，在通过还原和恢复操作修复损坏之前，数据库无法再次打开。构成用户数据表空间的其他文件受损通常不会导致实例崩溃。Oracle将使受损的文件脱机，使其内容不可访问，但数据库的其余部分仍保持打开。您的应用程序对此的反应将取决于其...
Oracle行迁移和行链接详解(原创)
czmmiao的专栏
02-13 731
行迁移成 因：当发出update导致记录行长增加，block的剩余空间不足以存放这条记录，就会产生行迁移，发生行迁移时rowid不会改变，原来的block 中会用一个指针存放这条记录在新的block中的地址，发生行迁移会对性能产生影响，因为读这条记录会读两个BLOCK。后果：导致应用需要访问更多的数据块，性能下降。预防：1. 将数据块的PCTFREE调大；2. 针对表空间扩大数据块大小检查：a...
MySQL数据库的详细学习步骤
2401_87352036的博客
10-11 1196
MySQL数据库的详细学习步骤可以归纳为以下几个阶段，每个阶段都包含了特定的学习内容和目标。
Java分布式锁
juanmiao的专栏
10-12 637
每种方法都有其优点和适应的场景，数据库通常简单，但可能存在性能问题，redis非常快，但需要第三方库，并且可能引入网诺问题，Zookeeper提供了更复杂的同步原语，但需要额外学习曲线。分布式锁的实现比线程锁和进程锁要复杂得多，因为它需要在网诺中的多个节点之间进行协调，以保证锁的唯一性和一致性。在实现分布式锁时，通常会有一个中心节点（或者称为锁服务），所有需要获取锁的节点都需要向这个中心节点申请。这个中心节点负责协调和管理所有节点的锁请求，确保锁的唯一性和一致性。创建临时节点，执行业务逻辑，释放锁。
mysql锁之乐观锁、悲观锁、表锁、行锁、共享锁、排他锁
最新发布
qq_68883928的博客
10-14 544
锁是计算机协调多个进程或线程并发访问某一个资源的机制，在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
SQLite数据库在Android中的应用及操作方式
Good_tea_h的博客
10-14 546
SQLite数据库在Android中的应用非常广泛，它作为一个轻量级的关系型数据库，以其高效、可靠和易于使用的特点，成为了Android平台上存储和管理数据的首选方案。以下是对SQLite数据库在Android中的应用及操作方式的详细介绍。
Java与Oracle连接入门示例及Employee数据库应用
由于资源中提到的是一个较好的示例来开始Java-Oracle的连接，我们可以推测Prob1文件中可能包含了一个具体的操作示例，比如建立一个连接到Employee数据库的Java程序。这将涉及以下代码步骤： ```java import java....
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值