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:
- Ctas
- Move
- Export /import
- Online redefinition
- Shrink
Index defragmention
What we check in table fragementation:
- Table size
- Num_rows
- Blocks and empty_blocks
- 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;
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;
SQL> alter index IDX_OBJ_ID rebuild online;
STEP 21: CHECK THE STATUS AS VALID: