Monday, October 27, 2014

Oracle Table Fragmentation

What is Table fragmentation
When rows are not stored contiguously, or if rows are split onto more than one page, performance decreases because these rows require additional page accesses. Table fragmentation is distinct from file fragmentation.

When lots of DML operation apply on tables then tables is fragmented.
because DML is not release free space from table below HWM.

Hint: HWM is indicator for USED BLOCKS in database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted.
Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark in a full table scan.

DDL statement always reset HWM.


How to find table fragmentation



SQL> select count(*) from big1;

1000000 rows selected.

SQL> delete from big1 where rownum <= 300000;

300000 rows deleted.

SQL> commit;

Commit complete.

SQL> update big1 set object_id = 0 where rownum <=350000;

342226 rows updated.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.


Table Size ( with fragmented)




SQL> select table_name,round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 72952kb


Actual data in table


SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 30604.2kb

Note= 72952 – 30604 = 42348 Kb is wasted space in table

The difference between two values is 60% and Pctfree 10% (default) so table is 50% extra space which is wasted because there is no data.

How to reset HWM / remove fragemenation

For that we need to reorganize fragmented table

We have four options to reorganize fragmented tables

1. alter table … move + rebuild indexes
2. export / truncate / import
3. create table as select ( CTAS)
4. dbms_redefinition


Option: 1 “alter table … move + rebuild indexes”



SQL> alter table BIG1 move;

Table altered.

SQL> select status,index_name from user_indexes
2 where table_name = 'BIG1';

STATUS INDEX_NAME
-------- ------------------------------
UNUSABLE BIGIDX

SQL> alter index bigidx rebuild;

Index altered.

SQL> select status,index_name from user_indexes
2 where table_name = 'BIG1';

STATUS INDEX_NAME
-------- ------------------------------
VALID BIGIDX


SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 38224kb

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 30727.37kb



Option: 2 “Create table as select”




SQL> create table big2 as select * from big1;

Table created.

SQL> drop table big1 purge;

Table dropped.

SQL> rename big2 to big1;

Table renamed.

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 85536kb

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 68986.97kb

SQL> select status from user_indexes
2 where table_name = 'BIG1';

no rows selected

SQL> --Note we need to create all indexes.



Option: 3 “export / truncate / import”



SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 42535.54kb

SQL> select status from user_indexes where table_name = 'BIG1';

STATUS
--------
VALID

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\>exp scott/tiger@Orcl file=c:\big1.dmp tables=big1

Export: Release 10.1.0.5.0 - Production on Sat Jul 28 16:30:44 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table BIG1 468904 rows exported
Export terminated successfully without warnings.

C:\>sqlplus scott/tiger@orcl

SQL*Plus: Release 10.1.0.5.0 - Production on Sat Jul 28 16:31:12 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> truncate table big1;

Table truncated.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\>imp scott/tiger@Orcl file=c:\big1.dmp ignore=y

Import: Release 10.1.0.5.0 - Production on Sat Jul 28 16:31:54 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table "BIG1" 468904 rows imported
Import terminated successfully without warnings.

C:\>sqlplus scott/tiger@orcl

SQL*Plus: Release 10.1.0.5.0 - Production on Sat Jul 28 16:32:21 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 42535.54kb

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 51840kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 42542.27kb

SQL> select status from user_indexes where table_name = 'BIG1';

STATUS
--------
VALID

SQL> exec dbms_redefinition.can_redef_table('SCOTT','BIG1',-
> dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.



Option: 4 “dbms_redefinition”





SQL> create table TABLE1 (
2 no number,
3 name varchar2(20) default 'NONE',
4 ddate date default SYSDATE);

Table created.

SQL> alter table table1 add constraint pk_no primary key(no);

Table altered.

SQL> begin
2 for x in 1..100000 loop
3 insert into table1 ( no , name, ddate)
4 values ( x , default, default);
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> create or replace trigger tri_table1
2 after insert on table1
3 begin
4 null;
5 end;
6 /

Trigger created.

SQL> select count(*) from table1;

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

SQL> delete table1 where rownum <= 50000;

50000 rows deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1');

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';

TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 2960kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';

TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 822.69kb


SQL> --Minimum Privs required "DBA" role or "SELECT" on dbms_redefinition pkg
SQL> --First check table is condidate for redefinition.
SQL>
SQL> exec sys.dbms_redefinition.can_redef_table('SCOTT',-
> 'TABLE1',-
> sys.dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

SQL> --After verifying that the table can be redefined online, you manually crea
te an empty interim table (in the same schema as the table to be redefined)
SQL>
SQL> create table TABLE2 as select * from table1 WHERE 1 = 2;

Table created.

SQL> exec sys.dbms_redefinition.start_redef_table ( 'SCOTT',-
> 'TABLE1',-
> 'TABLE2');

PL/SQL procedure successfully completed.

SQL> --This procedure keeps the interim table synchronized with the original tab
le.
SQL>
SQL> exec sys.dbms_redefinition.sync_interim_table ('SCOTT',-
> 'TABLE1',-
> 'TABLE2');

PL/SQL procedure successfully completed.

SQL> --Create PRIMARY KEY on interim table(TABLE2)
SQL> alter table TABLE2
2 add constraint pk_no1 primary key (no);

Table altered.

SQL> create trigger tri_table2
2 after insert on table2
3 begin
4 null;
5 end;
6 /

Trigger created.

SQL> --Disable foreign key on original table if exists before finish this proces
s.
SQL>
SQL> exec sys.dbms_redefinition.finish_redef_table ( 'SCOTT',-
> 'TABLE1',-
> 'TABLE2');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1');

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';

TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 1376kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';

TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 841.4kb

SQL> select status,constraint_name
2 from user_constraints
3 where table_name = 'TABLE1';

STATUS CONSTRAINT_NAME
-------- ------------------------------
ENABLED PK_NO1

SQL> select status ,trigger_name
2 from user_triggers
3 where table_name = 'TABLE1';

STATUS TRIGGER_NAME
-------- ------------------------------
ENABLED TRI_TABLE2

SQL> drop table TABLE2 PURGE;

Table dropped.

Sunday, October 26, 2014

How to Replace disk on freenas for CLI

[root@freenas] ~# camcontrol identify ada0 | grep LBA48
LBA48 supported       5860533168 sectors
[root@freenas] ~# camcontrol identify ada2 | grep LBA48
LBA48 supported       5860533168 sectors
[root@freenas] ~#
[root@freenas] ~# smartctl -a /dev/ada1 | grep "Serial"
Serial Number:    W1F390KB
[root@freenas] ~# smartctl -a /dev/ada2 | grep "Serial"
Serial Number:    WCC1T1236411
[root@freenas] ~# smartctl -a /dev/ada0 | grep "Serial"
Serial Number:    W1F3M4FP
[root@freenas] ~#


[root@freenas] ~# cat /etc/version
FreeNAS-8.3.2-RELEASE-x86 (r12686+70c2cb8)


[root@freenas] ~# camcontrol devlist sysctl kern.disks
          at scbus0 target 0 lun 0 (pass0,ada0)
          at scbus1 target 0 lun 0 (pass1,ada1)
        at scbus2 target 0 lun 0 (pass2,ada2)
          at scbus3 target 0 lun 0 (pass3,ada3)
          at scbus4 target 0 lun 0 (pass4,ada4)
          at scbus5 target 0 lun 0 (pass5,ada5)
    at scbus8 target 0 lun 0 (pass6,da0)

[root@freenas] ~#glabel status


                                      Name  Status  Components

gptid/55414a31-55c3-11e3-b6e3-4ce676286ed3     N/A  ada0p2

gptid/55ecb111-55c3-11e3-b6e3-4ce676286ed3     N/A  ada1p2

          gpt/Microsoft reserved partition     N/A  ada2p1

gptid/fa7d0f15-3dff-4dbb-87b4-2719b856d7ad     N/A  ada2p1

gptid/573cee31-55c3-11e3-b6e3-4ce676286ed3     N/A  ada3p2

gptid/57e5235e-55c3-11e3-b6e3-4ce676286ed3     N/A  ada4p2

gptid/5877a718-55c3-11e3-b6e3-4ce676286ed3     N/A  ada5p1

gptid/58928e16-55c3-11e3-b6e3-4ce676286ed3     N/A  ada5p2

                             ufs/FreeNASs3     N/A  da0s3

                             ufs/FreeNASs4     N/A  da0s4

                            ufs/FreeNASs1a     N/A  da0s1a

gptid/ef001e5f-03b8-4b2a-867d-30b9de1d8301     N/A  zvol/nas12t/lptrungp1


[root@freenas] ~# gpart show


=>        34  5860533101  ada0  GPT  (2.7T)

          34          94        - free -  (47k)

         128     4194304     1  freebsd-swap  (2.0G)

     4194432  5856338696     2  freebsd-zfs  (2.7T)

  5860533128           7        - free -  (3.5k)



=>        34  5860533101  ada1  GPT  (2.7T)

          34          94        - free -  (47k)

         128     4194304     1  freebsd-swap  (2.0G)

     4194432  5856338696     2  freebsd-zfs  (2.7T)

  5860533128           7        - free -  (3.5k)



=>        34  5860533101  ada2  GPT  (2.7T)

          34      262144     1  ms-reserved  (128M)

      262178  5860270957        - free -  (2.7T)



=>        34  5860533101  ada3  GPT  (2.7T)

          34          94        - free -  (47k)

         128     4194304     1  freebsd-swap  (2.0G)

     4194432  5856338696     2  freebsd-zfs  (2.7T)

  5860533128           7        - free -  (3.5k)



=>        34  5860533101  ada4  GPT  (2.7T)

          34          94        - free -  (47k)

         128     4194304     1  freebsd-swap  (2.0G)

     4194432  5856338696     2  freebsd-zfs  (2.7T)

  5860533128           7        - free -  (3.5k)



=>        34  5860533101  ada5  GPT  (2.7T)

          34          94        - free -  (47k)

         128     4194304     1  freebsd-swap  (2.0G)

     4194432  5856338696     2  freebsd-zfs  (2.7T)

  5860533128           7        - free -  (3.5k)



=>      63  30945217  da0  MBR  (14G)

        63   1930257    1  freebsd  [active]  (942M)

   1930320        63       - free -  (31k)

   1930383   1930257    2  freebsd  (942M)

   3860640      3024    3  freebsd  (1.5M)

   3863664     41328    4  freebsd  (20M)

   3904992  27040288       - free -  (12G)



=>      0  1930257  da0s1  BSD  (942M)

        0       16         - free -  (8.0k)

       16  1930241      1  !0  (942M)



=>     34  4194237  zvol/nas12t/lptrung  GPT  (2.0G)

       34     2014                       - free -  (1M)

     2048  4192223                    1  !aa31e02a-400f-11db-9590-000c2911d1b8  (2G)




[root@freenas] ~# zpool status


  pool: nas12t

 state: DEGRADED

status: One or more devices could not be opened.  Sufficient replicas exist for

    the pool to continue functioning in a degraded state.

action: Attach the missing device and online it using 'zpool online'.

   see: http://www.sun.com/msg/ZFS-8000-2Q

  scan: scrub repaired 0 in 651h3m with 0 errors on Sat Aug 30 03:03:53 2014

config:



    NAME                                            STATE     READ WRITE CKSUM

    nas12t                                          DEGRADED     0     0     0

      raidz1-0                                      DEGRADED     0     0     0

        gptid/55414a31-55c3-11e3-b6e3-4ce676286ed3  ONLINE       0     0     0

        gptid/55ecb111-55c3-11e3-b6e3-4ce676286ed3  ONLINE       0     0     0

        16889620854227218693                        UNAVAIL      0     0     0  was /dev/gptid/5694c48b-55c3-11e3-b6e3-4ce676286ed3
        gptid/573cee31-55c3-11e3-b6e3-4ce676286ed3  ONLINE       0     0     0

        gptid/57e5235e-55c3-11e3-b6e3-4ce676286ed3  ONLINE       0     0     0

        gptid/58928e16-55c3-11e3-b6e3-4ce676286ed3  ONLINE       0     0     0



errors: No known data errors


[root@freenas] ~# zpool  replace nas12t /dev/gptid/5694c48b-55c3-11e3-b6e3-4ce676286ed3 ada2


[root@freenas] ~# zpool status


  pool: nas12t

 state: DEGRADED

status: One or more devices is currently being resilvered.  The pool will

    continue to function, possibly in a degraded state.

action: Wait for the resilver to complete.

  scan: resilver in progress since Sun Oct 26 06:09:47 2014

        180G scanned out of 9.47T at 110M/s, 24h32m to go

        30.0G resilvered, 1.86% done

config:



    NAME                                            STATE     READ WRITE CKSUM

    nas12t                                          DEGRADED     0     0     0

      raidz1-0                                      DEGRADED     0     0     0

        gptid/55414a31-55c3-11e3-b6e3-4ce676286ed3  ONLINE       0     0     0

        gptid/55ecb111-55c3-11e3-b6e3-4ce676286ed3  ONLINE       0     0     0

        replacing-2                                 UNAVAIL      0     0     0

          16889620854227218693                      UNAVAIL      0     0     0  was /dev/gptid/5694c48b-55c3-11e3-b6e3-4ce676286ed3

          ada2                                      ONLINE       0     0     0  (resilvering)

        gptid/573cee31-55c3-11e3-b6e3-4ce676286ed3  ONLINE       0     0     0

        gptid/57e5235e-55c3-11e3-b6e3-4ce676286ed3  ONLINE       0     0     0

        gptid/58928e16-55c3-11e3-b6e3-4ce676286ed3  ONLINE       0     0     0



errors: No known data errors

Wait many hour => for complete

[root@freenas] ~# zpool status
  pool: nas12t
 state: DEGRADED
  scan: resilvered 1.58T in 14h28m with 0 errors on Sun Oct 26 20:38:20 2014
config:

        NAME                                            STATE     READ WRITE CKSUM
        nas12t                                          DEGRADED     0     0     0
          raidz1-0                                      DEGRADED     0     0     0
            gptid/55414a31-55c3-11e3-b6e3-4ce676286ed3  ONLINE       0     0     0
            gptid/55ecb111-55c3-11e3-b6e3-4ce676286ed3  ONLINE       0     0     0
            replacing-2                                 UNAVAIL      0     0     0
              16889620854227218693                      UNAVAIL      0     0     0  was /dev/gptid/5694c48b-55c3-11e3-b6e3-4ce676286ed3
              ada2                                      ONLINE       0     0     0
            gptid/573cee31-55c3-11e3-b6e3-4ce676286ed3  ONLINE       0     0     0
            gptid/57e5235e-55c3-11e3-b6e3-4ce676286ed3  ONLINE       0     0     0
            gptid/58928e16-55c3-11e3-b6e3-4ce676286ed3  ONLINE       0     0     0

errors: No known data errors

[root@freenas] ~# zpool detach nas12t 16889620854227218693
[root@freenas] ~# zpool status
  pool: nas12t
 state: ONLINE
  scan: resilvered 1.58T in 14h28m with 0 errors on Sun Oct 26 20:38:20 2014
config:

        NAME                                            STATE     READ WRITE CKSUM
        nas12t                                          ONLINE       0     0     0
          raidz1-0                                      ONLINE       0     0     0
            gptid/55414a31-55c3-11e3-b6e3-4ce676286ed3  ONLINE       0     0     0
            gptid/55ecb111-55c3-11e3-b6e3-4ce676286ed3  ONLINE       0     0     0
            ada2                                        ONLINE       0     0     0
            gptid/573cee31-55c3-11e3-b6e3-4ce676286ed3  ONLINE       0     0     0
            gptid/57e5235e-55c3-11e3-b6e3-4ce676286ed3  ONLINE       0     0     0
            gptid/58928e16-55c3-11e3-b6e3-4ce676286ed3  ONLINE       0     0     0

errors: No known data errors
[root@freenas] ~#