Snippet Name: Find most active SQL in the last hour Description: Finds the most active SQL code run in the last hour. Also see:» Add PSOUG Search to SQL Developer » Converting Rows to Columns » Database Links: CURRENT_USER » Instant Test Database with DCBA » Show info on current context » Lookup Oracle error messages » Display and release DBMS_LOCK locks » Display locks and latches » Show rollback segment stats » Show active transactions » List supported INIT.ORA parameters » Display database SGA statistics » Measure the Buffer Cache Hit Ratio » List security related profile informat... » Find users with deadly privileges » Audit User Logins (User Login Trigger) » Block TOAD and other tools » Kill Session » Extents » DBA Users » DBA Tablespaces » DBA triggers » DBA Sessions » DBA Roles » DBA Objects » DBA Links » DBA Jobs » Job Queue » DBA Free Space » Data Files Comment: (none) Language: PL/SQL Highlight Mode: PLSQL Last Modified: February 28th, 2009
Description: Finds the most active SQL code run in the last hour.
Also see:» Add PSOUG Search to SQL Developer » Converting Rows to Columns » Database Links: CURRENT_USER » Instant Test Database with DCBA » Show info on current context » Lookup Oracle error messages » Display and release DBMS_LOCK locks » Display locks and latches » Show rollback segment stats » Show active transactions » List supported INIT.ORA parameters » Display database SGA statistics » Measure the Buffer Cache Hit Ratio » List security related profile informat... » Find users with deadly privileges » Audit User Logins (User Login Trigger) » Block TOAD and other tools » Kill Session » Extents » DBA Users » DBA Tablespaces » DBA triggers » DBA Sessions » DBA Roles » DBA Objects » DBA Links » DBA Jobs » Job Queue » DBA Free Space » Data Files
DESC gv$active_session_history SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD FROM gv$active_session_history WHERE sample_time > SYSDATE - 1/24 AND session_type = 'BACKGROUND' GROUP BY sql_id ORDER BY COUNT(*) DESC; SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD FROM gv$active_session_history WHERE sample_time > SYSDATE - 1/24 AND session_type = 'FOREGROUND' GROUP BY sql_id ORDER BY COUNT(*) DESC;
