set ver off
SET FEEDBACK   OFF
SET TERMOUT    OFF
set lines 300

DEFINE NUM_ROWS = ''
COLUMN NUM_ROWS_val NOPRINT NEW_VALUE NUM_ROWS
SELECT '&&1' NUM_ROWS_val FROM dual;

DEFINE STAT_NAME = ''
COLUMN STAT_NAME_val NOPRINT NEW_VALUE STAT_NAME
SELECT NVL('&&2','DBT') STAT_NAME_val FROM dual;

SET TERMOUT    ON

@header_dbinfo 107 DC

declare
	V_NUM_ROWS INTEGER := '&NUM_ROWS';
	
	V_STAT_NAME_COD DBA_HIST_SERVICE_STAT.STAT_NAME%TYPE := UPPER('&STAT_NAME');
	V_STAT_NAME DBA_HIST_SERVICE_STAT.STAT_NAME%TYPE := 'DB time';
	TYPE T_DATA_STATS IS TABLE OF INTEGER;
	V_DATA_STATS T_DATA_STATS := T_DATA_STATS();

	TYPE R_SERVICE IS RECORD (
	SERVICE_NAME VARCHAR2(100),
	V_DATA_STATS T_DATA_STATS := T_DATA_STATS(0,0,0,0,0,0,0,0,0,0)
	);

	TYPE TR_SERVICE IS TABLE OF R_SERVICE;
	VTR_SERVICE TR_SERVICE := TR_SERVICE();

	CURSOR CMAESTRO (P_VSNAPID1 NUMBER, P_VBID1 NUMBER, P_PLINSTANCIA1 NUMBER, P_VSTAT_NAME1 VARCHAR2) IS
		SELECT NAME, VALUE VALUE
			FROM (SELECT NAME, VALUE
				FROM (SELECT E.SERVICE_NAME              NAME
					, E.VALUE - NVL(B.VALUE,0) VALUE
					FROM DBA_HIST_SERVICE_STAT B
					, GV$SERVICE_STATS E
					WHERE B.SNAP_ID(+)          = P_VSNAPID1
					AND B.DBID(+)             = P_VBID1
					AND B.INSTANCE_NUMBER(+)  = P_PLINSTANCIA1
					AND E.INST_ID             = P_PLINSTANCIA1
					AND B.STAT_NAME           = E.STAT_NAME
					AND B.SERVICE_NAME        = E.SERVICE_NAME
					AND E.STAT_NAME = P_VSTAT_NAME1
				)
				ORDER BY VALUE DESC
			)
		WHERE ROWNUM <= V_NUM_ROWS;
	
	RCMAESTRO CMAESTRO%ROWTYPE;
	
	V_DBID V$DATABASE.DBID%TYPE;
	V_INSTANCE_NUMBER V$INSTANCE.INSTANCE_NUMBER%TYPE;
	V_MAX_SNAP_ID DBA_HIST_SNAPSHOT.SNAP_ID%TYPE;
	V_COUNT INTEGER := 0;
	V_VALUE NUMBER;
	V_LINEA VARCHAR2(500);
	V_B_VALUE NUMBER;
	V_E_VALUE NUMBER;
	
	V_TITTLE_H1 VARCHAR2(20);
	V_TITTLE_H2 VARCHAR2(20);
	V_TITTLE_H3 VARCHAR2(20);
	V_TITTLE_H4 VARCHAR2(20);
	V_TITTLE_H5 VARCHAR2(20);
	V_TITTLE_H6 VARCHAR2(20);
BEGIN

dbms_output.put(rpad('DBC  - DB CPU',29,' '));
dbms_output.put(rpad('GCRBRT - gc cr block receive time',39,' '));
dbms_output.put(rpad('PTE  - parse time elapsed',33,' '));
dbms_output.put_line(rpad('UIOWT - user I/O wait time',37,' '));

dbms_output.put(rpad('DBT  - DB time',29,' '));
dbms_output.put(rpad('GCRBR  - gc cr blocks received',39,' '));
dbms_output.put(rpad('PR   - physical reads',33,' '));
dbms_output.put_line(rpad('UCA   - user calls',37,' '));

dbms_output.put(rpad('AWT  - application wait time',29,' '));
dbms_output.put(rpad('GCUBRT - gc current block receive time',39,' '));
dbms_output.put(rpad('PW   - physical writes',33,' '));
dbms_output.put_line(rpad('UCM   - user commits',37,' '));

dbms_output.put(rpad('CLWT - cluster wait time',29,' '));
dbms_output.put(rpad('GCUBR  - gc current blocks received',39,' '));
dbms_output.put(rpad('RS   - redo size',33,' '));
dbms_output.put_line(rpad('UCR   - user rollbacks',37,' '));

dbms_output.put(rpad('CWT  - concurrency wait time',29,' '));
dbms_output.put(rpad('LC     - logons cumulative',39,' '));
dbms_output.put(rpad('SCCH - session cursor cache hits',33,' '));
dbms_output.put_line(rpad('WEM   - workarea executions multipass',37,' '));

dbms_output.put(rpad('DBLC - db block changes',29,' '));
dbms_output.put(rpad('OCC    - opened cursors cumulative',39,' '));
dbms_output.put(rpad('SLR  - session logical reads',33,' '));
dbms_output.put_line(rpad('WE1   - workarea executions onepass',37,' '));

dbms_output.put(rpad('EC   - execute count',29,' '));
dbms_output.put(rpad('PC     - parse count (total)',39,' '));
dbms_output.put(rpad('SEET - sql execute elapsed time',33,' '));
dbms_output.put_line(rpad('WEO   - workarea executions optimal',37,' '));

dbms_output.put_line(rpad('-',150,'-'));

	IF V_STAT_NAME_COD NOT IN ('DBC','DBT','AWT','CLWT','CWT','DBLC','EC','GCRBRT','GCRBR','GCUBRT',
								'GCUBR','LC','OCC','PC','PTE','PR','PW','RS','SCCH','SLR','SEET',
								'UIOWT','UCA','UCM','UCR','WEM','WE1','WEO') THEN
		V_STAT_NAME_COD := 'DBT';
	END IF;

	IF V_STAT_NAME_COD = 'DBC' THEN
		V_STAT_NAME := 'DB CPU';
	END IF;

	IF V_STAT_NAME_COD = 'GCRBRT' THEN
		V_STAT_NAME := 'gc cr block receive time';
	END IF;

	IF V_STAT_NAME_COD = 'PTE' THEN
		V_STAT_NAME := 'parse time elapsed';
	END IF;

	IF V_STAT_NAME_COD = 'UIOWT' THEN
		V_STAT_NAME := 'user I/O wait time';
	END IF;

	IF V_STAT_NAME_COD = 'DBT' THEN
		V_STAT_NAME := 'DB time';
	END IF;

	IF V_STAT_NAME_COD = 'GCRBR' THEN
		V_STAT_NAME := 'gc cr blocks received';
	END IF;

	IF V_STAT_NAME_COD = 'PR' THEN
		V_STAT_NAME := 'physical reads';
	END IF;

	IF V_STAT_NAME_COD = 'UCA' THEN
		V_STAT_NAME := 'user calls';
	END IF;

	IF V_STAT_NAME_COD = 'AWT' THEN
		V_STAT_NAME := 'application wait time';
	END IF;

	IF V_STAT_NAME_COD = 'GCUBRT' THEN
		V_STAT_NAME := 'gc current block receive time';
	END IF;

	IF V_STAT_NAME_COD = 'PW' THEN
		V_STAT_NAME := 'physical writes';
	END IF;

	IF V_STAT_NAME_COD = 'UCM' THEN
		V_STAT_NAME := 'user commits';
	END IF;

	IF V_STAT_NAME_COD = 'CLWT' THEN
		V_STAT_NAME := 'cluster wait time';
	END IF;

	IF V_STAT_NAME_COD = 'GCUBR' THEN
		V_STAT_NAME := 'gc current blocks received';
	END IF;

	IF V_STAT_NAME_COD = 'RS' THEN
		V_STAT_NAME := 'redo size';
	END IF;

	IF V_STAT_NAME_COD = 'UCR' THEN
		V_STAT_NAME := 'user rollbacks';
	END IF;

	IF V_STAT_NAME_COD = 'CWT' THEN
		V_STAT_NAME := 'concurrency wait time';
	END IF;

	IF V_STAT_NAME_COD = 'LC' THEN
		V_STAT_NAME := 'logons cumulative';
	END IF;

	IF V_STAT_NAME_COD = 'SCCH' THEN
		V_STAT_NAME := 'session cursor cache hits';
	END IF;

	IF V_STAT_NAME_COD = 'WEM' THEN
		V_STAT_NAME := 'workarea executions - multipass';
	END IF;

	IF V_STAT_NAME_COD = 'DBLC' THEN
		V_STAT_NAME := 'db block changes';
	END IF;

	IF V_STAT_NAME_COD = 'OCC' THEN
		V_STAT_NAME := 'opened cursors cumulative';
	END IF;

	IF V_STAT_NAME_COD = 'SLR' THEN
		V_STAT_NAME := 'session logical reads';
	END IF;

	IF V_STAT_NAME_COD = 'EC' THEN
		V_STAT_NAME := 'execute count';
	END IF;

	IF V_STAT_NAME_COD = 'PC' THEN
		V_STAT_NAME := 'parse count (total)';
	END IF;

	IF V_STAT_NAME_COD = 'SEET' THEN
		V_STAT_NAME := 'sql execute elapsed time';
	END IF;

	IF V_STAT_NAME_COD = 'WEO' THEN
		V_STAT_NAME := 'workarea executions - optimal';
	END IF;

	dbms_output.put_line('ESTADISTICA: '||V_STAT_NAME_COD||' - '|| V_STAT_NAME);
	
	SELECT DBID INTO V_DBID FROM V$DATABASE;
	SELECT INSTANCE_NUMBER INTO V_INSTANCE_NUMBER FROM V$INSTANCE;
	SELECT MAX(SNAP_ID) INTO V_MAX_SNAP_ID FROM DBA_HIST_SNAPSHOT WHERE INSTANCE_NUMBER = V_INSTANCE_NUMBER AND DBID = V_DBID;
	
	OPEN CMAESTRO(V_MAX_SNAP_ID, V_DBID, V_INSTANCE_NUMBER, V_STAT_NAME);
	LOOP
		FETCH CMAESTRO INTO RCMAESTRO;
		EXIT WHEN CMAESTRO%NOTFOUND;
		
		VTR_SERVICE.EXTEND;
		V_COUNT := V_COUNT + 1;
		VTR_SERVICE(V_COUNT).V_DATA_STATS := T_DATA_STATS(0,0,0,0,0,0,0);
		
		VTR_SERVICE(V_COUNT).SERVICE_NAME := RCMAESTRO.NAME;

		IF V_STAT_NAME_COD IN ('DBC','DBT','AWT','CLWT','CWT','GCRBRT1','GCUBRT1','PTE','SEET','UIOWT') THEN
			VTR_SERVICE(V_COUNT).V_DATA_STATS(1) := RCMAESTRO.VALUE / 1000000;
		ELSE 
			VTR_SERVICE(V_COUNT).V_DATA_STATS(1) := RCMAESTRO.VALUE;
		END IF;
	END LOOP;
	CLOSE CMAESTRO;
	
	FOR I IN 1..V_COUNT LOOP
		FOR J IN 2..7 LOOP
			--BEGIN
			--SELECT VALUE/1000000 INTO V_VALUE
			--  FROM (SELECT E.STAT_NAME              NAME
			--			 , E.VALUE - NVL(B.VALUE,0) VALUE
			--		 FROM DBA_HIST_SERVICE_STAT B
			--			, DBA_HIST_SERVICE_STAT E
			--		WHERE B.SNAP_ID(+) = E.SNAP_ID  - 1 AND B.DBID(+) = E.DBID AND B.INSTANCE_NUMBER(+)= E.INSTANCE_NUMBER
			--		  AND B.SNAP_ID(+)          = V_MAX_SNAP_ID - J + 1
			--		  AND E.SNAP_ID             = V_MAX_SNAP_ID - J + 2
			--		  AND B.DBID(+)             = V_DBID
			--		  AND E.DBID                = V_DBID
			--		  AND B.INSTANCE_NUMBER(+)  = V_INSTANCE_NUMBER
			--		  AND E.INSTANCE_NUMBER     = V_INSTANCE_NUMBER
			--		  AND B.STAT_NAME           = E.STAT_NAME
			--		  AND B.SERVICE_NAME        = E.SERVICE_NAME
			--		  AND B.SERVICE_NAME        = VTR_SERVICE(I).SERVICE_NAME
			--		  AND E.SERVICE_NAME        = VTR_SERVICE(I).SERVICE_NAME
			--		  AND E.STAT_NAME = V_STAT_NAME
			--	   );
			--EXCEPTION
			--	WHEN OTHERS THEN
			--		V_VALUE := 0;
			--END;
			
			BEGIN
				SELECT NVL(B.VALUE,0) INTO V_B_VALUE
				FROM DBA_HIST_SERVICE_STAT B
				WHERE B.SNAP_ID             = V_MAX_SNAP_ID - J + 1
				AND B.DBID                = V_DBID
				AND B.INSTANCE_NUMBER     = V_INSTANCE_NUMBER
				AND B.SERVICE_NAME        = VTR_SERVICE(I).SERVICE_NAME
				AND B.STAT_NAME = V_STAT_NAME;
			EXCEPTION
				WHEN OTHERS THEN 
					V_B_VALUE := 0;
			END;
				   
			BEGIN
				SELECT NVL(VALUE,0) INTO V_E_VALUE
				FROM DBA_HIST_SERVICE_STAT
				WHERE SNAP_ID           = V_MAX_SNAP_ID - J + 2
				AND DBID                = V_DBID
				AND INSTANCE_NUMBER     = V_INSTANCE_NUMBER
				AND SERVICE_NAME        = VTR_SERVICE(I).SERVICE_NAME
				AND STAT_NAME = V_STAT_NAME;
			EXCEPTION
				WHEN OTHERS THEN 
					V_E_VALUE := 0;
			END;

			--dbms_output.put_line('J:'||j);
			--dbms_output.put_line('V_MAX_SNAP_ID:'||V_MAX_SNAP_ID);
			--dbms_output.put_line('V_DBID:'||V_DBID);
			--dbms_output.put_line('V_INSTANCE_NUMBER:'||V_INSTANCE_NUMBER);
			--dbms_output.put_line('VTR_SERVICE(I).SERVICE_NAME:'||VTR_SERVICE(I).SERVICE_NAME);
			--dbms_output.put_line('V_STAT_NAME:'||V_STAT_NAME);
			
			VTR_SERVICE(I).V_DATA_STATS(J) := 0;
			IF V_E_VALUE >= V_B_VALUE THEN
				VTR_SERVICE(I).V_DATA_STATS(J) := V_E_VALUE - V_B_VALUE;
				
				IF V_STAT_NAME_COD IN ('DBC','DBT','AWT','CLWT','CWT','GCRBRT1','GCUBRT1','PTE','SEET','UIOWT') THEN
					VTR_SERVICE(I).V_DATA_STATS(J) := VTR_SERVICE(I).V_DATA_STATS(J) / 1000000;
				END IF;
			END IF;
			
			--VTR_SERVICE(I).V_DATA_STATS(J) := 0;
		END LOOP;
	END LOOP;
		
	V_TITTLE_H1 := LPAD(LPAD(EXTRACT(HOUR from CAST(SYSDATE AS TIMESTAMP)),3,'.')||'H',15);
	V_TITTLE_H2 := LPAD(LPAD(EXTRACT(HOUR from CAST(SYSDATE AS TIMESTAMP))-1,3,'.')||'H',15);
	V_TITTLE_H3 := LPAD(LPAD(EXTRACT(HOUR from CAST(SYSDATE AS TIMESTAMP))-2,3,'.')||'H',15);
	V_TITTLE_H4 := LPAD(LPAD(EXTRACT(HOUR from CAST(SYSDATE AS TIMESTAMP))-3,3,'.')||'H',15);
	V_TITTLE_H5 := LPAD(LPAD(EXTRACT(HOUR from CAST(SYSDATE AS TIMESTAMP))-4,3,'.')||'H',15);
	V_TITTLE_H6 := LPAD(LPAD(EXTRACT(HOUR from CAST(SYSDATE AS TIMESTAMP))-5,3,'.')||'H',15);

	--V_LINEA := RPAD('SERVICIO',30)||LPAD(TO_CHAR(SYSDATE,'HH24:MI'),15)||LPAD(EXTRACT(HOUR from CAST(SYSDATE AS TIMESTAMP)),15)||LPAD(EXTRACT(HOUR from CAST(SYSDATE AS TIMESTAMP))-1,15)||LPAD(EXTRACT(HOUR from CAST(SYSDATE AS TIMESTAMP))-2,15)||LPAD(EXTRACT(HOUR from CAST(SYSDATE AS TIMESTAMP))-3,15)||LPAD(EXTRACT(HOUR from CAST(SYSDATE AS TIMESTAMP))-4,15)||LPAD(EXTRACT(HOUR from CAST(SYSDATE AS TIMESTAMP))-5,15);
	V_LINEA := RPAD('SERVICIO',30)||LPAD(TO_CHAR(SYSDATE,'HH24:MI'),15)||V_TITTLE_H1||V_TITTLE_H2||V_TITTLE_H3||V_TITTLE_H4||V_TITTLE_H5||V_TITTLE_H6;
	DBMS_OUTPUT.PUT_LINE(V_LINEA);
	
	V_LINEA := RPAD('-------------------------',30)||LPAD('------------',15)||LPAD('------------',15)||LPAD('------------',15)||LPAD('------------',15)||LPAD('------------',15)||LPAD('------------',15)||LPAD('------------',15);
	DBMS_OUTPUT.PUT_LINE(V_LINEA);
	FOR I IN 1..V_COUNT LOOP
		V_LINEA := RPAD(VTR_SERVICE(I).SERVICE_NAME,30);
		FOR J IN 1..7 LOOP
			V_LINEA := V_LINEA || LPAD(TRIM(VTR_SERVICE(I).V_DATA_STATS(J))||'.0',15);
		END LOOP;
		DBMS_OUTPUT.PUT_LINE(V_LINEA);
	END LOOP;
END;
/



UNDEFINE NUM_ROWS
UNDEFINE STAT_NAME
UNDEFINE &1
UNDEFINE &2