IBAnalyst 2.6

What's new

03/23/2007 IBAnalyst 2.01

• New
• InterBase 2007 Write and Dump timestamps support
• Forced Writes = OFF warning is changed for ODS 12 (InterBase 2007) databases. When journaling is enabled for this database, Forced Writes = OFF is OK.
• Fixes
• when header page not contained sweep information, 200000 was shown instead of default 20000.
• "division by zero" error during some .iba statistics files load (it happened when there were very low transaction activity in database)
• Sometimes database info panel was not shown automatically after statistics loading (via API or from file)
• Doubleclick on broken index now moves to index view
• Extended report on broken or incomplete indices
• False "long running snapshot transaction" when Oldest Snapshot Transaction was less than Oldest Transaction. Instead of this another message and hint displayed for Sweep gap (yellow color)

09/23/2006 IBAnalyst 2.00

• New
• Improved algorithms of statistics analysis
• Full support for Firebird 2
• InterBase 2007 compatible (additional IB 2007 specific features will be supported in next sub-release)
• Both InterBase and Firebird client libraries (gds32.dll and fbclient.dll)
• Transaction Calculator option added, enabling to check average transaction count in common or per application.
• "Database size" row shows database size in megabytes, and % of data pages and indices (only via Services API).
• "TIP size" column, show Transaction Inventory Pages count and size in kilobytes.
• Broken or incomplete indices detection
• Interface
• Toolbar menu
• Server and database list. Old IBAnalyst registry records is available.
• Right mouse click in server/database list allow delete unneeded items (server or database) from list or take statistics from current database, last open database, or new database.
• "Reload" button. Statistics now can be reloaded, header page only (default) or full, after it was taken from server. Note that if you reload only header page information, statistics for tables and indices is out of sync with this info.
• Saving statistics automatically name file as server_databasename_yyyymmdd_hh-mm.iba
• Warnings
• Check for broken or inconsistent indices (key count less than record count). Reported at Summary page and at Index page. You can also look at hint for Key column - it will show difference between key count and record+version count. This information can be used to understand what indexed columns are changed during update (less keys means no update of indexed columns).
• "Problematic items filter" - shows only warning or suspicious items. Tables that don't have warnings are shown if their indices have problems or warnings. Filter is being reset when new statistics is open or current is reloaded.
• Transaction Inventory Pages size warning and report, when it is close to it's limit in InterBase 5.x/6.0 databases (ODS 9.x and ODS 10.0). If you use Firebird 1.0, you can ignore this warning. Warning is not shown for newer InterBase or Firebird versions.
• Changes
• Statistics taken from Services API is being saved in "object format" (*.iba). This format stores all current statistics info, including metadata, and enable to view complete statistics later.
  Saving statistics in old format (gstat compatible, incomplete) is not more supported.
• Fixes
• Statistics date for services api was shown without time
• "Reset changes" button now work in transaction calculator
• Inactive indises was not shown in outdated selectivity indices report.
• Wrong error message on database file open error
• Special message is shown when database corruption is detected

05/20/2006 IBAnalyst 1.98

• Changes
• Warning about table is being close to maximum limit of 36.7 gigabytes is shown when table size is about 30 gigabytes (80% below limit). This warning is shown for all InterBase and Firebird versions, including Firebird 2.0, which does not have this limit in it's native ODS 11 database format. (this format does not compatible with InterBase 7.0 ODS 11.0).
• Fixes
• If header page does not have Sweep Interval info (databases, created with Firebird 1.x), IBAnalyst shows default value (20000). This is correct, because Firebird use default Sweep Interval 20000, even if it is not stored in database header page.
• "Index is deactivated (inactive)" hint is shown for inactive indices with selectivity <> 0
• Fixed difference computation for computed and rdb$statistics selectivity
• Warning for empty indices with wrong selectivity was shown with wrong color
• Right mouse button menu fixed for statistics being loaded from file
• If gds32.dll does not exists on computer, IBAnalyst will still run and allow to analyze statistics from text files (from gstat output)
• Minor fixes and additions in Help.

10/28/2005 IBAnalyst 1.92

• Changes
• Empty indices (with keys = 0) with outdated statistics are marked by blue color (Selectivity column). Please read Q&A (help file)
  "10. How to improve optimizer performance in case of frequent updates"
• Added note about gds32.dll supported version in Known Problems.
• Fixes
• Fixed bug computing statistics on empty indices
• Minor help fixes.

10/05/2005 IBAnalyst 1.91

• New
• metadata - Added boolean type to the table structure hint
• metadata - Added hint for index name with indexed column names
• metadata - Inactive indices are shown, their name marked with italic font, hint with column names also have word "Inactive"
• metadata - Hint RecLength shows maximal record size
• Added column Statistics (Index view). Information is taken from Services API (if Load Metadata is On). Column shows rdb$indices.rdb$statistics data. If this information is outdated, it will be marked yellow for less than 20% difference with calculated index selectivity, or red if difference greater than 20%. For that indices right mouse click on Statistics column enables to recompute selectivity.
• Added recommendation about outdated index statistics.
• Added column "Size, mb" to Index view.
• Added total index size into row "Data version percent" at Summary view.
• Changes
• Column Leafs is shown together with Fill distributuion columns
• Blob table fragmentation is checked for tables with more than 50 data pages
• Transaction warnings not shown for low activity systems
• Fixes
• ChessFill color bug fixed
• Removed RealFill column when statistics does not have record version info
• ODS 9.x information about numeric fields is now taken correctly
• Wrong ODS error now processed and reported correctly
• Fixed problem with first F1 keypress in modal dialogs
• TIP size report in recommendations fixed from bytes to kilobytes
• Fixed report in recommendations about gap between Oldest Snapshot and Oldest Active
• Fixed error viewing inactive indices

Note that help file contain 2 articles about statistics and transaction information, and Q&A topic.

06/16/2005 IBAnalyst 1.82

1. Fixed save statistics question when statistics was loaded in turn from file or Services API.
2. Fixed main window and dialog positions for dual monitor systems
3. Fixed some memory leaks
4. Added main windows size and position store/load.
5. Option "take only header page" in Services API dialog is saved/restored
6. New "Load metadata" option in Services API dialog. Enabling this option will load metadata (table structure) for table name hint.

05/22/2005 IBAnalyst 1.80

1. Added Firebird 2.0 alpha/beta/release ODS support
2. Added Summary ODS row Hint (all ODS numbers for all InterBase/Firebird versions)
3. Used new (upgrade) VirtualTreeView
4. Each table and index at Summary, Table and Index views shown with it's icon (fragmented, ok, versioned...). Icons background also corrected.
5. Fixed error parsing empty indices
6. Fixed storing server name in registry
7. Added column Size for Table view - table pages size, in megabytes
8. Added colum Table Real Fill - same as Average Fill, but computed by IBAnalyst.
9. Summary page in "Data version percent" row shows also total size of records and versions in pages (mb)
10. Hint and recommendations fixed - links to Help. Help is also corrected.
11. Corrected data versions percent calculation for Services API
12. Services API dialog changed, default Password left as "masterkey".
13. Fixed getting statistics via Services API from Classic servers.
14. Changed method of Services API statistics save. It can be saved:
1. in Statistics menu
2. when you'll exit IBAnalyst with unsaved statistics
3. when new statistics is being loaded
   Yes - save to file, No - don't save, Cancel - cancel operation. When statistics is not saved it is being cleared only when new statistics is loaded successfuly (from file or services api). You can't save statistics, loaded from text file.

01/26/2005 IBAnalyst 1.76

1. Fixed problem with Tables and Indices label (right upper main window corner)
2. Fixed some Services API statistics retrieval functions
3. Fixed problem with statistics taken from IBConsole.
4. Services API dialog now have empty password input. Password is remembered during session.
5. Services API dialog can be resized. Width change remembered and stored in ibanalyst.ini
6. New row at Summary page - Data version percent. Shows versions percent of records, for the whole database (all tables). Fragmentation is ignored.
7. Documents
        How to get statistics...
        What you see at Summary page...
        Additional Q&A
        Version History
        Known problems
     are moved to Help (corrected and extended).

01/13/2005 IBAnalyst 1.75

1. Verlen/Reclen ratio calculation corrected - now Record/Version header is counted. Table view does not count record/version header, so reclength and verlen colums can still show 0 bytes.

2. added verlen % for Versioned records, to allow visually understand percent of updates/deletes of records. If verlen % > 70-80, this versions mostly generated by deletes. If less, versions are mostly generated by updates.

3. Corrected average transactions per day calculations and warnings when working days count much larger than transaction count.

4. new "Take only header page" option in Services API dialog. Allows to retrieve only transaction information (header page) from database.

01/11/2005 IBAnalyst 1.71-1.74

1. Hint and Help for VerLen column corrected at Table view. See p. 6 in howto.txt

2. Parser error handling corrected
a) any empty lines are ignored
b) when error, it is possible to ignore it or to abort parsing at all. If you got any parsing error, try "Ignore" button 2-3 times, if there are still errors, press "Abort". You will see all correct info that parser could understand.

12/22/2004 IBAnalyst 1.70

1. added KB3, KB4, KB5 and KB6 to howto.txt

2. if table is "versioned" and have less than 1000 records, version count marked as yellow, not red.

3. Added "Snapshot Gap" to Summary page.

4. Added "Creation date", "Statistics date" and "Transactions per day" to Summary page. Creation date is taken from database statistics info. Statistics date is taken from file (if stats loaded from file) or as current date (if stats loaded via Services API).

"Transactions per day" is being calculated as Next_Transaction div DaysBetween(Statistics_date, Creation_date) and useless if it is not a production database statistics.

5. Some formatting and output corrections for Recommendations.

6. Table/Index fragmentation columns (20, 40, 60, 80 and 100%) can be turned on/off in Options dialog. By default is OFF.

7. Added Record/Version Size option to Options. By default 120% (min 1%, max 300%). Column VerLen is marked Yellow if average version length greater than specified % of average record size. Indicates updates that update lot of fields in record.

8. Added Uniques column for indices (right after MaxDup and TotalDup). It is unique (different) key values in index (Keys-TotalDup). Useful for non-unique indices to view how many different keys are stored (for FK, for example) in index.

If index have more than 1000 keys and only 1 value for all keys (Uniques = 1) than it is marked yellow in Uniques column. At Summary page you can see all these indices, marked as "Useless indices list".

Hint on Uniques column (and useless indices list) shows % of unique values for each index. If Uniques = 1 hint is changed to "Index is useless...".

9. Leafs column in index view moved to the end, after AvgFill column.

10. Help contents update.

11. MaxDup now marked red only if TotalDup check (Options -Keys/TotalDup) is True. Earlier it was checked independently of TotalDup, wich was incorrect.

12. TotalDup warning calculation fix. Also it was ignored in Options.

13. New List of "Tables fragmented with blobs" is shown at summary page. These tables marked as Pale at Tables view, Records column), hint shows estimated records for existing data pages count, and real average fill. See recommendations comments on these tables (or KB5 in howto.txt).

14. Primitive "Copy to clipboard" function added. First select desired columns (all row data will be copied, but only first column will be viewed as selected). then press right mouse button, and click Copy. Data is copied as plain text. You can easily select many rows by pressing Shift and Up/Down arrows.

15. "Bad indices" report reduced by including only indices that doesn't pass Options Key/TotalDup > x%.

16. Tables suspected for massive deletes/updates marked as blue in MaxVers column. Also added 2 additional parameters to Options: min version count to check this condition, and version % of records to show warning when MaxVers = 1, i.e. massive deletes/updates suspected.

You can distinguish deletes from updates by looking at RecLen and VerLen columns. If VerLen is ~9 (average delete stub size), than Versions are 100% deleted records (see KB4 in howto.txt). If VerLen is greater than 9, it maybe partly updates and deletes. Anyway, these versions are not yet processed by garbage collection (or not being read), so table size is not reduced yet.

Also "Massive updates/deletes" table list added to Summary page.

07/28/2004 IBAnalyst 1.60

1. Added versioned tables list to Recommendations.

2. Slightly changed hints for index Total Dup and Max Dup.

3. Total Dup is marked yellow if Max Dup is less than optimizer's estimated selectivity (1/(Keys-TotalDup) > 0.01), and red if in addition Keys/MaxDup ratio greater than 30%.

4. Help cleanup

07/01/2004 IBAnalyst 1.55

1. Services API connection protocol changed from Local to TCP

2. Fixed lost % color fill for Tables.AverageFill

3. Added Index Average Fill calculation, column, Option and Hint.

4. Doubleclick on Table name in Incices view will switch to Table->Indices master-detail view.

5. Fixed AV when doubleclick is made on empty Summary view.

6. ServicesAPI Server names and Database names are stored in registry.

7. Fixed deep index reporting in recommendations output

06/29/2004 IBAnalyst 1.52

public v 1.5.2 release.

02/01/2004 IBAnalyst 1.0

internal v 1.0 release (IBStat)