Fragmentation

Fragmentation of tables and indexes may reduce performance.
Fragmentation also leads to greater overall storage space usage.

There are two level
• Table Fragmentation
• Index Fragmentation

Table analyze two level
1.compute statistics

Analyze table tablename compute statistics;
2.Estimate statistics

Analyze table tablename estimate statistics sample 70 percent;

Analyze Packages

• GATHER_TABLE_STATS
• GATHER_INDEX_STATS
• GATHER_SYSTEM_STATS
• GATHER_SCHEMA_STATS
• GATHER_DICTIONARY_STATS
• GATHER_FIXED_STATS
• GATHER_DATABASE_STATS

Example:

exec dbms_stats.gather_table_stats('OWNERNAME','TABLENAME');

Table size check Views

• Dba_tables
• Dba_segments
• Dba_indexes
• Dba_free_space

Example:

exec dbms_stats.gather_table_stats('OWNERNAME','TABLENAME');

Table Defragmention types:
1. Ctas
2. Move
3. Export /import
4. Online redefinition
5. Shrink

Index defragmention

What we check in table fragementation:
1. Table size
2. Num_rows
3. Blocks and empty_blocks
4. How much percentage fragment

BELOW THE STEPS . WE CREATE THE TABLE AND INSERT SOME RECORD. WE DO FRAGEMENT AND HOW ITS DEFRAGMENT:

STEP 1 : CREATE TABLESPACE AND IT TO BE SET DEFAULT:

STEP 2 : CREATE A TABLE AND IT NAME HAS FRAG AND INSERT SOME RECORDS:

STEP 3 : CHECK THE TABLE_SIZE AND NUM OF ROWS:

STEP 4 : ANALYZE THE TABLE:, THEN CHECK THE TABLE:

STEP 5 : INSERT SOME MORE RECORDS:

STEP 6 : ANALYZE ONCE AGAIN: CHECK THE NUM OF ROWS, BYTES:

STEP 7: DELETE SOME RECORDS FOR FRAGMENT:

STEP 8: DELETE AFTER ANALYZE NUM_ROWS ONLY CHANGE, BLOCKS AND BYTES NOT:

STEP 9: NOW DEFRAGMENT THE TABLE USING CTAS METHOD:

STEP 10: ANTOHER METHOD OF DEFRAGMENTATION:

STEP 11:ANOTHER METHOD TO FIND FRAGEMENTATION:
-->WE HAVE 80000 NUM of rows before fragment






AFTER MOVE FRAGEMENTATION:



PREVIOUSLY 1518 BLOCKS IN A TABLE, AFTER MOVE FRAGEMENTION BLOCKS CAN BE REDUCE IT HAS NOW 737 AND THE BYTES 6MB.

STEP 12: ENABLE THE ROW MOVEMENT:

STEP 13: NOW SEE THE DIFFERNECE

STEP 14: ANOTHER METHOD EXPORT AND IMPORT:

[oracle@tesdb ~]$ mkdir backup
[oracle@tesdb ~]$ cd backup/
[oracle@tesdb backup]$ ll
total 0
[oracle@tesdb backup]$ export ORACLE_SID=dev
[oracle@tesdb backup]$ exp file=frag.dmp log=frag.log tables=frag1 statistics=none
Export: Release 12.2.0.1.0 - Production on Thu Sep 28 17:25:32 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: tesdb/tesdb
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

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

STEP 15: NOW THE DROP TABLE:

SQL> drop table frag1 purge;
Table dropped.

STEP 16: IMPORT THE DUMPFILE:

[oracle@tesdb backup]$ imp file=frag.dmp log=imp.log fromuser=tesdb touser=tesdb
Import: Release 12.2.0.1.0 - Production on Thu Sep 28 17:29:17 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: tesdb/tesdb
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Export file created by EXPORT:V12.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
IMP-00403:

Warning: This import generated a separate SQL file "imp_sys.sql" which contains DDL that failed due to a privilege issue.

. importing TESDB's objects into TESDB
. . importing table "FRAG1" 60000 rows imported
Import terminated successfully with warnings.

STEP 17: INDEX FRAGEMENTATION:

• Index fragmentation may bring a higher performance Issue.
• When accessing data through an index and an index range scan.


SQL > alter table indfrag move tablespace users;

STEP 18: MOVE TABLE AFTER GATHER STATS:

STEP 19: CHECK THE STATUS.IT HAS TO BE UNUSABLE:

STEP 20: DEFRAGEMENT THE TABLE:
Index rebuild

SQL> alter index i1 rebuild;
Index altered.
SQL> alter index IDX_OBJ_ID rebuild online;
Index altered.

STEP 21: CHECK THE STATUS AS VALID:

View:
Dba_indexes