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.
Mark Hoxey 