Sign in or 
Share this
Statistics Collection for Oracle OLAP Sessions attached to Multidimensional Datatypes
These steps should be performed by a privileged user such as SYS or SYSTEM
drop table olap_session_stats cascade constraints purge;
exec sys.dbms_scheduler.drop_job(job_name => '"OLAP_SESSION_STATS_JOB"');
create table "OLAP_SESSION_STATS"
( "TIMESTAMP" timestamp (6) with time zone
, "USERNAME" varchar2(30)
, "SID" number
, "SERIAL#" number
, "INST_ID" number
, "OWNER" varchar2(30) not null enable
, "AW_NAME" varchar2(30)
, "SERVER" varchar2(9)
, "PGA_USED" number
, "PGA_MAX" number
, "OPP_SIZE" number
, "AW_MODE" varchar2(10)
, "AW_GEN" number
, "OPP_HITRTO" number
, "OPP_TOUCHS" number
, "OPP_HITS" number
, "OPP_MISSES" number
, "OPP_NEWPGS" number
, "OPP_RECLMD" number
, "PROC_USED" number
, "PROC_ALLOC" number
, "CURS_CURR" number
);
begin
sys.dbms_scheduler.create_job(
job_name => '"OLAP_SESSION_STATS_JOB"', job_type => 'PLSQL_BLOCK',
job_action => '
begin
insert into sys.OLAP_SESSION_STATS
select systimestamp, gvs.username, gvs.sid, gvs.serial#, gvs.inst_id, owner, aw_name,
server, pga_used_mem, pga_max_mem, pool_size, attach_mode,
generation, round(100*((pool_hits)/((pool_hits)+(pool_misses))),1),
pool_hits+pool_misses opp_touchs, pool_hits, pool_misses,
pool_new_pages, pool_reclaimed_pages, sum(gvm.used),
sum(gvm.max_allocated), sum(gvt.value)
from gv$process gvp, gv$session gvs, gv$aw_calc gva, gv$aw_olap gvo, dba_aws da,
gv$process_memory gvm, gv$sesstat gvt
where gva.session_id = gvs.sid and
gvp.inst_id = gvs.inst_id and
gvs.inst_id = gva.inst_id and
gva.inst_id = gvm.inst_id and
gvm.inst_id = gvt.inst_id and
gva.session_id = gvo.session_id and
gvp.addr = gvs.paddr and
gvt.statistic# = 3 and
da.aw_number = gvo.aw_number and
gvm.pid = gvp.pid and
gvp.serial# = gvm.serial# and
gvt.sid = gvs.sid
group by systimestamp, gvs.username, gvs.sid, gvs.serial#, gvs.inst_id,owner, aw_name,
generation, server, pga_used_mem, pga_max_mem, pool_size, attach_mode, pool_hits,
pool_misses, pool_new_pages, pool_reclaimed_pages
order by gvs.username, gvs.sid, gvs.serial#;
commit;
end;',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
start_date => systimestamp at time zone 'US/Eastern', job_class => 'DEFAULT_JOB_CLASS',
comments => 'Session Statistics Collection for OLAP Analytic Workspaces',
auto_drop => FALSE, enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"OLAP_SESSION_STATS_JOB"',
attribute => 'restartable', value => TRUE);
sys.dbms_scheduler.enable( '"OLAP_SESSION_STATS_JOB"' );
end;
/
set lines 120 pages 500
col usn for a20 hea "USER (SID,SER#,SVR)"
col atc for a18 hea "OLAP AW(GEN)(MODE)"
col tme hea "COLLECTED"
col pmx for 9990.9 hea "PGA MB|MAX"
col psd for 9990.9 hea "PGA MB|USED"
col oph for 990.9 hea "OLAP|HIT %"
col ops for 9990.9 hea "OLAP|MB"
col pus for 9990.9 hea "PROC MB|USED"
col pal for 9990.9 hea "PROC MB|ALLOC"
col csc for 9990 hea "CURS|CURR"
break on usn on atc skip 1
select username||' ('||sid||','||serial#||','||
decode(server,'DEDICATED','D','SHARED','S','U')||')' usn,
to_char(timestamp,'YYYYMMDD HH24:MI:SS') tme,
owner||'.'||aw_name||'('||aw_gen||')('||decode(aw_mode,
'READ WRITE','RW','READ ONLY','RO','MULTIWRITE','MW','EXCLUSIVE','XW',aw_mode)||')' atc,
round(OPP_SIZE/1024/1024,2) ops, OPP_HITRTO oph, round(PGA_USED/1024/1024,2) psd,
round(PGA_MAX/1024/1024,2) pmx, round(PROC_USED/1024/1024,2) pus,
round(PROC_ALLOC/1024/1024,2) pal, curs_curr csc
from OLAP_SESSION_STATS
order by username, sid, serial#, atc, tme;
.................... ................. .............. ...OLAP ..OLAP .PGA MB .PGA MB PROC MB PROC MB .CURS
USER (SID,SER#,SVR). COLLECTED ....... AW(GEN)(MODE). .....MB .HIT % ...USED ....MAX ...USED ..ALLOC .CURR
-------------------- ----------------- -------------- ------- ------ ------- ------- ------- ------- -----
SYS (60,11122,D) ... 20070807 14:26:59 SYS.FOO(1)(RO) ....0.3 ..98.4 ...14.1 ...21.9 ....1.8 ...20.4 ...90
.................... 20070807 14:27:06 .............. ....0.3 ..98.4 ...14.9 ...21.9 ....2.2 ...21.1 ...95
.................... 20070807 14:28:39 .............. ....0.3 ..98.4 ...14.5 ...21.9 ....2.2 ...20.2 ...95
exec sys.dbms_scheduler.run_job('"OLAP_SESSION_STATS_JOB"');
exec sys.dbms_scheduler.disable('"OLAP_SESSION_STATS_JOB"');
Drop Table and Scheduler Job if exists
drop table olap_session_stats cascade constraints purge;
exec sys.dbms_scheduler.drop_job(job_name => '"OLAP_SESSION_STATS_JOB"');
Create a new collections table
create table "OLAP_SESSION_STATS"
( "TIMESTAMP" timestamp (6) with time zone
, "USERNAME" varchar2(30)
, "SID" number
, "SERIAL#" number
, "INST_ID" number
, "OWNER" varchar2(30) not null enable
, "AW_NAME" varchar2(30)
, "SERVER" varchar2(9)
, "PGA_USED" number
, "PGA_MAX" number
, "OPP_SIZE" number
, "AW_MODE" varchar2(10)
, "AW_GEN" number
, "OPP_HITRTO" number
, "OPP_TOUCHS" number
, "OPP_HITS" number
, "OPP_MISSES" number
, "OPP_NEWPGS" number
, "OPP_RECLMD" number
, "PROC_USED" number
, "PROC_ALLOC" number
, "CURS_CURR" number
);
Create a new scheduler job to run every 15 minutes (or adjust)
begin
sys.dbms_scheduler.create_job(
job_name => '"OLAP_SESSION_STATS_JOB"', job_type => 'PLSQL_BLOCK',
job_action => '
begin
insert into sys.OLAP_SESSION_STATS
select systimestamp, gvs.username, gvs.sid, gvs.serial#, gvs.inst_id, owner, aw_name,
server, pga_used_mem, pga_max_mem, pool_size, attach_mode,
generation, round(100*((pool_hits)/((pool_hits)+(pool_misses))),1),
pool_hits+pool_misses opp_touchs, pool_hits, pool_misses,
pool_new_pages, pool_reclaimed_pages, sum(gvm.used),
sum(gvm.max_allocated), sum(gvt.value)
from gv$process gvp, gv$session gvs, gv$aw_calc gva, gv$aw_olap gvo, dba_aws da,
gv$process_memory gvm, gv$sesstat gvt
where gva.session_id = gvs.sid and
gvp.inst_id = gvs.inst_id and
gvs.inst_id = gva.inst_id and
gva.inst_id = gvm.inst_id and
gvm.inst_id = gvt.inst_id and
gva.session_id = gvo.session_id and
gvp.addr = gvs.paddr and
gvt.statistic# = 3 and
da.aw_number = gvo.aw_number and
gvm.pid = gvp.pid and
gvp.serial# = gvm.serial# and
gvt.sid = gvs.sid
group by systimestamp, gvs.username, gvs.sid, gvs.serial#, gvs.inst_id,owner, aw_name,
generation, server, pga_used_mem, pga_max_mem, pool_size, attach_mode, pool_hits,
pool_misses, pool_new_pages, pool_reclaimed_pages
order by gvs.username, gvs.sid, gvs.serial#;
commit;
end;',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
start_date => systimestamp at time zone 'US/Eastern', job_class => 'DEFAULT_JOB_CLASS',
comments => 'Session Statistics Collection for OLAP Analytic Workspaces',
auto_drop => FALSE, enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"OLAP_SESSION_STATS_JOB"',
attribute => 'restartable', value => TRUE);
sys.dbms_scheduler.enable( '"OLAP_SESSION_STATS_JOB"' );
end;
/
Query the table containing the collections
set lines 120 pages 500
col usn for a20 hea "USER (SID,SER#,SVR)"
col atc for a18 hea "OLAP AW(GEN)(MODE)"
col tme hea "COLLECTED"
col pmx for 9990.9 hea "PGA MB|MAX"
col psd for 9990.9 hea "PGA MB|USED"
col oph for 990.9 hea "OLAP|HIT %"
col ops for 9990.9 hea "OLAP|MB"
col pus for 9990.9 hea "PROC MB|USED"
col pal for 9990.9 hea "PROC MB|ALLOC"
col csc for 9990 hea "CURS|CURR"
break on usn on atc skip 1
select username||' ('||sid||','||serial#||','||
decode(server,'DEDICATED','D','SHARED','S','U')||')' usn,
to_char(timestamp,'YYYYMMDD HH24:MI:SS') tme,
owner||'.'||aw_name||'('||aw_gen||')('||decode(aw_mode,
'READ WRITE','RW','READ ONLY','RO','MULTIWRITE','MW','EXCLUSIVE','XW',aw_mode)||')' atc,
round(OPP_SIZE/1024/1024,2) ops, OPP_HITRTO oph, round(PGA_USED/1024/1024,2) psd,
round(PGA_MAX/1024/1024,2) pmx, round(PROC_USED/1024/1024,2) pus,
round(PROC_ALLOC/1024/1024,2) pal, curs_curr csc
from OLAP_SESSION_STATS
order by username, sid, serial#, atc, tme;
.................... ................. .............. ...OLAP ..OLAP .PGA MB .PGA MB PROC MB PROC MB .CURS
USER (SID,SER#,SVR). COLLECTED ....... AW(GEN)(MODE). .....MB .HIT % ...USED ....MAX ...USED ..ALLOC .CURR
-------------------- ----------------- -------------- ------- ------ ------- ------- ------- ------- -----
SYS (60,11122,D) ... 20070807 14:26:59 SYS.FOO(1)(RO) ....0.3 ..98.4 ...14.1 ...21.9 ....1.8 ...20.4 ...90
.................... 20070807 14:27:06 .............. ....0.3 ..98.4 ...14.9 ...21.9 ....2.2 ...21.1 ...95
.................... 20070807 14:28:39 .............. ....0.3 ..98.4 ...14.5 ...21.9 ....2.2 ...20.2 ...95
Run the procedure whenever you wish
exec sys.dbms_scheduler.run_job('"OLAP_SESSION_STATS_JOB"');
Disable the procedure whenever you wish
exec sys.dbms_scheduler.disable('"OLAP_SESSION_STATS_JOB"');
olapdba |
Latest page update: made by olapdba
, Jul 9 2009, 1:44 PM EDT
(about this update
About This Update
Edited by olapdba
view changes - complete history) |
|
Keyword tags:
None
More Info: links to this page
|
There are no threads for this page.
Be the first to start a new thread.
