Description:-
In OLTP database environment some times performance degraded because of Table plan changes, row-chaining and Row-migration issues. Based on transactions on table, it may require maintenance activities such as table reorganization. After table reorg, it is seen performance improved a lot.
Fragmentation is bad for database for the below reason:-
Negative Performance impact:-SQL statements that performs full-scan and large index range scans may run more slowly in a fragmented table. When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses.
Wasted Disk Space:- It means you have space in your disk which your database can not use.
What is the causes Fragmentation happen in database?
As DML activity happens in the database, it is possible for there to be discontinuous chunks, or fragments of unused space within the tablespace and fragmentation within the table rows.
When you insert or update row in table?
As rows are added to tables, the table expands into unused space within the space. It will naturally fragment as discontiguous data blocks are fetched to receive new rows. Updating table records may also cause row chaining if the updated row can’t fit into same data block.
When you delete rows from table?
At deletion, a table may coalesce extents, releasing unused space back into the tablespace. A lot of deletes leaves high-water mark behind at a high value. It will cause slower full-table-scan performance since Oracle must read to the high water mark.
When you insert or update row in table?
As rows are added to tables, the table expands into unused space within the space. It will naturally fragment as discontiguous data blocks are fetched to receive new rows. Updating table records may also cause row chaining if the updated row can’t fit into same data block.
Types of Reorg:-
Alter table Move
CTAS method
Datapump or exp/imp
Demo:-Here we are going to see archiving and purging data (only 365 days data only going to import . Here we are getting two advantages one is Reorg & decreasing size of the table )
Oracle 12c Table Reorganization using Expdp and Impdp operation :-
Steps :-
High level steps involved in this process are
- Precheck in table and index level
- Table archiving and purging
- Collecting the Statistics
- Post check in table and index level
Precheck in table and index level:
Check the Table size and Index size
select segment_name,TABLESPACE_NAME ,segment_type, bytes/1024/1024/1024 size_gb from dba_segments where segment_name = '&segment_name' or segment_name in (select index_name from dba_indexes where table_name='&tablename' and table_owner='&owner');
Check the invalid object count
SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS=’INVALID’;
Check the LAST_ANALYZED stats of the table
SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME IN (&tablename);
Table and Index size output before REORG :-
select segment_name,TABLESPACE_NAME ,segment_type, bytes/1024/1024/1024 size_gb from dba_segments where segment_name = '&segment_name' or segment_name in (select index_name from dba_indexes where table_name='&tablename' and table_owner='&owner'); SEGMENT_NAME TABLESPACE_NAME SEGMENT_TYPE SIZE_GB --------------- -------------------- ------------ ---------- X_DMMMK INDEXESN2 INDEX 61.4998779 DIM_IDX_01 DATA INDEX 14.8768311 X_DMRJE INDEXESN6 INDEX 67.5897217 IDX$$_1BBA50004 DATA INDEX 15.9821167 DIM_MM DATAN1 TABLE 131.789124 PK_DIM_MM INDEXESN5 INDEX 20.3564453 X_DIMMM INDEXESN5 INDEX 31.295166 7 rows selected.
Check HWM and fragmentation in tables:-
High water mark(HWM) check query :
set verify off
column owner format a10
column alcblks heading 'Allocated|Blocks' just c
column usdblks heading 'Used|Blocks' just c
column hgwtr heading 'High|Water' just c
break on owner skip page
select a.owner,a.table_name,b.blocks alcblks,a.blocks usdblks,(b.blocks-a.empty_blocks-1)
hgwtr from dba_tables a,dba_segments b where a.table_name=b.segment_name
and a.owner=b.owner and a.owner not in('SYS','SYSTEM') and a.blocks <> (b.blocks-a.empty_blocks-1)
and a.owner like upper('&owner')||'%'and a.table_name like upper('&table_name')||'%'
order by 1,2;
Enter value for owner: LOVIA_PROD
Enter value for table_name: DIM_MM
OWNER TABLE_NAME
------- ---------------------------------------------------
LOVIA_PROD DIM_MM
Allocated Used High
Blocks Blocks Water
--------- ------- ---------
14069376 13861652 14069375
Table Fragmentation query in database level :
set lines 170
set pages 10000
col owner format a30
col table_name format a30
col TOTAL_SIZE format 99999999999
col ACTUAL_SIZE format 999999999999
col FRAGMENTED_SPACE format 999999999999
select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),0) "TOTAL_SIZE", round((num_rows*avg_row_len
/1024/1024),0) "ACTUAL_SIZE", round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),0) "FRAGMENTED_SPACE" from
dba_tables where owner not in ('SYS','SYSTEM','FDBA','PERFSTAT','DBMON') and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)
> 100 order by 8 desc;
OWNER TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN TOTAL_SIZE
----- ---------- ------ -------- ----------- ------------ ACTUAL_SIZE FRAGMENTED_SPACE
----------- ----------------
LOVIA_PROD DIM_MM 15699345 377267414 285 122651 102540 20111
Table archiving and purging :
- Checking min. value of the DIM_MM table after import we have only last year(sysdate -365) data.
select min(trunc(DIM_ASOF_DATE)) from LOVIA_PROD.DIM_MM order by DIM_ASOF_DATE desc; MIN(TRUNC --------- 1-MAR-16
- Before export,check the count for the last one year
SQL> select count(*) from LOVIA_PROD.DIM_MM where DIM_ASOF_DATE > sysdate-365
COUNT(*)
---------
304083606
- Export the data into DIM_MM table,
expdp parfile='expdp_lovia_dim_mm.par'
userid=system/****** DIRECTORY=EXPTEMP tables=LOVIA_PROD.DIM_MM DUMPFILE=lovia_dim_mm.dmp logfile=expdp_lovia_dim_mm.log
Export: Release 12.1.0.2.0 - Production on Sat Sep 15 23:33:24 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_27": userid=system/******** DIRECTORY=EXPTEMP tables=LOVIA_PROD.DIM_MM DUMPFILE=lovia_dim_mm.dmp logfile=expdp_lovia_dim_mm.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 131.7 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "LOVIA_PROD"."DIM_MM" 102.8 GB 416624179 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_27" successfully loaded/unloaded
***********************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_27 is:
/orashare1/LINUX_MIGRATION/export/lovia_dim_mm.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_27" successfully completed at Sun Sep 16 00:48:48 2018 elapsed 0 01:15:23
- Import the data into DIM_MM table which is less than 365 days,
userid=system/***** directory=EXPTEMP DUMPFILE=lovia_dim_mm.dmp logfile=impdp_lovia_dim_mm.log parallel=3 table_exists_action=replace query=LOVIA_PROD.DIM_MM:'"where DIM_ASOF_DATE > sysdate-365"' ;;; Import: Release 12.1.0.2.0 - Production on Sun Sep 16 02:19:40 2018 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. ;;; Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_FULL_01": system/******** AS SYSDBA directory=EXPTEMP DUMPFILE=lovia_dim_mm.dmp logfile=impdp_lovia_dim_mm.log parallel=7 table_exists_action=replace query=LOVIA_PROD.DIM_MM:"where DIM_ASOF_DATE > sysdate-365" Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "LOVIA_PROD"."DIM_MM" 102.8 GB 332249336 out of 416624179 rows Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/TRIGGER Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Sun Sep 16 05:15:29 2018 elapsed 0 02:55:23
- After impdp,check the data which has recent data less than 365 days
select count(*) from lovia_prod.DIM_MM;
COUNT
---------
332249336
Collecting the Gather the statistics of the table
spool /orashare1/Tablearchive/lvprod/DIM_MM/LOVIA_PROD_DIM_MM_STATS.log SET PAGESIZE 2000 SET LINESIZE 500 SET TIME ON TIMING ON; SET FEED ON set echo on begin DBMS_STATS.GATHER_TABLE_STATS(ownname => 'LOVIA_PROD', tabname => 'DIM_MM', ESTIMATE_PERCENT => 100,METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE,DEGREE => 200); end; / PL/SQL procedure successfully completed
Post check in table and index level :-
a) Checking index status after import:
SELECT INDEX_NAME, TABLE_NAME,STATUS FROM ALL_INDEXES WHERE TABLE_NAME IN ('DIM_MM');
INDEX_NAME TABLE_NAME STATUS
--------------- ------------ ------
X_DIMMM DIM_MM VALID
PK_DIM_MM DIM_MM VALID
IDX$$_1BBA50004 DIM_MM VALID
X_DMRJE DIM_MM VALID
DIM_IDX_01 DIM_MM VALID
X_DMMMK DIM_MM VALID
6 rows selected.
b) Checking object status:
SELECT OBJECT_NAME,OBJECT_TYPE,STATUS FROM DBA_OBJECTS WHERE OWNER='LOVIA_PROD' AND STATUS='INVALID'; no rows selected.
c) Checking min. data of the DIM_MM table:
select min(trunc(DIM_ASOF_DATE)) from LOVIA_PROD.DIM_MM order by DIM_ASOF_DATE desc; MIN(TRUNC --------- 16-SEP-17
d) Check SYSDATE from database level:
SQL> select sysdate from dual; SYSDATE --------- 16-SEP-18
Table and Index size query after REORG :-
select segment_name,TABLESPACE_NAME ,segment_type, bytes/1024/1024/1024 size_gb from dba_segments where segment_name = '&segment_name' or segment_name in (select index_name from dba_indexes where table_name='&tablename' and table_owner='&owner'); DIM_MM :- SEGMENT_NAME TABLESPACE_NAME SEGMENT_TYPE SIZE_GB --------------- --------------- ------------ ---------- X_DIMMM INDEXESN5 INDEX 23.8910522 DIM_MM DATAN1 TABLE 85.7998056 PK_DIM_MM INDEXESN5 INDEX 14.9221191 IDX$$_1BBA50004 DATA INDEX 12.0922241 X_DMRJE INDEXESN6 INDEX 50.168457 DIM_IDX_01 DATA INDEX 10.7139282 X_DMMMK INDEXESN2 INDEX 45.6452026 7 rows selected.
Fragmentation output after table reorg:
OWNER TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN TOTAL_SIZE ----- ---------- ------ -------- ----------- ---------- ACTUAL_SIZE FRAGMENTED_SPACE ----------- ---------------- LOVIA_PROD DIM_MM 13861652 332249336 285 85.7998056 71.0765423 15990
Catch Me On:- Hariprasath Rajaram
Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba
