An index is a database object that used primarily to improve the performance of SQL queries.
How Often to Rebuild Indexes
Rebuilding indexes
is both a resource intensive and blocking task. Both these considerations make
it ideal as an offline activity, to be run when as few users as possible are
accessing a database. In general, this means during a scheduled maintenance
window.
It is not really
feasible to devise a catch-all plan with regard to when and how often to
rebuild indexes. These decisions are highly dependent on the type of data you
work with, as well as the indexes and queries that are utilized. With that in
mind, here are a few guidelines regarding when to rebuild indexes:
·
Rebuilding
Indexes Nightly
If your indexes
fragment rapidly, and you have a nightly maintenance window that allows you to
run the Rebuild Index task, in addition to all your other maintenance tasks,
then, by all means, go ahead.
·
Weekly,
at minimum
If you can't rebuild
indexes on a nightly basis, then, it should be done once a week at a minimum.
If you wait much longer than a week, you risk hurting your SQL Server's
performance due to the negative impact of wasted empty space and logical
fragmentation.
·
Alternative
scheduling
If you don't have a
maintenance window accommodate this task at least once a week, then you need to
pay close attention to how your indexes are faring.
Some of the key reasons to rebuild an
index include the following:
•You want to rebuild an index that has
become fragmented over time.
•You want to rebuild an index after a
large, direct-path load of data.
•You want to move an index to a
different tablespace.
•The index is in an unusable state
because of a partition-level operation on the associated table.
The main disadvantage of the index
rebuild process is that you will need space for both indexes, which is required
during the rebuild process. also keep in mind that the degree of parallelism
stays on the index
after the creation is complete so
therefore need to change it again with
no parallel or need to reset the degree
of parallelism to its original value on the index after creating it.
To ascertain index fragmentation, the following
SQL statement can be used:
SQL>analyze index index_name
validate structure;
SQL>SELECT name,del_lf_rows,lf_rows
- del_lf_rows lf_rows_used,
to_char(del_lf_rows /
(lf_rows)*100,'999.99999') ibadness
FROM index_stats where name ='index_name';
if 15-20% of the table data changes,
then you may consider rebuilding the index.
There are a couple of effective methods
for freeing up unused space associated with an index:
•
Rebuilding the index
•
Shrinking the index
Before you perform either of these
operations, first check USER_SEGMENTS to verify that the amount of space used
corresponds with the Segment Advisor’s
advice. In this example, the segment name is F_REGS_IDX1:
SQL> select bytes from user_segments
where segment_name = 'F_REGS_IDX1';
BYTES
----------
166723584
This example uses the ALTER
INDEX...REBUILD statement to re-organize and compact the space used by an
index:
SQL> alter index f_regs_idx1
rebuild;
Alternatively, use the ALTER
INDEX...SHRINK SPACE statement to free up unused space in an index—for example:
SQL> alter index f_regs_idx1 shrink
space;
Index altered.
Now query USER_SEGMENTS again to verify
that the space has been de-allocated. Here is the output for this
example:
BYTES
----------
524288
The space consumed by the index has
considerably decreased.
If you use the ALTER INDEX...SHRINK
SPACE operation to free up unused index space, keep in mind that
this feature requires that the target
object must be created within a tablespace with automatic segment space
management enabled
The clustering factor reflects how
sorted the table data is with respect to the given index key. Rebuilding an index never has an influence on
the clustering factor but instead requires a table re-organization.
Secondly the impact of rebuilding the
index can be quite significant, please read the following comments thoroughly:
1. Most scripts around depend on the
index_stats dynamic table. This is populated by the command:
analyze index ... validate structure;
While this is a valid method to inspect
the index, it grabs an exclusive table lock while analyzing the index. Especially for large indexes, this can be
very dramatic, as DML operations on the table are not permitted during that
time. While it can be run online without
the locking considerations, it may consume additional time.
2. Redo activity may increase and general performance might be impacted as a direct result of rebuilding an index.
Insert/update/delete causes the index
to evolve over time as the index splits and grows. When the index is rebuild,
it will become more tightly packed; however as DML operations continue on the
table, the index splits have to be redone again until the index reaches its
equilibrium. As a result, the redo activity increases and the index splits are
now more likely to impact performance directly as we consume more I/O, CPU, etc
to serve the index restructuring. After a certain period of time the index may
again experience 'issues' and may be re-flagged for a rebuild, causing the
vicious cycle to continue. Therefore, it is often better to leave the index in
its natural equilibrium and/or at least prevent indexes from being rebuilt on a
regular basis.
3. An index coalesce is often preferred
instead of an index rebuild. It has the following advantages:
- does not require approximately 2 times the disk storage
- always online
- does not restructure the index, but combines index leaf blocks as much
as possible, avoiding system overhead as explained in point 2.
Generally speaking, the need to rebuild
b-tree indexes is very rare, basically because a b-tree index is largely self-managed
or self-balanced.
The most common justifications given
for rebuilding an index are:
- index becomes fragmented
- index grows and grows - deleted space
is not re-used
- index clustering factor becomes out
of sync
An index coalesce is often preferred
instead of an index rebuild. It has the following advantages:
does not require approximately 2 times the disk storage
- always online
- does not restructure the index, but combines index leaf blocks as much
as possible, avoiding system overhead as explained in point 2.
Note: To re-allocate an index, to
another tablespace for example a rebuild is required.
- deleted entries represent 20% or more
of the current entries.
- the index depth is more then 4
levels.
Lab-1
Check top
object with high DML
select m.table_owner,m.table_name,
m.timestamp, sum(m.inserts), sum(m.updates), sum(m.deletes), t.num_rows,
t.last_analyzed from
sys.dba_tab_modifications m, dba_tables t
where m.table_owner = t.owner and m.table_name = t.table_name and
m.table_owner not
in('SYS','OUTLN','SYSTEM','TSMSYS','DBSNMP','WMSYS','EXFSYS','XDB','OLAPSYS','MDSYS','CAGAUDIT','SYSMAN','DMSYS','CTXSYS','ORDSYS')
group by m.table_owner,m.table_name,
m.timestamp, t.num_rows,t.last_analyzed
order by 1,2
Check
specific table now
select m.table_owner,m.table_name,
m.timestamp, sum(m.inserts), sum(m.updates), sum(m.deletes), t.num_rows,
t.last_analyzed from
sys.dba_tab_modifications m, dba_tables t
where m.table_owner = t.owner and m.table_name = t.table_name and
m.table_name in('PAYMENT_PLAN_CREDIT_SHARE','ZEUS_BUILD_FAILURES') group
by m.table_owner, m.table_name, m.timestamp, t.num_rows, t.last_analyzed order by 1,2
4
/
TABLE_NAME TIMESTAMP SUM(M.INSERTS) SUM(M.UPDATES)
SUM(M.DELETES) NUM_ROWS LAST_ANALYZ
------------------------------
----------- -------------- -------------- -------------- ---------- -----------
PAYMENT_PLAN_CREDIT_SHARE 23-may-2017 266574883 265166199 21923 2334869164 23-nov-2016
select bytes/1024/1024/1024 from dba_segments
where segment_name='PAYMENT_PLAN_CREDIT_SHARE';
BYTES/1024/1024/1024
--------------------
371.484375
Check indexes
on Table
SELECT t.table_name,
i.index_name,i.blevel,i.status,t.last_analyzed FROM dba_tables t, dba_indexes i
WHERE t.table_name = i.table_name AND t.table_name
='PAYMENT_PLAN_CREDIT_SHARE';
TABLE_NAME INDEX_NAME BLEVEL STATUS LAST_ANALYZ
------------------------------
------------------------------ ---------- -------- -----------
PAYMENT_PLAN_CREDIT_SHARE PF_PAY_PLAN_CRED_S_03 3 VALID 23-nov-2016
PAYMENT_PLAN_CREDIT_SHARE PF_PAY_PLAN_CRED_S_05 3 VALID
23-nov-2016
PAYMENT_PLAN_CREDIT_SHARE PF_PAY_PLAN_CRED_S_04 3 VALID 23-nov-2016
PAYMENT_PLAN_CREDIT_SHARE PK_PAY_PLAN_CRED_S 3 VALID 23-nov-2016
PAYMENT_PLAN_CREDIT_SHARE PF_PAY_PLAN_CRED_S_01 3 VALID 23-nov-2016
PAYMENT_PLAN_CREDIT_SHARE PF_PAY_PLAN_CRED_S_02 3 VALID 23-nov-2016
select
owner,segment_name,segment_type,bytes/1024/1024/1024 from dba_segments where
segment_name in ('PF_PAY_PLAN_CRED_S_03','PF_PAY_PLAN_CRED_S_05','PF_PAY_PLAN_CRED_S_04','PK_PAY_PLAN_CRED_S','PF_PAY_PLAN_CRED_S_01','PF_PAY_PLAN_CRED_S_02');
OWNER SEGMENT_NAME
SEGMENT_TYPE
BYTES/1024/1024/1024
------------------------------
---------------------------------------------------------------------------------
------------------ --------------------
SCHEMANAME PF_PAY_PLAN_CRED_S_05 INDEX 136.523438
SCHEMANAME PF_PAY_PLAN_CRED_S_04
INDEX
125.683594
SCHEMANAME PF_PAY_PLAN_CRED_S_03
INDEX
166.503906
SCHEMANAME PF_PAY_PLAN_CRED_S_02
INDEX 114.648438
SCHEMANAME PF_PAY_PLAN_CRED_S_01
INDEX
143.75
SCHEMANAME PK_PAY_PLAN_CRED_S INDEX 184.082031
Upon
identification of index issue ,rebuild
index.
Existing index can be rebuilt quickly by using
parallel option. It will use multiple
processes to speed up the index rebuild process.
spool rebuild_index.sql
select 'ALTER INDEX
'||index_name||'.'||','|| owner||' REBUILD online nologging PARALLEL 8; ' from
dba_indexes where owner='DMS_USER' AND INDEX_NAME
IN('PRCL_ITEM_PRCL_FK_I','CUST_REQ_CUSTORD_FK_I','CUSTORD_PK','PRCL_ITEM_PK','PRCL_ITEM_PCDR_DATE_I','PRCL_PK','CUST_REQIT_CUST_REQ_FK_I','PRCL_ITEM_PP_FK_I',
'PRCL_ITEM_WMS_STAT_I','WMSORD_PK','P_LOCN_DC_LOCN_FK_I','PLS_PROD_I','WSF_PROD_I','CUST_REQIT_PP_FK_I')
order by 3;
spool off;
ALTER INDEX
LITTLEWOODS.WEBREP_DEPOT_QUERY_UPI_IDX REBUILD ONLINE tablespace TSPACEI02A
PARALLEL 4 NOLOGGING;
ALTER INDEX
LITTLEWOODS.WEBREP_DEPOT_QUERY_UPI_IDX NOPARALLEL LOGGING;
ALTER INDEX LITTLEWOODS.CO_CN_IX
REBUILD ONLINE tablespace TSPACEI02A PARALLEL 4 NOLOGGING;
ALTER INDEX LITTLEWOODS.CO_CN_IX
NOPARALLEL LOGGING;
ALTER INDEX LITTLEWOODS.CO_DE_FK_I
REBUILD ONLINE tablespace TSPACEI02A PARALLEL 4 NOLOGGING;
ALTER INDEX LITTLEWOODS.CO_DE_FK_I
NOPARALLEL LOGGING;
ALTER INDEX LITTLEWOODS.PK_IPB REBUILD
ONLINE tablespace TSPACEI02A PARALLEL 4 NOLOGGING;
ALTER INDEX LITTLEWOODS.PK_IPB
NOPARALLEL LOGGING;
S set linesize 200
SQL>
SQL>
select owner,segment_name,SEGMENT_TYPE,bytes/1024/1024 from dba_segments where
tablespace_name='TSPACEI02A';
exec
dbms_stats.gather_index_stats('LITTLEWOODS', 'CO_DE_FK_I');
exec
dbms_stats.gather_index_stats('LITTLEWOODS', 'CO_RE_FK_I');
exec dbms_stats.gather_index_stats('LITTLEWOODS',
'CO_CN_IX');
exec
dbms_stats.gather_index_stats('LITTLEWOODS', 'CO_CU_FK_I')
Release space from table space if
required
COL TABLESPACE_NAME FORMAT A30
select
a.TABLESPACE_NAME,
a.TOTAL_IN_GB,
to_char(nvl((b.used),0.00),'999990.99') USED_IN_GB,
to_char(nvl((c.free),0.00),'999990.99') FREE_IN_GB,
to_char(nvl(((b.used/a.TOTAL_IN_GB)*100),0.00),'99990.99') PCT_USED
from
(select TABLESPACE_NAME,
to_char(sum(bytes)/(1024*1024*1024),'9999990.99') TOTAL_IN_GB
from sys.dba_data_files
group by TABLESPACE_NAME) a,
(select TABLESPACE_NAME,bytes/(1024*1024*1024) used
from sys.SM$TS_USED) b,
(select TABLESPACE_NAME,bytes/(1024*1024*1024) free
from sys.SM$TS_free) c
where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+) and
a.TABLESPACE_NAME=c.tablespace_name(+)
TABLESPACE_NAME TOTAL_IN_MB USED_IN_MB
FREE_IN_MB PCT_USED
------------------------------
----------- ---------- ---------- ---------
TSP_ANNOTATION_I 60.94 18.85
41.11 30.93
TSP_TC_ACCOUNT_SNAPSHOT 64.94 20.51
41.99 31.58
TSP_PARCEL_ITEM_I 83.08 25.88
55.66 31.15
TSP_ORDER_REQUEST_I 75.99
28.32 46.58 37.27
TSP_LARGE 3552.47 2323.34
1618.65 65.40
TSP_MEDIUM 1393.37 980.16
412.52 70.34
TSP_ANNOTATION 50.60 31.25
14.65 61.76
TSP_LARGE_ARC 46.94 9.28
37.60 19.76
TSP_ORDER_ITEM_I 235.02 51.76
179.98 22.02
TSP_MINIMUM 7.91 7.07 3.18
89.39
TSP_PARCEL_ITEM 28.26 21.48 3.91
76.02
For reclaiming space from Tablespace ,We need
to follow below steps ,As this is large tablespace , We need to move huge
number of tables , indexes LOBINDEX and
LOBSEGMENT whose details are below
select count(1) from segment_type from dba_segments group
by segment_type.
COUNT(1)
SEGMENT_TYPE
---------- ------------------
367 INDEX
22 LOBINDEX
22 LOBSEGMEN
377 TABLE
eg
1) Create New Tablespace TSP_LARGE_NEW with appropriate size
2) Move a table to another tablespace, issue the following command:
ALTER TABLE PAYMENT_PLAN_CREDIT_SHARE MOVE TABLESPACE TSP_LARGE_NEW;
3) To move an index, use the following:
alter index SCHEMANAME.PK_PAY_PLAN_CRED_S parallel 8 TSP_LARGE_NEW
alter index SCHEMANAME.PF_PAY_PLAN_CRED_S_05 parallel 8
TSP_LARGE_NEW
alter index SCHEMANAME.PF_PAY_PLAN_CRED_S_04 parallel 8
TSP_LARGE_NEW
alter index SCHEMANAME.PF_PAY_PLAN_CRED_S_03 parallel 8
TSP_LARGE_NEW
alter index SCHEMANAME.PF_PAY_PLAN_CRED_S_02 parallel 8
TSP_LARGE_NEW
alter index SCHEMANAME.PF_PAY_PLAN_CRED_S_01 parallel 8
TSP_LARGE_NEW
alter index SCHEMANAME.PK_PAY_PLAN_CRED_S noparallel
alter index SCHEMANAME.PF_PAY_PLAN_CRED_S_05 noparallel
alter index SCHEMANAME.PF_PAY_PLAN_CRED_S_04 noparallel
alter index SCHEMANAME.PF_PAY_PLAN_CRED_S_03 noparallel
alter index SCHEMANAME.PF_PAY_PLAN_CRED_S_02 noparallel
alter index SCHEMANAME.PF_PAY_PLAN_CRED_S_01 noparallel
4) To move the LOB when moving the table, use the
following:
ALTER TABLE table_name MOVE TABLESPACE TSP_LARGE
LOB (lob_item) STORE AS (TABLESPACE TSP_LARGE_NEW);
5) Drop old Tablespace TSP_LARGE.
DROP TABLESPACE tspacei02 including contents and datafiles;
DROP TABLESPACE tspacei02 including datafiles;
There are several ways to reclaim wasted space in a table
but From Oracle 10g onwards ,this is
best method to reclaim space .
1. Enable row movement for the table.
2.Use the ALTER TABLE...SHRINK SPACE statement to free up
unused space.
Note The shrink
table feature requires that the table’s tablespace use automatic space segment
management.
When you shrink a table, this requires that rows (if any)
be moved which requires that row movement be enabled:
SQL> alter table <table_name> enable row movement;
Next the table shrink operation is executed via an ALTER
TABLE statement:
SQL> alter table <table_name> shrink space;
You can also shrink the space associated with any index segments via the CASCADE clause:
SQL> alter table
<table Name> shrink space cascade
Database
Monitoring
We need to monitor archive log
and tablespace usages during index rebuild
Archive Log Monitoring script
----------------------------
column USED_GB format 999,990.999
column USED% format 990.99
column RECLAIM_GB format
999,990.999
column RECLAIMABLE% format 990.99
column LIMIT_GB format
999,990.999
select frau.file_type as type,
frau.percent_space_used/100 *
rfd.space_limit /1024/1024 "USED_MB",
frau.percent_space_used
"USED%",
frau.percent_space_reclaimable
"RECLAIMABLE%",
frau.percent_space_reclaimable/100
* rfd.space_limit /1024/1024 "RECLAIM_MB",
frau.number_of_files
"FILES#"
from v$flash_recovery_area_usage
frau,v$recovery_file_dest rfd order by file_type;
select name from
v$recovery_file_dest;
show parameter
db_recovery_file_dest_size
To check TableSpace Usages
col "TABLESPACE" for a50
col "Totalspace(MB)" for 9999999
col "Used Space(MB)" for 9999999
set linesize
200
select t.tablespace,
t.totalspace as "
Totalspace(MB)",
round((t.totalspace-nvl(fs.freespace,0)),2) as "Used
Space(MB)",
nvl(fs.freespace,0) as
"Freespace(MB)",
round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as
"%Used",
round((nvl(fs.freespace,0)/t.totalspace)*100,2)
as "% Free"
from
(select
round(sum(d.bytes)/(1024*1024)) as totalspace,
d.tablespace_name tablespace
from dba_data_files d group by
d.tablespace_name) t,
(select
round(sum(f.bytes)/(1024*1024)) as freespace,
f.tablespace_name
tablespace
from dba_free_space f group by
f.tablespace_name) fs
where t.tablespace=fs.tablespace (+)
order by t.tablespace;
To check Temp TableSpace
select sysdate dtstamp,
s.tablespace_name,
d.tbspc_mb,
s.total_blocks*8192/1024/1024 temp_tot_mb,
s.used_blocks*8192/1024/1024 temp_used_mb,
s.free_blocks*8192/1024/1024 temp_free_mb
from v$sort_segment s,
(select tablespace_name,sum(bytes/1024/1024)
tbspc_mb
from dba_data_files
group by tablespace_name
union
select tablespace_name,sum(bytes/1024/1024)
tbspc_mb
from dba_temp_files
group by tablespace_name) d
where s.tablespace_name=d.tablespace_name;
No comments:
Post a Comment