SET LONG 10000
SET FEEDBACK OFF RECSEP OFF UNDERLINE "~" VERIFY OFF DEFINE "&"
DEFINE TOPS=15
DEFINE USU_SCHEMA = "'SCHEMA', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4','SCHEMA5'"
DEFINE USU_SCHEMA = "'USR_SOAP'"
COL X NOPRINT
COL SERVER FORMAT A19 HEAD "Processes"
COL USERNAME FORMAT A23 HEAD "Users (Top &TOPS.)"
COL STATUS FORMAT A19 HEAD "Status"
COL SESSOES FORMAT 999999999 HEAD "Sessions"
COL PROCESS FORMAT 999999999 HEAD " Quantidade"
COL PERCENT FORMAT 999999D00 HEAD "% Total"
COL SHARED FORMAT 999999999 HEAD " Shared"
COL DEDICADO FORMAT 999999999 HEAD " Dedicated"
COL PGA_USED FORMAT 999990D00 HEAD "PGA|Used (Mb)" JUST L
COL PGA_ALLOC FORMAT 999990D00 HEAD "PGA|Allocated (Mb)" JUST L
COL PGA_FREEABLE FORMAT 999990D00 HEAD "PGA|Freeable (Mb)" JUST L
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF PGA_USED PGA_ALLOC PGA_FREEABLE PROCESS PERCENT ON REPORT
SELECT
V1.SERVER, V1.PGA_USED, V1.PGA_ALLOC, V1.PGA_FREEABLE, V1.PROCESS, V1.PROCESS*100/V1.TOT_PROCESS PERCENT
FROM (
SELECT DECODE( P.BACKGROUND, 1, 'BACKGROUND',
DECODE( SUBSTR( P.PROGRAM, INSTR( P.PROGRAM, '(' ) + 1, 2 )
,'PS', 'PSEUDO'
,'SN', 'JOB QUEUE', 'CJ', 'JOB QUEUE'
,'J0', 'JOB QUEUE', 'q0', 'JOB QUEUE'
,'TN', 'DEDICATED', 'or', 'DEDICATED'
,'D0', 'DISPATCHERS','D1', 'DISPATCHERS'
,'S0', 'SHARED SERVERS','S1', 'SHARED SERVERS'
,'P0', 'PARALLEL SERVERS','P1', 'PARALLEL SERVERS' ) ) SERVER
,COUNT(*) PROCESS
,ROUND(SUM(PGA_USED_MEM)/1048576,2) PGA_USED
,ROUND(SUM(PGA_ALLOC_MEM)/1048576,2) PGA_ALLOC
,ROUND(SUM(PGA_FREEABLE_MEM)/1048576,2) PGA_FREEABLE
,( sum(count(*)) over () ) TOT_PROCESS
FROM V$PROCESS P
GROUP BY DECODE( P.BACKGROUND, 1, 'BACKGROUND',
DECODE( SUBSTR( P.PROGRAM, INSTR( P.PROGRAM, '(' ) + 1, 2 )
,'PS', 'PSEUDO'
,'SN', 'JOB QUEUE', 'CJ', 'JOB QUEUE'
,'J0', 'JOB QUEUE', 'q0', 'JOB QUEUE'
,'TN', 'DEDICATED', 'or', 'DEDICATED'
,'D0', 'DISPATCHERS','D1', 'DISPATCHERS'
,'S0', 'SHARED SERVERS','S1', 'SHARED SERVERS'
,'P0', 'PARALLEL SERVERS','P1', 'PARALLEL SERVERS' ) )
ORDER BY PROCESS DESC
) V1
/
CLEAR BREAK
CLEAR COMPUTE
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF SESSOES PERCENT SHARED DEDICADO ON REPORT
SELECT V1.USERNAME, V1.SESSOES, V1.SESSOES*100/V1.TOT_SESSOES PERCENT, V1.SHARED, V1.DEDICADO
FROM (
SELECT COUNT(*) SESSOES,
( sum(count(*)) over () ) TOT_SESSOES,
SUM(DECODE( SERVER, 'NONE', 1, 'SHARED', 1, 0 ) ) SHARED,
SUM(DECODE( SERVER, 'DEDICATED', 1, 0 ) ) DEDICADO,
DECODE(USERNAME, NULL, 'BACKGROUND',
DECODE( SUBSTR(USERNAME,3,2), 'BR', 'TIT_ONLINE (xxBR)', USERNAME ) ) USERNAME
FROM V$SESSION
GROUP BY
DECODE(USERNAME, NULL, 'BACKGROUND',
DECODE( SUBSTR(USERNAME,3,2), 'BR', 'TIT_ONLINE (xxBR)', USERNAME ) )
ORDER BY 1 DESC
) V1
WHERE ROWNUM <= &TOPS.
/
CLEAR BREAK
CLEAR COMPUTE
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF SESSOES PERCENT SHARED DEDICADO ON REPORT
SELECT V1.USERNAME STATUS, V1.SESSOES, V1.SESSOES*100/V1.TOT_SESSOES PERCENT, V1.SHARED, V1.DEDICADO
FROM (
SELECT COUNT(*) SESSOES,
( sum(count(*)) over () ) TOT_SESSOES,
SUM(DECODE( SERVER, 'NONE', 1, 'SHARED', 1, 0 ) ) SHARED,
SUM(DECODE( SERVER, 'DEDICATED', 1, 0 ) ) DEDICADO,
STATUS USERNAME
FROM V$SESSION
GROUP BY STATUS
ORDER BY 1 DESC
) V1
/
CLEAR BREAK
CLEAR COMPUTE
COL SCHEMA FORMAT A35
col program format a50 head "Programa"
SELECT LOWER ( USERNAME || '.' || MACHINE ) SCHEMA, REGEXP_REPLACE( PROGRAM, '\(P[0..9][0..9][0..9]\)', '' ) PROGRAM, COUNT(*) "Qtde"
FROM V$SESSION
WHERE USERNAME IN ( &USU_SCHEMA )
GROUP BY LOWER ( USERNAME || '.' || MACHINE ), REGEXP_REPLACE( PROGRAM, '\(P[0..9][0..9][0..9]\)', '' )
order by count(*) desc
/
PROMPT
SET VERIFY OFF SERVEROUT ON FEEDBACK OFF
DECLARE
cValue varchar2(8000);
nPosI number := 1;
nPosF number := 0;
BEGIN
dbms_output.put_line( 'Dispatchers Configurados ' );
dbms_output.put_line( lpad( '~', 77, '~' ) );
SELECT distinct TRIM(VALUE) || ',' INTO cValue
FROM V$PARAMETER WHERE NAME in ( 'mts_dispatchers', 'dispatchers' );
LOOP
nPosF := instr(substr( cValue, nPosI), ',' );
dbms_output.put_line( Trim( substr( cValue, nPosI, nPosF-1)) );
nPosI := nPosI + nPosF;
exit when nPosI > length( cValue ) ;
END LOOP;
END;
/
COL LISTENER FORMAT A15 HEAD "Listener"
COL USERNAME FORMAT A31 HEAD "Usuário"
COL SESSOES FORMAT 9999999 HEAD "Sessões|Total"
COL SESSOES_ATIVAS FORMAT 9999999 HEAD "Sessões|Ativas"
COL SESSOES_INATIVAS FORMAT 9999999 HEAD "Sessões|Inativas"
COL SESSOES_SNIPED FORMAT 9999999 HEAD "Sessões|Sniped"
COL SESSOES_KILLED FORMAT 9999999 HEAD "Sessões|Killed"
col program format a50 head "Programa"
SET NULL TOTAL
BREAK ON LISTENER SKIP 1
WITH Nomes AS
(
SELECT
nvl(username, 'BackGround') username,
machine/* , program, server */,
replace( initcap
(
CASE
WHEN USERNAME IS NULL THEN 'BACKGROUND'
WHEN USERNAME = 'CAD_CONS1' THEN 'TELNET'
WHEN substr(username,3,2) = 'BR' THEN 'Titulo_Online'
WHEN USERNAME IN ( &USU_SCHEMA ) THEN decode(lower(substr(machine,1,4)),'xxx\','schema.xxx',username||'.'||machine )
ELSE USERNAME END
),'_','') eqNome
FROM
v$session
--ORDER BY 1
),
sd AS
(
SELECT nomes.username, nomes.eqnome, nomes.machine
FROM nomes
GROUP BY nomes.username, nomes.eqnome, nomes.machine
HAVING count(*) > 4
),
--g as (SELECT eqnome FROM nomes group by eqnome having count(*) > 4 ),
--sd AS
--(
-- SELECT distinct nomes.username, nomes.eqnome, nomes.machine
-- FROM nomes, g
-- WHERE nomes.eqnome = g.eqnome
--),
Circuitos AS
(
SELECT
decode( grouping(d.conf_indx) + grouping( sd.eqnome ), 2, 99, nvl(d.conf_indx+1,0) ) LISTENER
,decode( grouping(d.conf_indx) + grouping( sd.eqnome ), 2, 1, 1, 1, 0 ) ORDEM
,decode( grouping(sd.EqNome), 0, nvl(sd.EqNome, 'Outros' ), 'Total' ) USERNAME
,count(*) SESSOES
,sum( decode( s.status, 'ACTIVE' , 1, 0 ) ) SESSOES_ATIVAS
,sum( decode( s.status, 'INACTIVE', 1, 0 ) ) SESSOES_INATIVAS
,sum( decode( s.status, 'SNIPED' , 1, 0 ) ) SESSOES_SNIPED
,sum( decode( s.status, 'KILLED' , 1, 0 ) ) SESSOES_KILLED
FROM sd, v$session s, v$circuit c, v$dispatcher d
WHERE nvl(s.username, 'BackGround' ) = sd.username(+)
AND s.machine = sd.machine(+)
AND s.saddr = c.saddr(+) and c.dispatcher = d.paddr(+)
GROUP BY ROLLUP( d.conf_indx, sd.eqnome )
)
SELECT
decode( c.listener, 99, 'Total', 0, 'Dedicado', 'Dispatcher' ) LISTENER
,c.username
,c.sessoes
,c.sessoes_ativas
,c.sessoes_inativas
,c.sessoes_sniped
,c.sessoes_killed
FROM
CIRCUITOS C
ORDER BY c.listener, c.ordem, c.sessoes
/
PROMPT
SET FEEDBACK 6 UNDERLINE "-" VERIFY ON NULL ''
CLEAR BREAK
COL SERVER CLEAR
COL STATUS CLEAR
COL PERCENT CLEAR
COL PROCESS CLEAR
COL LISTENER CLEAR
COL USERNAME CLEAR
COL SESSOES CLEAR
COL SESSOES_ATIVAS CLEAR
COL SESSOES_INATIVAS CLEAR
COL SESSOES_SNIPED CLEAR
COL SESSOES_KILLED CLEAR
COL PGA_USED CLEAR
COL PGA_ALLOC CLEAR
COL PGA_FREEABLE CLEAR
COL PROGRAM CLEAR
UNDEFINE USO_SCHEMA
