Script sql : LOAD et AAS


ACCEPT data_to_map CHAR PROMPT 'LOAD, AAS, AAS_CPU or AAS_WAIT: '

set verify off

set serveroutput on;

spool &data_to_map._map.html

DECLARE
    myMax NUMBER;
    myDBName VARCHAR2(16);
    myTitle  VARCHAR2(128);
    myGraphType VARCHAR2(10);

FUNCTION DataCell ( P_Value NUMBER, P_Data_To_Map VARCHAR2 ) RETURN VARCHAR2 IS
    myReturn VARCHAR2(128);
    myColorHex VARCHAR2(16);
    myNumCpu NUMBER;

    threshold_1 NUMBER;  
    threshold_2 NUMBER;
    threshold_3 NUMBER;
    threshold_4 NUMBER; --very critical
BEGIN

    SELECT value INTO myNumCpu FROM v$osstat WHERE stat_name='NUM_CPUS';
    
    IF P_Data_To_Map = 'LOAD' THEN
        threshold_4 := 2;
        threshold_3 := 1;
        threshold_2 := 0.5;
        threshold_1 := 0.25;
    ELSE
        threshold_4 := 2 * myNumCpu;
        threshold_3 := 1 * myNumCpu;
        threshold_2 := 0.5 * myNumCpu;
        threshold_1 := 0.25 * myNumCpu;
    END IF;

    IF P_Value >= threshold_4 THEN myColorHex := '#C00000';
    ELSIF P_Value >= threshold_3 THEN myColorHex := '#FF0000';
    ELSIF P_Value >= threshold_2 THEN myColorHex := '#FFC000';
    ELSIF P_Value >= threshold_1 THEN myColorHex := '#FFFF00';
    ELSE myColorHex := '#92D050';
    END IF;
    
    myReturn := '<TD STYLE="background-color: '|| myColorHex || '; font-family: monospace">' || P_Value || '</TD>';
    RETURN myReturn;
END DataCell;
    
BEGIN
    DBMS_OUTPUT.ENABLE(100000);
    
    SELECT NAME INTO myDBName FROM V$DATABASE;

    myGraphType := '&data_to_map';
    CASE
        WHEN myGraphType = 'LOAD' THEN myTitle :='LOAD (AAS/CPU_COUNT)';
        WHEN myGraphType = 'AAS' THEN myTitle :='AAS (Active Average Session)';
        WHEN myGraphType = 'AAS_CPU' THEN myTitle :='AAS_CPU (CPU part of AAS)';
        WHEN myGraphType = 'AAS_WAIT' THEN myTitle :='AAS_WAIT (Wait part of AAS)';
        ELSE  DBMS_OUTPUT.PUT_LINE('!!! Houston, We Have a Problem! !!!');
    END CASE;
    
    DBMS_OUTPUT.PUT_LINE('<HTML>');
    DBMS_OUTPUT.PUT_LINE('<H1>Map of '||myTitle||' for database: '||myDBName||'</H1>');
    DBMS_OUTPUT.PUT_LINE('<TABLE>');
    DBMS_OUTPUT.PUT_LINE('<TR>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">Date / Hour</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">00-01</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">01-02</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">02-03</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">03-04</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">04-05</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">05-06</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">06-07</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">07-08</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">08-09</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">09-10</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">10-11</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">11-12</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">12-13</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">13-14</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">14-15</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">15-16</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">16-17</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">17-18</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">18-19</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">19-20</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">20-21</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">21-22</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">22-23</TD>');
    DBMS_OUTPUT.PUT_LINE('<TD STYLE="font-family: monospace; font-weight: bold; background-color:#DEDEDE">23-24</TD>');
    DBMS_OUTPUT.PUT_LINE('<TR>');

FOR cur IN (
WITH t AS
  (SELECT TO_CHAR(mtime,'YYYY/MM/DD') mtime,
    TO_CHAR(mtime,'HH24') d,
    &data_to_map AS value
  FROM
    (SELECT to_date(mtime,'YYYY-MM-DD HH24') mtime,
      ROUND(SUM(c1),1) AAS_WAIT,
      ROUND(SUM(c2),1) AAS_CPU,
      ROUND(SUM(cnt),1) AAS,
      ROUND(SUM(load),2) LOAD
    FROM
      (SELECT TO_CHAR(sample_time,'YYYY-MM-DD HH24') mtime,
        DECODE(session_state,'WAITING',COUNT(*),0)/360 c1,
        DECODE(session_state,'ON CPU',COUNT( *),0) /360 c2,
        COUNT(                               *)/360 cnt,
        COUNT(                               *)/360/cpu.core_nb load
      FROM dba_hist_active_sess_history,
        (--SELECT value AS core_nb FROM v$osstat WHERE stat_name='NUM_CPU_CORES' -- not like in https://laurent-leturgez.com/page/3/
        SELECT value AS core_nb FROM v$osstat WHERE stat_name='NUM_CPUS'
        ) cpu
      WHERE sample_time > sysdate - 30
      GROUP BY TO_CHAR(sample_time,'YYYY-MM-DD HH24'),
        session_state,
        cpu.core_nb
      )
    GROUP BY mtime
    )
  )
SELECT mtime,
  NVL("00-01_ ",0) "00-01_ ",
  NVL("01-02_ ",0) "01-02_ ",
  NVL("02-03_ ",0) "02-03_ ",
  NVL("03-04_ ",0) "03-04_ ",
  NVL("04-05_ ",0) "04-05_ ",
  NVL("05-06_ ",0) "05-06_ ",
  NVL("06-07_ ",0) "06-07_ ",
  NVL("07-08_ ",0) "07-08_ ",
  NVL("08-09_ ",0) "08-09_ ",
  NVL("09-10_ ",0) "09-10_ ",
  NVL("10-11_ ",0) "10-11_ ",
  NVL("11-12_ ",0) "11-12_ ",
  NVL("12-13_ ",0) "12-13_ ",
  NVL("13-14_ ",0) "13-14_ ",
  NVL("14-15_ ",0) "14-15_ ",
  NVL("15-16_ ",0) "15-16_ ",
  NVL("16-17_ ",0) "16-17_ ",
  NVL("17-18_ ",0) "17-18_ ",
  NVL("18-19_ ",0) "18-19_ ",
  NVL("19-20_ ",0) "19-20_ ",
  NVL("20-21_ ",0) "20-21_ ",
  NVL("21-22_ ",0) "21-22_ ",
  NVL("22-23_ ",0) "22-23_ ",
  NVL("23-24_ ",0) "23-24_ "
FROM t pivot( SUM(value) AS " " FOR d IN ('00' AS "00-01",'01' AS "01-02",'02' AS "02-03",'03' AS "03-04",'04' AS "04-05",'05' AS "05-06",'06' AS "06-07",'07' AS "07-08",
                                          '08' AS "08-09",'09' AS "09-10",'10' AS "10-11", '11' AS "11-12",'12' AS "12-13",'13' AS "13-14",'14' AS "14-15",'15' AS "15-16",
                                          '16' AS "16-17",'17' AS "17-18",'18' AS "18-19",'19' AS "19-20",'20' AS "20-21",'21' AS "21-22", '22' AS "22-23",'23' AS "23-24")
            )
ORDER BY mtime
)

LOOP
    DBMS_OUTPUT.PUT_LINE('<TR>');
    DBMS_OUTPUT.PUT_LINE('<TD style="font-family: monospace; font-weight: bold; background-color:#DEDEDE">' ||cur.mtime|| '<EM></TD>');
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."00-01_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."01-02_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."02-03_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."03-04_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."04-05_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."05-06_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."06-07_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."07-08_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."08-09_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."09-10_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."10-11_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."11-12_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."12-13_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."13-14_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."14-15_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."15-16_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."16-17_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."17-18_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."18-19_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."19-20_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."20-21_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."21-22_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."22-23_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE( DataCell(cur."23-24_ ",myGraphType) );
    DBMS_OUTPUT.PUT_LINE('</TR>');
END LOOP;

    DBMS_OUTPUT.PUT_LINE('</TABLE>');
    DBMS_OUTPUT.PUT_LINE('</HTML>');
END;
/

spool off;