From: Richardson, Brian (brian.richardson@ABAINDUSTRIES.COM)
Date: Mon Aug 19 2002 - 08:26:16 EDT
Does anybody know of a resource where I might find the same type of script
for Informix? I have the same problem. I think this might be a bit OT so it
might be better to send any replies to brian.richardson@abaindustries.com
instead of this list. Thanks!
Brian Richardson
IT Manager
Heroux-Devtek Gas Turbine Components Division
-----Original Message-----
From: Gene Sais [mailto:Gsais@CO.PALM-BEACH.FL.US]
Sent: Friday, August 16, 2002 2:39 PM
To: aix-l@Princeton.EDU
Subject: Re: Oracle Table Space vs JFS Space
here you go, this does more than you ask but gives a nice overview of db
performance.
hth,
gene
---------------------------------------------------------------------------
-- ORASQL:db_perfsum_info.sql
---------------------------------------------------------------------------
/* Performance Statistics Summarization */
set linesize 132
set pagesize 66
set feedback off
set verify off
set heading off;
select 'RUN TIME: ' || to_char(sysdate, 'dd-MON-yyyy hh:mi') ||
' DB OPEN SINCE ' || open_time from v$thread;
set heading on;
prompt
prompt
=======================================================================
prompt Free space is needed in tablespaces to allow dynamic extension
prompt of tables and indexes. When a tablespace cannot support the
prompt the extension, the requesting transaction will fail. When it
prompt is necessary to expand a tablespace, save the objects it held,
prompt then recreate it as a larger datafile. If it had only indexes,
prompt recreating the indexes will be sufficient. If time does not allow,
prompt use the 'alter tablespace ... add datafile command'.
rem Need this view to perform free space query. Create statement rem
requires 'grant select on sys.dba_free_space to ops$oracle' rem or to the
oracle id of the non-sys user running this script. create or replace view
space_free as
select tablespace_name, sum(bytes) total_free
from sys.dba_free_space
group by tablespace_name
/
create or replace view space_total as
select tablespace_name, sum(bytes) total
from sys.dba_data_files
group by tablespace_name
/
column TABLESPACE_NAME format A25
column "TOTAL MB" format 999,999,999,999
column "TOTAL USED" format 999,999,999,999
column "TOTAL FREE" format 999,999,999,999
column "PCT FREE" format 999
set feedback on;
select a.tablespace_name, b.total/1048576 "TOTAL MB",
(b.total-a.total_free)/1048576 "TOTAL USED",
a.total_free/1048576 "TOTAL FREE",
(a.total_free/b.total) * 100 "PCT FREE"
from space_free a, space_total b
where a.tablespace_name = b.tablespace_name
order by 5,1
/
set heading off
set feedback off
select '*** TABLESPACE TOTAL MB is ' || sum(total)/1048576 || ' ***'
from space_total
/
set feedback on
set heading on
set feedback off;
prompt
prompt
=======================================================================
prompt Identify tables and indexes with excessive number of extents. prompt
Any table or index with 50 or more extents is a candidate for prompt
resizing into a single extent. At the very least increase the prompt
default storage for next extent. prompt prompt Do not try to recreate
objects owned by SYS or SYSTEM, but prompt do increase 'next' storage
parameter.
column owner format a14 heading 'Owner'
column seg_name format a24 heading 'Segment| Name'
column seg_type format a6 heading 'Segment| Type'
column next_ext format 999,999,999,999 heading 'Next |Extent'
column ext format 99,999 heading '#Ext'
column pct_incr format 90 heading '%Incr'
select segment_type seg_type, owner, segment_name seg_name, extents ext,
next_extent next_ext, pct_increase pct_incr
from sys.dba_segments
where extents > 50
and segment_type not in ('ROLLBACK', 'TEMP', 'TEMPORARY')
-- and owner not in ('SYS', 'SYSTEM')
order by extents desc,owner,segment_name
/
prompt
prompt
=======================================================================
prompt Determine if effectively using shared sql area of sga.
prompt if ratio of cache missed to executions if more than 1%,
prompt increase init.ora shared_pool_size
column execs format 999,999,999 heading 'Executions'
column cache_miss format 999,999,999 heading 'Cache Misses |while Executing'
column reload_pct format 9.99 heading 'Pct of Reloads| to Misses'
select sum(pins) execs,
sum(reloads) cache_miss, round(((sum(reloads))/(sum(pins)))*100,2)||'%'
reload_pct from v$librarycache; prompt prompt
=======================================================================
prompt If there are too many misses (above .15) increase prompt
DB_BLOCK_BUFFERS in the INIT.ORA file. prompt It will depend on the amount
of free memory available. prompt Should be 25% System memory if Oracle is
Primary Application prompt Buffer Cache stores blocks of data in memory for
tables, prompt indexes, rollback segments and clusters. Tune the shared
pool prompt size first then the database buffer cache.
column value format 999,999,999,999
column miss_ratio format 9.99
select
substr(name,1,20) "Name",
value
from v$sysstat
where name in ('db block gets','consistent gets','physical reads') /
select a.value / (b.value + c.value) MISS_RATIO
from v$sysstat a,
v$sysstat b,
v$sysstat c
where a.name = 'physical reads' and
b.name = 'consistent gets' and
c.name = 'db block gets'
/
prompt
prompt
=======================================================================
prompt Get Current SGA.
select 'TOTAL System Global Area' "NAME", sum(value) bytes from v$sga union
select name, value bytes from v$sga order by 2 desc / prompt prompt
=======================================================================
prompt If large number of disk sorts increase SORT_AREA_SIZE.
select
substr(name,1,20) "Name",
value
from v$sysstat
where name like '%sort%'
/
prompt
prompt
=======================================================================
prompt The percentage of physical reads to logical block reads shows the
number prompt of data buffers. If more than 1%, then increase the number
of prompt of data buffers in the INIT.ORA, or check efficiency of
application prompt queries...
-- column tn format a16 heading 'TSpace Name'
column fn format a50 heading 'Data File Name'
column mb format 999,999,999,999 heading 'Size MB'
column pr format 999,999,999,999 heading 'Phys Reads'
column pw format 999,999,999,999 heading 'Phys Writes'
column prpw format 999,999,999,999 heading 'Sum'
column effic format A9 heading 'Phyrds / |Lphyblkrd'
select
-- tablespace_name tn,
file_name fn,
bytes / 1024 / 1024 mb,
phyrds pr,
phywrts pw,
phyrds + phywrts prpw,
lpad(decode(phyrds,0,null,round(phyrds/phyblkrd,2))||'%',9) effic from
sys.dba_data_files df, v$filestat fs
where
df.file_id = fs.file#
-- order by phywrts desc
order by 5 desc
/
prompt
prompt
=======================================================================
prompt Tuning Redo Log Buffer Latches:
prompt Value should be near zero. If it is not then do the prompt
following: Increase LOG_BUFFER in increments of 5% prompt until "Value"
nears zero. select
substr(name,1,20) "Name",
value
from v$sysstat
where name = 'redo log space requests'
/
prompt
prompt
=======================================================================
prompt Tuning Redo Log Activity:
prompt If ((Misses / Gets) * 100) > 1% or
prompt ((Immediate Misses / (Immediate Gets + Immediate Misses))
prompt * 100) > 1%
prompt for either record than...
prompt Reduce Contention for "Redo Allocation"...
prompt - Decrease the "INIT" parameter "LOG_SMALL_ENTRY_MAX_SIZE"
prompt Reduce Contention for "Redo Copy"
prompt - On Multi. CPU systems only
prompt - Increase the "INIT" parameter "LOG_SIMULTANEOUS_COPIES"
prompt This parameter should be no larger than (2 * # of CPU's)
prompt - On all systems...
prompt - Increase "INIT" parameter "LOG_ENTRY_PREBUILD_THRESHOLD"
prompt This parameter is expressed in bytes
select substr(l.name,1,20) "Name",
gets "Gets",
misses "Misses",
immediate_gets "Immed Gets",
immediate_misses "Immed Misses"
from V$LATCH l, V$LATCHNAME ln
where ln.name in ('redo allocation', 'redo copy') and
ln.latch# = l.latch#;
-- prompt
-- prompt
=======================================================================
-- prompt Show Free memory and State of database
-- select
-- decode (state,0,'FREE',
-- 1,'Read and Modified',
-- 2,'Read and Non-Modified',
-- 4,'Current Block Read ', 'Other') memory,
-- count(*)
-- from sys.x$bh OBJECT NO LONGER DEFINED
-- group by
-- decode (state,0,'FREE',
-- 1,'Read and Modified',
-- 2,'Read and Non-Modified',
-- 4,'Current Block Read ', 'Other')
-- /
prompt
prompt
=======================================================================
prompt Dynamic Extention can cause excessive recursive calls.. prompt
Check for alot of smaller extents and reduce them to one prompt larger
extent. prompt Other causes are...
prompt - Misses on the data dictionary cache
prompt - Firing of database triggers
prompt - Execution of Data Definition Language statements
prompt - Execution of SQL statements within stored procedures,
prompt functions, packages, and anonymous PL/SQL blocks.
prompt - Enforcement of referential integrity constraints.
select
substr(name,1,20) "Name",
value
from V$SYSSTAT
where name = 'recursive calls';
prompt
prompt
=======================================================================
prompt Check dictionary cache for high proportion of
prompt getmisses. This may contribute to recursive calls.
column miss_ratio format 9.99;
select parameter, gets, getmisses, getmisses/gets miss_ratio,
scancompletes
from v$rowcache
where gets/getmisses > 10
and getmisses > 0
order by 4 desc
/
prompt
prompt
=======================================================================
prompt Reducing Free List Contention:
prompt If Percent Wait is greater than 1% than...
prompt Re-create the table and make "FREELISTS" equal to the
prompt number of Oracle processes that concurrently insert
prompt data into the table...
select A.class "Class", A.count "Count",
sum(B.value) "Total Requests",
round(((A.count / sum(B.value)) * 100),3) "Percent Wait" from
V$WAITSTAT A, V$SYSSTAT B where A.class = 'free list' and
B.name in ('db block gets', 'consistent gets')
group by A.class, A.count;
prompt
prompt
=======================================================================
prompt Rollback wait statisitics indicate contention for prompt these
segments. If PCT_WAITS (count/sum of all gets) prompt is greater than .01,
add a rollback segment. column PCT_WAITS format 999.99 REM The next block
of code, which includes a select in the from REM clause will work on
version 7.1(.5) or better. When the 7.0 REM databases are upgraded, use it
instead of the current REM statements (column get_count...;
REM select sum(value) get_count ...
REM select class, count...)
REM
REM select class, count, /* all_gets.val_sum, */
REM DECODE( all_gets.val_sum, 0, 0, count/all_gets.val_sum) PCT_WAITS
REM from v$waitstat,
REM (select NVL(sum(value), 0) val_sum from v$sysstat
REM where name in ('db block gets','consistent gets')) all_gets
REM where class in ('system undo header',
REM 'system undo block',
REM 'undo header',
REM 'undo block')
REM /
REM noprint option suppresses display of the get_count column
REM which is of use within the 'select class ..' query
column get_count new_value get_cnt noprint;
select nvl(sum(value), 0) get_count
from v$sysstat
where name in ('db block gets','consistent gets')
/
select class, count,
decode (&get_cnt, 0, 0, count/&get_cnt) PCT_WAITS
from v$waitstat
where class in('system undo header',
'system undo block',
'undo header',
'undo block')
/
prompt
prompt
=======================================================================
prompt Check Dispatcher Protocol Busy Times.
prompt If Protocol Busy Time % > 40 then Add a Dispatcher column "Network
Protocol" format a40
column "Busy Time %" format 99.99
select network "Network Protocol",
sum(busy)/(sum(idle)+sum(busy)) * 100 "Busy Time %"
from v$dispatcher
group by network
/
prompt
prompt
=======================================================================
prompt Show the resource usage, including calculated percentage values set
pagesize 1000 set linesize 150 column current_utilization heading
"Current|Utilisation" format 9999 column max_utilization heading
"Max|Utilisation" format 9999 column initial_allocation heading
"Initial|Allocation" format A10 column limit_value heading "Limit|Value"
format A10 column cur_pcnt FORMAT 999.99 column max_pcnt FORMAT 999.99
select
resource_name ,
current_utilization ,
max_utilization ,
initial_allocation ,
limit_value ,
TO_NUMBER ( decode( limit_value , ' 0' , '100' ,
' UNLIMITED' , '0' ,
TO_CHAR(current_utilization / limit_value * 100) ) )
cur_pcnt ,
TO_NUMBER ( decode( limit_value , ' 0' , '100' ,
' UNLIMITED' , '0' ,
TO_CHAR(max_utilization / limit_value * 100) )
) max_pcnt
from
v$resource_limit
order by
max_pcnt , max_utilization , limit_value desc , initial_allocation desc
/
prompt
prompt
=======================================================================
prompt *** End of Report ***
prompt
=======================================================================
set feedback on
exit
>>> jriorda2@CSC.COM 08/16/02 02:05PM >>>
Hi John
Thanks for responding. To answer your question, yes I am looking to find
the free space in the tablespace. I get reports on the filesystem space
once a week with scripts I wrote and nmon. But most of the filesystems that
the tables are on, report at 100% . When the dba's created the tables they
used the complete filesystem space.
I appreciate you looking for the script. I am trying my hand at writing an
sql statement that might get the information but it is not going very well.
Thanks again
John
John Riordan
Unix Systems Administrator
CSC/BIW DD(X)
Brunswick, ME 04011
207.442.1094 jriorda2@csc.com
John Jolet
<john.jolet To: aix-l@Princeton.EDU
@MISYSHEALTHC cc:
ARE.COM> Subject: Re: Oracle Table Space
vs JFS Space
Sent by: IBM
AIX
Discussion
List <aix-l
08/16/2002
12:10 PM
Please
respond to
IBM AIX
Discussion
List
are you needing to find out how much free space is in the tablespace, or on
the filesystem? There IS query you can run to find the free space in a
table space. If the tablespace is set up to autoallocate, it should never
fill up. You can't extend the filesystem?
I'll try to find the script I had a year or so ago to find out the free
space in the tablespace. At 11:12 AM 8/16/2002 -0400, you wrote:
>Hi All,
>
>I'm looking to see if any of you have had to monitor data in a defined
>table space residing on a filesystem. I know our Oracle dba can do
>this. But they are off site and most of the table spaces have been
>created to
the
>full capacity of the filesystems. We had a full filesystem this
>morning and I have been asked to include actual data size in the tables
>with my weekly disk statistics. I would assume using sqlplus could
>extract the data and create a text file that I could import into excel.
>But I don't know enough about sqlplus. I have tried to get in touch
>with our Oracle dba's but as of yet no luck. I have also looked
>through the list archives but did not really find anything. We have
>six Oracle servers three H-50's, an H-80, and two B80's.
>
>Hope I did not ramble, any help would be greatly appreciated.
>
>Thanks in advance
>John
>
>
>
>John Riordan
>Unix Systems Administrator
>CSC/BIW DD(X)
>Brunswick, ME 04011
>207.442.1094 jriorda2@csc.com
This archive was generated by hypermail 2.1.7 : Wed Apr 09 2008 - 22:16:09 EDT