-- When your server hardware is behind the times, you need a way to find out who is 
-- running queries or other SQL that have brought the cpu to a screeching halt. The function 
-- and view listed here will help you find the SQL that cause CPU slowdown. 
-- It will also tell you who ran the SQL and when it was run.


CREATE OR REPLACE FUNCTION system.f$_sql_text (
   p_address      VARCHAR2,
   p_hash_value   VARCHAR2
)
/*
   =============================================================
   This function returns the entire query (up to 250 lines)
   as the query was issued to the database, including formatting.
   SQL issued with no formatting is formatted in a basic way.

   ©2005 Edward Stoever 

   This function was created for use in the SLOW_SQL view defined 
   at the bottom of this page.
   =============================================================
*/
RETURN VARCHAR2
IS
   var_f       VARCHAR2 (32000);

   CURSOR c1
   IS
      SELECT   SUBSTR (sql_text, 1, 32000)
          FROM v$sqltext_with_newlines
         WHERE address = p_address AND hash_value = p_hash_value
      ORDER BY piece ASC;

   var_sql     v$sqltext_with_newlines.sql_text%TYPE;

   CURSOR c2
   IS
      SELECT COUNT (*)
        FROM v$sqltext_with_newlines
       WHERE address = p_address AND hash_value = p_hash_value;

   var_count   NUMBER;

   FUNCTION simple_format (unformatted_sql VARCHAR2)
      RETURN VARCHAR2
   IS
      cnt   NUMBER           := 0;
      yy    VARCHAR2 (32000);
      zz    VARCHAR2 (1);
   BEGIN
      FOR i IN 1 .. LENGTH (unformatted_sql)
      LOOP
         cnt := cnt + 1;
         zz := (SUBSTR (unformatted_sql, i, 1));

         IF cnt > 50 AND zz = ' '
         THEN
            cnt := 0;
            zz := CHR (10);
            yy := yy || zz || '       ';
         ELSE
            yy := yy || zz;
         END IF;
      END LOOP;

      RETURN yy;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN '--> Error in sub-function simple_format';
   END;
BEGIN
   var_f := NULL;

   OPEN c2;

   FETCH c2
    INTO var_count;

   CLOSE c2;

   IF var_count > 250
   THEN
      var_f :=
         '-- SQL IS LARGER THAN CAN BE DISPLAYED BY THIS FUNCTION'
         || CHR (10);
      RETURN var_f;
   END IF;

   OPEN c1;

   FOR i IN 1 .. 250                                       --maximum 250 lines
   LOOP
      FETCH c1
       INTO var_sql;

      IF LENGTH (var_sql) > 30000
      THEN
         var_f :=
               '-- SQL IS LARGER THAN CAN BE DISPLAYED BY THIS FUNCTION'
            || CHR (10);
         RETURN var_f;
      END IF;

      EXIT WHEN c1%NOTFOUND;
      var_f := var_f || var_sql;
   END LOOP;

   CLOSE c1;

   IF var_f IS NULL
   THEN
      RETURN '--> No SQL found!';
   ELSE
      IF     INSTR (var_f, CHR (9)) = 0
         AND INSTR (var_f, CHR (10)) = 0
         AND INSTR (var_f, CHR (13)) = 0
      THEN
         var_f := simple_format (var_f);
         NULL;
      END IF;

      var_f := var_f || CHR (10);
      RETURN var_f;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN '--> Error in Function f$_sql_text';
END f$_sql_text;
/

CREATE OR REPLACE FORCE VIEW SYSTEM.SLOW_SQL
(SQL_TEXT, USERNAME)
AS 
SELECT      '======= '
            || username
            || ' - '
            || last_load_time
            || ' - '
            || 'CPU TIME: '
            || cpu_time
            || ' ======='
            || CHR (10)
            || f$_sql_text (address, hash_value) AS sql_text,
		USERname
       FROM v$sql, DBA_USERS
      WHERE parsing_user_id = user_id
	  AND cpu_time > 400000000
            WITH READ ONLY;
/

CREATE PUBLIC SYNONYM SLOW_SQL FOR SYSTEM.SLOW_SQL;
/