AUTOTRACE Problem

I’ve commented before about how AUTOTRACE is a favoured tool for a quick performance check of a query. So, whenever a developer pays an interest in it I’m glad to help where I can.

Recently a developer told me that AUTOTRACE wasn’t working for them. They sent across their script, which enabled AUTOTRACE with:

SET AUTOTRACE TRACEONLY

and the output they were getting. The output was very strange (this is from an 11.2.0.3 database):

Statistics
----------------------------------------------------------
          0  user rollbacks
          0  global enqueue releases
          0  physical read requests optimized
          0  physical write total multi block requests
          0  hot buffers moved to head of LRU
          0  commit wait performed
          0  global undo segment hints helped
          0  global undo segment hints were stale
          0  IMU commits
          0  IMU Flushes
        244  rows processed

Running their script in my own account revealed nothing wrong; the AUTOTRACE output was present and correct. Looking into the roles and privileges on the developer’s account showed nothing amiss either… but their account did show as having an expired password. A change of their password and the problem was resolved.

Why did resetting a password resolve an AUTOTRACE problem? What might not be understood about AUTOTRACE is that it creates a new session in order to do its profiling. With the password expired I guess that AUTOTRACE couldn’t do its usual work and generated the strange output above. It’s not good that it didn’t report some sort of login problem but it didn’t take long to resolve. I’ve simply made a mental note (and a blog post to remind me) to check for expired passwords next time someone tells me that AUTOTRACE isn’t working for them.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s