 |
|
Oracle Scripts for Improving Database Performance
Oracle Tips by Burleson Consulting
December 2022 |
Even if you have
some fancy third-party tools for measuring database performance, you can benefit
from extending your basic Oracle performance tools and creating a proactive,
customized framework for tuning your Oracle databases.
by Don Burleson
This article originally appeared in Oracle Magazine.
LISTING 1: A sample perf UNIX script that replaces the regular UTLESTAT
utility with a customized version called SPECIALESTAT.
if [ $# != 1 ]
then
echo "usage: $0 { bstat | estat }"
exit 1
fi
if [ "${1}" != bstat -a "${1}" != estat ]
then
echo "usage: $0 { bstat | estat }"
exit 1
fi
SQLPATH=/usr/perfmon
if [ "${1}" = bstat ]
then
# Begin collection
sqlplua << !
connect internal
@${ORACLE_HOME}/rdbms/admin/utlbstat
exit
!
else
# End data collection
sqlplus << !
connect internal
@${SQLPATH}/specialestat
exit
!
sqlplus / @${SQLPATH}/tracker ${ORACLE_SID}
fi
exit 0
LISTING
2: This script invokes the tracker utility to
capture the UTLESTAT information into permanent tables and then drop the
temporary tables. rem This SQL script will load the tracking tables
insert into track_stats
( oracle_sid, collection_started)
select '&1',min(stats_gather_times)
from sys.stats$dates;
update track_stats
set collection_ended =
(select max(stats_gather_times)
from sys.stats$dates),
run_date = to_date(substr(collection_started,1,12),'DD-MON-YY HH24'),
consistent_gets =
(select change
from sys.stats$stats
where name = 'consistent gets'),
block_gets =
(select change
from sys.stats$stats
where name = 'db block gets'),
physical_reads =
(select change
from sys.stats$stats
where name = 'physical reads'),
buffer_busy_waits =
(select change
from sys.stats$stats
where name = 'buffer busy waits'),
buffer_free_needed =
(select change
from sys.stats$stats
where name = 'free buffer requested'),
free_buffer_waits =
(select change
from sys.stats$stats
where name = 'free buffer waits'),
free_buffer_scans =
(select change
from sys.stats$stats
where name = 'free buffer scans'),
enqueue_timeouts =
(select change
from sys.stats$stats
where name = 'enqueue timeouts'),
redo_space_wait =
(select change
from sys.stats$stats
where name = 'redo log space wait time'),
write_wait_time =
(select change
from sys.stats$stats
where name = 'write wait time'),
write_complete_waits =
(select change
from sys.stats$stats
where name = 'write complete waits'),
rollback_header_gets =
(select sum(trans_tbl_gets)
from sys.stats$roll),
rollback_header_waits =
(select sum(trans_tbl_waits)
from sys.stats$roll)
where collection_ended is null;
insert into latches
(ls_latch_name, ls_latch_gets, ls_latch_misses,
ls_latch_sleeps, ls_latch_immed_gets,
ls_latch_immed_misses)
select name, gets, misses, sleeps, immed_gets, immed_miss
from sys.stats$latches;
update latches set
ls_collection_started =
(select min(stats_gather_times)
from sys.stats$dates)
where ls_oracle_sid is null;
update latches set
run_date = to_date(substr(ls_collection_started,
1,12),'DD-MON-YY HH24')
where ls_oracle_sid is null;
update latches
set ls_oracle_sid =
(select '&1'
from sys.dual),
ls_collection_ended =
(select max(stats_gather_times)
from sys.stats$dates)
where ls_oracle_sid is null;
LISTING 3: This routine interrogates all tablespaces and dumps
the information into a statistical table.
insert into tablespace_stat values (
select dfs.tablespace_name,
round(sum(dfs.bytes)/1048576,2),
round(max(dfs.bytes)/1048576,2)
from sys.dba_free_space dfs
group by dfs.tablespace_name
order by dfs.tablespace_name);
LISTING 4: Attach this script to a cron process to gather table-extent
information at a specified time interval.
insert into tab_stat values(
select ds.tablespace_name,
dt.owner,
dt.table_name,
ds.bytes/1024,
ds.extents,
dt.max_extents,
dt.initial_extent/1024,
dt.next_extent/1024,
dt.pct_increase,
dt.pct_free,
dt.pct_used
from sys.dba_segments ds,
sys.dba_tables dt
where ds.tablespace_name = dt.tablespace_name
and ds.owner = dt.owner
and ds.segment_name = dt.table_name
order by 1,2,3);
LISTING 5: This table-extents-report script joins the extents
table against itself to show growth in extents.
break on c0 skip 2 on c1 skip 1
ttitle " Table Report| > 50 Extents or new extents";
spool /tmp/rpt10
select
distinct
b.sid c0,
substr(b.owner,1,6) c1,
substr(b.tablespace_name,1,10) c2,
substr(b.table_name,1,20) c3,
(b.blocks_alloc*2048)/1024 c4,
c.next_extent/1024 c5,
a.extents c6,
b.extents c7
from tab_stat a,
tab_stat b,
dba_tables c
where
rtrim(c.table_name) = rtrim(b.table_name)
and
a.sid = b.sid
and
rtrim(a.tablespace_name) <> 'SYSTEM'
and
a.tablespace_name = b.tablespace_name
and
a.table_name = b.table_name
and
to_char(a.run_date) = to_char(b.run_date-7)
-- compare to one week prior
and
(
a.extents < b.extents
-- where extents has increased
or
b.extents > 50
)
order by b.sid;
LISTING 6: Use this script to get a fast overview of the state
of a troubled system.
spool /tmp/snap;
prompt****************************************************
prompt Hit Ratio Section
prompt****************************************************
prompt
prompt =========================
prompt BUFFER HIT RATIO
prompt =========================
prompt (should be > 70, else increase db_block_buffers in init.ora)
--select trunc((1-(sum(decode(name,'physical reads',value,0))/
--(sum(decode(name,'db block gets',value,0))+
--(sum(decode(name,'consistent gets',value,0)))))
-- )* 100) "Buffer Hit Ratio"
--from v$sysstat;
column "logical_reads" format 99,999,999,999
column "phys_reads" format 999,999,999
column "phy_writes" format 999,999,999
select a.value + b.value "logical_reads",
c.value "phys_reads",
d.value "phy_writes",
round(100 * ((a.value+b.value)-c.value) /
(a.value+b.value))
"BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c, v$sysstat d
where
a.statistic# = 37
and
b.statistic# = 38
and
c.statistic# = 39
and
d.statistic# = 40;
prompt
prompt
prompt =========================
prompt DATA DICT HIT RATIO
prompt =========================
prompt (should be higher than 90 else increase shared_pool_size in init.ora)
prompt
column "Data Dict. Gets" format 999,999,999
column "Data Dict. cache misses" format 999,999,999
select sum(gets) "Data Dict. Gets",
sum(getmisses) "Data Dict. cache misses",
trunc((1-(sum(getmisses)/sum(gets)))*100) "DATA DICT CACHE HIT
RATIO"
from v$rowcache;
prompt
prompt =========================
prompt LIBRARY CACHE MISS RATIO
prompt =========================
prompt (If > .1, i.e., more than 1% of the pins
prompt resulted in reloads, then increase the shared_pool_size in init.ora)
column "LIBRARY CACHE MISS RATIO" format 99.9999
column "executions" format 999,999,999
column "Cache misses while executing" format 999,999,999
select sum(pins) "executions", sum(reloads) "Cache misses while executing",
(((sum(reloads)/sum(pins)))) "LIBRARY CACHE MISS RATIO"
from v$librarycache;
