Script sql : LOAD et AAS
- Détails
- Catégorie : Uncategorised
- Publié le vendredi 2 février 2018 20:20
- Écrit par Administrator
- Affichages : 45135
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;