Introducing Total Session History

Attribution of time in database application determines potential benefits of Exadata

Exadata is a technology that delivers superior database performance, so it attacks the portion of time an application spends INSIDE the database.  Exadata will not make the network faster, and will not make application tiers faster.  Oracle database applications will have some transactions and/or batch jobs that show small increases in performance and transactions or jobs that show large performance increases.  It’s all a matter of where  the time is being spent.

Every Oracle database application includes portions that will show a LARGE benefit from Exadata, while other portions of that same application will show SMALL performance benefits.  To determine which portions of an application will show more or less performance benefit from a faster database, there are built-in features of the Oracle database that enable such analysis.

The Oracle Automated Workload Repository (AWR)

Oracle’s Automated Workload Repository (AWR) feature enables performance analysis of Oracle databases at the system level.  AWR reports show performance statistics for the system as a whole, including the SQL statements that consume the most resources on the system.  AWR is a great tool for doing analysis of system-wide performance issues.

The Oracle Active Session History (ASH)

Active Session History (ASH) facilitates performance tuning of SQL statements that consume the most resources in the system.  ASH provides a historical repository, making it possible to go back in time and find high resource consuming SQL statements.  This is a truly revolutionary capability for performance tuning because it allows the DBA to go back in time to see what performance problems existed when users were having troubles.

Total Session History (TSH) Utility

While AWR and ASH are great tools, they don’t facilitate analysis of the complete application from a session perspective.  The Total Session History (TSH) utility allows database administrators to analyze what factors account for time consumed by sessions in the database.  TSH gives a better picture of where time is being spent and how Exadata might help performance as viewed from the perspective of an end-user or within a batch job.

The TSH utility relies on built-in features of the database engine to track performance statistics and facilitate analysis of those statistics for all active sessions in the database.  Unlike the Active Session History (ASH) feature of Oracle, TSH captures the TOTAL session history.  This includes the full duration of each session as well as ALL sessions in the database regardless of whether they are active or not.