This section focuses on collecting performance statistics (not optimizer statistics) in modern Oracle architectures. The difficulty associated with this type of data collection is the result of the end users being increasingly separated technically and possibly physically from where their requests are processed. This separation causes significant technical challenges when profiling a single end user's activity. To understand the challenges and truly appreciate the solution, we need to first look at the dramatic changes in Oracle system architectures.

Oracle architectures have changed greatly over the years, making session profiling (Oracle session-level response-time analysis) nearly impossible without purchasing a product. Keep in mind that there are always architectural variations, but the core dynamic remains true.

As shown in Figure 5-14, in the 1980s, both Oracle's SGA and the Oracle server and client processes resided on the database server. It's important to understand that because the Oracle server process works directly on Oracle's cache (which resides on the database server in shared memory segments), the server process must also reside on the same machine as the SGA. So regardless of all the creative and fascinating Oracle architectures we can think of, each requires the server process and the SGA to be on the same physical machine.8 Profiling a session based on a Figure 5-14 architecture was very simple, as there was a single server process for every client process and the client process could be easily associated with the end user.