Диагностика shared server : Oracle

select name, status from v$shared_server;

select sid, operation_type, work_area_size from v$sql_workarea_active;

select sid,status, event, seconds_in_wait from v$session order by 4;

select s.sid,s.serial#,s.LOGON_TIME,s.STATUS,s.username,s.LAST_CALL_ET,s.module,s.event,s.SECONDS_IN_WAIT,s.lockwait,s.osuser,s.machine,s.server,x.sql_fulltext,px.sql_text prev_sql_txt
from v$session s, v$sqlarea x,v$sqlarea px
where s.status='ACTIVE' and s.TYPE!='BACKGROUND' and s.sql_address= x.address(+) and s.PREV_SQL_ADDR= px.address(+);

set linesize 1000
column MACHINE FORMAT A20
column EVENT FORMAT A30
column USERNAME FORMAT A20
select s.LOGON_TIME,s.username,s.LAST_CALL_ET,s.event,s.SECONDS_IN_WAIT,s.machine,s.server,s.status
from v$session s, v$sqlarea x,v$sqlarea px
where (s.status='ACTIVE' or s.server='SHARED') and s.TYPE!='BACKGROUND' and s.sql_address= x.address(+) and s.PREV_SQL_ADDR= px.address(+) order by 5;

select distinct status,server from v$session;

select username from v$session where server='SHARED' and status='INACTIVE';

select status,count(status) from v$circuit group by status;

select queue,count(queue) from v$circuit group by queue;

CREATE OR REPLACE TRIGGER BORED_SORTERS
AFTER LOGON ON DATABASE
DECLARE
V_USER VARCHAR2 (64);
BEGIN
SELECT SYS_CONTEXT('userenv','session_user') INTO V_USER FROM dual;
IF V_USER IN ('Q','LEVIT','ПЕТРОВ_В','MARKET','RAS_SUPERMAG') THEN
execute immediate 'alter session set workarea_size_policy=manual';
execute immediate 'alter session set sort_area_size=1024000';
execute immediate 'alter session set hash_area_size=1024000';
END IF;
END;
/