on the local DB areprocessed.
To process the indexes of a user in a remote DB, this remote DB must bemaintained in transaction DBCO.
If no index, table, tablespace, storage quality or index size isspecified, the workingset to be analyzed is not restricted by thiscriteria. Especially when leaving all fielda empty ALL indexes areprocessed.
It is recommended to process only indexes larger than 10 MB to keepruntime, datavolume etc. small. The algorithm to calculate the storagequality is optimized for large indexes. Small indexes can have lowstorage qualities although they are not unbalanced. Low storagequalities for small indexes or indexes on tables with a few rows butlarge initial extent can be ignored.Actions in Detail
Show History
Use this action to get information about successful index storagequality analysis and defragmentations in the past. Only successfuloperations are tracked in the history.
The output can be restricted using the Object Restriction fields on theselection screen. If only the latest action of each type should bedisplayed for each index the output can be restricted further byflagging 'Only last Action'. This flag is evaluated after evaluating allother selection criteria.
Column description of the history view
Action
Possible values are 'Defragment' or 'Analyze'
Method
Possible values for action 'Defragment' are 'Coalesce' or 'Rebuild' andfor 'Analyze' either 'Fast' or 'Exact'
Startdate, Starttime, Enddate, Endtime, Duration
Timing information when the action on the index was started, ended andhow long it took
Storage Quality
For action 'Defragment' this column contains always '-1' becausedefragmentation does not calculate a storage quality. For action'Analyze' the storage quality in percent is shown.
KB
Size of the index when the action was started
Workingset, WS Startdate, WS Starttime
ID and start timestamp of the workingset this action belonged to. Thiscolumn can be used to group indexes processed in one run of a workingsettogether.
Start Workingset
Use this action to define and process a new set of indexes.
If Defragment and Analyze is selected, for each index the analysis ofthe storage quality is done directly after the defragmentation beforestarting the defragmentation of the next index.
Defragmentation
To defragment the indexes either coalesce or rebuild online can be used.
If option 'Check Locks' is choosen the index rebuild is not started ifthere are currently open transactions on the corresponding table. Thisreduces - not excludes - the probability of complete table locks at thebeginning of the index rebuild. The index is tried to be rebuild at alater point in time again (after at least 120 secons). If after 10trials the rebuild cannot be started this is tracked as an error.
If option 'Compute Statistics' is choosen on Oracle 9 the command 'alterindex # rebuild online compute statistics' is executed performing animplicit statistic calculation during the rebuild. On Oracle 8.1 thisoption is ignored because the command extension is not available. WithOracle 10 statistics are always calculated implicitly.
If option 'Tablespace Coalesce' is choosen after rebuilding indexes ofsummed size more than 100 MB in one tablespace the freespace in thetablespace is coalesced.
Analyze
For calculating the storage quality the number of indexblocks, th numberof rows in the table and the average length of an index entry has to bedetermined. Both information can either be gotten from a currentsnapshot leading to an index fast full scan on the index (Exact) or fromOracle statistics (Fast).
The exact methods runtime depends on the size of the index. The fastmethod has constant runtime, but may be not as exact.. Usually the fastmethod is sufficent. Because of the significant runtime difference thefast method is recommended.
In case statistics are not in place when using the fast method the exactmethod is taken as a fallback automatically. Table monitoring is takeninto account from the fast method if switched on.
Show Status of Workingsets
As long as a workingset is not processed completely successfully it canbe displayed choosing this action. By doubleclicking on the appropriatefield of the output, the indexes currently in process, to be processedand processed successfully or with error can be displayed.
If a workprocess is working on a workingset but currently notdefragmenting or analyzing an index no entry for this workprocess isdisplayed (e.g. when waiting for taking the next index).
'Show Workingset' can be used to check the success of processing aworkingset and to see which errors came up.
Column description of the workingset header view
Workingset, WS Startdate, WS Starttime
ID and start timestamp of the workingset. Every (re)start of aworkingset sets a new timestamp.
Last Update Date, Last Update Time
Timestamp when the last time an action on an index was completed inthis workingset. If the timestamp is long in the past and the status ofthe workingset is 'Active' this may be an indicator for a hardtermination of a workingset (see also 'Set Stop Status for Workingset'below).
Status
Possible values are
- 'Initializing': The workingset is created by reading data from indexesfitting the conditions of the selection screen. The initialization canlast up to several minutes if the number of indexes to be processed islarge.
- 'Active': The workingset is currently processed.
- ''Terminating': The workingset should be stopped (see also 'StopWorkingset' below). Indexes currently in process are finished normally.If all processes working on the workingset finished their current indexthe workingset status changes to 'Stopped'
- 'Stopped': No process is currently working on the workingset. Thereare indexes in the workingset that need to be (re)processed. This statuseither indicates that a workingset was manually stopped or all indexeshave been tried to be processed and at least one error occured duringprocessing.
If all indexes of a workingset are successfully processed the workingsetis automatically deleted and its name can be reused.
Active Processes
The number of workprocesses working on the workingset. The firstworkprocess is assigned to a workingset by 'Start Workingset'. Furtherworkprocesses can be assigned to the workingset by 'IncreaseWorkingsetparallelity'.
As long as the workingset is in status 'Initializing' the number ofactive processes is set to one regardless how many processes startedusing 'Increase Workingsetparallelity'.
Doubleclicking this field when the workingset is already initializedshows on which indexes the processes are working. If currently no indexis processed by some of the processes, less lines than indicated by'Active Processes' are returned.
Indexes Total
Number of Indexes assigned to the workingset when it was (re)started.
Indexes to do
Number of indexes for which processing was not started or which arecurrently processed. Doubleclicking this field returns detailinformation about the indexes.
Indexes ok
Number of indexes that have been successfully processed. Doubleclickingthis field returns the history rows for those indexes.
Indexes error
Number of indexes that have been processed with errors. Doubleclickingthis field shows the Oracle error code.
KB Total, KB To Do, KB OK, KB Error
Shows the volume based progress of processing the workingset. This is analternative to the number-of-indexes based progress.
Action
Posible values are: Defragment(Coalesce) or Defragment(Rebuild) and/orAnalyze(Fast) or Analyze(Exact)
Options
Further selection screen options like 'Lock Check', 'TablespaceCoalesce', 'Rebuild Options'
Increase Workingsetparallelity
With this action another instance of the report can be started to workon a workingset currently in process. In other words the parallel degreeof processes working on one workingset can be increased by one.
In general there are three levels of parallelity available:
Parallelity between workingsets with different IDs
Several workingsets can be processed in parallel. It is recommended toput each index only to one workingset to avoid the possibility ofparallel processing of the same index.
This kind of parallelism can be used if a set of indexes should beprocessed daily and another set weekly. During the weekly execution alsothe daily set of indexes can be processed.
Parallelity within a workingset (this can be increased by 'IncreaseWorkingsetparallelity')
Several workprocesses can work on the same workingset. Therefore theprocessing of a workingset is started in the normal way and afterwardsthe workingset parallelity is increased by planning further jobs withthe action 'Increase Workingsetparallelity'.
Stopping processing of a workingset stops all processes working on thisworkingset.
This kind of parallelism can be used if the parallel degree should beincreased dynamically e.g. if it turns out that after starting aworkingset workprocess/CPU/memory/IO resources can still cope with theload of additional processes.
Parallelity within a command on an index of a workingset
For rebuilding or exactly analyzing an index a parallel degree can bespecified. The parallel degree is handed over to Oracle for the rebuildcommand (rebuild)/index fast full scan (exact analysis).
This kind of parallelity can be used if just one workprocess should beallocated. For doing a fast storage analysis in parallel this optioncannot be used.
Stop Workingset
Active workingsets can be stopped with this option. The remainingindexes can be processed later using 'Restart Workingset' or cleaned upusing 'Cleanup Workingset' .
Stopping a workingset just sets a flag signalling the processes workingon the workingset to terminate after processing of their current indexis done. If the current index is large it can last a while until thestatus of the workingset changes from 'Terminating' to 'Stopped''.
Restart Workingset
Stopped workingsets can be restarted with this option. A restart may beneccessary after stopping a workingset or to reprocess indexes for whicherrors in processing occured.
Cleanup Workingset
If the rest of indexes in a workingset should not be processed anymorethe workingset can be deleted with this option. Afterwards the ID of theWorkingset can be reused.
Set Stop Status for Workingset
This functionality is needed after a hard termination during processing.
A hard termination of a workingset is a termination that cannot behandled by the report itself. Usually hard terminations result in ashortdump. Hard terminations can be caused by a stop of the databasewhich is currently accessed, killing the workprocess working on aworkingset etc.
The problem with a hard terminations is - compared with a controlledstop of a workingset - that it cannot adjust the status of theworkingset anymore to 'Stopped'. Using the option 'Stop Workingset'after a hard termination would change the status to 'Terminating'. butthis status would never be left. As a result the workingset can neitherbe restarted nor cleaned up nor the ID of the workingset can be reused.
To come out of this situation the administrator needs to check if thereare still processes working on this workingset. If so they need to beterminated. If it is ensured that no workprocesses are working on theworkingset anymore the option 'Set stop status for workingset' can beused to enforce the 'Stopped' status. Afterwards the workingset can berestarted or cleaned up.
To check which workprocesses are working on a workingset choose 'ShowStatus of Workingsets'->doubleclick the 'Active Workprocesses' field ofthe workingset->Get the number of the Workprocesses in the column'Status Description'.
Cleanup History,,
With this action all the history information about successfuldefragmentations and analysis older than the specified number of days isdeleted. '0' means cleanup the complete history.
Example Usage
Cleanup Job
Before you start using this report define a cleanup batchjob ISQ_CLEANUPwith the following variant that deletes the history of future actions.
1. Variant name: CLEANUP :
Keep the last 100 days.
Run the job once a week.
Complete DB check scenario
To avoid unnecessary IO and buffer cache usage by fragmented indexesdefine a batch job ISQ_ with the following two variants.
1. Variant: ALL__STEP1:
Name the workingset id as the variant. Analyze (fast) all objects largerthan 10000 KB.
2. Variant: ALL__STEP2
Name the workingset id as the variant. Rebuild (online) and analyze(fast) all objects larger than 10000 KB and with a storage quality lessthan 60%. Use options Lockcheck, Tablespace Coalesce and ComputeStatistics.
Plan the batchjob on demand or periodically in times of low load andideally after a statistic calculation.
Check the Joblog afterwards. If errors occured handle them and restartthe workingset or cleanup the workingset to enable reuseage of theworkingset id.
To force a reusage of a workingset automatic cleanup steps can be addedto the batchjob BEFORE the above steps are executed.