Row Migration and Row Chaining in Oracle

 

SQL>

SQL> create user sam identified by sam;

User created.

SQL> show parameter db_2k

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_2k_cache_size                     big integer 0

SQL>

SQL> alter system set db_2k_cache_size=150m;

System altered.

SQL>

SQL>  show parameter db_2k

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_2k_cache_size                     big integer 160M

SQL>

SQL>

SQL> create tablespace tbs201 datafile '/data01/RNDDB/tbs201.dbf' size 100m blocksize 2k;

Tablespace created.

SQL>

SQL>

SQL> select tablespace_name, block_size from dba_tablespaces;

TABLESPACE_NAME                BLOCK_SIZE

------------------------------ ----------

SYSTEM                               8192

SYSAUX                               8192

UNDOTBS1                             8192

TEMP                                 8192

TEMP1                                8192

USER1                                8192

PRIYA                                8192

TBS201                               2048

8 rows selected.

SQL>

SQL>

SQL> grant dba to sam;

Grant succeeded.

SQL> conn sam/sam

Connected.

SQL>

SQL>

SQL> create table sample(id number constraint idpk primary key, name char(2000), address char(2000), fathername char(2000), email char(2000)) tablespace tbs201;

Table created.

SQL>

SQL> select extent_id, blocks from dba_extents where segment_name='SAMPLE' and owner='SAM';

no rows selected

SQL>

SQL> insert into sample (id) values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select extent_id, blocks from dba_extents where segment_name='SAMPLE' and owner='SAM';

 EXTENT_ID     BLOCKS

---------- ----------

         0         32

SQL> insert into sample (id) values(2);

1 row created.

SQL> select extent_id, blocks from dba_extents where segment_name='SAMPLE' and owner='SAM';

 EXTENT_ID     BLOCKS

---------- ----------

         0         32

SQL> commit;

Commit complete.

SQL> select extent_id, blocks from dba_extents where segment_name='SAMPLE' and owner='SAM';

 EXTENT_ID     BLOCKS

---------- ----------

         0         32

SQL> insert into sample (id) values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> select extent_id, blocks from dba_extents where segment_name='SAMPLE' and owner='SAM';

 EXTENT_ID     BLOCKS

---------- ----------

         0         32

SQL> select id from sample;

        ID

----------

         1

         2

         3

SQL>

SQL> ================now row migration because of UPDATE statement======

SQL>  update sample set name='abc' , address='xyz', email='gmail.com' where id=3;

1 row updated.

SQL> commit;

Commit complete.

SQL> update sample set name='samik';

3 rows updated.

SQL> update sample set name='neel' where id=1;

1 row updated.

SQL>

SQL> select * from sample;

        ID ---------- NAME -------------------------------------------------------------------------------- ADDRESS 

SQL>

SQL> select rowid from sample;

ROWID

------------------

AAAFrTAAGAAAAIMAAA

AAAFrTAAGAAAAIMAAB

AAAFrTAAGAAAAIMAAC

SQL> select rowid,id from sample;

ROWID                      ID

------------------ ----------

AAAFrTAAGAAAAIMAAA          1

AAAFrTAAGAAAAIMAAB          2

AAAFrTAAGAAAAIMAAC          3

SQL>

SQL> select rowid, id, dbms_rowid.rowid_block_number(rowid) as block_num from sample;

ROWID                      ID  BLOCK_NUM

------------------ ---------- ----------

AAAFrTAAGAAAAIMAAA          1        524

AAAFrTAAGAAAAIMAAB          2        524

AAAFrTAAGAAAAIMAAC          3        524

SQL>

SQL>

SQL> ==============how to identify row migration is happening or not=====================

SQL>

SQL>

SQL> desc v$statname

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 STATISTIC#                                         NUMBER

 NAME                                               VARCHAR2(64)

 CLASS                                              NUMBER

 STAT_ID                                            NUMBER

 DISPLAY_NAME                                       VARCHAR2(64)

 CON_ID                                             NUMBER

SQL>

SQL> select * from v$statname where name='table fetch continued row'

  2  /

STATISTIC# NAME

---------- ----------------------------------------------------------------

     CLASS    STAT_ID

---------- ----------

DISPLAY_NAME                                                         CON_ID

---------------------------------------------------------------- ----------

      1017 table fetch continued row

        64 1413702393

table fetch continued row                                                 0

SQL> col name for a20

SQL> /

STATISTIC# NAME                      CLASS    STAT_ID

---------- -------------------- ---------- ----------

DISPLAY_NAME                                                         CON_ID

---------------------------------------------------------------- ----------

      1017 table fetch continue         64 1413702393

           d row

table fetch continued row                                                 0

SQL> col DISPLAY_NAME for a25

SQL> /

STATISTIC# NAME                      CLASS    STAT_ID DISPLAY_NAME

---------- -------------------- ---------- ---------- -------------------------

    CON_ID

----------

      1017 table fetch continue         64 1413702393 table fetch continued row

           d row

         0

SQL> set lines 200

SQL> /

STATISTIC# NAME                      CLASS    STAT_ID DISPLAY_NAME                  CON_ID

---------- -------------------- ---------- ---------- ------------------------- ----------

      1017 table fetch continue         64 1413702393 table fetch continued row          0

           d row

SQL> col NAME for a45

SQL> /

STATISTIC# NAME                                               CLASS    STAT_ID DISPLAY_NAME                  CON_ID

---------- --------------------------------------------- ---------- ---------- ------------------------- ----------

      1017 table fetch continued row                             64 1413702393 table fetch continued row          0

SQL>

SQL>

SQL> desc V$mystat

 Name                   Null?    Type

 ---------------------- -------- 

 SID                             NUMBER

 STATISTIC#                      NUMBER

 VALUE                           NUMBER

 CON_ID                          NUMBER

SQL>

SQL>

SQL> select name,value from v$statname s inner join v$mystat m on m.STATISTIC#=s.STATISTIC# where s.name='table fetch continued row';

NAME                                               VALUE

--------------------------------------------- ----------

table fetch continued row                              6

SQL>

SQL>

SQL> select * from sample;

        ID NAME

---------- ---------------------------------------------

ADDRESS

---------------------------------------------------------------------------------------------------------

FATHERNAME

---------------------------------------------------------------------------------------------------------

EMAIL

-----------------------------------------------------------------------

SQL>

SQL>

SQL> select count(name) from sample;

COUNT(NAME)

-----------

          3

SQL> select name,value from v$statname s inner join v$mystat m on m.STATISTIC#=s.STATISTIC# where s.name='table fetch continued row';

NAME                                               VALUE

--------------------------------------------- ----------

table fetch continued row                             14

SQL>

SQL>

SQL> =============number of extra blocking are getting read because your row has been migrated;================

SQL>

SQL>

SQL> select count(address) from sample;

COUNT(ADDRESS)

--------------

             1

SQL> select name,value from v$statname s inner join v$mystat m on m.STATISTIC#=s.STATISTIC# where s.name='table fetch continued row';

NAME                                               VALUE

--------------------------------------------- ----------

table fetch continued row                             18

SQL>

SQL> desc sample

 Name                            Null?    Type

 ------------------------------- -------- ----------------------------------------------------------------------------

 ID                              NOT NULL NUMBER

 NAME                                     CHAR(2000)

 ADDRESS                                  CHAR(2000)

 FATHERNAME                               CHAR(2000)

 EMAIL                                    CHAR(2000)

SQL>

SQL> select count(email) from sample;

COUNT(EMAIL)

------------

           1

SQL>

SQL> select name,value from v$statname s inner join v$mystat m on m.STATISTIC#=s.STATISTIC# where s.name='table fetch continued row';

NAME                                               VALUE

--------------------------------------------- ----------

table fetch continued row                             24

SQL>

SQL>

SQL>=================== "We have to stop this row migration and row chaining" =======================

S

SQL>

SQL>

SQL>

SQL>

SQL> =========Let's check row chaining now =====================================

SQL>

SQL>

SQL>

SQL> create table example (id number constraint idpk2 primary key , name char(2000), address char(2000),  email char(2000)) tablespace tbs201;

Table created.

SQL>

SQL>

SQL> insert into example values (1,'abc','xyz','abc@gmail.com');

1 row created.

SQL> commit;

Commit complete.

SQL> select id from exmple;

select id from exmple

               *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> select id from example;

        ID

----------

         1

SQL> ----------Our block is 2k and our records are more than 8k---------------

SQL>

SQL> ----------We have a view from that , we have count the row Chaining------

SQL>

SQL>

SQL> select table_name, chain_cnt from user_tables where table_name='EXAMPLE';

TABLE_NAME                        CHAIN_CNT

-------------------------------------------

EXAMPLE

SQL>

SQL> analyze table example compute statistics;

Table analyzed.

SQL> select table_name, chain_cnt from user_tables where table_name='EXAMPLE';

TABLE_NAME            CHAIN_CNT

-------------------------------

EXAMPLE                       1

SQL>

SQL> col table_name for a20

SQL> /

TABLE_NAME            CHAIN_CNT

-------------------- ----------

EXAMPLE                       1

SQL>

SQL>

SQL> ------------ it means that your table has Row Chain -----------------and it showing Chain Count =1 ---

SQL>

SQL>

SQL>

SQL> insert into example values (2,'abc','xyz','abc@gmail.com');

1 row created.

SQL> commit;

Commit complete.

SQL> analyze table example compute statistics;

Table analyzed.

SQL> select table_name, chain_cnt from user_tables where table_name='EXAMPLE';

TABLE_NAME            CHAIN_CNT

-------------------- ----------

EXAMPLE                       2

SQL>

SQL> select name,value from v$statname s inner join v$mystat m on m.STATISTIC#=s.STATISTIC# where s.name='table fetch continued row';

NAME                                               VALUE

--------------------------------------------- ----------

table fetch continued row                             28

SQL>

SQL>

SQL> select * from example;

        ID NAME

---------- -----------------------

ADDRESS

----------------------------------

EMAIL

----------------------------------

         1 abc

SQL> select name,value from v$statname s inner join v$mystat m on m.STATISTIC#=s.STATISTIC# where s.name='table fetch continued row';

NAME                                               VALUE

--------------------------------------------- ----------

table fetch continued row                             37

SQL>

SQL>

SQL> ---------------------------Solution------------------

SQL>

SQL> ------------increase the block size------------------

SQL>

SQL>

SQL>

SQL> ------------move the table into that block size-----------

SQL>

SQL>

SQL> alter table example move pctfree 10 pctused 50 tablespace TBS19;

SQL> alter index idpk2 rebuild;

SQL> analyze table example compute statistics;

SQL> select name,value from v$statname s inner join v$mystat m on m.STATISTIC#=s.STATISTIC# where s.name='table fetch continued row';

NAME                                               VALUE

--------------------------------------------- ----------

table fetch continued row                             41

 SQL> select * from example;

Again Value is 41, it means that no row migration and row chaining ...

SQL> select name,value from v$statname s inner join v$mystat m on m.STATISTIC#=s.STATISTIC# where s.name='table fetch continued row';

NAME                                               VALUE

--------------------------------------------- ----------

table fetch continued row                             41

Now go to cd $ORACLE_HOME/rdbms/admin/

$ORACLE_HOME/rdbms/admin/

utlchain.sql

SQL>@utlchain.sql

Now analyze the table "example" list chained row...

SQL> analyze table example list chained row;

Table anaylzed.

You will get output in the chained_rows table.

Don't Forget System Level Statistics

 

Don't Forget System Level Statistics

If you do any work with performance tuning, you know the importance of Object Level statistics. Oracle uses this information to estimate how many rows will be returned by different steps in the plan. These estimates help the Optimizer form what it thinks is the best plan.

There are numerous subtopics that can have large impacts on SQL Statements. One statistics related item that is often overlooked is System Level Statistics.

When I talk about system level statistics I am referring to three different things:
1. Fixed object statistics
2. Data Dictionary statistics
3. System stats.

If these items are not kept up to date, then you can see some performance degradation. Oracle has several blog posts detailing the importance of these statistics. These statistics should be updated following upgrades to the database software. The system statistics should be updated following any hardware changes.

Fixed Object Statistics

This is different from user level object statistics. For example if you are querying the EMP and DEPT tables then you usually will want to have accurate statistics on the tables which reflect the data distribution of values in the columns of those tables. I say usually because there may be exceptions to this rule.

You can check the status of the fixed object statistics using the following query:
SELECT owner
     , table_name
     , last_analyzed
FROM   dba_tab_statistics
WHERE  table_name IN
             (SELECT name
              FROM   v$fixed_table
              WHERE  type = 'TABLE'
             )
ORDER BY last_analyzed NULLS LAST;

Fixed Objects are the X$ objects defined in the database. These objects are not owned by regular users, so gathering stats on user level objects will not impact these objects. There is a different command, EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS, that will gather stats on these objects. The statistics should be run while the system is performing a normal workload. If there are significant updates to the workload then the statistics should be regathered. Also, the statistics should be regathered following any upgrades.

For more information about the importance of fixed objects can be seen here:
https://blogs.oracle.com/optimizer/entry/fixed_objects_statistics_and_why

Data Dictionary Statistics

Similar to the fixed objects, data dictionary statistics are also used by the system to build plans for system queries. If these are not kept up to date you will see some performance issues related to queries against those objects.

You can check the status of data dictionary statistics with the following query:
SELECT owner
     , table_name
     , last_analyzed
FROM   dba_tab_statistics
WHERE  owner='SYS'
ORDER BY last_analyzed NULLS LAST;

The command to update these statistics is DBMS_STATS.GATHER_DICTIONARY_STATS.

At a minimum this should be run after every database upgrade. To keep these statistics up to date, you may choose to run the gather command more frequently such as every quarter.

For additional information you can also look at the My Oracle Support Document, How to Gather Statistics on Objects Owned by the 'SYS' User and 'Fixed' Objects (Doc ID 457926.1)

System Statistics

The system statistics collect information about the hardware, in particular CPU performance and I/O performance. The Oracle optimizer will use this information to determine the cost of a statement. If this data is not accurate then it can result in suboptimal plans. This data is collected with the command EXEC DBMS_STATS.GATHER_SYSTEM_STATS. As part of any hardware changes or database upgrades, these statistics should be recollected. Note that if you are running on Exadata, you should use the command EXEC DBMS_STATS.GATHER_SYSTEM_STATS(‘EXADATA’).

Warning! There is a case where a bug caused the command to collect inaccurate information. Oracle bug 9842771 impacted the system statistics collected in release 11.2.0.2. So it is advised to review the values before and after collecting.

To check the system statistics use the following query

select * from sys.aux_stats$;

These fixed object and system statistics provide a foundation that is used by the optimizer to impact many different statements. If you are collecting user level objects but not keeping the other statistics up to date then you are putting yourself at risk for poor performance.