The session profiling or session-level response time analysis7 trap is insidious. It works likes this. First we profile a session and a report is produced. The report classifies time wonderfully by providing a breakdown of response time and various levels of classification. We may also see the SQL executed during the profile. Then we make the fatal assumption that the response time and its components are based entirely on the SQL executed during the profile. Doesn't that sound correct? But it is not correct.

The trap begins when we hold the report in our hands and we think we hold both the cause and effect before us. We start believing that the SQL was run in isolation and is unaffected by anything and everything else occurring on the system. For this to be true, the database server would need to be completely dedicated to that specific SQL. All other database server activity and operating system activity would need to halt while your SQL ran.

Here's a very simple yet crystal-clear example. When a SQL statement reads 1,000 blocks from disk, is it because the SQL statement is not tuned, the buffer cache is too small for the table, or other SQL statements replaced cached blocks with other blocks our SQL is not interested in? The answer is possibly, possibly, and possibly.