Oracle Magazine, May/June 2019
Database Developer and DBA ETL Sometimes an impatient user will not be pleased about being asked to repeat potentially multiple times an activity that is causing frustration If tracing cannot be successfully utilized there are still other options available for homing in on the SQL statements that are directly impacting business functions Automatic Workload Repository The Automatic Workload Repository feature of Oracle Database collects and analyzes performance statistics in the database to assist with problematic SQL detection The gathered data is stored both in memory and in the database and the data includes SQL execution and performance statistics An Automatic Workload Repository snapshot is a capture of the state of a database at a point in time so when youre analyzing a performance issue encountered by a user a delta of the snapshots before and after the time when the performance issue occurred can be used to discover problematic SQL statements that most probably were related to the issue Much as with the trace methodology the ideal scenario is to take snapshots immediately before and after replicating a performance ORACLE MAGAZINE MAY JUNE 2019 109 problem Active Session History The Active Session History feature of Oracle Database samples active database sessions each second writing the data to memory and persistent storage An active session is a session that is using CPU and is not sitting idle waiting for a request from an application and or user Slow running SQL statements can also be detected by Active Session History because a session running a SQL statement for say 10 seconds will have 10 consecutive entries in the V ACTIVE_ SESSION_ HISTORY view for the same SQL_ ID SQL_ EXEC_ ID columns pairing Automatic Workload Repository and Active Session History are powerful tools because they allow for after the fact problem analysis A business user may report a performance problem that occurred a few hours beforehand Even if that problem cannot be replicated the Automatic Workload Repository and Active Session History
You must have JavaScript enabled to view digital editions.
