Help - Search - Members - Calendar
Full Version: Tablespace IO Stats and perfstat user
Oracle DBA Forums > Oracle > Oracle Forum
serenaender
Hi,

I`ve a problem with the statspack installation in Oracle 9.2.0.7 (windows). I can perform a statspack report without any problem but the Tablespace IO Stats section only shows stats for two tablespaces, PERFSTAT and TEMP.

Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
TEMPTS
38 0 6.3 3.4 45 0 0 0.0
PERFSTAT
17 0 7.6 1.0 0 0 0 0.0
-------------------------------------------------------------

I suspect that is a problem with perfstat`s privileges. Those are the actual PERFSTAT´s privileges:

-- 1 Role for PERFSTAT
GRANT SELECT_CATALOG_ROLE TO PERFSTAT;
ALTER USER PERFSTAT DEFAULT ROLE ALL;
-- 7 System Privileges for PERFSTAT
GRANT CREATE TABLE TO PERFSTAT;
GRANT ALTER SESSION TO PERFSTAT;
GRANT CREATE SESSION TO PERFSTAT;
GRANT CREATE SEQUENCE TO PERFSTAT;
GRANT CREATE PROCEDURE TO PERFSTAT;
GRANT DROP PUBLIC SYNONYM TO PERFSTAT;
GRANT CREATE PUBLIC SYNONYM TO PERFSTAT;
-- 1 Tablespace Quota for PERFSTAT
ALTER USER PERFSTAT QUOTA UNLIMITED ON PERFSTAT;
-- 46 Object Privileges for PERFSTAT
GRANT EXECUTE ON SYS.DBMS_JOB TO PERFSTAT;
GRANT EXECUTE ON SYS.DBMS_SHARED_POOL TO PERFSTAT;
GRANT SELECT ON SYS.STATS$V_$FILESTATXS TO PERFSTAT;
GRANT SELECT ON SYS.STATS$V_$SQLXS TO PERFSTAT;
GRANT SELECT ON SYS.STATS$V_$TEMPSTATXS TO PERFSTAT;
GRANT SELECT ON SYS.STATS$X_$KCBFWAIT TO PERFSTAT;
GRANT SELECT ON SYS.STATS$X_$KSPPI TO PERFSTAT;
GRANT SELECT ON SYS.STATS$X_$KSPPSV TO PERFSTAT;
GRANT SELECT ON SYS.V_$BUFFER_POOL TO PERFSTAT;
GRANT SELECT ON SYS.V_$BUFFER_POOL_STATISTICS TO PERFSTAT;
GRANT SELECT ON SYS.V_$DATABASE TO PERFSTAT;
GRANT SELECT ON SYS.V_$DB_CACHE_ADVICE TO PERFSTAT;
GRANT SELECT ON SYS.V_$DLM_MISC TO PERFSTAT;
GRANT SELECT ON SYS.V_$ENQUEUE_STAT TO PERFSTAT;
GRANT SELECT ON SYS.V_$INSTANCE TO PERFSTAT;
GRANT SELECT ON SYS.V_$INSTANCE_RECOVERY TO PERFSTAT;
GRANT SELECT ON SYS.V_$LATCH TO PERFSTAT;
GRANT SELECT ON SYS.V_$LATCH_CHILDREN TO PERFSTAT;
GRANT SELECT ON SYS.V_$LATCH_MISSES TO PERFSTAT;
GRANT SELECT ON SYS.V_$LATCH_PARENT TO PERFSTAT;
GRANT SELECT ON SYS.V_$LIBRARYCACHE TO PERFSTAT;
GRANT SELECT ON SYS.V_$PARAMETER TO PERFSTAT;
GRANT SELECT ON SYS.V_$PGASTAT TO PERFSTAT;
GRANT SELECT ON SYS.V_$PGA_TARGET_ADVICE TO PERFSTAT;
GRANT SELECT ON SYS.V_$RESOURCE_LIMIT TO PERFSTAT;
GRANT SELECT ON SYS.V_$ROLLSTAT TO PERFSTAT;
GRANT SELECT ON SYS.V_$ROWCACHE TO PERFSTAT;
GRANT SELECT ON SYS.V_$SEGMENT_STATISTICS TO PERFSTAT;
GRANT SELECT ON SYS.V_$SEGSTAT TO PERFSTAT;
GRANT SELECT ON SYS.V_$SEGSTAT_NAME TO PERFSTAT;
GRANT SELECT ON SYS.V_$SESSION TO PERFSTAT;
GRANT SELECT ON SYS.V_$SESSION_EVENT TO PERFSTAT;
GRANT SELECT ON SYS.V_$SESSTAT TO PERFSTAT;
GRANT SELECT ON SYS.V_$SGA TO PERFSTAT;
GRANT SELECT ON SYS.V_$SGASTAT TO PERFSTAT;
GRANT SELECT ON SYS.V_$SHARED_POOL_ADVICE TO PERFSTAT;
GRANT SELECT ON SYS.V_$SQL TO PERFSTAT;
GRANT SELECT ON SYS.V_$SQLAREA TO PERFSTAT;
GRANT SELECT ON SYS.V_$SQL_PLAN TO PERFSTAT;
GRANT SELECT ON SYS.V_$SQLTEXT TO PERFSTAT;
GRANT SELECT ON SYS.V_$SQL_WORKAREA_HISTOGRAM TO PERFSTAT;
GRANT SELECT ON SYS.V_$SYSSTAT TO PERFSTAT;
GRANT SELECT ON SYS.V_$SYSTEM_EVENT TO PERFSTAT;
GRANT SELECT ON SYS.V_$SYSTEM_PARAMETER TO PERFSTAT;
GRANT SELECT ON SYS.V_$UNDOSTAT TO PERFSTAT;
GRANT SELECT ON SYS.V_$WAITSTAT TO PERFSTAT;


The snaps have been created with i_snap_level=>10 and i_snap_level=>5
Balasubramanian
QUOTE (serenaender @ Jun 25 2008, 09:11 PM) *
Hi,

I`ve a problem with the statspack installation in Oracle 9.2.0.7 (windows). I can perform a statspack report without any problem but the Tablespace IO Stats section only shows stats for two tablespaces, PERFSTAT and TEMP.

Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
TEMPTS
38 0 6.3 3.4 45 0 0 0.0
PERFSTAT
17 0 7.6 1.0 0 0 0 0.0
-------------------------------------------------------------

I suspect that is a problem with perfstat`s privileges. Those are the actual PERFSTAT´s privileges:

-- 1 Role for PERFSTAT
GRANT SELECT_CATALOG_ROLE TO PERFSTAT;
ALTER USER PERFSTAT DEFAULT ROLE ALL;
-- 7 System Privileges for PERFSTAT
GRANT CREATE TABLE TO PERFSTAT;
GRANT ALTER SESSION TO PERFSTAT;
GRANT CREATE SESSION TO PERFSTAT;
GRANT CREATE SEQUENCE TO PERFSTAT;
GRANT CREATE PROCEDURE TO PERFSTAT;
GRANT DROP PUBLIC SYNONYM TO PERFSTAT;
GRANT CREATE PUBLIC SYNONYM TO PERFSTAT;
-- 1 Tablespace Quota for PERFSTAT
ALTER USER PERFSTAT QUOTA UNLIMITED ON PERFSTAT;
-- 46 Object Privileges for PERFSTAT
GRANT EXECUTE ON SYS.DBMS_JOB TO PERFSTAT;
GRANT EXECUTE ON SYS.DBMS_SHARED_POOL TO PERFSTAT;
GRANT SELECT ON SYS.STATS$V_$FILESTATXS TO PERFSTAT;
GRANT SELECT ON SYS.STATS$V_$SQLXS TO PERFSTAT;
GRANT SELECT ON SYS.STATS$V_$TEMPSTATXS TO PERFSTAT;
GRANT SELECT ON SYS.STATS$X_$KCBFWAIT TO PERFSTAT;
GRANT SELECT ON SYS.STATS$X_$KSPPI TO PERFSTAT;
GRANT SELECT ON SYS.STATS$X_$KSPPSV TO PERFSTAT;
GRANT SELECT ON SYS.V_$BUFFER_POOL TO PERFSTAT;
GRANT SELECT ON SYS.V_$BUFFER_POOL_STATISTICS TO PERFSTAT;
GRANT SELECT ON SYS.V_$DATABASE TO PERFSTAT;
GRANT SELECT ON SYS.V_$DB_CACHE_ADVICE TO PERFSTAT;
GRANT SELECT ON SYS.V_$DLM_MISC TO PERFSTAT;
GRANT SELECT ON SYS.V_$ENQUEUE_STAT TO PERFSTAT;
GRANT SELECT ON SYS.V_$INSTANCE TO PERFSTAT;
GRANT SELECT ON SYS.V_$INSTANCE_RECOVERY TO PERFSTAT;
GRANT SELECT ON SYS.V_$LATCH TO PERFSTAT;
GRANT SELECT ON SYS.V_$LATCH_CHILDREN TO PERFSTAT;
GRANT SELECT ON SYS.V_$LATCH_MISSES TO PERFSTAT;
GRANT SELECT ON SYS.V_$LATCH_PARENT TO PERFSTAT;
GRANT SELECT ON SYS.V_$LIBRARYCACHE TO PERFSTAT;
GRANT SELECT ON SYS.V_$PARAMETER TO PERFSTAT;
GRANT SELECT ON SYS.V_$PGASTAT TO PERFSTAT;
GRANT SELECT ON SYS.V_$PGA_TARGET_ADVICE TO PERFSTAT;
GRANT SELECT ON SYS.V_$RESOURCE_LIMIT TO PERFSTAT;
GRANT SELECT ON SYS.V_$ROLLSTAT TO PERFSTAT;
GRANT SELECT ON SYS.V_$ROWCACHE TO PERFSTAT;
GRANT SELECT ON SYS.V_$SEGMENT_STATISTICS TO PERFSTAT;
GRANT SELECT ON SYS.V_$SEGSTAT TO PERFSTAT;
GRANT SELECT ON SYS.V_$SEGSTAT_NAME TO PERFSTAT;
GRANT SELECT ON SYS.V_$SESSION TO PERFSTAT;
GRANT SELECT ON SYS.V_$SESSION_EVENT TO PERFSTAT;
GRANT SELECT ON SYS.V_$SESSTAT TO PERFSTAT;
GRANT SELECT ON SYS.V_$SGA TO PERFSTAT;
GRANT SELECT ON SYS.V_$SGASTAT TO PERFSTAT;
GRANT SELECT ON SYS.V_$SHARED_POOL_ADVICE TO PERFSTAT;
GRANT SELECT ON SYS.V_$SQL TO PERFSTAT;
GRANT SELECT ON SYS.V_$SQLAREA TO PERFSTAT;
GRANT SELECT ON SYS.V_$SQL_PLAN TO PERFSTAT;
GRANT SELECT ON SYS.V_$SQLTEXT TO PERFSTAT;
GRANT SELECT ON SYS.V_$SQL_WORKAREA_HISTOGRAM TO PERFSTAT;
GRANT SELECT ON SYS.V_$SYSSTAT TO PERFSTAT;
GRANT SELECT ON SYS.V_$SYSTEM_EVENT TO PERFSTAT;
GRANT SELECT ON SYS.V_$SYSTEM_PARAMETER TO PERFSTAT;
GRANT SELECT ON SYS.V_$UNDOSTAT TO PERFSTAT;
GRANT SELECT ON SYS.V_$WAITSTAT TO PERFSTAT;


The snaps have been created with i_snap_level=>10 and i_snap_level=>5


Hi,

This is a normal behaviour. The temporary tablespace is being used while gathering and generating your statspack report. So obviously you get the I/O stats for Temp tablespace. If you feel it is not worth having I/O to Temp tablespace then set/increase your PGA_AGGREGATE_TARGET (only in case of Dedicated server conection).

Thanks,

Bala
serenaender
Not, my problem is that the rest of tablespaces are missing in the tablespace IO stats section. Only the two mentioned tablespaces appear.
dave
maybe they didnt se an ya ctivity - how long was the snap for
serenaender
QUOTE (dave @ Jun 26 2008, 08:07 AM) *
maybe they didnt se an ya ctivity - how long was the snap for


That is right. I have performed several tests executing statements and other tablespaces have appeared like undo.

Thanks.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.