Categories
Peoplesoft PeopleTools Performance SQL Server Tools Tuning Utilities

_WA_Sys Stats or Hand Crafted?

SQL Server generates histogram statistics automatically whenever a column is referenced in a predicate and it is not already the leading column in an existing histogram statistic. By design, indexes create histogram statistics automatically so any column you have that is the leading column in an index will already have histogram statistics.

The autogenerated statistics are prefixed _WA_Sys_ followed by:

  • the column name (in older SQL Server versions) or the column number in hex (for SQL Server 2008 onwards) e.g. EMPLID or 00000001
  • the object_id of the table in hex e.g. 57490C28

separated by an underscore. So auto-generated statistics look like this:

  • _WA_Sys_DESCR_57490C28 or _WA_Sys_00000013_57490C28

The issue for me as a PeopleSoft Administrator/DBA is whether I should keep these statistics or recreate them as “user” statistics with more sensible names. Oddly enough the “old style” naming convention which included the column name was actually better than the “hex” version we have now. After all what is column 00000014 (decimal 20) in table X?

My personal preference is to process the system generated statistics and recreate them as statistic “column name” on table X. I have a perl script that generates the relevant DROP/CREATE STATISTICS commands for an entire PeopleSoft database. The output looks like this:

--- Remove statistic _WA_Sys_AUDITRECNAME_5921A398 for column AUDITRECNAME on table PSRECDEFN
 
DROP STATISTICS [dbo].[PSRECDEFN].[_WA_Sys_AUDITRECNAME_5921A398];
 
--- Create User Statistic for column AUDITRECNAME (+ 1 keys) on table PSRECDEFN
 
CREATE STATISTICS [AUDITRECNAME] ON [dbo].[PSRECDEFN] ([AUDITRECNAME],[RECNAME]) WITH FULLSCAN;

Why do I like to do this? Well there are a number of reasons:

  1. The naming convention is clearer to me – open the statistics tree within a table in object explorer and you get a list of column names plus the statistics created for any indexes.
  2. Once I have changed to “user” statistics I can query for any new _WA_Sys_ statistics (yes I leave auto-generate switched on). This shows me any “new” query activity that accesses via previously unused predicate columns. This can help in deciding if perhaps new indexes are required.
  3. I can ensure the existing statistic are recreated using a “WITH FULLSCAN” not just sampled data. For many PeopleSoft systems this is essential – data can often be very skewed and sampling can miss this on occasion.
  4. I like to add in the “key” columns of the table to the histogram to provide better density information for the optimizer.  This can be essential information when intermediate key fields have a single value e.g. EMPL_RCD in HR is typically 0 in most cases.

If you would like to discuss this in more detail, leave a comment.

Categories
Hardware Linux Utilities

Hardware Monitoring on Linux

There are a few graphical applets that show hardware status on Linux, but I really like the Python based curses interface of glances. Check it out – a really clean interface, remote monitoring via the web. Obligatory screenshot:

glances_screenshot_phenom

 

Categories
Peoplesoft Tools Utilities

Downloading from Oracle with wget

Downloads from My Oracle Support are facilitated through a wget shell (wget.sh) script that can be used as an alternative to interactively downloading the individual files via a web browser. This feature is particularly useful for headless *nix servers or where X is not installed at all, but also for those large image downloads you would like to download outside core office hours.

However, more recent versions of wget can have issues with the generated script provided by Oracle. This post offers some .wgetrc settings to make the supplied scripts work correctly without modification (apart from adding your password).

Here is a .wgetrc that works on the Linux systems I have tried i.e. Debian/Ubuntu based ones like LinuxMint:

#
# Suitable wget settings for downloads from https://updates.oracle.com 
# 
max-redirect=5 
continue=on 
secure-protocol=TLSv1 
progress=dot:giga

Note: The entries continue=on and progress=dot:giga are more for useablity. I like to use resumed downloads where possible in case a script fails and I need to restart it. Oracle’s web server supports ranged downloads so this does work. The progress report changes just reduce the size of the log files by making each line represent 32M of downloaded data.

Categories
Development Tools Utilities

Beyond Compare 4

I just invested in the latest version of Scooter Software’s incredible file/folder/data comparison tool Beyond Compare. Lots of new features but most significantly for me is that there is now a native Mac OSX version. Full list of changes here

The jury is out on the UI changes but it is still as slick as ever at what it does. Buy it!

http://www.scootersoftware.com/