| Previous | Table of Contents | Next |
Once these tables have been populated, it is a relatively simple matter to write some SQL to generate alert reports for the DBA.
A tablespace alert report may be defined by the DBA, but would usually contain a list of tablespaces where the largest fragment is less than 10 percent of the size of the tablespace. Regardless, the data that is created from table analysis routines can be used to generate variance reports that alert the DBA to possible problems in the databases. (See Listing 11.9.) Note the clever method of joining the extents table against itself to show growth in extents.
Listing 11.9 A table extents report.
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;
While the marketplace is full of fancy tools for measuring Oracle performance, the basic Oracle DBA can benefit from extending the basic Oracle performance tools to create a proactive, customized framework for Oracle tuning. Today’s Oracle DBA must automate as many functions as possible in order to maximize productivity.
Once a mechanism has been put in place to deliver the statistics to a centralized Oracle database, a menu-based system can be created to allow reports to be generated against the data. Here is a sample menu for such a system:
Performance Reports for Oracle
MAIN MENU
Enter choice ==>
This menu is driven by a Korn shell script, as shown in Listing 11.10.
Listing 11.10 A Korn shell script to extract Oracle performance data.
trap "exit 0" 1 2 3 4 5 6 7 8 10 15
USER_NAME='id -un'
#if [ "$USER_NAME" != "root" -a "$USER_NAME" != "oracle" ]
if [ "$USER_NAME" != "root" ]
then
echo
echo "You must be ROOT to execute this script."
echo
return 0
fi
# Set PATH
PATH=/bin:/u01/bin:/etc:/u01/contrib/bin:/u01/oracle/bin:/u01/
oracle/etc:/u01/lib:/u01/lib/acct:/u01/backup/bin
export PATH
# Set up Oracle environment
ORACLE_HOME=/u01/oracle
ORAKITPATH=/u01/oracle/resource
FORMS30PATH=/u01/oracle/resource
MENU5PATH=/u01/oracle/resource
export ORACLE_HOME ORACLE_SID ORAKITPATH FORMS30PATH MENU5PATH
PATH=$PATH:${ORACLE_HOME}/bin:
export PATH
ORAENV_ASK=NO
TWO_TASK=perfdb
export TWO_TASK
unset ORACLE_SID
get_sid()
{
echo
echo "Enter the database name: \c"
read DBNAME1
END1=${DBNAME1}
export END1
}
get_parms()
{
#**********************************************************************
# Prompt for database name
#**********************************************************************
echo
echo "Enter the database name: \c"
read DBNAME1
case $DBNAME1 in
"" ) END1="\" \""
;;
* ) END1="\" and upper(rtrim(brndb.sid))=upper('"${DBNAME1}"')\""
;;
esac
export END1
} # End of get_parms()
get_date()
{
#****************************************************************
# Prompt for start date
#****************************************************************
echo
echo "Enter a start date:(mm/dd/yy) (or press enter for none) \c"
read DATE1
if [ $DATE1 ]
then
END2="\" and run_date >=to_date('$DATE1','MM/DD/YY')\""
else
END2="\" and run_date >=to_date('01/01/01','MM/DD/YY')\""
fi
export END2
#**************************************************************
# Prompt for end date
#**************************************************************
echo
echo "Enter an end date:(mm/dd/yy) (or press enter for none) \c"
read DATE2
if [ $DATE2 ]
then
END3="\" and run_date <=to_date('$DATE2','MM/DD/YY')\""
else
END3="\" and run_date <=to_date('12/31/99','MM/DD/YY')\""
fi
export END3
} # end of get_date...
get_extents()
{
#**********************************************************************
# Prompt for number of extents
#**********************************************************************
echo
echo "Where number of extents is greater than (default 0):\c"
read EXT1
if [ $EXT1 ]
then
END4="\" and extents > $EXT1\""
else
END4="\" and extents > 0\""
fi
export END4
} # end of get_extents...
three_mess()
{
echo " "
echo "Data for this report is collected directly from the remote host."
echo " "
echo "Therefore, you may not want to run this against a database"
echo "unless you are prepared to wait awhile."
echo " "
}
#**********************************************************************
# Main routine loop
#**********************************************************************
while true ; do
clear
echo " PER Reports for Oracle"
echo
echo " 1. SGA waits & Buffer hit ratio report"
echo " 2. Cache hit ratio report"
echo " 3. Data Dictionary report"
echo " 4. File report"
echo " 5. Tablespace report"
echo " 6. Table report"
echo " 7. Index report"
echo
echo " 8. SGA - hit ratio/wait count alert "
echo " 9. Tablespace - fragment/free space alert"
echo " 10. Table - Increased extents alert"
echo " 11. Index - Increased extents alert"
echo
echo " 12. Audit - Check a database for security violations"
echo
echo " 14. EXIT"
echo
echo $MESS1
echo
echo "Enter choice ==> \c:"
read PICK
case $PICK in
"1") PREFIX=ps
one_mess;
get_parms;
get_date;
/u01/oracle/per/perf/rpt1.sh
MESS1="Report has been spooled to /tmp/rpt1.lst"
;;
"2") PREFIX=ds
one_mess;
get_parms;
get_date;
/u01/oracle/per/perf/rpt2.sh
MESS1="Report has been spooled to /tmp/rpt2.lst"
;;
"3") PREFIX=ls
one_mess;
get_parms;
get_date;
/u01/oracle/per/perf/rpt3.sh
MESS1="Report has been spooled to /tmp/rpt3.lst"
;;
"4") PREFIX=fs
one_mess;
get_parms;
get_date;
/u01/oracle/per/perf/rpt4.sh
MESS1="Report has been spooled to /tmp/rpt4.lst"
;;
"5") get_parms;
get_date;
/u01/oracle/per/perf/rpt5.sh
MESS1="Report has been spooled to /tmp/rpt5.lst"
;;
"6") get_parms;
get_date;
get_extents;
/u01/oracle/per/perf/rpt6.sh
MESS1="Report has been spooled to /tmp/rpt6.lst"
;;
"7") get_parms;
get_date;
get_extents;
/u01/oracle/per/perf/rpt7.sh
MESS1="Report has been spooled to /tmp/rpt7.lst"
;;
"8") nohup /u01/oracle/per/perf/rpt8.sh > /tmp/rpt8.lst 2>&1 &
MESS1="Report will be spooled to /tmp/rpt8.lst"
;;
"9") nohup /u01/oracle/per/perf/rpt9.sh > /tmp/rpt9.lst 2>&1 &
MESS1="Report will be spooled to /tmp/rpt9.lst"
;;
"10") nohup /u01/oracle/per/perf/rpt10.sh > /tmp/rpt10.lst 2>&1 &
MESS1="Report will be spooled to /tmp/rpt10.lst"
;;
"11") nohup /u01/oracle/per/perf/rpt11.sh > /tmp/rpt11.lst 2>&1 &
MESS1="Report will be spooled to /tmp/rpt11.lst"
;;
"12") three_mess;
get_sid;
/u01/oracle/per/perf/rpt12.sh
MESS1="Report has been spooled to /tmp/rpt12.lst"
;;
"14") echo bye
break
;;
esac
| Previous | Table of Contents | Next |