Here is a reusable template for summarizing ASH session activity by any column or combination of columns:

-- Function: Summarize session activity by <dimension>

SELECT
    <dimension_column>,
    COUNT(*) AS cnt,
    TO_CHAR(100 * TRUNC(RATIO_TO_REPORT(COUNT(*)) OVER (), 4), 'FM990.99') || '%' AS "%"
FROM
    <ASH_data>
    <optional_joins>
WHERE
    <time_rang_condition>
    <optional_conditions>
GROUP BY
    <dimension_column>
ORDER BY
    COUNT(*) DESC
FETCH FIRST 10 ROWS ONLY;

Explanation:

• ASH_data: Either V$ACTIVE_SESSION_HISTORY for recent activities or DBA_HIST_ACTIVE_SESS_HISTORY for historical activities.
• time_rang_condition:
  • Recent n Minutes: WHERE sample_time > SYSDATE – <n> / 60 / 24
  • Explicit Date Range: WHERE sample_time BETWEEN TO_DATE(‘<start_date>’, ‘YYYY-MM-DD HH24:MI:SS’) AND TO_DATE(‘<end_date>’, ‘YYYY-MM-DD HH24:MI:SS’)

By leveraging this formula, you can analyze session activity using any of the 112 columns available in ASH views. You can group by a single column or a combination to gain deeper insight into specific dimensions of activity.

Conclusion

These examples demonstrate how to identify top entities in session activity. Hopefully, these examples have piqued your interest in further exploration. Here are some ideas you can consider when crafting your own queries:

• Identify other top entities such as users, modules, or services.
• Adjust time range to analyze a different duration. For historical analysis, use DBA_HIST_ACTIVE_SESS_HISTORY instead of V$ACTIVE_SESSION_HISTORY as the data source.
• Apply additional filters not only to narrow the analysis but also to provide data from different angles, such as filtering by specific users, modules, services, or wait events, and their combinations.
• Join ASH data with other views (e.g., V$SQL, DBA_HIST_SQLSTAT, DBA_OBJECTS) to gain more comprehensive information.

You can customize the SQL queries to suit your specific needs and uncover the details most relevant to your analysis.

• Top SQL statements
• Top Wait Events
• Top Accessed Objects
• Top PGA or Temp Space Consumers
• General Query Formula