This is just a small blog post to take part to Tim Hall's OTN Appreciation Day.

My favourite Oracle Database feature is SQL trace: in my opinion it is still the most powerful tool to analyse Oracle database session activity because it is the most accurate way to get all details:

  • which SQL statements have been executed
  • what is the elapsed time of each SQL statement
  • what is the SQL statement execution plan and the details of each execution plan steps (CPU time, logical reads, physical reads, physical writes, etc.).

    • It simply allows to know what is really going on instead of guessing: this should so obvious but according to my experience it is still a problem in many IT shops
    (partly because not all software stack are so well instrumented like Oracle kernel
    and wait interface concepts seems to me not widely used).

    Not convinced ? Please read Optimizing Oracle Performance by Jeffrey Holt and Cary Milsap: Yes, this is 15 years old book ... Yes, it mainly deals with Oracle 8 ... BUT the basics have not fundamentally changed. Just read it: you won't waste your time and you will start to use SQL trace.