Friday, December 30, 2005

More on IT Trainers

A few days ago I brought up some problems I've found with IT application training and trainers in my entry "(Almost) All Users Are Idiots". Following a comment from Doug Burns I read an entry in his blog entitled "In Praise of Good Trainers". Doug (an excellent trainer himself, in my opinion based on having attended two courses taught by him) has layed out a pretty much definitive description of what makes a good Oracle DBA trainer, although I'm sure the same principles could be applied to any subject area.

A section of the entry that particularly resonates is near the end where Doug describes three particular aspects of trainers who aren't so good, and who's work he does not respect: Won't divert from the course materials; Tends not to know the answers to questions and may make up answers to hide this; Tends to demean or belittle students.

I've had a few trainers that hit one, two or all three of those. I'd perhaps edit the first one slightly, based on my observations, to add "(or contradicts the course materials without giving any explanation why".

Project Raptor

Project Raptor has escaped (software isn't released, it just sometimes escapes).

I downloaded it at work and had a quick play. Overall I liked it a lot. People used to products like TOAD won't see much new. The biggest advantage I've seen is that you don't seem to need any extra Oracle software installed. Just copy the software on to the machine, enter the IP address, the port and the SID/Service Name and you've got a connection.

Of course, the other big advantage is that it's free.

There's been some interesting discussion of Raptor over on Tom Kyte's blog.

I agree that it's probably not for the hardcore DBA, not as a primary tool anyway.

Where I can see me probably using it is as something I can give to the non-DBAs who happen to do some DB management (we have a lot of them where I work). I'll have to investigate it further to make sure that it's reasonably safe to let them use it though. I haven't had a chance to read much of the docs yet and haven't been able to hit the forums.

For my own use I can see me possibly putting it on a USB thumb drive so if I'm away from my desk and have to use a PC which doesn't have the Oracle client installed I can just plug it in and connect to a database if needed.

Actually, I'm going to be launching an internal Oracle Users Group in the organisation I work for in the next month or so, a demonstration of Raptor would probably be a good opening gambit to get people interested.

Networked Database Naming

The organisation I work for has a large number of sites (literaly hundreds) all connected via a WAN. Whilst for some applications everyone who uses that app are in the same office (or two or three offices) for others the users are widely disperesed, so making maintenence when a database changes name or moves to a different machine very difficult and time consuming. In one case we have a thick client application (i.e. there's a connection directly from the desktop to the database, not going through an application server) where the database is due to move in the near future, this application is used by hundreds of users accross almost as many sites. I was asked to come up with a list of options for providing a centrally administered naming service. One key piece of information is that in this organisation most applications (and their databases) are managed by a departmental support team, hence there is currently no central repository of database names.

Oracle Names Server
  • Oracle supported solution
  • Will not impact on existing directory servers
  • Graphical and command line management tools
  • Well documented

  • Have to set up at least one Oracle Names Server
  • Have to visit every desktop to configure them to use the Oracle Names Server
  • Oracel Names is supported in Oracle 8/8i, deprecated (i.e. still there but Oracle advise not to use it) in Oracle 9i and Obsolete (i.e. not there anymore, the code has been removed) in Oracle 10g and later

Oracle Internet Directory
  • Oracle supported solution
  • Will not impact on existing directory servers
  • LDAP version 3 compliant
  • Support for Enterprise Roles and Single Sign On (could be useful where one user access a number of databases with the same username.
  • Graphical and command line management tools
  • Well documented

  • Have to set up Oracle Internet Directory Server
  • Have to visit every desktop to configure them to use Oracle Internet Directory server
  • Not fully supported on pre-9i databases

Microsoft Active Directory (the Meta Directory)
  • Oracle supported solution
  • Already have the servers set up
  • Can be configured to be LDAP version 3 compliant
  • Graphical management tools

  • Have to visit each desktop to configure them to use the Microsoft Active Directory server
  • Would add objects to the meta directory
  • Not guaranteed to be supported in future releases (Novell NDS/eDirectory has already been deprecated and obsoleted as a compatible directory)

Other LDAP version 3 compliant directory
  • Will not impact on existing directory servers
  • LDAP version 3 compliant
  • Depending on directory selected could use open source
  • Not tied into to one vendor's product, can just export settings and import them into another compliant product.

  • Not Oracle supported solution
  • Only works with Oracle 9i and 10g, may work with later versions but isn't guaranteed
  • Would have to set up a server with the appropriate directory software on
  • Depending on OS and directory software used might not have much by way of management tools
  • Have to visit every desktop to configure them to use an LDAP directory

Use environment variable "TNS_ADMIN" to point to location of TNSNAMES.ORA file on a network drive
  • In use in a lot of Oracle shops worldwide and has been working for some time.
  • Pretty much version independant (TNS_ADMIN was introduced in Oracle 7 and is still used in Oracle 10g)
  • May be able to set in profile or login script so removing need to visit every PC
  • Will not impact existing directory servers
  • Oracle supported solution
  • Simplifies trouble shooting for connectivity problems, If the file works fine for other people then we know that it's not the file causing the problem. Similarly for all Oracle Net8 configuration files that live in that location.
  • Easier to stop users meddling, can make the file read only for non admin users

  • Will need co-ordination between sections as all databases that every user needs to access via a thick client that connects to the database will need to be in one file at one central location
  • May run into issues where PC has multiple different versions of Oracle client installed
  • File would have to be read only for non-admin users to prevent a user who thinks they know what they're doing going in and stripping out the entries they don't need (but other people who use that file do)

Use login script to copy TNSNAMES.ORA in from a network drive to %ORACLE_HOME%\network\admin
  • In use in a lot of Oracle shops worldwide
  • Pretty much version independant
  • Will not impact existing directory servers

  • Will need co-ordination between sections as all databases that every user needs to access via a thick client that connects to the database will need to be in one file atr one central location
  • May run into issues where PC has multiple different versions of the Oracle client installed
  • Not an Oracle supported solution
    Updates only propergate when users logs on
  • Users can update their local file but the updates are wiped out when they login.

Wednesday, December 28, 2005

More Oracle Blogs

I just thought I'd mention that I've added a bunch more Oracle related blogs to the list down the right hand side of the page. I recommend that you give them a read. If you know of any others I should be reading (if only I had the time to read more) then feel free to comment here.

Monday, December 26, 2005

(Almost) All users are idiots

No, I haven't turned into BOFH.

A common comment I've heard in every IT department I've worked in has been about the idiocy of users, or how one particular user has done something really stupid, because they don't understand IT. But why should they? They're users, they do a job which isn't IT based but just happens to use IT. They're finance people, or HR people or social services people, or planning people, or something else people. They have knowledge and skills (we hope) in other areas, areas that we as IT people almost certainly don't have. Why should they know how the network works? Why should they know how the application they're using works under the hood? Why should they know how the database, the application they use connects to, works?

There are a couple of groups I do have a problem with the idiocy (although idiocy might be too strong a word) of, IT (especially application) trainers and IT administrators.

An example of why can be seen in a problem that was recently (Friday afternoon, i.e. the day before the long break for Christmas) brought to my attention. We have a document management system that uses an Oracle database to hold the metadata. The data part of the data base is only supposed to be a few gig, it's up to about 4Gb so far, so that's very small by our standards. I don't manage it directly myself, like most of our apps it's managed by the departmental IT team with input from the project team that put it in (it's not fully live yet). For a few months the person responsible for making sure it's backed up has been worried about the amount of time it's taking to complete the backup (it's currently backed up cold each night, at some point we'll be moving to hot backups). On Friday afternoon this was brought to my attention.

I first went into the database and queried dba_data_files and v$datafile to see if any tablespaces had grown. They hadn't, although I did note that a number of datafiles that had been non-autoextend were now autoextend. I then went to the command prompt and did an ls -l to check that the files on disk were different to what I was expecting. I found that the file for the temp tablespace was 32Gb. A useful demonstration that since the introduction of tempfiles you can't just rely on dba_data_files or even v$datafile for your tablespace size information (which I admit I'd forgotten, fortunately my natural inclination to "Measure twice, cut once" meant I caught it before it caught me out), from the documentation:
Tempfile information is shown in the dictionary view DBA_TEMP_FILES and the dynamic performance view V$TEMPFILE, but not in DBA_DATA_FILES or the V$DATAFILE view.
A quick check back in the database showed that this file had also been changed to autoextend.

Another important piece of information about this system was that earlier this year I had noticed a lot of queries were doing a large amount of sorting and had where clauses where a lot of the conditions were something like "table4.field7 like '%'", which seemed to imply that the form that had generated the query had a space for the contents of that field but the user hadn't entered anything so it had been wild carded.

From this I surmised that what has probably been happening is that a lot of users have been running very unselective searches which have returned a large number of rows which have been sorted (as part of the joins and to put the results in the order the user wanted) so blowing the PGA sort area and going to disk-based sorts. When the tempfile wasn't autoextend they were probably getting a lot of searches failing due to running out of space in the temp tablespace ("Cannot extend temporary segment ...") so the administrator had, without thinking of the consequences, changed the tempfile to autoextend. At some point the number of users sorting large datasets reached the point where a 32Gb tempfile was required to hold all the temp segments.

And so we get onto the trainers and the administrators.

IT application trainers (and those who write the courses they deliver) are supposed to train users on how to use the application. I'm sure they do that, but do they train the users on the best ways of using the app? In the example above it probably didn't come up in the training environment (with the small number of users and small data volume) that unselective searches might result in huge data sets. Unfortunately in the live environment I'm sure that the users not only ran into failed searches (due to not being able to extend the temporary segments) but also found that the searches that did complete were slow and produced large outputs which they then had to read through to find the results they wanted. Perhaps if they had been trained to make their searches as selective as possible from the start then the problem would not have arisen?

I know that trainers are often beholden to budgets and other constraints (and may even be asked to deliver a course in a far shorter period than they would like)'s still frustrating to be the one who has to pick up the pieces. Users shouldn't have to understand the underlying systems but I don't think it's unreasonable that the trainers should have a reasonable grasp of the implications of how the application works.

And then there's the administrators. I've been working with Oracle for about nine and a third years now (it'll be 10 next August). I can probably reasonably claim to have been a DBA for about 7 to 8 of those years, my first formal training in Oracle being a course in 1999 taught by Doug Burns (one of the best Oracle trainers I've had the pleasure of being trained by), I'm also the only Oracle DBA in the entire organisation (about 50,000 employees with 480 IT staff). Most of the other people, in the organisation, who manage Oracle databases don't come close to that length (or depth) of experience. Additionally a number of them have a tendency to just slap on whatever patch, change or whatever comes through the door or from a Google search with no real understanding of or thought about the implications, possibly due to years of working with Microsoft systems.

Even with those years of experience I still, as evidenced by the datafiles/tempfiles above, find details I didn't know or have forgotten. Still, one of the things that my experience has taught me is that no matter how loud management are screaming, no matter how simple a change seems, no matter how right a change seems when you first think of it, always think of the implications.

Now I just have to communicate that to the other 'administrators'.