Sign in or 
Share this
11.Statistics Collection for Oracle OLAP Sessions attached to Multidimensional Datatypes (11.1.0)
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 NOT NULL ENABLE,
"USERNAME" VARCHAR2(30) NOT NULL ENABLE,
"SID" NUMBER NOT NULL ENABLE,
"SERIAL#" NUMBER NOT NULL ENABLE,
"INST_ID" NUMBER NOT NULL ENABLE,
"OWNER" VARCHAR2(30) NOT NULL ENABLE,
"AW_NAME" VARCHAR2(30) NOT NULL ENABLE,
"SERVER" VARCHAR2(9) NOT NULL ENABLE,
"UGA_USED_MEM" NUMBER,
"UGA_MAX_MEM" NUMBER,
"PGA_USED_MEM" NUMBER,
"PGA_ALLOC_MEM" NUMBER,
"PGA_FREEABLE_MEM" NUMBER,
"PGA_MAX_MEM" NUMBER,
"ATTACH_MODE" VARCHAR2(10) NOT NULL ENABLE,
"GENERATION" NUMBER NOT NULL ENABLE,
"TEMP_SPACE_PAGES" NUMBER,
"TEMP_SPACE_READS" NUMBER,
"LOB_READS" NUMBER,
"AGGREGATE_CACHE_HITS" NUMBER,
"AGGREGATE_CACHE_MISSES" NUMBER,
"AGGREGATE_CACHE_HIT_RATIO" NUMBER(*,0) GENERATED ALWAYS AS (100*("AGGREGATE_CACHE_HITS"/("AGGREGATE_CACHE_HITS"+"AGGREGATE_CACHE_MISSES"))) VIRTUAL VISIBLE,
"AGGREGATE_CACHE_TOUCHES" NUMBER GENERATED ALWAYS AS ("AGGREGATE_CACHE_HITS"+"AGGREGATE_CACHE_MISSES") VIRTUAL VISIBLE,
"SESSION_CACHE_HITS" NUMBER,
"SESSION_CACHE_MISSES" NUMBER,
"SESSION_CACHE_HIT_RATIO" NUMBER(*,0) GENERATED ALWAYS AS (100*("SESSION_CACHE_HITS"/("SESSION_CACHE_HITS"+"SESSION_CACHE_MISSES"))) VIRTUAL VISIBLE,
"SESSION_CACHE_TOUCHES" NUMBER GENERATED ALWAYS AS ("SESSION_CACHE_HITS"+"SESSION_CACHE_MISSES") VIRTUAL VISIBLE,
"POOL_CHANGED_PAGES" NUMBER,
"POOL_UNCHANGED_PAGES" NUMBER,
"POOL_SIZE" NUMBER,
"POOL_HITS" NUMBER,
"POOL_MISSES" NUMBER,
"POOL_HIT_RATIO" NUMBER(*,0) GENERATED ALWAYS AS (100*("POOL_HITS"/("POOL_HITS"+"POOL_MISSES"))) VIRTUAL VISIBLE,
"POOL_TOUCHES" NUMBER GENERATED ALWAYS AS ("POOL_HITS"+"POOL_MISSES") VIRTUAL VISIBLE,
"POOL_NEW_PAGES" NUMBER,
"POOL_RECLAIMED_PAGES" NUMBER,
"PROC_MEMORY_USED" NUMBER,
"PROC_MEMORY_ALLOCATED" NUMBER,
"OPENED_CURSORS_CURRENT" NUMBER,
"CURR_DML_COMMAND" VARCHAR2(64),
"PREV_DML_COMMAND" VARCHAR2(64),
"LOGON_TIME" DATE
);
COMMENT ON COLUMN "OLAP_SESSION_STATS"."AGGREGATE_CACHE_HIT_RATIO" IS 'as 100*(aggregate_cache_hits/(aggregate_cache_hits + aggregate_cache_misses))';
COMMENT ON COLUMN "OLAP_SESSION_STATS"."AGGREGATE_CACHE_TOUCHES" IS 'as aggregate_cache_hits + aggregate_cache_misses';
COMMENT ON COLUMN "OLAP_SESSION_STATS"."SESSION_CACHE_HIT_RATIO" IS 'as 100*(session_cache_hits/(session_cache_hits + session_cache_misses))';
COMMENT ON COLUMN "OLAP_SESSION_STATS"."SESSION_CACHE_TOUCHES" IS 'as session_cache_hits + session_cache_misses';
COMMENT ON COLUMN "OLAP_SESSION_STATS"."POOL_HIT_RATIO" IS 'as 100*(pool_hits/(pool_hits + pool_misses))';
COMMENT ON COLUMN "OLAP_SESSION_STATS"."POOL_TOUCHES" IS 'as pool_hits + pool_misses';
set lines 110 pages 500
col comments for a77
select column_name,comments
from user_col_comments
where table_name='OLAP_SESSION_STATS' and comments is not null;
COLUMN_NAME ................ COMMENTS
---------------------------- -----------------------------------------------------------------------------
AGGREGATE_CACHE_HIT_RATIO .. as 100*(aggregate_cache_hits/(aggregate_cache_hits + aggregate_cache_misses))
AGGREGATE_CACHE_TOUCHES .... as aggregate_cache_hits + aggregate_cache_misses
SESSION_CACHE_HIT_RATIO .... as 100*(session_cache_hits/(session_cache_hits + session_cache_misses))
SESSION_CACHE_TOUCHES ...... as session_cache_hits + session_cache_misses
POOL_HIT_RATIO ............. as 100*(pool_hits/(pool_hits + pool_misses))
POOL_TOUCHES ............... as pool_hits + pool_misses
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 (timestamp, username, sid, serial#, inst_id, owner,
aw_name, server, uga_used_mem, uga_max_mem,
pga_used_mem, pga_alloc_mem, pga_freeable_mem, pga_max_mem, attach_mode,
generation, temp_space_pages, temp_space_reads, lob_reads, aggregate_cache_hits,
aggregate_cache_misses, session_cache_hits, session_cache_misses, pool_changed_pages,
pool_unchanged_pages, pool_size, pool_hits, pool_misses, pool_new_pages,
pool_reclaimed_pages, proc_memory_used, proc_memory_allocated, opened_cursors_current,
curr_dml_command, prev_dml_command, logon_time)
select systimestamp, gvs.username, gvs.sid, gvs.serial#, gvs.inst_id, owner, aw_name,
server, gvt1.value, gvt2.value,
pga_used_mem, pga_alloc_mem, pga_freeable_mem, pga_max_mem,
attach_mode, generation, temp_space_pages,
temp_space_reads, lob_reads, aggregate_cache_hits, aggregate_cache_misses,
session_cache_hits, session_cache_misses, pool_changed_pages, pool_unchanged_pages,
pool_size, pool_hits, pool_misses, pool_new_pages, pool_reclaimed_pages, sum(gvm.used),
sum(gvm.max_allocated), gvt.value, curr_dml_command, prev_dml_command,
gvs.logon_time
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, gv$sesstat gvt1, gv$sesstat gvt2
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
gvt.inst_id = gvt1.inst_id and gvt1.inst_id = gvt2.inst_id and
gva.session_id = gvo.session_id and gvp.addr = gvs.paddr and
gvt.statistic# = 3 and gvt1.statistic# = 20 and gvt2.statistic# = 21 and
da.aw_number = gvo.aw_number and gvm.pid = gvp.pid and gvp.serial# = gvm.serial# and
gvt.sid = gvs.sid and gvt1.sid = gvs.sid and gvt2.sid = gvs.sid
group by systimestamp, gvs.username, gvs.sid, gvs.serial#, gvs.inst_id, owner, aw_name,
generation, temp_space_pages, temp_space_reads, lob_reads, aggregate_cache_hits,
aggregate_cache_misses, session_cache_hits, session_cache_misses, pool_changed_pages,
pool_unchanged_pages, server, gvt1.value, gvt2.value,
pga_used_mem, pga_alloc_mem, pga_freeable_mem, pga_max_mem,
pool_size, attach_mode, pool_hits, pool_misses, pool_new_pages, pool_reclaimed_pages,
gvt.value, curr_dml_command, prev_dml_command, gvs.logon_time
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;
/
select a.statistic#, a.value, b.name
from v$mystat a, v$statname b
where a.statistic#=b.statistic# and
value !=0 order by a.value;
set lines 120 pages 500
col usn for a20 hea "USER (SID,SER#,SVR)"
col atc for a22 hea "OLAP AW(GEN)(MODE)"
col tme hea "COLLECTED"
col pmx for 990.9 hea "PGA MB|MAX"
col psd for 990.9 hea "PGA MB|USED"
col oph for 990.9 hea " OLAP|HIT %"
col ops for 990.9 hea " OLAP|MB"
col pal for 990.9 hea "PRC MB|ALLOC"
col pus for 990.9 hea "PRC MB|USED"
col ist for 90 hea "IN|ID"
break on usn on atc skip 1
select username||' ('||sid||','||serial#||','||
decode(server,'DEDICATED','D','SHARED','S','U')||')' usn, inst_id ist,
to_char(timestamp,'YYYYMMDD HH24:MI:SS') tme,
owner||'.'||aw_name||'('||generation||')('||decode(attach_mode,
'READ WRITE','RW','READ ONLY','RO','MULTIWRITE','MW','EXCLUSIVE','XW',attach_mode)||')' atc,
round(POOL_SIZE/1024/1024,2) ops, POOL_HIT_RATIO oph, round(PGA_USED_MEM/1024/1024,2) psd,
round(PGA_MAX_MEM/1024/1024,2) pmx, round(PROC_MEMORY_USED/1024/1024,2) pus,
round(PROC_MEMORY_ALLOCATED/1024/1024,2) pal
from OLAP_SESSION_STATS
order by username, sid, serial#, atc, tme;
......................IN............................................OLAP ..OLAP PGA MB PGA MB PRC MB PRC MB
USER (SID,SER#,SVR) . ID COLLECTED ....... OLAP AW(GEN)(MODE) ........MB .HIT % . USED .. MAX . USED .ALLOC
-------------------- --- ----------------- ---------------------- ------ ------ ------ ------ ------ ------
SYS (170,5,D)..........1 20090720 12:30:10 SYS.EXPRESS(2)(RO)....... 0.4 ..98.0 .. 7.1 . 45.2 .. 2.4 . 45.2
.......................1 20090720 12:30:14 ..........................0.4 ..98.0 .. 6.5 . 45.2 .. 1.2 .. 2.4
.......................1 20090720 12:30:14 ..........................0.4 ..98.0 .. 6.6 . 45.2 .. 1.4 . 42.8
.......................1 20090720 12:30:10 SYS.FOO(1)(RO) ...........0.4 ..98.0 .. 7.1 . 45.2 .. 2.4 . 45.2
.......................1 20090720 12:30:14 ..........................0.4 ..98.0 .. 6.5 . 45.2 .. 1.2 .. 2.4
.......................1 20090720 12:30:14 ..........................0.4 ..98.0 .. 6.6 . 45.2 .. 1.4 . 42.8
select column_name
from user_tab_columns
where table_name='OLAP_SESSION_STATS'
order by data_type, data_length, column_name;
LOGON_TIME
AGGREGATE_CACHE_HITS
AGGREGATE_CACHE_HIT_RATIO
AGGREGATE_CACHE_MISSES
AGGREGATE_CACHE_TOUCHES
GENERATION
INST_ID
LOB_READS
OPENED_CURSORS_CURRENT
PGA_ALLOC_MEM
PGA_FREEABLE_MEM
PGA_MAX_MEM
PGA_USED_MEM
POOL_CHANGED_PAGES
POOL_HITS
POOL_HIT_RATIO
POOL_MISSES
POOL_NEW_PAGES
POOL_RECLAIMED_PAGES
POOL_SIZE
POOL_TOUCHES
POOL_UNCHANGED_PAGES
PROC_MEMORY_ALLOCATED
PROC_MEMORY_USED
SERIAL#
SESSION_CACHE_HITS
SESSION_CACHE_HIT_RATIO
SESSION_CACHE_MISSES
SESSION_CACHE_TOUCHES
SID
TEMP_SPACE_PAGES
TEMP_SPACE_READS
UGA_MAX_MEM
UGA_USED_MEM
TIMESTAMP
SERVER
ATTACH_MODE
AW_NAME
OWNER
USERNAME
CURR_DML_COMMAND
PREV_DML_COMMAND
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 with Virtual Columns and Comments
CREATE TABLE "OLAP_SESSION_STATS"
( "TIMESTAMP" TIMESTAMP (6) WITH TIME ZONE NOT NULL ENABLE,
"USERNAME" VARCHAR2(30) NOT NULL ENABLE,
"SID" NUMBER NOT NULL ENABLE,
"SERIAL#" NUMBER NOT NULL ENABLE,
"INST_ID" NUMBER NOT NULL ENABLE,
"OWNER" VARCHAR2(30) NOT NULL ENABLE,
"AW_NAME" VARCHAR2(30) NOT NULL ENABLE,
"SERVER" VARCHAR2(9) NOT NULL ENABLE,
"UGA_USED_MEM" NUMBER,
"UGA_MAX_MEM" NUMBER,
"PGA_USED_MEM" NUMBER,
"PGA_ALLOC_MEM" NUMBER,
"PGA_FREEABLE_MEM" NUMBER,
"PGA_MAX_MEM" NUMBER,
"ATTACH_MODE" VARCHAR2(10) NOT NULL ENABLE,
"GENERATION" NUMBER NOT NULL ENABLE,
"TEMP_SPACE_PAGES" NUMBER,
"TEMP_SPACE_READS" NUMBER,
"LOB_READS" NUMBER,
"AGGREGATE_CACHE_HITS" NUMBER,
"AGGREGATE_CACHE_MISSES" NUMBER,
"AGGREGATE_CACHE_HIT_RATIO" NUMBER(*,0) GENERATED ALWAYS AS (100*("AGGREGATE_CACHE_HITS"/("AGGREGATE_CACHE_HITS"+"AGGREGATE_CACHE_MISSES"))) VIRTUAL VISIBLE,
"AGGREGATE_CACHE_TOUCHES" NUMBER GENERATED ALWAYS AS ("AGGREGATE_CACHE_HITS"+"AGGREGATE_CACHE_MISSES") VIRTUAL VISIBLE,
"SESSION_CACHE_HITS" NUMBER,
"SESSION_CACHE_MISSES" NUMBER,
"SESSION_CACHE_HIT_RATIO" NUMBER(*,0) GENERATED ALWAYS AS (100*("SESSION_CACHE_HITS"/("SESSION_CACHE_HITS"+"SESSION_CACHE_MISSES"))) VIRTUAL VISIBLE,
"SESSION_CACHE_TOUCHES" NUMBER GENERATED ALWAYS AS ("SESSION_CACHE_HITS"+"SESSION_CACHE_MISSES") VIRTUAL VISIBLE,
"POOL_CHANGED_PAGES" NUMBER,
"POOL_UNCHANGED_PAGES" NUMBER,
"POOL_SIZE" NUMBER,
"POOL_HITS" NUMBER,
"POOL_MISSES" NUMBER,
"POOL_HIT_RATIO" NUMBER(*,0) GENERATED ALWAYS AS (100*("POOL_HITS"/("POOL_HITS"+"POOL_MISSES"))) VIRTUAL VISIBLE,
"POOL_TOUCHES" NUMBER GENERATED ALWAYS AS ("POOL_HITS"+"POOL_MISSES") VIRTUAL VISIBLE,
"POOL_NEW_PAGES" NUMBER,
"POOL_RECLAIMED_PAGES" NUMBER,
"PROC_MEMORY_USED" NUMBER,
"PROC_MEMORY_ALLOCATED" NUMBER,
"OPENED_CURSORS_CURRENT" NUMBER,
"CURR_DML_COMMAND" VARCHAR2(64),
"PREV_DML_COMMAND" VARCHAR2(64),
"LOGON_TIME" DATE
);
Add and Verify Comments to the Virtual Columns
COMMENT ON COLUMN "OLAP_SESSION_STATS"."AGGREGATE_CACHE_HIT_RATIO" IS 'as 100*(aggregate_cache_hits/(aggregate_cache_hits + aggregate_cache_misses))';
COMMENT ON COLUMN "OLAP_SESSION_STATS"."AGGREGATE_CACHE_TOUCHES" IS 'as aggregate_cache_hits + aggregate_cache_misses';
COMMENT ON COLUMN "OLAP_SESSION_STATS"."SESSION_CACHE_HIT_RATIO" IS 'as 100*(session_cache_hits/(session_cache_hits + session_cache_misses))';
COMMENT ON COLUMN "OLAP_SESSION_STATS"."SESSION_CACHE_TOUCHES" IS 'as session_cache_hits + session_cache_misses';
COMMENT ON COLUMN "OLAP_SESSION_STATS"."POOL_HIT_RATIO" IS 'as 100*(pool_hits/(pool_hits + pool_misses))';
COMMENT ON COLUMN "OLAP_SESSION_STATS"."POOL_TOUCHES" IS 'as pool_hits + pool_misses';
set lines 110 pages 500
col comments for a77
select column_name,comments
from user_col_comments
where table_name='OLAP_SESSION_STATS' and comments is not null;
COLUMN_NAME ................ COMMENTS
---------------------------- -----------------------------------------------------------------------------
AGGREGATE_CACHE_HIT_RATIO .. as 100*(aggregate_cache_hits/(aggregate_cache_hits + aggregate_cache_misses))
AGGREGATE_CACHE_TOUCHES .... as aggregate_cache_hits + aggregate_cache_misses
SESSION_CACHE_HIT_RATIO .... as 100*(session_cache_hits/(session_cache_hits + session_cache_misses))
SESSION_CACHE_TOUCHES ...... as session_cache_hits + session_cache_misses
POOL_HIT_RATIO ............. as 100*(pool_hits/(pool_hits + pool_misses))
POOL_TOUCHES ............... as pool_hits + pool_misses
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 (timestamp, username, sid, serial#, inst_id, owner,
aw_name, server, uga_used_mem, uga_max_mem,
pga_used_mem, pga_alloc_mem, pga_freeable_mem, pga_max_mem, attach_mode,
generation, temp_space_pages, temp_space_reads, lob_reads, aggregate_cache_hits,
aggregate_cache_misses, session_cache_hits, session_cache_misses, pool_changed_pages,
pool_unchanged_pages, pool_size, pool_hits, pool_misses, pool_new_pages,
pool_reclaimed_pages, proc_memory_used, proc_memory_allocated, opened_cursors_current,
curr_dml_command, prev_dml_command, logon_time)
select systimestamp, gvs.username, gvs.sid, gvs.serial#, gvs.inst_id, owner, aw_name,
server, gvt1.value, gvt2.value,
pga_used_mem, pga_alloc_mem, pga_freeable_mem, pga_max_mem,
attach_mode, generation, temp_space_pages,
temp_space_reads, lob_reads, aggregate_cache_hits, aggregate_cache_misses,
session_cache_hits, session_cache_misses, pool_changed_pages, pool_unchanged_pages,
pool_size, pool_hits, pool_misses, pool_new_pages, pool_reclaimed_pages, sum(gvm.used),
sum(gvm.max_allocated), gvt.value, curr_dml_command, prev_dml_command,
gvs.logon_time
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, gv$sesstat gvt1, gv$sesstat gvt2
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
gvt.inst_id = gvt1.inst_id and gvt1.inst_id = gvt2.inst_id and
gva.session_id = gvo.session_id and gvp.addr = gvs.paddr and
gvt.statistic# = 3 and gvt1.statistic# = 20 and gvt2.statistic# = 21 and
da.aw_number = gvo.aw_number and gvm.pid = gvp.pid and gvp.serial# = gvm.serial# and
gvt.sid = gvs.sid and gvt1.sid = gvs.sid and gvt2.sid = gvs.sid
group by systimestamp, gvs.username, gvs.sid, gvs.serial#, gvs.inst_id, owner, aw_name,
generation, temp_space_pages, temp_space_reads, lob_reads, aggregate_cache_hits,
aggregate_cache_misses, session_cache_hits, session_cache_misses, pool_changed_pages,
pool_unchanged_pages, server, gvt1.value, gvt2.value,
pga_used_mem, pga_alloc_mem, pga_freeable_mem, pga_max_mem,
pool_size, attach_mode, pool_hits, pool_misses, pool_new_pages, pool_reclaimed_pages,
gvt.value, curr_dml_command, prev_dml_command, gvs.logon_time
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;
/
Check Individual Statistics (optional)
select a.statistic#, a.value, b.name
from v$mystat a, v$statname b
where a.statistic#=b.statistic# and
value !=0 order by a.value;
Query the table containing the collections
set lines 120 pages 500
col usn for a20 hea "USER (SID,SER#,SVR)"
col atc for a22 hea "OLAP AW(GEN)(MODE)"
col tme hea "COLLECTED"
col pmx for 990.9 hea "PGA MB|MAX"
col psd for 990.9 hea "PGA MB|USED"
col oph for 990.9 hea " OLAP|HIT %"
col ops for 990.9 hea " OLAP|MB"
col pal for 990.9 hea "PRC MB|ALLOC"
col pus for 990.9 hea "PRC MB|USED"
col ist for 90 hea "IN|ID"
break on usn on atc skip 1
select username||' ('||sid||','||serial#||','||
decode(server,'DEDICATED','D','SHARED','S','U')||')' usn, inst_id ist,
to_char(timestamp,'YYYYMMDD HH24:MI:SS') tme,
owner||'.'||aw_name||'('||generation||')('||decode(attach_mode,
'READ WRITE','RW','READ ONLY','RO','MULTIWRITE','MW','EXCLUSIVE','XW',attach_mode)||')' atc,
round(POOL_SIZE/1024/1024,2) ops, POOL_HIT_RATIO oph, round(PGA_USED_MEM/1024/1024,2) psd,
round(PGA_MAX_MEM/1024/1024,2) pmx, round(PROC_MEMORY_USED/1024/1024,2) pus,
round(PROC_MEMORY_ALLOCATED/1024/1024,2) pal
from OLAP_SESSION_STATS
order by username, sid, serial#, atc, tme;
......................IN............................................OLAP ..OLAP PGA MB PGA MB PRC MB PRC MB
USER (SID,SER#,SVR) . ID COLLECTED ....... OLAP AW(GEN)(MODE) ........MB .HIT % . USED .. MAX . USED .ALLOC
-------------------- --- ----------------- ---------------------- ------ ------ ------ ------ ------ ------
SYS (170,5,D)..........1 20090720 12:30:10 SYS.EXPRESS(2)(RO)....... 0.4 ..98.0 .. 7.1 . 45.2 .. 2.4 . 45.2
.......................1 20090720 12:30:14 ..........................0.4 ..98.0 .. 6.5 . 45.2 .. 1.2 .. 2.4
.......................1 20090720 12:30:14 ..........................0.4 ..98.0 .. 6.6 . 45.2 .. 1.4 . 42.8
.......................1 20090720 12:30:10 SYS.FOO(1)(RO) ...........0.4 ..98.0 .. 7.1 . 45.2 .. 2.4 . 45.2
.......................1 20090720 12:30:14 ..........................0.4 ..98.0 .. 6.5 . 45.2 .. 1.2 .. 2.4
.......................1 20090720 12:30:14 ..........................0.4 ..98.0 .. 6.6 . 45.2 .. 1.4 . 42.8
Columns in OLAP_SESSION_STATS Available for Query
select column_name
from user_tab_columns
where table_name='OLAP_SESSION_STATS'
order by data_type, data_length, column_name;
LOGON_TIME
AGGREGATE_CACHE_HITS
AGGREGATE_CACHE_HIT_RATIO
AGGREGATE_CACHE_MISSES
AGGREGATE_CACHE_TOUCHES
GENERATION
INST_ID
LOB_READS
OPENED_CURSORS_CURRENT
PGA_ALLOC_MEM
PGA_FREEABLE_MEM
PGA_MAX_MEM
PGA_USED_MEM
POOL_CHANGED_PAGES
POOL_HITS
POOL_HIT_RATIO
POOL_MISSES
POOL_NEW_PAGES
POOL_RECLAIMED_PAGES
POOL_SIZE
POOL_TOUCHES
POOL_UNCHANGED_PAGES
PROC_MEMORY_ALLOCATED
PROC_MEMORY_USED
SERIAL#
SESSION_CACHE_HITS
SESSION_CACHE_HIT_RATIO
SESSION_CACHE_MISSES
SESSION_CACHE_TOUCHES
SID
TEMP_SPACE_PAGES
TEMP_SPACE_READS
UGA_MAX_MEM
UGA_USED_MEM
TIMESTAMP
SERVER
ATTACH_MODE
AW_NAME
OWNER
USERNAME
CURR_DML_COMMAND
PREV_DML_COMMAND
Run the OLAP Statistics Collection Procedure whenever you wish
exec sys.dbms_scheduler.run_job('"OLAP_SESSION_STATS_JOB"');
Disable the OLAP Statistics Collection Procedure
exec sys.dbms_scheduler.disable('"OLAP_SESSION_STATS_JOB"');
..........................
olapdba |
Latest page update: made by olapdba
, Jul 20 2009, 4:21 PM EDT
(about this update
About This Update
Edited by olapdba
1 word added 1 word deleted 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.
