DB Time of a SQL statement
Using Oracle’s v$active_session_history performance view is a way to find out the time spent by SQL statements in the database, as well as between different executions of those SQL statements, and find the slowest ones. The most useful column in v$active_session_history in this context is the USECS_PER_ROW column. Taking the sum of this column can show us the time spent by a SQL statement in the database. In the following section, the first query only shows the database time of a query in the database. However, using sql_exec_id, we can see the time of each execution in more detail and find the slowest execution on a sql_id.
select ash2.sql_id ash2.SQL_EXEC_ID, min(ash2.SQL_EXEC_START) start_time, sum(ash2.USECS_PER_ROW) as DBtime_usecs, ROUND(sum(ash2.USECS_PER_ROW) / (1000000), 1) DBtime_secs from v$active_session_history ash2 where sql_id = '65ugy5sb8sv8v' group by ash2.sql_id, ash2.SQL_EXEC_ID;
Above output shows the total db time of a SQL statement in the “DBTIME_SECS” column.
select ash2.sql_id, ash2.SQL_EXEC_ID, min(ash2.SQL_EXEC_START) start_time, sum(ash2.USECS_PER_ROW) as DBtime_usecs, ROUND(sum(ash2.USECS_PER_ROW) / (1000000),1) DBtime_secs from v$active_session_history ash2 where sql_id=’65ugy5sb8sv8v’ group by ash2.sql_id,ash2.SQL_EXEC_ID
Above output shows db time for different executions of a SQL statement in the “DBTIME_SECS” column.
Oracle Exadata DMA & Performance Eng. | ZDLRA | Oracle ACE Pro | CMC®
2moThank you for sharing; the ability to break down DB Time by SQL_EXEC_ID is a clever approach for identifying outlier executions. This prompts a deeper look into the methodology, particularly the choice of SUM(USECS_PER_ROW) over the more standard COUNT(*) for estimating time, given ASH's sampling-based nature. Moreover, it's crucial to consider the inherent limitations of ASH, such as its inability to capture DB Time during events like a 'logon storm' where activity occurs before a session is fully registered. A powerful diagnostic technique is to compare this ASH-based estimate against the DB time from V$SYS_TIME_MODEL; a significant discrepancy can reveal systemic issues, such as a misconfigured connection pool, that a SQL-specific analysis would miss. Finally, correlating these time variations with the SQL_PLAN_HASH_VALUE would complete the analysis, as plan instability is often the root cause of inconsistent performance. Thank you again for sparking such an important discussion.
Advisor to the CEO @ RayanHamAfza | Oracle DBA | AI DBA
2moThanks for sharing this practical approach!
Architect at Oracle
2moCouple comments: 1/ ash is used to estimate DB time 2/ accuracy of individual execution estimates depends on latency - long executions have more accurate estimates So this technique should only be used for long running executions, such as in your example. Indeed, whenever ASH is used to estimate DB time, more samples = more accuracy.