Already a member?
Sign in
Welcome! Wikis are websites that everyone can build together. It's easy!
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, "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 timestamp, vs.username, vs.sid, vs.serial#, owner, aw_name, server,
pga_used_mem pga_used, pga_max_mem pga_max, pool_size opp_size, attach_mode aw_mode,
generation, round(100*((pool_hits)/((pool_hits)+(pool_misses))),1) opp_hitrto,
pool_hits+pool_misses opp_touchs, pool_hits opp_hits, pool_misses opp_misses,
pool_new_pages opp_newpgs, pool_reclaimed_pages opp_reclmd, sum(vm.used) pmusd,
sum(vm.max_allocated) pmallo, sum(vt.value) curval
from v$process vp, v$session vs, v$aw_calc va, v$aw_olap vo, dba_aws da,
v$process_memory vm, v$sesstat vt
where va.session_id=vs.sid and va.session_id=vo.session_id and vp.addr=vs.paddr and
vt.statistic#=3 and da.aw_number=vo.aw_number and vm.pid=vp.pid and
vp.serial#=vm.serial# and vt.sid=vs.sid and not aw_name in (''AWXML'',''EXPRESS'')
group by systimestamp, vs.username, vs.sid, vs.serial#, 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 vs.username, vs.sid, vs.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, "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 timestamp, vs.username, vs.sid, vs.serial#, owner, aw_name, server,
pga_used_mem pga_used, pga_max_mem pga_max, pool_size opp_size, attach_mode aw_mode,
generation, round(100*((pool_hits)/((pool_hits)+(pool_misses))),1) opp_hitrto,
pool_hits+pool_misses opp_touchs, pool_hits opp_hits, pool_misses opp_misses,
pool_new_pages opp_newpgs, pool_reclaimed_pages opp_reclmd, sum(vm.used) pmusd,
sum(vm.max_allocated) pmallo, sum(vt.value) curval
from v$process vp, v$session vs, v$aw_calc va, v$aw_olap vo, dba_aws da,
v$process_memory vm, v$sesstat vt
where va.session_id=vs.sid and va.session_id=vo.session_id and vp.addr=vs.paddr and
vt.statistic#=3 and da.aw_number=vo.aw_number and vm.pid=vp.pid and
vp.serial#=vm.serial# and vt.sid=vs.sid and not aw_name in (''AWXML'',''EXPRESS'')
group by systimestamp, vs.username, vs.sid, vs.serial#, 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 vs.username, vs.sid, vs.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"');
Latest page update: made by olapdba
, Mar 3 2008, 5:34 PM EST
(about this update
About This Update
Edited by olapdba
view changes
- complete history)
Edited by olapdba
view changes
- complete history)
Keyword tags:
None
More Info: links to this page