OTN Appreciation Day: SQL Trace

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.

    Post a comment or leave a trackback: Trackback URL.

    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 )

    Google photo

    You are commenting using your Google 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 )

    Connecting to %s

    %d bloggers like this: