Why Reorg:
Update
activity against a table can eventually lead to inefficient utilization of
space and reduced performance; use the reorg to command
reorganize the use of table space and improve performance.
Table fragmentation
occurs when rows are not stored contiguously, or when rows are split between
multiple pages. These rows require additional page access, and this reduces the
performance of the database server.
If you reduce
fragmentation and performance is still poor, another issue may be to blame,
such as inaccurate statistics
reorg is useful when:
Ø A large number of
forwarded rows causes extra I/O during read operations.
Ø Inserts and
serializable reads are slow because they encounter pages with noncontiguous
free space that must be reclaimed.
Ø Large I/O operations
are slow because of low cluster ratios for data and index pages.
Ø sp_chgattribute has been used
to change a space management setting (reservepagegap, fillfactor,
or exp_row_size) and the change is to be applied to all existing
rows and pages in a table, not just to future updates.
What is Reorg:
The reorg command can improve performance for data-only-locked
tables by improving the space utilization for tables and indexes
All of the other reorg commands, including reorg rebuild on an
index, lock a small number of pages at a time, and use short, independent
transactions to perform their work. You can run these commands at any time. The
only negative effects might be on systems that are very I/O bound.
Rebuilding a table requires space for a complete copy of the
table. Rebuilding an index works in small transactions, and deallocates pages
once they are copied; therefore, the process needs space only for the pages
copied on each transaction.
Considerations:
· Versions
of SAP ASE earlier than 15.0 restricted you from using reorg rebuild on
all-pages locked tables. SAP ASE versions 15.0 and later allow you to run reorg
rebuild on entire a table, it locks the table that uses all pages locking for
the entire time it takes to rebuild the table and its indexes. This means that
you should schedule the reorg rebuild command on a table when users do not need
access to the table.
· You must be a system administrator or
the object owner to issue reorg.
· You cannot issue reorg within a transaction.
· The table specified in
the command, if any, must use the datarows locking or datapages locking scheme.
• Exclusive
lock on table
• Copies data
from Old to New data pages
• De-allocates
old data pages
• Rebuilds
clustered and non-clustered indexes against new data pages
• Commits all
open transactions
• Releases
locks on system table
•rebuild – undoes
row forwarding, reclaims unused page space and rebuilds
an entire table or index
reorg rebuild “<TABLENAME>"
You do not need to set select into to rebuild an index.
· Rewrites
all rows to accord with a table’s clustered index, if it has one
· Rewrites
space for data and index partitions.
· Works
on individual partitions.
· Writes rows to data pages to accord with any changes made in space management settings through sp_chgattribute
•reclaim_space – reclaims unused space left by deletes
and updates. For each data page in a table, if there is unused space resulting
from committed deletes or row-shortening updates, reorg reclaim_space rewrites
the current rows contiguously, leaving all unused space at the end of the page.
If there are no rows on the page, the page is deallocated.
If a table is marked for compression, reclaim_space compresses
the data
reorg reclaim_space <table_name>
•forwarded_rows – removes row forwarding. While
unforwarding or reinserting a data row on a table configured for compression,
the row is compressed according to the table’s compression level
reorg forwarded_rows <table_name>
•compact – performs both reclaim_space and
forwarded_rows
reorg compact <table_name>
•defrag - reorganizes each partition list or partition
in the table while allowing concurrent reads or writes on the data being
reorganized.
reorg defrag <table_name>
sp_helpdefrag reports
defragmentation information for either all eligible objects for reorg defrag in the database whose context it
is invoked from or for the given object if it is eligible for reorg defrag
NOTE: REORG DEFRAG can be considered to be a cut down
version of REORG REBUILD which can be run outside of maintenance windows.
Reorg-presteps:
1.reorg rebuild requires that you set the database option select
into/bulkcopy/pllsort to true and run checkpoint in the database.
>sp_dboption <DBNAME>,"select
into/bulkcopy/pllsort",true
>go
2.reorg rebuild requires additional disk space equal to the size of the table and its indexes. You can find out how much space a table currently occupies by using sp_spaceused. You can use sp_helpsegment to check the amount of space available
Types of reorgs:
1.offline reorgs
In order to mitigate any database-related factors which maybe
affecting performance, you can reorganize and rebuild the database tables
manually via command line tool "isql", in addition to and/or outside
of DBACockpit ATM routines.
The reorganization and rebuild of tables and their indexes may
have to be run outside of the regularly-scheduled DBACockpit ATM routines. A
manual option is available to reorganize a single table that has been
identified in slow-running queries from jobs, and from deadlocks reported in
the ASE error log.
It is advisable to run the manual "reorg" during
periods of minimal activity.
1. Log on to the operating system with syb<sid>
2. Open a shell / command line and connect to the database
server using isql. Enter the password of sapsa user when prompted:
isql -Usapsa -S<SID> -D<SID> -X -w4000
•Use the following command to reorganize and rebuild for AS
ABAP tables:
use <SID>
go
set quoted_identifier on
go
reorg rebuild SAPSR3."<TABLENAME>"
go
•Use the following command to reorganize and rebuild for AS
Java tables:
use <SID>
go
set quoted_identifier on
go
reorg rebuild SAPSR3DB."<TABLENAME>"
go
2.online reorgs
It is not advisable to running online reorg rebuild on tables
with page-level compression on currently available releases of SAP ASE
REORG REBUILD of a table with ONLINE may cause data corruption
> At the index-level:
For small-medium sized tables, indexes are rebuilt in
"online" mode via DBACockpit ATM (you can verify this via the profile
details). "REORG REBUILD <table> <index>" is run
"with online" option, by default on SAP ASE.
For large tables’ indexes, by default, DBACockpit ATM runs the
reorg with "compact/forwarded_rows" options.
> At the table-level:
The "REORG REBUILD <table> WITH ONLINE" can be
run manually via isql; as such, on DML-intensive SAP Systems, it is recommended
to run this command during periods of minimal activity, especially on large
tables.
Another option is using “REORG DEFRAG WITH RESUME” for large
tables; this is available as an ATM profile option.
In this way the DBA/administrator has control over the
time/execution of this fairly intrusive command.
1. Log on to the operating system with syb<sid>
2. Open a shell / command line and connect to the database
server using isql. Enter the password of sapsa user when prompted:
isql -Usapsa -S<SID> -D<SID> -X -w4000
Use the following command to reorganize and rebuild for AS
ABAP tables:
set quoted_identifier on
go
reorg rebuild SAPSR3."<TABLENAME>" with online
go
Use the following command to reorganize and rebuild for AS
Java tables:
set quoted_identifier on
go
reorg rebuild SAPSR3DB."<TABLENAME>" with
online
go
Post steps:
1. set the database option select into/bulkcopy/pllsort to
false in the database.
>sp_dboption <DBNAME>,"select
into/bulkcopy/pllsort”, false
>go
2.After running reorg rebuild, you must dump the database
before you can dump the transaction log since the command creates unlogged
transactions
NOTE: optdiag utility is used to
check the clustered ratio
If the clustered ration is >
0.9 then we need to
run reorg rebuild
optdiag is located
in $SYBASE/$SYBASE_ASE/bin